Categories:

Table, View, & Sequence DDL

ALTER EXTERNAL TABLE

Manually refreshes an existing external table.

See also:

CREATE EXTERNAL TABLE , DROP EXTERNAL TABLE , SHOW EXTERNAL TABLES

In this Topic:

Syntax

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

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

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.

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