Snowflake数据工程——数据转化

本章内容：

• 从云存储中摄取半结构化数据
• 将半结构化数据扁平化为关系型表
• 使用存储过程封装转换
• 在存储过程中实现异常处理和日志记录
• 构建健壮的数据管道

在本章中，我们将增强从云存储摄取数据的数据管道，这个管道是在第三章中构建的。我们将在管道的数据转换部分添加更多功能，并增加异常处理和日志记录，以帮助构建一个尽可能抗误操作的健壮数据管道。

我们将为管道添加的第一个转换类型是将JSON格式的半结构化数据扁平化为关系型表。然后，我们将使用存储过程执行更多的数据转换操作。接下来，我们将添加数据转换步骤到数据管道中。

然后，我们将通过增加异常处理和日志记录来增强管道的健壮性。在发生任何错误或执行不完整时，我们希望确保数据管道是健壮的，以便它可以重新启动，并且在修复错误后再次执行时不会重复处理数据。

为了说明构建管道的示例，我们将继续使用在第二章中介绍的虚构面包店。简要回顾一下，这家面包店生产多种面包和糕点，并将这些烘焙食品配送给小型商户，如邻里的杂货店、咖啡店和餐馆。

由于面包店没有在线订购系统，客户通过电子邮件订购烘焙食品。面包店将这些订单存储在本地文件系统中的CSV文件中。面包店的一位客户——附近的一家餐馆——以CSV文件的形式提供每日订单信息，并将其存储在其云存储平台中的专用Blob存储容器中。面包店构建了两个数据管道来摄取这些文件，如第二章和第三章所述：

1. 一名面包店员工将CSV文件从本地文件系统上传到Snowflake的内部命名阶段。数据管道从这些文件摄取数据到Snowflake表中。
2. 第二个数据管道通过指向餐馆云存储容器的外部阶段访问餐馆的文件。然后，管道将数据从文件摄取到Snowflake表中。

邻里的一家小酒店想开始从面包店购买烘焙食品。该酒店已经有一个信息系统，其中的一项功能是收集订单信息，并以JSON格式在云存储中创建包含订单的文件。

面包店将从酒店的云存储中摄取JSON格式的文件，并构建一个类似于第三章中描述的摄取餐馆云存储中订单数据的管道。两个管道之间的主要区别是餐馆以CSV格式下订单，这些文件可以直接转换为关系型格式，而酒店的订单以JSON格式提供，我们必须将其扁平化为关系型数据结构。

注意：本章的所有代码和示例数据文件都可以在随附的GitHub仓库中找到，位于Chapter_04文件夹中，链接：mng.bz/PNJ5。SQL代码存储在多个文件中，文件名以Chapter_4_Part开头，其中表示序列号。请按照文件顺序阅读并完成练习。

为了构建摄取来自酒店云存储文件的数据的管道，面包店的数据工程师将创建一个外部阶段，以便将数据文件提供给Snowflake。他们将使用与酒店云存储提供商的存储集成对象，并将进行初始设置。

一旦外部阶段创建完成，我们将使用COPY命令将数据从阶段复制到暂存表。图4.1展示了三个数据管道，这些管道分别摄取来自面包店本地文件系统的CSV文件、来自餐馆云存储提供商的CSV文件以及来自酒店云存储提供商的JSON文件。本章将详细描述第三个管道，即从酒店云存储文件摄取数据的管道。

4.1 从云存储摄取半结构化数据

为了构建一个从酒店云存储摄取JSON格式文件的数据管道，我们将从创建存储集成对象开始，就像我们在第三章中为摄取餐馆订单数据所做的那样。与使用Microsoft Azure作为云存储提供商的餐馆不同，酒店使用Amazon S3。我们将在本章的示例中使用Amazon S3。

提示
如果你使用的是Microsoft Azure帐户，你仍然可以继续使用它来完成本章的示例。按照第三章中的描述，你可以在Microsoft Azure中创建存储集成对象和外部阶段。

我们将为本章的练习准备一个Amazon S3桶。你将开始在你的AWS账户中创建资源，执行以下步骤：

1. 按照Snowflake文档中的说明创建IAM策略和IAM角色，文档链接：mng.bz/JNJQ。记下角色摘要页面中的Role ARN值。例如，Role ARN可能是：arn:aws:iam::567890987654:role/Snowflake-demo。
2. 创建一个S3桶。

注意
在选择S3桶的名称时，请记住桶名称必须在3到63个字符之间，并且只能包含小写字母、数字、点号和连字符。此外，你的存储账户名称必须在所有AWS账户和所有AWS区域中唯一。本章的练习使用parkinnorders001作为桶名。如果你想使用类似的名称但该名称已不可用，请将后缀001更改为其他数字组合，并相应修改代码。

一旦资源设置完成，你可以导航到parkinnorders001桶，并从GitHub仓库中的Chapter_04文件夹上传其中的示例文件Orders_2023-08-11.json到该桶。

4.1.1 创建存储集成

为了创建存储集成，酒店需要提供与其存储账户相关的以下信息：

• 角色ARN（Role ARN）
• S3桶的名称

在我们的示例中，角色ARN是arn:aws:iam::567890987654:role/Snowflake-demo（这是一个虚构的角色ARN用于说明目的），而S3桶的名称是parkinnorders001。

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

use role ACCOUNTADMIN;
create storage integration PARK_INN_INTEGRATION
  type = external_stage
  storage_provider = 'S3'
  enabled = true
  storage_aws_role_arn = 'arn:aws:iam::567890987654:role/Snowflake-demo'
  storage_allowed_locations = ('s3://parkinnorders001/');

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

