Snowflake数据工程——数据暂存的最佳实践

数据智能老司机
246 阅读20分钟

本章内容

  • 创建外部阶段，从云存储中获取数据文件
  • 使用目录表查看阶段元数据
  • 准备数据文件以提高加载效率
  • 使用外部表查询外部阶段中的数据
  • 使用物化视图提高查询性能

在本章中，我们将继续扩展在第2章中创建的第一个数据管道。我们将在数据摄取步骤中加入更多功能，通过访问云存储中的文件，而不是本地文件系统，来进行数据摄取。我们将通过认证云存储提供商并创建外部阶段。

由于数据管道的需求各不相同，因此没有“一刀切”的构建方法。在本章中，我们将展示并比较从云存储摄取数据的不同选项，包括在外部阶段上使用 COPY 命令、创建外部表，以及创建物化视图以提高查询性能。我们还将回顾如何准备云存储中的数据文件以提高数据摄取效率。

我们的第一步将是创建一个外部阶段，使云存储中的文件可以被 Snowflake 访问。在本章中，我们将继续使用 CSV 文件进行数据摄取。除了 CSV 格式，Snowflake 还支持摄取多种结构化和半结构化文件格式，例如 JSON、Parquet 或 XML，后者将在下一章讨论。

创建外部阶段的语法和参数可能会因云存储提供商（如 Amazon S3、Google Cloud Storage 或 Microsoft Azure Blob Storage）不同而有所不同。本章将使用 Microsoft Azure 作为平台来演示示例，但您也可以使用其他云存储提供商进行操作。我们将提供指向 Snowflake 文档的链接，您可以在其中找到有关与每个受支持的云存储提供商交互的详细语法。

提示： 如果您已经拥有对某个支持的云存储提供商的访问权限，您可以使用该服务来跟随本章的示例进行操作，但请确保您有足够的权限上传文件。否则，您可以创建一个免费的试用帐户，以使用任何支持的云存储提供商。

在本章中，我们将继续使用第2章中介绍的虚构面包店作为示例来构建管道。简单回顾一下，面包店制作面包和糕点，并将这些烘焙食品交付给邻近的小型商户，如杂货店、咖啡馆和餐厅。由于面包店没有在线订购系统，客户通过电子邮件下单，面包店将订单存储在本地文件系统中的 CSV 文件中。

面包店的一位客户，附近的一家餐馆，最近将其信息基础设施迁移到了云端。现在，这家餐馆可以在其云存储平台的专用容器中提供每日订单信息，文件格式为 CSV。面包店将构建一个数据管道，从餐馆的云存储容器中的订单文件摄取数据，并将其存储到 Snowflake 数据库中。

注： 本章的所有代码和示例数据文件均可在 GitHub 仓库的 Chapter_03 文件夹中找到，链接：mng.bz/AaJQ。

为了构建一个从云存储摄取餐馆文件的管道，面包店的数据工程师必须获取有关文件位置和访问凭证的信息。然后，他们将创建外部阶段以便将数据文件暴露给 Snowflake。通常，创建外部阶段有两种方法：

  1. 使用 Snowflake 存储集成对象创建外部阶段
    存储集成对象用于认证 Snowflake 与云存储提供商之间的连接，并用来创建外部阶段。它封装了访问云存储的凭证，因此开发人员不需要手动提供凭证。同时，存储集成对象还让云存储管理员能够控制访问权限。
  2. 在创建外部阶段时提供凭证来认证 Snowflake 与云存储提供商的连接
    数据工程师可以使用这种方法快速将一些数据文件从云存储加载到 Snowflake 中——例如，在没有创建存储集成对象的情况下进行测试或概念验证时使用。

在通过这两种方式之一创建外部阶段后，文件的摄取过程与从内部阶段摄取文件类似，如第2章所解释的那样。COPY 命令用于将数据从外部阶段复制到暂存表。然后，数据可以从暂存表加载到目标表，类似于第2章中的管道。图3.1展示了将数据从云存储加载到 Snowflake 所需的步骤。

image.png

3.1 创建外部阶段

外部阶段是 Snowflake 中的一个对象，存在于一个模式下。该对象存储了云存储中文件的位置、用于访问云存储帐户的参数以及其他附加参数，例如描述阶段文件格式的选项。

Snowflake 可以从任何支持的云存储提供商（如 Amazon S3、Google Cloud Storage 或 Microsoft Azure Blob Storage）摄取数据，无论 Snowflake 帐户所在的云平台是什么。

在本章的示例中，我们假设提供订单信息的餐馆已经在其 Microsoft Azure 帐户中设置了一个 Blob 存储容器。

有关在 Amazon S3 和 Google Cloud Storage 中设置存储集成的详细信息，请参阅以下链接：Amazon S3 和 Google Cloud Storage。

提示： 如果您偏好使用 AWS 帐户，请参阅第4章，其中介绍了如何在 Amazon S3 中创建存储集成对象和外部阶段。

