SQL Server How do I use latest version in table_changes function for a delta table?

nvbavucw 于 2023-06-21 发布在其他

关注(0)|答案(1)|浏览(134)

I have a query which gives me the latest version of a delta table in the following format

(Query 1):

%sql
SELECT VERSION FROM (DESCRIBE HISTORY dbo.customers LIMIT 1)

This gives me output like this:

I have a query for table_changes which I'm using it in this way

(Query 2):

%sql
select * from table_changes('dbo.customers', 3)

It gives me the records of whatever changes were there in version 3 which is the latest version.

Now, when I am trying to do it in 1 query instead of 2, I attempted the following

(Query 3):

%sql
select * from table_changes('dbo.customers', SELECT VERSION FROM (DESCRIBE HISTORY dbo.customers LIMIT 1))

It gives me a syntax error.

I understand that (Query 1) is returning a table but I just want to use the value of the first row in the second argument of the table_changes function.

How do I use it or is there any better way to get the result in a single query?

sql-server

来源：https://stackoverflow.com/questions/76415836/how-do-i-use-latest-version-in-table-changes-function-for-a-delta-table

1条答案

按热度按时间

30byixjq1#

You can use widget in databricks to use the value in table_changes as below.

%python
import json
result = spark.sql("SELECT  VERSION  FROM (DESCRIBE  HISTORY  delta.`/sampDelta/names/` LIMIT  1)").toJSON()
j_obj = json.loads(result.collect()[0])
print(j_obj['VERSION'])
dbutils.widgets.text('vers', str(j_obj['VERSION']))

Then use that value in function as Integer.

%sql
select  *  from  table_changes('delta.`/sampDelta/names/`', int('${vers}'))

赞(0）回复(0）举报 2023-06-21

我来回答

SQL Server How do I use latest version in table_changes function for a delta table?

1条答案

相关问题

热门标签

最新问答