ALTER VIEW

Change an existing view.

Syntax

ALTER VIEW <view_name>
  [ AT { REF[ERENCE] | BRANCH } <reference_name> ]
  { REFRESH METADATA
    [ FOR PARTITIONS ( <partition_name> = '<value>') ]
        [ { AVOID | AUTO } PROMOTION ]
        [ { FORCE | LAZY } UPDATE ]
        [ { MAINTAIN | DELETE } WHEN MISSING ]
  | CREATE EXTERNAL REFLECTION <reflection_name> USING <view_name>
  | CREATE AGGREGATE REFLECTION <reflection_name> USING  
    { DIMENSIONS ( <column_name1>, <column_name2>, ... )
    | MEASURES ( <column_name1> ( <aggregation_type>, <column_name2> <aggregation_type> , ... ) )
    | DIMENSIONS ( <column_name1>, <column_name2>, ... ) MEASURES ( <column_name1> ( <aggregation_type>, <column_name2> <aggregation_type> , ... ) ) }
    [ PARTITION BY ( <column_name1>, <column_name2>, ... ) ]
    [ LOCALSORT BY ( <column_name1>, <column_name2>, ... ) ]
  | CREATE RAW REFLECTION <reflection_name> USING DISPLAY ( <column_name1>, <column_name2>, ...)
    [ PARTITION BY ( <column_name1>, <column_name2>, ... ) ]
    [ LOCALSORT BY ( <column_name1>, <column_name2>, ... ) ]
  | DROP REFLECTION <reflection_name>
  | REFRESH REFLECTIONS
  | ROUTE REFLECTIONS TO { DEFAULT ENGINE | ENGINE { <engine_name> | <engine_uuid> } }
  }

Parameters

<view_name> String

The name of the view that you want to create. The name of the view should be unique.

AT { REF[ERENCE] | BRANCH } <reference_name> String

Specifies the reference at which you want to alter the view. When this parameter is omitted, the current reference is used. REF: Identifies the specific reference. BRANCH: Identifies the specific branch.

REFRESH METADATA String Optional

Refreshes the metadata associated with the specified view. Applies only to views in Iceberg REST Catalog sources.

You can choose to either do a full or partial metadata refresh:

A full refresh will update the metadata for the entire view.
A partial refresh will enable you to only update specified partitions.

Optional clauses are available for refreshing a table's metadata. If you choose to use two or more of these clauses, they must be entered in a specified priority order:

Promotion option: either AVOID PROMOTION or AUTO PROMOTION
Update option: either FORCE UPDATE or LAZY UPDATE
Missing option: either MAINTAIN WHEN MISSING or DELETE WHEN MISSING

note

If the view is not partitioned, you will receive an error when you attempt to refresh the metadata using the FOR PARTITIONS clause.

FOR PARTITIONS ( <partition_name> = '<value>' ) String Optional

Use this clause to do a partial refresh of the view's metadata.

<partition_name> identifies the name of the partition to be refreshed.
<value> identifies the specific partition that should be refreshed. Must be contained in single quotes.

{ AVOID | AUTO } PROMOTION Optional

Clauses that determine whether files and folders are promoted to datasets when you run a query.

AVOID PROMOTION prevents queries from promoting files/folders to datasets. The AUTO PROMOTION allows queries to promote files/folders to datasets. This is the default option when you do not include a promotion clause.

{ FORCE | LAZY } UPDATE Optional

Clauses that determine whether metadata is updated when you run a query.

FORCE UPDATE forces a full update of metadata.
LAZY UPDATE does not perform a full update of metadata. This is the default option when you do not include an update clause.

{ MAINTAIN | DELETE } WHEN MISSING Optional

Clauses that determine how missing metadata is handled when you run a query.

MAINTAIN WHEN MISSING prevents missing metadata from being deleted during refresh.
DELETE WHEN MISSING deletes missing metadata during refresh. This is the default option when you do not include a clause.

CREATE EXTERNAL REFLECTION <reflection_name> String

Creates an external Reflection with the specified name.

USING <view_name> String

The path of the derived view to use with the Reflection.

CREATE AGGREGATE REFLECTION <reflection_name> String

Creates an aggregation Reflection with the specified name.

DIMENSIONS ( <column_name1>, <column_name2>, ... ) String

The columns to include as dimensions in the Reflection.

MEASURES ( <column_name1> ( <saggregation_type> ), <column_name2> ( <aggregation_type ), ... ) String

The columns to include as measures in the Reflection, and the type of aggregation to perform on them. The possible types are COUNT, MIN, MAX, SUM, and APPROXIMATE COUNT DISTINCT.

PARTITION BY ( <column_name1>, <column_name2>, ... ) String Optional

The columns on which to partition the data horizontally in the Reflection.

LOCALSORT BY ( <column_name1>, <column_name2>, ... ) String Optional

The columns on which to sort the data that is in the Reflection.

CREATE RAW REFLECTION <reflection_name> String

Creates a raw Reflection with the specified name.

DROP REFLECTION String Optional

Drops the specified Reflection defined on the view.

REFRESH REFLECTIONS String Optional

For information about the refresh action performed, see Triggering Refreshes by Using the Reflection API, the Catalog API, or an SQL Command.

ROUTE REFLECTIONS TO { DEFAULT ENGINE | ENGINE { <engine_name> | <engine_uuid> } } String

Specify the engine on which to run jobs that create and refresh Reflections that are defined on the view. Use either the name or UUID of the engine. If neither a name nor a UUID is specified, the default engine is used. You can also directly specify the default engine.

Examples

Create a raw Reflection that sorts customers by last name and partitions them by country

ALTER VIEW Sales."customers"
  CREATE RAW REFLECTION customers_by_country USING DISPLAY (id,lastName,firstName,address,country)
    PARTITION BY (country)
    LOCALSORT BY (lastName);

Create an aggregation Reflection that counts the cities per state and sorts by state

ALTER VIEW Samples."samples.dremio.com"."zips.json"
  CREATE AGGREGATE REFLECTION per_state USING
  DIMENSIONS (state) MEASURES (city (COUNT))
  LOCALSORT BY (state);

Create an aggregation Reflection that counts p1 and p2 as measures in the Reflection

ALTER VIEW BI."p1_view"
  CREATE AGGREGATE REFLECTION p1_view_agg USING
  DIMENSIONS (c1,c2) MEASURES (p1(COUNT, MIN), p2(SUM, MAX, APPROXIMATE COUNT DISTINCT))
    PARTITION BY (c1)
    LOCALSORT BY (c2)

Route Reflections to Engine 1

ALTER VIEW "View 1" ROUTE REFLECTIONS TO ENGINE "Engine 1";
ALTER VIEW "View 2" ROUTE REFLECTIONS TO ENGINE "Engine 1";
ALTER VIEW "View 3" ROUTE REFLECTIONS TO ENGINE "Engine 1";

Parameters​

Examples​

Parameters

Examples