CREATE EXTERNAL TABLE¶

CREATE EXTERNAL TABLE ...USING TEMPLATE¶

创建一个新的外部表，其列定义派生自一组包含半结构化数据的暂存文件。此功能支持 Apache Parquet、Apache Avro、ORC、JSON 和 CSV 文件。对 CSV 和 JSON 文件的支持目前为预览版。

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

Copy

有关 COPY GRANTS 的更多信息，请参阅本文档中的 COPY GRANTS。

分区参数¶

使用这些参数对外部表进行分区。

part_col_name col_type AS part_expr

定义外部表中的一个或多个分区列。

分区列定义的格式会有所不同，具体取决于分区是从每个分区列中的表达式自动计算和添加分区，还是手动添加分区。

从表达式添加:

分区列必须计算为解析 METADATA$FILENAME 伪列中的路径或文件名信息的表达式。分区列通过修剪不需要扫描的数据文件（即对外部表进行分区）来优化查询性能。分区由与分区列表达式中的路径或文件名匹配的所有数据文件组成。

part_col_name	指定分区列标识符（即名称）的字符串。表标识符的所有要求也适用于列标识符。
col_type	指定列的数据类型的字符串（常量）。数据类型必须与列的 part_expr 结果匹配。
part_expr	指定列的表达式的字符串。表达式必须包含 METADATA$FILENAME 伪列。

外部表目前支持分区表达式中的以下函数子集：

• =、<>、>、>=、<、<=

• ||

• +、-

• - （负号）

• *

• 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

手动添加:

必需： 还要将 PARTITION_TYPE 参数值设置为 USER_SPECIFIED。

分区列定义是解析内部（隐藏）METADATA$EXTERNAL_TABLE_PARTITION 列中的列元数据的表达式。从本质上讲，该定义仅定义列的数据类型。以下示例显示了分区列定义的格式：

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

例如，假设列 col1、col2 和 col3 分别包含 varchar、number 和 timestamp（时区）数据：

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

为表定义任何分区列后，使用 PARTITION BY 子句标识这些列。

备注

用户指定的分区列名的最大长度为 32 个字符。

PARTITION_TYPE = USER_SPECIFIED

将外部表的分区类型定义为 user-defined。外部表的所有者（即对外部表拥有 OWNERSHIP 权限的角色）必须通过运行 ALTER EXTERNAL TABLE ...ADD PARTITION 语句，手动将分区添加到外部元数据。

如果在评估分区列中的表达式时自动将分区添加到外部表元数据中，则 不要 设置此参数。

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

指定要为外部表评估的任何分区列。

用途:

查询外部表时，在 WHERE 子句中包含一个或多个分区列，例如：

... WHERE part_col_name = 'filter_value'

Snowflake 筛选分区列以限制要扫描的数据文件集。会扫描这些文件中的所有行。如果 WHERE 子句包含非分区列，则在筛选完数据文件 后 会对这些筛选器进行评估。

一种常见的做法是根据时间增量对数据文件进行分区；或者，如果数据文件是从多个源暂存的，则按数据源标识符和日期或时间戳进行分区。

TYPE = CSV¶

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

为要查询的数据文件指定当前压缩算法的字符串（常量）。Snowflake 使用此选项来检测 已压缩 的数据文件是如何压缩的，以便提取文件中的压缩数据进行查询。

支持的值	备注
AUTO	压缩算法会自动检测，但 Brotli 压缩文件除外（目前无法自动检测）。如果查询 Brotli 压缩文件，请显式使用 BROTLI 而不是 AUTO。
GZIP
BZ2
BROTLI	查询 Brotli 压缩文件时必须指定此值。
ZSTD	支持 Zstandard v0.8（及更高版本）。
DEFLATE	使用 Deflate 压缩的文件（带有 zlib 标头、RFC1950）。
RAW_DEFLATE	使用 Raw Deflate 压缩的文件（无标头、RFC1951）。
NONE	数据文件尚未压缩。

RECORD_DELIMITER = 'string' | NONE

用于分隔输入文件中记录的一个或多个字符。接受常见的转义序列或以下单字节或多字节字符：

单字节字符:

八进制值（以 \\ 为前缀）或十六进制值（以 0x 或 \x 为前缀）。例如，对于由重音符号 (^) 字符分隔的记录，请指定八进制 (\\136) 或十六进制 (0x5e) 值。

多字节字符:

十六进制值（前缀为 \x）。例如，对于由分 (¢) 字符分隔的记录，请指定十六进制 (\xC2\xA2) 值。

