A RetroSearch Logo

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Search Query:

Showing content from https://docs.snowflake.com/en/sql-reference/sql/alter-dynamic-table below:

Website Navigation


ALTER DYNAMIC TABLE | Snowflake Documentation

ALTER DYNAMIC TABLE

Modifies the properties of a dynamic table.

See also:

CREATE DYNAMIC TABLE, DESCRIBE DYNAMIC TABLE, DROP DYNAMIC TABLE, SHOW DYNAMIC TABLES

Syntax 
ALTER DYNAMIC TABLE [ IF EXISTS ] <name> { SUSPEND | RESUME }

ALTER DYNAMIC TABLE [ IF EXISTS ] <name> RENAME TO <new_name>

ALTER DYNAMIC TABLE [ IF EXISTS ] <name> SWAP WITH <target_dynamic_table_name>

ALTER DYNAMIC TABLE [ IF EXISTS ] <name> REFRESH [ COPY SESSION ]

ALTER DYNAMIC TABLE [ IF EXISTS ] <name> { clusteringAction }

ALTER DYNAMIC TABLE [ IF EXISTS ] <name> { tableColumnCommentAction }

ALTER DYNAMIC TABLE <name> { SET | UNSET } COMMENT = '<string_literal>'

ALTER DYNAMIC TABLE [ IF EXISTS ] <name> dataGovnPolicyTagAction

ALTER DYNAMIC TABLE [ IF EXISTS ] <name> searchOptimizationAction

ALTER DYNAMIC TABLE [ IF EXISTS ] <name> SET
  [ TARGET_LAG = { '<num> { seconds | minutes | hours | days }'  | DOWNSTREAM } ]
  [ WAREHOUSE = <warehouse_name> ]
  [ DATA_RETENTION_TIME_IN_DAYS = <integer> ]
  [ MAX_DATA_EXTENSION_TIME_IN_DAYS = <integer> ]
  [ DEFAULT_DDL_COLLATION = '<collation_specification>' ]
  [ LOG_LEVEL = '<log_level>' ]
  [ CONTACT ( <purpose> = <contact_name> [ , <purpose> = <contact_name> ... ] ) ]
  [ IMMUTABLE WHERE ( <expr> ) ]

ALTER DYNAMIC TABLE [ IF EXISTS ] <name> UNSET
  [ DATA_RETENTION_TIME_IN_DAYS ],
  [ MAX_DATA_EXTENSION_TIME_IN_DAYS ],
  [ DEFAULT_DDL_COLLATION ]
  [ LOG_LEVEL ]
  [ CONTACT <purpose> ]
  [ IMMUTABLE ]

Copy

Where:

clusteringAction ::=
  {
    CLUSTER BY ( <expr> [ , <expr> , ... ] )
    | { SUSPEND | RESUME } RECLUSTER
    | DROP CLUSTERING KEY
  }

Copy

For more information, see Clustering Keys & Clustered Tables.

tableCommentAction ::=
  {
    ALTER | MODIFY [ ( ]
                           [ COLUMN ] <col1_name> COMMENT '<string>'
                         , [ COLUMN ] <col1_name> UNSET COMMENT
                       [ , ... ]
                   [ ) ]
  }

Copy

dataGovnPolicyTagAction ::=
  {
      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
  }
  |
  {
    { 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 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>
        SET PROJECTION POLICY <policy_name>
          [ FORCE ]
      | UNSET PROJECTION POLICY
}
|
  { ALTER | MODIFY } [ COLUMN ] <col1_name> UNSET TAG <tag_name> [ , <tag_name> ... ]
                  , [ COLUMN ] <col2_name> UNSET TAG <tag_name> [ , <tag_name> ... ]
  }
  |
  {
      SET TAG <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' ... ]
    | UNSET TAG <tag_name> [ , <tag_name> ... ]
  }

Copy

searchOptimizationAction ::=
  {
    ADD SEARCH OPTIMIZATION [
      ON <search_method_with_target> [ , <search_method_with_target> ... ]
        [ EQUALITY ]
      ]

    | DROP SEARCH OPTIMIZATION [
      ON { <search_method_with_target> | <column_name> | <expression_id> }
        [ EQUALITY ]
        [ , ... ]
      ]

    | SUSPEND SEARCH OPTIMIZATION [
       ON { <search_method_with_target> | <column_name> | <expression_id> }
          [ , ... ]
     ]

    | RESUME SEARCH OPTIMIZATION [
       ON { <search_method_with_target> | <column_name> | <expression_id> }
          [ , ... ]
     ]
  }

Copy

For details, see Search optimization actions (searchOptimizationAction).

Parameters
name

Identifier for the dynamic 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.

For more information, see Identifier requirements.

SUSPEND | RESUME

Specifies the action to perform on the dynamic table:

RENAME TO new_name

Renames the specified dynamic table with a new identifier that is not currently used by any other dynamic tables in the schema.

Renaming a dynamic table requires the CREATE DYNAMIC TABLE privilege on the schema for the dynamic table.

