Snowflake数据工程——持续数据摄取

本章内容包括：

• 比较批量数据摄取与持续数据摄取
• 介绍 Snowpipe
• 使用云消息配置 Snowpipe
• 使用和监控 Snowpipe
• 使用 Snowflake 动态表进行持续数据转换

在本章中，我们将构建一个新的数据管道，持续地从外部云存储中的文件摄取数据，并尽量减少延迟。我们将解释持续数据摄取和批量数据摄取之间的区别，这一点我们在前几章中已描述。我们将介绍 Snowpipe，这是 Snowflake 中用于数据管道中持续数据摄取的功能。最后，我们将使用动态表持续地进行数据转换。

我们将构建一个数据管道，使用 Snowpipe 从存储在外部云存储位置的 JSON 文件中摄取数据，并将数据从 JSON 格式转换为关系型格式。不同于第4章中执行存储过程的方式，我们将通过创建动态表来物化数据。

为了说明本章中构建管道的示例，我们将继续使用第2章中介绍的虚构面包店。简单回顾一下，面包店制作面包和糕点，并将这些烘焙食品送到附近的小型商家，如杂货店、咖啡馆和餐馆。

由于面包店没有在线订购系统，客户通过电子邮件下订单，面包店将这些订单存储在本地文件系统的 CSV 文件中。一些面包店的客户将每日订单信息提供为存储在专用云存储容器中的 CSV 或 JSON 文件。为了摄取所有这些文件，面包店的数据工程师构建了管道，通过内部或外部存储阶段访问文件。这些管道将数据从文件摄取到 Snowflake 表中，并根据需要进行转换。管道的运行时间安排在每天晚上的工作结束后，以便第二天提供最新的订单信息。

面包店的业务正在进一步扩展。它与一个在城市内提供食品配送服务的公司签订了合作协议。由于配送服务在工作时间内全天运营，面包店不能像现有数据管道那样等待每日文件摄取，因为他们需要每当客户下单时立即获取配送服务的订单信息。数据工程师必须构建一个新的数据管道，全天候持续摄取数据。

该食品配送服务有一个云存储平台，将订单以 JSON 格式保存在文件中。面包店的数据工程师将构建一个类似于第4章中描述的酒店云存储中的订单文件摄取管道。与该管道的主要区别在于，我们每天摄取酒店的文件一次，而通过 Snowpipe 摄取食品配送服务的文件时是持续进行的。

注意：本章的所有代码和示例数据文件可以在随附的 GitHub 仓库中的 Chapter_05 文件夹中找到，网址是 mng.bz/4plg。

为了构建从食品配送服务的云存储中摄取数据的管道，面包店的数据工程师将创建一个外部存储阶段，使数据文件可供 Snowflake 使用。他们将使用与配送服务云存储提供商的存储集成对象。这一步骤与第4章中描述的从云存储摄取数据的方式相同。

接着，将创建一个 Snowpipe，并与云存储提供商的通知服务进行集成。当有新文件到达云存储时，通知将发送到队列，允许 Snowpipe 知晓并摄取新文件。本章接下来的部分将更详细地说明如何配置 Snowpipe 与云消息服务。最后，将通过动态表将从 JSON 格式文件中摄取的平整化数据物化。图 5.1 描述了这一数据管道。

5.1 比较批量数据摄取与持续数据摄取

批量数据加载是指定期调度的数据管道，将数据摄取到 Snowflake 表中。通常，管道的调度会安排在每日的晚上或夜间运行。这种数据加载方式适用于新数据按已知时间间隔到达的情况，或数据消费者不需要最新数据，可以使用前一天的数据。

当消费者需要更频繁的数据时，可以将数据管道调度在更短的时间间隔内运行，例如每小时一次或每几分钟一次。这种频繁调度的数据管道通常被称为微批处理。如果整个管道能够在下次调度前完成执行，这种方法是可行的。但在实践中，通常由于管道的执行时间较长，无法满足要求，因此管道不能调度在比总执行时间更短的间隔内。

作为微批处理调度数据管道的替代方案，Snowflake 提供了 Snowpipe 功能，它旨在从文件中加载数据，一旦文件在存储阶段可用。Snowpipe 的行为类似于微批处理，它使用 COPY 命令将数据从文件加载到 Snowflake 表中，不同的是它不是通过时间间隔调度，而是通过事件触发。为了正常工作，Snowpipe 可以配置云消息服务，当新文件到达时，发送通知触发管道执行。

5.2 在云存储中准备文件