如果您使用 Microsoft Azure 帐户跟随本章内容，您需要通过以下步骤在您的帐户中创建资源：

  1. 创建资源组，如果您已经有资源组并希望将其用于本章的练习，可以选择使用现有资源组。您可以随意命名资源组。

  2. 在资源组中创建存储帐户。

  3. 创建容器。容器类似于文件系统中的目录，用于组织一组文件。您可以随意命名容器，但如果您希望与本章的练习保持一致，请命名为 orderfiles。

    重要： 在选择存储帐户名称时，请记住，存储帐户名称必须在 3 到 24 个字符之间，并且只能包含数字和小写字母。此外，您的存储帐户名称必须在 Azure 中唯一。本章练习中使用的存储帐户名称是 bakeryorders001。如果您希望使用类似名称，但该名称不可用，可以将后缀从 001 改为其他数字组合，并相应地修改代码。

一旦您创建了资源，您可以导航到 orderfiles 容器，并从 GitHub 仓库的 Chapter_03 文件夹中上传一个示例文件 Orders_2023-08-04.csv 到该容器中。我们将使用存储集成创建外部阶段，从存储容器中的 CSV 文件中摄取数据。

3.1.1 配置存储集成

存储集成是一个对象，用于验证 Snowflake 对云存储提供商的访问权限。它使得 Snowflake 可以从外部阶段读取数据，并可选择性地将数据写入外部阶段。根据云存储提供商的不同，存储集成对象将使用不同的参数来保存访问信息。

在本章中，我们将使用 Microsoft Azure 作为示例，Snowflake 必须作为应用程序在 Azure 帐户中注册。只有具有管理员权限的用户才能授予所需的权限给 Snowflake 应用程序。此外，存储集成还必须指定一个存储容器，这将限制您创建外部阶段的位置。

我们将使用 CREATE STORAGE INTEGRATION 命令，创建一个与餐馆云存储位置相关的存储集成。具有 ACCOUNTADMIN 角色或具有 CREATE INTEGRATION 权限的其他角色的 Snowflake 用户可以创建存储集成。

在执行命令创建存储集成之前，我们需要收集由拥有存储帐户的餐馆提供的以下信息：

  • Azure Tenant ID
  • 存储帐户名称
  • 存储容器名称

在我们的示例中，Azure Tenant ID 为 1234abcd-xxx-56efgh78（这是一个虚构的 Tenant ID 用于说明），存储帐户为 bakeryorders001，容器为 orderfiles。

提示： Azure Tenant ID 是分配给每个使用 Microsoft 服务的组织的唯一标识符。您可以通过点击设置，然后选择 “Directories + Subscriptions” 来找到您的 Tenant ID。您将看到一个目录列表，找到“Default Directory”，然后查找 Directory ID 的值。该值即为您的 Tenant ID。

使用这些信息（Tenant ID、存储帐户和容器），我们可以使用以下命令创建 BISTRO_INTEGRATION 存储集成：

use role ACCOUNTADMIN;
create storage integration BISTRO_INTEGRATION
  type = external_stage
  storage_provider = 'AZURE'
  enabled = true
  azure_tenant_id = '1234abcd-xxx-56efgh78'
  storage_allowed_locations = 
    ('azure://bakeryorders001.blob.core.windows.net/orderfiles/');

执行此命令后，将在 Snowflake 帐户中创建一个存储集成对象。我们可以通过执行以下命令来查看存储集成的详细信息：

describe storage integration BISTRO_INTEGRATION;

表 3.1 显示了该命令的示例输出（您的输出将包含您的 Azure Tenant ID、同意 URL 和应用程序名称）。

表 3.1 DESCRIBE STORAGE INTEGRATION 命令的示例输出：

属性属性类型属性值
ENABLED布尔值true
STORAGE_PROVIDER字符串AZURE
STORAGE_ALLOWED_LOCATIONS列表azure://bakeryorders001.blob.core.windows.net/orderfiles/
STORAGE_BLOCKED_LOCATIONS列表
AZURE_TENANT_ID字符串1234abcd-xxx-56efgh78
AZURE_CONSENT_URL字符串login.microsoftonline.com/1234abcd-xx…
AZURE_MULTI_TENANT_APP_NAME字符串12abcsnowflakepacint_1234567890

此命令返回存储集成的属性。我们需要注意以下几个属性的值：

  • AZURE_CONSENT_URL：该参数的值可能类似于 https://login.microsoftonline.com/1234abcd-xxx-56efgh78z/oauth2/authorize?client_id=1234abcd-xxx-56efgh78z&response_type=code（这是一个虚构的 URL 用于说明）。
  • AZURE_MULTI_TENANT_APP_NAME：该参数的值可能类似于 12abcsnowflakepacint_1234567890（这是一个虚构的应用程序名称用于说明）。

首先，我们复制 AZURE_CONSENT_URL 属性的值，并将其粘贴到新的浏览器窗口中。Azure 管理员必须点击显示的表单中的 Accept 按钮。点击此按钮将授予 Snowflake 应用程序（也称为 Azure 中的服务主体）在存储容器上的访问令牌。然而，光有此操作还不足以允许 Snowflake 访问存储容器中的文件，还需要将权限授予存储容器。