describe storage integration PARK_INN_INTEGRATION;

表4.1显示了此命令的示例输出（你的输出将包含你的AWS IAM用户、IAM角色和外部ID）。

表4.1 DESCRIBE STORAGE INTEGRATION命令的示例输出

属性	属性类型	属性值
ENABLED	Boolean	TRUE
STORAGE_PROVIDER	String	S3
STORAGE_ALLOWED_LOCATIONS	List	s3://parkinnorders001/
STORAGE_BLOCKED_LOCATIONS	List
STORAGE_AWS_IAM_USER_ARN	String	arn:aws:iam::345678987654:user/nxl3-s-eusc3487
STORAGE_AWS_ROLE_ARN	String	arn:aws:iam::567890987654:role/Snowflake-demo
STORAGE_AWS_EXTERNAL_ID	String	ZY34586_SFCRole=2_NbxDAb2mWHQom1HDCwpeyA8RDsEv

该命令返回存储集成的属性。我们必须注意以下属性的值：

• STORAGE_AWS_IAM_USER_ARN：这个参数的值可能看起来像arn:aws:iam::345678987654:user/nxl3-s-eusc3487（这是一个虚构的用户ARN）。
• STORAGE_AWS_EXTERNAL_ID：这个参数的值可能像ZY34586_SFCRole=2_NbxDAb2mWHQom1HDCwpeyA8RDsEv（这是一个虚构的外部ID）。它代表了建立Snowflake和AWS之间信任关系所需的外部ID。

现在我们回到AWS控制台，在这里我们将授予IAM用户访问S3桶中文件的必要权限。详细说明可以参考Snowflake文档中的“授予IAM用户访问桶对象的权限”部分：mng.bz/w5Wg。

在我们的示例中，由AWS管理员执行AWS管理步骤，而不是数据工程师。如果你正在使用自己的AWS账户，你也将是AWS管理员。

一旦我们创建了存储集成并完成了AWS中的授权步骤，我们就可以使用它来创建外部阶段。我们将向存储集成对象授予SYSADMIN角色的使用权限，SYSADMIN角色将用于创建外部阶段并构建管道。我们可以通过执行以下命令来实现：

grant usage on integration PARK_INN_INTEGRATION to role SYSADMIN;

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

4.1.2 创建外部阶段

现在，我们将切换到SYSADMIN角色。为了将与来自JSON文件的外部数据相关的对象与之前创建的对象区分开来，我们将在BAKERY_DB数据库中创建一个名为EXTERNAL_JSON_ORDERS的新架构，并使用以下命令：

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

接下来，我们将使用PARK_INN_INTEGRATION存储集成创建一个名为PARK_INN_STAGE的外部阶段。我们将提供来自酒店的存储容器中文件的位置（存储账户名称为parkinnorders001，容器名称为orderjsonfiles）。由于我们知道文件是JSON格式的，因此我们将FILE_FORMAT参数设置为type = json。我们可以执行以下命令来创建外部阶段：

create stage PARK_INN_STAGE
  storage_integration = PARK_INN_INTEGRATION
  url = 's3://parkinnorders001/'
  file_format = (type = json);

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

list @PARK_INN_STAGE;

你之前上传到S3桶中的文件Orders_2023-08-11.json将在此命令的输出中显示。

4.1.3 检查JSON结构

让我们查看暂存文件中的数据。我们可以在阶段上使用SELECT命令，但请记住，在从阶段选择数据时，我们不会像通常的SQL查询那样使用SELECT *语法来表示所有列。相反，我们使用$符号来引用文件中的列——例如，$1表示第一列。我们可以执行以下命令来查看暂存文件中的数据：

select $1 from @PARK_INN_STAGE;

此命令的输出应该返回包含酒店订单信息的JSON数据。JSON数据看起来如下所示（并未显示所有输出）：

列出 4.1 酒店订单信息的JSON数据

{
  "Customer": "Park Inn",
  "Order date": "2023-08-11",
  "Orders": [
    {
      "Delivery date": "2023-08-14",
      "Orders by day": [
        {
          "Baked good type": "English Muffin",
          "Quantity": 30
        },
        ...
        {
          "Baked good type": "Croissant",
          "Quantity": 36
        }
      ]
    },
    {
      "Delivery date": "2023-08-15",
      "Orders by day": [
        {
          "Baked good type": "English Muffin",
          "Quantity": 26
        },
        ...
        {
          "Baked good type": "Croissant",
          "Quantity": 30
        }
      ]
    }
  ]
}

从这个输出中，我们可以推测JSON数据的结构。在层级的最顶端有三个键值对，分别是：“Customer”，“Order date”和“Orders”。“Orders”键的值包含两个键值对，键分别为“Delivery date”和“Orders by day”。“Orders by day”键的值包含一系列键值对，键为“Baked good type”和“Quantity”。为了更好地理解，列出4.1中的JSON结构在图4.2中进行了说明。

4.1.4 将JSON数据摄取到VARIANT数据类型中

让我们构建一个数据管道，将此JSON数据摄取到Snowflake并将其扁平化为关系型表。

Snowflake对半结构化数据的支持

除了本章讨论的JSON格式外，Snowflake还可以从Avro、ORC、Parquet和XML格式摄取半结构化数据。半结构化数据包含标识数据中实体的键或其他类型的标记。这些键通常通过嵌套的方式按层次结构组织。与存储在关系型表中的结构化数据不同，半结构化数据不需要预定义的架构，并且架构可能随着时间的推移发生变化。Snowflake将半结构化数据存储在VARIANT数据类型中。此数据类型可以包含其他数据类型，包括OBJECT、ARRAY以及其他VARIANT数据类型。

