what is the best practice on where to store hive external tables in HDFS?

[#1]

Hi,

I was just wondering what is the best practice on where to store hive external tables in HDFS?

Currently they are being stored in the /tmp directory in HDFS but sometimes someone accidentally deletes the files under /tmp and right now I am trying to add permissions to ranger to prevent this from happening.

Thanks,

[#2]

Hello @ryu 

There is no such path as best path but obviously not /tmp location.

You can create some path under /user/external_tables and further create the tables here. Again it totally depends upon you how you are designing and your use case.

[#3]

Thanks @tusharkathpal  for the response.

Can you please let me know what are some of the use cases to storing the external tables at a certain location?

Thanks,

[#4 · Accepted solution]

Well @ryu, My understanding is when you are storing things on HDFS and that too things related to hive, it is best to use managed table considering in mind that CDP is now coming up with compaction features where in small file issue would automatically get addressed.

Compaction will not happen on external tables.

one would prefer to choose external tables if the data is stored outside HDFS like S3. This is my understanding, but again it could vary on customer to customer based on their use cases.

[#5]

To see more on Hive Managed and External tables, please see our public documentation for CDP Hive.