ALTER VIEW
Change an existing view.
SyntaxALTER 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.
|
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:
- Promotion option: either
AVOID PROMOTION
orAUTO PROMOTION
- Update option: either
FORCE UPDATE
orLAZY UPDATE
- Missing option: either
MAINTAIN WHEN MISSING
orDELETE 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 columnsALTER VIEW customers
MODIFY COLUMN ssn_col
SET MASKING POLICY protect_ssn (ssn_col, region)
ALTER VIEW customers
MODIFY COLUMN ssn_col
UNSET MASKING POLICY protect_ssn
ALTER VIEW customers
ADD ROW ACCESS POLICY state_policy ( state_col )
ALTER VIEW customers
DROP ROW ACCESS POLICY protect_ssn (ssn_col)