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>
  | MODIFY COLUMN <column_name>
     { SET MASKING POLICY <function_name> ( <column_name> [, ... ] )
     | UNSET MASKING POLICY <function_name> }
   | { ADD | DROP } ROW ACCESS POLICY <function_name> ( <column_name> [, ... ] )
  | 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>
  | REFRESH REFLECTIONS
  | SET ENABLE_DEFAULT_REFLECTION = TRUE | FALSE
  | ROUTE REFLECTIONS TO { DEFAULT QUEUE | QUEUE { <queue_name> | <queue_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.

---

MODIFY COLUMN <column_name> String

Specifies the column on which to either set a masking policy or unset one.

---

SET MASKING POLICY <function_name> ( <column_name> [, ... ] ) String

Sets a policy for masking data. The UDF serving as the masking policy must accept and return the same data type as the column it is masking. If a UDF with the given name does not exist, then the affected view will not be reachable until the policy is dropped or a UDF created.

---

UNSET MASKING POLICY <function_name> String

Removes a policy for masking data.

---

{ ADD | DROP } ROW ACCESS POLICY <function_name> ( <column_name> [, ... ] ) String

Adds a row-access policy to the view, or removes one from the view. The UDF serving as the row-access policy must return BOOLEAN. If the UDF specified in the policy does not exist, then the Dremio engine gives an error when attempting to query the view.

---

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.

---

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.

---

SET ENABLE_DEFAULT_REFLECTION = TRUE | FALSE String   Optional

Enable or disable the default reflection for the view by setting the option to TRUE or FALSE.

---

ROUTE REFLECTIONS TO { DEFAULT QUEUE | QUEUE { <queue_name> | <queue_uuid> } } String

Use the queue name or unique identifier to specify the queue on which to run jobs that create and refresh reflections that are defined on the view. If a queue name or unique identifier is not specified, the default queue is used. You can also directly specify the default queue.

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

Enable the default reflection for a view

ALTER VIEW myView
  SET ENABLE_DEFAULT_REFLECTION = TRUE

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 specific queues

ALTER VIEW "View 1" ROUTE REFLECTIONS TO DEFAULT QUEUE;
ALTER VIEW "View 2" ROUTE REFLECTIONS TO QUEUE "REFLECTIONS QUEUE";
ALTER VIEW "View 3" ROUTE REFLECTIONS TO QUEUE "QUEUE 1";

Set a column-masking policy on multiple columns

ALTER VIEW customers
    MODIFY COLUMN ssn_col
    SET MASKING POLICY protect_ssn (ssn_col, region)

Unset a column-masking policy

ALTER VIEW customers
    MODIFY COLUMN ssn_col
    UNSET MASKING POLICY protect_ssn

Add a row-access policy to a view

ALTER VIEW customers
  ADD ROW ACCESS POLICY state_policy ( state_col )

Remove row-access policy from a view

ALTER VIEW customers
  DROP ROW ACCESS POLICY protect_ssn (ssn_col)