ALTER VIEW

Change an existing view.

Syntax

ALTER VIEW <view_name>
  [ AT { REF[ERENCE] | BRANCH } <reference_name> ]
  { { ALTER | MODIFY | CHANGE } COLUMN <old_column_name> <new_column_name> <data_type>
  | ADD COLUMNS ( <column_name1> <data_type> [,... ] )
  | DROP COLUMN <column_name>
  | { ADD | DROP } PARTITION FIELD { <column_name> | <partition_transform> }
  | 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>, ... ) ]
    [ ARROW CACHE ]
  | CREATE RAW REFLECTION <reflection_name> USING DISPLAY ( <column_name1>, <column_name2>, ...)
    [ PARTITION BY ( <column_name1>, <column_name2>, ... ) ]
    [ LOCALSORT BY ( <column_name1>, <column_name2>, ... ) ]
    [ ARROW CACHE ]
  | DROP REFLECTION <reflection_name>
  | 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.

{ ALTER | MODIFY | CHANGE } COLUMN <old_column_name> <new_column_name> <data_type> String

Changes the data type for a column, and gives you the option to rename the column.

ADD COLUMNS ( <column_name1> <data_type> [, ... ] ) String

Creates one or more columns that have the specified names, data types, and character limits. The size is an attribute of the data type.

DROP COLUMN <column_name> String

Drops the specified column. This action cannot be undone.

{ ADD | DROP } PARTITION FIELD { <column_name> | <partition_transform> } String Optional

ADD partitions the view data on the values in a single column or by using one of the partition-transformation functions. DROP drops the partition definition. These are the partition-transformation functions:

Transform	Description
identity( <col> )	Explicitly specified identity transform
year( <col> )	Partition by year. The column uses either the TIMESTAMP or DATE data type.
month( <ts_col> )	Partition by month. The column uses either the TIMESTAMP or DATE data type.
day( <ts_col> )	Partition by day. The column uses either the TIMESTAMP or DATE data type.
hour( <ts_col> )	Partition by hour. The column uses either the TIMESTAMP or DATE data type.
bucket( <count>, <col> )	Partition by hashed value into <count> buckets
truncate( <length>, <col> )	Partition by truncated value. Strings are truncated to the specified length. Integer and biginteger values are truncated to bins (for example: truncate(10, i) produces 0, 10, 20, and so on).

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 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 external reflection.

CREATE RAW REFLECTION <reflection_name> String

Creates a raw reflection with the specified name.

USING DISPLAY ( <column_name1>, <column_name2>, ... ) String

The columns to include in the reflection.

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.

DROP REFLECTION String Optional

Drops the specified reflection defined on the view.

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

Add a column for a view

ALTER VIEW customers
  ADD COLUMNS (ssn_col VARCHAR)

Rename a column for a view

ALTER VIEW customers
  RENAME COLUMN ssn_col protect_ssn

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)
    ARROW CACHE;

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";

ALTER VIEW

Parameters​

Examples​

Parameters

Examples