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 |