为此，Azure 管理员必须登录 Azure 门户，并为 Snowflake 服务主体授予角色分配。详细的操作说明可以参考 Snowflake 文档：mng.bz/2gNw。

Azure 管理员可以授予以下权限：

  • 通过 Storage Blob Data Reader 角色授予读取权限，或者
  • 通过 Storage Blob Data Contributor 角色授予读取和写入权限。

提示： 如果您正在跟随本章的示例，注意可能需要一小时或更长时间，Snowflake 服务主体才会出现在 Azure 中。如果它在您接受同意 URL 后没有立即显示，请耐心等待。

在我们的示例中，Azure 管理员将执行 Azure 管理步骤，而不是数据工程师。如果您使用自己的 Azure 帐户跟随示例，您也将是 Azure 管理员。一旦存储集成创建并且访问权限被授予后，我们就可以使用它来创建外部阶段。

3.1.2 使用存储集成创建外部阶段

为了创建一个使用存储集成的外部阶段，必须授予创建外部阶段的角色使用存储集成对象的权限。我们将继续使用第二章中创建的 BAKERY_DB 数据库，并使用 SYSADMIN 角色。因为 SYSADMIN 角色需要使用存储集成，所以创建 BISTRO_INTEGRATION 存储集成的 ACCOUNTADMIN 角色必须授予 SYSADMIN 角色对此对象的使用权限。可以通过执行以下命令来完成：

grant usage on integration BISTRO_INTEGRATION to role SYSADMIN;

接下来，我们将切换到 SYSADMIN 角色。为了将外部数据源的相关对象与第二章中创建的对象分开，我们将在 BAKERY_DB 数据库中创建一个名为 EXTERNAL_ORDERS 的新模式（schema）。我们可以使用以下命令进行创建：

use role SYSADMIN;
create warehouse if not exists BAKERY_WH with warehouse_size = 'XSMALL';
create database if not exists BAKERY_DB;
use database BAKERY_DB;
create schema EXTERNAL_ORDERS;
use schema EXTERNAL_ORDERS;

接下来，我们将使用 BISTRO_INTEGRATION 存储集成创建一个名为 BISTRO_STAGE 的外部阶段。我们将提供存储容器中文件的存储位置，这是在创建存储集成时从餐馆那里获得的（存储帐户名为 bakeryorders001，容器为 orderfiles）。我们可以执行以下命令来创建外部阶段：

命令 3.1 使用存储集成创建外部阶段：

create stage BISTRO_STAGE
  storage_integration = BISTRO_INTEGRATION
  url = 'azure://bakeryorders001.blob.core.windows.net/orderfiles';

要查看外部阶段的内容，我们可以执行以下命令：

list @BISTRO_STAGE;

此命令的输出将显示餐馆已经上传到 Blob 存储容器中的任何文件。

3.1.3 使用凭证创建外部阶段

Snowflake 推荐使用存储集成对象创建外部阶段。这些对象封装了访问云存储的凭证，因此数据工程师在创建阶段或加载数据时不需要提供这些凭证。维持 Snowflake 与云提供商之间的存储集成的另一个好处是，云提供商可以更好地控制 Snowflake 的访问权限。如果出现安全问题或不再需要访问权限，云提供商可以阻止 Snowflake 应用程序访问云存储平台。

然而，创建存储集成对象需要云存储管理员的配合，而管理员可能并不总是可用。有时，数据工程师希望快速将数据文件从云存储加载到 Snowflake 中，例如在测试或进行概念验证时，而不想承担创建存储集成对象的开销。Amazon S3 和 Microsoft Azure（但 Google Cloud Platform 不支持）允许通过提供凭证而不使用存储集成对象来创建外部阶段。

在使用 Azure 存储容器时，数据工程师可以生成一个共享访问签名（SAS）令牌来访问存储帐户中的对象。例如，提供订单信息的餐厅可以发送一个 SAS 令牌来访问存储容器中的 CSV 文件。

提示：如果你在使用 Microsoft Azure 账户进行操作，可以根据 Snowflake 文档中的说明生成 SAS 令牌，网址为 mng.bz/1aER。

一旦我们获得了 SAS 令牌的值（例如，?sv=2023-...%3D，这是一个用于示例的虚构 SAS 令牌），我们可以使用以下命令创建一个名为 BISTRO_SAS_STAGE 的外部阶段，来访问与之前相同存储帐户和容器中的文件（存储帐户名为 bakeryorders001，容器名为 orderfiles）：

create stage BISTRO_SAS_STAGE
  URL = 'azure://bakeryorders001.blob.core.windows.net/orderfiles'
  CREDENTIALS = (AZURE_SAS_TOKEN = '?sv=2023-...%3D');

我们可以通过执行以下命令查看外部阶段的内容：

list @BISTRO_SAS_STAGE;

上传到 Blob 存储容器中的文件将会显示。

