Azure Databricks写入Spark DataFrame过慢问题及配置疑问咨询

Hey there! Let's break down your questions and walk through why that write operation is taking way longer than it should, plus how to fix it.

1. Is the 4-5 hour write speed expected behavior? #

Absolutely not. For a 120 million-row, 3-column Spark DataFrame on a 9-node cluster with 56GB memory per node, you should expect the write to finish in 30-60 minutes (depending on storage type and configuration)—not hours. That kind of delay means there's clear room to optimize your setup or write logic.

2. Can you manually tune Spark configurations instead of relying on auto-tuning? #

Yes, you absolutely can! Azure Databricks doesn't lock you into auto-tuning. You have full control over Spark configurations at two levels:

• Cluster-level: Set configs when creating/editing your cluster (under the "Advanced Options" > "Spark" tab).
• Session-level: Override configs in your notebook using spark.conf.set("<config-key>", "<value>") for that specific session.

Auto-tuning is great for getting started, but manual tweaks are often necessary for large-scale workloads like yours.

Key Optimizations to Speed Up Your Write #

Here are actionable steps to cut down that write time:

1. Fix Partitioning & Parallelism

• Repartition to match cluster capacity: Calculate your total available cores (e.g., 9 nodes × 8 cores/node = 72 cores). Set your DataFrame's partition count to 2-3× that number (144-216 partitions). This ensures each executor has a manageable chunk of data (around 50-100MB per partition, ideal for Parquet/Delta writes). Example:

  df = df.repartition(180)  # Adjust based on your cluster's actual core count
  

• Avoid over-partitioning with partitionBy: If you're using partitionBy, make sure the partition column doesn't have thousands of unique values (this creates too many small files/directories, killing performance). Only use partitionBy if you actually need to query by that column later.

2. Tune Spark Write Configs

Try adding these configs to your session or cluster:

• spark.sql.shuffle.partitions: Set to match your total core count (e.g., 72) to avoid unnecessary shuffle overhead.
• spark.sql.files.maxRecordsPerFile: Limit each output file to ~1 million rows to balance file size and parallelism:

  spark.conf.set("spark.sql.files.maxRecordsPerFile", "1000000")
  

• For Delta Tables: Enable auto-optimization to automatically merge small files and optimize partitioning:

  spark.conf.set("spark.databricks.delta.optimizeWrite.enabled", "true")
  spark.conf.set("spark.databricks.delta.autoCompact.enabled", "true")
  

• Compression: Stick to snappy (default for Parquet/Delta) for the best balance of speed and compression ratio.

3. Check Storage Performance

• Ensure your target storage (ADLS Gen2/Blob Storage) is using the Premium tier—it offers higher IOPS and throughput than Standard, which is critical for large writes.
• Avoid writing to remote storage with high latency; use a storage account in the same region as your Databricks workspace.

4. Validate Preprocessing Output

• Before writing, check if your preprocessed DataFrame has a lot of tiny partitions (from previous transformations). Use df.rdd.getNumPartitions() to confirm—if it's way higher than your target repartition count, merge them first.

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