To partition a PySpark DataFrame by multiple columns, use df.write.partitionBy("col1", "col2").format("parquet").save("path"). This creates a directory structure like path/col1=value1/col2=value2/, which can dramatically speed up queries that filter on those columns.
What Partitioning Does in PySpark
Partitioning is a way to organize data files on disk based on the values of one or more columns. When you write a DataFrame with partitionBy, Spark creates a separate directory for each unique combination of values in the specified columns. For example, if you partition by year and month, you get folders like year=2023/month=01, year=2023/month=02, and so on.
This layout is not just for aesthetics—it enables partition pruning. When a query filters on a partitioned column, Spark can skip entire directories that don’t match the filter, reading only the relevant data. This can reduce I/O and query time dramatically, especially on large datasets.
How to Use partitionBy with Multiple Columns
The syntax is straightforward. You pass the column names as separate arguments to partitionBy. Here’s a complete example:
from pyspark.sql import SparkSession
spark = SparkSession.builder.appName("partition_example").getOrCreate()
df = spark.read.csv("sales.csv", header=True)
df.write.partitionBy("year", "month").mode("overwrite").parquet("output/sales")
This writes the DataFrame to output/sales with subdirectories like year=2024/month=05. You can chain additional options like .option("compression", "snappy") before the save action.
When reading back, Spark automatically discovers the partitioning structure if you read the parent directory:
spark.read.parquet("output/sales")
You can also use partitionBy with other formats like JSON, CSV, or ORC, though Parquet is the most common for analytical workloads.
Choosing the Right Columns to Partition By
Not all columns are good candidates for partitioning. The key is to pick columns that:
- Have low cardinality—few distinct values relative to the total row count. For example,
countryoryearworks well;user_idwith millions of unique values does not. - Are frequently used in filters—if queries often filter by
date, partitioning by date helps. - Have a balanced distribution—avoid columns where one value dominates (e.g., 90% of rows have the same value), as that creates large uneven directories.
Order matters. The first column in partitionBy creates the top-level directories, and the second creates subdirectories. Put the column with the fewest distinct values first to keep the directory tree shallow. For instance, partitioning by year then month is better than month then year because year has fewer distinct values.
Common Problems and How to Avoid Them
One frequent issue is too many small files. If you partition by a high-cardinality column, you can end up with thousands of tiny files, which hurts performance due to metadata overhead. To mitigate this, use repartition or coalesce before writing, or set spark.sql.shuffle.partitions appropriately.
Another problem is partition explosion when using multiple columns with many distinct values. For example, partitioning by user_id and product_id will create a directory for every combination—likely millions of directories, which is impractical. Stick to low-cardinality columns.
Also, be careful with data types. Partition columns are stored as strings in the directory paths. If you partition by a date column, it will be cast to a string like 2024-05-01. This is fine, but when reading back, the column type becomes string, not date. You may need to cast it back in your queries.
Performance Considerations and Costs
Partitioning can significantly speed up queries, but it’s not free. Writing partitioned data can be slower because Spark must shuffle data to group rows by partition keys. The shuffle overhead is more noticeable with many partitions or high-cardinality columns.
Storage cost can also increase if you have many small files, as each file has a minimum block size overhead. On cloud storage like S3, listing many directories can add latency. However, the query-time savings usually outweigh these costs for analytical workloads.
A good rule of thumb is to keep the number of partitions manageable—typically in the hundreds to low thousands, not millions. You can check the number of output files by listing the output directory.
Comparing partitionBy with bucketBy and repartition
partitionBy controls directory layout on disk. repartition controls the number of in-memory partitions during a shuffle. They serve different purposes: repartition affects parallelism and file size, while partitionBy affects data organization for pruning.
bucketBy is another technique that creates a fixed number of files with hashed values, often used with bucketing for joins. Unlike partitioning, bucketing doesn’t create directories; it creates a fixed number of files per bucket. You can combine partitioning and bucketing, but that’s advanced and often overkill.
For most use cases, partitioning by a few low-cardinality columns is sufficient. Bucketing is more useful when you have high-cardinality columns and need to optimize joins.
Frequently Asked Questions
Can I partition by multiple columns in PySpark?
Yes, just pass multiple column names to partitionBy. The order determines the directory hierarchy: the first column creates the top-level folders, the second creates subfolders, and so on.
What is the difference between partitionBy and repartition?
partitionBy is used when writing data to disk to organize files by column values. repartition is used to change the number of in-memory partitions, which affects parallelism and file sizes during a shuffle. They are independent; you can repartition before writing and then partitionBy on write.
Does partitionBy work with CSV or JSON?
Yes, partitionBy works with any format that Spark can write, including CSV, JSON, Parquet, and ORC. However, for CSV and JSON, each partition directory will contain one or more files with the data, and the partition columns are removed from the data itself because they are represented by the directory structure.
How do I read a partitioned DataFrame back?
Simply read the parent directory with spark.read.parquet("output/sales"). Spark automatically discovers the partition columns and includes them as columns in the resulting DataFrame. You can also specify the base path if you’re reading a subset.
What happens if I partition by a column with null values?
Null values are handled by Spark and placed in a special directory named __HIVE_DEFAULT_PARTITION__. When reading back, nulls are restored correctly. However, be aware that having many nulls can create an uneven partition, so consider using a default value or dropping nulls if possible.
Can I change the partition columns after writing?
No, you cannot change the partitioning of an existing dataset without rewriting it. You would need to read the data, repartition by new columns, and write it out again. This is a common reason to plan your partitioning strategy carefully before writing large datasets.
Related
- Profit and Loss Excel Templates: How to Use Them
- Excel Template Sales Tracker: How to Build One
- Excel Template to Pay Off Debt? Yes, Here’s How
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Outlook Signature: Create and Set Up an Email Sign-offTo create an email signature in Outlook, open the signature settings for your version, make a new signature,…
- Google Sheets Indian Currency Format: Rupee GuideTo show amounts in Indian rupees in Google Sheets, select the cells and choose Format > Number >…
- How to Calculate Mass Balance in ChemistryTo calculate a chemical mass balance, define the system, then account for everything that enters, leaves, forms, is…
- Machine Checklist Format in Excel: A Practical TemplateA useful machine checklist in Excel puts one inspection item on each row and records the machine, inspection…
- Freeze Panes in Excel: How to Keep Rows and Columns VisibleTo insert a freeze pane in Excel for Microsoft 365 on Windows, select the cell immediately below the…
- How to Find a Range in Google Sheets: Select Cells FastTo find and select a range in Google Sheets, click the Name box to the left of the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.