Snowflake 外部表

引言

数据平台中的外部表提供了一种查询存储在数据库外部（通常在云存储中，如 Amazon S3 或 Azure Blob Storage）的数据的方法，而无需导入。这使得企业能够利用其现有的云存储数据，而无需移动或复制数据。外部表有助于减少数据重复、最小化存储成本，并在云原生数据仓库环境（如 Snowflake、Amazon Redshift 或 BigQuery）中实现对大型数据集的无缝查询。

规划外部表的模式

在创建外部表时，仔细考虑模式配置对于性能和便利性至关重要。模式决定了外部数据如何被解释并以表格形式呈现，正确的规划可确保对这些表的查询高效且准确。

规划外部表时存在一些选项

• 数据类型：确保将外部数据中的适当数据类型映射到模式。
• 文件格式一致性：确保外部文件格式（例如 CSV、Parquet、ORC）与外部表支持的文件格式一致。
• 分区：考虑按特定列对数据进行分区以加快查询速度。
• 模式演变：规划外部数据模式的变化，例如添加或删除列及其对外部表的影响。

读取时构建模式

外部表采用“读取时构建模式”的方法，这意味着模式在查询数据时应用，而不是在存储数据时应用。这与传统的“写入时构建模式”数据库不同，后者在存储数据时会根据预定义的模式进行存储。这种灵活性在处理半结构化数据（如 JSON 或 Parquet）时非常有用，因为它们的结构可能会演变而不会破坏查询。

外部表列

所有外部表都包含以下列

虚拟列

除了上面列出的标准列之外，一些平台还允许使用虚拟列，这些列是在查询执行期间根据其他列或外部因素（例如时间戳、文件名或行索引）计算得出的。虚拟列在处理半结构化或不断演变的数据时非常有用，它们允许您提取或转换数据，而无需实际修改文件。

通用文件大小建议

为了优化外部表的并行扫描，应根据格式和预期的查询工作负载来确定文件大小。这有助于平衡读取效率和计算资源利用率之间的性能。

分区外部表

外部表的分区是一种技术，用于根据特定列值（如日期或区域）将大型数据集划分为更小、更易于管理的块。分区通过允许查询引擎仅扫描相关分区而不是整个数据集来提高查询性能。根据平台的不同，分区可以在创建或更新表时自动或手动添加。

自动添加分区

一些数据平台允许根据底层数据文件中的预定义表达式或模式自动进行分区。当数据集以可预测的方式组织时（例如基于日期的文件夹），这尤其有用。

基于表达式自动添加分区的 CREATE EXTERNAL TABLE 语法可能如下所示

在此示例中

• PARTITIONED BY 指定用于确定数据将如何自动划分的列或表达式。
• WITH PARTITION COLUMNS 定义外部表将进行分区的特定列。系统将根据这些列推断分区。
• LOCATION 指示云存储或外部数据源的路径。
• FORMAT 指定文件格式（例如 Parquet、CSV）。

示例

如果外部数据存储在云存储路径中，例如 s3://bucket-name/folder/year=2023/month=01/day=01/，则平台可以使用文件夹结构自动按年、月和日对外部表进行分区。

分区外部表

外部表的分区是一种技术，用于根据特定列值（如日期或区域）将大型数据集划分为更小、更易于管理的块。分区通过允许查询引擎仅扫描相关分区而不是整个数据集来提高查询性能。根据平台的不同，分区可以在创建或更新表时自动或手动添加。

自动添加分区

一些数据平台允许根据底层数据文件中的预定义表达式或模式自动进行分区。当数据集以可预测的方式组织时（例如基于日期的文件夹），这尤其有用。

基于表达式自动添加分区的 CREATE EXTERNAL TABLE 语法可能如下所示

在此示例中

• PARTITIONED BY 指定用于确定数据将如何自动划分的列或表达式。
• WITH PARTITION COLUMNS 定义外部表将进行分区的特定列。系统将根据这些列推断分区。
• LOCATION 指示云存储或外部数据源的路径。
• FORMAT 指定文件格式（例如 Parquet、CSV）。

示例

如果外部数据存储在云存储路径中，例如 s3://bucket-name/folder/year=2023/month=01/day=01/，则平台可以使用文件夹结构自动按年、月和日对外部表进行分区。

手动添加分区

为了更好地控制分区的定义方式，可以选择手动分区。在这种情况下，您必须显式指定分区并自行管理。手动分区允许您根据自动表达式不易捕获的自定义业务逻辑或模式来分区数据。

手动添加分区的 CREATE EXTERNAL TABLE 语法可能如下所示

创建表后，您可以使用以下语法手动添加分区