使用 SAS 令牌时，请记住它在创建时定义了过期日期，因此它是有有效期的，不能无限期使用。与存储集成相比，在存储集成中，管理员控制外部存储的访问权限；而使用 SAS 令牌时，管理员无法监督谁生成了 SAS 令牌，因此也不知道谁有访问权限。

从现在起，我们将使用通过存储集成创建的 BISTRO_STAGE 外部阶段，因此我们只需要使用一个外部阶段。这个选择是任意的；我们也可以使用 BISTRO_SAS_STAGE 外部阶段，因为它们的行为是相同的。

3.1.4 从暂存文件加载数据到暂存表

我们使用COPY命令将外部暂存中的文件数据加载到暂存表中，类似于将数据从内部命名暂存加载到表中的方法，如第二章所述。
让我们来看一下餐厅提供的CSV文件，该文件存储在Blob存储容器中。例如，餐厅在8月4日的所有订单都保存在名为Orders_2023-08-04.csv的CSV文件中。该文件的内容如下表3.2所示。

表3.2 餐厅在某一天下的所有订单

CustomerOrder dateDelivery dateBaked good typeQuantity
New Bistro2023-08-042023-08-07Baguette15
New Bistro2023-08-042023-08-07Whole wheat loaf12
New Bistro2023-08-042023-08-07White loaf10
New Bistro2023-08-042023-08-07Croissant30
New Bistro2023-08-042023-08-08Baguette12
New Bistro2023-08-042023-08-08Whole wheat loaf12
New Bistro2023-08-042023-08-08White loaf10
New Bistro2023-08-042023-08-08Croissant20

我们将把餐厅的CSV文件数据加载到名为ORDERS_BISTRO_STG的暂存表中。该表的结构与第二章中使用的ORDERS_STG暂存表相同。此表中的列包括：

  • CSV文件中的所有列，包括客户、订单日期、交付日期、烘焙产品类型和数量。
  • CSV文件的名称，通过元数据列metadata$filename提取——就像内部暂存一样，外部暂存中的文件也包含可以查询的元数据列。
  • 数据摄取的时间戳。

我们将使用以下命令在BAKERY_DB数据库中的EXTERNAL_ORDERS架构下创建此表：

use database BAKERY_DB;
use schema EXTERNAL_ORDERS;
create table ORDERS_BISTRO_STG (
  customer varchar,
  order_date date,
  delivery_date date,
  baked_good_type varchar,
  quantity number,
  source_file_name varchar,
  load_ts timestamp
);

为了将BISTRO_STAGE外部暂存中的CSV文件数据加载到ORDERS_BISTRO_STG暂存表中，我们可以执行以下命令：

copy into ORDERS_BISTRO_STG
from (
  select $1, $2, $3, $4, $5, metadata$filename, current_timestamp() 
  from @BISTRO_STAGE
)
file_format = (type = CSV, skip_header = 1)
on_error = abort_statement;

在使用COPY命令将数据从外部暂存加载到暂存表后，我们可以通过执行SQL SELECT命令查看表中的数据：

select * from ORDERS_BISTRO_STG;

如表3.2所示，我们应该能看到与CSV文件中的数据匹配的八行数据。

在第二章中，当我们从内部暂存加载CSV文件数据到目标表时，我们在COPY命令中使用了PURGE = TRUE选项。此选项在加载后会将文件从内部暂存中删除，以防止下次执行时意外加载相同的文件。PURGE命令还确保不再需要的旧文件不会随着时间的推移积累。

我们也可以在外部暂存中使用PURGE选项，如在上述命令中所示，但该选项仅在存储集成对象中的Snowflake服务主体被授予Storage Blob Data Contributor角色时有效，这样才允许删除文件。

如果数据工程师只拥有Storage Blob Data Reader角色，而没有删除文件的权限，则在使用PURGE选项时，虽然不会删除文件，但也不会报错。在这种情况下，数据工程师必须手动检查文件是否已从暂存中删除。

Snowflake会跟踪从暂存加载到表中的文件，并且不会再次加载相同的文件。然而，如果我们允许文件在云存储中积累，每次执行COPY时，Snowflake都会与已经加载的文件列表进行对比，这会导致数据加载的时间逐渐增加。因此，建议定期删除已处理的文件或将它们移到归档位置，这通常由云存储平台的开发人员或管理员来执行。

提示：Snowflake数据工程师如果具有足够的权限，可以使用REMOVE命令从外部暂存中删除文件。

3.1.5 避免从暂存文件加载数据时的数据重复

当我们执行COPY命令时，Snowflake会跟踪每个存储从暂存文件加载数据的表的元数据。该元数据包括文件名、文件大小、实体标签（ETag；文件内容的版本标识符）、文件中的行数、最后加载的时间戳以及加载过程中发生的任何错误。
这确保了后续或同时执行COPY命令时，不会再次从相同的文件加载数据，从而避免数据重复。此外，当加载单个文件的数据由于某种原因失败时，Snowflake会在下次执行COPY命令时再次尝试加载这些文件的数据。