RECORD_DELIMITER 或 FIELD_DELIMITER 分隔符不能是其他文件格式选项（例如 FIELD_DELIMITER = 'aa' RECORD_DELIMITER = 'aabb'）的分隔符的子字符串。

指定的分隔符必须是有效的 UTF-8 字符，而不是随机的字节序列。另请注意，分隔符限制为最多 20 个字符。

也接受 NONE 的值。

默认值：换行符。请注意，“换行符”是合乎逻辑的，因而 \r\n 可以理解为 Windows 平台上文件的换行符。

FIELD_DELIMITER = 'string' | NONE

一个或多个单字节或多字节字符，用于分隔输入文件中的字段。接受常见的转义序列或以下单字节或多字节字符：

单字节字符:

八进制值（以 \\ 为前缀）或十六进制值（以 0x 或 \x 为前缀）。例如，对于由重音符号 (^) 字符分隔的记录，请指定八进制 (\\136) 或十六进制 (0x5e) 值。

多字节字符:

十六进制值（前缀为 \x）。例如，对于由分 (¢) 字符分隔的记录，请指定十六进制 (\xC2\xA2) 值。

RECORD_DELIMITER 或 FIELD_DELIMITER 分隔符不能是其他文件格式选项（例如 FIELD_DELIMITER = 'aa' RECORD_DELIMITER = 'aabb'）的分隔符的子字符串。

备注

对于非 ASCII 字符，必须使用十六进制字节序列值来获取确定性行为。

指定的分隔符必须是有效的 UTF-8 字符，而不是随机的字节序列。另请注意，分隔符限制为最多 20 个字符。

也接受 NONE 的值。

默认值：逗号 (,)

MULTI_LINE = TRUE | FALSE

布尔值，指定是否允许多行。

如果将 MULTI_LINE 设置为 FALSE，并且指定记录分隔符存在于 CSV 字段中，则包含该字段的记录将被解析为错误。

默认：TRUE

SKIP_HEADER = integer

要跳过的文件开头的行数。

注意，SKIP_HEADER 不使用 RECORD_DELIMITER 或 FIELD_DELIMITER 值来决定标头行；相反，它会直接跳过文件中指定数量的以 CRLF（回车符、换行）分隔的行。然后，使用 RECORD_DELIMITER 和 FIELD_DELIMITER 确定要查询的数据行。

默认：0

SKIP_BLANK_LINES = TRUE | FALSE

使用:

仅数据查询

定义:

布尔，指定是否跳过数据文件中遇到的任何空行；否则，空行将产生记录结束错误（默认行为）。

默认：FALSE

ESCAPE_UNENCLOSED_FIELD = 'character' | NONE

仅用作未封闭字段值的转义字符的单字节字符串。转义字符对字符序列中的后续字符调用替代解释。您可以使用 ESCAPE 字符将数据中 FIELD_DELIMITER 或 RECORD_DELIMITER 字符的实例解释为字面量。转义字符还可用于对数据中自身的实例进行转义。

接受常见的转义序列、八进制值或十六进制值。

仅指定未封闭字段的转义字符。

备注

• 默认值为 \\。如果数据文件中的行以反斜杠 (\) 字符结尾，则此字符将转义为 RECORD_DELIMITER 文件格式选项指定的换行符或回车符。因此，此行和下一行将作为单行数据进行处理。若要避免此问题，请将值设置为 NONE。

• 此文件格式选项仅支持单字节字符。请注意，UTF-8 字符编码将高阶 ASCII 字符表示为多字节字符。如果数据文件使用 UTF-8 字符集进行编码，则不能将高阶 ASCII 字符指定为选项值。

  此外，如果指定高阶 ASCII 字符，建议将文件格式选项设置为 ENCODING = 'string' 数据文件的字符编码，以确保正确解释该字符。

默认值：反斜杠 (\\)

TRIM_SPACE = TRUE | FALSE

布尔，指定是否从字段中移除空格。

例如，如果您的外部数据库软件将字段放在引号中，但插入了前导空格，则 Snowflake 会读取前导空格而不是左引号字符作为字段的开头（即引号被解释为字段数据字符串的一部分）。将此选项设置为 TRUE 可在查询数据时移除不需要的空格。

再举一个例子，如果字符串两边的引号两边有前导空格或尾随空格，则可以使用此选项移除周围的空格，并使用 FIELD_OPTIONALLY_ENCLOSED_BY 选项移除引号字符。请注意，引号 内 的任何空格都将保留。例如，假设 FIELD_DELIMITER = '|' 和 FIELD_OPTIONALLY_ENCLOSED_BY = '"'：

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

Copy

请注意，此示例中的括号不返回；它们用于划分返回字符串的开头和结尾。

默认：FALSE

