How to insert Spark DataFrame to Hive Internal table?

Question

What's the right way to insert DF to Hive Internal table in Append Mode. It seems we can directly write the DF to Hive using "saveAsTable" method OR store the DF to temp table then use the query.

df.write().mode("append").saveAsTable("tableName")

OR

df.registerTempTable("temptable") 
sqlContext.sql("CREATE TABLE IF NOT EXISTS mytable as select * from temptable")

Will the second approach append the records or overwrite it?

Is there any other way to effectively write the DF to Hive Internal table?

Answer 1

df.saveAsTable("tableName", "append") is deprecated. Instead you should the second approach.

sqlContext.sql("CREATE TABLE IF NOT EXISTS mytable as select * from temptable")

It will create table if the table doesnot exist. When you will run your code second time you need to drop the existing table otherwise your code will exit with exception.

Another approach, If you don't want to drop table. Create a table separately, then insert your data into that table.

The below code will append data into existing table

sqlContext.sql("insert into table mytable select * from temptable")

And the below code will overwrite the data into existing table

sqlContext.sql("insert overwrite table mytable select * from temptable")

This answer is based on Spark 1.6.2. In case you are using other version of Spark I would suggests to check the appropriate documentation.