You can also move the dynamic table to a different database and/or schema while optionally renaming the dynamic table. To do so, specify a qualified new_name value that includes the new database and/or schema name in the form db_name.schema_name.new_name or schema_name.new_name, respectively.

The following restrictions apply:

SWAP WITH target_dynamic_table_name

Swaps two dynamic tables in a single transaction. The role used to perform this operation must have OWNERSHIP privileges on both dynamic tables.

The following restrictions apply:

REFRESH [ COPY SESSION ]

Specifies that the dynamic table should be manually refreshed.

Both user-suspended and auto-suspended dynamic tables can be manually refreshed. Manually refreshed dynamic tables return MANUAL as the output for refresh_trigger in the DYNAMIC_TABLE_REFRESH_HISTORY function.

Note that refreshing a dynamic table also refreshes all upstream dynamic tables as of the same data timestamp. For more information, see Alter the warehouse or target lag for dynamic tables.

For information on dynamic table refresh status, see DYNAMIC_TABLE_REFRESH_HISTORY.

COPY SESSION

Runs the refresh operation in a copy of the current session using the current user and warehouse.

This only applies to a single manual refresh; it does not permanently update the credentials for the dynamic table. Use the GRANT OWNERSHIP command to transfer the ownership for scheduled refreshes. For more information, see Transfer ownership.

The primary role is the role that owns the dynamic table and secondary roles will match the DEFAULT_SECONDARY_ROLES property of the user.

SET ...

Specifies one or more properties/parameters to set for the table (separated by blank spaces, commas, or new lines):

TARGET_LAG = { num { seconds | minutes | hours | days } | DOWNSTREAM }

Specifies the target lag for the dynamic table:

'num seconds | minutes | hours | days'

Specifies the maximum amount of time that the dynamic table’s content should lag behind updates to the base tables.

For example:

  • If the data in the dynamic table should lag by no more than 5 minutes, specify 5 minutes.

  • If the data in the dynamic table should lag by no more than 5 hours, specify 5 hours.

The minimum value is 1 minute. If a dynamic table A depends on another dynamic table B, the minimum lag for A must be greater than or equal to the lag for B.

DOWNSTREAM

Specifies that the dynamic table should be refreshed if any dynamic table downstream of it is refreshed.

WAREHOUSE = warehouse_name

Specifies the name of the warehouse that provides the compute resources for refreshing the dynamic table.

The owner role of the dynamic table must have the USAGE privilege on this warehouse.

DATA_RETENTION_TIME_IN_DAYS = integer

Object-level parameter that modifies the retention period for the dynamic table for Time Travel. For more details, see Understanding & using Time Travel and Working with Temporary and Transient Tables.

For a detailed description of this parameter and more information about object parameters, see Parameters.

Values:

  • Standard Edition: 0 or 1

  • Enterprise Edition:

    • 0 to 90 for permanent dynamic tables

    • 0 or 1 for transient dynamic tables

Note

A value of 0 effectively disables Time Travel for the dynamic table.

MAX_DATA_EXTENSION_TIME_IN_DAYS = integer

Object parameter that specifies the maximum number of days Snowflake can extend the data retention period to prevent streams on the dynamic table from becoming stale.

For a detailed description of this parameter, see MAX_DATA_EXTENSION_TIME_IN_DAYS.

DEFAULT_DDL_COLLATION = 'collation_specification'

Specifies a default collation specification for any new columns added to the dynamic table.

Setting this parameter does not change the collation specification for any existing columns.

For more information, see DEFAULT_DDL_COLLATION.

LOG_LEVEL = 'log_level'

Specifies the severity level of events for this dynamic table that are ingested and made available in the active event table. Events at the specified level (and at more severe levels) are ingested.

For more information about levels, see LOG_LEVEL. For information about setting the log level, see Setting levels for logging, metrics, and tracing.

CONTACT purpose = contact [ , purpose = contact ... ]

Associate the existing object with one or more contacts.

You cannot set the CONTACT property with other properties in the same statement.

IMMUTABLE WHERE

Specifies a condition that defines the immutable portion of the dynamic table. For more information, see Create dynamic tables with immutability constraints.

UNSET ...

Specifies one or more properties/parameters to unset for the dynamic table, which resets them back to their defaults:

Clustering actions (clusteringAction)
CLUSTER BY ( expr [ , expr , ... ] )

Specifies (or modifies) one or more table columns or column expressions as the clustering key for the dynamic table. These are the columns/expressions for which clustering is maintained by Automatic Clustering. Before you specify a clustering key for a dynamic table, you should understand micro-partitions. For more information, see Understanding Snowflake Table Structures.

Note the following when using clustering keys with dynamic tables:

SUSPEND | RESUME RECLUSTER

Enables or disables Automatic Clustering for the dynamic table.

DROP CLUSTERING KEY

Drops the clustering key for the dynamic table.

For more information about clustering keys and reclustering, see Understanding Snowflake Table Structures.

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 quota for objects.

policy_name

Identifier for the policy; must be unique for your schema.

ADD ROW ACCESS POLICY policy_name ON (col_name [ , ... ])