有关半结构化数据的更多信息，请参阅Snowflake文档：mng.bz/q0Gz。

首先，我们将在Snowflake中创建一个表来存储原始的JSON数据。在这里，我们可以使用Snowflake的VARIANT数据类型来存储半结构化数据，例如JSON。我们将创建一个名为ORDERS_PARK_INN_RAW_STG的表，其中包含一个名为CUSTOMER_ORDERS的VARIANT列，用于存储JSON数据。我们还将添加两个列：SOURCE_FILE_NAME，它将存储来自外部阶段的数据源文件名，以及LOAD_TS列，表示我们将数据加载到Snowflake时的时间戳。我们可以使用以下命令来创建此表：

use database BAKERY_DB;
use schema EXTERNAL_JSON_ORDERS;
create table ORDERS_PARK_INN_RAW_STG (
  customer_orders variant,
  source_file_name varchar,
  load_ts timestamp
);

我们可以使用COPY命令将数据从外部阶段加载到该表中，正如前几章所讨论的那样。我们将执行以下命令：

copy into ORDERS_PARK_INN_RAW_STG
from (
  select 
    $1, 
    metadata$filename, 
    current_timestamp() 
  from @PARK_INN_STAGE
)
on_error = abort_statement;

执行COPY命令后，我们可以通过执行SELECT语句查询ORDERS_PARK_INN_RAW_STG表中的数据：

select * 
from ORDERS_PARK_INN_RAW_STG;

根据上一条命令的输出，我们应该能看到JSON结构，类似于列出4.1中的内容，并且包含两个附加列：数据来源的云存储文件名和加载时间戳，如表4.2所示。

表4.2 从ORDERS_PARK_INN_RAW_STG表选择时的示例输出（Customer orders列未完全显示）

