CREATE EXTERNAL TABLE¶

CREATE EXTERNAL TABLE … USING TEMPLATE¶

Creates a new external table with the column definitions derived from a set of staged files containing semi-structured data. This feature supports Apache Parquet, Apache Avro, ORC, JSON, and CSV files. The support for CSV and JSON files is currently in preview.

CREATE [ OR REPLACE ] EXTERNAL TABLE <table_name>
  [ COPY GRANTS ]
  USING TEMPLATE <query>
  [ ... ]

Copy

For more details about COPY GRANTS, see COPY GRANTS in this document.

Partitioning parameters¶

Use these parameters to partition your external table.

part_col_name col_type AS part_expr

Defines one or more partition columns in the external table.

The format of a partition column definition differs depending on whether partitions are computed and added automatically from an expression in each partition column or the partitions are added manually.

Added from an expression:

A partition column must evaluate as an expression that parses the path and/or filename information in the METADATA$FILENAME pseudocolumn. Partition columns optimize query performance by pruning out the data files that do not need to be scanned (i.e. partitioning the external table). A partition consists of all data files that match the path and/or filename in the expression for the partition column.

part_col_name	String that specifies the partition column identifier (i.e. name). All the requirements for table identifiers also apply to column identifiers.
col_type	String (constant) that specifies the data type for the column. The data type must match the result of part_expr for the column.
part_expr	String that specifies the expression for the column. The expression must include the METADATA$FILENAME pseudocolumn.

External tables currently support the following subset of functions in partition expressions:

List of supported functions:

• =, <>, >, >=, <, <=

• ||

• +, -

• - (negate)

• *

• AND, OR

• ARRAY_CONSTRUCT

• CASE

• CAST , ::

• CONCAT , ||

• ENDSWITH

• IS [ NOT ] NULL

• IFF

• IFNULL

• [ NOT ] IN

• LOWER

• NOT

• NULLIF

• NVL2

• SPLIT_PART

• STARTSWITH

• SUBSTR , SUBSTRING

• UPPER

• ZEROIFNULL

Added manually:

Required: Also set the PARTITION_TYPE parameter value to USER_SPECIFIED.

A partition column definition is an expression that parses the column metadata in the internal (hidden) METADATA$EXTERNAL_TABLE_PARTITION column. Essentially, the definition only defines the data type for the column. The format of the partition column definition is as follows:

part_col_name col_type AS ( PARSE_JSON (METADATA$EXTERNALTABLE_PARTITION):part_col_name::data_type )

For example, suppose columns col1, col2, and col3 contain varchar, number, and timestamp (time zone) data, respectively:

col1 varchar as (parse_json(metadata$external_table_partition):col1::varchar),
col2 number as (parse_json(metadata$external_table_partition):col2::number),
col3 timestamp_tz as (parse_json(metadata$external_table_partition):col3::timestamp_tz)

Copy

After defining any partition columns for the table, identify these columns using the PARTITION BY clause.

Note

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

PARTITION_TYPE = USER_SPECIFIED

Defines the partition type for the external table as user-defined. The owner of the external table (i.e. the role that has the OWNERSHIP privilege on the external table) must add partitions to the external metadata manually by executing ALTER EXTERNAL TABLE … ADD PARTITION statements.

Do not set this parameter if partitions are added to the external table metadata automatically upon evaluation of expressions in the partition columns.

[ PARTITION BY ( part_col_name [, part_col_name ... ] ) ]

Specifies any partition columns to evaluate for the external table.

Usage:

When querying an external table, include one or more partition columns in a WHERE clause, e.g.:

... WHERE part_col_name = 'filter_value'

Snowflake filters on the partition columns to restrict the set of data files to scan. Note that all rows in these files are scanned. If a WHERE clause includes non-partition columns, those filters are evaluated after the data files have been filtered.

A common practice is to partition the data files based on increments of time; or, if the data files are staged from multiple sources, to partition by a data source identifier and date or timestamp.

TYPE = CSV¶

COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE

String (constant) that specifies the current compression algorithm for the data files to be queried. Snowflake uses this option to detect how already-compressed data files were compressed so that the compressed data in the files can be extracted for querying.

