Introduction to external tables¶外部表简介

An external table is a Snowflake feature that you can use to query data stored in an external stage as if the data were inside a table in Snowflake. The external stage is not part of Snowflake, so Snowflake doesn’t store or manage the stage. To harden your security posture, you can configure the external stage for outbound private connectivity to access the external table by using private connectivity.外部表是一项 Snowflake 功能，您可以使用它来查询存储在外部暂存区中的数据，就像这些数据位于 Snowflake 的表中一样。外部暂存区不属于 Snowflake，因此 Snowflake 不会存储或管理该暂存区。为了加强安全态势，您可以配置外部暂存区以实现出站私有连接，从而通过私有连接访问外部表。

External tables let you store (within Snowflake) certain file-level metadata, including filenames, version identifiers, and related properties. External tables can access data stored in any format that the COPY INTO <table> command supports, except XML.外部表允许您（在 Snowflake 内部）存储某些文件级元数据，包括文件名、版本标识符和相关属性。外部表可以访问 COPY INTO <table> 命令支持的任何格式的数据，但 XML 除外。

External tables are read-only. You can’t perform data manipulation language (DML) operations on external tables. However, you can use external tables for query and join operations. You can also create views against external tables.外部表是只读的。您无法对外部表执行数据操作语言 (DML) 操作。但是，您可以将外部表用于查询和连接操作。您还可以针对外部表创建视图。

Querying data in an external table might be slower than querying data that you store natively in a table within Snowflake. To improve query performance, you can use a materialized view based on an external table. For optimal query performance when you work with Parquet files, consider using Apache Iceberg™ tables instead.查询外部表中的数据可能比查询存储在 Snowflake 原生表中的数据要慢。为了提高查询性能，您可以使用基于外部表的物化视图。在处理 Parquet 文件时，若要获得最佳查询性能，请考虑改用 Apache Iceberg™ 表。

Planning the schema of an external table¶规划外部表的架构

The following sections describe the options available to you to plan your external tables.以下部分介绍了可用于规划外部表的选项。

Schema on read¶读取时架构 (Schema on read)

All external tables include the following columns:所有外部表都包含以下列：

VALUE:

A VARIANT type column that represents a single row in the external file.一个 VARIANT 类型列，代表外部文件中的单行数据。

METADATA$FILENAME:

A pseudocolumn that identifies the name of each staged data file that is included in the external table, including its path in the stage.一个伪列，用于标识包含在外部表中的每个暂存数据文件的名称，包括其在暂存区中的路径。

METADATA$FILE_ROW_NUMBER:

A pseudocolumn that shows the row number for each record in a staged data file.一个伪列，显示暂存数据文件中每条记录的行号。

To create external tables, you are only required to have some knowledge of the file format and record format of the source data files. Knowing the schema of the data files isn’t required.要创建外部表，您只需要了解源数据文件的文件格式和记录格式即可。无需了解数据文件的架构。

Virtual columns¶虚拟列

If you’re familiar with the schema of the source data files, you can create additional virtual columns as expressions by using the VALUE column and the METADATA$FILENAME or METADATA$FILE_ROW_NUMBER pseudocolumns. When the external data is scanned, the data types of any specified fields or semi-structured data elements in the data file must match the data types of these additional columns in the external table. This requirement enables strong type checking and schema validation over the external data.如果您熟悉源数据文件的架构，则可以使用 VALUE 列以及 METADATA$FILENAME 或 METADATA$FILE_ROW_NUMBER 伪列，通过表达式创建额外的虚拟列。扫描外部数据时，数据文件中任何指定字段或半结构化数据元素的数据类型必须与外部表中这些额外列的数据类型相匹配。此要求能够对外部数据进行强大的类型检查和架构验证。

General file sizing recommendations¶常规文件大小建议

To optimize the number of parallel scanning operations when you query external tables, we recommend the following file or row group sizes per format:为了在查询外部表时优化并行扫描操作的数量，我们针对每种格式建议以下文件或行组大小：

For optimal performance when querying large data files, create and query materialized views over external tables.若要在查询大型数据文件时获得最佳性能，请在外部表上创建并查询物化视图。

Partitioned external tables¶分区外部表

