Best practices for partition tables in Impala 3.2.0

Question

Hello,We need to partition 5 hive tables with more than 100 million records each. Tables have multiple columns to partition with integer and string data allowing less than 1000 partitions per column. Could we combine several of these fields to partition the tables? would there be a restriction on the maximum number of combined partitions? Should they have a specific order according to the number of partitions in each table? for example, first partition by year (10 partitions) , then by country (50 partitions) and then by city name (500 partitions) ? Must the combination of partitions be less than a particular number?Best Regards,H.M

mszurap · Accepted Answer

Hi @wcg_hdp_manager ,Please review the Impala partitioning best practices guide:https://docs.cloudera.com/best-practices/latest/impala-partitioning/topics/bp-impala-partitioning-considerations.htmlAnd CDP 7.1.8 Impala partitioning guide:https://docs.cloudera.com/cdp-private-cloud-base/7.1.8/impala-reference/topics/impala-partition.html Do not partition your table if you do not have a good reason to do so. The number of records (100m) itself is not necessarily is a reason. You need to know what kind of queries you will have on your table (do you always know in WHERE clause one or more columns so Impala can take advantage of partition pruning? if not, then anyway the whole dataset might be scanned), how you ingest those (do you load new partitions each day? or some other factors?).Creating too many partitions will likely implicitly create too many small files instead of less but bigger files. Processing data which are in more datafiles is less efficient and you can put a stress on the HDFS NameNode (which needs to keep track of the many datafiles) if that is going to be a general trend.Hope this helps, Miklos

DianaTorres · Answer

@wcg_hdp_manager Welcome to the Cloudera Community!

To help you get the best possible solution, I have tagged our Hive expert @mszurap who may be able to assist you further.

Please keep us updated on your post, and we hope you find a satisfactory solution to your query.

Sign up

Please use your Cloudera account.

Login to the community

Please use your Cloudera account.