Spark Parquet分区键选择指南：解决小文件与查询优化问题

Should I partition my user dataset by ID? #

Short answer: Probably not—unless you only have a few hundred unique users (not thousands or millions). Here’s why:

• Partitioning by a high-cardinality key like user ID will create one partition per user, which almost always leads to a flood of tiny Parquet files. Even if a user only has a handful of records, Spark will generate at least one file for their partition. This kills performance across the board:
  • Metadata bloat: Your metastore (like Hive) has to track thousands/millions of partitions, slowing down query planning to a crawl.
  • Inefficient reads: When you run a query that needs to scan multiple users, Spark has to open and read hundreds of small files—way less efficient than reading a few large ones.
• Better alternatives to try:
  • Bucket by ID: Use bucketBy(100, "user_id") (adjust the number of buckets based on your data size) when writing the dataset. This groups user IDs into a fixed number of buckets (e.g., 100), so you get a manageable number of files. Queries filtering by user ID can still efficiently scan only the relevant bucket(s) without the partition metadata overhead.
  • Combine with a coarser partition key: If your data has a time component (like event date), partition by date first, then bucket by user_id. This gives you time-based partition pruning for date-filtered queries, plus efficient ID-based filtering within each date partition.

Is partitioning by two keys meaningful if queries use either alone? #

It depends on the cardinality of the keys and your actual query patterns—here’s how to break it down:

• If one key is low-cardinality and the other is medium/high: Composite partitioning works great. For example, if you partition by region (10 unique values) and month (12 unique values), you end up with 120 total partitions—totally manageable. Queries filtering by region will skip all other regions, and queries filtering by month will scan all regions but only the target month’s partitions (still way better than scanning everything).
• Order matters: Always put the most frequently queried key first in the composite partition. If you query by region 70% of the time and month 30%, use (region, month) instead of (month, region). This way, region-only queries get maximum partition pruning.
• Avoid two high-cardinality keys: If both keys have thousands of unique values (like user_id and product_id), composite partitioning will create millions of partitions—this is way worse than using a single high-cardinality key. Stick to bucketing for one or both instead.
• If queries use either key equally: Consider partitioning by one key and bucketing the other. For example, partition by date and bucket by category—this handles both date-only and category-only queries efficiently.

Quick Checklist for Choosing Partition Keys #

• Cardinality first: Steer clear of high-cardinality keys (millions of unique values) as partition keys. Aim for low to medium cardinality (hundreds to thousands of partitions max).
• Align with query patterns: Prioritize keys that show up in WHERE clauses most often—partition pruning is the biggest performance win here.
• Target ideal file sizes: Each partition should produce files between 128MB-256MB (Spark’s default block size sweet spot). Adjust your partition granularity to hit this range.

内容的提问来源于stack exchange，提问作者Jiew Meng