On this page

    Dataset SQL Statements

    Datasets can be managed using SQL commands.

    Managing Virtual Datasets

    Creating Virtual Datasets

    CREATE VDS <VIRTUAL-DATASET-PATH> AS <SQL-QUERY>
    

    For example:

    CREATE VDS demo.jobs_vds as SELECT * FROM "oracle_e2e".DREMIO.JOBS
    

    Note:
    The path of the dataset needs to exist before creation.

    Enabling Default Reflections

    Dremio administrators enable Default Reflections for each virtual dataset with the following SQL command:

    ALTER VDS <dataset> SET ENABLE_DEFAULT_REFLECTION = TRUE | FALSE
    

    For example:

    ALTER VDS mytable SET ENABLE_DEFAULT_REFLECTION = TRUE
    

    Replacing Virtual Datasets

    CREATE OR REPLACE VDS <VIRTUAL-DATASET-PATH> AS <SQL-QUERY>
    

    For example:

    CREATE OR REPLACE VDS demo.jobs_vds as SELECT * FROM "oracle_e2e".DREMIO.JOBS
    

    Dropping Virtual Datasets

    DROP VDS <VIRTUAL-DATASET-PATH>
    

    Managing Physical Datasets

    Forgetting Physical Dataset Metadata

    This command removes all metadata for a given dataset and deletes it from the UI until the next metadata refresh. The dataset can still be queried using SQL.

    ALTER PDS <PHYSICAL-DATASET-PATH> FORGET METADATA
    

    Refreshing Physical Dataset Metadata

    This command refreshes metadata for a given dataset.

    ALTER PDS <PHYSICAL-DATASET-PATH> REFRESH METADATA
        [AVOID PROMOTION | AUTO PROMOTION]
        [FORCE UPDATE | LAZY UPDATE]
        [MAINTAIN WHEN MISSING | DELETE WHEN MISSING]
    
    
    Sub-CommandDescription
    AVOID PROMOTIONPrevents queries from promoting files/folders to datasets. (Default)
    AUTO PROMOTIONAllows queries to promote files/folders to datasets.
    FORCE UPDATEForces a full update of metadata.
    LAZY UPDATEDoes not perform a full update of metadata. (Default)
    MAINTAIN WHEN MISSINGPrevents missing metadata from being deleted during refresh.
    DELETE WHEN MISSINGDeletes missing metadata during refresh. (Default)

    Note:
    Order Matters**

    Correct order: refresh metadata AUTO PROMOTION LAZY UPDATE
    Incorrect order refresh metadata LAZY UPDATE AUTO PROMOTION