为了看看这个过程如何运作，我们再执行一次与列出3.2中的COPY命令相同的命令。由于外部暂存中的CSV文件已经被加载到暂存表中，Snowflake不会再次加载相同的文件。当我们第二次执行COPY命令时，命令的输出将是：

Copy executed with 0 files processed

加载具有过期元数据的文件

每个从暂存文件加载数据到表中的文件元数据会保留64天。当Snowflake遇到超过64天未修改的文件，或者数据在64天前加载到表中的情况，它将无法确定该文件的数据是否已成功加载。在这种情况下，为了避免意外的重复加载，Snowflake不会再次加载该文件。
数据工程师可以在执行COPY命令时，指定额外的选项（如FORCE或LOAD_UNCERTAIN_FILES）来控制Snowflake如何加载具有过期元数据的文件。
当FORCE选项为TRUE时，Snowflake会加载所有文件，而不管加载元数据。这在数据工程师想要重新加载所有暂存中的文件数据时尤其有用。
当LOAD_UNCERTAIN_FILES选项为TRUE时，Snowflake会尝试加载具有过期加载元数据的文件。

为了验证在执行COPY命令后数据是否成功加载，我们可以查看INFORMATION_SCHEMA中的LOAD_HISTORY视图。此视图存储过去14天内加载到表中的数据历史。如果我们拥有足够的权限，还可以查看SNOWFLAKE数据库中的ACCOUNT_USAGE架构下的LOAD_HISTORY视图，该视图存储过去365天内加载到表中的数据历史。
一些有趣的列包括架构名称、表名、文件名、最后加载时间戳、加载的行数、解析的行数和加载状态。加载状态的可能值有：LOADED（已加载）、LOAD FAILED（加载失败）或PARTIALLY LOADED（部分加载）。其他列提供了更多关于错误消息的信息。

要查看我们通过COPY命令从BISTRO_STAGE外部暂存中的文件加载到ORDERS_BISTRO_STG表的加载历史，可以执行以下查询：

select *
from information_schema.load_history
where schema_name = 'EXTERNAL_ORDERS' and table_name = 'ORDERS_BISTRO_STG'
order by last_load_time desc;

此命令的输出应显示从外部暂存中加载到表中的文件列表。表3.3展示了一个包含部分列的示例输出。

表3.3 从LOAD_HISTORY视图的示例输出

FilenameTable nameLast load timeStatus
azure://bakeryorders001.blob.core.windows.net/orderfiles/Orders_2023-08-04.csvORDERS_BISTRO_STG2023-08-04T10:15:20.052LOADED
azure://bakeryorders001.blob.core.windows.net/orderfiles/202308/Orders_2023-08-07.csvORDERS_BISTRO_STG2023-08-04T10:15:20.052LOADED
azure://bakeryorders001.blob.core.windows.net/orderfiles/202308/Orders_2023-08-08.csvORDERS_BISTRO_STG2023-08-04T10:15:20.052LOADED

3.1.6 使用命名文件格式

在之前的示例中，当我们从暂存加载数据到表时，文件格式是作为COPY命令的一个参数来指定的。例如，列出3.2中的file_format = (type = CSV, skip_header = 1)参数。这个参数表示我们加载的文件是CSV格式，并且文件中包含一行头部，在加载数据时必须跳过这一行。

我们可以通过创建一个命名的文件格式来避免每次执行COPY命令时都指定相同的文件格式。这样做特别有用，尤其是在我们需要反复加载共享相同格式的文件时。

例如，我们可以创建一个命名文件格式来描述我们示例中加载的CSV文件格式。要创建一个名为ORDERS_CSV_FORMAT的文件格式，指定格式为CSV文件，逗号为分隔符，并且有一个头部行，可以执行以下命令：

create file format ORDERS_CSV_FORMAT
  type = csv
  field_delimiter = ','
  skip_header = 1;

然后，在执行COPY命令时，我们可以使用这个命名的文件格式作为参数，如列出3.3中的示例所示：

列出3.3 使用命名文件格式加载数据

copy into ORDERS_BISTRO_STG
from (
  select $1, $2, $3, $4, $5, metadata$filename, current_timestamp() 
  from @BISTRO_STAGE
)
file_format = ORDERS_CSV_FORMAT     #1
on_error = abort_statement;

#1 指定了命名文件格式

在Snowflake中，我们可以在多个地方指定暂存文件的文件格式。以下是我们可以定义文件格式的几种情况：

  • 创建暂存时
  • 从暂存加载数据到目标表时
  • 创建存储已加载数据的目标表时

我们只应该在这些地方之一指定文件格式，而不是全部。如果在多个地方指定了文件格式，Snowflake会按照以下优先级顺序使用文件格式：

  1. COPY命令：如果在COPY命令中指定了文件格式，则会使用该文件格式，覆盖其他地方指定的文件格式。
  2. 暂存定义：如果在暂存定义中指定了文件格式（但在COPY命令中没有指定），则会使用暂存定义中的文件格式。
  3. 表定义：如果在表定义中指定了文件格式（但在COPY命令或暂存定义中没有指定），则会使用表定义中的文件格式。

