Querying Dremio Metadata

Dremio catalogs, schemas, physical datasets, virtual datasets and columns can be queries using INFORMATION_SCHEMA queries.

Catalogs

Returns DREMIO as the only catalog in the system.

Usage

select * from INFORMATION_SCHEMA.CATALOGS

Output

CATALOG_NAME CATALOG_DESCRIPTION CATALOG_CONNECT
DREMIO The internal metadata used by Dremio

Schemas

Returns available schemas -- sources and spaces. This will include child paths (e.g space1, space1.folder1, space1.folder1.folder2) that have physical or virtual datasets in them as individual schemas.

Usage

select * from INFORMATION_SCHEMA.SCHEMATA

Sample 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.

Usage

select * from INFORMATION_SCHEMA."TABLES"

Sample 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.

Usage

select * from INFORMATION_SCHEMA.VIEWS

Sample Output

TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION
DREMIO SCHEMA1 VIEW1 select * from table1

Columns

Returns available columns. Note that the list of available columns for physical datasets is determined by the metadata caching policy on individual sources:

  • In All Datasets mode, Dremio will return columns for all physical datasets.
  • In Only Queried Datasets mode, Dremio will return columns for datasets that were queried in Dremio.

Usage

select * from INFORMATION_SCHEMA.COLUMNS

Sample 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

results matching ""

    No results matching ""