How to get a snapshot of a streaming delta table as a static table?

[Mado]

Hi,

Assume that I have a streaming delta table. Is there any way to get snapshot of the streaming table as a static table?

Reason is that I need to join this streaming table with a static table by:

output = output.join(country_information, ["Country"], "left")

"Output" is a static table and "country_information" is the streaming table.

But, I get the error:

Append mode error: LeftOuter join with a streaming DataFrame/Dataset on the right and a static DataFrame/Dataset on the left is not supported;

Also, I cannot change the the above code into:

country_information = country_information.join(output, ["Country"], "left")

Because the logic doesn't work.

Thanks.

[Kaniz_Fatma · Accepted solution]

Hi @Mohammad Saber​, Yes, you can try this approach

Create the snapshot with a timestamp

snapshot_time = "2022-10-01 00:00:00"
 
spark.sql(f"CREATE TABLE snapshot_table_at_time AS SELECT * FROM streaming_table VERSION AS OF '{snapshot_time}'")

Then, you can save it as a data frame.

snapshot_df = spark.read.format("delta").option("timestampAsOf", snapshot_time).load("streaming_table").toDF()
 

Thank you for your question. Please don't forget to click on the "Select As Best" button whenever the information provided helps resolve your question.