Categories:

Table, View, & Sequence DDL

ALTER EXTERNAL TABLE

Modifies the properties, columns, or constraints for an existing external table.

See also:

CREATE EXTERNAL TABLE , DROP EXTERNAL TABLE , SHOW EXTERNAL TABLES , DESCRIBE EXTERNAL TABLE

In this Topic:

Syntax

ALTER EXTERNAL TABLE [ IF EXISTS ] <name> REFRESH [ '<relative-path>' ]

ALTER EXTERNAL TABLE [ IF EXISTS ] <name> ADD FILES ( '<path>/[<filename>]' [ , '<path>/[<filename>'] ] )

ALTER EXTERNAL TABLE [ IF EXISTS ] <name> REMOVE FILES ( '<path>/[<filename>]' [ , '<path>/[<filename>]' ] )

ALTER EXTERNAL TABLE [ IF EXISTS ] <name> SET
  [ AUTO_REFRESH = { TRUE | FALSE } ]
  [ TAG <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' ... ] ]

ALTER EXTERNAL TABLE [ IF EXISTS ] <name> UNSET
  TAG <tag_name> [ , <tag_name> ... ]

Partitions added and removed manually

ALTER EXTERNAL TABLE <name> [ IF EXISTS ] ADD PARTITION ( <part_col_name> = '<string>' [ , <part_col_name> = '<string>' ] ) LOCATION '<path>'

ALTER EXTERNAL TABLE <name> [ IF EXISTS ] DROP PARTITION LOCATION '<path>'

Parameters

name

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

REFRESH [ 'relative-path' ]

Accesses the staged data files referenced in the external table definition and updates the table metadata:

  • New files in the path are added to the table metadata.

  • Changes to files in the path are updated in the table metadata.

  • Files no longer in the path are removed from the table metadata.

Optionally specify a relative path to refresh the metadata for a specific subset of the data files.

Using this parameter only needs to be done once, when the external table is created. This step synchronizes the metadata with the latest set of associated files in the stage and path in the external table definition. Also, this step ensures the external table can read the data files in the specified stage and path, and that no files were missed in the external table definition.

Note

  • This parameter is not supported by partitioned external tables when partitions are added manually by the object owner (i.e. when PARTITION_TYPE = USER_SPECIFIED).

  • If TABLE_FORMAT = DELTA is set on the external table, REFRESH does not support a relative path to refresh the metadata for a specific subset of the data files.

ADD FILES

Adds the specified comma-separated list of files to the external table metadata. Typically, the ADD FILES and REMOVE FILES parameters are used to refresh the external table metadata manually (i.e. when AUTO_REFRESH = FALSE). The file references are expressed as paths relative to [ WITH ] LOCATION in the external table definition. For information, see CREATE EXTERNAL TABLE.

List the path and filename of each file.

The ALTER EXTERNAL TABLE statement automatically refreshes the external table metadata and adds the files, if present in the specified storage location.

This parameter is not supported by partitioned external tables when partitions are added manually by the object owner (i.e. when PARTITION_TYPE = USER_SPECIFIED).

REMOVE FILES

Removes the specified comma-separated list of files from the external table metadata. Typically, the ADD FILES and REMOVE FILES parameters are used to refresh the external table metadata manually (i.e. when AUTO_REFRESH = FALSE). The file references are expressed as paths relative to [ WITH ] LOCATION in the external table definition. For information, see CREATE EXTERNAL TABLE.

List the path and filename of each file.

The ALTER EXTERNAL TABLE statement automatically refreshes the external table metadata and removes the files.

This parameter is not supported by partitioned external tables when partitions are added manually by the object owner (i.e. when PARTITION_TYPE = USER_SPECIFIED).

SET ...

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

AUTO_REFRESH = TRUE | FALSE

Specifies whether Snowflake should enable triggering automatic refreshes of the external table metadata when new or updated data files are available in the named external stage specified in the [ WITH ] LOCATION = setting.

Note

  • You must configure an event notification for your storage location to notify Snowflake when new or updated data is available to read into the external table metadata. For more information, see the instructions for your cloud storage service:

  • Currently, the ability to automatically refresh the metadata is not available for external tables that reference Google Cloud Storage stages.

    As a workaround, we suggest following our best practices for staging your data files and periodically executing an ALTER EXTERNAL TABLE … REFRESH statement to register any missed files. For satisfactory performance, we also recommend using a selective path prefix with ALTER EXTERNAL TABLE to reduce the number of files that need to be listed and checked if they have been registered already (e.g. bucket_name/YYYY/MM/DD/ or even bucket_name/YYYY/MM/DD/HH/ depending on your volume).

  • This parameter is not supported by partitioned external tables when partitions are added manually by the object owner (i.e. when PARTITION_TYPE = USER_SPECIFIED).

TRUE

Snowflake enables triggering automatic refreshes of the external table metadata.

FALSE

Snowflake does not enable triggering automatic refreshes of the external table metadata. You must manually refresh the external table metadata periodically using ALTER EXTERNAL TABLE … REFRESH to synchronize the metadata with the current list of files in the stage path.

Default: TRUE

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. The maximum number of unique tags that can be set on an object is 20. For more information, see Tag Quotas for Objects/Columns.

A single ALTER statement can set or unset a maximum of 5 tags on an object.

UNSET

  • TAG tag_name [ , tag_name ... ]

Partitions Added and Removed Manually

Use the following parameters to add or remove partitions when the partition type for the external table is user-specified (i.e. PARTITION_TYPE = USER_SPECIFIED):

ADD PARTITION ( <part_col_name> = '<string>' [ , <part_col_name> = '<string>' , ... ] ) LOCATION '<path>'

Manually add a partition for one or more partition columns defined for the external table in a specified location (i.e. path).

Adding a partition also adds any new or updated files in the location to the external table metadata.

DROP PARTITION LOCATION '<path>'

Manually drop all partitions in a specified location (i.e. path).

Dropping a partition also removes any files in the location from the external table metadata.

Access Control Requirements

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

Privilege

Object

Notes

OWNERSHIP

External table

OWNERSHIP is a special privilege on an object that is automatically granted to the role that created the object, but can also be transferred using the GRANT OWNERSHIP command to a different role by the owning role (or any role with the MANAGE GRANTS privilege).

USAGE

Stage

Required to manually refresh the external table metadata.

USAGE

File format

Required to manually refresh the external table metadata.

Note that operating on any object in a schema also requires the USAGE privilege on the parent database and 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 Access Control in Snowflake.

Usage Notes

  • Only the external table owner (i.e. the role with the OWNERSHIP privilege on the external table) or higher can execute this command.

  • The following commands can be used in explicit transactions (using BEGINCOMMIT):

    • ALTER EXTERNAL TABLE ... REFRESH

    • ALTER EXTERNAL TABLE ... ADD FILES

    • ALTER EXTERNAL TABLE ... REMOVE FILES

    Explicit transactions could be used to ensure a consistent state when manually replacing updated files in external table metadata.

  • Add or remove columns in an external table using the following syntax:

    Add column
    ALTER TABLE <name> ADD COLUMN ( <col_name> <col_type> AS <expr> ) [, ...]
    
    Rename column
    ALTER TABLE <name> RENAME COLUMN <col_name> to <new_col_name>
    
    Drop column
    ALTER TABLE <name> DROP COLUMN <col_name>
    

    Note

    The default VALUE and METADATA$FILENAME columns cannot be dropped.

    See the ALTER TABLE topic for examples.

  • To add and drop a row access policy on an external table, use the ALTER TABLE command.

  • 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.

Examples

Refresh Metadata Manually

Manually refresh the entire set of external table metadata based on changes in the referenced data files:

ALTER EXTERNAL TABLE exttable_json REFRESH;

Similar to the first example, but manually refresh only a path of the metadata for an external table:

CREATE OR REPLACE STAGE mystage
  URL='<cloud_platform>://twitter_feed/logs/'
  .. ;

-- Create the external table
-- 'daily' path includes paths in </YYYY/MM/DD/> format
CREATE OR REPLACE EXTERNAL TABLE daily_tweets
  WITH LOCATION = @twitter_feed/daily/;

-- Refresh the metadata for a single day of data files by date
ALTER EXTERNAL TABLE exttable_part REFRESH '2018/08/05/';

Add or Remove Files Manually

Add an explicit list of files to the external table metadata:

ALTER EXTERNAL TABLE exttable1 ADD FILES ('path1/sales4.json.gz', 'path1/sales5.json.gz');

Remove an explicit list of files from the external table metadata:

ALTER EXTERNAL TABLE exttable1 REMOVE FILES ('path1/sales4.json.gz', 'path1/sales5.json.gz');

Replace an updated log file for December 2019 in the external table metadata in an explicit transaction:

BEGIN;

ALTER EXTERNAL TABLE extable1 REMOVE FILES ('2019/12/log1.json.gz');

ALTER EXTERNAL TABLE extable1 ADD FILES ('2019/12/log1.json.gz');

COMMIT;

Add or Remove Partitions Manually

Manually add partitions in a specified location for the partition columns:

ALTER EXTERNAL TABLE et2 ADD PARTITION(col1='2022-01-24', col2='a', col3='12') LOCATION '2022/01';

Snowflake adds the partitions to the metadata for the external table. The operation also adds any new data files in the specified location to the metadata.

Manually remove partitions from a specified location:

ALTER EXTERNAL TABLE et2 DROP PARTITION LOCATION '2022/01';

Snowflake removes the partitions from the metadata for the external table. The operation also removes any data files in the specified location from the metadata.

Back to top