Customer orders	Source filename	Load timestamp
{"Customer": "Park Inn", "Order date": "2023-08-11", "Orders": [{"Delivery date": "2023-08-14", "Orders by day": [{"Baked good type": "English Muffin", "Quantity": 30...	Orders_2023-08-11.json	2023-08-11 07:57:15.882

现在，我们已经将JSON数据加载到Snowflake中的一个表中，接下来让我们将其扁平化，以便将其存储到关系型表中，供面包店经理使用，后者想知道在接下来的几天里，每种类型的烘焙食品的订购数量。

4.2 将半结构化数据扁平化为关系型表

Snowflake提供了查询半结构化数据（如JSON）的语法。在本章中，我们将使用所需的语法，将酒店的JSON文件扁平化为面包店可以使用的关系型格式。

注意
有关查询半结构化数据的更多信息以及语法的详细描述，请参阅Snowflake文档：mng.bz/75r4。

如我们在列出4.1中所见，酒店提供的JSON数据具有层次结构，在层次结构的最高级别有三个键：“Customer”，“Order date”和“Orders”。为了查看每个键的值，我们可以使用: 操作符从存储在CUSTOMER_ORDERS列中的JSON数据中选择键值。例如，要查看“Customer”键的值，我们可以使用以下语法：

customer_orders:"Customer"

以下列出了完整的SQL查询，它选择了层次结构最高级别的每个键的值。

列出 4.2 从层次结构最高级别的键选择值

select 
  customer_orders:"Customer"::varchar as customer, 
  customer_orders:"Order date"::date as order_date, 
  customer_orders:"Orders"
from ORDERS_PARK_INN_RAW_STG;

查询的输出应返回如表4.3所示的数据（“Orders”列未完全显示）。

表 4.3 来自JSON键的值（层次结构的最高级别）

Customer	Order date	Orders
Park Inn	2023-08-11	[{"Delivery date":"2023-08-14","Orders by day":[{"Baked good type":"English Muffin","Quantity":30}...

当我们从JSON结构中选择一个键时，返回值的数据类型是VARIANT。为了将值转换为相应的数据类型，我们必须显式进行转换。在列出4.2中的查询中，我们使用了:: Snowflake操作符，将“Customer”键的数据类型转换为varchar，并将“Order date”键转换为date。我们没有转换“Orders”键的值，因为该值包含我们将在下一步中解析的层次结构。

错误处理版本的数据类型转换函数

源数据并不总是以正确的格式提供，这可能导致数据摄取失败。例如，如果“Order date”列不包含有效日期，则::date操作将抛出错误，摄取将无法成功。为了避免数据类型转换过程中出现问题，Snowflake提供了错误处理版本的数据类型转换函数，如TRY_TO_DATE、TRY_TO_NUMBER等。每个函数都尝试将数据转换为所需的数据类型，但如果数据不匹配该数据类型，函数将返回NULL值，而不是错误。

有关错误处理版本的数据类型转换函数的更多信息，请参阅Snowflake文档：mng.bz/mRe0。

“Orders”键的值包含一个数组，我们通过它被方括号[]包围可以识别。在方括号内，有两个键，分别是“Delivery date”和“Orders by day”（参见图4.2，该图展示了JSON文档的完整结构）。我们希望将这两个键扁平化为单独的行，以便将其存储到关系型表中。为此，我们将使用LATERAL FLATTEN语法。

Snowflake提供了FLATTEN函数，用于对VARIANT值进行操作。该函数为VARIANT值中的每个对象返回一行。LATERAL修饰符将FLATTEN函数产生的数据与对象外部的列连接。在我们的示例中，我们将扁平化“Orders”键，并将其与已经从JSON结构中提取的CUSTOMER和ORDER_DATE列连接。

当我们使用LATERAL FLATTEN语法时，该函数的输出值是一个名为VALUE的列。我们可以引用此列，以提取嵌套在“Orders”键值中的“Delivery date”和“Orders by day”键的值，并将它们扁平化。

我们可以执行以下查询，将“Orders”键扁平化为单独的行，每行包含“Orders by day”和“Delivery date”键。

列出 4.3 从层次结构第二级的键选择值

select 
  customer_orders:"Customer"::varchar as customer, 
  customer_orders:"Order date"::date as order_date, 
  value:"Delivery date"::date as delivery_date,
  value:"Orders by day"
from ORDERS_PARK_INN_RAW_STG,
lateral flatten (input => customer_orders:"Orders");

此查询的输出返回如表4.4所示的数据（“Orders by day”列未完全显示）。查询中的LATERAL FLATTEN语法产生了两行，因为JSON文件包含两个不同的交付日期的订单。

表 4.4 来自JSON键的值（层次结构第二级）

Customer	Order date	Delivery date	Orders by day
Park Inn	2023-08-11	2023-08-14	[{"Baked good type":"English Muffin","Quantity":30}...
Park Inn	2023-08-11	2023-08-15	[{"Baked good type":"English Muffin","Quantity":26}...

“Orders by day”键的值包含一个数组，这意味着我们必须在查询中使用另一个LATERAL FLATTEN函数，将这些订单扁平化为关系型表。第二个LATERAL FLATTEN函数将再次返回一个VALUE列，类似于第一个。为了区分它们，我们将为每个LATERAL FLATTEN函数使用别名（CO和DO）。

“Orders by day”键包含名为“Baked good type”和“Quantity”的键列表。我们将使用以下查询从这些键中选择值。

列出 4.4 从层次结构第三级的JSON键选择值

select 
  customer_orders:"Customer"::varchar as customer, 
  customer_orders:"Order date"::date as order_date, 
  CO.value:"Delivery date"::date as delivery_date,
  DO.value:"Baked good type"::varchar as baked_good_type,
  DO.value:"Quantity"::number as quantity
from ORDERS_PARK_INN_RAW_STG,
lateral flatten (input => customer_orders:"Orders") CO,     #1
lateral flatten (input => CO.value:"Orders by day") DO;     #2
#1 第一个LATERAL FLATTEN
#2 第二个LATERAL FLATTEN

此查询的输出返回如表4.5所示的数据。我们可以看到，JSON数据现在已被扁平化至层次结构的最底层，不再有嵌套的对象或列表。

表 4.5 来自JSON键的值（层次结构第三级）

Customer	Order date	Delivery date	Baked good type	Quantity
Park Inn	2023-08-11	2023-08-14	English muffin	30
Park Inn	2023-08-11	2023-08-14	Whole wheat loaf	6
Park Inn	2023-08-11	2023-08-14	White loaf	4
Park Inn	2023-08-11	2023-08-14	Bagel	25
Park Inn	2023-08-11	2023-08-14	Croissant	36
Park Inn	2023-08-11	2023-08-15	English muffin	26
Park Inn	2023-08-11	2023-08-15	Whole wheat loaf	4
Park Inn	2023-08-11	2023-08-15	Bagel	22
Park Inn	2023-08-11	2023-08-15	Croissant	30

现在我们理解了将数据从JSON格式转换为关系结构的查询，我们可以使用此查询将数据加载到Snowflake表中。为了减少复杂性并节省存储成本，我们不直接将数据从ORDERS_PARK_INN_RAW_STG原始暂存表复制到关系型暂存表中，而是可以创建一个视图，将原始JSON数据以关系格式表示。

让我们使用列出4.4中的语法和包含源文件名和加载时间戳的列来创建一个名为ORDERS_PARK_INN_STG的视图：

create view ORDERS_PARK_INN_STG as
select                                               #1
  customer_orders:"Customer"::varchar as customer, 
  customer_orders:"Order date"::date as order_date, 
  CO.value:"Delivery date"::date as delivery_date,
  DO.value:"Baked good type"::varchar as baked_good_type,
  DO.value:"Quantity"::number as quantity,
  source_file_name,
  load_ts
from ORDERS_PARK_INN_RAW_STG,
lateral flatten (input => customer_orders:"Orders") CO,
lateral flatten (input => CO.value:"Orders by day") DO;
#1 来自列出4.4的查询

这个视图表示了从JSON文件中扁平化为关系格式的暂存数据。

克服16MB数据大小限制

存储在Snowflake表中VARIANT数据类型的数据的大小限制为16 MB。当文件大于16 MB时，我们无法直接将半结构化数据加载到VARIANT数据类型中。

解决这个限制的一种方法是要求数据提供者创建结构化文件，使每个JSON文档包含一个具有相同结构的多个对象数组。在这种情况下，我们可以在COPY命令中使用STRIP_OUTER_ARRAY关键字。该命令将数组中的每个对象加载到目标表中的单独一行。

有关更多信息，请参阅Snowflake文档：mng.bz/5OBq。

4.3 使用存储过程封装转换

让我们总结一下迄今为止我们所实现的暂存表和视图。三个管道填充了三个暂存表或视图：

• 在第二章，我们创建了ORDERS_STG暂存表，该表位于ORDERS架构中，包含面包店手动上传到内部阶段的CSV文件中的数据。
• 在第三章，我们创建了ORDERS_BISTRO_STG表，该表位于EXTERNAL_ORDERS架构中，包含存储在餐馆云存储中的CSV文件数据。
• 在本章，我们创建了ORDERS_PARK_INN_STG视图，该视图位于EXTERNAL_JSON_ORDERS架构中，包含存储在酒店云存储中的JSON文件数据。

这三种暂存表或视图具有相同的结构，包括以下列：customer、order date、delivery date、baked goods type、quantity、filename和load timestamp。

一旦数据摄取到暂存表中，我们就可以继续转换数据，以便下游使用者能够使用它。面包店经理有一个需求，他希望知道未来几天每种类型的烘焙食品的订购数量。这些信息对于面包店的生产原材料和员工安排计划至关重要。

为了支持面包店经理的需求，我们将向数据管道中添加额外的步骤，执行以下任务：

• 将所有暂存表和视图中的数据合并为一个数据结构
• 将合并后的暂存数据加载到目标表中
• 按天和烘焙食品类型汇总数据

我们将首先在BAKERY_DB数据库中创建一个名为TRANSFORM的新架构，包含所有转换对象。要创建此架构，我们可以执行以下命令：

use database BAKERY_DB;
create schema TRANSFORM;
use schema TRANSFORM;

然后，使用TRANSFORM架构，我们将创建一个名为ORDERS_COMBINED_STG的视图，该视图通过执行以下SQL语句来合并所有暂存表和视图：

create view ORDERS_COMBINED_STG as
select customer, order_date, delivery_date, 
  baked_good_type, quantity, source_file_name, load_ts
from bakery_db.orders.ORDERS_STG
union all
select customer, order_date, delivery_date, 
  baked_good_type, quantity, source_file_name, load_ts
from bakery_db.external_orders.ORDERS_BISTRO_STG
union all
select customer, order_date, delivery_date, 
  baked_good_type, quantity, source_file_name, load_ts
from bakery_db.external_json_orders.ORDERS_PARK_INN_STG;

提示
如果你在第二章和第三章中已经跟着做了，你应该已经创建了ORDERS_STG和ORDERS_BISTRO_STG暂存表。如果跳过了之前章节的任何练习，你可以从此处继续，只需修改ORDERS_COMBINED_STG视图，只包含你自己的暂存表。

如同第二章一样，我们将创建一个目标表来存储历史订单数据。每当我们收到来自面包店的CSV文件或餐馆或酒店云存储中新文件时，我们将把这些文件加载到相应的暂存表中。然后，我们将把来自合并暂存表的数据合并到目标表中。

我们将使用以下命令创建CUSTOMER_ORDERS_COMBINED目标表：

use database BAKERY_DB;
use schema TRANSFORM;
create or replace table CUSTOMER_ORDERS_COMBINED (
  customer varchar,
  order_date date,
  delivery_date date,
  baked_good_type varchar,
  quantity number,
  source_file_name varchar,
  load_ts timestamp
);

然后，我们将把来自暂存表的数据合并到目标表中。像第二章一样，我们将合并数据，以确保每个客户、每种烘焙食品类型和每个交付日期只有一行，并且目标表中存储的是最新的数量值。

我们将执行以下查询将来自ORDERS_COMBINED_STG暂存数据的数据合并到CUSTOMER_ORDERS_COMBINED目标表中：

列出 4.5 将客户订单从暂存表合并到目标表

merge into CUSTOMER_ORDERS_COMBINED tgt
using ORDERS_COMBINED_STG as src
on src.customer = tgt.customer 
  and src.delivery_date = tgt.delivery_date 
  and src.baked_good_type = tgt.baked_good_type
when matched then 
  update set tgt.quantity = src.quantity, 
    tgt.source_file_name = src.source_file_name, 
    tgt.load_ts = current_timestamp()
when not matched then
  insert (customer, order_date, delivery_date, 
    baked_good_type, quantity, source_file_name, load_ts)
  values(src.customer, src.order_date, src.delivery_date,
    src.baked_good_type, src.quantity, src.source_file_name,
    current_timestamp());

每次新数据到达外部阶段时，数据工程师可以构建一个存储过程来封装这个逻辑，而不必每次执行MERGE语句将数据从暂存表加载到目标表中。这个存储过程可以作为一个单独的过程调用，通过任务或其他编排机制执行。此外，存储过程还可以包含编程逻辑，例如分支和循环、异常处理和日志记录。

我们可以在Snowflake中使用任何支持的编程语言创建存储过程，包括SQL、Snowflake Scripting、JavaScript、Java、Python或Scala。请注意，Java、Python和Scala存储过程通过Snowpark API提供，在第六章中会有讨论。

在本章中，我们将使用Snowflake Scripting创建一个名为LOAD_CUSTOMER_ORDERS的存储过程，用于将数据从暂存表加载到目标表中。我们将描述Snowflake Scripting语法，仅限于构建存储过程所需的内容。

注意
有关如何在Snowflake Scripting中创建存储过程的更多信息，请参阅Snowflake文档：mng.bz/6Yd5。

4.3.1 创建一个基本的存储过程

让我们从一个基本的存储过程开始，稍后可以根据需要添加更多功能。创建一个名为LOAD_CUSTOMER_ORDERS的存储过程，该过程执行列出4.5中的MERGE语句，包含以下组件：

• CREATE PROCEDURE命令，后跟存储过程的名称和参数列表（如果使用参数的话；在我们的示例中，我们没有使用任何参数）
• RETURNS子句，定义返回的数据类型（我们将返回VARCHAR类型）
• LANGUAGE子句，定义存储过程的编程语言（在我们的例子中是SQL，即Snowflake Scripting）
• 存储过程的主体，包裹在$$符号中
• BEGIN和END语句，包裹存储过程将执行的查询

创建此存储过程的代码如下所示。

列出 4.6 创建基本的存储过程

use database BAKERY_DB;
use schema TRANSFORM;
create procedure LOAD_CUSTOMER_ORDERS()
returns varchar
language sql
as
$$
begin
  merge into CUSTOMER_ORDERS_COMBINED tgt...     #1
end;
$$
;
#1 来自列出4.5的查询

存储过程创建完成后，我们可以使用CALL命令执行它：

call LOAD_CUSTOMER_ORDERS();

为了验证存储过程是否正确执行，我们可以从CUSTOMER_ORDERS_COMBINED目标表中选择数据，查看数据是否已加载，执行以下SQL语句：

select * from CUSTOMER_ORDERS_COMBINED;

现在，从CSV和JSON文件摄取到暂存表的数据应该已经可以在这个目标表中找到。

4.3.2 在存储过程中返回一个值

我们刚刚创建的基本存储过程没有返回任何值。通常，编写存储过程时，返回一个值给调用者会更方便。返回值可以包含与执行存储过程相关的信息，比如受影响的行数。

在我们的存储过程中，我们可以返回一条文本消息，如“Load completed”。我们还可以通过添加SQL命令处理的记录数来丰富这个消息。Snowflake跟踪一个名为SQLROWCOUNT的内置全局变量，存储最后执行的查询所处理的受影响行数。我们可以在RETURN语句中使用这个变量，以构建如以下列出的返回值。

列出 4.7 构建RETURN语句

return 'Load completed. ' || SQLROWCOUNT || ' rows affected.';

在执行MERGE语句后，我们可以将如列出4.7所示的RETURN语句添加到存储过程。修改后的存储过程创建代码如下所示。

列出 4.8 创建一个返回值的存储过程

use database BAKERY_DB;
use schema TRANSFORM;
create or replace procedure LOAD_CUSTOMER_ORDERS()
returns varchar
language sql
as
$$
begin
  merge into CUSTOMER_ORDERS_COMBINED tgt...                    #1
  return 'Load completed. ' || SQLROWCOUNT || ' rows affected.';     #2
end;
$$
;
#1 来自列出4.5的查询
#2 来自列出4.7的RETURN语句

我们可以再次使用CALL命令执行存储过程：

call LOAD_CUSTOMER_ORDERS();

由于存储过程执行了一个MERGE语句，它既插入又更新了目标表中的行，因此受影响的行数等于目标表中的行数。例如，返回值可能是：

Load completed. 131 rows affected.

现在我们已经有了一个基本的存储过程，让我们添加异常处理。

4.3.3 在存储过程中实现异常处理

异常处理对于编程至关重要，因为错误、异常或意外结果是不可避免的。我们通过编写异常处理代码来捕获这些错误，并执行补救或通知操作。

在Snowflake Scripting中，我们通过添加EXCEPTION块来处理异常。如果在执行存储过程时没有异常，代码会在不执行EXCEPTION块的情况下完成。但是，如果发生错误，代码会从失败点恢复并进入EXCEPTION块。在EXCEPTION块中，我们可以执行诸如回滚事务等操作。我们还可以构建一个消息，该消息由EXCEPTION块返回。消息可以包括内置全局变量SQLERRM，它存储错误信息。

EXCEPTION块可以根据不同类型的异常执行不同的操作。这些异常可以是Snowflake内置的异常，也可以是自定义异常。我们使用WHEN子句来处理每个异常。

注意
有关异常处理和定义自定义异常的更多信息，请参阅Snowflake文档：mng.bz/o0Oj。

让我们通过添加一个EXCEPTION块重新创建LOAD_CUSTOMER_ORDERS存储过程，该块将使用RETURN语句返回一个错误消息。在WHEN子句中，我们不处理任何特定的异常；相反，我们将使用OTHER关键字，它处理所有其他异常。重新创建存储过程的代码如下所示。

列出 4.9 存储过程中的异常处理

use database BAKERY_DB;
use schema TRANSFORM;
create or replace procedure LOAD_CUSTOMER_ORDERS()
returns varchar
language sql
as
$$
begin
  merge into CUSTOMER_ORDERS_COMBINED tgt...            #1
  return 'Load completed. ' || SQLROWCOUNT || ' rows affected.';
exception                                                     #2
  when other then
    return 'Load failed with error message: ' || SQLERRM;
end;
$$
;
#1 来自列出4.5的查询
#2 添加了异常处理块

在这个例子中，EXCEPTION块捕获了所有未处理的异常并返回错误消息。SQLERRM会存储并显示具体的错误信息，帮助开发者了解问题所在。

4.4 在存储过程中添加日志记录

当数据管道按计划执行时——例如，每晚一次——数据工程师通常不会每次都进行监控以检查是否没有错误。相反，数据工程师通常会维护日志信息，第二天或定期查看日志表，回顾执行状态。

数据管道可以在发生故障或意外结果时发送通知；这将在第13章中详细描述。目前，我们仅在存储过程中添加日志记录，以便我们可以查看所有先前执行的状态。

添加日志记录到存储过程的一种方法是创建一个自定义日志表，位于我们选择的架构中。这个日志表可能包含诸如执行时间戳、存储过程名称、参数值（如果使用）、调用者的用户名、结果状态（成功或失败）、失败时的错误信息等列。然后，我们会向存储过程添加INSERT语句，将日志数据插入到自定义日志表中。

另一种为存储过程添加日志记录的方法是使用Snowflake的内置日志记录功能。此功能允许您通过捕获存储过程执行期间的日志消息来记录存储过程的活动。这些消息存储在一个事件表中，我们可以使用SQL语句查询该表。

表 4.6 自定义日志记录与Snowflake内置日志记录的比较

日志类型	优点	缺点
自定义	灵活性高，可以定义需要记录的具体信息及记录时间	必须进行自定义开发
Snowflake内置	无需自定义开发；进行一些初始设置后即可使用Snowflake内置的日志记录功能	跨账户共享；因此必须设置访问控制，允许开发人员访问他们的日志消息

在本章中，我们将使用Snowflake的内置日志记录功能。

设置存储过程日志记录的步骤

1. 创建一个事件表。Snowflake中的此类型表包含一组预定义的列。
2. 将事件表与账户关联。每个Snowflake账户只能有一个活动的事件表。
3. 授予数据工程师角色所需的权限，以允许他们设置日志级别。
4. 在存储过程中调用日志记录功能。
5. 使用SQL查询语句查看已记录的数据。

注意
有关Snowflake日志记录和跟踪的更多信息，请参阅文档：mng.bz/n0p8。

设置日志记录的第一步是创建一个事件表。为了这个练习，我们将在BAKERY_DB数据库中的现有架构中创建一个日志表。

注意
在实际场景中，我们通常会在一个专门的数据库和架构中创建一个账户级事件表，该表存储所有应用程序的账户级对象。

我们将继续使用SYSADMIN角色和TRANSFORM架构，创建一个名为BAKERY_EVENTS的事件表，执行以下命令：

use role SYSADMIN;
use schema TRANSFORM;
create event table BAKERY_EVENTS;

创建事件表后，我们将把该表与账户关联。我们必须使用ACCOUNTADMIN角色执行此操作，并提供完全限定的表名。我们可以通过执行以下命令来完成：

use role ACCOUNTADMIN;
alter account set event_table = BAKERY_DB.TRANSFORM.BAKERY_EVENTS;

接下来，我们必须授予用于处理面包店对象的SYSADMIN角色所需的权限，以允许其更改存储过程的日志级别。为此，我们将授予MODIFY LOG LEVEL ON ACCOUNT权限，允许被授予者设置账户中任何数据库、架构、存储过程或函数的日志级别。我们仍然使用ACCOUNTADMIN角色，可以通过执行以下命令来授予权限：

grant modify log level on account to role SYSADMIN;

现在，我们可以切换回SYSADMIN角色，继续在存储过程中设置日志记录。我们必须首先设置存储过程的所需日志级别。默认的日志级别是OFF，这意味着不会记录任何内容。我们可以选择记录以下任一级别，从最严格的日志级别开始（例如，FATAL日志级别将仅记录致命错误）到最不严格的级别：FATAL、ERROR、WARN、INFO和DEBUG。每个日志级别记录该级别及其以上的消息。

在本练习中，我们将记录所有级别的消息。因此，我们将通过执行以下命令，将LOAD_CUSTOMER_ORDERS存储过程的日志级别设置为最不严格的DEBUG级别：

use role SYSADMIN;
alter procedure LOAD_CUSTOMER_ORDERS() set log_level = DEBUG;

接下来，我们将使用SYSTEM$LOG_DEBUG函数将日志记录添加到LOAD_CUSTOMER_ORDERS存储过程中，该函数以DEBUG级别记录消息。重建存储过程并添加日志的代码如下：

use database BAKERY_DB;
use schema TRANSFORM;
create or replace procedure LOAD_CUSTOMER_ORDERS()
returns varchar
language sql
as
$$
begin
  SYSTEM$LOG_DEBUG('LOAD_CUSTOMER_ORDERS begin ');      #1
  merge into CUSTOMER_ORDERS_COMBINED tgt...      #2
  return 'Load completed. ' || SQLROWCOUNT || ' rows affected.';
exception
  when other then
    return 'Load failed with error message: ' || SQLERRM;
end;
$$
;
#1 添加日志记录
#2 来自列出4.5的查询

我们可以使用以下CALL命令执行存储过程：

call LOAD_CUSTOMER_ORDERS();

提示
由于Snowflake的日志记录功能在账户级别工作，因此日志消息通常需要一些时间才会出现在事件表中。在检查日志信息是否可见之前，您需要等待几分钟。

要查询事件表，我们可以执行以下SQL语句：

select * 
from BAKERY_EVENTS
order by timestamp desc;

我们应该会看到此语句输出的已记录消息。

有关日志记录的更多信息，请参阅Snowflake文档：mng.bz/vJmq。

4.5 构建健壮的数据管道

让我们回顾一下我们所构建的内容，并将所有部分结合起来，形成一个面包店将定期执行的数据管道。该数据管道从CSV文件和JSON文件摄取数据到暂存表。然后，它将数据插入到目标表中。最后一步是按烘焙食品类型和日期汇总数据。

让我们创建一个名为SUMMARY_ORDERS的表来存储汇总数据，使用以下命令：

use database BAKERY_DB;
use schema TRANSFORM;
create table SUMMARY_ORDERS (
  delivery_date date,
  baked_good_type varchar,
  total_quantity number
);

接下来，我们将按交付日期和烘焙食品类型汇总CUSTOMER_ORDERS_COMBINED表中的数据，并将汇总的数据插入到SUMMARY_ORDERS表中。由于数据管道按计划执行——例如，每晚执行一次——我们必须避免数据重复。在插入汇总数据之前，我们必须使用TRUNCATE命令清空表。为了将汇总数据插入目标表，我们将执行以下命令：

列出 4.10 清空表并插入汇总数据

truncate table SUMMARY_ORDERS;
insert into SUMMARY_ORDERS(delivery_date, baked_good_type, total_quantity)
  select delivery_date, baked_good_type, sum(quantity) as total_quantity
  from CUSTOMER_ORDERS_COMBINED
  group by all;

与之前一样，在填充暂存表时，我们将创建一个名为LOAD_CUSTOMER_SUMMARY_ORDERS()的存储过程，将列出4.10中的SQL语句封装起来。我们将执行以下命令来创建存储过程：

use database BAKERY_DB;
use schema TRANSFORM;
create or replace procedure LOAD_CUSTOMER_SUMMARY_ORDERS()
returns varchar
language sql
as
$$
begin
  SYSTEM$LOG_DEBUG('LOAD_CUSTOMER_SUMMARY_ORDERS begin ');

  truncate table SUMMARY_ORDERS;
  insert into SUMMARY_ORDERS...                             #1
  return 'Load completed. ' || SQLROWCOUNT || ' rows inserted.';
exception
  when other then
    return 'Load failed with error message: ' || SQLERRM;
end;
$$
;
#1 来自列出4.10的插入语句

我们可以使用以下CALL命令执行存储过程：

call LOAD_CUSTOMER_SUMMARY_ORDERS();

面包店经理可以使用此汇总表中的数据来确定客户在接下来的几天里每种类型的烘焙食品订购了多少。我们可以执行以下查询来显示这些信息：

select * 
from SUMMARY_ORDERS 
order by delivery_date desc;

该查询的输出应该列出每个日期、每种烘焙食品类型以及每种烘焙食品类型在每一天的订购数量。

图 4.3 显示了从暂存表到最终汇总表的数据流。

在构建数据管道时，数据工程师必须在管道开始在生产环境中按计划执行时，仔细考虑如何使其尽可能健壮。一个健壮的数据管道是可靠的，且设计为能够应对意外事件并在需要时重新启动。同时，它还确保数据的质量和一致性。

在设计数据管道时，数据工程师应考虑的不仅仅是正面场景——即管道执行无错误的情况。数据工程师还必须考虑可能发生的负面场景，这些场景可能因各种原因发生，例如连接中断、源系统故障、意外的数据类型、数据不一致、长时间运行查询的超时、访问控制违规等。

在设计数据管道时，数据工程师必须考虑在这些负面场景下的应对措施。例如，他们必须决定在故障原因解决后，是否重新启动整个数据管道或从故障点重新启动。同时，他们还必须确保在这种不利情况下数据不会被重复或丢失。

设计良好且健壮的数据管道：

• 对失败作出响应而不影响数据完整性
• 提供日志记录和监控功能，使执行状态可见
• 在发生故障或意外事件时发出警报
• 在可行的情况下执行数据验证，例如验证主键的唯一性
• 以模块化的方式构建，允许代码重用
• 在部署前进行多种场景的测试
• 具有文档化，便于维护

在本章中，我们在设计数据管道时已经考虑了其中一些方面。我们使用存储过程作为代码模块化的示例。我们实现了日志记录功能，使执行状态可见。我们使用了SQL构造（如MERGE语句）来确保主键的唯一性。我们在插入汇总数据之前清空了汇总表，以防止数据重复。正是考虑到这些因素，我们可以在数据管道失败时重新启动它，而不会影响数据的完整性。

总结

半结构化数据包含通常以键值对的形式组织的数据，这些键值对通常按层次结构组织。与可以存储在关系型表中的结构化数据不同，半结构化数据不需要预定义的架构，并且可以存储在Snowflake中的VARIANT数据类型中。

JSON是一个流行的半结构化数据格式，用于以人类可读的格式交换数据。在本章中，我们演示了酒店如何以JSON格式向面包店提供订单信息。然后，我们可以从对象存储中将这些数据摄取到Snowflake中。

JSON数据可以存储在Snowflake中的VARIANT数据类型中，大小限制为16 MB。

Snowflake提供了查询半结构化数据（如JSON）的语法。要查看每个键的值，我们可以使用: 操作符从JSON数据中选择键值。

当我们从JSON结构中选择一个键时，返回值的数据类型为VARIANT。我们必须使用数据类型转换函数或操作符将其转换为相应的数据类型。

Snowflake提供了FLATTEN函数，它为JSON文档中的每个对象返回一行。LATERAL修饰符将FLATTEN函数产生的数据与对象外部的列连接。这种语法允许将包含多个对象的JSON对象（例如，某一特定日期的订单列表）表示为关系型表中的行。

我们可以在Snowflake中编写存储过程来封装数据转换逻辑。这些存储过程还可以包括编程逻辑，如分支和循环、异常处理和日志记录。我们可以使用任何支持的编程语言（包括Snowflake Scripting、JavaScript、Java、Python或Scala）来创建存储过程。

存储过程可以返回一个值给调用者。返回值可以包含指示存储过程是否成功执行的信息，以及与存储过程执行相关的其他信息，例如SQLROWCOUNT内置全局变量，它存储最后执行的查询处理的受影响行数。

异常处理对编程至关重要，因为错误、异常或意外结果是不可避免的。我们通过编写异常处理代码来捕获这些错误，并执行补救或通知操作。在Snowflake Scripting中，我们通过添加EXCEPTION块来处理异常。

数据工程师通常会维护日志信息，以便第二天或定期检查日志表，以回顾数据管道的执行状态。数据工程师可以使用Snowflake的内置日志记录功能，它允许他们通过捕获存储过程执行期间的日志消息来记录存储过程的活动。

日志消息存储在一个事件表中，这是一种具有预定义列集的表类型。事件表与账户相关联。每个Snowflake账户只能有一个活动的事件表。

Snowflake函数，如SYSTEM$LOG_DEBUG，可以为存储过程添加日志记录功能。

在构建数据管道时，数据工程师必须仔细考虑如何使管道在生产环境中按计划执行时尽可能健壮。一个健壮的数据管道是可靠的，设计上能够应对意外事件并在需要时进行重启。同时，它还确保数据质量和一致性。