We strongly recommend partitioning your external tables, which requires that your underlying data is organized using logical paths that include date, time, country, or similar dimensions in the path. Partitioning divides your external table data into multiple parts using partition columns.我们强烈建议对外部表进行分区，这要求您的底层数据使用逻辑路径进行组织，并在路径中包含日期、时间、国家/地区或类似维度。分区使用分区列将外部表数据划分为多个部分。

An external table definition can include multiple partition columns, which impose a multi-dimensional structure on the external data. Partitions are stored in the external table metadata.外部表定义可以包含多个分区列，这些列对外部数据施加了多维结构。分区存储在外部表元数据中。

Partitioning improves query performance. Because the external data is partitioned into separate slices or parts, query response time is faster when processing a small part of the data instead of scanning the entire data set.分区可提高查询性能。由于外部数据被划分为单独的切片或部分，因此在处理数据的一小部分而不是扫描整个数据集时，查询响应时间会更快。

Based on your individual use cases, you can do either of the following:根据您的具体用例，您可以执行以下任一操作：

• Add new partitions automatically by refreshing an external table that defines an expression for each partition column.通过刷新为每个分区列定义了表达式的外部表，自动添加新分区。
• Add new partitions manually.手动添加新分区。

Partition columns are defined when an external table is created, using the CREATE EXTERNAL TABLE … PARTITION BY syntax. After an external table is created, the method by which partitions are added can’t be changed.分区列是在创建外部表时使用 CREATE EXTERNAL TABLE … PARTITION BY 语法定义的。外部表创建后，添加分区的方法将无法更改。

The following sections explain the different options for adding partitions in greater detail. For examples, see CREATE EXTERNAL TABLE.以下部分更详细地解释了添加分区的不同选项。有关示例，请参阅 CREATE EXTERNAL TABLE。

Partitions added automatically¶自动添加的分区

An external table creator defines partition columns in a new external table as expressions that parse the path or filename information stored in the METADATA$FILENAME pseudocolumn. A partition consists of all data files that match the path or filename in the expression for the partition column.外部表创建者在新外部表中将分区列定义为解析存储在 METADATA$FILENAME 伪列中的路径或文件名信息的表达式。分区由所有与分区列表达式中的路径或文件名匹配的数据文件组成。

The following CREATE EXTERNAL TABLE syntax adds partitions automatically based on expressions:以下 CREATE EXTERNAL TABLE 语法可根据表达式自动添加分区：

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

Snowflake computes and adds partitions based on the defined partition column expressions when external table metadata is refreshed. By default, the metadata is refreshed automatically when the object is created. In addition, the object owner can configure the metadata to refresh automatically when new or updated data files are available in the external stage. The owner can alternatively refresh the metadata manually by executing the ALTER EXTERNAL TABLE … REFRESH command.当刷新外部表元数据时，Snowflake 会根据定义的分区列表达式计算并添加分区。默认情况下，元数据会在对象创建时自动刷新。此外，对象所有者可以配置元数据，使其在外部暂存区中有新的或更新的数据文件可用时自动刷新。或者，所有者也可以通过执行 ALTER EXTERNAL TABLE … REFRESH 命令手动刷新元数据。

Partitions added manually¶手动添加的分区

An external table creator determines the partition type of a new external table as user-defined and specifies only the data types of partition columns. Use this option when you prefer to add and remove partitions selectively rather than add partitions automatically for all new files in an external storage location that match an expression.外部表创建者将新外部表的分区类型确定为用户定义，并仅指定分区列的数据类型。当您倾向于选择性地添加和删除分区，而不是为外部存储位置中所有与表达式匹配的新文件自动添加分区时，请使用此选项。

You generally choose this option to synchronize external tables with other metastores (for example, AWS Glue or Apache Hive).通常选择此选项是为了将外部表与其他元存储（例如 AWS Glue 或 Apache Hive）同步。

The following CREATE EXTERNAL TABLE syntax manually adds partitions:以下 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
  ..

Include the required PARTITION_TYPE = USER_SPECIFIED parameter.包含必需的 PARTITION_TYPE = USER_SPECIFIED 参数。

The partition column definitions are expressions that parse the column metadata in the internal (hidden) METADATA$EXTERNAL_TABLE_PARTITION column.分区列定义是解析内部（隐藏）METADATA$EXTERNAL_TABLE_PARTITION 列中列元数据的表达式。

