0

Can we use Synapse serverless pool (Built-in) to query a delta file's previous version?

I am keen to a SQL statement similar to what we do in Databricks:

select * from delta.`/my_dir` version as of 2

Does the OPENROWSET support support a "version selection" option?

If not possible, does registering the delta table to an external managed table helps?

Alex Ott
  • 80,552
  • 8
  • 87
  • 132
QPeiran
  • 1,108
  • 1
  • 8
  • 18

1 Answers1

2

When I try to query delta table in serverless sql pool in synapse using below code:

select * from delta.original version  as  of  0

I got below output:

enter image description here

As per this

Serverless SQL pools don't support time travel queries.

AFAIK with SQL commands are not supported to time travel with Delta Lake. But you can use spark pool loading the data into a dataframe with PySpark. I used below code to load data to data frame:

df = spark.read.format("delta").option("versionAsOf",0)

.load("<file location>")

enter image description here

I displayed the data frame using below code:

df.show()

enter image description here

You can try in this way to query with delta table.

Bhavani
  • 1,725
  • 1
  • 3
  • 6