ALTER EXTERNAL TABLE¶

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 }

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

Copy

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:

  • Amazon S3:

    Refreshing external tables automatically for Amazon S3

  • Google Cloud Storage:

    Refreshing external tables automatically for Google Cloud Storage

  • Microsoft Azure:

    Refreshing external tables automatically for Azure Blob Storage

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

• Setting this parameter to TRUE is not supported for external tables that reference data files stored on an S3-compatible external stage.

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

Access control requirements¶

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

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¶

• 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 BEGIN … COMMIT):

  • 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> ) [, ...]
  

  Copy

  Rename column:

  ALTER TABLE <name> RENAME COLUMN <col_name> to <new_col_name>
  

  Copy

  Drop column:

  ALTER TABLE <name> DROP COLUMN <col_name>
  

  Copy

  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, or to set or unset a tag, use the ALTER TABLE command.

  However, you can create an external table with a row access policy and a tag on the table. See CREATE EXTERNAL TABLE.

• You can use data metric functions with external tables by executing an ALTER TABLE command. For more information, see Working with data metric functions.

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

ALTER EXTERNAL TABLE exttable_json REFRESH;

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/';

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

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

BEGIN;

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

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

COMMIT;

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

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