ALTER TABLE¶
Modifies the properties, columns, or constraints for an existing table.
- See also:
ALTER TABLE … ALTER COLUMN , CREATE TABLE , DROP TABLE , SHOW TABLES , DESCRIBE TABLE
Syntax¶
ALTER TABLE [ IF EXISTS ] <name> RENAME TO <new_table_name>
ALTER TABLE [ IF EXISTS ] <name> SWAP WITH <target_table_name>
ALTER TABLE [ IF EXISTS ] <name> { clusteringAction | tableColumnAction | constraintAction }
ALTER TABLE [ IF EXISTS ] <name> dataMetricFunctionAction
ALTER TABLE [ IF EXISTS ] <name> dataGovnPolicyTagAction
ALTER TABLE [ IF EXISTS ] <name> extTableColumnAction
ALTER TABLE [ IF EXISTS ] <name> searchOptimizationAction
ALTER TABLE [ IF EXISTS ] <name> SET
[ DATA_RETENTION_TIME_IN_DAYS = <integer> ]
[ MAX_DATA_EXTENSION_TIME_IN_DAYS = <integer> ]
[ CHANGE_TRACKING = { TRUE | FALSE } ]
[ DEFAULT_DDL_COLLATION = '<collation_specification>' ]
[ ENABLE_SCHEMA_EVOLUTION = { TRUE | FALSE } ]
[ COMMENT = '<string_literal>' ]
ALTER TABLE [ IF EXISTS ] <name> UNSET {
DATA_RETENTION_TIME_IN_DAYS |
MAX_DATA_EXTENSION_TIME_IN_DAYS |
CHANGE_TRACKING |
DEFAULT_DDL_COLLATION |
ENABLE_SCHEMA_EVOLUTION |
COMMENT |
}
[ , ... ]
Where:
clusteringAction ::= { CLUSTER BY ( <expr> [ , <expr> , ... ] ) /* RECLUSTER is deprecated */ | RECLUSTER [ MAX_SIZE = <budget_in_bytes> ] [ WHERE <condition> ] /* { SUSPEND | RESUME } RECLUSTER is valid action */ | { SUSPEND | RESUME } RECLUSTER | DROP CLUSTERING KEY }tableColumnAction ::= { ADD [ COLUMN ] [ IF NOT EXISTS ] <col_name> <col_type> [ { DEFAULT <default_value> | { AUTOINCREMENT | IDENTITY } /* AUTOINCREMENT (or IDENTITY) is supported only for */ /* columns with numeric data types (NUMBER, INT, FLOAT, etc.). */ /* Also, if the table is not empty (i.e. if the table contains */ /* any rows), only DEFAULT can be altered. */ [ { ( <start_num> , <step_num> ) | START <num> INCREMENT <num> } ] [ { ORDER | NOORDER } ] } ] [ inlineConstraint ] [ COLLATE '<collation_specification>' ] | RENAME COLUMN <col_name> TO <new_col_name> | ALTER | MODIFY [ ( ] [ COLUMN ] <col1_name> DROP DEFAULT , [ COLUMN ] <col1_name> SET DEFAULT <seq_name>.NEXTVAL , [ COLUMN ] <col1_name> { [ SET ] NOT NULL | DROP NOT NULL } , [ COLUMN ] <col1_name> [ [ SET DATA ] TYPE ] <type> , [ COLUMN ] <col1_name> COMMENT '<string>' , [ COLUMN ] <col1_name> UNSET COMMENT [ , [ COLUMN ] <col2_name> ... ] [ , ... ] [ ) ] | DROP [ COLUMN ] [ IF EXISTS ] <col1_name> [, <col2_name> ... ] } inlineConstraint ::= [ NOT NULL ] [ CONSTRAINT <constraint_name> ] { UNIQUE | PRIMARY KEY | { [ FOREIGN KEY ] REFERENCES <ref_table_name> [ ( <ref_col_name> ) ] } } [ <constraint_properties> ]For detailed syntax and examples for altering columns, see ALTER TABLE … ALTER COLUMN. .
For detailed syntax and examples for creating/altering inline constraints, see CREATE | ALTER TABLE … CONSTRAINT.
dataMetricFunctionAction ::= SET DATA_METRIC_SCHEDULE = { '<num> MINUTE' | 'USING CRON <expr> <time_zone>' | 'TRIGGER_ON_CHANGES' } | UNSET DATA_METRIC_SCHEDULE | { ADD | DROP } DATA METRIC FUNCTION <metric_name> ON ( <col_name> [ , ... ] ) [ , <metric_name_2> ON ( <col_name> [ , ... ] ) ] | MODIFY DATA METRIC FUNCTION <metric_name> ON ( <col_name> [ , ... ] ) { SUSPEND | RESUME } [ , <metric_name_2> ON ( <col_name> [ , ... ] ) { SUSPEND | RESUME } ]dataGovnPolicyTagAction ::= { SET TAG <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' ... ] | UNSET TAG <tag_name> [ , <tag_name> ... ] } | { ADD ROW ACCESS POLICY <policy_name> ON ( <col_name> [ , ... ] ) | DROP ROW ACCESS POLICY <policy_name> | DROP ROW ACCESS POLICY <policy_name> , ADD ROW ACCESS POLICY <policy_name> ON ( <col_name> [ , ... ] ) | DROP ALL ROW ACCESS POLICIES } | { SET AGGREGATION POLICY <policy_name> [ ENTITY KEY ( <col_name> [, ... ] ) ] [ FORCE ] | UNSET AGGREGATION POLICY } | ADD [ COLUMN ] [ IF NOT EXISTS ] <col_name> <col_type> [ [ WITH ] MASKING POLICY <policy_name> [ USING ( <col1_name> , <cond_col_1> , ... ) ] ] [ [ WITH ] PROJECTION POLICY <policy_name> ] [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ] | { { ALTER | MODIFY } [ COLUMN ] <col1_name> SET MASKING POLICY <policy_name> [ USING ( <col1_name> , <cond_col_1> , ... ) ] [ FORCE ] | UNSET MASKING POLICY } | { { ALTER | MODIFY } [ COLUMN ] <col1_name> SET PROJECTION POLICY <policy_name> [ FORCE ] | UNSET PROJECTION POLICY } | { ALTER | MODIFY } [ COLUMN ] <col1_name> SET TAG <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' ... ] , [ COLUMN ] <col2_name> SET TAG <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' ... ] | { ALTER | MODIFY } [ COLUMN ] <col1_name> UNSET TAG <tag_name> [ , <tag_name> ... ] , [ COLUMN ] <col2_name> UNSET TAG <tag_name> [ , <tag_name> ... ]extTableColumnAction ::= { ADD [ COLUMN ] [ IF NOT EXISTS ] <col_name> <col_type> AS ( <expr> ) | RENAME COLUMN <col_name> TO <new_col_name> | DROP [ COLUMN ] [ IF EXISTS ] <col1_name> [, <col2_name> ... ] }constraintAction ::= { ADD outoflineConstraint | RENAME CONSTRAINT <constraint_name> TO <new_constraint_name> | { ALTER | MODIFY } { CONSTRAINT <constraint_name> | PRIMARY KEY | UNIQUE | FOREIGN KEY } ( <col_name> [ , ... ] ) [ [ NOT ] ENFORCED ] [ VALIDATE | NOVALIDATE ] [ RELY | NORELY ] | DROP { CONSTRAINT <constraint_name> | PRIMARY KEY | UNIQUE | FOREIGN KEY } ( <col_name> [ , ... ] ) [ CASCADE | RESTRICT ] } outoflineConstraint ::= [ CONSTRAINT <constraint_name> ] { UNIQUE [ ( <col_name> [ , <col_name> , ... ] ) ] | PRIMARY KEY [ ( <col_name> [ , <col_name> , ... ] ) ] | [ FOREIGN KEY ] [ ( <col_name> [ , <col_name> , ... ] ) ] REFERENCES <ref_table_name> [ ( <ref_col_name> [ , <ref_col_name> , ... ] ) ] } [ <constraint_properties> ]For detailed syntax and examples for creating/altering out-of-line constraints, see CREATE | ALTER TABLE … CONSTRAINT.
searchOptimizationAction ::= { ADD SEARCH OPTIMIZATION [ ON <search_method_with_target> [ , <search_method_with_target> ... ] ] | DROP SEARCH OPTIMIZATION [ ON { <search_method_with_target> | <column_name> | <expression_id> } [ , ... ] ] }For details, see Search optimization actions (searchOptimizationAction).
Parameters¶
name
Identifier for the table to alter. If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case sensitive.
RENAME TO new_table_name
Renames the specified table with a new identifier that is not currently used by any other tables in the schema.
For more information about table identifiers, see Identifier requirements.
You can move the object to a different database and/or schema while optionally renaming the object. To do so, specify a qualified
new_name
value that includes the new database and/or schema name in the formdb_name.schema_name.object_name
orschema_name.object_name
, respectively.Note
The destination database and/or schema must already exist. In addition, an object with the same name cannot already exist in the new location; otherwise, the statement returns an error.
Moving an object to a managed access schema is prohibited unless the object owner (that is, the role that has the OWNERSHIP privilege on the object) also owns the target schema.
When an object (table, column, etc.) is renamed, other objects that reference it must be updated with the new name.
SWAP WITH target_table_name
Swap renames two tables in a single transaction.
Note that swapping a permanent or transient table with a temporary table, which persists only for the duration of the user session in which it was created, is not allowed. This restriction prevents a naming conflict that could occur when a temporary table is swapped with a permanent or transient table, and an existing permanent or transient table has the same name as the temporary table. To swap a permanent or transient table with a temporary table, use three
ALTER TABLE ... RENAME TO
statements: Rename tablea
toc
,b
toa
, and thenc
tob
.
Note
To rename a table or swap two tables, the role used to perform the operation must have OWNERSHIP privileges on the table(s). In addition, renaming a table requires the CREATE TABLE privilege on the schema for the table.
SET ...
Specifies one or more properties/parameters to set for the table (separated by blank spaces, commas, or new lines):
DATA_RETENTION_TIME_IN_DAYS = integer
Object-level parameter that modifies the retention period for the table for Time Travel. For more information, see Understanding & using Time Travel and Working with Temporary and Transient Tables.
For a detailed description of this parameter, as well as more information about object parameters, see Parameters.
Values:
Standard Edition:
0
or1
Enterprise Edition:
0
to90
for permanent tables0
or1
for temporary and transient tables
Note
A value of
0
effectively disables Time Travel for the table.MAX_DATA_EXTENSION_TIME_IN_DAYS = integer
Object parameter that specifies the maximum number of days for which Snowflake can extend the data retention period for the table to prevent streams on the table from becoming stale.
For a detailed description of this parameter, see MAX_DATA_EXTENSION_TIME_IN_DAYS.
CHANGE_TRACKING = TRUE | FALSE
Specifies to enable or disable change tracking on the table.
TRUE
enables change tracking on the table. This option adds several hidden columns to the source table and begins storing change tracking metadata in the columns. These columns consume a small amount of storage.The change tracking metadata can be queried using the CHANGES clause for SELECT statements, or by creating and querying one or more streams on the table.
FALSE
disables change tracking on the table. Associated hidden columns are dropped from the table.
DEFAULT_DDL_COLLATION = 'collation_specification'
Specifies a default collation specification for any new columns added to the table.
Setting the parameter does not change the collation specification for any existing columns.
For more information about the parameter, see DEFAULT_DDL_COLLATION.
ENABLE_SCHEMA_EVOLUTION = { TRUE | FALSE }
Enables or disables automatic changes to the table schema from data loaded into the table from source files, including:
Added columns.
By default, schema evolution is limited to a maximum of 10 added columns per load operation. To request more than 10 added columns per load operation, contact Snowflake Support.
The NOT NULL constraint can be dropped from any number of columns missing in new data files.
Setting it to
TRUE
enables automatic table schema evolution. The defaultFALSE
disables automatic table schema evolution.Note
Loading data from files evolves the table columns when all of the following are true:
The COPY INTO <table> statement includes the
MATCH_BY_COLUMN_NAME
option.The role used to load the data has the EVOLVE SCHEMA or OWNERSHIP privilege on the table.
Additionally, for schema evolution with CSV, when used with
MATCH_BY_COLUMN_NAME
andPARSE_HEADER
,ERROR_ON_COLUMN_COUNT_MISMATCH
must be set to false.COMMENT = 'string_literal'
Adds a comment or overwrites the existing comment for the table.
Note
Do not specify copy options using the CREATE STAGE, ALTER STAGE, CREATE TABLE, or ALTER TABLE commands. We recommend that you use the COPY INTO <table> command to specify copy options.
UNSET ...
Specifies one or more properties/parameters to unset for the table, which resets them back to their defaults:
DATA_RETENTION_TIME_IN_DAYS
MAX_DATA_EXTENSION_TIME_IN_DAYS
CHANGE_TRACKING
DEFAULT_DDL_COLLATION
ENABLE_SCHEMA_EVOLUTION
COMMENT
Clustering actions (clusteringAction
)¶
CLUSTER BY ( expr [ , expr , ... ] )
Specifies (or modifies) one or more table columns or column expressions as the clustering key for the table. These are the columns/expressions for which clustering is maintained by Automatic Clustering.
Important
Clustering keys are not intended or recommended for all tables; they typically benefit very large (i.e. multi-terabyte) tables.
Before you specify a clustering key for a table, please see Understanding Snowflake Table Structures.
RECLUSTER ...
Deprecated
Performs manual, incremental reclustering of a table that has a clustering key defined:
MAX_SIZE = budget_in_bytes
Deprecated — use a larger warehouse to achieve more effective manual reclustering
Specifies the upper-limit on the amount of data (in bytes) in the table to recluster.
WHERE condition
Specifies a condition or range on which to recluster data in the table.
Note
Only roles with the OWNERSHIP or INSERT privilege on a table can recluster the table.
SUSPEND | RESUME RECLUSTER
Enables or disables Automatic Clustering for the table.
DROP CLUSTERING KEY
Drops the clustering key for the table.
For more information about clustering keys and reclustering, see Understanding Snowflake Table Structures.
Table column actions (tableColumnAction
)¶
ADD [ COLUMN ] [ IF NOT EXISTS ] col_name col_data_type
.[ DEFAULT default_value | AUTOINCREMENT ... ]
.[ inlineConstraint ]
[ COLLATE 'collation_specification' ]
.[ [ WITH ] MASKING POLICY policy_name ]
.[ [ WITH ] PROJECTION POLICY policy_name ]
.[ [ WITH ] TAG ( tag_name = 'tag_value' [ , tag_name = 'tag_value' , ... ] ) ] [ , ...]
Adds a new column. You can specify a default value, an inline constraint, a collation specification, a masking policy, and/or one or more tags.
A default value for a column that you are adding must be a literal value; it cannot be an expression or a value returned by a function. For example, the following command returns an expected error:
ALTER TABLE t1 ADD COLUMN c5 VARCHAR DEFAULT 12345::VARCHAR;
002263 (22000): SQL compilation error: Invalid column default expression [CAST(12345 AS VARCHAR(16777216))]
When you first create a table, you can use expressions as default values, but not when you add columns.
The default value for a column must match the data type of the column. An attempt to set a default value with a non-matching data type fails with an error. For example:
ALTER TABLE t1 ADD COLUMN c6 DATE DEFAULT '20230101';
002023 (22000): SQL compilation error: Expression type does not match column data type, expecting DATE but got VARCHAR(8) for column C6
For additional details about table column actions, see:
ADD COLUMN operations can be performed on multiple columns in the same command.
If you are not sure if the column already exists, you can specify IF NOT EXISTS when adding the column. If the column already exists, ADD COLUMN has no effect on the existing column and does not result in an error.
Note
You cannot specify IF NOT EXISTS if you are also specifying any of the following for the new column:
DEFAULT, AUTOINCREMENT, or IDENTITY
UNIQUE, PRIMARY KEY, or FOREIGN KEY
RENAME COLUMN col_name to new_col_name
Renames the specified column to a new name that is not currently used for any other columns in the table.
You cannot rename a column that is part of a clustering key.
When an object (table, column, etc.) is renamed, other objects that reference it must be updated with the new name.
DROP COLUMN [ IF EXISTS ] col_name [ CASCADE | RESTRICT ]
Removes the specified column from the table.
If you are not sure if the column already exists, you can specify IF EXISTS when dropping the column. If the column does not exist, DROP COLUMN has no effect and does not result in an error.
Dropping a column is a metadata-only operation. It does not immediately re-write the micro-partition(s) and therefore does not immediately free up the space used by the column. Typically, the space within an individual micro-partition is freed the next time that the micro-partition is re-written, which is typically when a write is done either due to DML (INSERT, UPDATE, DELETE) or re-clustering.
Data metric function actions (dataMetricFunctionAction
)¶
DATA_METRIC_SCHEDULE ...
Specifies the schedule to run the data metric function periodically.
'num MINUTE'
Specifies an interval (in minutes) of wait time inserted between runs of the data metric function. Accepts positive integers only.
Also supports
num M
syntax.For data metric functions, use one of the following values:
5
,15
,30
,60
,720
, or1440
.'USING CRON expr time_zone'
Specifies a cron expression and time zone for periodically running the data metric function. Supports a subset of standard cron utility syntax.
For a list of time zones, see the list of tz database time zones.
The cron expression consists of the following fields, and the periodic interval must be at least 5 minutes:
# __________ minute (0-59) # | ________ hour (0-23) # | | ______ day of month (1-31, or L) # | | | ____ month (1-12, JAN-DEC) # | | | | _ day of week (0-6, SUN-SAT, or L) # | | | | | # | | | | | * * * * *
The following special characters are supported:
*
Wildcard. Specifies any occurrence of the field.
L
Stands for “last”. When used in the day-of-week field, it allows you to specify constructs such as “the last Friday” (“5L”) of a given month. In the day-of-month field, it specifies the last day of the month.
/{n}
Indicates the nth instance of a given unit of time. Each quanta of time is computed independently. For example, if
4/3
is specified in the month field, then the data metric function is scheduled for April, July and October (i.e. every 3 months, starting with the 4th month of the year). The same schedule is maintained in subsequent years. That is, the data metric function is not scheduled to run in January (3 months after the October run).
Note
The cron expression currently evaluates against the specified time zone only. Altering the TIMEZONE parameter value for the account (or setting the value at the user or session level) does not change the time zone for the data metric function.
The cron expression defines all valid run times for the data metric function. Snowflake attempts to run a data metric function based on this schedule; however, any valid run time is skipped if a previous run has not completed before the next valid run time starts.
When both a specific day of month and day of week are included in the cron expression, then the data metric function is scheduled on days satisfying either the day of month or day of week. For example,
DATA_METRIC_SCHEDULE = 'USING CRON 0 0 10-20 * TUE,THU UTC'
schedules a data metric function at 0AM on any 10th to 20th day of the month and also on any Tuesday or Thursday outside of those dates.The shortest granularity of time in cron is minutes.
If a data metric function is resumed during the minute defined in its cron expression, the first scheduled run of the data metric function is the next occurrence of the instance of the cron expression. For example, if data metric function scheduled to run daily at midnight (
USING CRON 0 0 * * *
) is resumed at midnight plus 5 seconds (00:00:05
), the first data metric function run is scheduled for the following midnight.
'TRIGGER_ON_CHANGES'
Specifies that the DMF runs when a DML operation modifies the table, such as inserting a new row or deleting a row.
You can specify
'TRIGGER_ON_CHANGES'
for the following objects:Dynamic tables
External tables
Apache Iceberg™ tables
Regular tables
Temporary tables
Transient tables
Changes to the table as a result of reclustering do not trigger the DMF to run.
{ ADD | DROP } DATA METRIC FUNCTION metric_name
Identifier of the data metric function to add to the table or view or drop from the table or view.
ON ( col_name [ , ... ] )
The table or view columns on which to associate the data metric function. The data types of the columns must match the data types of the columns specified in the data metric function definition.
[ , metric_name_2 ON ( col_name [ , ... ] ) [ , ... ] ]
Additional data metric functions to add to the table or view. Use a comma to separate each data metric function and its specified columns.
MODIFY DATA METRIC FUNCTION metric_name
Identifier of the data metric function to modify.
ON ( col_name [ , ... ] ) { SUSPEND | RESUME }
Suspends or resumes the data metric function on the specified columns. When a data metric function is set for a table or view, the data metric function is automatically included in the schedule.
SUSPEND
removes the data metric function from the schedule.RESUME
brings a suspended date metric function back into the schedule.
[ , metric_name_2 ON ( col_name [ , ... ] ) [ , ... ] { SUSPEND | RESUME } ]
Additional data metric functions to suspend or resume. Use a comma to separate each data metric function and its specified columns.
For details about the access control requirements for these actions, see DMF privileges.
External table column actions (extTableColumnAction
)¶
For all other external table modifications, see ALTER EXTERNAL TABLE.
ADD [ COLUMN ] [ IF NOT EXISTS ] <col_name> <col_type> AS ( <expr> ) [, ...]
Adds a new column to the external table.
If you are not sure if the column already exists, you can specify IF NOT EXISTS when adding the column. If the column already exists, ADD COLUMN has no effect on the existing column and does not result in an error.
This operation can be performed on multiple columns in the same command.
col_name
String that specifies the column identifier (i.e. name). All the requirements for table identifiers also apply to column identifiers.
For more information, see Identifier requirements.
col_type
String (constant) that specifies the data type for the column. The data type must match the result of
expr
for the column.For details about the data types that can be specified for table columns, see SQL data types reference.
expr
String that specifies the expression for the column. When queried, the column returns results derived from this expression.
External table columns are virtual columns, which are defined using an explicit expression. Add virtual columns as expressions using the VALUE column and/or the METADATA$FILENAME pseudocolumn:
- VALUE:
A VARIANT type column that represents a single row in the external file.
- CSV:
The VALUE column structures each row as an object with elements identified by column position (i.e.
{c1: <column_1_value>, c2: <column_2_value>, c3: <column_1_value> ...}
).For example, add a VARCHAR column named
mycol
that references the first column in the staged CSV files:mycol varchar as (value:c1::varchar)
- Semi-structured data:
Enclose element names and values in double-quotes. Traverse the path in the VALUE column using dot notation.
For example, suppose the following represents a single row of semi-structured data in a staged file:
{ "a":"1", "b": { "c":"2", "d":"3" } }
Add a VARCHAR column named
mycol
that references the nested repeatingc
element in the staged file:mycol varchar as (value:"b"."c"::varchar)
- METADATA$FILENAME:
A pseudocolumn that identifies the name of each staged data file included in the external table, including its path in the stage.
RENAME COLUMN col_name to new_col_name
Renames the specified column to a new name that is not currently used for any other columns in the external table.
DROP COLUMN [ IF EXISTS ] col_name
Removes the specified column from the external table.
If you are not sure if the column already exists, you can specify IF EXISTS when dropping the column. If the column does not exist, DROP COLUMN has no effect and does not result in an error.
Constraint actions (constraintAction
)¶
ADD CONSTRAINT
Adds an out-of-line integrity constraint to one or more columns in the table. To add an inline constraint (for a column), see Column Actions (in this topic).
RENAME CONSTRAINT constraint_name TO new_constraint_name
Renames the specified constraint.
ALTER | MODIFY CONSTRAINT ...
Alters the properties for the specified constraint.
DROP CONSTRAINT constraint_name | PRIMARY KEY | UNIQUE | FOREIGN KEY ( col_name [ , ... ] ) [ CASCADE | RESTRICT ]
Drops the specified constraint for the specified column or set of columns.
For detailed syntax and examples for adding or altering constraints, see CREATE | ALTER TABLE … CONSTRAINT.
Data Governance policy and tag actions (dataGovnPolicyTagAction
)¶
TAG tag_name = 'tag_value' [ , tag_name = 'tag_value' , ... ]
Specifies the tag name and the tag string value.
The tag value is always a string, and the maximum number of characters for the tag value is 256.
For information about specifying tags in a statement, see Tag quotas for objects and columns.
policy_name
Identifier for the policy; must be unique for your schema.
The following clauses apply to all table kinds that support row access policies, such as but not limited to tables, views, and event tables. To simplify, the clauses just refer to “table.”
ADD ROW ACCESS POLICY policy_name ON (col_name [ , ... ])
Adds a row access policy to the table.
At least one column name must be specified. Additional columns can be specified with a comma separating each column name. Use this expression to add a row access policy to both an event table and an external table.
DROP ROW ACCESS POLICY policy_name
Drops a row access policy from the table.
Use this clause to drop the policy from the table.
DROP ROW ACCESS POLICY policy_name, ADD ROW ACCESS POLICY policy_name ON ( col_name [ , ... ] )
Drops the row access policy that is set on the table and adds a row access policy to the same table in a single SQL statement.
DROP ALL ROW ACCESS POLICIES
Drops all row access policy associations from the table.
This expression is helpful when a row access policy is dropped from a schema before dropping the policy from an event table. Use this expression to drop row access policy associations from the table.
SET AGGREGATION POLICY policy_name
[ ENTITY KEY (col_name [ , ... ]) ] [ FORCE ]
Assigns an aggregation policy to the table.
Use the optional ENTITY KEY parameter to define which columns uniquely identity an entity within the table. For more information, see Implementing entity-level privacy with aggregation policies.
Use the optional FORCE parameter to atomically replace an existing aggregation policy with the new aggregation policy.
UNSET AGGREGATION POLICY
Detaches an aggregation policy from the table.
{ ALTER | MODIFY } [ COLUMN ] ...
USING ( col_name , cond_col_1 ... )
Specifies the arguments to pass into the conditional masking policy SQL expression.
The first column in the list specifies the column for the policy conditions to mask or tokenize the data and must match the column to which the masking policy is set.
The additional columns specify the columns to evaluate to determine whether to mask or tokenize the data in each row of the query result when a query is made on the first column.
If the USING clause is omitted, Snowflake treats the conditional masking policy as a normal masking policy.
FORCE
Replaces a masking or projection policy that is currently set on a column with a different policy in a single statement.
Note that using the
FORCE
keyword with a masking policy requires the data type of the policy in the ALTER TABLE statement (i.e. STRING) to match the data type of the masking policy currently set on the column (i.e. STRING).If a masking policy is not currently set on the column, specifying this keyword has no effect.
For details, see: Replace a masking policy on a column or Replace a projection policy.
Search optimization actions (searchOptimizationAction
)¶
ADD SEARCH OPTIMIZATION
Adds search optimization for the entire table or, if you specify the optional ON clause, for specific columns.
Note:
Search optimization can be expensive to maintain, especially if the data in the table changes frequently. For more information, see Search optimization cost estimation and management.
If you try to add search optimization on a materialized view, Snowflake returns an error message.
ON search_method_with_target [, search_method_with_target ... ]
Specifies that you want to configure search optimization for specific columns or VARIANT fields (rather than the entire table).
For
search_method_with_target
, use an expression with the following syntax:<search_method>( <target> [ , <target> , ... ] [ , ANALYZER => '<analyzer_name>' ] )
Where:
search_method
specifies one of the following methods that optimizes queries for a particular type of predicate:Search Method
Description
FULL_TEXT
Predicates that use VARCHAR (text), VARIANT, ARRAY, and OBJECT types.
EQUALITY
Equality and IN predicates.
SUBSTRING
Predicates that match substrings and regular expressions (for example, [ NOT ] LIKE, [ NOT ] ILIKE, [ NOT ] RLIKE, REGEXP_LIKE, etc.)
GEO
Predicates that use GEOGRAPHY types.
target
specifies the column, VARIANT field, or an asterisk (*).Depending on the value of
search_method
, you can specify a column or VARIANT field of one of the following types:Search Method
Supported Targets
FULL_TEXT
Columns of VARCHAR (text), VARIANT, ARRAY, and OBJECT data types, including paths to fields in VARIANTs.
EQUALITY
Columns of numerical, string, binary, and VARIANT data types, including paths to fields in VARIANTs.
SUBSTRING
Columns of string or VARIANT data types, including paths to fields in VARIANTs. Specify paths to fields as described above under
EQUALITY
; searches on nested fields are improved in the same way.GEO
Columns of the GEOGRAPHY data type.
To specify a VARIANT field, use dot or bracket notation (for example,
my_column:my_field_name.my_nested_field_name
ormy_column['my_field_name']['my_nested_field_name']
). You can also use a colon-delimited path to the field (for example,my_column:my_field_name:my_nested_field_name
).When you specify a VARIANT field, the configuration applies to all nested fields under that field. For example, if you specify
ON EQUALITY(src:a.b)
:This configuration can improve queries
on src:a.b
and on any nested fields (for example,src:a.b.c
,src:a.b.c.d
, etc.).This configuration does not affect queries that do not use the
src:a.b
prefix (for example,src:a
,src:z
, etc.).
To specify all applicable columns in the table as targets, use an asterisk (
*
).Note that you cannot specify both an asterisk and specific column names for a given search method. However, you can specify an asterisk in different search methods.
For example, you can specify the following expressions:
-- Allowed ON SUBSTRING(*) ON EQUALITY(*), SUBSTRING(*), GEO(*)
You cannot specify the following expressions:
-- Not allowed ON EQUALITY(*, c1) ON EQUALITY(c1, *) ON EQUALITY(v1:path, *) ON EQUALITY(c1), EQUALITY(*)
ANALYZER => 'analyzer_name'
specifies the name of the text analyzer, ifsearch_method
isFULL_TEXT
.When the
FULL_TEXT
search method is used and queries are executed with the SEARCH or SEARCH_IP function, the analyzer breaks the search terms (and the text from the column being searched) into tokens. A row matches if any of the tokens extracted from the search string matches a token extracted from any of the columns or fields being searched. The analyzer isn’t relevant when theFULL_TEXT
search method isn’t used or for queries that don’t use the SEARCH or SEARCH_IP function.The analyzer tokenizes a string by breaking it where it finds certain delimiters. These delimiters are not included in the resulting tokens, and empty tokens are not extracted.
This parameter accepts one of the following values:
DEFAULT_ANALYZER: Breaks text into tokens based on the following delimiters:
Character
Unicode code
Description
U+0020
Space
[
U+005B
Left square bracket
]
U+005D
Right square bracket
;
U+003B
Semicolon
<
U+003C
Less-than sign
>
U+003E
Greater-than sign
(
U+0028
Left parenthesis
)
U+0029
Right parenthesis
{
U+007B
Left curly bracket
}
U+007D
Right curly bracket
|
U+007C
Vertical bar
!
U+0021
Exclamation mark
,
U+002C
Comma
'
U+0027
Apostrophe
"
U+0022
Quotation mark
*
U+002A
Asterisk
&
U+0026
Ampersand
?
U+003F
Question mark
+
U+002B
Plus sign
/
U+002F
Slash
:
U+003A
Colon
=
U+003D
Equal sign
@
U+0040
At sign
.
U+002E
Period (full stop)
-
U+002D
Hyphen
$
U+0024
Dollar sign
%
U+0025
Percent sign
\
U+005C
Backslash
_
U+005F
Underscore (low line)
\n
U+000A
New line (line feed)
\r
U+000D
Carriage return
\t
U+0009
Horizontal tab
UNICODE_ANALYZER: Tokenizes based on Unicode segmentation rules that treat spaces and certain punctuation characters as delimiters. These internal rules are designed for natural language searches (in many different languages). For example, the default analyzer treats periods in IP addresses and apostrophes in contractions as delimiters, but the Unicode analyzer does not. See Using an analyzer to adjust search behavior.
For more information about the Unicode Text Segmentation algorithm, see https://unicode.org/reports/tr29/.
NO_OP_ANALYZER: Tokenizes neither the data nor the query string. A search term must exactly match the full text in a column or field, including case sensitivity; otherwise, the SEARCH function returns FALSE. Even if the query string looks like it contains multiple tokens (for example,
'sky blue'
), the column or field must equal the entire query string exactly. In this case, only'sky blue'
is a match;'sky'
and'blue'
are not matches.ENTITY_ANALYZER: Tokenizes the data for IP address searches.
This analyzer is used only for queries executed with the SEARCH_IP function.
To specify more than one search method on a target, use a comma to separate each subsequent method and target:
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c1), EQUALITY(c2, c3);
If you run the ALTER TABLE … ADD SEARCH OPTIMIZATION ON … command multiple times on the same table, each subsequent command adds to the existing configuration for the table. For example, suppose that you run the following commands:
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c1, c2); ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c3, c4);
This adds equality predicates for the columns c1, c2, c3, and c4 to the configuration for the table. This is equivalent to running the command:
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c1, c2, c3, c4);
For examples, see Enabling search optimization for specific columns.
DROP SEARCH OPTIMIZATION
Removes search optimization for the entire table or, if you specify the optional ON clause, from specific columns.
Note:
If a table has the search optimization property, then dropping the table and undropping it preserves the search optimization property.
Removing the search optimization property from a table and then adding it back incurs the same cost as adding it the first time.
ON search_method_with_target | column_name | expression_id [ , ... ]
Specifies that you want to drop the search optimization configuration for specific columns or VARIANT fields (rather than dropping search optimization for the entire table).
To identify the column configuration to drop, specify one of the following:
For
search_method_with_target
, specify a method for optimizing queries for one or more specific targets, which can be columns or VARIANT fields. Use the syntax described earlier.For
column_name
, specify the name of the column configured for search optimization. Specifying the column name drops all expressions for that column, including expressions that use VARIANT fields in the column.For
expression_id
, specify the ID for an expression listed in the output of the DESCRIBE SEARCH OPTIMIZATION command.
To specify more than one of these, use a comma between items.
You can specify any combination of search methods with targets, column names, and expression IDs.
For examples, see Dropping search optimization for specific columns.
Usage notes: General¶
Changes to a table are not automatically propagated to views created on that table. For example, if you drop a column in a table, and a view is defined to include that column, the view becomes invalid; the view is not adjusted to remove the column.
Dropping a column does not immediately free up the column’s storage space.
The space in each micro-partition is not reclaimed until that micro-partition is re-written. Write operations (insert, update, delete, etc.) on 1 or more rows in that micro-partition cause the micro-partition to be re-written. If you want to force space to be reclaimed, you can follow these steps:
Use a CREATE TABLE AS SELECT (CTAS) statement to create a new table that contains only the columns of the old table you want to keep.
Set the DATA_RETENTION_TIME_IN_DAYS parameter to
0
for the old table (optional).Drop the old table.
If the table is protected by the Time Travel feature, the space used by the Time Travel storage is not reclaimed until the Time Travel retention period expires.
If a new column with a default value is added to a table with existing rows, all of the existing rows are populated with the default value.
Adding a new column with a default value containing a function is not currently supported. The following error is returned:
Invalid column default expression (expr)
To alter a table, you must be using a role that has ownership privilege on the table.
To add clustering to a table, you must also have USAGE or OWNERSHIP privileges on the schema and database that contain the table.
For masking policies:
The
USING
clause and theFORCE
keyword are both optional; neither are required to set a masking policy on a column. TheUSING
clause and theFORCE
keyword can be used separately or together. For details, see:A single masking policy that uses conditional columns can be applied to multiple tables provided that the column structure of the table matches the columns specified in the policy.
When modifying one or more table columns with a masking policy or the table itself with a row access policy, use the POLICY_CONTEXT function to simulate a query on the column(s) protected by a masking policy and the table protected by a row access policy.
For row access policies:
Snowflake supports adding and dropping row access policies in a single SQL statement.
For example, to replace a row access policy that is already set on a table with a different policy, drop the row access policy first and then add the new row access policy.
For a given resource (i.e. table or view), to
ADD
orDROP
a row access policy you must have either the APPLY ROW ACCESS POLICY privilege on the schema, or the OWNERSHIP privilege on the resource and the APPLY privilege on the row access policy resource.A table or view can only be protected by one row access policy at a time. Adding a policy fails if the policy body refers to a table or view column that is protected by a row access policy or the column protected by a masking policy.
Similarly, adding a masking policy to a table column fails if the masking policy body refers to a table that is protected by a row access policy or another masking policy.
Row access policies cannot be applied to system views or table functions.
Similar to other DROP <object> operations, Snowflake returns an error if attempting to drop a row access policy from a resource that does not have a row access policy added to it.
If an object has both a row access policy and one or more masking policies, the row access policy is evaluated first.
If you create a foreign key, the columns in the REFERENCES clause must be listed in the same order as they were listed for the primary key. For example:
CREATE TABLE parent ... CONSTRAINT primary_key_1 PRIMARY KEY (c_1, c_2) ... CREATE TABLE child ... CONSTRAINT foreign_key_1 FOREIGN KEY (...) REFERENCES parent (c_1, c_2) ...
In both cases, the order of the columns is
c_1, c_2
. If the order of the columns in the foreign key had been different (for example,c_2, c_1
), the attempt to create the foreign key would have failed.
Regarding metadata:
Attention
Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata fields in Snowflake.
ALTER TABLE … CHANGE_TRACKING = TRUE
When a table is altered to enable change tracking, the table is locked for the duration of the operation. Locks can cause latency with some associated DDL/DML operations. For more information, refer to Resource locking.
Indexes in hybrid tables:
When you use the ALTER TABLE command to add or drop a unique or foreign-key constraint in a hybrid table, the corresponding index will also be created or dropped. For more information on indexes in a hybrid table, see CREATE INDEX.
Foreign-key constraints are supported only across hybrid tables that are storaged in the same database. The ability to move a hybrid table from one database to another is not supported. The primary key, unique, and foreign key constraints defined on hybrid tables have their RELY field marked as
TRUE
.A column that is used by an index cannot be dropped before the corresponding index is dropped.
Usage notes: Data metric functions¶
- Add a DMF to a table:
Prior to adding a data metric function to a table, you must:
Set the schedule for the data metric function to run. For details, see DATA_METRIC_SCHEDULE.
Configure the event table to store the results of calling the data metric function. For details, see View the DMF results.
Ensure that the table is view is not granted to a share because you cannot set a data metric function on a shared table or view.
Additionally:
You can add a data metric function to a table, external table, view, or materialized view. You cannot set a data metric function on any other kind of table, such as a dynamic table.
When you specify a column, Snowflake uses the ordinal position. If you rename a column after adding a data metric function to the table or view, the association of the data metric function to the column remains valid.
Only one data metric function of its kind can be added to a column. For example, a NULL_COUNT data metric function cannot be added to a single column twice.
If you drop a column after adding a data metric function that references the column, Snowflake cannot evaluate the data metric function.
Referencing a virtual column is not supported.
- Drop a DMF from a table:
Drop the data metric function from the table before using the DROP FUNCTION command to remove the data metric function from the system.
You can use the DATA_METRIC_FUNCTION_REFERENCES function to identify the table and view objects that have a data metric function set on them.
- Schedule a DMF
It takes ten minutes for the schedule to become effective once the schedule is set.
Similarly, it takes ten minutes once the DMF is unset for the scheduling changes to take effect. For more information, see Schedule your DMFs to run.
Examples¶
The following sections provide examples of using the ALTER COLUMN command:
Renaming a table¶
The following creates a table named t1
:
CREATE OR REPLACE TABLE t1(a1 number);
SHOW TABLES LIKE 't1';
+-------------------------------+------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+-----------------+-------------+-------------------------+-----------------+----------+--------+
| created_on | name | database_name | schema_name | kind | comment | cluster_by | rows | bytes | owner | retention_time | change_tracking | is_external | enable_schema_evolution | owner_role_type | is_event | budget |
|-------------------------------+------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+-----------------+-------------+-------------------------+-----------------+----------+--------|
| 2023-10-19 10:37:04.858 -0700 | T1 | TESTDB | MY_SCHEMA | TABLE | | | 0 | 0 | PUBLIC | 1 | OFF | N | N | ROLE | N | NULL |
+-------------------------------+------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+-----------------+-------------+-------------------------+-----------------+----------+--------+
The following statement changes the name of the table to tt1
:
ALTER TABLE t1 RENAME TO tt1;
SHOW TABLES LIKE 'tt1';
+-------------------------------+------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+-----------------+-------------+-------------------------+-----------------+----------+--------+
| created_on | name | database_name | schema_name | kind | comment | cluster_by | rows | bytes | owner | retention_time | change_tracking | is_external | enable_schema_evolution | owner_role_type | is_event | budget |
|-------------------------------+------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+-----------------+-------------+-------------------------+-----------------+----------+--------|
| 2023-10-19 10:37:04.858 -0700 | TT1 | TESTDB | MY_SCHEMA | TABLE | | | 0 | 0 | PUBLIC | 1 | OFF | N | N | ROLE | N | NULL |
+-------------------------------+------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+-----------------+-------------+-------------------------+-----------------+----------+--------+
Swapping tables¶
The following statements create tables named t1
and t2
:
CREATE OR REPLACE TABLE t1(a1 NUMBER, a2 VARCHAR, a3 DATE);
CREATE OR REPLACE TABLE t2(b1 VARCHAR);
DESC TABLE t1;
+------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
| name | type | kind | null? | default | primary key | unique key | check | expression | comment | policy name |
|------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------|
| A1 | NUMBER(38,0) | COLUMN | Y | NULL | N | N | NULL | NULL | NULL | NULL |
| A2 | VARCHAR(16777216) | COLUMN | Y | NULL | N | N | NULL | NULL | NULL | NULL |
| A3 | DATE | COLUMN | Y | NULL | N | N | NULL | NULL | NULL | NULL |
+------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
DESC TABLE t2;
+------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
| name | type | kind | null? | default | primary key | unique key | check | expression | comment | policy name |
|------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------|
| B1 | VARCHAR(16777216) | COLUMN | Y | NULL | N | N | NULL | NULL | NULL | NULL |
+------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
The following statement swaps table t1
with table t2
:
ALTER TABLE t1 SWAP WITH t2;
DESC TABLE t1;
+------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
| name | type | kind | null? | default | primary key | unique key | check | expression | comment | policy name |
|------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------|
| B1 | VARCHAR(16777216) | COLUMN | Y | NULL | N | N | NULL | NULL | NULL | NULL |
+------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
DESC TABLE t2;
+------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
| name | type | kind | null? | default | primary key | unique key | check | expression | comment | policy name |
|------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------|
| A1 | NUMBER(38,0) | COLUMN | Y | NULL | N | N | NULL | NULL | NULL | NULL |
| A2 | VARCHAR(16777216) | COLUMN | Y | NULL | N | N | NULL | NULL | NULL | NULL |
| A3 | DATE | COLUMN | Y | NULL | N | N | NULL | NULL | NULL | NULL |
+------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
Adding columns¶
The following creates a table named t1
:
CREATE OR REPLACE TABLE t1(a1 NUMBER);
DESC TABLE t1;
+------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
| name | type | kind | null? | default | primary key | unique key | check | expression | comment | policy name |
|------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------|
| A1 | NUMBER(38,0) | COLUMN | Y | NULL | N | N | NULL | NULL | NULL | NULL |
+------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
The following statement adds a column named a2
to this table:
ALTER TABLE t1 ADD COLUMN a2 NUMBER;
The following statement adds a column named a3
with a NOT NULL constraint:
ALTER TABLE t1 ADD COLUMN a3 NUMBER NOT NULL;
The following statement adds a column named a4
with a default value and a NOT NULL constraint:
ALTER TABLE t1 ADD COLUMN a4 NUMBER DEFAULT 0 NOT NULL;
The following statement adds a VARCHAR column named a5
with a language-specific
collation specification:
ALTER TABLE t1 ADD COLUMN a5 VARCHAR COLLATE 'en_US';
DESC TABLE t1;
+------+-----------------------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
| name | type | kind | null? | default | primary key | unique key | check | expression | comment | policy name |
|------+-----------------------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------|
| A1 | NUMBER(38,0) | COLUMN | Y | NULL | N | N | NULL | NULL | NULL | NULL |
| A2 | NUMBER(38,0) | COLUMN | Y | NULL | N | N | NULL | NULL | NULL | NULL |
| A3 | NUMBER(38,0) | COLUMN | N | NULL | N | N | NULL | NULL | NULL | NULL |
| A4 | NUMBER(38,0) | COLUMN | N | 0 | N | N | NULL | NULL | NULL | NULL |
| A5 | VARCHAR(16777216) COLLATE 'en_us' | COLUMN | Y | NULL | N | N | NULL | NULL | NULL | NULL |
+------+-----------------------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
The following statement uses the IF NOT EXISTS clause to add a column named a2
only if the column does not exist. There is
an existing column named a2
. Specifying the IF NOT EXISTS clause prevents the statement from failing with an error.
ALTER TABLE t1 ADD COLUMN IF NOT EXISTS a2 NUMBER;
As shown in the output of the DESCRIBE TABLE command, the statement above has no effect on the existing column named a2
:
DESC TABLE t1;
+------+-----------------------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
| name | type | kind | null? | default | primary key | unique key | check | expression | comment | policy name |
|------+-----------------------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------|
| A1 | NUMBER(38,0) | COLUMN | Y | NULL | N | N | NULL | NULL | NULL | NULL |
| A2 | NUMBER(38,0) | COLUMN | Y | NULL | N | N | NULL | NULL | NULL | NULL |
| A3 | NUMBER(38,0) | COLUMN | N | NULL | N | N | NULL | NULL | NULL | NULL |
| A4 | NUMBER(38,0) | COLUMN | N | 0 | N | N | NULL | NULL | NULL | NULL |
| A5 | VARCHAR(16777216) COLLATE 'en_us' | COLUMN | Y | NULL | N | N | NULL | NULL | NULL | NULL |
+------+-----------------------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
Renaming columns¶
The following statement changes the name of the column a1
to b1
:
ALTER TABLE t1 RENAME COLUMN a1 TO b1;
DESC TABLE t1;
+------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
| name | type | kind | null? | default | primary key | unique key | check | expression | comment | policy name |
|------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------|
| B1 | NUMBER(38,0) | COLUMN | Y | NULL | N | N | NULL | NULL | NULL | NULL |
| A2 | NUMBER(38,0) | COLUMN | Y | NULL | N | N | NULL | NULL | NULL | NULL |
| A3 | NUMBER(38,0) | COLUMN | N | NULL | N | N | NULL | NULL | NULL | NULL |
| A4 | NUMBER(38,0) | COLUMN | N | 0 | N | N | NULL | NULL | NULL | NULL |
+------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
Dropping columns¶
The following statement drops the column a2
:
ALTER TABLE t1 DROP COLUMN a2;
DESC TABLE t1;
+------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
| name | type | kind | null? | default | primary key | unique key | check | expression | comment | policy name |
|------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------|
| B1 | NUMBER(38,0) | COLUMN | Y | NULL | N | N | NULL | NULL | NULL | NULL |
| A3 | NUMBER(38,0) | COLUMN | N | NULL | N | N | NULL | NULL | NULL | NULL |
| A4 | NUMBER(38,0) | COLUMN | N | 0 | N | N | NULL | NULL | NULL | NULL |
+------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
The following statement uses the IF EXISTS clause to drop a column named a2
only if the column exists. There is no existing
column named a2
. Specifying the IF EXISTS clause prevents the statement from failing with an error.
ALTER TABLE t1 DROP COLUMN IF EXISTS a2;
As shown in the output of the DESCRIBE TABLE command, the statement above has no effect on the existing table:
+------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
| name | type | kind | null? | default | primary key | unique key | check | expression | comment | policy name |
|------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------|
| B1 | NUMBER(38,0) | COLUMN | Y | NULL | N | N | NULL | NULL | NULL | NULL |
| A3 | NUMBER(38,0) | COLUMN | N | NULL | N | N | NULL | NULL | NULL | NULL |
| A4 | NUMBER(38,0) | COLUMN | N | 0 | N | N | NULL | NULL | NULL | NULL |
+------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
Adding, renaming, and dropping columns in an external table¶
The following statement creates an external table named exttable1
:
CREATE EXTERNAL TABLE exttable1
LOCATION=@mystage/logs/
AUTO_REFRESH = true
FILE_FORMAT = (TYPE = PARQUET)
;
DESC EXTERNAL TABLE exttable1;
+-----------+-------------------+-----------+-------+---------+-------------+------------+-------+----------------------------------------------------------+-----------------------+
| name | type | kind | null? | default | primary key | unique key | check | expression | comment |
|-----------+-------------------+-----------+-------+---------+-------------+------------+-------+----------------------------------------------------------+-----------------------|
| VALUE | VARIANT | COLUMN | Y | NULL | N | N | NULL | NULL | The value of this row |
+-----------+-------------------+-----------+-------+---------+-------------+------------+-------+----------------------------------------------------------+-----------------------+
The following statement adds a new column named a1
to the external table:
ALTER TABLE exttable1 ADD COLUMN a1 VARCHAR AS (value:a1::VARCHAR);
DESC EXTERNAL TABLE exttable1;
+-----------+-------------------+-----------+-------+---------+-------------+------------+-------+----------------------------------------------------------+-----------------------+
| name | type | kind | null? | default | primary key | unique key | check | expression | comment |
|-----------+-------------------+-----------+-------+---------+-------------+------------+-------+----------------------------------------------------------+-----------------------|
| VALUE | VARIANT | COLUMN | Y | NULL | N | N | NULL | NULL | The value of this row |
| A1 | VARCHAR(16777216) | VIRTUAL | Y | NULL | N | N | NULL | TO_CHAR(GET(VALUE, 'a1')) | NULL |
+-----------+-------------------+-----------+-------+---------+-------------+------------+-------+----------------------------------------------------------+-----------------------+
The following statement changes the name of the a1
column to b1
:
ALTER TABLE exttable1 RENAME COLUMN a1 TO b1;
DESC EXTERNAL TABLE exttable1;
+-----------+-------------------+-----------+-------+---------+-------------+------------+-------+----------------------------------------------------------+-----------------------+
| name | type | kind | null? | default | primary key | unique key | check | expression | comment |
|-----------+-------------------+-----------+-------+---------+-------------+------------+-------+----------------------------------------------------------+-----------------------|
| VALUE | VARIANT | COLUMN | Y | NULL | N | N | NULL | NULL | The value of this row |
| B1 | VARCHAR(16777216) | VIRTUAL | Y | NULL | N | N | NULL | TO_CHAR(GET(VALUE, 'a1')) | NULL |
+-----------+-------------------+-----------+-------+---------+-------------+------------+-------+----------------------------------------------------------+-----------------------+
The following statement drops the column named b1
:
ALTER TABLE exttable1 DROP COLUMN b1;
DESC EXTERNAL TABLE exttable1;
+-----------+-------------------+-----------+-------+---------+-------------+------------+-------+----------------------------------------------------------+-----------------------+
| name | type | kind | null? | default | primary key | unique key | check | expression | comment |
|-----------+-------------------+-----------+-------+---------+-------------+------------+-------+----------------------------------------------------------+-----------------------|
| VALUE | VARIANT | COLUMN | Y | NULL | N | N | NULL | NULL | The value of this row |
+-----------+-------------------+-----------+-------+---------+-------------+------------+-------+----------------------------------------------------------+-----------------------+
Changing the order of clustering keys¶
The following statement creates a table named t1
that clusters by the id
and date
columns:
CREATE OR REPLACE TABLE T1 (id NUMBER, date TIMESTAMP_NTZ, name STRING) CLUSTER BY (id, date);
SHOW TABLES LIKE 'T1';
---------------------------------+------+---------------+-------------+-------+---------+------------+------+-------+--------------+----------------+
created_on | name | database_name | schema_name | kind | comment | cluster_by | rows | bytes | owner | retention_time |
---------------------------------+------+---------------+-------------+-------+---------+------------+------+-------+--------------+----------------+
Tue, 21 Jun 2016 15:42:12 -0700 | T1 | TESTDB | TESTSCHEMA | TABLE | | (ID,DATE) | 0 | 0 | ACCOUNTADMIN | 1 |
---------------------------------+------+---------------+-------------+-------+---------+------------+------+-------+--------------+----------------+
The following statement changes the order of the clustering key:
ALTER TABLE t1 CLUSTER BY (date, id);
SHOW TABLES LIKE 'T1';
---------------------------------+------+---------------+-------------+-------+---------+------------+------+-------+--------------+----------------+
created_on | name | database_name | schema_name | kind | comment | cluster_by | rows | bytes | owner | retention_time |
---------------------------------+------+---------------+-------------+-------+---------+------------+------+-------+--------------+----------------+
Tue, 21 Jun 2016 15:42:12 -0700 | T1 | TESTDB | TESTSCHEMA | TABLE | | (DATE,ID) | 0 | 0 | ACCOUNTADMIN | 1 |
---------------------------------+------+---------------+-------------+-------+---------+------------+------+-------+--------------+----------------+
Adding and dropping row access policies¶
The following example adds a row access policy on a table while specifying a single column. After setting the policy, you can verify by checking the information schema.
alter table t1 add row access policy rap_t1 on (empl_id);
The following example adds a row access policy while specifying two columns in a single table.
alter table t1 add row access policy rap_test2 on (cost, item);
The following example drops a row access policy from a table. Verify the policies were dropped by querying the information schema.
alter table t1 drop row access policy rap_v1;
The following example shows how to combine adding and dropping row access policies in a single SQL statement for a table. Verify the results by checking the information schema.
alter table t1 drop row access policy rap_t1_version_1, add row access policy rap_t1_version_2 on (empl_id);
Schedule for a data metric function to run¶
Set the data metric function schedule to run every 5 minutes:
ALTER TABLE hr.tables.empl_info SET DATA_METRIC_SCHEDULE = '5 MINUTE';
Set the data metric function schedule to run at 8:00 AM daily:
ALTER TABLE hr.tables.empl_info SET DATA_METRIC_SCHEDULE = 'USING CRON 0 8 * * * UTC';
Set the data metric function schedule to run at 8:00 AM on weekdays only:
ALTER TABLE hr.tables.empl_info SET DATA_METRIC_SCHEDULE = 'USING CRON 0 8 * * MON,TUE,WED,THU,FRI UTC';
Set the data metric function schedule to run three times daily at 0600, 1200, and 1800 UTC:
ALTER TABLE hr.tables.empl_info SET DATA_METRIC_SCHEDULE = 'USING CRON 0 6,12,18 * * * UTC';
Set the data metric function to run when a general DML operation, such as inserting a new row, modifies the table:
ALTER TABLE hr.tables.empl_info SET DATA_METRIC_SCHEDULE = 'TRIGGER_ON_CHANGES';