How to retrieve a column value from DESCRIBE DETAIL <table_name>

user_9178725 0
Viewed 230

I would like to use the "Last modified" value from the description of my table in databricks. I know how to get all columns from the table by using "DESCRIBE DETAIL table_name", but I wish to simply get the last modified value since I need to use it in my WHERE comparison

databricks-sql databricks
2 Answers

You can retrieve the result of the SQL query as a list and get the content like this :

spark.sql("DESCRIBE DETAIL database_name.table_name").collect()[0]['lastModified']

>>> Out[7]: datetime.datetime(2022, 2, 11, 8, 16, 5)
user_31353320

The DESCRIBE DETAIL functionality returns a dataframe with 1 row, but isn't handled as a proper table in Spark SQL using databricks as of now, but you can do it via temp view as @axel-r pointed out:

df = spark.sql("""DESCRIBE DETAIL  database_name.table_name""")
df.createOrReplaceTempView("details")

%sql
SELECT lastModified FROM details

In my case, I wanted the last update date from the DESCRIBE DETAIL command, and it happens that DESCRIBE HISTORY is treated as a proper table in Spark SQL, and adding LIMIT 1 shows the most recent record. You can get the same info that way. The benefit of that is that you can save it as permament view, where in the above method you can't:

%sql
SELECT timestamp as lastModified 
FROM (DESCRIBE HISTORY database_name.table_name LIMIT 1)
user_119664210
Related