我们还可以在创建暂存时指定文件格式，这样就不需要在COPY命令中再指定文件格式。例如，我们可以通过在创建外部暂存时使用文件格式参数，修改列出3.1中的命令如下：

create or replace stage BISTRO_STAGE
  storage_integration = BISTRO_INTEGRATION
  url = 'azure://bakeryorders001.blob.core.windows.net/orderfiles'
  file_format = ORDERS_CSV_FORMAT;    #1

#1 指定了命名文件格式

在重新创建外部暂存时指定了ORDERS_CSV_FORMAT文件格式后，我们可以在COPY命令中省略file_format选项。

3.2 使用目录表查看暂存元数据

在创建内部暂存或外部暂存时，我们可以添加一个目录表，用于存储暂存中文件的元数据。目录表是与暂存关联的对象，但不是一个独立的Snowflake对象。它可以在创建暂存时通过CREATE STAGE命令添加，或者在之后通过ALTER STAGE命令进行添加。

当文件被添加到或从外部暂存位置移除时，目录表中的元数据可以通过配置云存储提供商的事件通知服务自动刷新。事件通知将在下一章中进行更详细的讨论。

如果云存储提供商没有配置事件通知，我们也可以手动刷新外部暂存的目录表元数据。目前，内部暂存仅支持手动刷新文件元数据。

我们可以通过以下命令向之前创建的BISTRO_STAGE外部暂存添加目录表：

alter stage BISTRO_STAGE
set directory = (enable = true);

添加目录表后，我们将使用以下命令手动刷新它：

alter stage BISTRO_STAGE refresh;

最后，在目录表刷新后，我们可以使用以下命令查询暂存中文件的列表：

select * 
from directory (@BISTRO_STAGE);

此命令的输出示例，包含选择的列，如下表3.4所示：

表3.4 使用目录表查询列出暂存中文件的示例输出

相对路径文件大小最后修改时间ETag
202308/Orders_2023-08-07.csv4462023-11-18T 01:07:09"0x8DBE815BE847912"
202308/Orders_2023-08-08.csv4462023-11-18T 01:07:09"0x8DBE815BE847912"
Orders_2023-08-04.csv4482023-11-18T 01:06:31"0x8DBE815A84D8EE0"

此命令的输出列出了暂存中的文件，包含文件的相对路径、文件大小、最后修改时间、文件URL、文件MD5哈希和ETag等信息。当数据工程师需要比LIST命令提供的更多详细信息时，这些输出非常有用。

3.3 准备数据文件以实现高效的加载

在本章中的示例中，我们处理的是相对较小的数据量，在这种情况下，性能和效率并不会起到决定性作用。但假设你不再是一个小区面包店的工作人员，而是一个大规模工业化面包厂的数据工程师，该工厂生产大量面包并将其销售给大型超市。在这种情况下，你可能会处理大量数据管道，这些管道从许多文件中加载大量数据，数据加载的速度和效率就变得至关重要。

在这种情境下，Snowflake 提供了一些有效的数据加载建议，帮助优化性能和成本，例如云存储中的文件大小和组织方式。

3.3.1 文件大小建议

Snowflake 用于从文件加载数据的虚拟仓库的大小和集群数量，在规划高效加载时起着重要作用。虚拟仓库包含节点，节点由诸如 CPU、内存和临时存储等资源组成，这些资源被 COPY 命令用于将文件中的数据加载到 Snowflake 表中。每个文件的处理由一个节点完成。

提示：Snowflake 建议文件大小应在 100 MB 到 250 MB 之间（压缩后），以优化加载效率。

超过推荐大小的文件应拆分为较小的文件，以符合建议的文件大小。另一方面，许多小文件应按照文件大小建议合并为较大的文件。

当加载一个大文件时，该文件将在单个节点上处理，其他节点将处于空闲状态。增加虚拟仓库的大小（即增加可用节点数量）并不会提升性能，因为无论有多少节点，单个文件仍然只能在一个节点上处理。关于如何使用虚拟仓库来提高性能的技术将在第九章中详细讨论。

当加载许多小文件时，这些文件将在多个节点上并行处理，但每个文件的处理开销较大。此外，可能会出现排队现象，文件在等待节点可用时会被暂时延迟。

Snowflake 建议使用独立的虚拟仓库进行数据加载和查询。这样，每个仓库可以根据工作负载需求进行优化配置。例如，专门用于加载的仓库可以根据通常可用的文件数量和所需的性能进行最佳配置。当使用同一个仓库进行查询时，用户可能会注意到性能下降或查询排队，原因是在加载文件时该仓库正在忙于处理数据加载任务。

3.3.2 按路径组织数据

内部或外部阶段中的文件可以按路径进行组织。这意味着文件以逻辑分组的方式存储，例如按日期、地理来源或其他标准进行分组。这样的文件组织方式使数据工程师可以从单独的路径加载数据，而不是访问存储位置中的所有文件。

