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> }
  | RENAME [ COLUMN ] <old_column_name> [ TO ] <new_column_name>
  | REFRESH METADATA }

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. Example: truncate(10, i) produces 0, 10, 20, and so on.

.

---

RENAME COLUMN <old_column_name> [ TO ] <new_column_name>

String

Specifies the column to rename.

---

REFRESH METADATA

Refreshes the metadata associated with the specified view. 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 view's metadata. If you choose to use two or more of these clauses, they must be entered in a specified priority order:

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

[ IF EXISTS ]

String

Optional

If included in a query, this clause prevents exceptions from being thrown, should the specified object not exist.

---

<table_or_view_name>

String

The name of the table or view on which you want to set a masking policy.

---

<column_name>

String

The column on which to either set a masking policy. The UDF serving as the masking policy must accept and return the same data type as the column it is masking.

---

<function_name>

String

The function to be used for masking data. If a function with the given name does not exist, then the affected view will not be reachable until the policy is dropped or a UDF created.

---

<column_name>

String

The column names to which this column-masking policy will apply. Multiple columns may be specified if they are separated by commas.

Examples

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)