Skip to main content
Version: 24.3.x

Dataset SQL Statements

Datasets can be managed using SQL commands.

Managing Views

Creating Views

Syntax
CREATE VIEW <VIEW-PATH> AS <SQL-QUERY>

For example:

Example
CREATE VIEW demo.jobs_view 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 view with the following SQL command:

Syntax
ALTER VIEW <view> SET ENABLE_DEFAULT_REFLECTION = TRUE | FALSE

For example:

Example
ALTER VIEW mytable SET ENABLE_DEFAULT_REFLECTION = TRUE

Replacing Views

Syntax
CREATE OR REPLACE VIEW <VIEW-PATH> AS <SQL-QUERY>

For example:

Example
CREATE OR REPLACE VIEW demo.jobs_view as SELECT * FROM "oracle_e2e".DREMIO.JOBS

Dropping Views

Syntax
DROP VIEW [ IF EXISTS ] <view_name>

Parameters

[ IF EXISTS ] Optional

When included, the command will succeed regardless of whether the view existed. If this clause is not specified, the command will fail if the view to be dropped does not exist.


<view_name> String

The path of the view that you want to drop.

Managing Tables

Forgetting Table Metadata

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

Syntax
ALTER TABLE <TABLE-PATH> FORGET METADATA

Refreshing Table Metadata

This command refreshes metadata for a given table.

Syntax
ALTER TABLE <TABLE-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 tables. (Default)
AUTO PROMOTIONAllows queries to promote files/folders to tables.
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