为了构建一个从食品配送服务的云存储持续摄取文件的数据管道，我们将首先创建一个存储集成对象，就像在第4章中构建从酒店云存储摄取订单数据的管道一样。和酒店一样，食品配送服务使用 Microsoft Azure 作为云存储提供商。我们将使用 Microsoft Azure 来说明本章的示例。

在 Amazon S3 或 Google Cloud Storage 中创建存储集成

Snowflake 文档提供了有关在 Amazon S3 和 Google Cloud Storage 中设置存储集成的详细信息，链接为 Amazon S3 和 Google Cloud Storage。

如果你在使用自己的 Microsoft Azure 账户进行操作，可以为本章的练习准备 Blob 存储中的文件。你必须通过以下步骤在你的账户中创建资源：

1. 创建一个资源组，或者如果你已经有一个现有资源组，并且想要使用它进行练习，可以直接使用它。你可以随意命名资源组。
2. 在资源组中创建一个存储账户。
3. 创建一个容器。你可以随意命名容器，如果你希望与本章练习中的命名相同，可以将其命名为 speedyservicefiles。

注意： 在选择存储账户的名称时，请记住它必须在 Azure 内唯一。本章中的练习使用的存储账户名称为 speedyorders001。如果你希望使用类似的名称但该名称不可用，可以将后缀从 001 改为其他数字组合，并相应修改代码。

当资源准备好后，你可以导航到 speedyservicefiles 容器，并从 GitHub 仓库中的 Chapter_05 文件夹中上传几个示例文件，名称为 Orders_2023-09-04_12-30-00_12345.json、Orders_2023-09-04_12-30-00_12346.json 和 Orders_2023-09-04_12-45-00_12347.json，上传到存储容器中。

提示： 尽管 GitHub 仓库中有更多文件可用，但此时只需将几个示例文件上传到 Blob 存储容器中。稍后，当我们演示如何使用 Snowpipe 加载新文件时，你将继续上传更多文件。

5.2.1 创建存储集成

食品配送服务必须提供与其存储账户相关的以下信息，以便创建存储集成：

• Azure 租户 ID
• 存储账户名称
• 存储容器名称

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

使用这些信息，我们可以使用以下命令创建 SPEEDY_INTEGRATION 存储集成：

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

如同第4章中一样，我们将执行 DESCRIBE INTEGRATION 命令，并注意 AZURE_CONSENT_URL 和 AZURE_MULTI_TENANT_APP_NAME 属性。然后，我们将与食品配送服务的 Azure 管理员协作，管理员将接受 Snowflake 服务主体并授予存储容器的权限。

一旦我们创建了存储集成，并且 Azure 管理员在 Azure 中完成了授权步骤，我们可以使用它来创建外部存储阶段。我们将授予 SYSADMIN 角色对存储集成对象的使用权限，然后通过执行以下命令来创建外部存储阶段并构建管道：

grant usage on integration SPEEDY_INTEGRATION to role SYSADMIN;

提示： 在这些初始章节中，为了简便，我们使用 SYSADMIN 角色在 Snowflake 中创建对象。通常，数据工程师会使用在 Snowflake 账户中设置的自定义角色，但由于我们没有创建任何自定义角色，我们将使用内置角色。

5.2.2 创建外部存储阶段

现在，我们将切换到 SYSADMIN 角色。为了将与食品配送服务外部数据源相关的对象与之前创建的对象分开，我们将在 BAKERY_DB 数据库中创建一个名为 DELIVERY_ORDERS 的新模式，使用以下命令：

use role SYSADMIN;
use database BAKERY_DB;
create schema DELIVERY_ORDERS;
use schema DELIVERY_ORDERS;

然后，我们将使用 SPEEDY_INTEGRATION 存储集成创建一个名为 SPEEDY_STAGE 的外部存储阶段。我们将提供从食品配送服务收到的存储容器中文件的位置（存储账户名为 speedyorders001，容器名为 speedyservicefiles）。由于我们知道文件是 JSON 格式的，因此我们将提供 FILE_FORMAT 参数，类型为 json。我们可以执行以下命令来创建外部存储阶段：

create stage SPEEDY_STAGE
  storage_integration = SPEEDY_INTEGRATION
  url = 'azure://speedyorders001.blob.core.windows.net/speedyservicefiles/'
  file_format = (type = json);

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

list @SPEEDY_STAGE;

该命令的输出将显示食品配送服务已经上传到 Blob 存储容器中的任何文件。如果你跟随本章的操作，LIST 命令将显示你之前上传到存储容器中的文件。

我们可以通过执行 SQL SELECT 命令来查看暂存文件中的数据：

