A Hive dynamic-partition INSERT produced thousands of tiny files — what caused it and what do you tune?

## Why the files multiply Dynamic partitioning lets the partition value come from the data rather than from the statement: ```sql SET hive.exec.dynamic.partition.mode=nonstrict; INSERT OVERWRITE TABLE sales PARTITION (dt, country) SELECT id, amount, dt, country FROM sales_raw; ``` Each writing task processes whatever rows it was given. When a task encounters a row for `(dt='2026-01-01', country='DE')` it opens a writer for that partition directory and keeps it open. If rows for a partition are scattered across all tasks, then every task opens a writer in that partition. The output count is therefore bounded by **tasks × distinct partitions**, and with a shuffle-free map-only insert it is bounded by input splits × partitions, which can be worse. That is the whole mechanism, and stating it plainly is most of the answer. ## Fix 1 — route each partition to one writer Add a `DISTRIBUTE BY` on the partition columns so the shuffle sends all rows of a partition to a single reducer: ```sql INSERT OVERWRITE TABLE sales PARTITION (dt, country) SELECT id, amount, dt, country FROM sales_raw DISTRIBUTE BY dt, country; ``` Now each partition has one writer and one output file. Two caveats worth volunteering: - **This creates skew by construction.** A partition holding most of the data becomes one reducer's job and one long-running task. For a badly-skewed partition column, distributing by `dt, country, something_random_bucketed` gives a controlled number of files per partition instead of one, trading a few files for parallelism. - **Do not reach for `ORDER BY` instead.** In Hive, `ORDER BY` forces a *single* reducer for the entire result to produce a global ordering. It certainly reduces the file count, by destroying all parallelism. `SORT BY` orders within a reducer; `CLUSTER BY x` is shorthand for `DISTRIBUTE BY x SORT BY x`. ## Fix 2 — let Hive merge the output Hive can append a merge job that rewrites small files into larger ones: - `hive.merge.mapfiles` — merge the output of map-only jobs (enabled by default). - `hive.merge.mapredfiles` — merge the output of map-reduce jobs (off by default, and this is the one people forget). - `hive.merge.tezfiles` — the Tez equivalent, off by default. - `hive.merge.smallfiles.avgsize` — if the job's average output file is smaller than this, trigger the merge step. - `hive.merge.size.per.task` — the target size each merge task aims to produce. Merging costs an extra pass over the freshly-written data, which is usually a fine trade against thousands of downstream task launches. It is a cleanup, not a substitute for fix 1: it still has to read every tiny file once. ## Fix 3 — question the partition scheme A partition should be large enough to be worth a directory. If partitioning by `dt, country, device_type` yields 30 MB per partition, the layout is the problem. Coarsen it — partition by day only, and let columnar min/max indexes and predicate pushdown inside ORC or Parquet handle the finer filtering. Bucketing is the tool for high-cardinality columns you join on; partitioning is for a handful of coarse, frequently-filtered dimensions. Hive also enforces guardrails that surface this: `hive.exec.max.dynamic.partitions` caps the total number of dynamic partitions a statement may create, and `hive.exec.max.dynamic.partitions.pernode` caps how many a single node may create (100 by default). Hitting the per-node limit produces a fatal error about creating too many dynamic partitions. Raising those limits is occasionally right and much more often a signal that the partition column is too fine-grained — the interviewer is usually checking whether you raise the limit reflexively or ask why it was hit. ## Why anyone cares Three costs compound. Each file is an object in the NameNode's in-memory namespace, so millions of small files pressure the master's heap. Each file (or split) becomes a task, so a query over 60,000 files spends its life launching containers rather than reading data. And columnar formats lose their advantage: an ORC file smaller than a stripe cannot amortize its footer, indexes and dictionary, so compression and pushdown both degrade. ## The complete answer Name the mechanism (tasks × partitions), fix the routing with `DISTRIBUTE BY`, enable merging as cleanup, then challenge the partition granularity — and mention that you would check the resulting average file size afterwards rather than assume the tuning worked.