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-Command	Description
AVOID PROMOTION	Prevents queries from promoting files/folders to tables. (Default)
AUTO PROMOTION	Allows queries to promote files/folders to tables.
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