select $1 from @SPEEDY_STAGE;

该命令的输出将显示一个变体列，数据以 JSON 格式存储在外部存储的文件中。以下是 JSON 数据结构的示例。

示例 5.1 食品配送服务的订单信息包含的 JSON 数据：

{
    "Order id": "12345",
    "Order datetime": "2023-09-04 12:30:00",
    "Items": [
        {
            "Item": "Croissant",
            "Quantity": 2
        },
        {
            "Item": "Bagel",
            "Quantity": 3
        }
    ]
}

如同 示例 5.1 中所示，JSON 层次结构的最高层有三个键值对，分别是 "Order id"、"Order datetime" 和 "Items"。其中，"Items" 键的值是一个包含 "Item" 和 "Quantity" 键值对的列表。图 5.2 显示了 JSON 结构的图形表示。

我们将从 JSON 结构中提取 ORDER_ID 和 ORDER_DATETIME 列作为单独的列，但将 ITEMS 列保留为变体数据类型，不进行解析。执行这一操作的命令，同时添加 metadata$filename 元数据列和当前时间戳，示例如下所示。

示例 5.2 从层级的第一层提取键的值

select 
  $1:"Order id",
  $1:"Order datetime",
  $1:"Items",
  metadata$filename, 
  current_timestamp() 
from @SPEEDY_STAGE;

为了存储 JSON 文件中的数据，我们将使用以下命令创建一个名为 SPEEDY_ORDERS_RAW_STG 的原始暂存表：

create table SPEEDY_ORDERS_RAW_STG (
  order_id varchar,
  order_datetime timestamp,
  items variant,
  source_file_name varchar,
  load_ts timestamp
);

到目前为止，准备外部存储阶段和目标表的过程与批量加载相同。此时，我们可以使用 COPY 命令将数据从食品配送服务的订单文件批量加载到暂存表中。但由于我们希望持续加载文件，即文件一旦到达云存储就进行加载，我们将继续构建 Snowpipe。

5.3 配置 Snowpipe 与云消息服务

Snowpipe 是一种无服务器的数据摄取服务，用于自动将存储在外部存储位置的文件加载到 Snowflake 中。与批量加载不同，Snowpipe 使用 Snowflake 提供的计算资源，而不是用户定义的虚拟仓库。Snowflake 在后台自动选择所需的计算资源来处理 Snowpipe 的摄取，无需用户干预。

Snowpipe 是一种 Snowflake 管道对象，包含一个 COPY 语句，用于将数据从外部存储阶段加载到关系型表中。与批量加载一样，文件中的数据可以是各种格式，包括 CSV 或半结构化数据。

为了摄取数据，Snowpipe 可以通过不同方式被通知有新文件可用：

• 通过与云消息服务集成（本章将详细描述）
• 通过调用 Snowpipe REST 端点（本章未涉及，且需要自定义编码，更多信息请参见 Snowflake 文档）

调用 Snowpipe REST 端点

除了云消息服务外，调用 Snowpipe REST API 的应用程序也可以通知 Snowpipe 有新文件可用。应用程序调用公共 REST 端点，传入 Snowpipe 对象的名称和文件名列表。这些文件将被排队，以便通过 COPY 语句及 Snowpipe 对象中定义的其他参数从存储阶段加载到 Snowflake 表中。

关于如何调用 Snowpipe REST 端点加载数据的更多信息，包括 Java 和 Python 的示例代码，详见 Snowflake 文档：链接。

云消息服务，也称为事件通知，通知 Snowpipe 外部存储中有新数据文件到达，Snowpipe 需要对其进行摄取。事件通知被存储在队列中，并供 Snowpipe 用来识别新文件。

注意： 事件通知仅包含元数据，如文件名等，文件将保留在云存储容器中，直到 Snowpipe 摄取它们。

以下云存储事件通知被支持用于将数据持续加载到 Snowflake 表中：

• Amazon S3：SQS（简单队列服务）通知，用于 S3 存储桶
• Google Cloud Storage：Pub/Sub 消息，用于 Google Cloud Storage 事件
• Microsoft Azure Blob 存储：事件网格消息，用于 Blob 存储事件

在本章中，我们将使用 Microsoft Azure 事件网格消息。

配置 Amazon S3 或 Google Cloud Storage 中的云消息服务

Snowflake 文档提供了在 Amazon S3 和 Google Cloud Storage 中设置云消息服务的详细信息：Amazon S3，Google Cloud Storage。

5.3.1 配置 Azure Blob 存储事件的事件网格消息

