On this page

    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_NAMECATALOG_DESCRIPTIONCATALOG_CONNECT
    DREMIOThe 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_NAMESCHEMA_NAMESCHEMA_OWNERTYPEIS_MUTABLE
    DREMIOBEST_SCHEMA<owner>simpleNO

    Tables

    Returns available physical and virtual datasets in the system.

    select * from INFORMATION_SCHEMA."TABLES"
    

    Sample Table Output

    TABLE_CATALOGTABLE_SCHEMATABLE_NAMETABLE_TYPE
    DREMIOSCHEMA1TABLE1TABLE
    DREMIOSCHEMA1VIEW1VIEW
    DREMIOSYSSYSTEM_TABLE1SYSTEM_TABLE

    Views

    Returns available virtual datasets and their SQL definitions.

    select * from INFORMATION_SCHEMA.VIEWS
    

    Sample Views Output

    TABLE_CATALOGTABLE_SCHEMATABLE_NAMEVIEW_DEFINITION
    DREMIOSCHEMA1VIEW1select * 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_CATALOGTABLE_SCHEMATABLE_NAMECOLUMN_NAMEORDINAL_POSITIONCOLUMN_DEFAULTIS_NULLABLEDATA_TYPE
    DREMIOSCHEMA1TABLE1COLUMN11nullYESDOUBLE