Supported Values	Notes
AUTO	Compression algorithm detected automatically, except for Brotli-compressed files, which cannot currently be detected automatically. If querying Brotli-compressed files, explicitly use BROTLI instead of AUTO.
GZIP
BZ2
BROTLI	Must be specified when querying Brotli-compressed files.
ZSTD	Zstandard v0.8 (and higher) supported.
DEFLATE	Deflate-compressed files (with zlib header, RFC1950).
RAW_DEFLATE	Raw Deflate-compressed files (without header, RFC1951).
NONE	Data files have not been compressed.

RECORD_DELIMITER = 'string' | NONE

One or more characters that separate records in an input file. Accepts common escape sequences or the following singlebyte or multibyte characters:

Singlebyte characters:

Octal values (prefixed by \\) or hex values (prefixed by 0x or \x). For example, for records delimited by the circumflex accent (^) character, specify the octal (\\136) or hex (0x5e) value.

Multibyte characters:

Hex values (prefixed by \x). For example, for records delimited by the cent (¢) character, specify the hex (\xC2\xA2) value.

The delimiter for RECORD_DELIMITER or FIELD_DELIMITER cannot be a substring of the delimiter for the other file format option (e.g. FIELD_DELIMITER = 'aa' RECORD_DELIMITER = 'aabb').

The specified delimiter must be a valid UTF-8 character and not a random sequence of bytes. Also note that the delimiter is limited to a maximum of 20 characters.

Also accepts a value of NONE.

Default: New line character. Note that “new line” is logical such that \r\n is understood as a new line for files on a Windows platform.

FIELD_DELIMITER = 'string' | NONE

One or more singlebyte or multibyte characters that separate fields in an input file. Accepts common escape sequences or the following singlebyte or multibyte characters:

Singlebyte characters:

Octal values (prefixed by \\) or hex values (prefixed by 0x or \x). For example, for records delimited by the circumflex accent (^) character, specify the octal (\\136) or hex (0x5e) value.

Multibyte characters:

Hex values (prefixed by \x). For example, for records delimited by the cent (¢) character, specify the hex (\xC2\xA2) value.

The delimiter for RECORD_DELIMITER or FIELD_DELIMITER cannot be a substring of the delimiter for the other file format option (e.g. FIELD_DELIMITER = 'aa' RECORD_DELIMITER = 'aabb').

Note

For non-ASCII characters, you must use the hex byte sequence value to get a deterministic behavior.

The specified delimiter must be a valid UTF-8 character and not a random sequence of bytes. Also note that the delimiter is limited to a maximum of 20 characters.

Also accepts a value of NONE.

Default: comma (,)

SKIP_HEADER = integer

Number of lines at the start of the file to skip.

Note that SKIP_HEADER does not use the RECORD_DELIMITER or FIELD_DELIMITER values to determine what a header line is; rather, it simply skips the specified number of CRLF (Carriage Return, Line Feed)-delimited lines in the file. RECORD_DELIMITER and FIELD_DELIMITER are then used to determine the rows of data to query.

Default: 0

SKIP_BLANK_LINES = TRUE | FALSE

Use:

Data querying only

Definition:

Boolean that specifies to skip any blank lines encountered in the data files; otherwise, blank lines produce an end-of-record error (default behavior).

Default: FALSE

ESCAPE_UNENCLOSED_FIELD = 'character' | NONE

A singlebyte character string used as the escape character for unenclosed field values only. An escape character invokes an alternative interpretation on subsequent characters in a character sequence. You can use the ESCAPE character to interpret instances of the FIELD_DELIMITER or RECORD_DELIMITER characters in the data as literals. The escape character can also be used to escape instances of itself in the data.

Accepts common escape sequences, octal values, or hex values.

Specifies the escape character for unenclosed fields only.

Note

• The default value is \\. If a row in a data file ends in the backslash (\) character, this character escapes the newline or carriage return character specified for the RECORD_DELIMITER file format option. As a result, this row and the next row are handled as a single row of data. To avoid this issue, set the value to NONE.