FIELD_OPTIONALLY_ENCLOSED_BY = 'character' | NONE

用于包围字符串的字符。值可以是 NONE、单引号字符 (') 或双引号字符 (")。要使用单引号字符，请使用八进制或十六进制表示形式 (0x27) 或双单引号转义 ('')。

默认：NONE

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

用于与 SQL NULL 相互转换的字符串：

查询数据时，Snowflake 会将返回数据中的这些值替换为 SQL NULL。若要指定多个字符串，请将字符串列表放在括号中，并使用逗号分隔每个值。

请注意，无论数据类型如何，Snowflake 都会将该值的所有实例转换为 NULL。例如，如果 2 指定为值，则转换为字符串或数字的所有 2 实例。

例如：

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

请注意，此选项可以包含空字符串。

默认：\N``（即 NULL，假设 :code:`ESCAPE_UNENCLOSED_FIELD` 值为 ``\\）

EMPTY_FIELD_AS_NULL = TRUE | FALSE

指定是否为输入文件中的空字段返回 SQL NULL，这些字段由两个连续的分隔符表示（例如 ,,）。

如果设置为 FALSE，Snowflake 会尝试将空字段转换为相应列类型。对于类型为 STRING 的列，将返回一个空字符串。对于其他列类型，查询将返回错误。

默认：TRUE

ENCODING = 'string'

用于指定查询数据时源数据字符集的字符串（常量）。

字符 集	ENCODING 值	支持的语言	备注
Big5	BIG5	繁体中文
EUC-JP	EUCJP	日语
EUC-KR	EUCKR	韩语
GB18030	GB18030	中文
IBM420	IBM420	阿拉伯语
IBM424	IBM424	希伯来语
IBM949	IBM949	韩语
ISO-2022-CN	ISO2022CN	简体中文
ISO-2022-JP	ISO2022JP	日语
ISO-2022-KR	ISO2022KR	韩语
ISO-8859-1	ISO88591	丹麦语、荷兰语、英语、法语、德语、意大利语、挪威语、葡萄牙语、瑞典语
ISO-8859-2	ISO88592	捷克语、匈牙利语、波兰语、罗马尼亚语
ISO-8859-5	ISO88595	俄语
ISO-8859-6	ISO88596	阿拉伯语
ISO-8859-7	ISO88597	希腊语
ISO-8859-8	ISO88598	希伯来语
ISO-8859-9	ISO88599	土耳其语
ISO-8859-15	ISO885915	丹麦语、荷兰语、英语、法语、德语、意大利语、挪威语、葡萄牙语、瑞典语	与 ISO-8859-1 相同，但 8 个字符除外，包括欧元货币符号。
KOI8-R	KOI8R	俄语
Shift_JIS	SHIFTJIS	日语
UTF-8	UTF8	所有语言	要从分隔文件（CSV、TSV 等）加载数据，默认为 UTF-8。. . 要从所有其他支持的文件格式（JSON、Avro 等）加载数据以及卸载数据，UTF-8 是唯一支持的字符集。
UTF-16	UTF16	所有语言
UTF-16BE	UTF16BE	所有语言
UTF-16LE	UTF16LE	所有语言
UTF-32	UTF32	所有语言
UTF-32BE	UTF32BE	所有语言
UTF-32LE	UTF32LE	所有语言
windows-874	WINDOWS874	泰语
windows-949	WINDOWS949	韩语
windows-1250	WINDOWS1250	捷克语、匈牙利语、波兰语、罗马尼亚语
windows-1251	WINDOWS1251	俄语
windows-1252	WINDOWS1252	丹麦语、荷兰语、英语、法语、德语、意大利语、挪威语、葡萄牙语、瑞典语
windows-1253	WINDOWS1253	希腊语
windows-1254	WINDOWS1254	土耳其语
windows-1255	WINDOWS1255	希伯来语
windows-1256	WINDOWS1256	阿拉伯语

默认：UTF8

备注

Snowflake 将所有数据存储在 UTF-8 字符集中。数据将转换为 UTF-8。

TYPE = JSON¶

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

为要返回的数据文件指定当前压缩算法的字符串（常量）。Snowflake 使用此选项来检测 已压缩 的数据文件是如何压缩的，以便提取文件中的压缩数据进行查询。

支持的值	备注
AUTO	压缩算法会自动检测，但 Brotli 压缩文件除外（目前无法自动检测）。如果查询 Brotli 压缩文件，请显式使用 BROTLI 而不是 AUTO。
GZIP
BZ2
BROTLI
ZSTD
DEFLATE	使用 Deflate 压缩的文件（带有 zlib 标头、RFC1950）。
RAW_DEFLATE	使用 Raw Deflate 压缩的文件（无标头、RFC1951）。
NONE	指示文件尚未压缩。

默认：AUTO

MULTI_LINE = TRUE | FALSE

布尔值，指定是否允许多行。

如果将 MULTI_LINE 设置为 FALSE，并且 JSON 记录中存在新行，则包含新行的记录将被解析为错误。

默认：TRUE

ALLOW_DUPLICATE = TRUE | FALSE

布尔，指定允许重复对象字段名称（仅保留最后一个）。

默认：FALSE

STRIP_OUTER_ARRAY = TRUE | FALSE

布尔，指示 JSON 解析器移除外括号（即 [ ]）。

默认：FALSE

STRIP_NULL_VALUES = TRUE | FALSE

布尔，指示 JSON 解析器移除包含 null 值的对象字段或数组元素。例如，当设置为 TRUE 时，会发生以下变化：

之前	之后
[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}}