要在 Microsoft Azure 中配置事件网格消息服务，我们必须配置以下资源：

1. 存储账户：存储队列必须位于存储账户中。我们可以创建一个新的存储账户，也可以使用存储数据文件的账户。为了简化本章的练习，我们将使用已创建的 speedyorders001 存储账户，用于存储数据文件和存储队列。
2. 存储队列：用于存储以队列形式出现的通知。
3. 事件网格订阅：定义用户希望接收哪些特定主题的事件通知。在定义事件网格订阅时，我们将定义事件网格系统主题，用于捕获 Blob 存储事件，例如存储容器中新文件的出现。

以下部分将详细解释如何使用 Microsoft Azure 门户配置事件网格消息服务的步骤。

启用事件网格资源提供程序

在使用 Microsoft Azure 事件网格之前，必须检查事件网格资源提供程序是否在 Azure 订阅中启用。如果没有启用，按照以下步骤启用：

1. 导航到你的订阅。
2. 选择“资源提供程序”选项。
3. 在出现的资源列表中，找到“Microsoft.EventGrid”。
4. 如果状态为“已注册”，则无需进行任何操作。如果状态不是“已注册”，则选择“Microsoft.EventGrid”行，并点击页面顶部的“注册”选项以启用事件网格资源提供程序。

创建存储队列

存储队列用于存储一组消息，在本例中是来自事件网格的事件消息。我们将在 speedyorders001 存储账户中创建存储队列。创建存储队列的步骤如下：

1. 导航到存储账户。
2. 选择“队列”选项。
3. 创建一个新的队列。你可以随意命名队列，但如果你跟随本章操作，我们将把它命名为 speedyordersqueue。
4. 请注意队列 URL，因为稍后我们将需要它。在本示例中，URL 为 https://speedyorders001.queue.core.windows.net/speedyordersqueue。

创建事件网格订阅

订阅某个主题意味着通知事件网格要跟踪哪些事件。创建事件网格订阅的步骤如下：

1. 导航到存储账户。
2. 选择“事件”选项。
3. 创建一个新的事件订阅。你可以随意命名事件订阅，但如果你跟随本章操作，我们将命名为 speedysubscription。
4. 为事件网格系统主题添加名称，我们将其命名为 speedyordersevents。事件网格中的系统主题表示由 Azure 服务发布的一个或多个事件。在本示例中，当一个 Blob 上传到存储账户时，Azure 存储服务会将“Blob 创建”事件发布到事件网格的系统主题，然后将该事件转发给订阅者（在本例中为 speedyordersqueue 队列），该队列接收并处理该事件。
5. 展开“过滤到事件类型”选项，确保仅选择“Blob 创建”事件类型。只有这些事件会触发 Snowpipe 来加载文件，因为仅支持将新对象添加到 Blob 存储的事件类型。
6. 在“端点类型”下拉列表中，选择“存储队列”。然后点击“配置端点”选项。输入创建队列的存储容器名称——在本示例中为 speedyorders001。选择“选择现有队列”选项，并在下拉列表中选择 speedyordersqueue 队列。

图 5.3 显示了这些资源的图形表示。

一旦这些资源准备好，我们就可以在 Snowflake 中创建一个通知集成。这将使 Snowpipe 在触发 Microsoft Azure 事件网格通知时，自动加载数据。

5.3.2 创建通知集成

通知集成是一个 Snowflake 对象，它提供了 Snowflake 与第三方云消息队列服务（如 Azure 事件网格）之间的接口。

食品配送服务必须提供以下与其存储账户相关的信息，这些信息将在创建通知集成时使用：

• Azure 租户 ID
• 存储队列 URL

在我们的示例中，Azure 租户 ID 为 1234abcd-xxx-56efgh78（这是一个用于说明的虚构租户 ID），存储队列 URL 是我们在创建存储队列时记录的 https://speedyorders001.queue.core.windows.net/speedyordersqueue。

使用这些信息，我们可以使用以下命令创建 SPEEDY_QUEUE_INTEGRATION 通知集成：

use role ACCOUNTADMIN;
CREATE NOTIFICATION INTEGRATION SPEEDY_QUEUE_INTEGRATION
ENABLED = true
TYPE = QUEUE
NOTIFICATION_PROVIDER = AZURE_STORAGE_QUEUE
AZURE_STORAGE_QUEUE_PRIMARY_URI = 'https://speedyorders001.queue.core.windows.net/speedyordersqueue'
AZURE_TENANT_ID = '1234abcd-xxx-56efgh78';

