Spark DataFrame能否按列分区后按另一列聚类？附存储实操疑问

Great question—dealing with large datasets (millions of rows) in Spark often requires careful consideration of partitioning and file layout to balance performance and storage efficiency. Let’s break this down step by step.

Can we partition by month and cluster by cust_id to generate 50 files per partition? #

Absolutely! You can combine directory-based partitioning (by month) with clustering (grouping same cust_id records into the same files) to get exactly this outcome. Here are two reliable approaches:

Option 1: Use repartition + partitionBy #

This method gives you explicit control over the number of files per month partition, while ensuring same cust_id records end up in the same file:

df.repartition(col("month"), 50, col("cust_id"))
  .write
  .partitionBy("month")
  .saveAsTable("tbl")

• How it works: The repartition(col("month"), 50, col("cust_id")) call first groups data by month, then splits each month’s data into 50 buckets using a hash of cust_id. This ensures same cust_id records stay together.
• Storage outcome: Each month=YYYYMM/ directory will have exactly 50 files, with all records for a given cust_id within that month stored in one file.

Option 2: Use Hive Bucketing + Partitioning #

If you want to leverage Hive’s native optimized storage for clustered data, use bucketBy:

df.write
  .partitionBy("month")
  .bucketBy(50, "cust_id")
  .saveAsTable("tbl")

• How it works: This creates a Hive partitioned-bucketed table. Each month directory contains 50 bucket files, where records are hashed by cust_id into these buckets.
• Bonus: Hive can use bucketed tables to speed up queries (e.g., avoiding full shuffles for joins on cust_id). Just note this requires a managed Hive table (the default for saveAsTable).

Breaking Down the Three Storage Methods You Mentioned #

You’re right about the similarities between the first and third methods—but let’s clarify all three, including whether repartition(100) gets "canceled" by partitionBy:

1. df.write.partitionBy("month").saveAsTable("tbl") #

• Spark Logic: Spark will shuffle data to group records by month (if the original DataFrame isn’t already partitioned by month). The number of files per month directory depends on Spark’s default task parallelism (usually tied to cluster cores/memory).
• Storage Form: Creates a Hive partitioned table, with separate directories (month=202401/, month=202402/, etc.) for each month. Each directory has multiple files based on task count.

2. df.repartition(100).write.partitionBy("month").saveAsTable("tbl") #

• Does repartition(100) get canceled? No! Here’s what happens:
  1. First, repartition(100) randomly shuffles the entire dataset into 100 equal-ish partitions.
  2. Then, partitionBy("month") triggers a second shuffle to group records from those 100 partitions by month.
• Outcome: Each month directory will have up to 100 files (one per initial random partition that contained data for that month). This is useful if you want to limit the maximum file size per partition (to avoid huge files), but adds an extra shuffle step—only use it if you’re dealing with severe data skew.

3. df.repartition("month").write.saveAsTable("tbl") #

• Spark Logic: Similar to method 1—Spark shuffles data to group all month records into a single RDD partition per month.
• Storage Form: Creates a non-partitioned Hive table. All files live in the table’s root directory, with one file per month (since each RDD partition becomes one file). No separate directories for months—you’ll have to parse filenames or query the data to filter by month.

Your observation about methods 1 and 3 is spot-on: they both shuffle by month, but differ entirely in how Hive stores the data (directory partitions vs. single files per month).

内容的提问来源于stack exchange，提问作者Subramaniam Ramasubramanian