How to access the latest version of Delta table as Integer?

Question

Can someone let me know to convert the following from a Dataframe to an InterType()

df = DeltaTable.forPath(spark, '/mnt/lake/BASE/SQLClassification/cdcTest/dbo/cdcmergetest/1').history().select(max(col("version")).alias("version"))

I have tried the following

result = df.collect()[0]

result2  = df.withColumn("version",df.version.cast('integer'))

But with no luck

Any thoughts?

score 4 · Answer 1 · answered Mar 20 '23 at 14:41

4

from delta.tables import DeltaTable
import pyspark.sql.functions

dt = DeltaTable.forPath(spark, '/mnt/lake/BASE/SQLClassification/cdcTest/dbo/cdcmergetest/1')
latest_version = int(dt.history().select(max(col("version"))).collect()[0][0])

answered Mar 20 '23 at 14:41

Jacek Laskowski

72,696
27
242
420

1

Hi @Jacek, thanks for reaching. This worked perfectly. P.S. I love your book. – Patterson Mar 20 '23 at 18:58

score 1 · Accepted Answer · answered Jul 03 '23 at 13:53

1

If table already exist:

dftemp = spark.sql("DESCRIBE history table1 limit 1").collect()
verno = int(dftemp[0][0])

answered Jul 03 '23 at 13:53

Laki

76
1
1
9

Patterson · Answer 3 · 2023-03-18T19:58:05.360

0

I figured it out.

I needed to add collect() to the dataframe.

df = DeltaTable.forPath(spark, '/mnt/lake/BASE/SQLClassification/cdcTest/dbo/cdcmergetest/1').history().select(max(col("version")).alias("version")).collect()

And then simply strip out the 0

df = df[0][0]

edited Mar 18 '23 at 19:58

answered Mar 18 '23 at 18:59

Patterson

1,927
1
19
56

OK, I thought my solution worked but it was a false positive. I still need help – Patterson Mar 18 '23 at 19:10

How to access the latest version of Delta table as Integer?

3 Answers3

Linked