Spark Sql 1.5 dataframe saveAsTable how to add hive table properties

Question

I am running spark sql on hive. I need to add auto.purge table properties while creating new hive table. I tried below code to add options while calling saveAsTable method :

inputDF.write.option("auto.purge" -> "true").saveAsTable(hiveTableName)

Above line of code added a property under WITH SERDEPROPERTIES of table. I need to add this property under TBLPROPERTIES section of hive DDL.

1.2.1 hive . when i tried drop table xyz purge; spark sql disregard purge and give exception saying cannot delete from encryption zone. — Aaditya Raj
Looks like this property is not working. Are you dropping the same way as you mentioned in your question ? — Sandeep Singh
What about a .registerTempTable("tmp"), then an explicit "CREATE TABLE target" SQL command, then an explicit "INSERT INTO TABLE target SELECT * FROM tmp"? — Samson Scharfrichter
@SamsonScharfrichter Its a good idea, but here my opinion is not to create a target table explicitly. This will also create a target table in hive by selecting data and schema from temporary table SqlContext.sql("create db.table_name as select * from df_temp_tbl") — Sandeep Singh

Aaditya Raj Aaditya Raj · Accepted Answer · 2017-01-05T07:17:03

Finally i found a solution, I am not sure if this is the best solution. Unfortunately Spark 1.5 sql saveAsTable method doesn't support table property as input.They are creating new tableProperties map before hive table creation. check out below code: https://github.com/apache/spark/blob/v1.5.0/sql/hive/src/main/scala/org/apache/spark/sql/hive/HiveMetastoreCatalog.scala

To add table properties to existing hive table use alter table command.

ALTER TABLE table_name SET TBLPROPERTIES ('auto.purge'='true');

Above command will add table property to hive meta store. To drop existing table inside encryption zone run above command before drop command.

Spark Sql 1.5 dataframe saveAsTable how to add hive table properties

1 Answers