The object owner adds partitions to the external table metadata manually by running the ALTER EXTERNAL TABLE … ADD PARTITION command:对象所有者通过运行 ALTER EXTERNAL TABLE … ADD PARTITION 命令手动将分区添加到外部表元数据中：

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

Automatically refreshing an external table with user-defined partitions isn’t supported. Attempting to manually refresh this type of external table produces a user error.不支持自动刷新具有用户定义分区的外部表。尝试手动刷新此类外部表会产生用户错误。

Delta Lake support¶Delta Lake 支持

Delta Lake is a table format on your data lake that supports ACID (atomicity, consistency, isolation, durability) transactions among other features. All data in Delta Lake is stored in Apache Parquet format. You can create external tables that reference your cloud storage locations enhanced with Delta Lake.Delta Lake 是数据湖上的一种表格式，它支持 ACID（原子性、一致性、隔离性、持久性）事务以及其他功能。Delta Lake 中的所有数据均以 Apache Parquet 格式存储。您可以创建引用已通过 Delta Lake 增强的云存储位置的外部表。

To create an external table that references a Delta Lake, set the TABLE_FORMAT = DELTA parameter in the CREATE EXTERNAL TABLE statement.要创建引用 Delta Lake 的外部表，请在 CREATE EXTERNAL TABLE 语句中设置 TABLE_FORMAT = DELTA 参数。

When you set this parameter, the external table scans for Delta Lake transaction log files in the [ WITH ] LOCATION location. Delta log files have names like _delta_log/00000000000000000000.json or _delta_log/00000000000000000010.checkpoint.parquet. When the metadata for an external table is refreshed, Snowflake parses the Delta Lake transaction logs and determines which Parquet files are current. In the background, the refresh performs add and remove file operations to keep the external table metadata in sync.设置此参数后，外部表会扫描 [ WITH ] LOCATION 位置中的 Delta Lake 事务日志文件。Delta 日志文件具有类似 _delta_log/00000000000000000000.json 或 _delta_log/00000000000000000010.checkpoint.parquet 的名称。当刷新外部表的元数据时，Snowflake 会解析 Delta Lake 事务日志并确定哪些 Parquet 文件是当前的。在后台，刷新操作会执行添加和删除文件操作，以保持外部表元数据同步。

For more information, including examples, see CREATE EXTERNAL TABLE.有关更多信息（包括示例），请参阅 CREATE EXTERNAL TABLE。

Migrate a Delta external table to Apache Iceberg™¶将 Delta 外部表迁移到 Apache Iceberg™

To migrate one or more external tables that reference a Delta Lake to Apache Iceberg™ tables, complete the following steps:要将一个或多个引用 Delta Lake 的外部表迁移到 Apache Iceberg™ 表，请完成以下步骤：

1. Use the SHOW EXTERNAL TABLES command to retrieve the location (external stage and folder path) for the external table(s).使用 SHOW EXTERNAL TABLES 命令检索外部表的位置（外部暂存区和文件夹路径)。

   For example, the following command returns information for external tables and filters on names like my_delta_ext_table:例如，以下命令返回外部表的信息，并筛选名称类似于 my_delta_ext_table 的表：

   Copy code

   SHOW EXTERNAL TABLES LIKE 'my_delta_ext_table';
   

