delta lake in databricks - a consistent "view" of just the last half hour of a stream

Question

I have consisently updated table from spark structured streaming (kafka source) Written like this (in eachBatch)


parsedDf \
        .select("somefield", "anotherField",'partition', 'offset') \
        .write \
        .format("delta") \
        .mode("append") \
        .option("mergeSchema", "true") \
        .save(f"/mnt/defaultDatalake/{append_table_name}")

I need to keep a fast view on this table for "items inserted in the last half hour" How can this be achieved? I can have a readStream from this table, but what I'm missing is how keep just the "tail" of the stream there

Databricks 7.5 spark 3.

user_6933993 · Answer

Given that Delta lake does not have materalized views and that Delta Lake time-travel is not relevant as you want the most current data:

You can load the data and include a key that does not need to be looked up whilst inserting.
Pre-populate a time dimension for joining with your data. See it as a dimension with a grain of a minute.
Join the data with this dimension, relying on dynamic file pruning. Thus you need to query per minute every 30 minutes with rolling window and set those values in the query.
See https://databricks.com/blog/2020/04/30/faster-sql-queries-on-delta-lake-with-dynamic-file-pruning.html#:~:text=Dynamic%20File%20Pruning%20(DFP)%2C%20a%20new%20feature%20now%20enabled,queries%20on%20non%2Dpartitioned%20tables.

delta lake in databricks - a consistent "view" of just the last half hour of a stream

1 Answers