question Re: Hive compactions on External table in Archives of Support Questions (Read Only)

Hive compactions on External table

chrismcg89 — Wed, 11 May 2016 18:05:30 GMT

Hi,

I am currently using Spark streaming to write to an external hive table every 30 mins.

rdd.toDF().write.partitionBy("dt").options(options).format("orc").mode(SaveMode.Append).saveAsTable("table_name")

The issue with this is it creates lots of small files in HDFS, like so

	part-00000
	part-00000_copy_1

My table was created with transactions enabled, and I have enabled ACID transactions on the Hive instance however, I can't see any compactions running nor do any get created when I force compaction with ALTER TABLE command. I would expect compaction to run and merge these files as they are very small 200 KB's in size.

Any idea's or help greatly appreciated

Re: Hive compactions on External table

ewalk — Thu, 12 May 2016 01:11:07 GMT

Hi @Chris McGuire,

Can you please provide an "hdfs dfs -ls -R <table-folder>"

Compaction only operates on tables with delta directories. I suspect that the method you're using (SaveMode.Append) is just appending to the existing partition (or adding a new partition) and not actually creating deltas.

Best,

Eric

Re: Hive compactions on External table

quadoss — Thu, 12 May 2016 01:28:43 GMT

@Chris McGuire,

Make sure these are set properties are set to true as these would merge the small files into one or more big files.

hive.merge.mapfiles

hive.merge.mapredfiles

hive.merge.tezfiles

Re: Hive compactions on External table

chrismcg89 — Thu, 12 May 2016 01:55:11 GMT

@Eric Walk Thanks, Yes you are correct Spark isn't writing deltas its just adding to the existing partition.

Any idea on how to get Spark to write the delta's?

-rw-r--r--   3 cmcguire hdfs          0 2016-05-11 16:36 /test_data/test_test_tbl/_SUCCESS
drwxr-xr-x   - cmcguire hdfs          0 2016-05-11 16:40 /test_data/test_tbl/dt=11-05-2016
-rwxr-xr-x   3 cmcguire hdfs       3750 2016-05-11 16:37 /test_data/test_tbl/dt=11-05-2016/part-00000
-rwxr-xr-x   3 cmcguire hdfs       5468 2016-05-11 16:37 /test_data/test_tbl/dt=11-05-2016/part-00000_copy_1
-rwxr-xr-x   3 cmcguire hdfs       8264 2016-05-11 16:38 /test_data/test_tbl/dt=11-05-2016/part-00000_copy_2
-rwxr-xr-x   3 cmcguire hdfs       7068 2016-05-11 16:38 /test_data/test_tbl/dt=11-05-2016/part-00000_copy_3
-rwxr-xr-x   3 cmcguire hdfs       5157 2016-05-11 16:39 /test_data/test_tbl/dt=11-05-2016/part-00000_copy_4
-rwxr-xr-x   3 cmcguire hdfs      10684 2016-05-11 16:39 /test_data/test_tbl/dt=11-05-2016/part-00000_copy_5
-rwxr-xr-x   3 cmcguire hdfs       4796 2016-05-11 16:40 /test_data/test_tbl/dt=11-05-2016/part-00000_copy_6

Re: Hive compactions on External table

chrismcg89 — Thu, 12 May 2016 01:56:03 GMT

@mbalakrishnan

Thanks, yes those properties are set, I believe its something to do with how the data is getting written to Hive via Spark Streaming

Re: Hive compactions on External table

ewalk — Thu, 12 May 2016 02:09:02 GMT

@Chris McGuire, I'm not sure you're using the Hive Streaming API, then. I'm not sure how Spark Streaming is setup to write out to hive, so it could be behaving correctly.

Re: Hive compactions on External table

ewalk — Thu, 12 May 2016 02:09:56 GMT

@mbalakrishnan, do you think it might be missing it because they are originating from spark, not map, mapred or tez?

Re: Hive compactions on External table

quadoss — Thu, 12 May 2016 02:19:20 GMT

@Chris McGuire, @Eric Walk,

Yes, that could well be the reason. There are properties for hive to merge spark file. The property is called hive.merge.sparkfiles by default this is false. You may want to enable it and also look at this wiki for hive-spark configuration:

https://cwiki.apache.org/confluence/display/Hive/Hive+on+Spark%3A+Getting+Started

Re: Hive compactions on External table

chrismcg89 — Thu, 12 May 2016 02:25:03 GMT

Thanks, I will make sure the Spark version of the property is set

Thanks for the help, I wonder if instead of rdd.toDF().saveAsTable I should be writing insert statements this might force the delta files to be created.

Re: Hive compactions on External table

ewalk — Thu, 12 May 2016 02:29:41 GMT

@Chris McGuire, that is probably the case, I'm not very familiar with the way spark is configured. I do know that, generally speaking, unless you explicitly say insert or use hive streaming you don't have deltas and don't need to worry about compaction. The partition append merging is a whole different story...

Re: Hive compactions on External table

chrismcg89 — Thu, 12 May 2016 04:59:09 GMT

Thanks @mbalakrishnan, Im currently running Spark Streaming job locally which is writing to the Hive deployed on my cluster. I have added the hive.merge.sparkfiles property. Will this work on files written with the saveAsTable command ?

Re: Hive compactions on External table

quadoss — Thu, 12 May 2016 06:06:09 GMT

@Chris McGuire I'm not sure whether this would work on saveAsTable command since I have very limited to no knowledge on spark. I'm hoping that this property should work for the spark streaming job as well.

Re: Hive compactions on External table

ekoifman — Fri, 13 May 2016 01:24:50 GMT

Hive Acid tables are not integrated with Spark. To write to an Acid table in a streaming fashion you could use https://cwiki.apache.org/confluence/display/Hive/Hive+Transactions#HiveTransactions-StreamingAPIs

(hdfs dfs -ls -R output shows the table to not be in expected format for Acid table. You can check metastore log for errors regarding compaction, but I would not expect it to work)

Re: Hive compactions on External table

gaurang_n_shah — Fri, 07 Sep 2018 22:08:48 GMT

Compaction works only on transactional table, and to make any table transactional it should meet following properties.

Should be ORC Table
Should be bucketed
Should be managed table.

Due you see the last point, you can't run compaction on non transactional table, if you do it from hive you will definitely get error, not sure from spark.