Skip to main content

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> }
| 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:

TransformDescription
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).

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

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