For us it didn't appear to be any particular table having too many files or partitions, but rather the catalog tracking too many of them overall. So definitely compact the most fragmented ones to start with, but the goal is to lower total files.
We use impala itself, doing an insert overwrite in place. This does result in a short outage as queries will fail for a few seconds on that table (if reading the same partitions being overwritten), so we schedule this late at night.
For a typical table partitioned by event_date_yearmonth, and account_id_mod (i.e. account_id % 10), we typically will compact the current month (which has data coming in throughout the day so many new small files) with:
insert overwrite sometable partition(event_date_yearmonth, and account_id_mod)
select * from sometable where event_date_yearmonth = '201712'
This will result in 1 file in each partition (or more if partition's data bigger than block size), and all account_id_mod partitions for event_date_yearmonth 201712 will be rewritten, while other months will not be touched.
Notice I didn't specify partition values in the partition clause, so it's fully dynamic, and therefore the * works ( * returns all the schema columns AND the partition columns).
Caution though: having 1 file per partition will decrease parallelism and query performance if common use case is to read a single partition at a time. If so, you can set PARQUET_FILE_SIZE before the insert, to create files in each partition smaller than the defaul 128m.
