cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

Get metadata information about Delta Table using only SQL Editor (Query)?

heron
New Contributor

I'm trying to obtain the basic information and the storage location of the delta table, but without success.
Is there a way to get: storage location, type, catalog, schema, table name using SQL Editor through a query?
I can get the basic information (catalog, schema, table name) through the information_schema.tables table, but I couldn't find the rest (storage location, type).
I did a search and the AI-ChatGPT ​​suggested the query below but it's not working.

SELECT
t.table_catalog, t.table_schema, t.table_name, t.table_type,
detail.sizeInBytes, detail.location, detail.format
FROM
information_schema.tables t,
LATERAL (DESCRIBE DETAIL delta.`${t.table_catalog}.${t.table_schema}.${t.table_name}`) detail
WHERE
t.table_type = 'EXTERNAL' AND detail.format = 'delta'

Thank you very much if anyone can help

 

2 REPLIES 2

brockb
Valued Contributor
Valued Contributor

Hi Heron,

The output of the `DESCRIBE DETAIL table_name` includes the table format, size in bytes, location, and other information that sounds like what you're looking for. The only one I'm unsure about is the 'table type'. Here's the schema of the output of that command:

https://docs.databricks.com/en/delta/table-details.html

Hope it helps.

jacovangelder
Contributor III

I believe what you're looking for is DESCRIBE EXTENDED <table_name>.
This returns both delta storage location aswell as detailed table information such as type, table properties, catalog, schema, etc. 

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!