Categories:

Table, View, & Sequence DDL

# ALTER EXTERNAL TABLE¶

Modifies the properties, columns, or constraints for an existing 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> ... ]


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.

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.

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

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.

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¶

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

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

-- 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¶

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.