Querying Dremio Metadata
Dremio catalogs, schemas, physical datasets, virtual datasets and columns can be queried
using INFORMATION_SCHEMA
queries.
Catalogs
Returns DREMIO
as the only catalog in the system.
select * from INFORMATION_SCHEMA.CATALOGS
Sample Catalog Output
CATALOG_NAME | CATALOG_DESCRIPTION | CATALOG_CONNECT |
---|---|---|
DREMIO | The internal metadata used by Dremio |
Schemas
Returns available schemas – sources and spaces.
This includes child paths (e.g space1
, space1.folder1
, space1.folder1.folder2
)
that have physical or virtual datasets in them as individual schemas.
select * from INFORMATION_SCHEMA.SCHEMATA
Sample Schema Output
CATALOG_NAME | SCHEMA_NAME | SCHEMA_OWNER | TYPE | IS_MUTABLE |
---|---|---|---|---|
DREMIO | BEST_SCHEMA | <owner> | simple | NO |
Tables
Returns available physical and virtual datasets in the system.
select * from INFORMATION_SCHEMA."TABLES"
Sample Table Output
TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE |
---|---|---|---|
DREMIO | SCHEMA1 | TABLE1 | TABLE |
DREMIO | SCHEMA1 | VIEW1 | VIEW |
DREMIO | SYS | SYSTEM_TABLE1 | SYSTEM_TABLE |
Views
Returns available virtual datasets and their SQL definitions.
select * from INFORMATION_SCHEMA.VIEWS
Sample Views Output
TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | VIEW_DEFINITION |
---|---|---|---|
DREMIO | SCHEMA1 | VIEW1 | select * from table1 |
Columns
Returns available columns. In Only Queried Datasets
mode, Dremio returns columns for datasets that were queried in Dremio.
select * from INFORMATION_SCHEMA.COLUMNS
note:
The list of available columns for physical datasets is determined by the metadata caching policy on individual sources.
Sample Column Output
TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE |
---|---|---|---|---|---|---|---|
DREMIO | SCHEMA1 | TABLE1 | COLUMN1 | 1 | null | YES | DOUBLE |