示例

要按年和月手动分区销售数据表，您可以为每个值组合指定单个分区

在此示例中

• CREATE EXTERNAL TABLE 语句定义了表及其分区列。
• ALTER EXTERNAL TABLE 语句通过提供值和关联的文件位置来添加特定分区。

通过手动添加分区，您可以控制数据的结构方式，如果您的数据在 云存储 中没有以可预测或一致的方式组织，这一点尤其有用。

Delta Lake 支持

Delta Lake 是一个开源存储层，它为数据湖带来了 ACID（原子性、一致性、隔离性、持久性）事务、可伸缩的元数据处理和数据版本控制，从而使数据湖更加可靠和高效。Delta Lake 在 Apache Spark 等大数据分析平台中广泛用于处理大量的流式和批处理数据。引用 Delta Lake 文件的外部表允许您直接查询数据，而无需将其移动到结构化数据库。

注意：引用 Delta Lake 文件的外部表不支持删除向量。删除向量允许系统跟踪已删除的记录，而无需立即从数据集中删除它们。Delta Lake 不支持删除向量意味着必须使用其他策略来处理已删除的数据，例如从存储层物理删除记录或实现软删除机制（例如，标记已删除的记录）。

将 Delta 外部表迁移到 Iceberg

如果需要从 Delta Lake 迁移到 Apache Iceberg，Apache Iceberg 为大型数据湖提供了更高级的功能，例如时间旅行、快照和优化的数据文件布局。如果 Iceberg 的功能更符合您数据环境的性能和操作需求，则此迁移可能会有所裨益。

要将一个或多个引用 Delta Lake 的外部表迁移到 Iceberg 表，请完成以下步骤

确保兼容性

确认您的数据平台同时支持 Delta Lake 和 Apache Iceberg 格式。如 Snowflake 和 AWS Athena 等平台支持查询这些格式的数据。

转换数据格式

• 使用支持 Delta Lake 和 Iceberg 的工具或查询引擎（例如 Apache Spark）将 Delta Lake 文件转换为 Iceberg 格式。这可能涉及读取 Delta Lake 数据并将其作为 Iceberg 表写出。
• Spark 中命令的示例可能如下所示

创建 Iceberg 外部表

• 迁移底层数据后，创建一个指向新的 Iceberg 数据的外部表

更新查询

• 更新任何现有查询或工作流程，使其引用新的 Iceberg 表而不是 Delta 表。

手动刷新

不支持自动刷新，因为云存储中 DDL 操作触发的事件通知的顺序不能保证。要注册迁移后添加或删除的任何文件，您必须定期执行 ALTER EXTERNAL TABLE ... REFRESH 语句，以确保新数据文件被识别。

添加或删除列

模式演变是外部表的重要考虑因素，尤其是在底层数据不断变化时。与直接将模式更改应用于表结构的传统数据库不同，外部表中的模式更改（例如添加或删除列）通常在查询时处理，因为模式仅在读取数据时强制执行。

添加列：在向外部表添加列时

• 如果新列可以具有默认值，则可能不需要修改底层数据。
• 可以更改外部表定义以包含新列。

示例

查询表时，新列将出现，并且不包含该列数据的行可以用默认值填充。

删除列：从外部表删除列很简单，但必须小心确保不丢失关键数据。确保更新或删除任何依赖于该列的查询。

示例

保护外部表

由于外部表依赖于外部云存储，因此应用访问控制和数据保护策略至关重要。有几种方法可以保护外部表，包括掩码策略和行访问策略。这些有助于保护敏感数据，并确保只有授权用户才能访问或操作外部表中的数据。

掩码策略和外部表

掩码策略允许隐藏敏感数据，使其对未经授权的用户不可读，同时仍允许具有适当权限的用户合法访问。可以将掩码策略应用于外部表，以确保信用卡号等敏感信息（PII）或财务数据受到保护。

例如，应用掩码策略以隐藏信用卡号可能如下所示

在这种情况下，只有具有 authorized_role 角色的用户才能看到完整的信用卡号，而所有其他用户将看到数据的掩码版本。

行访问策略和外部表

行访问策略提供了一种基于用户角色、区域或部门等条件来强制执行行级安全性的方法。这确保用户只能看到他们被允许访问的行，这取决于策略规则。

用于限制对特定区域数据的访问的行访问策略示例

在这种情况下，用户只能查询 region 列与其当前区域匹配的行，从而确保他们只能看到与其位置相关的数据。

这些策略结合使用，提供了一种强大的机制来保护存储在外部表中的数据，同时保持灵活性和性能。它们还通过控制用户可以看到的列以及他们可以访问的行来确保遵守安全和隐私法规。

