Not able to get metadata information of the Delta Lake table using Spark

Question

I am trying to get metadata information of the Delta Lake table created using DataFrame. Information on the version, timestamp.

Tried: spark.sql("describe deltaSample").show(10,false) — this is not giving information related to version and timestamp:

I want to know how many versions exist with timeStamp information

+--------+---------+-------+
|col_name|data_type|comment|
+--------+---------+-------+
|_c0     |string   |null   |
|_c1     |string   |null   |
+--------+---------+-------+

Below is the code : // download delta in spark-shell

spark2-shell --packages io.delta:delta-core_2.11:0.2.0
val data = spark.read.csv("/xyz/deltaLake/deltaLakeSample.csv")

// save data frame

data.write.format("delta").save("/xyz/deltaLake/deltaSample")

// create delta lake table

spark.sql("create table deltaSample using delta location '/xyz/deltaLake/deltaSample'")
val updatedInfo = data.withColumn("_c1",when(col("_c1").equalTo("right"), "updated").otherwise(col("_c1")) )

// update delta lake table

updatedInfo.write.format("delta").mode("overwrite").save("/xyz/deltaLake/deltaSample")
spark.read.format("delta").option("versionAsOf", 0).load("/xyz/deltaLake/deltaSample/").show(10,false)
+---+-----+
|_c0|_c1  |
+---+-----+
|rt |right|
|lt |left |
|bk |back |
|frt|front|

+---+-----+

spark.read.format("delta").option("versionAsOf", 1).load("/xyz/deltaLake/deltaSample/").show(10,false)
+---+-------+
|_c0|_c1    |
+---+-------+
|rt |updated|
|lt |left   |
|bk |back   |
|frt|front  |
+---+-------+

// get metadata of the table created. with version, timestamp info.

spark.sql("describe history deltaSample") -- not working

org.apache.spark.sql.AnalysisException: Table or view was not found: history;
  at org.apache.spark.sql.catalyst.analysis.package$AnalysisErrorAt.failAnalysis(package.scala:47)
  at org.apache.spark.sql.catalyst.analysis.Analyzer$ResolveRelations$.org$apache$spark$sql$catalyst$analysis$Analyzer$ResolveRelations$$lookupTableFromCatalog(Analyzer.scala:733)
  at org.apache.spark.sql.catalyst.analysis.Analyzer$ResolveRelations$.resolveRelation(Analyzer.scala:685)

expected table display ( eg: added column version , timestamp) :

+--------+---------+-------+-------+------------
|_c0     |_c1      |Version|timestamp           |
+--------+---------+-------+-------+------------
|rt      |right    |0      |2019-07-22 00:24:00|
|lt      |left     |0      |2019-07-22 00:24:00|
|rt      |updated  |1      |2019-08-22 00:25:60|
|lt      |left     |1      |2019-08-22 00:25:60|
+--------+---------+-------+------------------+

val a = spark.read.format("delta").option("versionAsOf", 1).load("/xyz/deltaLake/deltaSample/") a.printSchema should print the schema in form of a table. Is this what you are looking for? — Rakshith
No. printSchema only give info about (_c0 : string , _c1:string). It doesn't talk about version and timestamp columns. — poornima
I want to know how many versions exist with timeStamp information — poornima

Denny Lee Denny Lee · Accepted Answer · 2019-08-02T14:25:28

The ability to view the history of a Delta Lake table was included the recently announced 0.3.0 per Announcing the Delta Lake 0.3.0 Release.

Currently you can do this using the Scala API; have the ability to do this in SQL is currently on the roadmap. For a Scala API example, with 0.3.0,

import io.delta.tables._

val deltaTable = DeltaTable.forPath(spark, pathToTable)

val fullHistoryDF = deltaTable.history()    // get the full history of the table.

val lastOperationDF = deltaTable.history(1) // get the last operation.

with the result of the fullHistoryDF being similar to:

+-------+-------------------+------+--------+---------+--------------------+----+--------+---------+-----------+--------------+-------------+
|version|          timestamp|userId|userName|operation| operationParameters| job|notebook|clusterId|readVersion|isolationLevel|isBlindAppend|
+-------+-------------------+------+--------+---------+--------------------+----+--------+---------+-----------+--------------+-------------+
|      5|2019-07-29 14:07:47|  null|    null|   DELETE|[predicate -> ["(...|null|    null|     null|          4|          null|        false|
|      4|2019-07-29 14:07:41|  null|    null|   UPDATE|[predicate -> (id...|null|    null|     null|          3|          null|        false|
|      3|2019-07-29 14:07:29|  null|    null|   DELETE|[predicate -> ["(...|null|    null|     null|          2|          null|        false|
|      2|2019-07-29 14:06:56|  null|    null|   UPDATE|[predicate -> (id...|null|    null|     null|          1|          null|        false|
|      1|2019-07-29 14:04:31|  null|    null|   DELETE|[predicate -> ["(...|null|    null|     null|          0|          null|        false|
|      0|2019-07-29 14:01:40|  null|    null|    WRITE|[mode -> ErrorIfE...|null|    null|     null|       null|          null|         true|
+-------+-------------------+------+--------+---------+--------------------+----+--------+---------+-----------+--------------+-------------+

Not able to get metadata information of the Delta Lake table using Spark

1 Answers