Inserting data into a static Hive partition using Spark SQL

Question

I have trouble figuring out how to insert data into a static partition of a Hive table using Spark SQL. I can use code like this to write into dynamic partitions:

df.write.partitionBy("key").insertInto("my_table")

However, I can't figure out how to insert the data into a static partition. That means, I want to define the partition where the entire DataFrame should be written without the need to add the column to the DataFrame.

I see static partitioning mentioned in the InsertIntoHiveTable class, so I guess it is supported. Is there a public API to do what I want?

were you able to find any API similar to writing into Dynamic partition? — Explorer
No, I was not able to find anything and ended up registering the RRD as a temporary table and running SQL INSERT from that. However a lot of the Hive code has been rewritten in Spark 2.0 so it might be possible now. — Lukáš Lalinský
did this work for you, Iam unable to doe partition by with insertinto — Jubin Justifies

tpham tpham · Accepted Answer · 2016-08-05T08:54:46

You can use

DataFrame tableMeta = sqlContext.sql(String.format("DESCRIBE FORMATTED %s", tableName));
String location = tableMeta.filter("result LIKE 'Location:%'").first().getString(0);

and use regex to get your table partition. Once you get the table location, you can easily construct the partition location like

String partitionLocation = location + "/" + partitionKey

(partitionKey is something like dt=20160329/hr=21)

Then, you can write to that path

df.write.parquet(partitionLocation)

(in my case when I build the dataframe, I do not include the partition columns in. Not sure if there is any error when partition columns are included)

Inserting data into a static Hive partition using Spark SQL

1 Answers