2. Create an external volume; specify the location that you retrieved in the previous step as the STORAGE_BASE_URL.创建外部卷；将您在上一步中检索到的位置指定为 STORAGE_BASE_URL。

   To create a single external volume for multiple Delta tables under the same storage location, set the external volume’s active location (STORAGE_BASE_URL) as the common root directory.要为同一存储位置下的多个 Delta 表创建一个外部卷，请将外部卷的活动位置 (STORAGE_BASE_URL) 设置为公共根目录。

   For example, consider the following locations for three Delta tables that branch from the same storage location:例如，考虑以下三个从同一存储位置分支出来的 Delta 表的位置：

   • s3://my-bucket/delta-ext-table-1/s3://my-bucket/delta-ext-table-1/
   • s3://my-bucket/delta-ext-table-2/s3://my-bucket/delta-ext-table-2/
   • s3://my-bucket/delta-ext-table-3/s3://my-bucket/delta-ext-table-3/

   As shown in the following example, specify the bucket as the STORAGE_BASE_URL when you create the external volume. Later, you can specify the relative path to the table files (for example, delta-ext-table-1/) as the BASE_LOCATION when you create an Iceberg table:如下例所示，在创建外部卷时将存储桶指定为 STORAGE_BASE_URL。稍后，在创建 Iceberg 表时，您可以将表文件的相对路径（例如 delta-ext-table-1/）指定为 BASE_LOCATION：

   Copy code

   CREATE OR REPLACE EXTERNAL VOLUME delta_migration_ext_vol
   STORAGE_LOCATIONS = (
     (
    NAME = storage_location_1
    STORAGE_PROVIDER = 'S3'
    STORAGE_BASE_URL = 's3://my-bucket/'
    STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::123456789123:role/my-storage-role' )
   );
   

3. Create a catalog integration for Delta tables.为 Delta 表创建目录集成。

4. Create an Iceberg table by using the CREATE ICEBERG TABLE (Delta files in object storage) command. The BASE_LOCATION of the external volume must point to the existing external table location.使用 CREATE ICEBERG TABLE（对象存储中的 Delta 文件）命令创建 Iceberg 表。外部卷的 BASE_LOCATION 必须指向现有的外部表位置。

   The following example creates an Iceberg table based on external table files located in s3://my-bucket/delta-ext-table-1/, and references the external volume created previously. To determine the full storage location for the table, Snowflake appends the BASE_LOCATION to the STORAGE_BASE_URL of the external volume:以下示例创建了一个基于位于 s3://my-bucket/delta-ext-table-1/ 的外部表文件的 Iceberg 表，并引用了之前创建的外部卷。为了确定表的完整存储位置，Snowflake 会将 BASE_LOCATION 追加到外部卷的 STORAGE_BASE_URL 之后：

   Copy code

   CREATE ICEBERG TABLE my_delta_table_1
     BASE_LOCATION = 'delta-ext-table-1'
     EXTERNAL_VOLUME = 'delta_migration_ext_vol'
     CATALOG = 'delta_catalog_integration';
   

5. Drop the external table:删除外部表：

   Copy code

   DROP EXTERNAL TABLE my_delta_ext_table_1;
   

Add or drop columns¶添加或删除列

To alter an existing external table to add or remove columns, use the following ALTER TABLE syntax:要更改现有外部表以添加或删除列，请使用以下 ALTER TABLE 语法：

• Add columns: ALTER TABLE … ADD COLUMN.添加列：ALTER TABLE … ADD COLUMN。
• Remove columns: ALTER TABLE … DROP COLUMN.删除列：ALTER TABLE … DROP COLUMN。

For more information, see the example in ALTER TABLE.有关更多信息，请参阅 ALTER TABLE 中的示例。

Protection of external tables¶外部表的保护

You can protect an external table by using a masking policy and a row access policy. For more information, see the following topics:您可以使用掩码策略和行访问策略来保护外部表。有关更多信息，请参阅以下主题：

• Masking policies and external tables.掩码策略和外部表。
• Row access policies and external tables.行访问策略和外部表。

Materialized views over external tables¶外部表上的物化视图

In many cases, materialized views over external tables can provide faster performance than equivalent queries over the underlying external table. When you run a query frequently or your query is sufficiently complex, materialized views can be significantly faster.在许多情况下，外部表上的物化视图可以提供比底层外部表上的等效查询更快的性能。当您频繁运行查询或查询足够复杂时，物化视图的速度会显著加快。

Refresh the file-level metadata in any queried external tables so that your materialized views reflect the current set of files in the referenced cloud storage location.刷新任何查询过的外部表中的文件级元数据，以便您的物化视图反映引用云存储位置中的当前文件集。

You can refresh the metadata for an external table automatically by using the event notification service for your cloud storage service or by manually using ALTER EXTERNAL TABLE … REFRESH statements.您可以使用云存储服务的事件通知服务自动刷新外部表的元数据，或者手动使用 ALTER EXTERNAL TABLE … REFRESH 语句进行刷新。

Automatically refreshing external table metadata¶自动刷新外部表元数据

You can automatically refresh the metadata for an external table by using the event notification service for your cloud storage service.您可以使用云存储服务的事件通知服务自动刷新外部表的元数据。

The refresh operation synchronizes the metadata with the latest set of associated files in the external stage and path, that is:刷新操作会将元数据与外部暂存区和路径中的最新相关文件集同步，即：

• 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.路径中不再存在的文件会从表元数据中删除。

For more information, see Refresh external tables automatically.有关更多信息，请参阅自动刷新外部表。

Billing for external tables¶外部表的计费

Snowflake includes an overhead in your charges to manage event notifications for the automatic refreshing of external table metadata. This overhead increases in relation to the number of files added in cloud storage for the external stages and paths specified for your external tables. This overhead charge appears as Snowpipe charges in your billing statement because Snowpipe is used for event notifications for the automatic external table refreshes. You can estimate this charge by querying the PIPE_USAGE_HISTORY function or examining the Account Usage PIPE_USAGE_HISTORY view.Snowflake 会将管理外部表元数据自动刷新的事件通知的开销计入您的费用。此开销会随着为外部表指定的外部暂存区和路径在云存储中添加的文件数量而增加。此开销费用在您的账单中显示为 Snowpipe 费用，因为 Snowpipe 用于自动刷新外部表的事件通知。您可以通过查询 PIPE_USAGE_HISTORY 函数或查看 Account Usage PIPE_USAGE_HISTORY 视图来估算此费用。

In addition, Snowflake includes a small maintenance-overhead charge for manually refreshing the external table metadata (using ALTER EXTERNAL TABLE … REFRESH). This overhead is charged in accordance with the standard cloud services billing model, like all similar activity in Snowflake. Manual refreshes of standard external tables are cloud services operations only; however, manual refreshes of external tables enhanced with Delta Lake rely on user-managed compute resources (that is, a virtual warehouse).此外，Snowflake 还包含一笔用于手动刷新外部表元数据（使用 ALTER EXTERNAL TABLE … REFRESH）的小额维护开销费用。此开销根据标准云服务计费模型收取，与 Snowflake 中的所有类似活动一样。标准外部表的手动刷新仅属于云服务操作；但是，通过 Delta Lake 增强的外部表的手动刷新依赖于用户管理的计算资源（即虚拟仓库）。

Users with the ACCOUNTADMIN role, or a role with the global MONITOR USAGE privilege, can query the AUTO_REFRESH_REGISTRATION_HISTORY table function to retrieve the history of data files registered in the metadata of specified objects and the credits that are billed for these operations.拥有 ACCOUNTADMIN 角色或拥有全局 MONITOR USAGE 权限的角色用户，可以查询 AUTO_REFRESH_REGISTRATION_HISTORY 表函数，以检索指定对象元数据中注册的数据文件的历史记录以及为这些操作计费的额度。

Overview of setup and load workflows¶设置和加载工作流概述

External tables don’t support storage versioning (S3 versioning, Object Versioning in Google Cloud Storage, or versioning for Azure Storage).外部表不支持存储版本控制（S3 版本控制、Google Cloud Storage 中的对象版本控制或 Azure 存储的版本控制）。

Amazon S3 workflow¶Amazon S3 工作流

The following steps provide a high-level overview of the setup and load workflow for external tables that reference Amazon S3 stages. For complete instructions, see Refresh external tables automatically for Amazon S3:以下步骤提供了引用 Amazon S3 暂存区的外部表的设置和加载工作流的高级概述。有关完整说明，请参阅自动刷新 Amazon S3 的外部表：

1. Create a named stage object (by using CREATE STAGE) that references the external location (that is, S3 bucket) where your data files are staged.创建一个引用数据文件存放的外部位置（即 S3 存储桶）的命名暂存区对象（使用 CREATE STAGE）。

2. Create an external table (by using CREATE EXTERNAL TABLE) that references the named stage.创建一个引用该命名暂存区的外部表（使用 CREATE EXTERNAL TABLE）。

3. Manually refresh the external table metadata by using ALTER EXTERNAL TABLE … REFRESH to synchronize the metadata with the current list of files in the stage path. This step also verifies the settings in your external table definition.使用 ALTER EXTERNAL TABLE … REFRESH 手动刷新外部表元数据，以将元数据与暂存区路径中的当前文件列表同步。此步骤还会验证外部表定义中的设置。

4. Configure an event notification for the S3 bucket. Snowflake relies on event notifications to continually refresh the external table metadata to maintain consistency with the staged files.为 S3 存储桶配置事件通知。Snowflake 依赖事件通知来持续刷新外部表元数据，以保持与暂存文件的一致性。

5. Manually refresh the external table metadata one more time by using ALTER EXTERNAL TABLE … REFRESH to synchronize the metadata with any changes that occurred after Step 3. Thereafter, the S3 event notifications trigger the metadata refresh automatically.使用 ALTER EXTERNAL TABLE … REFRESH 再手动刷新一次外部表元数据，以将元数据与步骤 3 之后发生的任何更改同步。此后，S3 事件通知将自动触发元数据刷新。

6. Configure Snowflake access control privileges for any additional roles to grant them query access to the external table.为任何其他角色配置 Snowflake 访问控制权限，以授予它们对外部表的查询访问权限。

Google Cloud Storage workflow¶Google Cloud Storage 工作流

The following steps provide a high-level overview of the setup and load workflow for external tables that reference Google Cloud Storage (GCS) stages:以下步骤提供了引用 Google Cloud Storage (GCS) 暂存区的外部表的设置和加载工作流的高级概述：

1. Configure a Google Pub/Sub subscription for GCS events.为 GCS 事件配置 Google Pub/Sub 订阅。
2. Create a notification integration in Snowflake. A notification integration is a Snowflake object that provides an interface between Snowflake and third-party cloud message queuing services such as Pub/Sub.在 Snowflake 中创建一个通知集成。通知集成是一个 Snowflake 对象，它提供了 Snowflake 与第三方云消息队列服务（如 Pub/Sub）之间的接口。
3. Create a named stage object (by using CREATE STAGE) that references the external location (that is, GCS bucket) where your data files are staged.创建一个引用数据文件存放的外部位置（即 GCS 存储桶）的命名暂存区对象（使用 CREATE STAGE）。
4. Create an external table (by using CREATE EXTERNAL TABLE) that references the named stage and integration.创建一个引用该命名暂存区和集成的外部表（使用 CREATE EXTERNAL TABLE）。
5. Manually refresh the external table metadata one time by using ALTER EXTERNAL TABLE … REFRESH to synchronize the metadata with any changes that occurred after Step 4. Thereafter, the Pub/Sub notifications trigger the metadata refresh automatically.使用 ALTER EXTERNAL TABLE … REFRESH 手动刷新一次外部表元数据，以将元数据与步骤 4 之后发生的任何更改同步。此后，Pub/Sub 通知将自动触发元数据刷新。
6. Configure Snowflake access control privileges for any additional roles to grant them query access to the external table.

Microsoft Azure workflow¶Microsoft Azure 工作流

The following steps provide a high-level overview of the setup and load workflow for external tables that reference Azure stages. For complete instructions, see Refresh external tables automatically for Azure Blob Storage:以下步骤提供了引用 Azure 暂存区的外部表的设置和加载工作流的高级概述。有关完整说明，请参阅自动刷新 Azure Blob 存储的外部表：

1. Configure an Event Grid subscription for Azure Storage events.为 Azure 存储事件配置 Event Grid 订阅。
2. Create a notification integration in Snowflake. A notification integration is a Snowflake object that provides an interface between Snowflake and third-party cloud message queuing services such as Microsoft Event Grid.在 Snowflake 中创建一个通知集成。通知集成是一个 Snowflake 对象，它提供了 Snowflake 与第三方云消息队列服务（如 Microsoft Event Grid）之间的接口。
3. Create a named stage object (by using CREATE STAGE) that references the external location (that is, Azure container) where your data files are staged.创建一个引用数据文件存放的外部位置（即 Azure 容器）的命名暂存区对象（使用 CREATE STAGE）。
4. Create an external table (by using CREATE EXTERNAL TABLE) that references the named stage and integration.
5. Manually refresh the external table metadata one time by using ALTER EXTERNAL TABLE … REFRESH to synchronize the metadata with any changes that occurred after Step 4. Thereafter, the Event Grid notifications trigger the metadata refresh automatically.使用 ALTER EXTERNAL TABLE … REFRESH 手动刷新一次外部表元数据，以将元数据与步骤 4 之后发生的任何更改同步。此后，Event Grid 通知将自动触发元数据刷新。
6. Configure Snowflake access control privileges for any additional roles to grant them query access to the external table.

Querying external tables¶查询外部表

Query external tables just as you would standard tables.像查询标准表一样查询外部表。

Snowflake omits query results for records that contain invalid UTF-8 data. After encountering invalid data, Snowflake continues to scan the file without returning an error message.Snowflake 会忽略包含无效 UTF-8 数据的记录的查询结果。遇到无效数据后，Snowflake 会继续扫描文件，而不会返回错误消息。

To avoid missing records in your query results caused by invalid UTF-8 data, specify REPLACE_INVALID_CHARACTERS = TRUE for your file format. Doing so replaces any invalid UTF-8 characters with the Unicode replacement character (�) when you query the table.为避免因无效 UTF-8 数据导致查询结果中丢失记录，请为您的文件格式指定 REPLACE_INVALID_CHARACTERS = TRUE。这样做会在您查询表时，将任何无效的 UTF-8 字符替换为 Unicode 替换字符 ()。

For Parquet files, you can also set BINARY_AS_TEXT = FALSE for your file format so that Snowflake interprets the columns with no defined logical data type as binary data instead of as UTF-8 text.对于 Parquet 文件，您还可以为文件格式设置 BINARY_AS_TEXT = FALSE，以便 Snowflake 将没有定义逻辑数据类型的列解释为二进制数据，而不是 UTF-8 文本。

Filtering records in Parquet files¶筛选 Parquet 文件中的记录

To use row group statistics to prune data in Parquet files, you can include either partition columns, regular columns, or both in a WHERE clause. The following limitations apply:要使用行组统计信息来修剪 Parquet 文件中的数据，您可以在 WHERE 子句中包含分区列、常规列或两者。适用以下限制：

• The clause can’t include any VARIANT columns.子句不能包含任何 VARIANT 列。

• The clause can only include one or more of the following comparison operators:子句只能包含一个或多个以下比较运算符：

  • =
  • >
  • <

• The clause can only include one or more logical/Boolean operators, as well as the STARTSWITH SQL function.子句只能包含一个或多个逻辑/布尔运算符，以及 STARTSWITH SQL 函数。

In addition, queries in the form "value:<path>::<data type>" (or the GET/ GET_PATH , : function equivalent) use the vectorized scanner. Queries in the form "value" or simply "value:<path>" are processed by using the non-vectorized scanner. Convert all time-zone data to a standard time zone by using the CONVERT_TIMEZONE function for queries that use the vectorized scanner.此外，形式为 "value:<path>::<data type>"（或等效的 GET/GET_PATH , : 函数）的查询使用向量化扫描程序。形式为 "value" 或仅 "value:<path>" 的查询使用非向量化扫描程序进行处理。对于使用向量化扫描程序的查询，请使用 CONVERT_TIMEZONE 函数将所有时区数据转换为标准时区。

You might get better pruning results when files are sorted by a key included in a query filter, and if there are multiple row groups in the files, .当文件按查询筛选器中包含的键排序，且文件中存在多个行组时，您可能会获得更好的修剪结果。

The following table shows similar query structures that show the behaviors in this section, where et is an external table and c1, c2, and c3 are virtual columns:下表显示了展示本节行为的类似查询结构，其中 et 是外部表，c1、c2 和 c3 是虚拟列：

Persisted query results¶持久化查询结果

Similar to tables, the query results for external tables persist for 24 hours. Within this 24-hour period, the following operations invalidate and purge the query result cache for external tables:与表类似，外部表的查询结果会保留 24 小时。在此 24 小时期间内，以下操作会使外部表的查询结果缓存失效并被清除：

• Any DDL operation that modifies the external table definition. This includes explicitly modifying the external table definition (by using ALTER EXTERNAL TABLE) or recreating the external table (by using CREATE OR REPLACE EXTERNAL TABLE).任何修改外部表定义的 DDL 操作。这包括显式修改外部表定义（使用 ALTER EXTERNAL TABLE）或重新创建外部表（使用 CREATE OR REPLACE EXTERNAL TABLE）。
• Changes in the set of files in cloud storage that are registered in the external table metadata. Either automatic refresh operations by using the event notification service for the storage location or manual refresh operations (by using ALTER EXTERNAL TABLE … REFRESH) invalidate the result cache.云存储中注册在外部表元数据中的文件集发生变化。使用存储位置的事件通知服务进行的自动刷新操作或手动刷新操作（使用 ALTER EXTERNAL TABLE … REFRESH）都会使结果缓存失效。

Example: Remove older staged files from external table metadata¶示例：从外部表元数据中删除较旧的暂存文件

The following steps provide an example of how you can use an ALTER EXTERNAL TABLE … REMOVE FILES statement to remove older staged files from the metadata in an external table . The stored procedure removes the files from the metadata based on their last modified date in the stage:以下步骤提供了一个示例，说明如何使用 ALTER EXTERNAL TABLE … REMOVE FILES 语句从外部表的元数据中删除较旧的暂存文件。存储过程根据文件在暂存区中的最后修改日期从元数据中删除这些文件：

1. Create the stored procedure by using a CREATE PROCEDURE statement:使用 CREATE PROCEDURE 语句创建存储过程：

   Copy codeExpand code block

   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 + "/";
   
     $$;
   

   Show lessSee more

   Scroll to top

2. Call the stored procedure:调用存储过程：

   Copy codeExpand code block

   -- 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);
   

   Alternatively, you can create a task by using CREATE TASK to call the stored procedure periodically to remove older files from the external table metadata.或者，您可以使用 CREATE TASK 创建一个任务，定期调用该存储过程，从外部表元数据中删除较旧的文件。

