CODEWITHSUNDEEP
databricksdatabricks-sql
Pontus Johansson
Pontus Johansson

Reputation: 81

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

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

Upvotes: 3

Views: 5154

Answers (2)

matkurek
matkurek

Reputation: 781

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)

Upvotes: 3

Axel R.
Axel R.

Reputation: 1300

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)

Upvotes: 1

Related Questions