Azure 数据工程权威指南（二）
八、在 SQL 数据库中为管道活动指标构建自定义日志
在前面的章节中，我演示了如何使用数据工厂将数据从 ADLS Gen2 加载到 Synapse Analytics 专用的 SQL 池中。在这一章中，我将演示如何利用已经建立的管道来实现一个过程，该过程用于跟踪运行和保存数据的管道的日志活动。
Azure Data Factory 是一个健壮的基于云的 ELT 工具，能够适应记录管道审计数据的多种场景，包括现成的服务，如日志分析、Azure Monitor 等，以及提取管道指标并将它们传递给另一个自定义流程的更多自定义方法。在本章中，我将向您展示如何实现这些可能的自定义日志记录选项中的三个，它们是:
-
选项 1–创建存储过程活动:使用 ADF 更新静态管道参数表中的管道状态和日期时间列可以通过使用存储过程活动来实现。
-
选项 2–在 Data Lake Storage Gen2 中创建 CSV 日志文件:为创建的每个拼花文件生成一个元数据 CSV 文件，并将日志作为 CSV 文件存储在 ADLS Gen2 中的分层文件夹中，这可以使用复制数据活动来实现。
-
选项 3——在 Azure SQL 数据库中创建日志表:在 Azure SQL 数据库中创建管道日志表，并将管道活动作为记录存储在表中，可以通过使用 Copy data activity 来实现。
图 8-1 说明了这三个选项，并显示了从复制表活动到创建各种记录方法的数据流的可视化表示。
图 8-1
用于记录自定义管道数据的 ADF 选项
选项 1:创建一个存储过程活动
存储过程活动将用于调用 Synapse Analytics 专用 SQL 池中的存储过程。
对于这个场景，您可能希望将您的pipeline_status和pipeline_date细节作为列保存在您的adf_db.dbo.pipeline_parameter表中，而不是拥有一个单独的日志表。这种方法的缺点是，它不会保留历史日志数据，而只是根据对传入文件在pipeline_parameter表中的记录的查找来更新原始pipeline_parameter表中的值。这提供了一种快速但不一定可靠的方法来查看管道参数表中所有项目的状态和加载日期。这种方法的具体用例可能是根据传入的文件夹和文件名以及时间戳来记录最大文件夹日期。
首先，在 ForEach 循环活动中添加一个存储过程活动，如图 8-2 所示，以确保流程使用存储过程迭代并记录每个表。请注意绿色箭头和线连接器，它指示存储过程必须在复制数据活动成功时运行。
图 8-2
使用存储过程记录数据的 ADF 选项 1
接下来，将以下存储过程添加到管道参数表所在的数据库中。此过程只是在管道参数表中查找目标表名，并在复制数据活动成功后更新每个表的状态和日期时间。类似地，通过在复制活动完成后将存储过程链接到失败约束，您可以轻松地添加一个新的存储过程来处理错误:
SET quoted_identifier ON
go
CREATE PROCEDURE [dbo].[Sql2adls_data_files_loaded] @dst_name NVARCHAR(500)
AS
SET nocount ON
-- turns off messages sent back to client after DML is run, keep this here
DECLARE @Currentday DATETIME = Getdate();
UPDATE [dbo].[pipeline_parameter]
SET pipeline_status = 'success',
pipeline_date = @Currentday
WHERE dst_name = @dst_name;
go
创建存储过程后，确认它已在相应的数据库中创建。注意图 8-3 中的确认，这是 SSMS 境内的屏幕截图。
图 8-3
显示 ADF 选项 1 使用的存储过程的 SSMS 视图
接下来，返回到数据工厂管道并配置存储过程活动。在“存储过程”选项卡中，选择刚刚创建的存储过程。还要添加一个新的存储过程参数，该参数引用在复制活动中配置的目的地名称，如图 8-4 所示。
图 8-4
ADF 存储过程详细信息和参数
在保存、发布和运行管道之后，注意到pipeline_date和pipeline_status列已经作为 ADF 存储过程活动的结果被更新，如图 8-5 中的pipeline_parameter表视图所示。这是一个轻量级的测试模式，在一个集中的位置为您提供每个表的状态和加载日期的详细信息。我注意到 ADF 并不总是提供与有问题的表或列相关的健壮细节。
图 8-5
ADF 存储过程中更新的 pipeline_date 和 pipeline_status 列的 SSMS 视图
选项 2:在数据湖存储二代中创建一个 CSV 日志文件
由于您的复制表活动正在将快速拼花文件生成到分层的 ADLS Gen2 文件夹中，您可能还希望创建一个 CSV 文件，其中包含您的 ADLS Gen2 帐户中每个拼花文件的管道运行详细信息。对于这个场景，您可以设置一个数据工厂事件网格触发器来监听元数据文件，然后触发一个流程来转换源表并将其加载到一个管理区域中，从而在某种程度上复制一个接近实时的 ELT 流程。
开始设计 ADF 管道，通过添加用于创建日志文件的复制活动并将其连接到 Copy-Table 活动，在您的 ADLS Gen2 帐户中创建 CSV 日志文件，如图 8-6 所示。与上一个遍历每个表的过程类似，该过程将在每个表的元数据文件夹中生成一个 CSV 扩展元数据文件。
图 8-6
ADF 选项 2 用于记录数据，使用复制数据活动创建 CSV 文件
若要配置源数据集，请选择源本地 SQL Server。接下来，添加如图 8-7 所示的查询作为源查询。请注意，该查询将包含管道活动、复制表活动和用户定义参数的组合。
图 8-7
ADF 选项 2 用于提取管道活动指标的源查询
回想一下前面章节中的注释，这些注释指出将源查询作为代码嵌入到 ADF 管道中仅仅是为了直观演示的目的。将源查询作为存储过程添加，然后调用存储过程，这总是一个更好、更高效的过程。这将有助于在一个集中的位置维护代码。
下面是图 8-7 中使用的相应源代码:
SELECT '@{pipeline().DataFactory}' AS datafactory_name,
'@{pipeline().Pipeline}' AS pipeline_name,
'@{pipeline().RunId}' AS runid,
'@{item().src_name}' AS source,
'@{item().dst_name}' AS destination,
'@{pipeline().TriggerType}' AS triggertype,
'@{pipeline().TriggerId}' AS triggerid,
'@{pipeline().TriggerName}' AS triggername,
'@{pipeline().TriggerTime}' AS triggertime,
'@{activity('copy-TABLE').output.rowsCopied}' AS rowscopied,
'@{activity('copy-TABLE').output.rowsRead}' AS rowsread,
'@{activity('copy-TABLE').output.usedParallelCopies}' AS no_parallelcopies,
'@{activity('copy-TABLE').output.copyDuration}' AS copyduration_in_secs,
'@{activity('copy-TABLE').output.effectiveIntegrationRuntime}' AS effectiveintegrationruntime,
'@{activity('copy-TABLE').output.executionDetails[0].source.type}' AS source_type,
'@{activity('copy-TABLE').output.executionDetails[0].sink.type}' AS sink_type,
'@{activity('copy-TABLE').output.executionDetails[0].status}' AS execution_status,
'@{activity('copy-TABLE').output.executionDetails[0].start}' AS copyactivity_start_time,
'@{utcnow()}' AS copyactivity_end_time,
'@{activity('copy-TABLE').output.executionDetails[0].detailedDurations.queuingDuration}' AS copyactivity_queuingduration_in_secs,
'@{activity('copy-TABLE').output.executionDetails[0].detailedDurations.timeToFirstByte}' AS copyactivity_timetofirstbyte_in_secs,
'@{activity('copy-TABLE').output.executionDetails[0].detailedDurations.transferDuration}' AS copyactivity_transferduration_in_secs
sink 将是一个 CSV 数据集，扩展名为 CSV，如图 8-8 所示。
图 8-8
CSV 的 ADF 接收器数据集
图 8-9 显示了用于 CSV 数据集的连接配置。
图 8-9
CSV 的 ADF 接收器数据集连接属性
以下参数化路径将确保在正确的文件夹结构中生成文件。下面是如图 8-9 所示的代码:
@{item().server_name}/@{item().src_db}/@{item().src_schema}/@{item().dst_name}/metadata/@{formatDateTime(utcnow(),'yyyy-MM-dd')}/@{item().dst_name}.csv
保存、发布和运行管道后，请注意元数据文件夹是如何在以下文件夹结构中创建的:
Server>database>schema>date>Destination_table location
图 8-10 显示了您在 ADSL Gen2 中看到的这个文件夹。
图 8-10
ADF 管道选项 2 生成的 ADLS Gen2 文件夹
打开元数据文件夹，注意管道运行的每一天都会创建 CSV 文件夹，如图 8-11 所示。
图 8-11
由 ADF 管道选项 2 创建的 ADLS Gen2 文件夹(按时间戳)
最后，请注意图 8-12 中的元数据。以该表的名称命名的 csv 文件已创建。
图 8-12
包含来自选项 2 的 ADF 管道指标的 ADLS Gen2 元数据文件
下载并打开文件，注意所有的查询结果都已经被填充到。csv 文件，如图 8-13 所示。
图 8-13
ADLS Gen2 元数据文件，包含选项 2 中 ADF 管道指标的详细信息
选项 3:在 Azure SQL 数据库中创建日志表
本章的最后一个场景是在参数表所在的数据库中创建一个日志表，然后将数据作为记录写入该表。对于该选项，首先添加一个连接到复制表活动的复制数据活动，如图 8-14 所示。
图 8-14
ADF 选项 3，用于使用写入日志表的复制数据活动记录数据
接下来，在 ADF_DB 数据库中创建下表。此表将存储和捕获管道和复制活动的详细信息:
SET ansi_nulls ON
go
SET quoted_identifier ON
go
CREATE TABLE [dbo].[pipeline_log]
(
[log_id] [INT] IDENTITY(1, 1) NOT NULL,
[parameter_id] [INT] NULL,
[datafactory_name] NVARCHAR NULL,
[pipeline_name] NVARCHAR NULL,
[runid] NVARCHAR NULL,
[source] NVARCHAR NULL,
[destination] NVARCHAR NULL,
[triggertype] NVARCHAR NULL,
[triggerid] NVARCHAR NULL,
[triggername] NVARCHAR NULL,
[triggertime] NVARCHAR NULL,
[rowscopied] NVARCHAR NULL,
[dataread] [INT] NULL,
[no_parallelcopies] [INT] NULL,
[copyduration_in_secs] NVARCHAR NULL,
[effectiveintegrationruntime] NVARCHAR NULL,
[source_type] NVARCHAR NULL,
[sink_type] NVARCHAR NULL,
[execution_status] NVARCHAR NULL,
[copyactivity_start_time] NVARCHAR NULL,
[copyactivity_end_time] NVARCHAR NULL,
[copyactivity_queuingduration_in_secs] NVARCHAR NULL,
[copyactivity_transferduration_in_secs] NVARCHAR NULL,
CONSTRAINT [PK_pipeline_log] PRIMARY KEY CLUSTERED ( [log_id] ASC )WITH (
statistics_norecompute = OFF, ignore_dup_key = OFF) ON [PRIMARY]
)
ON [PRIMARY]
go
与最后一个管道选项类似，将本地 SQL Server 配置为源，并将选项 2 中提供的查询代码用作源查询，如图 8-15 所示。
图 8-15
ADF 选项 3 用于提取管道活动指标的源查询
接收器将连接到之前创建的 SQL 数据库管道日志表。
在保存、发布和运行管道之后，注意管道复制活动记录已经被捕获到dbo.pipeline_log表中，如图 8-16 所示。
图 8-16
pipeline_log 表结果的 SSMS 视图，它将 ADF 选项 3 管道数据记录到 SQL DW 中
摘要
在本章中，我演示了如何使用自定义方法记录 ADF 管道数据，该方法从 ADF 管道中提取每个表或文件的指标，然后将结果写入pipeline_parameter表、pipeline_log表或 ADLS Gen2 中的 CSV 文件。此自定义日志记录流程可添加到多个 ADF 管道活动步骤中，以实现强大且可重复使用的日志记录和审计流程，该流程可在 SSMS 通过自定义 SQL 查询轻松查询，甚至可链接到 Power BI 仪表板和报告，以促进对日志记录数据的强大报告。在下一章，我将演示如何将 ADF 管道中的错误细节记录到 Azure SQL 表中。
九、在 SQL 数据库中捕获管道错误日志
在第八章 ?? 中，我讨论了各种捕获 Azure 数据工厂管道日志并将数据持久化到 SQL 表或 Azure 数据湖存储二代中的方法。尽管当管道活动成功时，捕获管道日志数据的过程是有价值的，但本章将介绍如何捕获 Azure 数据工厂管道错误并将其持久化到前面章节中创建的 ADF_DB 内的 SQL 表中。此外，我们将回顾我在前面章节中讨论过的管道参数流程，以展示pipeline_errors、pipeline_log和pipeline_parameter表之间的相互关系。
概括地说，这个过程需要的表格包括
-
管道参数
-
管道 _ 日志
-
管道 _ 错误
图 9-1 展示了这些表格是如何相互连接的。
图 9-1
描述管道错误、日志和参数表之间关系的图表
创建参数表
概括地说，我们在前几章中创建了一些pipeline_parameter表的变体。下面的脚本将创建一个更新版本的pipeline_parameter表，其中包含一些额外的列，并将列parameter_id作为主键。回想一下前面章节的练习，该表推动了元数据 ETL 方法:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
go
CREATE TABLE [dbo].[pipeline_parameter]
(
[parameter_id] [INT] IDENTITY(1, 1) NOT NULL,
[server_name] NVARCHAR NULL,
[src_type] NVARCHAR NULL,
[src_schema] NVARCHAR NULL,
[src_db] NVARCHAR NULL,
[src_name] NVARCHAR NULL,
[dst_type] NVARCHAR NULL,
[dst_name] NVARCHAR NULL,
[include_pipeline_flag] NVARCHAR NULL,
[partition_field] NVARCHAR NULL,
[process_type] NVARCHAR NULL,
[priority_lane] NVARCHAR NULL,
[pipeline_date] NVARCHAR NULL,
[pipeline_status] NVARCHAR NULL,
[load_synapse] NVARCHAR NULL,
[load_frequency] NVARCHAR NULL,
[dst_folder] NVARCHAR NULL,
[file_type] NVARCHAR NULL,
[lake_dst_folder] NVARCHAR NULL,
[spark_flag] NVARCHAR NULL,
[dst_schema] NVARCHAR NULL,
[distribution_type] NVARCHAR NULL,
[load_sqldw_etl_pipeline_date] [DATETIME] NULL,
[load_sqldw_etl_pipeline_status] NVARCHAR NULL,
[load_sqldw_curated_pipeline_date] [DATETIME] NULL,
[load_sqldw_curated_pipeline_status] NVARCHAR NULL,
[load_delta_pipeline_date] [DATETIME] NULL,
[load_delta_pipeline_status] NVARCHAR NULL,
PRIMARY KEY CLUSTERED ( [parameter_id] ASC )WITH (statistics_norecompute =
OFF, ignore_dup_key = OFF) ON [PRIMARY]
)
ON [PRIMARY]
go
创建日志表
下一个脚本将创建用于捕获数据工厂成功日志的pipeline_log表。在该表中，log_id列是主键，parameter_id列是外键，引用参数表中的parameter_id列:
SET ansi_nulls ON
go
SET quoted_identifier ON
go
CREATE TABLE [dbo].[pipeline_log]
(
[log_id] [INT] IDENTITY(1, 1) NOT NULL,
[parameter_id] [INT] NULL,
[datafactory_name] NVARCHAR NULL,
[pipeline_name] NVARCHAR NULL,
[runid] NVARCHAR NULL,
[source] NVARCHAR NULL,
[destination] NVARCHAR NULL,
[triggertype] NVARCHAR NULL,
[triggerid] NVARCHAR NULL,
[triggername] NVARCHAR NULL,
[triggertime] NVARCHAR NULL,
[rowscopied] NVARCHAR NULL,
[dataread] [INT] NULL,
[no_parallelcopies] [INT] NULL,
[copyduration_in_secs] NVARCHAR NULL,
[effectiveintegrationruntime] NVARCHAR NULL,
[source_type] NVARCHAR NULL,
[sink_type] NVARCHAR NULL,
[execution_status] NVARCHAR NULL,
[copyactivity_start_time] NVARCHAR NULL,
[copyactivity_end_time] NVARCHAR NULL,
[copyactivity_queuingduration_in_secs] NVARCHAR NULL,
[copyactivity_transferduration_in_secs] NVARCHAR NULL,
CONSTRAINT [PK_pipeline_log] PRIMARY KEY CLUSTERED ( [log_id] ASC )WITH (
statistics_norecompute = OFF, ignore_dup_key = OFF) ON [PRIMARY]
)
ON [PRIMARY]
go
ALTER TABLE [dbo].[pipeline_log]
WITH CHECK ADD FOREIGN KEY([parameter_id]) REFERENCES
[dbo].[pipeline_parameter] ([parameter_id]) ON UPDATE CASCADE
go
创建一个错误表
创建一个错误表需要执行下一个脚本，该脚本将创建一个pipeline_errors表，用于从失败的管道活动中捕获数据工厂错误细节。在该表中，列error_id是主键，列parameter_id是外键，引用来自pipeline_parameter表的列parameter_id:
SET ansi_nulls ON
go
SET quoted_identifier ON
go
CREATE TABLE [dbo].[pipeline_errors]
(
[error_id] [INT] IDENTITY(1, 1) NOT NULL,
[parameter_id] [INT] NULL,
[datafactory_name] NVARCHAR NULL,
[pipeline_name] NVARCHAR NULL,
[runid] NVARCHAR NULL,
[source] NVARCHAR NULL,
[destination] NVARCHAR NULL,
[triggertype] NVARCHAR NULL,
[triggerid] NVARCHAR NULL,
[triggername] NVARCHAR NULL,
[triggertime] NVARCHAR NULL,
[no_parallelcopies] [INT] NULL,
[copyduration_in_secs] NVARCHAR NULL,
[effectiveintegrationruntime] NVARCHAR NULL,
[source_type] NVARCHAR NULL,
[sink_type] NVARCHAR NULL,
[execution_status] NVARCHAR NULL,
[errordescription] NVARCHAR NULL,
[errorcode] NVARCHAR NULL,
[errorloggedtime] NVARCHAR NULL,
[failuretype] NVARCHAR NULL,
CONSTRAINT [PK_pipeline_error] PRIMARY KEY CLUSTERED ( [error_id] ASC )WITH
(statistics_norecompute = OFF, ignore_dup_key = OFF) ON [PRIMARY]
)
ON [PRIMARY]
textimage_on [PRIMARY]
go
ALTER TABLE [dbo].[pipeline_errors]
WITH CHECK ADD FOREIGN KEY([parameter_id]) REFERENCES
[dbo].[pipeline_parameter] ([parameter_id]) ON UPDATE CASCADE
go
创建一个存储过程来更新日志表
现在您已经准备好了所有必要的 SQL 表，通过使用下面的脚本开始创建一些必要的存储过程，这将创建一个存储过程来用成功的管道运行中的数据更新pipeline_log表。请注意，此存储过程将在运行时从数据工厂管道中调用:
SET ansi_nulls ON
go
SET quoted_identifier ON
go
CREATE PROCEDURE [dbo].[usp_updatelogtable] @datafactory_name
VARCHAR(250),
@pipeline_name
VARCHAR(250),
@runid
VARCHAR(250),
@source
VARCHAR(300),
@destination
VARCHAR(300),
@triggertype
VARCHAR(300),
@triggerid
VARCHAR(300),
@triggername
VARCHAR(300),
@triggertime
VARCHAR(500),
@rowscopied
VARCHAR(300),
@dataread
INT,
@no_parallelcopies
INT,
@copyduration_in_secs
VARCHAR(300),
@effectiveintegrationruntime
VARCHAR(300),
@source_type
VARCHAR(300),
@sink_type
VARCHAR(300),
@execution_status
VARCHAR(300),
@copyactivity_start_time
VARCHAR(500),
@copyactivity_end_time
VARCHAR(500),
@copyactivity_queuingduration_in_secs
VARCHAR(500),
@copyactivity_transferduration_in_secs VARCHAR(500)
AS
INSERT INTO [pipeline_log]
([datafactory_name],
[pipeline_name],
[runid],
[source],
[destination],
[triggertype],
[triggerid],
[triggername],
[triggertime],
[rowscopied],
[dataread],
[no_parallelcopies],
[copyduration_in_secs],
[effectiveintegrationruntime],
[source_type],
[sink_type],
[execution_status],
[copyactivity_start_time],
[copyactivity_end_time],
[copyactivity_queuingduration_in_secs],
[copyactivity_transferduration_in_secs])
VALUES ( @datafactory_name,
@pipeline_name,
@runid,
@source,
@destination,
@triggertype,
@triggerid,
@triggername,
@triggertime,
@rowscopied,
@dataread,
@no_parallelcopies,
@copyduration_in_secs,
@effectiveintegrationruntime,
@source_type,
@sink_type,
@execution_status,
@copyactivity_start_time,
@copyactivity_end_time,
@copyactivity_queuingduration_in_secs,
@copyactivity_transferduration_in_secs )
go
创建一个存储过程来更新错误表
接下来，运行下面的脚本，它将创建一个存储过程，用失败的管道运行中的详细错误数据更新pipeline_errors表。请注意，此存储过程将在运行时从数据工厂管道中调用:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_updateerrortable]
@datafactory_name nvarchar NULL,
@pipeline_name nvarchar NULL,
@runid nvarchar NULL,
@source nvarchar NULL,
@destination nvarchar NULL,
@triggertype nvarchar NULL,
@triggerid nvarchar NULL,
@triggername nvarchar NULL,
@triggertime nvarchar NULL,
@no_parallelcopies [int] NULL,
@copyduration_in_secs nvarchar NULL,
@effectiveintegrationruntime nvarchar NULL,
@source_type nvarchar NULL,
@sink_type nvarchar NULL,
@execution_status nvarchar NULL,
@errordescription nvarchar NULL,
@errorcode nvarchar NULL,
@errorloggedtime nvarchar NULL,
@failuretype nvarchar NULL
AS
INSERT INTO [pipeline_errors]
(
[datafactory_name],
[pipeline_name],
[runid],
[source],
[destination],
[triggertype],
[triggerid],
[triggername],
[triggertime],
[no_parallelcopies],
[copyduration_in_secs],
[effectiveintegrationruntime],
[source_type],
[sink_type],
[execution_status],
[errordescription],
[errorcode],
[errorloggedtime],
[failuretype]
)
VALUES
(
@datafactory_name,
@pipeline_name,
@runid,
@source,
@destination,
@triggertype,
@triggerid,
@triggername,
@triggertime,
@no_parallelcopies,
@copyduration_in_secs,
@effectiveintegrationruntime,
@source_type,
@sink_type,
@execution_status,
@errordescription,
@errorcode,
@errorloggedtime,
@failuretype
)
GO
创建源错误
在第四章中，您启动了构建 ADF 管道的过程，将源 SQL Server 表加载到 Data Lake Storage Gen2。基于这个过程，让我们测试数据工厂管道中的一个已知错误，并为了这个练习的目的通过重新创建一个错误进行处理。通常，包含至少 8，000 个以上字符的 varchar(max)数据类型在加载到 Synapse Analytics 专用 SQL 池时会失败，因为 varchar(max)是不支持的数据类型。这似乎是一个很好的错误测试用例。
首先创建一个表(如SalesLT.Address)，该表将存储一大块文本，这些文本在加载到 Synapse Analytics 专用 SQL 池时最终会导致错误。图 9-2 所示的SalesLT.Address包含具有 varchar(max)数据类型的描述列。
图 9-2
SSMS 的销售观。地址
在SalesLT.Address内，添加一大块文本。例如，您可以输入一些大于 8001 个单词的随机文本，类似于图 9-3 *中所示的示例查询输出。*确认 Description 列包含 8001 个单词，由于 Synapse Analytics 专用 SQL 池目标端的 8000 个字符的长度限制，这肯定会使 Azure 数据工厂管道失败，并且它将触发在pipeline_errors表中创建记录。
图 9-3
SSMS 的销售观。包含大量示例文本的地址描述列
向参数表中添加记录
在确定了要在流程中运行的源 SQL 表之后，将它们添加到pipeline_parameter表中，如图 9-4 所示。对于本练习，添加在上一步中创建的包含 8，001 个字符的大块的SalesLT.Address表，以及一个我们期望成功的常规表(例如SalesLT.Customer)，以演示成功和失败的端到端日志记录过程。
图 9-4
添加到 pipeline_parameter 表的记录的 SSMS 视图，用于测试 ADF 管道中的错误
验证 Azure 数据湖存储二代文件夹和文件
在运行管道将 SQL 表加载到 Azure Data Lake Storage Gen2 之后，图 9-5 显示目的地 ADLS Gen2 容器现在已经以 snappy compressed Parquet 格式拥有了这两个表。
图 9-5
包含来自 ADF 管道执行的文件夹的 ADLS Gen2 视图
作为额外的验证步骤，地址文件夹包含图 9-6 所示的预期拼花文件。
图 9-6
包含来自 ADF 管道执行的拼花文件的 ADLS Gen2 视图
配置管道查找活动
现在是构建和配置 ADF 管道的时候了。在第七章中， I 详细介绍了如何构建一个 ADF 管道来加载 Synapse Analytics 专用 SQL 池，其中包含存储在 ADLS Gen2 中的 parquet 文件。作为流程的回顾，查找中的 select 查询获取需要加载到 Synapse Analytics 专用 SQL 池的 parquet 文件列表，然后将它们传递到 ForEach 循环，该循环将 parquet 文件加载到 Synapse Analytics 专用 SQL 池，如图 9-7 所示。
图 9-7
ADF 管道查找活动设置和查询以获取列出的 pipeline_parameter 表
配置管道 ForEach 循环活动
ForEach 循环活动包含 Copy-Table 活动，该活动获取 parquet 文件并将它们加载到 Synapse Analytics 专用的 SQL 池中，同时自动创建表。如果复制表活动成功，它将把管道运行数据记录到pipeline_log表中。但是，如果 Copy-Table 活动失败，它会将管道错误详细信息记录到pipeline_errors表中。
配置存储过程来更新日志表
还要注意，之前创建的UpdateLogTable存储过程将被图 9-8 所示的成功存储过程活动调用。
图 9-8
ADF 管道存储过程设置
图 9-9 显示了将更新pipeline_log表并可直接从存储过程导入的存储过程参数。
图 9-9
ADF 管道存储过程参数
表 9-1 中列出的下列值需要作为存储过程活动参数值输入，如图 9-9 中部分所示。
表 9-1
要输入到管道日志存储过程活动的参数设置中的值列表
|名字
|
价值
| | --- | --- | | 数据工厂名称 | @{pipeline()。数据工厂} | | 管道名称 | @{pipeline()。管道} | | 运行 Id | @{pipeline()。runid} | | 来源 | @{item()。src_name} | | 目的地 | @{item().dst_name} | | 触发类型 | @{pipeline()。triggertype} | | 已触发 | @{pipeline()。triggerid} | | TriggerName | @{pipeline()。triggername} | | 触发时间 | @{pipeline()。triggertime} | | 罗斯佩德 | @ { activity(' Copy-Table '). output . rowscopied } | | RowsRead | @ { activity(' Copy-Table '). output . rows read } | | No _ ParallelCopies | @ { activity(' Copy-Table '). output . usedparallelcopys } | | 复制持续时间(秒) | @ { activity(' Copy-Table '). output . Copy duration } | | 有效积分运行时间 | @ { activity(' Copy-Table '). output . effective integration runtime } | | 来源类型 | @ { activity(' Copy-Table '). output . execution details[0]. source . type } | | 汇 _ 类型 | @ { activity(' Copy-Table '). output . execution details[0]. sink . type } | | 执行 _ 状态 | @ { activity(' Copy-Table '). output . execution details[0]。状态} | | 复制活动开始时间 | @ { activity(' Copy-Table '). output . execution details[0]。开始} | | 复制活动结束时间 | @{utcnow()} | | 复制活动 _ 队列持续时间 _ 秒 | @ { activity(' Copy-Table '). output . execution details[0]. detailed durations . queuingduration } | | 复制活动 _ 传输持续时间 _ 秒 | @ { activity(' Copy-Table '). output . execution details[0]. detailed durations . transfer duration } |
配置一个存储过程来更新错误表
ForEach 循环活动中的最后一个存储过程是之前创建的UpdateErrorTable存储过程，将被失败存储过程活动调用，如图 9-10 所示。
图 9-10
错误的 ADF 管道存储过程
图 9-11 显示了将更新pipeline_errors表的存储过程参数，这些参数可以直接从存储过程中导入。
图 9-11
错误的 ADF 管道存储过程参数
表 9-2 中的以下值需要作为存储过程参数值输入，如图 9-11 中部分所示。
表 9-2
要输入到管道错误存储过程活动的参数设置中的值列表
|名字
|
价值
| | --- | --- | | 数据工厂名称 | @{pipeline()。数据工厂} | | 管道名称 | @{pipeline()。管道} | | 运行 Id | @{pipeline()。runid} | | 来源 | @{item()。src_name} | | 目的地 | @{item().dst_name} | | 触发类型 | @{pipeline()。triggertype} | | 已触发 | @{pipeline()。triggerid} | | TriggerName | @{pipeline()。triggername} | | 触发时间 | @{pipeline()。triggertime} | | No _ ParallelCopies | @ { activity(' Copy-Table '). output . usedparallelcopys } | | 复制持续时间(秒) | @ { activity(' Copy-Table '). output . Copy duration } | | 有效积分运行时间 | @ { activity(' Copy-Table '). output . effective integration runtime } | | 来源类型 | @ { activity(' Copy-Table '). output . execution details[0]. source . type } | | 汇 _ 类型 | @ { activity(' Copy-Table '). output . execution details[0]. sink . type } | | 执行 _ 状态 | @ { activity(' Copy-Table '). output . execution details[0]。状态} | | 错误代码 | @ { activity(' Copy-Table '). error . error code } | | 错误描述 | @ { activity(' Copy-Table '). error . message } | | 错误日志时间 | @utcnow() | | 故障类型 | @ concat(activity(' Copy-Table '). error . message，' failuretype:'，activity(' Copy-Table '). error . failure type)。 |
运行管道
现在您已经配置了管道，继续运行管道。从图 9-12 中的调试模式输出日志中可以看出，正如所料，一个表成功，另一个表失败。
图 9-12
包含活动状态的 ADF 管道运行输出
核实结果
最后，验证pipeline_log表中的结果。注意在图 9-13 中pipeline_log表已经捕获了一个包含源SalesLT.Customer的日志。
图 9-13
pipeline_log 表的 SSMS 视图显示成功的 ADF 管道
pipeline_errors表中有一条SalesLT.Address的记录，以及详细的错误代码、描述、消息等，如图 9-14 所示。
图 9-14
pipeline_errors 表的 SSMS 视图，显示失败的 ADF 管道
作为最后一项检查，在导航到 Synapse Analytics 专用 SQL 池时，请注意图 9-15 中的两个表都是自动创建的，尽管一个失败，一个成功。
图 9-15
自动创建的 Synapse Analytics 专用 SQL 池表的视图
值得注意的是，由于etl.Address发生故障，数据仅加载到etl.Customer中，随后没有数据加载到其中。图 9-16 显示了来自etl.Address的数据的select *，以确认表格不包含任何数据。
图 9-16
选择*自动创建的 etl。地址表，由于 ADF 管道失败，该表不包含任何数据
其他 ADF 日志记录选项
前面几节描述了在 ADF 管道中记录和处理错误的一种更加自定义的方法。在图 9-17 所示的复制数据活动中，有一个验证数据一致性、管理容错和启用日志记录的内置方法。
图 9-17
ADF 复制数据活动中的数据验证、容错和日志记录选项
以下列表包含 ADF 中“复制数据”活动的“设置”选项卡中作为可选配置属性提供的功能的详细信息和定义。这些功能旨在提供额外的现成数据验证、确认和日志记录方法，并且可以在 ADF 管道中有选择地启用和/或禁用它们:
-
数据一致性验证:选择此选项时，复制活动将在数据移动后在源和目标存储之间进行额外的数据一致性验证。验证包括二进制文件的文件大小检查和校验和验证，以及表格数据的行数验证。
-
容错:选择此选项时，您可以忽略复制过程中间发生的一些错误，例如，源存储和目标存储之间的不兼容行、数据移动过程中文件被删除等。
-
启用日志记录:选择此选项时，您可以将复制的文件、跳过的文件和行记录到 blob 存储中。
类似地，在映射数据流中，sink 设置中也有一个选项，在图 9-18 的情况下，它使用 Azure SQL DB。请注意各种选项，包括错误行处理、事务提交和报告错误成功选项。
图 9-18
ADF 映射数据流中的错误行处理
以下列表包含 ADF 中接收器复制数据活动的“设置”选项卡中作为可选配置属性提供的功能的详细信息和定义。这些特性旨在提供额外的开箱即用错误处理、事务提交等，并且可以在 ADF 管道中有选择地启用和/或禁用它们:
-
错误行处理:“出错时继续”将防止管道失败——例如，在加载数百万行时，有几行在过程中失败。
-
事务提交:在 SQL 事务提交的上下文中允许“单个”和“批量”提交。例如，批处理将具有更好的性能，因为数据将成批加载，而不是单次提交。
-
输出被拒绝的数据:此选项类似于常规的 ADF 复制数据活动，选择后会提示您将被拒绝的数据记录到存储帐户。
-
出错时报告成功:选中时，管道出错时报告成功。
摘要
在这一章中，我介绍了自定义 ADF 日志框架中的pipeline_errors表，并讨论了它与前面章节中创建的pipeline_parameter和pipeline_log表的相关性和关系。此外，我还演示了如何在 ADF ELT 管道中设计和实现一个流程，通过在源系统上重新创建一个错误，并跟踪失败活动的 ADF 管道执行流程如何处理错误，从而将错误详细信息记录到pipeline_errors表中。最后，我描述了其他一些内置方法，用于管理 ADF 的复制数据活动和映射数据流中的错误。
十、动态加载雪花数据仓库
许多组织和客户正在考虑将雪花数据仓库作为 Azure 的 Synapse Analytics 专用 SQL 池的替代方案。在应用第七章中介绍的相同模式的同时，本章将着重向您展示如何通过使用数据块和数据工厂将数据加载到雪花 DW 中。
在现代 Azure 数据平台中，将 ADLS 第二代数据动态加载到雪花型数据仓库有多种选择。您将在本章中了解到的一些选项包括
-
ADF 管道中的参数化数据块笔记本
-
数据工厂的常规复制活动
-
数据工厂的映射数据流
在对这些不同的选项进行了详细的端到端练习之后，我将讨论基于各种 ADF 管道执行运行中的选项的功能和限制的建议。
图 10-1 中的架构图说明了 Azure 资源正在被评估，以动态地将数据从 SQL 数据库(AdventureWorks)加载到 ADLS Gen2 帐户，使用数据工厂作为 ELT，雪花数据库(ADF_DB)作为控制和记录表。请注意，ADF_DB已经在前面包含元数据管道参数表和错误表的章节中创建。在学习本章的过程中，将探索将数据加载到雪花数据仓库的各种选项，以便将 ADLS Gen2 文件动态加载到雪花数据仓库中。
图 10-1
用于在雪花数据仓库中动态加载数据的 Azure 数据流架构
链接服务和数据集
最初，您需要在 ADF 中创建一些链接的服务和数据集来构建管道。链接的服务将包括源、接收器、控制数据库和数据块记事本，它们将是 ADF 管道流程的一部分。
基础链接服务
要开始这个过程，请在数据工厂中创建如图 10-2 所示的以下链接服务。
图 10-2
将数据加载到雪花所需的基本链接服务
如图 10-3 所示，一个 ADLS Gen2 链接服务将作为 snappy 压缩文件的容器和登陆区。
图 10-3
ADLS Gen2 的连接属性
一个示例AdventureWorks Azure SQL 数据库将作为源 SQL 数据库，图 10-4 显示了用于生成本章示例的数据库的连接属性。
图 10-4
Azure SQL DB 的连接属性
雪花图ADF_DB将被用作控制数据库，用于存放 ADF 管道中的控制、日志和审计表。图 10-5 显示了一些示例连接属性。
图 10-5
雪花控制表的连接属性
图 10-6 显示了雪花AdventureWorks DB，它将作为所有AdventureWorks表将要到达的目的地/目标雪花 DB。
图 10-6
雪花目标数据仓库的连接属性
创建图 10-7 所示的数据块链接服务是为了处理包含 Scala 代码的数据块笔记本，该代码将 ADLS Gen2 文件推送到雪花目标表。
图 10-7
Azure 数据块的连接属性
资料组
一旦创建了链接的服务，还需要创建图 10-8 中的数据集。这些是将在管道中使用的数据集。
图 10-8
ADF 管道所需的数据集
图 10-9 所示的 ADLS Gen2 数据集也包含由控制表和动态输入文件驱动的参数化文件夹路径和结构。
图 10-9
ADLS 第二代数据集的连接属性详细信息
以下是图 10-9 中文件路径部分包含的代码:
poc/raw/@{item().SRC_DB}/@{item().SRC_SCHEMA}/@{item().DST_TAB_NAME}/@{formatDateTime(utcnow(),'yyyy-MM-dd')}/ @{item().SAT_TAB_NAME}
需要设置图 10-10 中的参数。这些参数由包含详细元数据信息的雪花控制表填充，在 ADF 管道和数据集中称为动态参数。
图 10-10
ADLS 第二代数据集的参数属性详细信息
接下来，请创建如图 10-11 所示的AdventureWorks数据集。这是我们到AdventureWorks2019LT数据库的源数据库连接，它将驻留在 Azure SQL 数据库上。我们将使用这个数据库提取这些表，并将它们加载到 Data Lake Storage Gen2，然后使用 Databricks 笔记本将它们移动到 Snowflake DW，这将由 ADF 编排和调用。
图 10-11
Azure SQL 数据库的连接属性详细信息
需要创建如图 10-12 所示的动态参数化雪花数据库数据集。
图 10-12
动态雪花 DW 目标表的连接属性详细信息
此外，确保在目标雪花数据库中创建一个包含控制表的ADF_DB，这些控制表可以用作图 10-13 所示的数据集。
图 10-13
雪花 DW 控制数据库和表的连接属性详细资料
雪花中的ADF_DB控制表包含如下图 10-14 所示的模式，可以根据需要进一步更新和编辑。
图 10-14
雪花型 ADF_DB 控制表方案
雪花控制数据库和表格
在第四章中，我向您展示了如何通过元数据驱动和参数化流程实现用于控制 ADF 管道的控制数据库和表格。在本章中，遵循雪花型数据仓库中控制数据库和表的相同概念。与 T-SQL 相比，SnowSQL 语法会有一些变化，我将提供您需要在 Snowflake DW 上执行的 SnowSQL 代码。
图 10-15 显示了被称为ETL_PIPELINE_CTL的ADF_DB管线控制表。
图 10-15
雪花 ADF_DB。ETL _ 管道 _CTL 控制表
此外，以下是用于在雪花中创建控制和审计表的脚本。确保在您的ADF_DB数据库的 ETL 模式中运行该脚本。该脚本将创建两个表—PIPELINE_CTL和AUDIT_TAB:
CREATE OR replace TABLE pipeline_ctl
(
parameter_id number(38,0) NOT NULL autoincrement,
server_name varchar(500),
src_type varchar(500),
src_schema varchar(500),
src_db varchar(500),
src_tab_name varchar(500),
dst_type varchar(500),
dst_tab_name varchar(500),
include_pipeline_flag varchar(500),
process_type varchar(500),
load_snowflake varchar(500),
load_frequency varchar(500),
dst_folder varchar(500),
file_type varchar(500),
lake_dst_folder varchar(500),
dst_schema varchar(500),
distribution_type varchar(500),
asql_to_lake_pipeline_date timestamp_ntz(9),
asql_to_lake_pipeline_status varchar(500),
load_snow_etl_pipeline_date timestamp_ntz(9),
load_snow_etl_pipeline_status varchar(500),
load_snow_curated_pipeline_date timestamp_ntz(9),
load_snow_curated_pipeline_status varchar(500),
load_delta_pipeline_date timestamp_ntz(9),
load_delta_pipeline_status varchar(500),
partition_field varchar(500),
priority_lane varchar(500),
spark_flag varchar(500),
swim_lane int,
PRIMARY KEY (parameter_id)
);
CREATE OR replace TABLE audit_tab
(
pipeline_name varchar(100),
db_name varchar(20),
sch_name varchar(20),
table_name varchar(50),
source_count number(38,0),
adls_count number(38,0),
snowflake_count number(38,0),
load_time timestamp_ntz(9) DEFAULT CURRENT_TIMESTAMP()
);
管道
既然已经创建并填充了基本元数据驱动的 ETL 控制表，那么就按照几个关键步骤开始创建 ADF 管道。首先，您需要设计并执行 ADF 管道来将 Azure SQL 数据库加载到 ADLS Gen2。下一步将是加载 ADLS 第二代到雪花。在将 ADLS Gen2 加载到雪花的第二步中，您将了解一些不同的选项来完成这个摄取任务。
步骤 1:设计并执行一个 ADF 管道，将 Azure SQL 数据库加载到 Data Lake Storage Gen2
本节记录了从 Azure SQL DB 到 ADLS Gen2 的数据迁移。作为参考，在第四章中已经详细讨论和演示了将数据加载到 Synapse Analytics 专用 SQL 池中的过程，而本章重点介绍了作为目标数据仓库的雪花。
一旦创建了数据集和链接服务，图 10-16 中所示的以下查找活动将查找控制表中定义的表列表，并将它们传递给 ForEach 循环活动，该循环活动将遍历源表列表(一次最多并行 50 个批处理计数)。确保在“常规”选项卡中对查找活动进行如下配置。
图 10-16
ADF 管道查找和外部 ForEach 循环活动常规属性
还要确保查找活动的配置如图 10-17 中的设置选项卡所示。
图 10-17
ADF 管道查找和外部 ForEach 循环活动设置
最后，确保在设置选项卡中配置外部 ForEach 循环活动，如图 10-18 所示。
图 10-18
ADF 管道外部 ForEach 循环活动设置
然后，ForEach 循环的内部复制活动将以 Parquet 格式将数据复制到 ADLS Gen2 中。确保在如图 10-19 所示的常规选项卡中进行配置。
图 10-19
ADF 管道复制活动常规连接属性
还要确保复制活动的源数据集属性的配置如图 10-20 所示。
图 10-20
ADF 管道复制活动源数据集属性
以下是添加到图 10-20 中的源查询部分的代码:
SELECT * FROM @item().SRC_SCHEMA}.@item().DST_TAB_NAME}
最后一个配置步骤是确保复制活动的汇数据集属性如图 10-21 所示进行配置。
图 10-21
ADF 管道复制活动接收器数据集属性
一旦管道成功执行，我们可以看到所有的表都以 Parquet 格式成功加载到 ADLS Gen2 中。图 10-22 显示了成功的管道活动运行的成功确认。
图 10-22
ADF 管道活动运行确认执行成功
图 10-23 显示 ADLS Gen2 中的文件夹结构与预期一致。
图 10-23
从 ADF 管道创建的 ADLS Gen2 文件夹
如图 10-24 所示，文件与 ADLS Gen2 中的文件一致。
图 10-24
从 ADF 管道创建的 ADLS Gen2 文件
步骤 2:设计数据湖存储二代到雪花 ADF 管道
一旦文件以 snappy 压缩文件格式进入 ADLS Gen2，有几个选项可以将拼花文件加载到雪花中。对于湖泊到雪花的摄取过程，本章评估了以下 ADF 管道选项，如图 10-25 所示。
图 10-25
将拼花文件加载到雪花中的选项
选项 1:使用 Azure Databricks 将 ADLS Gen2 加载到雪花的 ADF 管道
使用 Databricks 的 ADLS Gen2 到雪花 ADF 管道是一个选项，可以将参数从数据工厂传递到参数化 Databricks 笔记本，并确保两个服务之间的连接和集成，如图 10-26 所示。
图 10-26
选项 1 的 ADF 管道数据流
在 ForEach 循环活动中，添加 Databricks notebook 活动，并将其连接到我们在图 10-27 所示的前一部分中运行的复制数据活动。
图 10-27
ADF 管道 Azure 数据块连接属性
请注意图 10-28 中的设置选项卡，笔记本路径引用数据块笔记本，该笔记本包含代码以及将值动态传递到数据块笔记本以供进一步处理所需的基本参数。
图 10-28
具有数据块笔记本路径和参数设置的 ADF 管道
图 10-28 所示的数据块笔记本设置部分中的基本参数值使用的代码如下:
@item().DST_TAB_NAME}
@{item().DST_SCHEMA}
raw/AdventureWorksLT2019/SALESLT/@{item().DST_TAB_NAME}/@{formatDateTime(utcnow(),'yyyy')}-@{formatDateTime(utcnow(),'MM')}-@{formatDateTime(utcnow(),'dd')}/
在 Databricks 中，笔记本将包含以下 Scala 代码，如图 10-29 所示，它动态地接受来自 ADF 复制活动的参数，然后将它们传递给数据帧，该数据帧根据动态参数读取拼花文件，然后将其写入雪花表:
图 10-29
Databricks 笔记本包含将数据从 ADLS Gen2 加载到雪花的代码，从 ADF 管道活动中调用
import org.apache.spark.sql.{SaveMode, SparkSession}
spark.conf.set(
"fs.azure.account.key.adl001.dfs.core.windows.net",
"ENTER-ACCOUNT-KEY-HERE"
)
val DST_TAB_NAME = dbutils.widgets.get("DST_TAB_NAME")
val DST_SCHEMA = dbutils.widgets.get("DST_SCHEMA")
val FOLDER_PATH = dbutils.widgets.get("FOLDER_PATH")
var options = Map(
"sfUrl" -> "px.east-us-2.azure.snowflakecomputing.com",
"sfUser" -> "USERNAME",
"sfPassword" -> "PW",
"sfDatabase" -> "ADVENTUREWORKS",
"sfSchema" -> DST_SCHEMA,
"truncate_table" -> "ON",
"usestagingtable" -> "OFF",
"sfWarehouse" -> "COMPUTE_WH"
)
val df = spark.read.parquet("abfss://poc@gze2np1adl001.dfs.core.windows.net/"+FOLDER_PATH+DST_TAB_NAME)
df.write
.format("snowflake")
.options(options)
.option("dbtable", DST_TAB_NAME)
.mode(SaveMode.Overwrite)
.save()
选项 2:使用 ADF 复制活动将 ADLS Gen2 加载到雪花的 ADF 管道
下一个 ADLS Gen2 到 Snowflake ADF 管道选项将使用所有使用常规复制活动的数据工厂原生工具。ADF 管道的高层数据流如图 10-30 所示。
图 10-30
选项 2，将使用数据工厂的本地工具，使用常规的复制活动
此外，该选项将要求创建和配置 Blob 存储服务，并且仅要求对 Blob 链接服务连接进行 SAS URI 认证，图 10-31 中显示了一个配置示例。
图 10-31
Blob 存储连接属性
确保已经配置了以下复制数据活动源设置，如图 10-32 所示。
图 10-32
复制数据源数据集属性
图 10-33 确认以下复制数据活动接收器设置已正确配置。
图 10-33
复制数据接收器设置详细信息
请注意，在图 10-34 的设置部分，需要启用暂存并链接到服务。
图 10-34
复制数据设置详细信息
选项 3:使用映射数据流将 ADLS Gen2 加载到雪花的 ADF 管道
这个 ADLS Gen2 到雪花 ADF 管道选项将在映射数据流中使用所有数据工厂原生工具以及 Spark compute。图 10-35 显示了这个高级 ADF 数据流的样子。
图 10-35
在映射数据流中使用所有数据工厂原生工具和 Spark 计算的选项
在 ForEach 循环活动中，确保数据流活动连接到复制数据活动，并如图 10-36 所示配置设置。
图 10-36
映射数据流设置
在映射数据流活动中，有一个到 ADLS Gen2 帐户的源连接需要配置，如图 10-37 所示。
图 10-37
将数据流活动来源连接映射到 ADLS Gen2
还要确保引用目标雪花账户的目的地配置如下，如图 10-38 所示。
图 10-38
映射数据流活动接收器雪花连接属性
请注意，在图 10-39 的目标设置中，我们指定了一个“重建表格”动作。
图 10-39
指定“重新创建表格”操作的目标设置
图 10-40 显示了在映射部分中有其他有价值的特性来跳过重复。
图 10-40
映射部分中跳过重复项的功能
此外，图 10-41 显示有优化分区的选项。
图 10-41
优化分区的选项
比较各种 ADLS 第二代和雪花摄入选项
在本章的前几节中，我已经演示了几种将数据加载到雪花中的方法。在这一节中，我将比较这些不同的摄取选项，总结这些选项的优缺点。
表 10-1 显示了所有三项活动的执行管道的一些比较，比较了稳定、可扩展和成功摄取管道的关键组成部分。
表 10-1
比较各种 ADLS 第二代和雪花摄入选项
| |参数化数据块笔记本
|
数据工厂映射数据流
|
数据工厂复制活动
| | --- | --- | --- | --- | | 数据类型映射问题 | AdventureWorks 示例表中没有记录。 | 二进制数据类型会导致错误。 | 二进制数据类型会导致错误。 | | 动态自动创建表格能力 | 是的(目前，自动创建表的过程正在最大限度地利用雪花中的数据类型)。 | 是的(目前，自动创建表的过程正在最大限度地利用雪花中的数据类型)。 | 否(目前，没有现成的动态自动创建表的功能)。 | | 动态自动截断功能 | 是 | 是 | 是 | | 管道活动审计捕获能力 | 是 | 是 | 是 | | 需要中间斑点阶段 | 不 | 不 | 是 | | 需要 SAS URI 认证 | 不 | 不 | 是 | | 所需的集群预热时间或运行状态 | 是(Databricks 集群预热大约需要 5 分钟；指定核心数量、计算类型和生存时间的能力)。 | 是(映射数据流群集预热大约需要 5 分钟；指定核心数量、计算类型和生存时间的能力)。 | 不 | | 指定计算和内核数量的能力 | 是 | 是 | 不适用(本活动不使用 Spark 计算)。 | | 管理模式漂移/演变的能力 | 是 | 是 | 不适用的 | | 优化分区的能力 | 是 | 是 | 不适用的 | | 追加库(罐子、鸡蛋、轮子)的能力 | 是 | 是 | 不适用(本练习不使用 Spark 计算，因此没有附加库功能)。 | | 运行并行活动的能力 | 是 | 是 | 是 |
泳道
由于 ADF 目前能够通过其 ForEach 循环管道活动同时并行运行 50 个表，如果需要并行运行大量的表，您可以向雪花控制表添加一个名为swim_lane的自定义属性，然后运行下面的代码，该代码将为一组表分配一个唯一的编号，然后通过对swim_lane列进行筛选，这些表可以通过一个活动集运行。使用这种方法，您可以在 ADF 管道中使用多个 ForEach 循环拥有多个泳道。
以下代码将更新该表以创建swim_lane列:
UPDATE adf_db.etl.pipeline_ctl PL1
SET swim_lane = 2
FROM (SELECT src_tab_name,
Row_number()
OVER (
ORDER BY src_tab_name ) AS rn
FROM adf_db.etl.pipeline_ctl) b
WHERE PL1.src_tab_name = b.src_tab_name
AND b.rn > 5
数据有效性
有必要通过验证从源 Azure SQL 数据库服务器复制到 ADLS Gen2 并从那里复制到雪花目标表集的最终目的地的行数来执行基本的完整性检查。
这种级别的详细信息可以在您的雪花环境中的控制表中捕获，并可以命名为AUDIT_TAB。该表将接受来自 ADF 和 Databricks 笔记本的动态传递的参数。请参见第八章和第九章，了解如何在 ADF 中构建一个健壮的日志记录框架，然后将其与本章的知识相结合，以将该日志记录流程与 ADF、Databricks 和雪花集成。
以下代码可用于在雪花 DW 中创建日志表:
CREATE OR replace TABLE audit_tab
(
pipeline_name varchar(100),
src_db_name varchar(20),
dest_db_name varchar(20),
sch_name varchar(20),
table_name varchar(50),
source_count number(38,0),
adls_count number(38,0),
snowflake_count number(38,0),
load_time timestamp_ntz(9) DEFAULT CURRENT_TIMESTAMP()
);
摘要
在本章中，我演示了一个两步流程，该流程在一个 ADF 管道中链接在一起，用于(1)将数据从 Azure SQL 数据库加载到 Data Lake Storage Account Gen2，以及(2)将数据从 Data Lake Storage Gen2 加载到雪花数据仓库。在该过程的第二部分(ADLS Gen2 到雪花)，我演示了三个选项:数据块、ADF 复制活动和映射数据流。我还介绍了通过利用从第八章和第九章学到的知识，将 ADF 管道指标记录到雪花表格中的想法。最后，我比较了三个选项，以了解每个选项的优缺点。
十一、为数据仓库 ETL 映射数据流
Azure 众多数据服务的出现在数据仓库领域引发了兴奋和困惑，原因有几个。客户对现代基于云的数据仓库的概念感兴趣，但可能会被市场上过多的基于云的服务所淹没。他们想知道如何开始实施传统的数据仓库概念，例如维度建模、渐变维度(SCDs)、星型模式以及云中的数据仓库 ETL 或 ELT。SQL Server Integration Services(SSIS)等传统的内部 Microsoft 技术是成熟的数据集成工具，已经流行了十多年。随着越来越多的基于云的工具变得可用，客户有兴趣将他们的 ETL 迁移到云中，或者只是开始了解云中的数据仓库。
微软继续通过映射数据流来扩展他们在 Azure Data Factory 中的服务产品，这允许可视化和无代码的数据转换逻辑，该逻辑使用横向扩展的 Azure Databricks 集群作为 Azure Data Factory 管道的活动来执行。本质上，这一产品使 Azure Data Factory 更接近微软的传统 SQL Server Integration Services，后者已用于数据仓库 ETL 多年。映射数据流在数据仓库空间中对于几种数据仓库模式具有巨大的潜力，例如渐变维度类型 I 和类型 II 以及事实提取、转换和数据加载。
在这一章中，我将讨论典型的数据仓库负载模式，称为渐变维度类型 I，以展示如何使用 Azure Data Factory 的映射数据流来设计这种数据流模式的实际例子。
现代数据仓库
数据工厂在现代数据仓库环境中扮演着关键角色，因为它可以很好地集成结构化、非结构化和本地数据。最近，它开始与 Data Lake Storage Gen2 和 Azure Databricks 很好地集成。图 11-1 中的图表很好地描述了数据工厂在现代数据仓库中的位置。
图 11-1
现代 Azure 数据平台数据流架构图
从图 11-1 中可以看出，数据工厂是源、目标和分析系统之间的关键集成者。此外，通过添加一个无代码的基于图形用户的界面，如映射数据流，利用引擎盖下的 Spark 集群，Data Factory 肯定会在现代数据仓库的设计和开发中发挥关键作用。
创建基础 Azure 数据资源
出于本章示例练习的目的，您将需要创建一个源 OLTP 数据库以及一个包含转换维度的目标 OLAP 数据库。在第四章中，我描述了如何免费获取样本数据，比如AdventureWorksLT表和数据，然后可以加载到 Azure SQL 数据库中。这个练习将利用与OLTP_Source相同的AdventureWorksLT数据库。一旦在 Azure 中创建了数据库，请注意图 11-2 中包含了您将在本练习中使用的SalesLT.Customer表。
图 11-2
源 OLTP 和目标 OLAP 数据库
在OLAP_Target数据库中创建下面的DimCustomer表。DimCustomer的模式如下:
CREATE TABLE dbo.dimcustomer
(
[customerid] INT NOT NULL,
[title] NVARCHAR (8) NULL,
[firstname] [dbo].[NAME] NOT NULL,
[middlename] [dbo].[NAME] NULL,
[lastname] [dbo].[NAME] NOT NULL,
[suffix] NVARCHAR (10) NULL,
[companyname] NVARCHAR (128) NULL,
[salesperson] NVARCHAR (256) NULL,
[emailaddress] NVARCHAR (50) NULL,
[inserteddate] DATETIME NULL,
[updateddate] DATETIME NULL,
[hashkey] NVARCHAR (128) NOT NULL,
PRIMARY KEY (customerid)
);
一旦创建了表，通过导航到 Azure Portal 中的资源组并确认数据工厂、逻辑 SQL Server、源数据库和目标数据库都已创建，验证所有必要的资源都已创建，如图 11-3 所示。
图 11-3
Azure 门户资源
缓变尺寸 I 型
渐变维度是维度数据仓库常用的高级技术，用于捕获维度内随时间变化的数据。本章中的练习将利用数据工厂管道中缓慢变化的维度类型 I。
虽然有许多类型的渐变维度，但我将在本章中介绍的类型 I 只是用新值覆盖现有的数据值。这种方法的优点是，它使维度的更新变得容易，并将增长限制在新记录上。缺点是会丢失历史数据，因为维度总是只包含每个属性的当前值。
创建数据工厂管道和数据集
通过单击“Author & Monitor”启动数据工厂控制台，开始为渐变维度类型 I ETL 模式创建 ADF 管道，如图 11-4 所示。
图 11-4
通过单击“作者和监视器”启动 Azure 数据工厂
接下来，点击“创建管道”创建一个新的管道，如图 11-5 所示。
图 11-5
创建新的 ADF 管道
将新管道命名为 DW ETL，它将包含以下两个数据集:
-
AzureSqlCustomerTable**:**这是包含
AdventureWorksLT表的 OLTP Azure SQL 源数据库。具体来说，使用Customer表进行 ETL。 -
AzureSqlDimCustomerTable**:**这是包含维度 Customer 表的 OLAP Azure SQL 目标数据库。这个维度表与 OLTP 源表的不同之处在于，它包含更少的字段，并且包含一个
InsertedDate、UpdatedDate和HashKey，您将把它们用于 SCD Type I ETL 模式。
创建管道和数据集后，工厂资源部分将被填充，如图 11-6 所示。
图 11-6
ADF 管道和数据集
创建数据工厂映射数据流
现在，您已经为源和目标创建了 ADF 管道和数据集，您已经准备好使用 SCD Type I 创建您的数据流。首先，将数据流从 Activities 拖到 Data Factory 画布上，如图 11-7 所示。
图 11-7
ADF 数据流活动
然后给你的数据流一个直观的名字。此时为DimCustomer，如图 11-8 所示。
图 11-8
DimCustomer 的 ADF 数据流活动
创建数据流之前，请确保打开数据流调试模式( https://docs.microsoft.com/en-us/azure/data-factory/concepts-data-flow-debug-mode ))，因为您将在将管道部署到生产环境之前对其进行测试。图 11-9 显示了数据流调试模式，这对于测试是足够的。当您切换到调试模式时，系统会询问您是否希望继续。单击“确定”继续。
图 11-9
ADF 数据流调试模式
图 11-10 显示集群需要几分钟的时间准备好，准确地说通常需要 5 到 7 分钟。
ADF 在 Azure integration runtime 中为数据流生存时间(TTL)引入了一个新的“快速重用”特性。启用 TTL 后，ADF 可以在管道中执行最后一个数据流后的一段时间内维护 Spark 集群，这将在数据流活动中使用相同的 Azure IR 提供更快的连续执行。
图 11-10
ADF 数据流调试模式群集正在准备就绪
一旦打开数据流调试模式，图 11-11 所示的绿点将出现在它的右侧，以确认它已准备好开始调试模式流水线运行。此时，您可以开始创建渐变维度类型 I 数据流。
图 11-11
ADF 数据流调试模式群集准备就绪
如图 11-12 所示，通过添加和配置两个数据集作为源开始数据流。
图 11-12
ADF 映射数据流源设置
点击AzureSqlCustomerTable旁边图 11-13 所示的+图标，添加一个派生列，称为CreateHash。
图 11-13
ADF 映射数据流派生列
然后选择散列列并使用散列函数。对于这个场景，派生列将使用以下函数，如图 11-14 所示:
图 11-14
ADF 映射数据流派生列设置
sha1(FirstName+LastName+CompanyName)
然后将以下输入、模式和行修饰符添加到数据流中，如图 11-15 所示。
图 11-15
DimCustomer 中 SCD 类型 1 的 ADF 映射数据流活动
以下小节描述了图 11-15 中的每个步骤。
存在
Exists 步骤将通过比较新创建的源哈希键和图 11-16 所示的目标哈希键来检查哈希键是否已经存在。
图 11-16
ADF 映射数据流存在设置
外观更新
然后LookupDates步骤将连接从源到目标的CustomerID以确保包含相关的记录，如图 11-17 所示。
图 11-17
ADF 映射数据流查找设置
SetAttributes
SetAttributes步骤将添加两个派生列。如果为空，InsertedDate将插入当前时间戳，UpdatedDate将总是用当前时间戳更新该行，如图 11-18 所示。
图 11-18
ADF 映射数据流派生列的设置
以下是图 11-18 中InsertedDate和UpdatedDate的列字段中使用的代码:
iif(isNull(InsertedDate),currentTimestamp(),{InsertedDate})
currentTimestamp()
交替行
AlterRows 允许 upserts，条件设置为true()，这将更新或插入通过流的所有内容，如图 11-19 所示。
图 11-19
ADF 映射数据流改变行设置
sink1
最后，接收步骤将写回图 11-20 所示的DimCustomer表。
图 11-20
ADF 映射数据流接收器设置
在图 11-21 所示的设置选项卡中，确保选中“允许上插”，并将CustomerID指定为关键栏。
图 11-21
ADF 映射数据流接收器更新方法“允许向上插入”
最后，确保映射是准确的。可能需要禁用“自动映射”，如图 11-22 所示，以正确映射新创建的派生列。
图 11-22
ADF 映射数据流接收器映射
一旦完成渐变维度类型 I 数据流，它将如图 11-23 所示。
图 11-23
在 DimCustomer MDF 管道中，ADF 映射数据流已完成 SCD 类型 1
为DimCustomer设计和配置 SCD 类型 I 数据流的过程现在已经完成。由于调试模式已经打开，只需在管道视图中点击图 11-24 中的“调试”并等待管道完成运行。
图 11-24
ADF 映射数据流管道在调试模式下运行
一旦管道完成运行，请注意管道成功状态右侧的绿色检查圈。由于DimCustomer表中没有数据，所以这次管道运行将所有记录加载到DimCustomer表中，如图 11-25 所示。
图 11-25
ADF 映射数据流调试模式管道成功输出
更新记录
为了测试管道的功能，从 OLTP 系统中选择一条记录，如图 11-26 所示。它可以是任何记录，为了举例，我们假设您选择了艾伦·布鲁尔的记录。
图 11-26
从 OLTP 源客户表中选择一条记录
以下是已经在图 11-26 中执行的 SQL 代码:
SELECT *
FROM [SalesLT].[customer]
WHERE customerid = 148
然后从 Brewer 更新CustomerID 148 的LastName，更新如图 11-27 所示。
图 11-27
更新 OLTP 源客户表中的记录
以下是已经在图 11-27 中执行的 SQL 代码:
UPDATE [SalesLT].[customer]
SET lastname = 'Update'
WHERE customerid = 148;
SELECT *
FROM [SalesLT].[customer]
WHERE customerid = 148
在重新运行 ADF 管道之前，在DimCustomer表上运行 select *查询，注意到LastName仍然是“Brewer”，如图 11-28 所示。
图 11-28
从 OLAP DimCustomer 表中选择记录
以下是已经在图 11-28 中执行的 SQL 代码:
SELECT *
FROM [dbo].[Dimcustomer]
WHERE CustomerId = 148
重新运行 ADF 管道后，请注意图 11-29 中的LastName已被适当更新，这确认了 ADF 管道考虑了更新。
图 11-29
更新 OLAP DimCustomer 表中的记录
以下是已经在图 11-29 中执行的 SQL 代码:
SELECT *
FROM [dbo].[DimCustomer]
插入记录
还要测试向 OLTP 系统中插入一条新记录，然后重新运行 ADF 管道，查看插入是否被拾取。为此，请执行以下示例中的 USE 和 INSERT 语句。执行以下查询，将新记录插入到[SalesLT].[Customer]表中:
USE [OLTP_Source]
go
INSERT INTO [SalesLT].[customer]
([namestyle],
[title],
[firstname],
[middlename],
[lastname],
[suffix],
[companyname],
[salesperson],
[emailaddress],
[phone],
[passwordhash],
[passwordsalt],
[rowguid],
[modifieddate])
VALUES (1,
'Mr.',
'John',
'B',
'Doe',
NULL,
'MSSQLTips',
NULL,
NULL,
NULL,
'L/Rlwxzp4w7RWmEgXX+/A7cXaePEPcp+KwQhl2fJL7d=',
'1KjXYs4=',
'2005-08-01 00:00:00:000')
go
然后检查以确保您看到如图 11-30 所示的结果。您应该会看到刚刚插入的记录。
图 11-30
在 OLTP 客户表中选择插入的记录
以下是已经在图 11-30 中执行的 SQL 代码:
SELECT *
FROM [SalesLT].[customer]
WHERE companyname = 'MSSQLTips'
在您重新运行 ADF 管道之后，请注意图 11-31 中新的 OLTP 记录也被插入到[dbo].[DimCustomer]表中，这确认了 ADF 管道考虑了插入。
图 11-31
选择 OLAP DimCustomer 表中的插入记录
以下是已经在图 11-31 中执行的 SQL 代码:
SELECT *
FROM [dbo].[dimcustomer]
WHERE companyname = 'MSSQLTips'
完成后，请记住关闭“数据流调试”模式，以防止数据工厂中未使用的功能产生不必要的成本。另外，记得根据需要清理并删除资源组中任何未使用的资源，如图 11-32 所示。
图 11-32
根据需要删除未使用的资源和/或资源组
摘要
在这一章中，我讨论了现代数据仓库以及 Azure Data Factory 的映射数据流及其在这一领域中的作用。您了解了如何设置源、目标和数据工厂资源，以便为使用映射数据流设计渐变维度类型 I ETL 模式做准备。此外，您还学习了如何在 Azure Data Factory 中设计和测试缓慢变化的 Dimension Type I 数据流和管道。
有几种不同类型的缓变尺寸可用于不同的目的。微软的 Azure Data Factory 提供了很多方法让快速上手使用 Data Factory 中的模板，还有为渐变维度类型 II、数据清理等添加的模板。记住要理解 Ralph Kimball 的数据仓库 工具包 作为维度建模的权威指南，这将有助于更深入地理解设计数据仓库的基本要素。
十二、使用映射数据流聚合和转换大数据
在数据湖中清理和转换大数据集的过程已经成为现代企业数据架构中越来越受欢迎和关键的一步。微软推出了几种大数据分析和编排工具，以满足大数据湖提取-加载-转换(ELT)的需求。客户正在寻求基于云的服务，这些服务可以轻松地清理、转换和聚合非常大的数据集，并且学习曲线较低。他们正在寻求了解哪些工具和技术可能符合大数据湖清理和转换的要求。
Azure Data Factory 的映射数据流已经成为大数据湖清理和转换的一个有前途的解决方案。在第十一章中，我讨论了现代数据仓库的概念，并展示了一个为企业数据仓库转换映射数据流的实际例子。在本章中，我将继续演示映射数据流的其他数据清理和聚合功能，特别是处理作为分层文件存储在 Azure Data Lake Storage Gen2 中的大数据文件。
将文件和文件夹添加到 Azure 数据湖存储二代
通过使用最佳实践来正确构建数据湖存储是关键。当数据存储在 Data Lake Storage Gen2 中时，文件大小、文件数量和文件夹结构会对性能产生影响。
文件大小
根据使用数据的服务和工作负载，文件的合适大小是 256 MB 或更大。如果在数据湖存储中无法对文件大小进行批处理，您可以进行单独的压缩作业，将这些文件合并成更大的文件。
文件夹结构
文件夹和文件的组织结构可以帮助某些查询只读取数据的子集，这通过优化较大的文件大小和每个文件夹中合理的文件数量来提高性能。了解性能调优和优化技术，以及文件夹和文件结构建议。
在本练习中，您将学习如何创建一个名为湖的 ADLS Gen2 容器，以及一些用于组织 2016 年数据的附加文件夹。首先，请确保您已经设置了 ADLS Gen2 帐户和 ADF 帐户。接下来，您需要将 2016 年的销售文件从以下 GitHub 账户( https://github.com/ronlesteve/sales-datasets )上传到如图 12-1 所示的 ADLS Gen2 文件夹结构中。
从图 12-1 中我们可以看到，在 2016 年的销售文件夹中，额外的文件夹是按月份编号组织的。
图 12-1
按月份编号组织的 2016 年销售文件夹
每个月内，有**。按天整理保存的 txt** 文件，如图 12-2 所示。
图 12-2
按月日文本文件组织的 2016 年销售文件夹
最后，在打开其中一个文本文件时，请注意图 12-3 中的数据结构，该数据结构由以下选中的列组成。
图 12-3
文本文件的结构
创建 Azure 数据工厂资源
既然数据湖文件和文件夹已经被结构化了，是时候创建必要的 Azure 数据工厂资源了。数据工厂打开后，开始创建新的管道，如图 12-4 所示。
图 12-4
创建新的 ADF 管道
接下来，添加一个新的数据集，该数据集将引用具有以下连接属性的 lake 容器。请注意，图 12-5 中的目录和文件属性被留空，因为这可以在映射数据流属性中动态设置。另外，将列分隔符设置为制表符() 。
图 12-5
ADLS 第二代数据集连接属性
发布资源后，图 12-6 中的工厂资源部分将可用。这些资源由管道和数据集组成。
图 12-6
ADF 管道和数据集资源
在新创建的管道中，从 Activities 中展开 Move & Transform ，然后将如图 12-7 所示的数据流活动拖到画布上。
图 12-7
ADF 数据流活动
创建映射数据流
现在您已经准备好创建如图 12-8 所示的数据流活动。
图 12-8
ADF 销售订单数据流
首先，将图 12-9 中所示的源连接添加到上一节中创建的 SalesOrderDataset 中。
图 12-9
与 SalesOrderDataset 的 ADF 数据流源连接
确保允许模式漂移，因为文件中可能会有更改的列。此外，选择推断漂移柱类型允许自动检测漂移柱类型。这些选项如图 12-10 所示。
图 12-10
ADF 源数据流设置
将 2016 年参数化，这样就可以在硬编码路径之外维护这些值。要添加参数，单击映射数据流画布中的空白区域，然后选择并添加所需的参数，如图 12-11 所示。
图 12-11
ADF 源数据集参数
添加参数后，返回到“源选项”选项卡。在源选项下，在通配符路径中添加 2016 sales 文件夹的路径。此设置将覆盖数据集中设置的文件夹路径，从容器根目录开始。 ****** 将允许递归目录嵌套。最后，使用 *.txt. 指定所有需要的文本文件。信号源选项选项卡的这些设置已在图 12-12 中列出。
图 12-12
ADF 源选项
以下是添加到图 12-12 中源选项选项卡的通配符路径字段的代码:
'DataLakeCleansing/raw/sales/'+$Year+'/**/*.txt'
在图 12-13 所示的投影选项卡中，验证并更改列模式。
图 12-13
ADF 数据流源投影
接下来，添加一个如图 12-14 所示的选择模式修饰符来删除需要的列。为此，单击源活动旁边的+图标，并添加 Select schema 修饰符。请注意，还有许多其他选项可以用来转换数据和模式。
图 12-14
ADF 映射数据流“选择”修饰符活动
也可以选择跳过重复的选项，如图 12-15 所示。
图 12-15
ADF 数据流“选择设置”以跳过重复项
接下来，添加一个派生列模式修饰符来添加两个新列，如图 12-16 所示:
图 12-16
ADF 数据流派生列
-
基于
OrderDate的订单月数 -
年数也基于
OrderDate
派生列非常适合通过表达式的力量进行数据清理。
正则表达式(Regex)
正则表达式是指定搜索模式的字符序列。通常，字符串搜索算法使用这种模式对字符串进行“查找”或“查找和替换”操作，或者进行输入验证。它是在理论计算机科学和形式语言理论中发展起来的一种技术。正则表达式函数与映射数据流兼容，可以添加到 ADP 管道内的活动中。以下是一些示例正则表达式及其预期用途:
-
RegexReplace(Address,^a-zA-Z\d\s:,''):删除所有非字母数字字符 -
RegexReplace(Address,[ ]{2}|.,' '):获取包含街道地址字符串的地址字段，并替换任何出现的两个空格或点“.”只有一个空格。 -
Regex_extract(Address,^(\d+), 1):使用街道地址提取门牌号。
桑迪克斯
Soundex 是一种语音算法，用于通过声音索引姓名，如在英语中发音为。目标是将同音字编码为相同的表示，这样即使拼写有微小差异，它们也可以匹配。
当在湖中处理半结构化或非结构化数据时，这是一个很好的表达方式，可以用来解决在没有键的情况下连接和清理数据的问题。
接下来，添加一个如图 12-17 所示的聚合模式修饰符来聚合单价*数量。
图 12-17
ADF 数据流聚合架构修饰符
请记住按图 12-18 中所示的以下各列对该聚合进行分组。
图 12-18
按聚合设置分组的 ADF 数据流
图 12-19 显示了您需要输入总计算值的位置。
图 12-19
ADF 数据流聚合
现在聚合已经完成，添加一个 Windows 模式修饰符，如图 12-20 所示。
窗口转换是在数据流中定义基于窗口的列聚合的地方。在表达式生成器中，您可以定义基于数据或时间窗口(SQL OVER 子句，如 LEAD、LAG、NTILE、CUMEDIST、RANK 等)的不同类型的聚合。).将在您的输出中生成一个包含这些聚合的新字段。您还可以包括可选的分组依据字段。
图 12-20
ADF 数据流窗口架构修饰符
图 12-21 展示了如何通过CustomerName对总数进行排序。
图 12-21
“结束”的 ADF Windows 设置
图 12-22 显示了如何按降序排列总数，从最高到最低排列总数。
图 12-22
“排序”的 ADF 窗口设置
将范围 by 设为无界，如图 12-23 所示。
图 12-23
“范围依据”的 ADF 窗口设置
接下来，在总数中添加一个密集排名函数。请注意，有一些秩和行数函数适合特定的需求和用例。
等级函数
RANK 函数用于根据 ORDER BY 子句的条件检索已排序的行。
表 12-1
秩函数样本输出
|易名中国
|
盐
|
等级
| | --- | --- | --- | | 史密斯 | eight hundred | one | | 詹姆斯 | Nine hundred and fifty | Two | | 亚当斯 | One thousand one hundred | three | | 马丁 | One thousand two hundred and fifty | four | | 病房 | One thousand two hundred and fifty | four | | 车工 | One thousand five hundred | six |
下面是一个包含 RANK 函数的示例查询:
SELECT ename,
sal,
Rank()
OVER (
ORDER BY sal) RANK
FROM emp;
表 12-1 显示了在样本数据集上运行的 RANK 函数的样本输出。
稠密秩函数
DENSE_RANK 函数类似于 RANK 函数。但是，如果前面记录的等级之间存在关联，DENSE_RANK 函数不会跳过任何等级。
以下是包含 DENSE_RANK 函数的示例查询:
SELECT ename,
sal,
Dense_rank()
OVER (
ORDER BY sal) DEN_RANK
FROM emp;
表 12-2 显示了在样本数据集上运行的 DENSE_RANK 函数的样本输出。
表 12-2
DENSE_RANK 函数示例输出
|易名中国
|
盐
|
等级
| | --- | --- | --- | | 史密斯 | eight hundred | one | | 詹姆斯 | Nine hundred and fifty | Two | | 亚当斯 | One thousand one hundred | three | | 马丁 | One thousand two hundred and fifty | four | | 病房 | One thousand two hundred and fifty | four | | 车工 | One thousand five hundred | five |
行数函数
与 RANK 和 DENSE_RANK 函数不同，ROW_NUMBER 函数只返回从 1 开始排序的记录的行号。ADF 中映射数据流转换任务的窗口设置如图 12-24 所示。
图 12-24
“窗口栏”的 ADF 窗口设置
窗口功能完成后，添加一个 sink 将丰富的结果存储在数据湖中，如图 12-25 所示。
图 12-25
用于存储丰富数据的 ADF 数据流接收器
使用图 12-26 中所示的以下 sink 数据集设置，并记住选中允许模式漂移选项。Azure Data Factory 本身支持灵活的模式，这些模式会随着执行的不同而变化，因此您可以构建通用的数据转换逻辑，而无需重新编译您的数据流。
模式漂移是数据源经常改变元数据的情况。可以动态地添加、删除或更改字段、列和类型。如果不处理模式漂移，您的数据流将很容易受到上游数据源更改的影响。当传入的列和字段发生变化时，典型的 ETL 模式会失败，因为它们往往与那些源名称联系在一起。
为了防止模式漂移，作为一名数据工程师，让数据流工具中的设施允许您
-
定义具有可变字段名、数据类型、值和大小的源。
-
定义可以处理数据模式而不是硬编码字段和值的转换参数。
-
定义理解模式的表达式以匹配传入字段，而不是使用命名字段。
图 12-26
ADF 数据流接收器数据集连接属性
另外，将设置配置为输出到单个文件并指定文件名，如图 12-27 所示。
图 12-27
ADF 数据流接收器设置
映射数据流的好处之一是数据流调试模式，它允许预览转换后的数据，而不必手动创建集群和运行管道。
记得打开调试模式以便预览如图 12-28 所示的数据，然后在退出 Azure Data Factory 之前关闭它。请注意，调试模式将在一段时间后自动终止。
图 12-28
ADF 数据流调试触发器
按客户排序的总结果将类似于图 12-29 中显示的结果。
图 12-29
按客户排列的总结果
下一个练习将向您展示如何将总计分割成一个新的分支，以创建一个具有不同窗口函数的新文件，这次将按月对总计进行排序，并将结果输出到数据湖中的不同文件，如图 12-30 所示。
图 12-30
将总聚合拆分为新分支
确保新窗口功能的设置如图 12-31 所示。
图 12-31
按月排列总数并将结果输出到文件的设置
一旦映射数据流完成，它将如图 12-32 所示。
图 12-32
完整的端到端 ADF 映射数据流
摘要
在本章中，我演示了如何创建一个指向数据湖容器的数据集。接下来，我向您展示了如何向 2016 sales 文件夹中的所有文本文件添加参数化通配符路径。然后，我向您展示了如何选择相关的列，添加几个关键的派生列，执行聚合，添加窗口函数，分割分支，以及将所需的结果导出到数据湖中的富文本文件。
输出数据集包含按客户名称和月份进行聚合和降序排列的总计(单价*数量)。所有这些都是通过利用 Azure Data Factory 的映射数据流特性完成的，并使用数据流调试功能进行了测试。