例如，餐厅向面包店提供订单信息，并按月和年组织其 CSV 文件。餐厅不是将所有文件存储在同一个 orderfiles 容器中，而是每个月存储在不同的路径下，例如 orderfiles/202308、orderfiles/202309 等。

如果你跟着操作，可以从 GitHub 仓库中上传更多订单文件到存储容器。在 Chapter_03 文件夹中，有一个名为 202308 的文件夹，并且在这个文件夹中有两个额外的订单文件，分别为 Orders_2023-08-07.csv 和 Orders_2023-08-08.csv。当你将这些文件上传到存储容器时，请确保展开“高级选项”并在“上传到文件夹”文本框中输入 202308，这样文件就会被存储在容器中的同一文件夹下。

我们可以通过执行 LIST 命令查看当前在外部阶段中可用的文件（或者，我们也可以刷新阶段并从目录表中选择）：

list @BISTRO_STAGE;

此命令的输出将显示外部阶段中的所有文件，且路径作为前缀。例如，在我们的示例中，阶段中有三个文件，一个位于容器的顶层，另外两个则位于 202308 文件夹中：

azure://bakeryorders001.blob.core.windows.net/orderfiles/202308/Orders_2023-08-07.csv
azure://bakeryorders001.blob.core.windows.net/orderfiles/202308/Orders_2023-08-08.csv
azure://bakeryorders001.blob.core.windows.net/orderfiles/Orders_2023-08-04.csv

当文件按路径组织时，我们可以通过筛选所选路径来从外部阶段加载数据。例如，如果我们只想加载来自 202308 文件夹的文件，则可以将列表 3.3 中的 COPY 命令修改如下：

copy into ORDERS_BISTRO_STG
from (
  select $1, $2, $3, $4, $5, metadata$filename, current_timestamp() 
  from @BISTRO_STAGE/202308     #1
)
file_format = ORDERS_CSV_FORMAT
on_error = abort_statement;

#1 表示添加了路径

COPY 命令还可以包含其他选项来限制加载文件的集合，例如提供要加载的文件名列表，或根据与文件名匹配的模式选择文件。

3.4 使用外部表构建数据管道

使用 COPY 命令的另一种方式是通过外部表从云存储加载数据。外部表是 Snowflake 对象，允许你像查询 Snowflake 中的其他表一样，从外部阶段查询数据。由于数据存储在外部阶段，物理上位于 Snowflake 之外，我们只能读取外部表中的数据，而不能修改它。此外，由于数据物理上存储在外部阶段，查询性能比查询 Snowflake 内部表的数据要慢。

使用外部表的一些优点包括：

  • 存储在外部阶段中的文件数据可以使用标准的 SQL 命令查询，就像查询 Snowflake 中的任何其他表一样。
  • 由于数据存储在 Snowflake 之外，它不会消耗 Snowflake 存储成本，并且可以供其他系统使用。

另一方面，使用外部表也有一些缺点，例如：

  • 查询性能比从标准的 Snowflake 表查询数据慢。
  • 外部表是只读的，这意味着我们无法执行数据操作语言（DML）操作，不能将其作为常规表使用。

为了提高性能，可以使用物化视图来持久化数据。当使用外部表时，可以将外部阶段中的文件数据呈现为外部表。然后，通过创建物化视图来提升性能，而不需要使用 COPY 命令，如图 3.2 所示。

image.png

3.4.1 使用外部表查询外部阶段中的数据

我们可以从BISTRO_STAGE外部阶段创建一个外部表，这样就可以使用标准的SQL查询来查询外部阶段中的数据。在创建外部表之前，我们必须知道文件格式。由于我们已经知道餐厅阶段中文件的格式，因此可以使用之前创建的ORDERS_CSV_FORMAT文件格式。

每个外部表都包含一些内置的元数据列，我们可以在创建表时引用这些列。这些列包括：

  • VALUE：该列包含外部阶段中文件中的每一行数据，数据类型为VARIANT。我们可以使用该列来解析数据类型中的单个列，通过引用c1表示第一列，c2表示第二列，依此类推。
  • METADATA$FILENAME：外部阶段中文件的名称和路径。
  • METADATA$FILE_ROW_NUMBER：每个文件中记录的行号。

我们可以使用以下命令创建ORDERS_BISTRO_EXT外部表，使用这些元数据列、阶段名称和文件格式：

列出3.4 从外部阶段创建外部表

create external table ORDERS_BISTRO_EXT (
  customer varchar as (VALUE:c1::varchar),
  order_date date as (VALUE:c2::date),
  delivery_date date as (VALUE:c3::date),
  baked_good_type varchar as (VALUE:c4::varchar),
  quantity number as (VALUE:c5::number),
  source_file_name varchar as metadata$filename
)
location = @BISTRO_STAGE
auto_refresh = FALSE
file_format = ORDERS_CSV_FORMAT;

我们可以通过执行SQL SELECT 命令查询外部表中的数据，例如：

