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]

Note:
Order Matters**

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