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


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

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.

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.

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

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


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;