cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Exporting delta table to one CSV

561064
New Contributor II

Process to export a delta table is taking ~2hrs.

Delta table has 66 partitions with total size of ~6gb, 4million rows and 270 columns.

Used below command

df.coalesce(1).write.csv("path")

what are my options to reduce the time?

5 REPLIES 5

Dribka
New Contributor III

A very interesting task in front of you.... let me know how you solve it!

Kaniz_Fatma
Community Manager
Community Manager

Hi @561064, Exporting a Delta table can indeed be time-consuming, especially when dealing with large datasets.

 

Letโ€™s explore some strategies to optimize the export process and reduce the time:

 

Partitioning:

  • Choose an appropriate partition column for your Delta table. The most commonly used partition column is date.
  • Avoid partitioning by columns with very high cardinality (e.g., user IDs with millions of distinct values).
  • Ensure that each partition contains at least 1 GB of data.
  • You can repartition the table to a smaller number of files using the repartition method. For example:df.repartition(numFiles).write.csv("path")
  • If you want to repartition just one partition based on a predicate, use where and replaceWhere.

Compaction:

  • Over time, a Delta table accumulates a large number of files due to continuous writes. Compaction helps consolidate these files into a smaller number of larger files.
  • Use the OPTIMIZE command to compact the table:OPTIMIZE '/path/to/delta/table'
  • You can also specify an optional partition predicate using WHERE if you want to optimize only a subset of the data.

V-Order Optimization:

  • V-Order is a write-time optimization for the Parquet file format.
  • It enables lightning-fast reads under Microsoft Fabric compute engines (e.g., Power BI, SQL, Spark).
  • V-Order works by applying special sorting, row group distribution, dictionary encoding, and compression to Parquet files.
  • To enable or disable V-Order:
    • In Apache Sparkโ„ข session:SET spark.sql.parquet.vorder.enabled=TRUE  -- Enable SET spark.sql.parquet.vorder.enabled=FALSE -- Disable
    • All Parquet writes will be made with V-Order enabled when set at the session level.

Delta Table Properties:

  • Use table properties to fine-tune performance.
  • Set properties like spark.databricks.delta.optimizeWrite.enabled and spark.databricks.delta.retentionDurationCheck.enabled.

Remember that these optimizations can significantly improve export times, but the actual impact may vary based on your specific use case.

 

Experiment with different approaches to find the best combination for your Delta table export process. ๐Ÿš€

561064
New Contributor II

Hi Kainz,

None of the options I tried helped as the challenge is not reading but writing it to a one CSV file. df.repartition(numFiles).write.csv("path") has consumed the same amount of time as 'df.coalesce(1).write.csv("path")' in my case.

any other options I can explore?

 

 

Kaniz_Fatma
Community Manager
Community Manager

Thank you for posting your question in our community! We are happy to assist you.

To help us provide you with the most accurate information, could you please take a moment to review the responses and select the one that best answers your question?

This will also help other community members who may have similar questions in the future. Thank you for your participation and let us know if you need any further assistance! 
 

Kaniz_Fatma
Community Manager
Community Manager

Thank you for posting your question in our community! We are happy to assist you.

To help us provide you with the most accurate information, could you please take a moment to review the responses and select the one that best answers your question?

This will also help other community members who may have similar questions in the future. Thank you for your participation and let us know if you need any further assistance! 
 

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโ€™t want to miss the chance to attend and share knowledge.

If there isnโ€™t a group near you, start one and help create a community that brings people together.

Request a New Group