外部表上的物化视图

可以在外部表上创建物化视图，通过预计算和存储复杂查询的结果来提高查询性能。外部表上的物化视图将查询结果存储在数据库的内部存储中，从而在重复查询相同数据时可以更快地访问。这对于存储在 Amazon S3、Google Cloud Storage 或 Microsoft Azure Blob Storage 等外部系统中的常用数据集特别有用。

外部表上的物化视图的主要优点包括

• 性能提升：物化视图存储查询结果，而不是重复扫描大文件，从而大大缩短查询时间。
• 高效聚合：预计算汇总（如总和、平均值或计数）并将它们存储在物化视图中，可以提高报告和分析的效率。
• 成本效益：由于物化视图只计算一次然后重复使用，因此减少了扫描外部存储中的大量数据的需要，从而降低了查询外部表的账单成本。

创建外部表上的物化视图的示例语法

自动刷新外部表元数据

为了使外部表的元数据与云存储中的底层数据文件保持同步，您可以设置自动刷新。这可确保添加到外部数据源的任何新文件或从外部数据源删除的任何文件都会反映在外部表的元数据中，从而确保查询可以访问最新数据。

但是，某些平台需要使用 ALTER EXTERNAL TABLE ... REFRESH 命令手动刷新元数据

此命令重新加载外部表的元数据以捕获任何新添加或删除的文件。

在某些平台中，可以通过计划任务或事件驱动的进程设置自动刷新选项，以确保元数据始终是最新的，而无需手动干预。

外部表的计费

外部表的计费通常基于查询执行期间扫描的数据量。由于外部表访问存储在外部位置（如 Amazon S3、Google Cloud Storage 或 Microsoft Azure Blob Storage）的数据，因此成本由以下因素驱动

• 数据扫描：从外部源读取的数据量直接影响计费。查询较大的文件或读取比必需数据量更多数据的未优化查询将导致更高的成本。
• 文件格式：Parquet 或 ORC 等更高效的文件格式允许查询引擎仅读取特定列，与 CSV 等效率较低的格式相比，可以降低数据扫描成本。
• 存储成本：虽然外部表在数据库平台内不产生存储成本，但在 Amazon S3、Google Cloud Storage 或 Azure Blob Storage 等服务中，存储成本将应用于外部数据。

云存储（Amazon S3、Google Cloud Storage、Microsoft Azure）中外部表的流程

Amazon S3、Google Cloud Storage 或 Microsoft Azure Blob Storage 等云存储系统通常支持外部表。查询这些外部系统中的数据通常遵循以下步骤

创建外部表：在数据平台中定义外部表，并指定数据的云存储位置。

• 数据分区：对外部数据进行分区，通过允许查询引擎仅扫描相关数据来提高查询性能。
• 查询外部表：像查询内部表一样在外部表上执行查询。

• 刷新元数据：定期刷新外部表的元数据，以使其与云存储中的底层文件保持同步。
• 自动化刷新和查询过程：使用调度工具或基于云的事件通知来自动化元数据刷新和查询执行。

查询外部表

查询外部表涉及从外部存储中的文件中检索数据。虽然查询这些表与查询内部表相似，但可以应用特定的优化，例如过滤和使用虚拟列来最小化数据扫描。

过滤 Parquet 文件中的记录

Parquet 是一种面向分析查询优化的列式存储格式。在查询存储 Parquet 格式数据的外部表时，过滤特定列可以减少读取的数据量，从而显著提高性能。

示例：使用 WHERE 子句过滤记录，以限制从外部 Parquet 文件扫描的列和行。

此查询仅从 Parquet 文件中读取列 1 和列 2，并对列 3 应用过滤器。结果是，查询引擎避免读取不必要的数据，从而降低扫描成本并提高性能。

查询行为（优化和未优化）

下表说明了基于查询优化的查询行为差异，其中 et 为外部表，c1、c2 和 c3 为虚拟列

持久化查询结果

对于频繁执行的查询，您可以持久化查询结果，将其存储起来以便将来快速访问。这可以避免重新扫描外部数据文件，并降低成本和查询时间。

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

随着数据老化或变得无关紧要，预计会从外部表的元数据中移除旧文件，以防止它们被包含在将来的查询中。您可以使用存储过程根据文件的最后修改日期从元数据中移除旧文件来实现这一点。

用于移除文件的存储过程示例

此存储过程从外部表的元数据中移除任何早于指定日期的文件。

通过定期移除旧的暂存文件，您可以确保查询仅处理相关、最新的数据，从而提高查询性能并降低成本。