Spark schema using bucketBy is NOT compatible with Hive

user_10316689 0
Viewed 2653

I am using Spark version 2.3 to write and save dataframes using bucketBy.

The table gets created in Hive but not with the correct schema. I am not able to select any data from the Hive table.

(DF.write
   .format('orc')
   .bucketBy(20, 'col1')
   .sortBy("col2")
   .mode("overwrite")
   .saveAsTable('EMP.bucketed_table1'))

I am getting below message:

Persisting bucketed data source table emp.bucketed_table1 into Hive metastore in Spark SQL specific format, which is NOT compatible with Hive.

The Hive Schema is being created as shown below:

hive> desc EMP.bucketed_table1;
OK
col                     array<string>           from deserializer

How to save and write dataframes to a Hive table that can be viewed later?

pyspark apache-spark hive
2 Answers

While Spark (in versions <= 2.4, at least) doesn't directly support Hive's bucketing format, as described here and here, it is possible to get Spark to output bucketed data that is readable by Hive, by using SparkSQL to load the data into Hive; following your example it would be something like:

//enable Hive support when creating/configuring the spark session
val spark = SparkSession.builder().enableHiveSupport().getOrCreate()

//register DF as view that can be used with SparkSQL
DF.createOrReplaceTempView("bucketed_df")

//create Hive table, can also be done manually on Hive
val createTableSQL = "CREATE TABLE bucketed_table1 (col1 int, col2 string) CLUSTERED BY col1 INTO 20 BUCKETS STORED AS PARQUET"
spark.sql(createTableSQL)

//load data from DF into Hive, output parquet files will be bucketed and readable by Hive
spark.sql("INSERT INTO bucketed_table1 SELECT * FROM bucketed_df")
user_14968900

All other DF Writer methods allow subsequent selecting from those bucketed tables via Hive and Impala editors, except they are not Spark bucketed.

You need to select from bucketed via spark.read. ...

This should help: https://spark.apache.org/docs/latest/sql-programming-guide.html

The answer to your question is that it not currently possible to select via Hive or Impala from Spark bucketed tables.

user_69339930
Related