Apache Hive metastore integration¶Apache Hive 元存储集成

Snowflake supports integrating Apache Hive metastores with Snowflake by using external tables. The Hive connector detects metastore events and transmits the events to Snowflake to keep the external tables synchronized with the Hive metastore. With this capability, users can manage their data in Hive while querying it from Snowflake.Snowflake 支持通过使用外部表将 Apache Hive 元存储与 Snowflake 集成。Hive 连接器检测元存储事件并将事件传输到 Snowflake，以保持外部表与 Hive 元存储同步。利用此功能，用户可以在管理 Hive 中的数据的同时，从 Snowflake 对其进行查询。

For instructions, see Integrate Apache Hive metastores with Snowflake.有关说明，请参阅将 Apache Hive 元存储与 Snowflake 集成。

External table DDL¶外部表 DDL

To support creating and managing external tables, Snowflake provides the following set of special DDL commands:为了支持外部表的创建和管理，Snowflake 提供了一组特殊的 DDL 命令：

• CREATE EXTERNAL TABLE
• ALTER EXTERNAL TABLE
• DROP EXTERNAL TABLE
• DESCRIBE EXTERNAL TABLE
• SHOW EXTERNAL TABLES

Required access privileges¶必需的访问权限

Creating and managing external tables requires a role with a minimum of the following role permissions:创建和管理外部表需要一个至少具有以下角色权限的角色：

Information Schema¶Information Schema

The Snowflake Information Schema includes views and table functions you can query to retrieve information about your external tables and their staged data files.Snowflake Information Schema 包含视图和表函数，您可以查询它们来检索有关外部表及其暂存数据文件的信息。

View¶视图

EXTERNAL_TABLES view

Displays information for external tables in the specified (or current) database.显示指定（或当前）数据库中外部表的信息。

Table functions¶表函数

AUTO_REFRESH_REGISTRATION_HISTORY

Retrieve the history of data files that are registered in the metadata of specified objects and the credits billed for these operations.检索指定对象元数据中注册的数据文件的历史记录以及为这些操作计费的额度。

EXTERNAL_TABLE_FILES

Retrieve information about the staged data files that are included in the metadata for a specified external table.检索有关包含在指定外部表元数据中的暂存数据文件的信息。

EXTERNAL_TABLE_FILE_REGISTRATION_HISTORY

Retrieve information about the metadata history for an external table, including any errors found when refreshing the metadata.检索有关外部表元数据历史记录的信息，包括刷新元数据时发现的任何错误。