topic Re: Get metadata information about Delta Table using only SQL Editor (Query)? in Data Engineering

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

heron — Sat, 08 Jun 2024 17:12:14 GMT

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

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

brockb — Sat, 08 Jun 2024 20:12:24 GMT

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.

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

jacovangelder — Tue, 11 Jun 2024 18:40:39 GMT

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.