Dataset SQL Statements

Datasets can be managed using SQL commands.

Managing Virtual Datasets

Creating Virtual Datasets

{{< codeheader "Syntax" >}}

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

For example:

{{< codeheader "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:

{{< codeheader "Syntax" >}}

ALTER VDS <dataset> SET ENABLE_DEFAULT_REFLECTION = TRUE | FALSE

For example:

{{< codeheader "Example" >}}

ALTER VDS mytable SET ENABLE_DEFAULT_REFLECTION = TRUE

Replacing Virtual Datasets

{{< codeheader "Syntax" >}}

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

For example:

{{< codeheader "Example" >}}

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

Dropping Virtual Datasets

{{< codeheader "Syntax" >}}

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.

{{< codeheader "Syntax" >}}

ALTER PDS <PHYSICAL-DATASET-PATH> FORGET METADATA

Refreshing Physical Dataset Metadata

This command refreshes metadata for a given dataset.

{{< codeheader "Syntax" >}}

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)

Note:
Order Matters**

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