You have a streaming job that writes to a Delta table. You want to ensure that small files are compacted regularly to optimize query performance. How can you achieve this within the streaming process, without requiring separate batch jobs?

You can achieve small file compaction within a streaming process writing to a Delta table using the optimizeWrite and autoOptimize.optimizeWrite options. These features are designed to handle small files generated by streaming workloads automatically.

1. Using optimizeWrite (For Spark Structured Streaming):

When writing to a Delta table using Spark Structured Streaming, you can use the optimizeWrite option:

(streaming_df
 .writeStream
 .format("delta")
 .option("checkpointLocation", "path/to/checkpoint")
 .option("optimizeWrite", "true")
 .trigger(processingTime="1 minute") # Example trigger
 .start("path/to/delta/table")
)

2. Using autoOptimize.optimizeWrite (Recommended for most cases):

This is the recommended approach as it's more automatic and often more efficient. You set this option at the table level, and it will automatically optimize writes for both batch and streaming operations.

ALTER TABLE my_delta_table SET TBLPROPERTIES (delta.autoOptimize.optimizeWrite = true);

Or when creating the table:

CREATE TABLE my_delta_table (...)
TBLPROPERTIES (delta.autoOptimize.optimizeWrite = true);

How these options work:

• optimizeWrite: This option restructures data within each micro-batch of the stream before writing it to the Delta table. This reduces the number of small files created by the streaming process. The effect is local to each micro-batch.
• autoOptimize.optimizeWrite: This option works in conjunction with autoOptimize.autoCompact. It automatically optimizes the layout of data files in the Delta table after each micro-batch is written. This is a background process that compacts small files into larger ones, significantly improving query performance.

Key Considerations:

• autoOptimize.autoCompact: For autoOptimize.optimizeWrite to be fully effective, you should also enable autoOptimize.autoCompact:

ALTER TABLE my_delta_table SET TBLPROPERTIES (delta.autoOptimize.autoCompact = true);

This triggers automatic compaction of small files after each write.

• Cost: While these features improve query performance, they do introduce some additional processing overhead. However, the performance gains from reduced small files generally outweigh the cost.
• Z-Ordering: If you have frequent queries that filter on specific columns, consider using Z-Ordering in conjunction with auto compaction for even better performance.
• No Manual Compaction Needed: With autoOptimize.optimizeWrite and autoOptimize.autoCompact enabled, you typically don't need to run manual compaction (using OPTIMIZE) for streaming workloads.

Choosing the right approach:

• For Spark Structured Streaming applications, using the optimizeWrite option is sufficient.
• For most cases, setting the table properties delta.autoOptimize.optimizeWrite = true and delta.autoOptimize.autoCompact = true is the recommended approach as it works for both streaming and batch writes.

By using these features, you can efficiently manage small files generated by streaming jobs and maintain optimal query performance in your Delta tables.