就像创建存储集成一样，我们将执行 DESCRIBE INTEGRATION 命令，并注意 AZURE_CONSENT_URL 和 AZURE_MULTI_TENANT_APP_NAME 属性，使用以下命令：

describe notification integration SPEEDY_QUEUE_INTEGRATION;

然后，我们将与食品配送服务的 Azure 管理员合作，管理员将接受 Snowflake 服务主体并授予“存储队列数据贡献者”角色。

提示： 详细的步骤说明如何在 Azure 中接受 Snowflake 服务主体并授予角色权限，参见 Snowflake 文档：链接。

一旦通知集成创建完成并且 Azure 中的授权步骤完成，我们就可以使用它来创建 Snowpipe。我们将向将创建 Snowpipe 并构建管道的角色授予通知集成对象的使用权限。通常，我们使用自定义角色执行数据工程活动，但由于我们正在进行练习且没有创建任何自定义角色，我们将使用 SYSADMIN 角色来创建 Snowpipe。因此，我们将通过执行以下命令向 SYSADMIN 角色授予通知集成对象的使用权限：

grant usage on integration SPEEDY_QUEUE_INTEGRATION to role SYSADMIN;

5.3.3 创建管道对象

现在，我们准备创建 Snowpipe。管道对象的名称是 SPEEDY_PIPE；它使用 SPEEDY_QUEUE_INTEGRATION 通知集成，并执行一个 COPY 命令，该命令将数据从 SPEEDY_STAGE 外部存储阶段中的暂存文件复制到 SPEEDY_ORDERS_RAW_STG 暂存表中，使用 示例 5.2 中的查询。以下命令创建了描述的 Snowpipe：

use role SYSADMIN;
create pipe SPEEDY_PIPE
  auto_ingest = true
  integration = 'SPEEDY_QUEUE_INTEGRATION'
  as
  copy into SPEEDY_ORDERS_RAW_STG
  from (
    select     #1
      $1:"Order id",
      $1:"Order datetime",
      $1:"Items",
      metadata$filename, 
      current_timestamp() 
    from @SPEEDY_STAGE
  );

#1 来自 示例 5.2 的查询

一旦 Snowpipe 创建完成，每当代表新文件出现的事件添加到消息队列时，它就会执行管道对象中定义的 COPY 命令。

由于我们刚刚创建了管道，它将仅开始从新到达的文件中摄取数据，但不会检测到云存储中已存在的文件。为了从创建 Snowpipe 之前就已存储在云存储中的文件中摄取数据，我们可以使用 REFRESH 关键字修改管道。

注意： 使用 REFRESH 命令修改管道时，它会检测过去 7 天内上传到云存储的文件。上传时间更早的文件不会被检测到。对于较旧的文件，可以通过执行 COPY 命令作为批量加载将数据摄取到 Snowflake 表中，正如第 2 章所解释的那样。

当我们使用 REFRESH 关键字修改管道时，之前上传到存储容器中的文件（如 Orders_2023-09-04_12-30-00_12345.json、Orders_2023-09-04_12-30-00_12346.json 和 Orders_2023-09-04_12-45-00_12347.json）的数据应该被摄取到 Snowflake 表中。我们可以执行以下命令：

alter pipe SPEEDY_PIPE refresh;

执行上面的命令后，我们希望验证云存储中的文件数据是否确实加载到 SPEEDY_ORDERS_RAW_STG 表中。我们可以通过执行以下 SQL 命令来查询该表：

select * 
from SPEEDY_ORDERS_RAW_STG;

该命令的输出应返回三行数据，如 表 5.1 所示。

表 5.1 刷新管道对象后的暂存表数据（显示前四列）

Order id	Order datetime	Orders	Source filename
12345	2023-09-04 12:30:00.000	[{"Item":"Croissant","Quantity":2}, {"Item":"Bagel","Quantity":3}]	Orders_2023-09-04_12-30-00_12345.json
12346	2023-09-04 12:30:00.000	[{"Item":"Croissant","Quantity":5}]	Orders_2023-09-04_12-30-00_12346.json
12347	2023-09-04 12:45:00.000	[{"Item":"Muffin","Quantity":12}]	Orders_2023-09-04_12-45-00_12347.json

提示： 数据可能需要几分钟才能出现在暂存表中。如果查询首次执行时返回零行，请稍等一两分钟后再试。

在监控和排查管道对象时，您可以使用 system$pipe_status 函数检查管道的状态，执行以下命令：

select system$pipe_status('SPEEDY_PIPE');