默认：FALSE

REPLACE_INVALID_CHARACTERS = TRUE | FALSE

布尔，指定是否将无效的 UTF-8 字符替换为 Unicode 替换字符 (�)。此选项执行一对一的字符替换。

如果设置为 TRUE，Snowflake 会将无效的 UTF-8 字符替换为 Unicode 替换字符。

如果设置为 FALSE，则加载操作在检测到无效的 UTF-8 字符编码时产生错误。

默认：FALSE

TYPE = AVRO¶

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

为要查询的数据文件指定当前压缩算法的字符串（常量）。Snowflake 使用此选项来检测 已压缩 的数据文件是如何压缩的，以便提取文件中的压缩数据进行查询。

支持的值	备注
AUTO	压缩算法会自动检测，但 Brotli 压缩文件除外（目前无法自动检测）。如果查询 Brotli 压缩文件，请显式使用 BROTLI 而不是 AUTO。
GZIP
BZ2
BROTLI
ZSTD
DEFLATE	使用 Deflate 压缩的文件（带有 zlib 标头、RFC1950）。
RAW_DEFLATE	使用 Raw Deflate 压缩的文件（无标头、RFC1951）。
NONE	要查询的数据文件尚未压缩。

默认：AUTO。

REPLACE_INVALID_CHARACTERS = TRUE | FALSE

布尔，指定是否将无效的 UTF-8 字符替换为 Unicode 替换字符 (�)。此选项执行一对一的字符替换。

如果设置为 TRUE，Snowflake 会将无效的 UTF-8 字符替换为 Unicode 替换字符。

如果设置为 FALSE，则加载操作在检测到无效的 UTF-8 字符编码时产生错误。

默认：FALSE

TYPE = ORC¶

TRIM_SPACE = TRUE | FALSE

布尔，指定是否从字符串中移除前导和尾随空格。

例如，如果您的外部数据库软件将字段放在引号中，但插入了前导空格，则 Snowflake 会读取前导空格而不是左引号字符作为字段的开头（即引号被解释为字段数据字符串的一部分）。将此选项设置为 TRUE 可移除不需要的空格。

此文件格式选项仅适用于以下操作：

• 查询暂存 ORC 数据文件中的对象值。

• 使用 MATCH_BY_COLUMN_NAME 复制选项查询单独列中的 ORC 数据。

• 通过在 COPY 语句中指定查询来查询单独列中的 ORC 数据（即 COPY 转换）。

默认：FALSE

REPLACE_INVALID_CHARACTERS = TRUE | FALSE

布尔，指定是否将无效的 UTF-8 字符替换为 Unicode 替换字符 (�)。此选项执行一对一的字符替换。

如果设置为 TRUE，Snowflake 会将无效的 UTF-8 字符替换为 Unicode 替换字符。

如果设置为 FALSE，则加载操作在检测到无效的 UTF-8 字符编码时产生错误。

默认：FALSE

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

用于与 SQL NULL 相互转换的字符串。Snowflake 用 SQL NULL 替换数据源中的这些字符串。若要指定多个字符串，请将字符串列表放在括号中，并使用逗号分隔每个值。

请注意，无论数据类型如何，Snowflake 都会将该值的所有实例转换为 NULL。例如，如果 2 指定为值，则转换为字符串或数字的所有 2 实例。

例如：

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

请注意，此选项可以包含空字符串。

在暂存 ORC 数据文件中查询对象值时，会应用此文件格式选项。

默认：``N``（即 NULL）

TYPE = PARQUET¶

COMPRESSION = AUTO | SNAPPY | NONE

指定 Parquet 文件中列的当前压缩算法的字符串（常量）。