• This file format option supports singlebyte characters only. Note that UTF-8 character encoding represents high-order ASCII characters as multibyte characters. If your data file is encoded with the UTF-8 character set, you cannot specify a high-order ASCII character as the option value.

  In addition, if you specify a high-order ASCII character, we recommend that you set the ENCODING = 'string' file format option as the character encoding for your data files to ensure the character is interpreted correctly.

Default: backslash (\\)

TRIM_SPACE = TRUE | FALSE

Boolean that specifies whether to remove white space from fields.

For example, if your external database software encloses fields in quotes, but inserts a leading space, Snowflake reads the leading space rather than the opening quotation character as the beginning of the field (i.e. the quotation marks are interpreted as part of the string of field data). Set this option to TRUE to remove undesirable spaces when querying data.

As another example, if leading or trailing spaces surround quotes that enclose strings, you can remove the surrounding spaces using this option and the quote character using the FIELD_OPTIONALLY_ENCLOSED_BY option. Note that any spaces within the quotes are preserved. For example, assuming FIELD_DELIMITER = '|' and FIELD_OPTIONALLY_ENCLOSED_BY = '"':

|"Hello world"|    /* returned as */  >Hello world<
|" Hello world "|  /* returned as */  > Hello world <
| "Hello world" |  /* returned as */  >Hello world<

Copy

Note that the brackets in this example are not returned; they are used to demarcate the beginning and end of the returned strings.

Default: FALSE

FIELD_OPTIONALLY_ENCLOSED_BY = 'character' | NONE

