外部表简介¶

外部表是 Snowflake 的一项功能，它允许您查询存储在外部暂存区中的数据，就像数据位于 Snowflake 中的表内部一样。外部暂存区不是 Snowflake 的一部分，因此 Snowflake 不存储或管理暂存区。

外部表允许您（在 Snowflake 中）存储某些文件级别元数据，包括文件名、版本标识符和相关属性。外部表可以访问以 COPY INTO <table> 命令支持的任何格式存储的数据。

外部表是只读的。您不能对它们执行数据操作语言 (DML) 操作。但是，您可以使用外部表进行查询和联接操作。也可以创建针对外部表的视图。

查询外部表中的数据可能比查询本地存储在 Snowflake 中表内的数据慢。若要提高查询性能，可以使用基于外部表的物化视图。

备注

如果 Snowflake 在查询操作期间扫描云存储中的文件时遇到错误，则会跳过该文件并继续扫描下一个文件。查询可以部分扫描文件并返回遇到错误之前扫描的行。

本主题内容：

规划外部表的架构¶

本部分描述可用于设计外部表的选项。

读取时的架构¶

所有外部表都包括以下列：

VALUE:: 表示外部文件中单个行的 VARIANT 类型列。
METADATA$FILENAME:: 一个伪列，标识外部表中包含的每个暂存数据文件的名称，包括其在暂存区中的路径。
METADATA$FILE_ROW_NUMBER:: 显示暂存数据文件中每个记录的行号的伪列。

要创建外部表，您只需略微了解源数据文件的文件格式和记录格式。不需要知道数据文件的架构。

注意，SELECT * 始终会返回 VALUE 列，其中所有常规或半结构化数据都投射到变体行。

虚拟列¶

如果您熟悉源数据文件的架构，则可以使用 VALUE 列和/或 METADATA$FILENAME 或 METADATA$FILE_ROW_NUMBER 伪列来创建用作表达式的额外虚拟列。扫描外部数据时，数据文件中任何指定字段或半结构化数据元素的数据类型必须与外部表中这些额外列的数据类型匹配。这允许对外部数据进行强类型检查和架构验证。

常规文件大小调整建议¶

为了优化查询外部表时的并行扫描操作数，我们建议每种格式的文件或行组大小如下：

格式	推荐大小范围	备注
Parquet 文件	256 - 512 MB
Parquet 行组	16 - 256 MB	注意，当 Parquet 文件包含多个行组时，Snowflake 可以在不同服务器中的每个行组上操作。为了提高查询性能，建议将 Parquet 文件的大小调整到建议的范围内；或者，如果需要较大的文件大小，可以在每个文件中包含多个行组。
所有其他受支持的文件格式	16 - 256 MB

为了在查询大型数据文件时优化性能，请创建和查询外部表上的物化视图。

已分区的外部表¶

我们强烈建议对外部表进行分区，这需要使用逻辑路径整理好基础数据，其中包括日期、时间、国家/地区或路径中的类似维度。分区会使用分区列将外部表数据划分为多个部分。

外部表定义可以包括多个分区列，这些分区列会对外部数据强制使用多维结构。分区存储在外部表元数据中。

分区的好处包括提高查询性能。由于外部数据被划分为单独的切片/部分，因此如果处理一小部分数据而不是扫描整个数据集，查询响应时间会更快。

根据您的个人应用场景，您也可以：

通过刷新为每个分区列定义表达式的外部表来自动添加新分区。
手动添加新分区。

在创建外部表时，使用 CREATE EXTERNAL TABLE ...PARTITION BY 语法定义分区列。创建外部表后，无法更改添加分区的方法。

以下各部分将更详细地解释用于添加分区的不同选项。有关示例，请参见 CREATE EXTERNAL TABLE。

自动添加的分区¶

外部表创建器将新外部表中的分区列定义为用于解析存储在 METADATA$FILENAME 伪列中的路径和/或文件名信息的表达式。分区由与分区列表达式中的路径和/或文件名匹配的所有数据文件组成。

基于表达式自动添加分区的 CREATE EXTERNAL TABLE 语法如下：

