EXTERNAL_TABLE
Supported DBMS products: Snowflake
Physical options related to EXTERNAL_TABLE
Keys related to EXTERNAL_TABLE
Good to know
External table in Snowflake requires the table location to be set by using STORAGE.EXTERNAL_TABLE_LOCATION physical option. The syntax is of type:
@<STAGE_NAME>/<path>/External table integration needs to be defined with STORAGE.EXTERNAL_TABLE_INTEGRATION physical option if the stage is located in Azure or GCP.
External table in Snowflake requires the file format to be defined even if the format has been defined on STAGE object previously.
Manual partitioning is to be activated with STORAGE.EXTERNAL_TABLE_USER_SPEC_PARTITION physical option set as true.
If only subset of available files should be handled from the stage, filenames can be filtered with STORAGE.EXTERNAL_TABLE_FILENAME_PATTERN physical option, which applies the given regular expression in automatic partitioning scenario.
Partitioning can be enabled by adding a PARTITION_KEY, but the virtual field referred must be utilizing either METADATA$FILENAME (automatic partitioning) or METADATA$EXTERNAL_TABLE_PARTITION (manual partitioning) metadata information.
Refresh behavior
If external table is wanted to be refreshed immediately at creation, set the
STORAGE.EXTERNAL_TABLE_REFRESH_ON_CREATE physical option as true.
If external table is wanted to be refreshed automatically, set the
REFRESH.EXTERNAL_TABLE_AUTO_REFRESH physical option as true.
If the automatic refresh is wanted to follow a SNS topic, this can be set with
STORAGE.EXTERNAL_TABLE_AWS_SNS_TOPIC physical option.