该命令的输出将显示管道的执行状态、待处理文件的数量、最后一次摄取的时间戳、最后一次摄取的文件名称等信息。这将帮助您更好地了解管道的状态。

另一个有用的函数是 copy_history 表函数。该函数显示由 COPY 命令执行的批量加载和 Snowpipe 执行的加载历史记录。我们可以执行以下命令，查看过去一小时内 SPEEDY_ORDERS_RAW_STG 表的加载历史记录：

select *
from table(information_schema.copy_history(
  table_name => 'SPEEDY_ORDERS_RAW_STG', 
  start_time => dateadd(hours, -1, current_timestamp())));

该命令的输出将显示如文件名、外部存储阶段位置、最后加载时间、行数、文件大小、错误消息（如果有）以及在 Snowpipe 执行 COPY 命令时的管道信息等内容。

提示： Snowpipe 会跟踪已摄取的文件，并避免重复摄取相同文件中的数据，以防止数据重复。这种跟踪适用于管道对象和目标表。如果你正在练习创建和重新创建管道对象，必须重新创建目标表，以便从相同文件中重新加载数据。否则，即使你重新创建了管道对象，但没有重新创建目标表，Snowpipe 也不会从暂存的文件中摄取数据，因为它认为数据已经被加载到目标表中。

5.3.4 持续摄取数据

现在我们已经设置了管道对象并摄取了初始数据，我们可以继续进行持续摄取数据的操作。我们将执行以下场景：

1. 从 SPEEDY_ORDERS_RAW_STG 暂存表中选择数据，查看已经加载到表中的文件数量。
2. 上传一个新文件到云存储。
3. 等待一两分钟。
4. 再次从 SPEEDY_ORDERS_RAW_STG 暂存表中选择数据，验证 Snowpipe 是否已将云存储中新文件的数据摄取并可用。

进入 Microsoft Azure 门户，导航到 speedyservicefiles 容器，从 GitHub 仓库 Chapter_05 文件夹中上传另一个名为 Orders_2023-09-04_12-45-00_12348.json 的示例文件到存储容器。

一两分钟后，从暂存表中选择数据：

select * 
from SPEEDY_ORDERS_RAW_STG;

上传文件的数据应出现在暂存表中。这确认了 Snowpipe 正在持续加载数据，并通过云消息服务在新文件到达时得到通知。

如果新数据没有立即出现在暂存表中，请耐心等待一分钟，然后再次执行查询。你可以用另一个文件重复此场景：将文件上传到云存储容器，然后检查数据是否已被摄取到暂存表中。

提示： 如果你跟随操作并多次重复上传新文件到云存储容器的场景，且 GitHub 仓库中的文件已用尽，你可以创建额外的文件。复制一个现有文件，并将其重命名为一个新的唯一名称。确保文件中的 JSON 结构保持不变，否则摄取步骤将失败。

5.3.5 将 JSON 结构扁平化为关系型格式

现在，来自云存储文件的数据正在持续加载，让我们更详细地检查订单数据的 JSON 结构，并将其扁平化为关系型结构。如 示例 5.1 所示，在层级的最高层有三个 JSON 键值对，键名分别为“Order id”、“Order datetime”和“Items”。“Items”键的值是一个包含键值对的列表，键名为“Baked good type”和“Quantity”。

在 SPEEDY_ORDERS_RAW_STG 暂存表中，我们将层级最高层三个键的值提取为单独的列。接下来，我们将扁平化“Items”键的值为关系型列。

我们将使用 FLATTEN 函数，并配合 LATERAL 修饰符将 JSON 结构扁平化为关系型列，正如我们在第 4 章中处理酒店订单数据时所做的那样。我们可以使用以下查询从“Items”第一层键的值中提取第二层键（“Item”和“Quantity”）的值。

示例 5.3 扁平化第二层层级的键的值：

select
  order_id,
  order_datetime,
  value:"Item"::varchar as baked_good_type,
  value:"Quantity"::number as quantity
from SPEEDY_ORDERS_RAW_STG,
lateral flatten (input => items);

此查询的输出的前几行应类似于 表 5.2 中显示的数据。

表 5.2 从 JSON 结构扁平化后的数据：

Order id	Order datetime	Baked good type	Quantity
12345	2023-09-04 12:30:00.000	Croissant	2
12345	2023-09-04 12:30:00.000	Bagel	3
12346	2023-09-04 12:30:00.000	Croissant	5
12347	2023-09-04 12:45:00.000	Muffin	12
12348	2023-09-04 12:45:00.000	Muffin	2