CREATE EXTERNAL TABLE
  <table_name>
     ( <part_col_name> <col_type> AS <part_expr> )
     [ , ... ]
  [ PARTITION BY ( <part_col_name> [, <part_col_name> ... ] ) ]
  ..

Copy

刷新外部表元数据时，Snowflake 会根据定义的分区列表达式计算和添加分区。默认情况下，创建对象时会自动刷新元数据。此外，对象所有者可以将元数据配置为在外部暂存区中有新的或更新后的数据文件可用时自动刷新。所有者也可以通过执行 ALTER EXTERNAL TABLE ... REFRESH 命令来手动刷新元数据。

手动添加的分区¶

外部表创建者将新外部表的分区类型确定为 用户定义，并仅指定分区列的数据类型。如果您希望有选择地添加和删除分区，而不是为外部存储位置中与表达式匹配的所有新文件自动添加分区，请使用此选项。

通常选择此选项来同步外部表与其他元存储（例如 AWS Glue 或 Apache Hive）。

手动添加分区的 CREATE EXTERNAL TABLE 语法如下：

CREATE EXTERNAL TABLE
  <table_name>
     ( <part_col_name> <col_type> AS <part_expr> )
     [ , ... ]
  [ PARTITION BY ( <part_col_name> [, <part_col_name> ... ] ) ]
  PARTITION_TYPE = USER_SPECIFIED
  ..

Copy

包括所需的 PARTITION_TYPE = USER_SPECIFIED 参数。

分区列的定义是解析内部（隐藏）METADATA$EXTERNAL_TABLE_PARTITION 列中的列元数据的表达式。

对象所有者通过执行 ALTER EXTERNAL TABLE ... ADD PARTITION 命令，向外部表元数据手动添加分区：

ALTER EXTERNAL TABLE <name> ADD PARTITION ( <part_col_name> = '<string>' [ , <part_col_name> = '<string>' ] ) LOCATION '<path>'

Copy

不支持使用用户定义的分区自动刷新外部表。尝试手动刷新这种类型的外部表会产生用户错误。

Delta Lake 支持¶