select *
from ORDERS_BISTRO_EXT;

与阶段上的目录表类似，外部表可以配置为在向外部阶段添加或移除新文件时自动刷新，通过配置云存储提供商的事件通知服务。如果通知服务不可用，外部表也可以手动刷新，例如使用以下命令：

alter external table ORDERS_BISTRO_EXT refresh;

由于查询外部表通常比查询常规的Snowflake表要慢，我们可以创建物化视图，将外部表中的数据持久化到Snowflake中，从而提高查询性能。

3.4.2 使用物化视图提高查询性能

物化视图是由查询创建的数据集，并存储以供后续查询使用。由于查询结果已被存储，查询物化视图的速度比执行构建物化视图的查询要快。

当查询涉及外部表时，物化视图特别有用，因为外部表通常比常规的 Snowflake 表查询性能较慢。定义了物化视图后，Snowflake 会自动处理其维护工作。这意味着，每当物化视图中使用的外部表中的数据更新时，物化视图也会更新。物化视图包含与外部表相同的数据，但由于数据存储在 Snowflake 中，当用户查询物化视图时，查询性能会更好。

如前所述，当向外部阶段添加新文件时，如果配置了事件通知服务，外部表中的数据会自动刷新。否则，我们必须手动刷新外部表。一旦外部表刷新，无论是自动还是手动，物化视图会随之刷新，而无需用户采取额外操作。

我们可以使用以下命令基于ORDERS_BISTRO_EXT外部表创建名为ORDERS_BISTRO_MV的物化视图：

create materialized view ORDERS_BISTRO_MV as
select customer, order_date, delivery_date, 
  baked_good_type, quantity, source_file_name
from ORDERS_BISTRO_EXT;

我们可以通过执行 SQL SELECT 命令查询物化视图中的数据，例如：

select * 
from ORDERS_BISTRO_MV;

该 SELECT 语句返回与外部表中相同的数据，但查询执行速度更快。

尽管创建外部表和物化视图是一种替代使用 COPY 命令创建数据管道的方法，但也存在一些缺点，例如：

  • 物化视图仅在 Snowflake 企业版或更高版本中可用。如果你使用的是标准版，则无法使用物化视图。
  • 物化视图的维护会增加成本，包括存储所需的数据和维护物化视图所需的计算资源。

数据工程师应与系统架构师合作，基于不同的因素和用户需求，找到从云存储加载数据的最佳方法，例如优先考虑更快的响应时间或优化成本。

总结

  • 外部阶段是一个存在于架构中的 Snowflake 对象。这个对象存储了云存储中文件的位置、用于访问云存储帐户的参数以及其他参数，例如描述暂存文件格式的选项。

  • 外部阶段可以通过 Snowflake 存储集成来创建，或者通过提供认证凭据来验证 Snowflake 与云存储提供商的连接。

  • 存储集成对象是一个对象，它使 Snowflake 只需进行一次认证，并利用该对象创建外部阶段。

  • 与从内部命名阶段加载数据类似（如第二章所述），我们可以使用 COPY 命令将外部阶段中的文件数据加载到暂存表中。

  • 当我们执行 COPY 命令时，Snowflake 会跟踪每个从暂存文件加载数据的表的元数据。这确保了后续或同时执行的 COPY 命令不会再次加载来自相同文件的数据，从而避免数据重复。

  • 我们可以创建一个命名的文件格式，而不必每次执行 COPY 命令时都指定相同的格式。特别是在我们反复加载共享相同格式的文件时，这非常有用。

  • 在创建内部或外部阶段时，我们可以添加一个目录表，用于存储阶段中文件的元数据。目录表是与阶段关联的对象，但不是一个独立的 Snowflake 对象。

  • 用于从文件加载数据的 Snowflake 虚拟仓库的大小和集群数量在规划高效加载时非常重要。Snowflake 推荐文件大小应在 100 MB 到 250 MB（压缩后）之间。超过推荐大小的文件应拆分成更小的文件，而多个小文件应合并成更大的文件。

  • Snowflake 虚拟仓库包含由资源（如 CPU、内存和临时存储）组成的节点，这些资源被 COPY 命令用于将数据从文件加载到 Snowflake 表中。每个文件可以由单个节点处理。

  • Snowflake 推荐使用单独的虚拟仓库进行数据加载，另一个用于查询。这样，每个仓库可以根据工作负载需求进行优化配置。

  • 内部或外部阶段中的文件可以按日期、地理来源或其他标准进行逻辑分组。这使数据工程师能够从单独的路径加载数据，而不是访问存储位置中的所有文件。

  • 使用 COPY 命令的另一种方式是通过外部表从云存储加载数据。外部表是一个 Snowflake 对象，允许你像查询 Snowflake 中的其他表一样，从外部阶段查询数据。

  • 物化视图是由查询创建并存储的数据集，用于进一步的查询。物化视图在查询外部表时非常有用，因为它们的性能优于直接查询外部表。

avatar
文章
阅读
粉丝