Adds a row access policy to the dynamic table.

At least one column name must be specified. Additional columns can be specified with a comma separating each column name.

DROP ROW ACCESS POLICY policy_name

Drops a row access policy from the dynamic 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 dynamic table and adds a row access policy to the same dynamic table in a single SQL statement.

DROP ALL ROW ACCESS POLICIES

Drops all row access policy associations from the dynamic table.

{ ALTER | MODIFY } [ COLUMN ] ...
USING ( col_name , cond_col_1 ... )

Specifies the arguments to pass into the conditional masking policy.

The first column in the list specifies the data to be masked or tokenized based on policy conditions and must match the column to which the masking policy is applied.

The additional columns specify which data to evaluate for masking or tokenization in each row of the query result when selecting from the first column.

If the USING clause is omitted, Snowflake treats the conditional masking policy as a normal masking policy.

SET AGGREGATION POLICY {policy_name}
[ ENTITY KEY ({col_name} [ , ... ]) ] [ FORCE ]

Assigns an aggregation policy to the dynamic table.

Use the optional ENTITY KEY parameter to define which columns uniquely identity an entity within the dynamic 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 dynamic table.

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 DYNAMIC 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 dynamic table or, if you specify the optional ON clause, for specific columns.

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.

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 dynamic table).

For search_method_with_target, use an expression with the following syntax:

<search_method>(<target> [, ...])

Copy

Where:

To specify all applicable columns in the table as targets, use an asterisk (*).

Note that you can’t 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:

ON SUBSTRING(*)
ON EQUALITY(*), SUBSTRING(*), GEO(*)

Copy

You can’t specify the following expressions:

ON EQUALITY(*, c1)
ON EQUALITY(c1, *)
ON EQUALITY(v1:path, *)
ON EQUALITY(c1), EQUALITY(*)

Copy

To specify more than one search method on a target, use a comma to separate each subsequent method and target:

ALTER DYNAMIC TABLE my_dynamic_table ADD SEARCH OPTIMIZATION ON EQUALITY(c1), EQUALITY(c2, c3);

Copy

If you run the ALTER DYNAMIC TABLE … ADD SEARCH OPTIMIZATION ON … command multiple times on the same table, each subsequent command adds to the existing configuration for the table. For instance, suppose that you run the following commands:

ALTER DYNAMIC TABLE my_dynamic_table ADD SEARCH OPTIMIZATION ON EQUALITY(c1, c2);
ALTER DYNAMIC TABLE my_dynamic_table ADD SEARCH OPTIMIZATION ON EQUALITY(c3, c4);

Copy

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 DYNAMIC TABLE my_dynamic_table ADD SEARCH OPTIMIZATION ON EQUALITY(c1, c2, c3, c4);

Copy

For examples, see Enabling search optimization for specific columns.

DROP SEARCH OPTIMIZATION

Removes search optimization for the entire dynamic table or, if you specify the optional ON clause, from specific columns.

The following restrictions apply:

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 dynamic table).

To identify the column configuration to drop, specify one of the following:

You can specify any combination of search methods with targets, column names, and expression IDs using a comma between items.

For examples, see Dropping search optimization for specific columns.

Access control requirements

A role used to execute this operation must have the following privileges at a minimum:

Privilege

Object

Notes

OWNERSHIP or OPERATE

The dynamic table you want to alter.

Some actions are only supported with the OWNERSHIP privilege. For more information, see Privileges to alter a dynamic table.

The USAGE privilege on the parent database and schema are required to perform operations on any object in a schema.

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

Usage notes Examples

Change the target lag time of a dynamic table named my_dynamic_table to 1 hour:

ALTER DYNAMIC TABLE my_dynamic_table SET
  TARGET_LAG = '1 hour';

Copy

Specify downstream target lag for my_dynamic_table:

ALTER DYNAMIC TABLE my_dynamic_table SET TARGET_LAG = DOWNSTREAM;

Copy

Suspend a dynamic table:

ALTER DYNAMIC TABLE my_dynamic_table SUSPEND;

Copy

Resume a dynamic table:

ALTER DYNAMIC TABLE my_dynamic_table RESUME;

Copy

Rename my_dynamic_table:

ALTER DYNAMIC TABLE my_dynamic_table RENAME TO my_updated_dynamic_table;

Copy

Swap my_dynamic_table with my_new_dynamic_table:

ALTER DYNAMIC TABLE my_dynamic_table SWAP WITH my_new_dynamic_table;

Copy

Change the clustering key for a dynamic table:

ALTER DYNAMIC TABLE my_dynamic_table CLUSTER BY (date);

Copy

Remove clustering from a dynamic table:

ALTER DYNAMIC TABLE my_dynamic_table DROP CLUSTERING KEY;

Copy

Perform a manual refresh of my_dynamic_table using the user, secondary roles, and warehouse settings from the current session. This ensures that the refresh operation runs with the exact context of the user session.

ALTER DYNAMIC TABLE my_dynamic_table REFRESH COPY SESSION

Copy

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4