随着食品配送服务的新订单添加，暂存表中的数据将增长。这可能导致执行 示例 5.3 查询时，扁平化 JSON 结构的查询性能下降。提高查询性能的一种方法是将查询结果物化到表中。面包店的数据工程师考虑了不同的数据物化方法，包括：

• 创建一个包含扁平化数据的视图，创建目标表，并将视图中的数据合并到目标表中。这种方法用于从酒店的云存储摄取订单数据，如第 4 章所述。由于酒店的订单信息每天都需要，所以批量加载调度足够了。对于食品配送服务而言，批量调度不可行，因为面包店持续需要订单数据。
• 创建一个物化视图，如第 3 章所述。如果扁平化数据的查询不太复杂，且不涉及从多个表中选择数据，这可以作为一个选项。
• 创建一个动态表，相比于物化视图，动态表在查询类型上具有更大的灵活性，并且可以更好地控制在表维护中使用的计算资源。

面包店的数据工程师决定构建一个动态表，以物化 SPEEDY_ORDERS_RAW_STG 暂存表中的扁平化数据。下一节将更详细地介绍动态表。

5.4 使用动态表转换数据

Snowflake 定义动态表为声明性数据转换管道的构建块。它们支持自动化的数据转换，这是面包店处理来自食品配送服务数据所必需的。

与创建目标表并将转换后的数据插入或合并到表中不同，您可以创建动态表来物化在动态表定义中指定的查询结果。数据工程师无需调度或管理填充动态表的数据任务，因为 Snowflake 在后台处理动态表的维护。

尽管动态表有一些限制，例如不允许使用存储过程、非确定性函数或外部函数，但与物化视图相比，动态表的限制较少，适用于许多不同的用例。

接下来，我们将创建一个动态表，将 SPEEDY_ORDERS_RAW_STG 暂存表中的数据扁平化并物化。除了提供动态表名称和需要物化的查询外，我们在创建动态表时还必须指定两个额外的参数：

• TARGET_LAG：定义动态表中的数据相对于基础表的最大延迟时间。例如，如果目标延迟设置为 1 分钟，则动态表中的数据应最多延迟 1 分钟。
• WAREHOUSE：指定将用于刷新动态表的虚拟仓库名称。

为了创建一个名为 SPEEDY_ORDERS 的动态表，该表的数据延迟不超过 1 分钟，使用 BAKERY_WH 虚拟仓库，并物化 示例 5.3 中的查询结果，我们可以执行以下命令：

create dynamic table SPEEDY_ORDERS
  target_lag = '1 minute'
  warehouse = BAKERY_WH
  as 
  select     #1
  order_id,
  order_datetime,
  value:"Item"::varchar as baked_good_type,
  value:"Quantity"::number as quantity,
  source_file_name,
  load_ts
from SPEEDY_ORDERS_RAW_STG,
lateral flatten (input => items);

#1 来自 示例 5.3 的查询

最初，动态表会物化定义查询中的数据。当我们从动态表中选择数据时，我们应该看到与 示例 5.3 查询结果相同的数据。

接下来，让我们看看当新数据到达云存储时，动态表会发生什么。我们将执行以下场景：

1. 从 SPEEDY_ORDERS 动态表中选择数据，查看已有多少文件被加载到表中。
2. 上传一个新文件到云存储。
3. 等待一两分钟。
4. 再次从 SPEEDY_ORDERS 动态表中选择数据，验证来自云存储的新文件的数据是否已在表中。

进入 Microsoft Azure 门户，导航到 speedyservicefiles 容器，从 GitHub 仓库 Chapter_05 文件夹中上传另一个名为 Orders_2023-09-04_13-00-00_12349.json 的示例文件到存储容器。等待一两分钟后，再次从动态表中选择数据。

示例 5.4 从动态表查看订单数据：

select *
from SPEEDY_ORDERS
order by order_datetime desc;

上传文件的数据应出现在动态表中。这确认了 Snowpipe 正在持续加载数据，并且动态表在定义的目标延迟 1 分钟内物化数据。

如果新数据没有立即出现在动态表中，请耐心等待一分钟，然后再次执行查询。您可以使用另一个文件重复此场景：将文件上传到云存储容器，然后检查数据是否出现在暂存表中。

通过执行 示例 5.4 中的查询，该查询按订单日期和时间降序排列，面包店员工可以看到最新的订单信息显示在查询结果的顶部。

像管道对象一样，我们可以通过 Snowsight 用户界面或执行 Snowflake 内置函数（如 dynamic_table_refresh_history 和 dynamic_table_graph_history）来监控和排查动态表的执行。

