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

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 }
Copy

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

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

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

Note

The maximum length of user-specified partition column names is 32 characters.

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

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

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

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');
Copy

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');
Copy

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

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

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

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.