支持的值	备注
AUTO	自动检测压缩算法。支持以下压缩算法：Brotli、gzip、Lempel-Ziv-Oberhumer (LZO)、LZ4、Snappy 或 Zstandard v0.8（及更高版本）。
SNAPPY
NONE	数据文件尚未压缩。

默认：AUTO

BINARY_AS_TEXT = TRUE | FALSE

布尔，指定是否将未定义逻辑数据类型的列解释为 UTF-8 文本。当设置为 FALSE 时，Snowflake 会将这些列解释为二进制数据。

默认：TRUE

备注

Snowflake 建议您将 BINARY_AS_TEXT 设置为 FALSE 以避免任何潜在的转换问题。

REPLACE_INVALID_CHARACTERS = TRUE | FALSE

布尔，指定是否将无效的 UTF-8 字符替换为 Unicode 替换字符 (�)。此选项执行一对一的字符替换。

如果设置为 TRUE，Snowflake 会将无效的 UTF-8 字符替换为 Unicode 替换字符。

如果设置为 FALSE，则加载操作在检测到无效的 UTF-8 字符编码时产生错误。

默认：FALSE

从分区列表达式自动添加的分区¶

创建一个外部表，其中包含从分区列定义中的表达式计算的分区。

在以下示例的步骤 2 中，数据文件按以下结构在云存储中组织：logs/YYYY/MM/DD/HH24。例如：

• logs/2018/08/05/0524/

• logs/2018/08/27/1408/

1. 为存储数据文件的存储位置创建一个名为 s1 的外部暂存区。有关更多信息，请参阅 CREATE STAGE。

   暂存区定义包括路径 /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. 查询暂存数据中的 METADATA$FILENAME 伪列，然后使用结果来开发分区列：

   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. 创建已分区的外部表。

   分区列 date_part 使用 TO_DATE、DATE 将 METADATA$FILENAME 伪列中的 YYYY/MM/DD 转换为日期。SQL 命令还将 Parquet 指定为文件格式类型。

   Amazon S3 和 Microsoft Azure 云存储的外部表包括由来自相应云消息传递服务的事件通知触发时自动刷新元数据所需的参数：

   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. 刷新外部表元数据：

   ALTER EXTERNAL TABLE et1 REFRESH;
   

   Copy

查询外部表时，使用 WHERE 子句按分区列筛选数据。Snowflake 仅扫描指定分区中符合筛选条件的文件：

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

手动添加的分区¶

使用用户定义的分区创建外部表（即分区由外部表所有者手动添加）。

1. 为存储数据文件的存储位置创建一个名为 s2 的外部暂存区：

   暂存区定义包括路径 /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. 创建已分区的外部表。外部表包括三个具有不同数据类型的分区列。

   以下规则适用：

   • 分区表达式中的列名区分大小写。

   • 分区列名 必须 为大写，除非列名放在双引号内。或者，在 SQL 表达式中使用 GET_IGNORE_CASE 而不是区分大小写的 : 字符。

   • 如果列名放在双引号内（例如 "Column1"），则分区列名也必须放在双引号内，并且与列名完全匹配。

   三种云存储服务（Amazon S3、Google Cloud Storage 和 Microsoft Azure）的语法相同，因为外部表元数据不会刷新：

   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. 为分区列添加分区：

   ALTER EXTERNAL TABLE et2 ADD PARTITION(col1='2022-01-24', col2='a', col3='12') LOCATION '2022/01';
   

   Copy

   Snowflake 将分区添加到外部表的元数据中。该操作还会将指定位置的任何新数据文件添加到元数据中：

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

查询外部表时，使用 WHERE 子句按分区列筛选数据。此示例按记录在暂存数据文件中的存储顺序返回记录：

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

外部表上的物化视图¶

基于 从分区列表达式自动添加的分区 中创建的外部表中列的子查询创建物化视图示例：

CREATE MATERIALIZED VIEW et1_mv
  AS
  SELECT col2 FROM et1;

有关此 SQL 命令的一般语法、用法说明和更多示例，请参阅 CREATE MATERIALIZED VIEW。

使用检测到的列定义创建的外部表¶

创建一个外部表，其中的列定义源自一组包含 Avro、Parquet 或 ORC 数据的暂存文件。

以下示例以 INFER_SCHEMA 主题中的一个示例为基础：

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

如果返回的结果大于 16 MB，则在 ARRAY_AGG(OBJECT_CONSTRUCT()) 中使用 * 可能会导致错误。避免对较大的结果集使用 *，而是仅将所需的列 COLUMN NAME、TYPE 和 NULLABLE 用于查询，如以下示例所示。使用 WITHIN GROUP (ORDER BY order_id) 时可以包含可选列 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