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:
REPLACE VDS demo.jobs_vds as SELECT * FROM "oracle_e2e".DREMIO.JOBS
[info] The path of the dataset needs to exist before creation.
Replacing Virtual Datasets
REPLACE VDS <VIRTUAL-DATASET-PATH> AS <SQL-QUERY>
For example:
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. |
| 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. |
| MAINTAIN WHEN MISSING | Prevents missing metadata from being deleted during refresh. |
| DELETE WHEN MISSING | Deletes missing metadata during refresh. |
[info] Order Matters
Correct order:
refresh metadata AUTO PROMOTION LAZY UPDATE
Incorrect orderrefresh metadata LAZY UPDATE AUTO PROMOTION