Character used to enclose strings. Value can be NONE, single quote character ('), or double quote character ("). To use the single quote character, use the octal or hex representation (0x27) or the double single-quoted escape ('').

Default: NONE

NULL_IF = ( 'string1' [ , 'string2' , ... ] )

String used to convert to and from SQL NULL:

When querying data, Snowflake replaces these values in the returned data with SQL NULL. To specify more than one string, enclose the list of strings in parentheses and use commas to separate each value.

Note that Snowflake converts all instances of the value to NULL, regardless of the data type. For example, if 2 is specified as a value, all instances of 2 as either a string or number are converted.

For example:

NULL_IF = ('\N', 'NULL', 'NUL', '')

Note that this option can include empty strings.

Default: \\N (i.e. NULL, which assumes the ESCAPE_UNENCLOSED_FIELD value is \\)

EMPTY_FIELD_AS_NULL = TRUE | FALSE

Specifies whether to return SQL NULL for empty fields in an input file, which are represented by two successive delimiters (e.g. ,,).

If set to FALSE, Snowflake attempts to cast an empty field to the corresponding column type. An empty string is returned for columns of type STRING. For other column types, the query returns an error.

Default: TRUE

ENCODING = 'string'

String (constant) that specifies the character set of the source data when querying data.

Character Set	ENCODING Value	Supported Languages	Notes
Big5	BIG5	Traditional Chinese
EUC-JP	EUCJP	Japanese
EUC-KR	EUCKR	Korean
GB18030	GB18030	Chinese
IBM420	IBM420	Arabic
IBM424	IBM424	Hebrew
IBM949	IBM949	Korean
ISO-2022-CN	ISO2022CN	Simplified Chinese
ISO-2022-JP	ISO2022JP	Japanese
ISO-2022-KR	ISO2022KR	Korean
ISO-8859-1	ISO88591	Danish, Dutch, English, French, German, Italian, Norwegian, Portuguese, Swedish
ISO-8859-2	ISO88592	Czech, Hungarian, Polish, Romanian
ISO-8859-5	ISO88595	Russian
ISO-8859-6	ISO88596	Arabic
ISO-8859-7	ISO88597	Greek
ISO-8859-8	ISO88598	Hebrew
ISO-8859-9	ISO88599	Turkish
ISO-8859-15	ISO885915	Danish, Dutch, English, French, German, Italian, Norwegian, Portuguese, Swedish	Identical to ISO-8859-1 except for 8 characters, including the Euro currency symbol.
KOI8-R	KOI8R	Russian
Shift_JIS	SHIFTJIS	Japanese
UTF-8	UTF8	All languages	For loading data from delimited files (CSV, TSV, etc.), UTF-8 is the default. . . For loading data from all other supported file formats (JSON, Avro, etc.), as well as unloading data, UTF-8 is the only supported character set.
UTF-16	UTF16	All languages
UTF-16BE	UTF16BE	All languages
UTF-16LE	UTF16LE	All languages
UTF-32	UTF32	All languages
UTF-32BE	UTF32BE	All languages
UTF-32LE	UTF32LE	All languages
windows-874	WINDOWS874	Thai
windows-949	WINDOWS949	Korean
windows-1250	WINDOWS1250	Czech, Hungarian, Polish, Romanian
windows-1251	WINDOWS1251	Russian
windows-1252	WINDOWS1252	Danish, Dutch, English, French, German, Italian, Norwegian, Portuguese, Swedish
windows-1253	WINDOWS1253	Greek
windows-1254	WINDOWS1254	Turkish
windows-1255	WINDOWS1255	Hebrew
windows-1256	WINDOWS1256	Arabic

Default: UTF8

Note

Snowflake stores all data internally in the UTF-8 character set. The data is converted into UTF-8.

TYPE = JSON¶

COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE

String (constant) that specifies the current compression algorithm for the data files to be returned. Snowflake uses this option to detect how already-compressed data files were compressed so that the compressed data in the files can be extracted for querying.

Supported Values	Notes
AUTO	Compression algorithm detected automatically, except for Brotli-compressed files, which cannot currently be detected automatically. If querying Brotli-compressed files, explicitly use BROTLI instead of AUTO.
GZIP
BZ2
BROTLI
ZSTD
DEFLATE	Deflate-compressed files (with zlib header, RFC1950).
RAW_DEFLATE	Raw Deflate-compressed files (without header, RFC1951).
NONE	Indicates the files have not been compressed.

Default: AUTO

ALLOW_DUPLICATE = TRUE | FALSE

Boolean that specifies to allow duplicate object field names (only the last one will be preserved).

Default: FALSE

STRIP_OUTER_ARRAY = TRUE | FALSE

Boolean that instructs the JSON parser to remove outer brackets (i.e. [ ]).

Default: FALSE

STRIP_NULL_VALUES = TRUE | FALSE

Boolean that instructs the JSON parser to remove object fields or array elements containing null values. For example, when set to TRUE:

Before	After
[null]	[]
[null,null,3]	[,,3]
{"a":null,"b":null,"c":123}	{"c":123}
{"a":[1,null,2],"b":{"x":null,"y":88}}	{"a":[1,,2],"b":{"y":88}}

Default: FALSE

REPLACE_INVALID_CHARACTERS = TRUE | FALSE

Boolean that specifies whether to replace invalid UTF-8 characters with the Unicode replacement character (�). This option performs a one-to-one character replacement.

If set to TRUE, Snowflake replaces invalid UTF-8 characters with the Unicode replacement character.

If set to FALSE, the load operation produces an error when invalid UTF-8 character encoding is detected.

Default: FALSE

TYPE = AVRO¶

COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE

String (constant) that specifies the current compression algorithm for the data files to be queried. Snowflake uses this option to detect how already-compressed data files were compressed so that the compressed data in the files can be extracted for querying.

Supported Values	Notes
AUTO	Compression algorithm detected automatically, except for Brotli-compressed files, which cannot currently be detected automatically. If querying Brotli-compressed files, explicitly use BROTLI instead of AUTO.
GZIP
BZ2
BROTLI
ZSTD
DEFLATE	Deflate-compressed files (with zlib header, RFC1950).
RAW_DEFLATE	Raw Deflate-compressed files (without header, RFC1951).
NONE	Data files to query have not been compressed.

Default: AUTO.

REPLACE_INVALID_CHARACTERS = TRUE | FALSE

Boolean that specifies whether to replace invalid UTF-8 characters with the Unicode replacement character (�). This option performs a one-to-one character replacement.

If set to TRUE, Snowflake replaces invalid UTF-8 characters with the Unicode replacement character.

If set to FALSE, the load operation produces an error when invalid UTF-8 character encoding is detected.

Default: FALSE

TYPE = ORC¶

TRIM_SPACE = TRUE | FALSE

Boolean that specifies whether to remove leading and trailing white space from strings.

For example, if your external database software encloses fields in quotes, but inserts a leading space, Snowflake reads the leading space rather than the opening quotation character as the beginning of the field (i.e. the quotation marks are interpreted as part of the string of field data). Set this option to TRUE to remove undesirable spaces.

This file format option is applied to the following actions only:

• Querying object values in staged ORC data files.

• Querying ORC data in separate columns using the MATCH_BY_COLUMN_NAME copy option.

• Querying ORC data in separate columns by specifying a query in the COPY statement (i.e. COPY transformation).

Default: FALSE

REPLACE_INVALID_CHARACTERS = TRUE | FALSE

Boolean that specifies whether to replace invalid UTF-8 characters with the Unicode replacement character (�). This option performs a one-to-one character replacement.

If set to TRUE, Snowflake replaces invalid UTF-8 characters with the Unicode replacement character.

If set to FALSE, the load operation produces an error when invalid UTF-8 character encoding is detected.

Default: FALSE

NULL_IF = ( 'string1' [ , 'string2' , ... ] )

String used to convert to and from SQL NULL. Snowflake replaces these strings in the data source with SQL NULL. To specify more than one string, enclose the list of strings in parentheses and use commas to separate each value.

Note that Snowflake converts all instances of the value to NULL, regardless of the data type. For example, if 2 is specified as a value, all instances of 2 as either a string or number are converted.

For example:

NULL_IF = ('\N', 'NULL', 'NUL', '')

Note that this option can include empty strings.

This file format option is applied when querying object values in staged ORC data files.

Default: \\N (i.e. NULL, which assumes the ESCAPE_UNENCLOSED_FIELD value is \\)

TYPE = PARQUET¶

COMPRESSION = AUTO | SNAPPY | NONE

String (constant) that specifies the current compression algorithm for columns in the Parquet files.

Supported Values	Notes
AUTO	Compression algorithm detected automatically. Supports the following compression algorithms: Brotli, gzip, Lempel-Ziv-Oberhumer (LZO), LZ4, Snappy, or Zstandard v0.8 (and higher).
SNAPPY
NONE	Data files have not been compressed.

Default: AUTO

BINARY_AS_TEXT = TRUE | FALSE

Boolean that specifies whether to interpret columns with no defined logical data type as UTF-8 text. When set to FALSE, Snowflake interprets these columns as binary data.

Default: TRUE

Note

Snowflake recommends that you set BINARY_AS_TEXT to FALSE to avoid any potential conversion issues.

REPLACE_INVALID_CHARACTERS = TRUE | FALSE

Boolean that specifies whether to replace invalid UTF-8 characters with the Unicode replacement character (�). This option performs a one-to-one character replacement.

If set to TRUE, Snowflake replaces invalid UTF-8 characters with the Unicode replacement character.

If set to FALSE, the load operation produces an error when invalid UTF-8 character encoding is detected.

Default: FALSE

Partitions added automatically from partition column expressions¶

Create an external table with partitions computed from expressions in the partition column definitions.

In the following example, the data files are organized in cloud storage with the following structure: logs/YYYY/MM/DD/HH24. For example:

• logs/2018/08/05/0524/

• logs/2018/08/27/1408/

1. Create an external stage named s1 for the storage location where the data files are stored. For more information, see CREATE STAGE.

   The stage definition includes the path /files/logs/:

   Amazon S3

   CREATE STAGE s1
     URL='s3://mybucket/files/logs/'
     ...
     ;
   

   Copy

   Google Cloud Storage

   CREATE STAGE s1
     URL='gcs://mybucket/files/logs/'
     ...
     ;
   

   Copy

   Microsoft Azure

   CREATE STAGE s1
     URL='azure://mycontainer/files/logs/'
     ...
     ;
   

   Copy

2. Query the METADATA$FILENAME pseudocolumn in the staged data. Use the results to develop your partition column(s):

   SELECT metadata$filename FROM @s1/;
   
   +----------------------------------------+
   | METADATA$FILENAME                      |
   |----------------------------------------|
   | files/logs/2018/08/05/0524/log.parquet |
   | files/logs/2018/08/27/1408/log.parquet |
   +----------------------------------------+
   

   Copy

3. Create the partitioned external table.

   The partition column date_part casts YYYY/MM/DD in the METADATA$FILENAME pseudocolumn as a date using TO_DATE , DATE. The SQL command also specifies Parquet as the file format type.

   The external tables for Amazon S3 and Microsoft Azure cloud storage include the parameter required to refresh the metadata automatically when triggered by event notifications from the respective cloud messaging service:

   Amazon S3

   CREATE EXTERNAL TABLE et1(
    date_part date AS TO_DATE(SPLIT_PART(metadata$filename, '/', 3)
      || '/' || SPLIT_PART(metadata$filename, '/', 4)
      || '/' || SPLIT_PART(metadata$filename, '/', 5), 'YYYY/MM/DD'),
    timestamp bigint AS (value:timestamp::bigint),
    col2 varchar AS (value:col2::varchar))
    PARTITION BY (date_part)
    LOCATION=@s1/logs/
    AUTO_REFRESH = true
    FILE_FORMAT = (TYPE = PARQUET)
    AWS_SNS_TOPIC = 'arn:aws:sns:us-west-2:001234567890:s3_mybucket';
   

   Copy

   Google Cloud Storage

   CREATE EXTERNAL TABLE et1(
     date_part date AS TO_DATE(SPLIT_PART(metadata$filename, '/', 3)
       || '/' || SPLIT_PART(metadata$filename, '/', 4)
       || '/' || SPLIT_PART(metadata$filename, '/', 5), 'YYYY/MM/DD'),
     timestamp bigint AS (value:timestamp::bigint),
     col2 varchar AS (value:col2::varchar))
     PARTITION BY (date_part)
     LOCATION=@s1/logs/
     AUTO_REFRESH = true
     FILE_FORMAT = (TYPE = PARQUET);
   

   Copy

   Microsoft Azure

   CREATE EXTERNAL TABLE et1(
     date_part date AS TO_DATE(SPLIT_PART(metadata$filename, '/', 3)
       || '/' || SPLIT_PART(metadata$filename, '/', 4)
       || '/' || SPLIT_PART(metadata$filename, '/', 5), 'YYYY/MM/DD'),
     timestamp bigint AS (value:timestamp::bigint),
     col2 varchar AS (value:col2::varchar))
     PARTITION BY (date_part)
     INTEGRATION = 'MY_INT'
     LOCATION=@s1/logs/
     AUTO_REFRESH = true
     FILE_FORMAT = (TYPE = PARQUET);
   

   Copy

4. Refresh the external table metadata:

   ALTER EXTERNAL TABLE et1 REFRESH;
   

   Copy

When querying the external table, filter the data by the partition column using a WHERE clause. Snowflake only scans the files in the specified partitions that match the filter conditions:

SELECT timestamp, col2 FROM et1 WHERE date_part = to_date('08/05/2018');

Partitions added manually¶

Create an external table with user-defined partitions (i.e. the partitions are added manually by the external table owner).

1. Create an external stage named s2 for the storage location where the data files are stored:

   The stage definition includes the path /files/logs/:

   Amazon S3

   CREATE STAGE s2
     URL='s3://mybucket/files/logs/'
     ...
     ;
   

   Copy

   Google Cloud Storage

   CREATE STAGE s2
     URL='gcs://mybucket/files/logs/'
     ...
     ;
   

   Copy

   Microsoft Azure

   CREATE STAGE s2
     URL='azure://mycontainer/files/logs/'
     ...
     ;
   

   Copy

2. Create the partitioned external table. The external table includes three partition columns with different data types.

   Note that the column names in the partition expressions are case-sensitive. The following rules apply:

   • A partition column name must be in uppercase, unless the column name is enclosed in double quotes. Alternatively, use GET_IGNORE_CASE instead of the case-sensitive : character in the SQL expression.

   • If a column name is enclosed in double quotes (e.g. “Column1”), the partition column name must also be enclosed in double quotes and match the column name exactly.

   The syntax for each of the three cloud storage services (Amazon S3, Google Cloud Storage, and Microsoft Azure) is identical because the external table metadata is not refreshed:

   create external table et2(
     col1 date as (parse_json(metadata$external_table_partition):COL1::date),
     col2 varchar as (parse_json(metadata$external_table_partition):COL2::varchar),
     col3 number as (parse_json(metadata$external_table_partition):COL3::number))
     partition by (col1,col2,col3)
     location=@s2/logs/
     partition_type = user_specified
     file_format = (type = parquet);
   

   Copy

3. Add partitions 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:

   +---------------------------------------+----------------+-------------------------------+
   |                       file            |     status     |          description          |
   +---------------------------------------+----------------+-------------------------------+
   | mycontainer/files/logs/2022/01/24.csv | REGISTERED_NEW | File registered successfully. |
   | mycontainer/files/logs/2022/01/25.csv | REGISTERED_NEW | File registered successfully. |
   +---------------------------------------+----------------+-------------------------------+
   

   Copy

When querying the external table, filter the data by the partition columns using a WHERE clause. This example returns the records in the order they are stored in the staged data files:

SELECT col1, col2, col3 FROM et1 WHERE col1 = TO_DATE('2022-01-24') AND col2 = 'a' ORDER BY METADATA$FILE_ROW_NUMBER;

External table that references files in a Delta Lake¶

Create a partitioned external table named ext_twitter_feed that references the Delta Lake files in Parquet format in the mystage external stage and daily path.

The partition column date_part casts YYYY/MM/DD in the METADATA$FILENAME pseudocolumn as a date using TO_DATE , DATE:

CREATE EXTERNAL TABLE ext_twitter_feed(
 date_part date AS TO_DATE(SPLIT_PART(metadata$filename, '/', 3)
   || '/' || SPLIT_PART(metadata$filename, '/', 4)
   || '/' || SPLIT_PART(metadata$filename, '/', 5), 'YYYY/MM/DD'),
 timestamp bigint AS (value:timestamp::bigint),
 col2 varchar AS (value:col2::varchar))
 PARTITION BY (date_part)
 LOCATION=@mystage/daily/
 REFRESH_ON_CREATE =  FALSE
 AUTO_REFRESH = FALSE
 FILE_FORMAT = (TYPE = PARQUET)
 TABLE_FORMAT = DELTA;

Materialized view on an external table¶

Create a materialized view based on a subquery of the columns in the external table created in the Partitions Added Automatically From Partition Column Expressions example:

CREATE MATERIALIZED VIEW et1_mv
  AS
  SELECT col2 FROM et1;

For general syntax, usage notes, and further examples for this SQL command, see CREATE MATERIALIZED VIEW.

External table created with detected column definitions¶

Create an external table where the column definitions are derived from a set of staged files that contain Avro, Parquet, or ORC data.

Note that the mystage stage and my_parquet_format file format referenced in the statement must already exist. A set of files must already be staged in the cloud storage location referenced in the stage definition.

This example builds on an example in the INFER_SCHEMA topic:

CREATE EXTERNAL TABLE mytable
  USING TEMPLATE (
    SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
    FROM TABLE(
      INFER_SCHEMA(
        LOCATION=>'@mystage',
        FILE_FORMAT=>'my_parquet_format'
      )
    )
  )
  LOCATION=@mystage
  FILE_FORMAT=my_parquet_format
  AUTO_REFRESH=false;

Copy

Using * for ARRAY_AGG(OBJECT_CONSTRUCT()) may result in an error if the returned result is larger than 16 MB. Avoid using * for larger result sets, and only use the required columns, COLUMN NAME, TYPE, and NULLABLE, for the query, as the following example demonstrates. Optional column ORDER_ID can be included when using WITHIN GROUP (ORDER BY order_id).

CREATE EXTERNAL TABLE mytable
  USING TEMPLATE (
    SELECT ARRAY_AGG(OBJECT_CONSTRUCT('COLUMN_NAME',COLUMN_NAME, 'TYPE',TYPE, 'NULLABLE', NULLABLE, 'EXPRESSION',EXPRESSION))
    FROM TABLE(
      INFER_SCHEMA(
        LOCATION=>'@mystage',
        FILE_FORMAT=>'my_parquet_format'
      )
    )
  )
  LOCATION=@mystage
  FILE_FORMAT=my_parquet_format
  AUTO_REFRESH=false;

Copy