> > >

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

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-Command	Description
AVOID PROMOTION	Prevents queries from promoting files/folders to datasets. (Default)
AUTO PROMOTION	Allows queries to promote files/folders to datasets.
FORCE UPDATE	Forces a full update of metadata.
LAZY UPDATE	Does not perform a full update of metadata. (Default)
MAINTAIN WHEN MISSING	Prevents missing metadata from being deleted during refresh.
DELETE WHEN MISSING	Deletes missing metadata during refresh. (Default)

Order Matters

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