要查看动态表的刷新历史记录，我们可以执行以下命令：

select *
from table(information_schema.dynamic_table_refresh_history())
order by refresh_start_time desc;

该命令的输出列出了每次动态表执行的详细信息，按目标延迟频率排序。例如，SPEEDY_ORDERS 动态表是以 1 分钟的延迟频率创建的。因此，刷新历史记录每分钟都会显示一行，表示动态表已按计划刷新。即使源表中没有新数据，且动态表没有需要刷新的内容，记录也会显示动态表尝试刷新。刷新历史记录的输出对于排查刷新失败和调查错误非常有帮助。

您可以通过动态表做更多事情，例如通过创建一个查询其他动态表的动态表来设置管道。您还可以定义使用 SQL 语句并在多个表之间使用连接条件的动态表。

注意： 有关动态表的更多信息，请参考 Snowflake 文档：链接。

SNOWPIPE 流式处理与 KAFKA 连接器

使用 Snowpipe 进行持续数据摄取和批量摄取都适用于数据文件。然而，并非所有数据都以文件的形式存在。有时数据以行的形式在流模式中生成——例如，来自 Apache Kafka 主题的数据。当我们需要摄取流数据时，可以使用 Snowpipe 流式处理 API，配合自定义的 Java 应用程序，该应用程序会在数据变得可用时自动将数据流加载到 Snowflake 中。

有关 Snowpipe 流式处理的更多信息，请参考 Snowflake 文档：链接。

Snowflake 还提供了 Kafka 连接器，这是一个旨在从 Kafka 主题读取数据并将数据写入 Snowflake 表的框架。

有关 Kafka 连接器的更多信息，请参考 Snowflake 文档：链接。 对于 Kafka 流式处理的一般信息，Stefan Sprenger 的《Streaming Data Pipelines with Kafka》一书是一个很好的资源，您可以在这里找到：链接。

总结

• 批量数据加载是指定期调度的数据管道，将数据摄取到 Snowflake 表中。管道可以安排在较短的时间间隔内运行——例如，每小时一次或每几分钟一次。这种频繁调度的数据管道被称为微批处理。
• 作为微批处理数据管道调度的替代方案，Snowflake 提供了Snowpipe功能，旨在当文件一旦在存储阶段可用时，立即将数据加载到 Snowflake 中。
• Snowpipe是一个 Snowflake 对象，包含一个 COPY 语句，用于将数据从外部存储阶段加载到关系型表中。与批量加载一样，文件中的数据可以是各种格式，包括 CSV 或半结构化格式。
• Snowpipe使用 Snowflake 提供的计算资源，而不是用户定义的虚拟仓库，这与批量加载不同。Snowflake 会在后台自动选择 Snowpipe 所需的计算资源，无需用户干预。
• 为了摄取数据，Snowpipe可以通过与云消息服务（也称为事件通知）集成，接收新的文件到达云存储的通知。事件通知会告知 Snowpipe 新文件到达云存储并需要被摄取。这些事件通知被存储在队列中。
• Microsoft Azure 的事件网格消息包括用于存储通知的存储队列、用于定义事件的事件网格订阅，以及用于捕捉 Blob 存储事件（如新文件出现在存储容器中的事件）的事件网格系统主题。
• 通知集成是一个 Snowflake 对象，它提供了 Snowflake 与第三方云消息队列服务（如 Azure 事件网格）之间的接口。
• 每当代表 Blob 存储中新文件出现的事件添加到消息队列时，Snowpipe会在管道对象的定义中执行 COPY 语句。可以使用 REFRESH 关键字修改管道，以摄取 Snowpipe 创建之前已经存在于云存储中的文件的数据。
• 可以使用 system$pipe_status 函数检查管道的状态。copy_history 表函数显示由批量加载和 Snowpipe 执行的 COPY 命令生成的表加载历史。
• 可以使用不同的方法来扁平化并物化暂存表中的数据，例如创建目标表并将数据合并，创建物化视图或创建 动态表。
• 动态表是声明性数据转换管道的构建块。与创建目标表并将转换后的数据插入或合并到其中不同，动态表可以物化在动态表定义中指定的查询结果。
• 数据工程师无需调度或管理填充动态表的数据任务，因为 Snowflake 会在后台处理动态表的维护。
• 在创建动态表时，除了提供动态表名称和需要物化的查询外，还需要指定两个额外的参数。它们分别是目标延迟（定义动态表的数据相对于查询表的最大延迟时间）和虚拟仓库（将用于刷新动态表）。