Delta Lake (https://delta.io/) 是数据湖中的一种表格式，支持 ACID（原子性、一致性、隔离性、持久性）事务以及其他功能。Delta Lake 中的所有数据都以 Apache Parquet 格式存储。创建引用使用 Delta Lake 增强的云存储位置的外部表。

若要创建引用 Delta Lake 的外部表，请设置 CREATE EXTERNAL TABLE 语句中的 TABLE_FORMAT = DELTA 参数。

设置此参数后，外部表将扫描 [ WITH ] LOCATION 位置中的 Delta Lake 事务日志文件。Delta 日志文件的名称为 _delta_log/00000000000000000000.json、_delta_log/00000000000000000010.checkpoint.parquet 等。刷新外部表的元数据时，Snowflake 将解析 Delta Lake 事务日志并确定哪些 Parquet 文件是当前文件。在后台，刷新会执行文件添加和移除操作，以保持外部表元数据同步。

请注意，云存储中的 DDL 操作触发的事件通知顺序不受保证。因此，自动刷新元数据的能力不适用于引用 Delta Lake 文件的外部表。请改为定期执行 ALTER EXTERNAL TABLE ... REFRESH 语句，以注册任何添加或移除的文件。

有关更多信息（包括示例），请参阅 CREATE EXTERNAL TABLE。

添加或删除列¶

使用以下 ALTER TABLE 语法更改现有外部表以添加或删除列：

添加列： ALTER TABLE ...ADD COLUMN。
删除列： ALTER TABLE ...DROP COLUMN。

备注

不能删除默认的 VALUE 列以及 METADATA$FILENAME 和 METADATA$FILE_ROW_NUMBER 伪列。

请参见 ALTER TABLE 中的示例。

保护外部表¶

可以使用掩码策略和行访问策略来保护外部表。有关详细信息，请参阅：

外部表上的物化视图¶

在许多情况下，外部表的物化视图可以提供比基础外部表的等效查询更快的性能。当查询频繁运行或足够复杂时，这种性能差异可能很明显。

刷新任何已查询外部表中的文件级别元数据，以便物化视图反映引用的云存储位置中的当前文件集。

您可以使用云存储服务的事件通知服务自动刷新外部表的元数据，也可以使用 ALTER EXTERNAL TABLE ...REFRESH 语句手动刷新。

自动刷新外部表元数据¶

外部表的元数据可以使用云存储服务的事件通知服务自动刷新。

刷新操作会将元数据与外部暂存区和路径中的一组近期关联文件同步，即：

路径中的新文件将添加到表元数据中。

对路径中文件的更改将在表元数据中更新。

路径中不再存在的文件将从表元数据中移除。

有关更多信息，请参阅自动刷新 External Tables。

外部表计费¶

管理自动刷新外部表元数据的事件通知的开销会包含在费用中。此开销随着为外部表指定的外部暂存区和路径在云存储中添加的文件数量而增加。此开销会在账单中显示为 Snowpipe 费用，因为自动外部表刷新的事件通知会使用 Snowpipe。您可以通过查询 PIPE_USAGE_HISTORY 函数或检查 Account Usage PIPE_USAGE_HISTORY 视图来估算此费用。

此外，手动刷新外部表元数据会产生一笔较小的维护开销（使用 ALTER EXTERNAL TABLE ...REFRESH）。与 Snowflake 中的所有类似活动一样，此开销根据标准云服务计费模型收取。手动刷新标准外部表只是云服务操作；而使用 Delta Lake 增强的外部表的手动刷新则依赖于用户管理的计算资源（即虚拟仓库）。

如果用户担任 ACCOUNTADMIN 角色或担任具有全局 MONITOR USAGE 权限的角色，则可以查询 AUTO_REFRESH_REGISTRATION_HISTORY 表功能，检索在指定对象的元数据中注册的数据文件的历史记录以及为这些操作计费的 credit。

工作流程¶

Amazon S3¶

本部分简要概述了引用 Amazon S3 暂存区的外部表的设置和加载工作流程。有关完整说明，请参阅自动刷新 Amazon S3 的外部表。

（使用 CREATE STAGE）创建一个已命名的暂存区对象，该对象引用暂存数据文件的外部位置（即 S3 桶）。
（使用 CREATE EXTERNAL TABLE）创建引用已命名暂存区的外部表。
手动刷新外部表元数据（使用 ALTER EXTERNAL TABLE ...REFRESH），以将元数据与暂存区路径中的当前文件列表同步。此步骤还会验证外部表定义中的设置。
为 S3 桶配置事件通知。Snowflake 依赖于事件通知来持续刷新外部表元数据，以保持与暂存文件的一致性。
再次手动刷新外部表元数据（使用 ALTER EXTERNAL TABLE ...REFRESH），以将元数据与执行第 3 步之后发生的任何变更同步。此后，S3 事件通知会自动触发元数据刷新。
为任何其他角色配置 Snowflake 访问控制权限，以授予他们对外部表的查询访问权限。

Google Cloud Storage¶

本部分简要概述了引用 Google Cloud Storage (GCS) 暂存区的外部表的设置和加载工作流程。

为 GCS 事件配置 Google Pub/Sub 订阅。
在 Snowflake 中创建通知集成。通知集成是一个 Snowflake 对象，它在 Snowflake 和诸如 Pub/Sub 这样的第三方云消息队列服务之间提供接口。
创建一个已命名的暂存区对象（使用 CREATE STAGE），引用暂存数据文件的外部位置（即 GCS 桶）。
（使用 CREATE EXTERNAL TABLE）创建一个引用已命名暂存区和集成的外部表。
手动刷新一次外部表元数据（使用 ALTER EXTERNAL TABLE ...REFRESH），以将元数据与执行第 4 步之后发生的任何变更同步。此后，Pub/Sub 通知会自动触发元数据刷新。
为任何其他角色配置 Snowflake 访问控制权限，以授予他们对外部表的查询访问权限。

Microsoft Azure¶

本部分简要概述了引用 Amazon Azure 暂存区的外部表的设置和加载工作流程。有关完整说明，请参阅自动刷新 Azure Blob 存储的外部表。

为 Azure 存储事件配置事件网格订阅。
在 Snowflake 中创建通知集成。通知集成是一个 Snowflake 对象，它在 Snowflake 和诸如 Microsoft 事件网格这样的第三方云消息队列服务之间提供接口。
创建一个已命名的暂存区对象（使用 CREATE STAGE），引用暂存数据文件的外部位置（即 Azure 容器）。
（使用 CREATE EXTERNAL TABLE）创建一个引用已命名暂存区和集成的外部表。
手动刷新一次外部表元数据（使用 ALTER EXTERNAL TABLE ...REFRESH），以将元数据与执行第 4 步之后发生的任何变更同步。此后，事件网格通知会自动触发元数据刷新。
为任何其他角色配置 Snowflake 访问控制权限，以授予他们对外部表的查询访问权限。

查询外部表¶

像查询标准表一样查询外部表。

筛选 Parquet 文件中的记录¶

若要利用行组统计信息来修剪 Parquet 文件中的数据，WHERE 子句可以包含分区列或常规列，或两者都包含。以下限制适用：

子句不能包含任何 VARIANT 列。
子句只能包含以下一个或多个比较运算符：
- =
- >
- <
子句只能包含一个或多个逻辑/布尔运算符，以及 STARTSWITH SQL 功能。

此外，采用 "value:<path>::<data type>" 形式（或等效 GET/ GET_PATH、: 函数）的查询会利用矢量化扫描器。采用 "value" 或简单的 "value:<path>" 形式的查询则会使用非矢量化扫描器进行处理。对于使用矢量化扫描器的查询，使用 CONVERT_TIMEZONE 函数将所有时区数据转换为标准时区。

当文件按查询筛选器中包含的键排序时，且文件中有多个行组，则可能获得更好的修剪结果。

下表显示了类似的查询结构，用于说明本部分中的行为，其中 et 是外部表，c1、c2 和 c3 则为虚拟列：

已优化	未优化
`SELECT c1, c2, c3 FROM et;`	`SELECT value:c1, c2, c3 FROM et;`
`SELECT c1, c2, c3 FROM et WHERE c1 = 'foo';` `SELECT c1, c2, c3 FROM et WHERE value:c1::string = 'foo';`	`SELECT c1, c2, c3 FROM et WHERE value:c1 = 'foo';`

已优化

未优化

SELECT c1, c2, c3 FROM et;

SELECT value:c1, c2, c3 FROM et;

SELECT c1, c2, c3 FROM et WHERE c1 = 'foo';

SELECT c1, c2, c3 FROM et WHERE value:c1::string = 'foo';

SELECT c1, c2, c3 FROM et WHERE value:c1 = 'foo';

持久化查询结果¶

与表类似，外部表的查询结果将持续 24 小时。在这 24 小时内，以下操作会使外部表的查询结果缓存失效并将其清除：

任何修改外部表定义的 DDL 操作。其中包括显式修改外部表定义（使用 ALTER EXTERNAL TABLE）或重新创建外部表（使用 CREATE OR REPLACE EXTERNAL TABLE）。
云存储中使用外部表元数据中注册的文件集的更改。无论是使用存储位置的事件通知服务的自动刷新操作，还是使用 ALTER EXTERNAL TABLE ... REFRESH 的手动刷新操作，都会使结果缓存失效。

注意，在以下情况下，对云存储中引用文件的更改不会使查询结果缓存失效，并导致查询结果过期：

禁用自动刷新操作（即 AUTO_REFRESH = FALSE）或配置不正确。
未手动刷新外部表元数据。

从外部表元数据中移除旧的暂存文件¶

存储过程可以使用 ALTER EXTERNAL TABLE ...REMOVE FILES 语句。存储过程将根据文件在暂存区中的新近修改日期从元数据中删除文件。

例如：

使用 CREATE PROCEDURE 语句创建存储过程：

CREATE or replace PROCEDURE remove_old_files(external_table_name varchar, num_days float)
  RETURNS varchar
  LANGUAGE javascript
  EXECUTE AS CALLER
  AS
  $$
  // 1. Get the relative path of the external table
  // 2. Find all files registered before the specified time period
  // 3. Remove the files


  var resultSet1 = snowflake.execute({ sqlText:
    `call exttable_bucket_relative_path('` + EXTERNAL_TABLE_NAME + `');`
  });
  resultSet1.next();
  var relPath = resultSet1.getColumnValue(1);


  var resultSet2 = snowflake.execute({ sqlText:
    `select file_name
     from table(information_schema.EXTERNAL_TABLE_FILES (
         TABLE_NAME => '` + EXTERNAL_TABLE_NAME +`'))
     where last_modified < dateadd(day, -` + NUM_DAYS + `, current_timestamp());`
  });

  var fileNames = [];
  while (resultSet2.next())
  {
    fileNames.push(resultSet2.getColumnValue(1).substring(relPath.length));
  }

  if (fileNames.length == 0)
  {
    return 'nothing to do';
  }


  var alterCommand = `ALTER EXTERNAL TABLE ` + EXTERNAL_TABLE_NAME + ` REMOVE FILES ('` + fileNames.join(`', '`) + `');`;

  var resultSet3 = snowflake.execute({ sqlText: alterCommand });

  var results = [];
  while (resultSet3.next())
  {
    results.push(resultSet3.getColumnValue(1) + ' -> ' + resultSet3.getColumnValue(2));
  }

  return results.length + ' files: \n' + results.join('\n');

  $$;

  CREATE or replace PROCEDURE exttable_bucket_relative_path(external_table_name varchar)
  RETURNS varchar
  LANGUAGE javascript
  EXECUTE AS CALLER
  AS
  $$
  var resultSet = snowflake.execute({ sqlText:
    `show external tables like '` + EXTERNAL_TABLE_NAME + `';`
  });

  resultSet.next();
  var location = resultSet.getColumnValue(10);

  var relPath = location.split('/').slice(3).join('/');
  return relPath.endsWith("/") ? relPath : relPath + "/";

  $$;

Copy

调用存储过程：

-- Remove all files from the exttable external table metadata:
call remove_old_files('exttable', 0);

-- Remove files staged longer than 90 days ago from the exttable external table metadata:
call remove_old_files('exttable', 90);

Copy

或者，使用 CREATE TASK 创建一个定期调用存储过程的任务，以从外部表元数据中删除较旧的文件。

Apache Hive 元存储集成¶

Snowflake 支持使用外部表将 Apache Hive (https://hive.apache.org/) 元存储与 Snowflake 集成。Hive 连接器检测元存储区事件并将其传输到 Snowflake，以保持外部表与 Hive 元存储的同步。这允许用户在 Hive 中管理数据，同时通过 Snowflake 对其进行查询。

有关说明，请参阅将 Apache Hive 元存储与 Snowflake 集成。

外部表 DDL¶

为了支持创建和管理外部表，Snowflake 提供了以下一组特殊 DDL 命令：

所需访问权限¶

创建和管理外部表需要至少具有以下角色权限的角色：

对象	权限
数据库	USAGE
架构	USAGE、CREATE、STAGE（如果创建新暂存区）、CREATE EXTERNAL TABLE
暂存区（如果使用现有暂存区）	USAGE

Information Schema¶

Snowflake Snowflake Information Schema 包括视图和表函数，您可以查询这些视图和表函数来检索有关外部表及其暂存数据文件的信息。

视图¶

EXTERNAL_TABLES 视图: 显示指定（或当前）数据库中外部表的信息。

表函数¶

AUTO_REFRESH_REGISTRATION_HISTORY: 检索在指定对象元数据中注册的数据文件的历史记录，以及为这些操作计费的 credit。
EXTERNAL_TABLE_FILES: 检索指定外部表的元数据中包含的暂存数据文件相关信息。
EXTERNAL_TABLE_FILE_REGISTRATION_HISTORY: 检索有关外部表的元数据历史记录的信息，包括刷新元数据时发现的任何错误。