I am trying to push incremental data into a base hive table. To do that, I prepared the data in my staging database and stored it in a table with exact ddl of my base table. In order to move the data from staging to base, I am trying the "Exchange partition" on the hive table from spark.sql as below.
Alter table base.basetable drop partition (vehicle='BIKE'); ALTER TABLE base.basetable EXCHANGE PARTITION (vehicle='BIKE') WITH TABLE staging.stagingtable;
But I see the exception:
org.apache.spark.sql.catalyst.parser.ParseException: Operation not allowed: ALTER TABLE EXCHANGE PARTITION(line 1, pos 0) == SQL == ALTER TABLE base.basetable (vehicle='BIKE') WITH TABLE staging.stagingtable ^^^
This looks like spark sql doesn't support "Exchange partition" even though I have set enableHiveSupport() in my program:
val spark = SparkSession.builder().config(conf).master("yarn").enableHiveSupport().config("hive.exec.dynamic.partition", "true").config("hive.exec.dynamic.partition.mode", "nonstrict").getOrCreate()
Shouldn't enableHiveSupport() give me all support & access for Hive querying ?
I can do
insert overwrite base.basetable partition(vehicle) select * from staging.stagingtable where vehicle='BIKE'
Some of our tables have huge data and if the feature "Exchange partition" doesn't work in spark sql, is there any alternative to it ?