Azure-数据工程权威指南-一-Azure 数据工程权威指南（一）一、工具和先决条件本章将通过让你掌握传统的微软商

Azure 数据工程权威指南（一）

原文：The Definitive Guide to Azure Data Engineering

协议：CC BY-NC-SA 4.0

一、工具和先决条件

本章将通过让你掌握传统的微软商业智能(BI)栈，涵盖一些 Azure 数据工程入门的关键技巧。本章将帮助您了解 Azure 的现代企业数据和分析平台、各种大数据选项和性能调整建议，以及 Azure 数据工程师助理认证的基本要求。最后，这一章将涵盖扩展你的其他 Azure 专业知识的价值，以解决 Azure 数据解决方案的商业价值，并向你介绍通过 Azure Portal 开始 Azure 数据工程的选项。这一章将以对本书将涉及的 Azure 服务以及未涉及的 Azure 服务的介绍结束。

掌握传统的微软商业智能堆栈

Azure 的许多数据平台工具都源于传统的微软 SQL Server BI 平台。例如，Azure Data Factory(ADF)的映射数据流(MDF)很像 SQL Server Integration Services(SSIS)，而 Azure Analysis Services 则植根于 SQL Server Analysis Services (SSAS)。虽然 Azure 有许多微软传统 BI 堆栈中不常见的复杂性，但对 SSIS、SSRS、SSAS、T-SQL、C#、数据仓库等的深刻理解将有助于更好地理解 Azure 的数据服务。许多组织都有多年使用传统微软 BI 堆栈的相似历史，可能会寻找 Azure 数据工程师来帮助开拓他们进入现代企业数据和分析平台的旅程。通过对这些组织目前拥有的传统工具的了解和经验，您将能够很好地适应环境，并展示对传统 Microsoft BI 堆栈以及数据仓库概念的掌握。例如，了解如何设计和实现将数据从内部 SQL 数据库增量同步到 Azure Data Lake Storage gen 2(ADLS gen 2)的过程是许多组织正在寻求的技能，也是本书在未来章节中涉及的主题。

有许多在线资源和书籍可以帮助您掌握传统的 Microsoft BI 技能。例如，Ralph Kimball 的 数据仓库工具包 提供了维度建模的权威指南。还有许多其他学习微软商务智能的在线资源，从付费视频课程到免费的 YouTube 教程，例如来自 WiseOwlTutorials 、 TechBrothersIT 等等。图 1-1 描绘了传统微软 BI 数据架构的典型端到端流程。

img/511918_1_En_1_Fig1_HTML.jpg

图 1-1

在微软商务智能平台中使用 SSIS、SSAS 和 SSRS

了解 Azure 的现代企业数据和分析平台

虽然微软 Azure 拥有大量的资源，但现代企业数据和分析平台中最常见的组件在图 1-2 中列出。作为一名 Azure 数据工程师，能够设计和实现一个端到端的解决方案是至关重要的，这个解决方案遵循这个架构过程或它的定制变体，同时考虑安全性、高可用性等等。理解多种数据存储和数据集成选项之间的差异和相似之处也很重要。

img/511918_1_En_1_Fig2_HTML.jpg

图 1-2

现代 Azure 数据架构平台

在下一章中，我们将讨论如何在 Azure Data Factory、SQL Server Integration Services(SSIS)和 Azure Databricks 之间进行选择，以了解如何根据特定的用例及场景为工作选择正确的 ETL(提取-加载-转换)工具，并帮助您选择最适合工作的工具。了解何时选择 Azure Synapse 分析数据仓库(DW)还是 Azure SQL 数据库(ASQLDB)也是一种很好的做法。Melissa Coates 的以下文章详细讨论了这些不同的选项:

" Azure SQL 数据仓库是一个很好的选择吗？"( www.bluegranite.com/blog/is-azure-sql-data-warehouse-a-good-fit-updated

最后，对新的和现有的 Azure 数据资源的最近趋势、功能更新、可用性版本等有很好的理解，只会帮助你在构建更多 Azure 数据工程解决方案时做出更有教养、更有经验、更深思熟虑的决策。 Azure 更新(https://azure.microsoft.com/en-us/updates/*)*是找到这些更新的好地方，你可以过滤产品类别到数据工程特定的资源。还有许多其他免费的学习资源，从文章到视频教程，都有助于了解 Azure 数据平台的最新动态。

了解如何使用 Azure 管理大数据

有许多数据存储选项可用，鉴于 MPP(大规模并行处理的缩写)和 Spark 之间的相似性，客户经常会问如何选择和决定一个何时适合另一个。

在 20 世纪 90 年代引入 MPP 之前，自 20 世纪 70 年代以来，分析数据库市场一直由 SMP 架构主导。SMP 在规模、可扩展性、工作负载管理、弹性和可用性方面存在缺陷。

MPP 架构解决了 SMP 在性能、可伸缩性、高可用性和读/写吞吐量方面的许多缺点。

然而，MPP 有与成本相关的缺点；它对数据分发有着迫切的需求，添加新节点和重新分发数据需要停机时间，按需扩展计算资源以满足实时处理需求的能力有限，并且由于将存储与计算隔离的限制，有可能出现容量过剩。

Spark 中的 RDD 类似于 MPP 中的分布式表，因为许多 RDD 操作都有等效的 MPP 操作。然而，RDD 确实为实时处理需求提供了更好的选择，并且能够纵向扩展节点以进行批处理，同时还能独立于计算经济高效地扩展存储(数据湖)。此外，对于高度非结构化的数据处理(文本、图像、视频等)，推荐使用 MPP。此外，它还提供了大规模高级分析(人工智能、ML[机器学习]、文本/情感分析等)的能力。

Spark 和 MPP 都有很好的互补性，两者之间的选择取决于用例。Spark 非常适合大数据工程、ETL、AI/ML、实时数据处理和非报告/非查询用例，而 MPP 非常适合需要大量查询和性能功能的大数据报告和 BI 分析平台，因为它基于传统的 RDBMS，并带来了 SQL Server 的最佳功能，如自动查询调优、数据洗牌、分析平台管理的简便性，甚至基于主键的数据分布等等。

虽然 Spark 非常适合大数据处理，但是小文件会导致查询性能下降。此外，Spark 中缺乏数据重组会导致性能问题。由于 Spark 是为大数据批处理而设计的，所以当从报告仪表板查询数据时，它的性能也可能很差。

Azure Synapse Analytics 中的 Apache Spark 试图通过将 Spark 和 MPP 的优势结合到一个统一的分析平台中，来弥合 MPP 和 Spark 之间的鸿沟。

随着围绕大数据的大肆宣传，以及 Azure 中可用的多种大数据产品和资源，大数据主题对许多组织来说变得越来越重要。作为一名 Azure 数据工程师，这些组织将把你视为他们在大数据领域的常驻专家。很好地理解以下内容将是关键:

大数据架构
调整 ADF 复制活动的性能和可伸缩性
创建和配置数据块火花簇
性能调整 ADF 的映射数据流

了解对数据工程师助理的基本要求

有这么多的微软 Azure 认证可供有志成为 Azure 专家的人使用，很明显关于 Azure 还有很多东西要学。数据工程师助理认证路径与 Azure 数据工程师最相关，最初包括通过两个考试，如图 1-3 所示:DP-200(实施 Azure 数据解决方案)和 DP-201(设计 Azure 数据解决方案)。

img/511918_1_En_1_Fig3_HTML.jpg

图 1-3

原始 Azure 数据工程师助理认证考试要求

获得数据工程师助理认证的学员通常要花大约 80 个小时来准备考试，同时有 3 个小时来完成每项考试。微软提供在线(免费)和教师指导的培训项目来准备这些考试。然后可以在线安排考试，并在远程或现场考试中心进行。完成这个认证将有助于获得 Azure 数据工程领域的基础知识。此外，对于那些为微软合作伙伴工作的人来说，成本可以完全报销，甚至可能包括通过考试的奖金，以及成为微软认证 Azure 数据工程师助理的荣耀。

Note

微软最近计划用一个考试 DP-203 取代考试 DP-200 和 DP-201，因为他们正在努力淘汰原来的考试。

图 1-4 展示了微软对 Azure 数据工程师的学习路径，包括使用 Azure 数据资源设计和实现数据的管理、监控、安全和隐私。

img/511918_1_En_1_Fig4_HTML.png

图 1-4

Azure 数据工程师的学习路径

跨 Azure 专业扩展您的知识

虽然 Azure 数据工程师角色涵盖了很多内容，但 Azure 中还有许多其他专业，包括 DevOps、安全、AI、数据科学、Azure 管理、数据库管理等等。虽然拥有跨这些领域的专业化很好，但获取这个知识库可能需要时间，并且它将有助于对 DevOps CI/CD、安全性、基础架构作为代码最佳实践、订阅和计费管理等有一个基本的了解，以及它如何与 Azure 中的数据工程相关联，以从角色基础之外的整体角度来拥抱 Azure。图 1-5 列出了其他可用的相关 Azure 认证及其相应的考试。

img/511918_1_En_1_Fig5_HTML.jpg

图 1-5

其他相关 Azure 认证列表

有了免费的在线视频教程，以及微软丰富的知识库文档，了解端到端架构流程以及它与连接性、安全性、基础设施代码、Azure 管理、DevOps CI/CD 以及计费和成本管理的关系，将有助于增强您对 Azure 的整体理解，帮助您的组织和团队宣传 Azure 数据工程并引领他们的云计算之旅。图 1-6 展示了一个包含多个组件的图，以及从架构的角度来看这些组件是如何联系在一起的。在本书的后续章节中，我们将讨论 DevOps CI/CD 的其他主题。

img/511918_1_En_1_Fig6_HTML.jpg

图 1-6

使用 DevOps CI/CD 的 Azure 数据架构的高级图表

能够解决 Azure 数据平台的商业价值

Azure 数据工程师支持并挑战组织大规模接受数字化转型。通常，Azure 数据工程师会参与到与组织中 C 级高管的对话中，并可能被要求参与涵盖成本、安全性以及 Azure 解决方案如何为组织带来真正商业价值的业务需求文档。能够谈论 Azure 数据和人工智能数字化转型解决方案的商业价值对于许多组织来说是一项宝贵的资产。图 1-7 展示了一些影响云商业价值的因素。

img/511918_1_En_1_Fig7_HTML.jpg

图 1-7

云的商业价值

微软有一门五小时(八个模块)的课程，涵盖了如何“学习微软 Azure 的商业价值” ( https://docs.microsoft.com/en-us/learn/paths/learn-business-value-of-azure/ *)。*此外，为了全面了解如何监控和控制你的 Azure 支出以及优化 Azure 资源的使用，微软提供了以下课程:用 Azure 成本管理+计费来控制 Azure 支出和管理账单 ( https://docs.microsoft.com/en-us/learn/paths/control-spending-manage-bills/ )。

通过 Azure 门户获得 Azure 数据工程实践

获得 Azure 数据工程实践的最佳方式之一是通过 Azure Portal 探索和创建这些资源。为了快速上手 Azure，您可以在 Azure Portal 中创建一个免费试用帐号(https://azure.microsoft.com/en-us/free/)，如图 1-8 所示。本书后续章节中的演示、练习和教程都是实践性的，所以如果你能够按照自己的叙述进行，这可能会补充你的学习经验。如果你没有 Azure 门户帐户，那么这本书作为 Azure 数据工程的权威指南仍然会有所帮助。

img/511918_1_En_1_Fig8_HTML.jpg

图 1-8

立即创建您的 Azure 免费帐户

本书涵盖的 Azure 服务

微软的 Azure 云平台有超过 6000 种服务，并且还在增加。本书旨在探索和深入研究这些资源中特定于数据提取、转换和摄取、开发运维、高级分析和治理的功能。本节将描述本书中涵盖的 Azure 数据服务，以及本书中如何使用这些服务的一些上下文。

数据湖存储第二代

Azure Data Lake Storage Gen2 是一组专用于大数据分析的功能，构建于 Azure Blob Storage 之上。Data Lake Storage Gen2 的一个基本部分是向 Blob 存储添加了一个层次名称空间。分层命名空间将对象/文件组织到目录的层次结构中，以实现高效的数据访问。在第三章中，我将详细讨论如何有效地设计 Azure Data Lake Storage Gen2 账户(ADLS Gen2)。在随后的许多章节中，您将通过 ETL 演示和练习更加熟悉 ADLS Gen2，了解它如何在数据接收和转换管道中用作源和接收器。

数据工厂

Azure Data Factory (ADF)是 Azure 的云 ETL 服务，用于横向扩展的无服务器数据集成和数据转换。它提供了一个无代码的用户界面，用于直观的创作和单一控制台监控和管理。您还可以将现有的 SSIS 包转移到 Azure，并在 ADF 中完全兼容地运行它们。本书的大部分章节，从第四章到第十五章，涵盖了围绕 Azure Data Factory 及其强大功能的深入主题。

摄取和加载

作为接收和加载过程的一部分，Data Factory 支持 90 多个源和接收器。在后续章节中，您将学习如何在 ADF 中创建管道、数据集、链接服务和活动:

**管道:**共同执行任务的活动的逻辑分组。
**活动:**定义对数据执行的操作(例如，复制数据活动、ForEach 循环活动等。)
**数据集:**数据的命名视图，它简单地指向或引用您希望在管道内的活动中使用的数据
**链接服务:**很像连接字符串，它定义了数据工厂连接到外部资源所需的连接信息

在 ADF 中，集成运行时(IRs)是用于提供数据集成功能(如数据流和数据移动)的计算基础设施。ADF 有以下三种 IR 类型:

Azure integration runtime**:**底层基础设施的所有修补、扩展和维护都由微软管理，IR 只能访问公共网络中的数据存储和服务。
自托管集成运行时 : 基础设施和硬件由您管理，您需要解决所有的修补、扩展和维护问题。IR 可以访问公共和私有网络中的资源。
运行 SSIS 引擎的虚拟机允许你本地执行 SSIS 软件包。所有的修补、扩展和维护都由微软管理。IR 可以访问公共和私有网络中的资源。

为转换和聚合映射数据流

映射数据流是 Azure Data Factory 中可视化设计的数据转换。数据流允许数据工程师在不编写代码的情况下开发数据转换逻辑。产生的数据流作为 Azure 数据工厂管道中的活动执行，这些管道使用横向扩展的 Apache Spark 集群。在第 11–15 章中，我们将探讨使用 SCD Type I、大数据湖聚合、增量插入和 Delta Lake 进行数据仓库 ETL 时映射数据流的一些功能。关于映射数据流的功能，还有许多其他用例没有在本书中介绍(例如，动态地将大文件分割成多个小文件、管理小文件问题、解析转换等等)，我鼓励您研究映射数据流的许多其他功能。

此外，可以通过 ADF 的常规复制数据活动中的存储过程活动来转换数据。

在映射数据流时，有三种不同的集群类型可用——通用、内存优化和计算优化:

**通用:**当您打算平衡性能和成本时，请使用默认的通用集群。该集群非常适合大多数数据流工作负载。
**内存优化:**如果您的数据流有许多连接和查找，请使用成本更高的每核内存优化集群，因为它们可以在内存中存储更多数据，并将您可能遇到的任何内存不足错误降至最低。如果在执行数据流时遇到任何内存不足的错误，请切换到内存优化的 Azure IR 配置。
**计算优化:**针对非内存密集型数据转换，如过滤数据或添加派生列，使用每内核价格更便宜的计算优化集群。

争论不休的数据流

Data Factory 中的数据争论允许您在本地构建交互式 Power Query 混搭，然后在 ADF 管道中大规模执行这些混搭。这本书将不会涉及这个介绍部分之外的争论数据流的主题。

计划和监控

对于触发的管道，您可以在 Azure 数据工厂用户体验中计划和监控所有管道运行。此外，您可以创建警报并接收与失败、成功或自定义管道执行状态相关的文本和/或电子邮件。

ADF 限制

目前，ADF 有一些限制。这些限制包括以下内容:

无法将 ForEach 活动或 Switch 活动添加到 If 活动中。
无法嵌套 ForEach 循环、If 和 Switch 活动。
查找活动最多有 5，000 行，最大大小为 4 MB。
无法为模块化调度添加 CRON 功能。

其中一些限制要么在 ADF 产品团队未来增强的路线图上，要么有定制的解决方案和变通办法。例如，单一管道中缺乏模块化调度可以通过利用 Azure 函数、逻辑应用、Apache Airflow 等工具来弥补。确保您有一个具有许多管道的模块化设计，以解决其他限制，例如每个管道 40 个活动的限制。其他限制包括每个管道 100 个排队运行，每个 Azure 集成运行时区域的每个订阅 1000 个并发管道活动运行。

大数据

Azure Databricks 是一个针对微软 Azure 云服务平台优化的数据分析平台。在本书的整个过程中，我们将探索 Databricks，除了它围绕图形分析(第二十一章)和机器学习(第二十三章)等用例的高级分析功能之外，它还是一种数据摄取和转换工具。此外，第十七章将讨论如何通过 Spark compute 使用 Databricks 进行实时物联网分析。

Synapse 分析

Azure Synapse Analytics 与 Databricks 非常相似，是一种企业服务，可以加快跨数据仓库和大数据系统的洞察速度。Azure Synapse 汇集了企业数据仓库中使用的最佳 SQL 技术、用于大数据的 Spark 技术、用于数据集成和 ETL/ELT 的管道，以及与 Power BI、Cosmos DB 和 Azure ML 等其他 Azure 服务的深度集成。在使用 Azure 数据工厂的 ELT 的后续章节中，我们将探讨如何动态加载 Azure Synapse Analytics DW。此外，在第二十二章中，我们将探讨如何开始使用 Azure Synapse 分析工作区示例来提升自助服务和高级分析功能。

对于客户来说，在 Synapse Analytics 和 Databricks 之间做出选择可能是一个挑战。尽管 Databricks 作为 Azure 中基于 Apache Spark 的分布式计算的行业标准已经达到了成熟和受尊重的水平，但 Synapse 正在缓慢但肯定地赶上成为一个统一的数据和分析平台，与其他 Azure 服务的集成可能比 Databricks 更紧密。随着 Synapse Analytics 的功能和产品不断成熟，许多在 Azure 中开始新项目的客户对探索其功能非常感兴趣。

数码宝贝/CD

CI/CD 管道用于自动化持续集成和持续部署的过程。管道通过构建、测试、合并和部署等阶段促进软件交付过程。Azure DevOps Server 是一款微软产品，提供版本控制、报告、需求管理、项目管理、自动化构建、测试和发布管理功能。它涵盖了整个应用生命周期，并支持 CI/CD 等开发运维功能。在第十九章中，我们将探索如何使用 DevOps CI/CD 将数据工厂部署到多个环境，在第二十章中，我们将探索如何使用 DevOps CI/CD 管道将 Azure SQL 数据库部署到多个环境。

物联网中心

物联网中心是一种托管在云中的平台即服务(PaaS)托管服务，充当物联网应用与其管理的设备之间双向通信的中央消息中心。在第十六章中，我们将建立一个物联网中心，它将从设备模拟器收集事件，以促进对即将到来的事件的实时分析和洞察。

在选择物联网中心之前，了解各种物联网中心选项之间的功能差异，并将它们与活动中心进行比较。表 1-1 列出了活动中心和物联网中心选项的功能。

表 1-1

物联网中心与活动中心

物联网能力

物联网中心标准层

物联网中心基础层

活动中心

| | --- | --- | --- | --- | | 设备到云的消息传递 | 是 | 是 | 是 | | 协议:HTTPS，AMQP，AMQP | 是 | 是 | 是 | | 协议:MQTT，MQTT over webSockets | 是 | 是 | | | 每个设备的身份 | 是 | 是 | | | 从设备上传文件 | 是 | 是 | | | 设备供应服务 | 是 | 是 | | | 云到设备的消息传递 | 是 | | | | 设备配对和设备管理 | 是 | | | | 设备流(预览) | 是 | | | | 物联网边缘 | 是 | | |

流分析

Azure Stream Analytics 是一个实时分析和复杂事件处理引擎，旨在同时分析和处理来自多个来源的大量快速流数据。模式和关系可以在从包括设备、传感器、点击流、社交媒体源和应用在内的许多输入源提取的信息中识别。在第十六章中，我们将探索特定于异常检测的流分析的实际使用案例，该案例可用于检测欺诈。

在时间流场景中，对时间窗口中包含的数据执行操作是一种常见的模式。流分析具有对窗口功能的本机支持，使开发人员能够以最少的工作量创作复杂的流处理作业。

Stream Analytics 中提供了以下窗口函数:

滚动窗口允许你将数据分割成不同的时间段(每 10 秒计算每个时区的推文数量)。
会话窗口允许对在相似时间到达的流媒体事件进行分组，并且不过滤任何数据(对彼此在 5 分钟内发生的推文进行计数)。
跳转窗口向后看以确定事件何时发生(每 5 秒，计算最近 10 秒内的推文数量)。
滑动窗口在事件发生时产生输出(过去 10 秒内单个主题的 tweets 计数)。
快照窗口对具有相同时间戳的事件进行分组。您可以通过添加系统来应用快照窗口。时间戳()添加到 GROUP BY 子句中。

商业智能

Power BI 是微软 Power 平台的一部分，是一个提供交互式可视化和商业智能功能的报告平台，它使最终用户能够创建自己的报告和仪表板。在第十六章中，我们将探讨如何构建一个 Power BI 仪表盘，用于实时监控和分析来自设备模拟器的传入物联网事件。

考虑电源 BI 时，有几个选项可供选择。这些选项包括高级版和专业版，它们都有自己的功能和价位。表 1-2 显示了 Power BI Pro 和 Premium 之间的并排比较，有助于根据您的报告需求选择合适的功能。

表 1-2

超级商务智能专业版与高级版

特征

Power BI Pro

Power BI 高级版

| | --- | --- | --- | --- | |

每个用户

单位容量

| | --- | --- | | 协作和分析 | | 移动应用访问 | 是 | 是 | 是 | | 发布报告以共享和协作 | 是 | 是 | | | 分页(RDL)报表 | | 是 | 是 | | 无需每用户许可证即可使用内容 | | | 是 | | 使用 Power BI 报告服务器进行内部报告 | | | 是 | | 数据准备、建模和可视化 | | 模型大小限制 | 1 GB | 100 GB | 400 GB | | 刷新率 | 8/天 | 48/天 | 48/天 | | 连接到 100 多个数据源 | 是 | 是 | 是 | | 使用 Power BI Desktop 创建报告和可视化效果 | 是 | 是 | 是 | | 嵌入 API 和控件 | 是 | 是 | 是 | | ai 视觉 | 是 | 是 | 是 | | 高级人工智能(文本分析、图像检测、自动机器学习) | | 是 | 是 | | XMLA 端点读/写连接 | | 是 | 是 | | 数据流(直接查询、链接和计算实体、增强的计算引擎) | | 是 | 是 | | 分析 Azure 数据湖存储中存储的数据 | | 是 | 是 | | 治理和行政 | | 数据安全和加密 | 是 | 是 | 是 | | 内容创建、消费和发布的指标 | 是 | 是 | 是 | | 应用生命周期管理 | | 是 | 是 | | 多地区部署管理 | | | 是 | | 自带钥匙(BYOK) | | | 是 | | 自动扩展附件可用性(预览) | | | 是 | | 最大存储量 | 10gb/用户 | 100 TB | 100 TB | | 持续集成和部署 | | 部署管道(包括分页报告管理) | | 是 | 是 |

范围

azure without 是一个统一的数据治理服务，通过自动数据发现、敏感数据分类和端到端数据沿袭，创建一个全面、最新的数据环境图，帮助您轻松管理和治理数据。它使数据消费者能够找到有价值、值得信赖的数据，并可以与本书中涵盖的许多服务集成，包括 Synapse Analytics、SQL 数据库、数据工厂等。在第二十四章中，我们将通过一个详细的练习来探索一个实现权限的实际例子。

雪花

雪花是一家云提供商，提供比传统产品更快、更易用、更灵活的数据存储、处理和分析解决方案。雪花数据平台不是建立在任何现有的数据库技术或“大数据”软件平台上，如 Hadoop。相反，雪花结合了一个全新的 SQL 查询引擎和一个为云设计的创新架构。雪花围绕数据共享、数据仓库和数据应用提供了一些非常好的功能。在第十章中，您将了解更多关于如何使用数据工厂和数据块动态加载雪花 DW 的信息。

SQL 数据库

Azure SQL Database 是一种云计算数据库服务(数据库即服务)，由微软 Azure 平台提供，有助于在云中托管和使用关系型 SQL 数据库，而无需安装任何硬件或软件。我们将在本书的许多章节中使用标准 Azure SQL 数据库作为源、接收器和元数据驱动的控制数据库。

购买模型(SQL DTU 与 vCore 数据库)

Azure 中有大量的 SQL 数据库选项，包括 DTU 和 vCore。DTU 和 vCore 是 Azure SQL 的两种不同购买模式，包括计算、内存、存储和 I/O 的变化。Azure Pricing Calculator 可以帮助将成本和功能与适当的 SQL 数据库解决方案相匹配。

DTU 单元是 CPU、内存和读写操作的组合，并且可以在需要更多功率时增加。如果您有一个预配置的资源配置，其中资源消耗在 CPU、内存和 I/O 之间保持平衡，这将是一个很好的解决方案。一旦达到分配资源的限制并遇到节流，您可以增加保留的 dtu 数量，这将导致性能下降或超时。

dtu 的缺点是不能灵活地只扩展特定的资源类型，比如内存或 CPU。正因为如此，你最终可能会为不需要或不使用的额外资源付费。

vCore 模型允许您独立扩展每个资源(CPU、内存、IO)。您可以根据数据库需要多少 GB 的存储空间来扩大和缩小存储空间，还可以扩展核心(vCores)的数量。缺点是不能独立控制内存的大小。此外，需要注意的是，vCore 无服务器计算资源的价格是调配计算资源价格的两倍，因此持续的高负载在无服务器中的成本要高于调配计算资源的成本。vCore 可以使用您在本地环境中获得的 SQL Server 许可证。

部署模型

Azure SQL 数据库有两种可用的部署模型:

单一数据库代表一个完全管理的、隔离的数据库。如果您有需要单一可靠数据源的现代云应用和微服务，您可能会使用此选项。单个数据库类似于 SQL Server 数据库引擎中的包含的数据库。
弹性池是单个数据库的集合，具有一组共享的资源，如 CPU 或内存，单个数据库可以移入和移出弹性池。

服务等级

Azure SQL 数据库有三个可用的服务层:

通用/标准 服务层专为常见工作负载而设计。它提供以预算为导向的平衡计算和存储选项。
关键业务/高级 服务层专为具有高事务率和最低 I/O 延迟的 OLTP 应用而设计。它通过使用几个独立的副本来提供最高的故障恢复能力。
超大规模 服务层专为大数据 OLTP 数据库以及适度扩展存储和计算的能力而设计。

宇宙数据库(Cosmos DB)

Azure Cosmos DB 是一个完全托管的 NoSQL 数据库，用于现代应用开发。个位数毫秒级响应时间和自动即时可扩展性保证了任何规模的速度。通过由 SLA 支持的可用性和企业级安全性来确保业务连续性。在第十八章中，我们将在为 Cosmos DB 建立实时 Synapse 分析链接的背景下探索 Cosmos DB，以了解在 Cosmos DB 中对交易数据进行实时分析的能力。Azure Synapse Link for Azure Cosmos DB 是一种云原生混合事务和分析处理(HTAP)功能，使您能够对 Azure Cosmos DB 中的运营数据运行近实时分析。Azure Synapse Link 在 Azure Cosmos DB 和 Azure Synapse Analytics 之间创建了紧密的无缝集成。

在决定何时为您的数据解决方案选择 SQL 或 NoSQL 数据库时，请确保考虑以下比较因素，如表 1-3 所示。

表 1-3

SQL vs. NoSQL

| |

结构化查询语言

NoSQL

| | --- | --- | --- | | 定义 | SQL 数据库主要称为 RDBMSs 或关系数据库。 | NoSQL 数据库主要被称为非关系数据库或分布式数据库。 | | 设计用于 | 传统的 RDBMS 使用 SQL 语法和查询来分析和获取数据以获得进一步的见解。它们用于 OLAP 系统。 | NoSQL 数据库系统由多种数据库技术组成。这些数据库是应现代应用开发的需求而开发的。 | | 查询语言 | 结构化查询语言 | 没有声明性查询语言 | | 类型 | SQL 数据库是基于表的数据库。 | NoSQL 数据库可以是基于文档、键值对、图形数据库。 | | 计划 | SQL 数据库有一个预定义的模式。 | NoSQL 数据库对非结构化数据使用动态模式。 | | 扩展能力 | SQL 数据库是垂直可伸缩的。 | NoSQL 数据库是水平可伸缩的。 | | 例子 | Oracle、Postgres 和 MS SQL。 | 蒙戈布、里兹、尼欧 4j、卡珊德拉、巴舍。 | | 最适合 | 复杂的查询密集型环境的理想选择。 | 它不太适合复杂的查询。 | | 分级数据存储 | SQL 数据库不适合分层数据存储。 | 更适合分层数据存储，因为它支持键值对方法。 | | 变化 | 一种稍有变化的类型。 | 许多不同的类型，包括键值存储、文档数据库和图形数据库。 | | 开发年份 | 它是在 20 世纪 70 年代开发的，用来处理平面文件存储的问题。 | 开发于 21 世纪后期，旨在克服 SQL 数据库的问题和限制。 | | 一致性 | 它应该被配置为具有很强的一致性。 | 这取决于 DBMS，因为有些 DBMS 提供很强的一致性，如 MongoDB，而有些 DBMS 只提供最终的一致性，如 Cassandra。 | | 最适用于 | RDBMS 是解决 ACID 问题的正确选择。 | NoSQL 最适合用于解决数据可用性问题。 | | 重要 | 应该在数据有效性超级重要的时候使用。 | 当快速数据比正确数据更重要时使用。 | | 最佳选择 | 当您需要支持动态查询时。 | 当您需要根据不断变化的需求进行扩展时使用。 | | 酸与碱模型 | ACID(原子性、一致性、隔离性和持久性)是 RDBMS 的标准。 | BASE(基本可用、软状态、最终一致)是许多 NoSQL 系统的模型。 |

Cosmos DB 中提供了以下 API:

SQL: 为熟悉 SQL 查询的数据用户提供功能。尽管数据是以 JSON 格式存储的，但是可以使用类似 SQL 的查询很容易地对其进行查询。
MongoDB : 现有的 MongoDB 实例可以毫不费力地迁移到 Azure Cosmos DB。
Gremlin : 可用于存储和执行图形数据操作，支持图形建模和遍历的原生能力。
Cassandra**:**为 Apache Cassandra 创建的应用的专用数据存储，用户可以通过 CQL (Cassandra 查询语言)与数据进行交互。
**表:**可以由本地准备的应用使用，以便与 Azure 存储表紧密协作。

未涵盖相关 Azure 服务

有大量相关的 Azure 服务不会在本书中涉及。我鼓励你对这些服务有一个基本的了解，因为它们经常是现代 Azure 数据平台架构堆栈的一部分。本节将对其中一些服务进行基本介绍。

分析服务

Azure Analysis Services 是一个完全托管的平台即服务，在云中提供企业级数据模型。使用高级混搭和建模功能来组合来自多个数据源的数据，定义指标，并在单个可信的表格语义数据模型中保护您的数据。Analysis Services 与 Logic 应用集成良好，也可以与 Azure 数据工厂和 Azure 函数集成，用于模型的增量处理。

在考虑分析服务时，可以将它与 Power BI Premium 进行比较，因为 Power BI Premium 有望提供一系列功能。

目前，在 Power BI Premium 和 Analysis Services 之间进行选择时，可以使用表 1-4 中的以下功能考虑因素来比较利弊。

表 1-4

Power BI Premium 与 Azure 分析服务

| |

Power BI 高级版

Azure 分析服务

| | --- | --- | --- | | 无限强大的商务智能内容查看 | 是 | 不 | | 分页报告 | 是 | 不 | | 数据流 | 是 | 不 | | 人工智能工作量 | 是 | 不 | | 多模式内存管理 | 是 | 不 | | 预聚合表 | 是 | 不 | | 复合模型 | 是 | 不 | | 自动增量刷新 | 是 | 不 | | 大型数据集 | 是 | 是 | | 第三方应用支持 | 是 | 是 | | 自带钥匙 | 是 | 不 | | 向外扩展 | 还没有 | 是 | | 元数据翻译 | 还没有 | 是 | | 对象级安全性 | 还没有 | 是 | | 观点 | 还没有 | 是 |

认知服务

认知服务通过 API 将 AI 带给开发者，并提供各种服务，为 AI 提供看、听、说、搜索、理解的能力，并将决策加速到应用中。所有技能水平的开发人员和那些不具备机器学习专业知识的开发人员都可以轻松地将人工智能功能添加到他们的应用中。

当前可用的认知服务包括以下内容。

决定

异常检测器 : 及早发现潜在问题。
内容版主 : 检测潜在的攻击性或不想要的内容。
度量指导: 监视度量并诊断问题。
个性化器 : 为每个用户创造丰富的个性化体验。

语言

沉浸式阅读器 : 利用视听提示帮助理解文本。
语言理解 : 在 app 中构建自然语言理解。
QnA Maker**:**在数据上创建对话式问答层。
文本分析 : 检测情感、关键短语和命名实体。
翻译器 : 检测并翻译超过 90 种支持的语言。

演讲

语音转文本 : 将音频语音转录成可读文本。
文本到语音 : 将文本转换为逼真的语音，实现自然的界面。
语音翻译 : 将实时语音翻译集成到 app 中。
说话人识别 : 根据音频识别和验证说话。

视力

计算机视觉 : 分析图像和视频中的内容。
自定义视觉 : 自定义图像识别，满足您的业务需求。
人脸 : 检测和识别图像中的人和情感。
表单识别器 : 从文档中提取文本、键值对、表格。
视频索引器 : 分析视频的视听，索引内容。

搜索

搜索**:**Azure Cognitive Search 是一项云搜索服务，为开发人员提供 API 和工具，用于在 web、移动和企业应用中构建丰富的私有异构内容搜索体验。

天蓝色机器学习

Azure 机器学习是一项提供完整数据科学平台的服务。它支持代码优先和低代码体验。Azure Machine Learning Studio 是 Azure Machine Learning 中的一个 web 门户，包含用于项目创作和资产管理的低代码和无代码选项。Azure 机器学习与 Databricks 和 Data Factory 等其他 Azure 服务集成得很好。

三种主要的机器学习技术包括以下内容。

监督学习

算法基于您提供的一组带标签的示例进行预测。当您知道结果应该是什么样子时，这种技术很有用。

无监督学习

算法通过组织数据或描述其结构来为您标记数据集。当您不知道结果应该是什么样时，这种技术很有用。

强化学习

算法从结果中学习，并决定下一步采取什么行动。在每一个动作之后，算法接收反馈，帮助它确定它做出的选择是正确的、中立的还是不正确的。对于需要在没有人类指导的情况下做出许多小决定的自动化系统来说，这是一种很好的技术。

班长

Azure Monitor 通过允许您监控基础架构和网络，帮助您最大限度地提高应用的性能和可用性，并在几秒钟内主动识别问题。

日志分析

通过日志分析，您可以从 Azure Monitor 日志收集的数据中编辑和运行日志查询，并交互式地分析它们的结果。您可以使用日志分析查询来检索符合特定标准的记录，确定趋势，分析模式，并提供对数据的各种见解。

活动中心

Event Hubs 是一个大数据流平台和事件摄取服务。它每秒可以接收和处理数百万个事件。发送到事件中心的数据可以通过使用任何实时分析提供程序或批处理/存储适配器进行转换和存储。

数据共享

Azure 数据共享使组织能够简单安全地与多个客户和合作伙伴共享数据。只需几次点击，您就可以提供新的数据共享帐户、添加数据集，并邀请您的客户和合作伙伴加入您的数据共享。数据提供者总是控制着他们共享的数据。值得注意的是，许多其他的多云平台(如 Snowflake)也提供了强大的数据共享机制和平台，这些机制和平台为被称为数据网格的现代架构模式做出了贡献。数据网格联合了领域数据所有者之间的数据所有权，这些所有者负责将他们的数据作为产品提供，同时还促进了不同位置的分布式数据之间的通信。

逻辑应用

Logic Apps 是一种云服务，当您需要跨企业或组织集成应用、数据、系统和服务时，它可以帮助您计划、自动化和协调任务、业务流程和工作流。

高级应用

Power Apps 是微软 Power 平台的一部分，它是一套应用、服务、连接器和数据平台，提供了一个快速的应用开发环境来构建满足您业务需求的定制应用。这些应用有两种风格:画布应用和模型驱动应用。

画布应用为您提供了一个空白画布，您可以在上面以任何形式拖放组件来设计用户界面。模型驱动的应用基于公共数据服务(CDS)中存储的底层数据，这是一个安全的基于云的存储空间，组织可以使用它来存储业务应用数据。Canvas 应用非常适合构建基于任务或基于角色的应用。另一方面，模型驱动的应用更适合创建端到端的解决方案。

应用服务

Azure App Service 是一个完全托管的 web 托管服务，用于构建 web 应用、移动后端和 RESTful APIs。

SQL 托管实例

SQL 托管实例是 Azure SQL 数据库的一个部署选项，提供了与最新的 SQL Server 本地(企业版)数据库引擎的接近 100%的兼容性，一个解决常见安全问题的本机虚拟网络(VNet) 实现，以及一个有利于本地 SQL Server 客户的业务模型。

数据盒子

Azure Data Box 可以让你以一种快速、廉价和可靠的方式将万亿字节的数据传入和传出 Azure。通过为您提供专有的数据盒存储设备，可以加速安全的数据传输。每个存储设备的最大可用存储容量为 80 TB，并通过区域运营商运输到您的数据中心。该设备有一个坚固的外壳，可以在传输过程中保护数据。

数据同步

Azure SQL 数据同步是一项服务，用于将 Azure SQL 数据库中的表复制到另一个 Azure SQL 数据库或本地数据库。数据可以单向或双向复制。

数据网关

网关作为一座桥梁，在内部数据源与您的逻辑应用、Power BI、Power 应用、Microsoft Flow 和 Analysis Services 之间提供快速数据传输和加密。

成本管理+计费

Azure 成本管理+计费帮助您了解您的 Azure 账单，管理您的计费帐户和订阅，监控和控制 Azure 支出，并优化资源使用。

数字双胞胎

Azure Digital Twins 是一个物联网(IoT)平台，使您能够创建真实世界的事物、地点、业务流程和人员的数字表示。获得洞察力，帮助您开发更好的产品、优化运营和成本，并创造突破性的客户体验。

移动的

Azure Mobile Services 为构建 Windows Store、Windows Phone、Apple iOS、Android 和 HTML/JavaScript 应用提供了可扩展的云后端。

建立关系网

Azure Networking 提供您需要的连接和规模，而不需要您构建或管理光纤。此外，它允许您使用 Azure App Gateway 管理应用的流量，使用 Azure WAF 进行保护，使用 Azure Front Door 定义和监控全局路由，以及使用 Azure Firewall 获得交钥匙防火墙功能。

安全

Azure Security Center 是一个统一的基础设施安全管理系统，它可以为您在云中的混合工作负载提供高级威胁保护，并加强您的数据中心的安全立场。

身份

Azure Active Directory (Azure AD)是微软基于云的身份和访问管理服务，它可以帮助您的员工登录和访问资源，如您公司网络和内部网的应用，以及您自己的组织开发的任何云应用。

忽必烈忽必烈忽必烈忽必烈忽必烈忽必烈忽必烈忽必烈忽必烈忽必烈

Azure Kubernetes Service (AKS)通过将运营开销卸载到 Azure，简化了在 Azure 中部署托管的 Kubernetes 集群。作为托管的 Kubernetes 服务，Azure 处理关键任务，如健康监控和维护。

功能

Azure Functions 是一种按需服务，为 Azure 提供无服务器计算，可用于构建 web APIs，响应数据库变化，处理物联网流，管理消息队列等。此外，函数可以从 Azure 数据工厂管道中调用并集成到其中。

HVR 实时数据复制

HVR for Azure 可用于将 Azure 数据与内部系统集成，以及云间集成和迁移，以及与 Azure 之间的零停机迁移，因为它提供了 Azure 中的实时异构数据复制，并允许您更快地移动大量数据，并以低延迟体验内部和云中的连续数据流。

摘要

在这一章中，我通过强调传统微软 BI 栈的重要性及其在 Azure 现代企业数据和分析平台中的重要作用，介绍了 Azure 数据工程入门技巧。我还介绍了关于性能调优建议的大数据选项的概念，以及 Azure 数据工程师助理认证的基本要求。此外，本章还介绍了在其他 Azure 专业领域扩展知识的价值，以解决 Azure 数据解决方案的商业价值，并向您介绍了通过 Azure Portal 开始 Azure 数据工程的选项。最后，本章向您介绍了本书将涉及的一些 Azure 资源，也讨论了本书将不会涉及的 Azure 资源。

二、数据工厂 vs. SSIS vs .DataBricks

基于来自微软不断增长的 Azure 数据工程和集成生态系统的许多数据集成产品，选择正确的 ELT 工具可能是困难的。从数据工程师到数据分析师的技术专业人员都对为工作选择正确的 ELT 工具感兴趣，并且在确定何时为他们的数据集成项目在 Azure Data Factory (ADF)、SQL Server Integration Services(SSIS)和 Azure Databricks 之间进行选择时，经常需要指导。

SSIS 和 ADF 都是健壮的 GUI 驱动的数据集成工具，设计用于 ELT 和 ETL 工作流、管道和操作，具有到多个源和汇的连接器。SSIS 开发托管在 SQL Server 数据工具中，而 ADF 开发是基于浏览器的体验；两者都有强大的调度和监控功能。借助 ADF 的映射数据流，通过聚合、派生列、模糊查找和其他类似于 SSIS 的可视化设计的数据转换来转换数据是一种允许数据工程师以无代码方式构建 ELT 的能力。ADF 的映射数据流和数据块都利用 Spark 集群来转换和处理 Azure 中的大数据和分析工作负载。本章旨在介绍 ADF、SSIS 和 Databricks 之间的相似之处和不同之处，并提供一些指导来帮助您确定如何在这些不同的数据集成服务之间进行选择。

选择正确的数据集成工具

当在 Azure Data Factory (ADF)和 SQL Server Integration Services(SSIS)之间为一个新项目做出选择时，了解您的组织是否有 Azure 足迹至关重要，如果有，您的数据集成项目能否在 Azure 中托管？如果答案是肯定的，那么 ADF 是这项工作的完美工具。另一方面，如果出于安全原因或因为已经存在 SSIS 生态系统，新项目必须在内部完成，那么 SSIS 是首选工具。通常，组织通过提升和转移场景获得将 SSIS 与 ADF 相结合的好处，在这些场景中，他们利用 ADF 的基于云的计算服务来调度、运行和执行 SSIS 包。

SSIS 是 SQL Server 的几个版本的一部分，价格从免费(Express 和 Developer editions)到每核约 14K 美元(Enterprise)，SSIS 集成运行时节点在 Azure 上每小时 0.84 美元起。也就是说，当使用 SSIS 运行大数据工作负载时，从价格和性能的角度来看，数据量可能会成为一个问题，因为需要购买硬件并经常维护。

Azure Data Factory V2 公司的现收现付计划起价为每 1000 次协调运行 1 美元，每 1000 次自托管 IR 运行 1.5 美元。对于拥有数百个 SSIS 包的组织来说，ADF 将是一个很好的资源，他们不希望在 ADF 中重写这些包，但希望通过利用 Azure 来降低运营成本、提高高可用性和增加可伸缩性。对于这种情况，混合将 SSIS 工作负载转移到云将是理想的选择。

从数据速度的角度来看，除了计划批处理触发器之外，ADF 本身还支持基于事件和滚动窗口触发器，而 SSIS 本身只支持批处理，能够为接近实时的数据流构建自定义触发器。例如，为 SQL Server Integration Services 开发一个文件监视器任务将在处理传入文件之前自动检查它们的目录。

从数据多样性的角度来看，ADF 可以本机连接到 90 多个数据源，从 REST API 到 CRM 系统，再到复杂的 JSON 结构，而 SSIS 更适合结构化数据源，但可以很好地集成到 JSON、REST API 等的第三方或自定义 C#连接器。

从可编程性的角度来看，Azure Data Factory 没有原生编程 SDK，但通过 PowerShell 在没有任何第三方组件的情况下支持自动化，而 SSIS 有编程 SDK，以及通过 BIML 和各种其他第三方组件的自动化。图 2-1 列出了 SSIS 和 ADF 的各种异同。

img/511918_1_En_2_Fig1_HTML.jpg

图 2-1

SSIS 和澳大利亚国防军的能力

何时使用 Azure Data Factory、Azure Databricks 或同时使用两者

对于大数据项目，Data Factory 和 Databricks 都是基于 Azure 云的数据集成工具，在微软 Azure 的数据生态系统中可用，可以处理大数据、批量/流数据和结构化/非结构化数据。两者都有基于浏览器的界面和现收现付的定价方案。

ADF 的映射数据流使用横向扩展的 Apache Spark 集群，这与 Databricks 的底层架构类似，并且在大数据聚合和转换方面表现相似。值得注意的是，映射数据流目前不支持与本地数据源的连接。此外，ADF 的原始复制活动不使用 Spark 集群，而是自托管集成运行时，并允许连接到本地 SQL 服务器。基于这些连接到本地 SQL 服务器的选项，Databricks 确实具有连接到本地数据源的能力，并且可能在大数据工作负载上优于 ADF，因为它利用了 Spark 集群。

从速度的角度来看，ADF 和 Databricks 都支持批处理和流选项。ADF 本身不支持实时流功能，因此需要 Azure 流分析。Databricks 支持结构化流，这是一个 Apache Spark API，可以处理实时流分析工作负载。

从开发界面的角度来看，ADF 的拖放式 GUI 与 SSIS 非常相似，对于熟悉 SSIS 的无代码界面的开发人员来说，这有助于降低学习难度，并且易于使用。此外，Spark 计算环境中的集群类型、核心和节点可以通过 ADF activity GUI 进行管理，以提供更多处理能力来读取、写入和转换数据。

Databricks 确实需要致力于学习 Spark、Scala、Java、R 或 Python 来进行数据工程和数据科学相关的活动。对于传统的 MS SQL BI 开发人员来说，这相当于一个更高的学习曲线，他们已经在 SSIS ETL 过程中扎根了十多年。对于熟悉和熟悉 Databricks 编程语言的数据工程师和科学家来说，Databricks 提供了一种通过笔记本编写和管理代码的整洁和有组织的方法。

ADF 和 Databricks 之间最后也是最显著的区别与它们的主要用途有关。ADF 在许多方面与 SSIS 相似，主要用于 ETL/ELT、数据移动和编排，而 Databricks 可用于数据工程师、数据科学家等之间的实时数据流和协作，以及支持数据科学家设计和开发 AI 和机器学习模型。

例如，Databricks 的 MLflow 通过在可再现的环境中跟踪多个用户之间的实验运行来简化机器学习生命周期，并管理模型到生产的部署。此外，Databricks 支持各种第三方机器学习工具。在随后的章节中，我们将详细介绍 MLflow 的功能以及 Databricks 的各种其他高级特性。

一旦开发了这些数据块模型，就可以将它们集成到 ADF 的数据块活动中，并链接到复杂的 ADF ETL/ELT 管道中，再加上将参数从 ADF 传递到数据块的无缝体验。此外，可以通过 ADF 安排和监控 Databricks 型号。我们将在以后的章节中探讨这些不同的组合管道和参数传递能力。图 2-2 列出了 Databricks 和 ADF 的各种异同。

img/511918_1_En_2_Fig2_HTML.jpg

图 2-2

数据块和 ADF 的功能

摘要

在这一章中，我探讨了 ADF、SSIS 和 Databricks 之间的区别和相似之处，并就何时选择其中一个以及何时一起使用它们提出了建议。该解决方案确实取决于许多不同的因素，如性能、成本、偏好、安全性、功能等。在接下来的章节中，我们将结合使用 ADF 和 Databricks 来演示真实的端到端工作流和管道。

三、设计数据湖存储第二代账户

在设计和架构 Azure Data Lake Storage Gen2 帐户时，有各种注意事项需要考虑。这些考虑因素包括安全性、区域、文件夹和文件结构、数据湖层等等。

本章将解释在设计 Azure 数据湖存储二代帐户时要考虑的各种因素。将涉及的主题包括

数据湖图层及其一些属性
区域、目录和文件的设计注意事项
不同级别的安全选项和注意事项

数据湖层

Azure Data Lake Storage Gen2 提供了将存储库划分为多个层的灵活性。这些层有助于轻松地组织、保护和管理数据湖。图 3-1 概述了在设计数据湖时可以考虑的数据湖中的各个层。这些层包括

环境
存储帐户
文件系统
区域
目录
文件

后续部分将更详细地讨论这些层。

img/511918_1_En_3_Fig1_HTML.png

图 3-1

数据湖中的各种层

环境

环境定义了设计数据湖时需要考虑的顶层。例如，如果需要开发、QA 和生产环境，那么这些环境还必须包括一个或多个 ADLS 第二代存储客户。图 3-2 描述了如何通过 Azure DevOps 管道来管理和编排这个多环境。

img/511918_1_En_3_Fig2_HTML.jpg

图 3-2

由 DevOps 管道协调的开发、QA、生产环境

存储帐户

创建 Azure Data Lake 存储帐户时需要配置几个属性，如图 3-3 所示。此外，在设计存储帐户时，考虑存储帐户的限制和容量对于确定是否拥有多个存储帐户至关重要。存储帐户级别的安全性将由控制平面 RBAC(基于角色的访问控制)定义，更多详细信息将在“安全性”部分介绍。

img/511918_1_En_3_Fig3_HTML.png

图 3-3

存储帐户图层属性

以下是可在存储帐户级别配置的各种属性:

性能层:标准存储帐户由磁性驱动器支持，每 GB 成本最低。它们最适合需要大容量存储或不常访问数据的应用。高级存储客户以固态硬盘为后盾，可提供一致的低延迟性能。它们最适合 I/O 密集型应用，如数据库。此外，所有磁盘都使用高级存储的虚拟机符合 99.9%的 SLA，即使在可用性集之外运行也是如此。存储帐户创建后，不能更改此设置。
帐户种类:通用存储帐户在一个统一的帐户中为 blobs、文件、表和队列提供存储。Blob 存储帐户专门用于存储 blob 数据，并支持选择访问层，这允许您指定访问帐户中数据的频率。选择符合您的存储需求并优化成本的访问层。
复制:您的 Azure 存储帐户中的数据总是被复制，以确保持久性和高可用性。选择符合您持久性要求的复制策略。存储帐户创建后，某些设置不能更改。
时间点恢复:使用时间点恢复将一个或多个容器恢复到以前的状态。如果启用了时间点还原，则还必须启用版本控制、更改馈送和 blob 软删除。
容器的软删除:软删除使您能够恢复先前标记为删除的容器。
文件共享的软删除:软删除使您能够恢复之前标记为删除的文件共享。
blob 的版本控制:使用版本控制来自动维护 blob 的先前版本，以便进行恢复和复原。
Blob 更改反馈:跟踪您帐户中 Blob 的创建和删除更改或修改。
连接方法:您可以通过公共 IP 地址或服务端点公开连接到您的存储帐户，也可以使用私有端点秘密连接到您的存储帐户。
路由偏好:微软网络路由将引导您的流量尽快从源头进入微软云。互联网路由将引导你的流量进入更靠近 Azure 端点的微软云。
需要安全传输:安全传输选项仅允许通过安全连接向存储帐户发送请求，从而增强了存储帐户的安全性。例如，当调用 REST APIs 来访问您的存储帐户时，您必须使用 HTTPS 进行连接。启用“需要安全传输”时，任何使用 HTTP 的请求都将被拒绝。当您使用 Azure 文件服务时，没有加密的连接将失败，包括使用 SMB 2.1、没有加密的 SMB 3.0 以及一些 Linux SMB 客户端的场景。因为 Azure storage 不支持自定义域名的 HTTPS，所以在使用自定义域名时，此选项不适用。
允许公共访问:启用“允许公共访问”时，允许配置容器 ACL(访问控制列表)以允许匿名访问存储帐户内的 blobs。禁用后，无论底层 ACL 配置如何，都不允许匿名访问存储帐户中的 blobs。
分层命名空间:ADLS 第二代分层命名空间加速了大数据分析工作负载，并支持文件级访问控制列表(ACL)。

文件系统

文件系统也称为容器，包含用于日志和数据的分层文件系统。图 3-4 显示了可以配置的容器级属性。数据平面 RBAC 安全级别将在“安全”一节中详细讨论。

img/511918_1_En_3_Fig4_HTML.png

图 3-4

文件系统属性

可以在容器级别配置以下属性:

公开访问级别:指定容器中的数据是否可以公开访问。默认情况下，容器数据是帐户所有者的私有数据。使用“Blob”允许对 Blob 的公共读取访问。使用“容器”允许对整个容器的公共读取和列表访问。
不可变策略:不可变存储提供了以一写多读(WORM)状态存储数据的能力。一旦数据被写入，数据将变得不可擦除和不可修改，并且您可以设置一个保留期，以便在该期限过去之前不能删除文件。此外，可以对数据进行合法保留，以使数据不可擦除和不可修改，直到保留被移除。
存储访问策略:建立存储访问策略用于对共享访问签名进行分组，并为受策略约束的签名提供附加限制。您可以使用存储的访问策略来更改签名的开始时间、到期时间或权限，或者在签名发布后撤销它。

区域、目录和文件

在文件夹和文件层，存储帐户的容器定义区域、目录和文件，类似于图 3-5 中的图示。目录和文件层的安全级别包括 ACL 和 SAS。这些安全级别将在“安全”一节中介绍。

img/511918_1_En_3_Fig5_HTML.png

图 3-5

文件夹和文件级区域、目录和结构

区域

区域定义了数据湖容器中的根级文件夹层次结构。区域可以由存储帐户中的多个容器或容器中的多个文件夹来定义。表 3-1 中的以下示例区域描述了它们的用途和典型用户群。

区域不需要总是驻留在同一个物理数据湖中，也可以作为单独的文件系统或不同的存储帐户驻留，甚至可以驻留在不同的订阅中。如果预计单个区域中的大吞吐量需求超过每秒 20，000 的请求速率，那么不同订阅中的多个存储帐户可能是一个好主意。

表 3-1

数据湖区域、访问和描述

区域

接近

描述

| | --- | --- | --- | | 生的 | 服务帐户(只读) | 没有转换；原始格式；按摄入日期储存 | | 脚手架 | 科学家、工程师 | 一般分期；增量准备 | | 当（博物馆、美术馆、图书馆）馆长 | 分析师、科学家、工程师 | 数据市场；数据湖标准 | | 敏感的 | 选择性访问 | 需要提升和选择性访问的敏感数据 | | 实验室 | 科学家、工程师 | 探索性分析；沙盒区域 | | 瞬态/温度 | 服务帐户 | 支持数据摄取的临时区域。此区域的一个使用情形是，如果您要跨网络移动大量压缩数据，您可能希望解压缩此区域中的数据。数据应该是短暂的，因此被称为瞬态的。 | | 主/参考 | 分析师、科学家、工程师 | 参考数据；存档数据 |

目录(文件夹)

设计数据湖文件夹结构时，图 3-6 中的层次结构针对分析查询进行了优化。每个源系统将被授予数据源文件夹级别的写权限，并指定默认 ACL。这将确保在创建新的日常文件夹和文件时继承权限。

img/511918_1_En_3_Fig6_HTML.jpg

图 3-6

样本数据湖文件夹结构

图 3-6 中所示的分层文件夹格式可以动态参数化并编码到数据块或数据工厂中的 ETL 解决方案中，以根据定义的层次自动创建文件夹和文件。以下代码片段定义了图 3-6 中相同的文件夹结构，并且可以添加到 ADF 管道的配置中。我将在以后的章节中更详细地讨论这一点:

\Raw\DataSource\Entity\YYYY\MM\DD\File.extension

原始层中的敏感子区域可以由顶层文件夹分隔。这将允许您定义单独的生命周期管理策略。以下代码通过在原始区域中引入子区域来进一步对数据源进行分类，展示了先前文件夹结构的变体:

\Raw\General\DataSource\Entity\YYYY\MM\DD\File.extension
\Raw\Sensitive\DataSource\Entity\YYYY\MM\DD\File.extension

文件

Azure Data Lake Storage Gen2 针对基于 Spark 的处理进行了优化，可以更好地处理每个文件大约 65mb-1gb 的较大文件。Azure 数据工厂压缩作业可以帮助实现这一点。此外，Databricks Delta 格式的优化或自动优化功能有助于实现这种压缩。借助事件中心，捕获特性可用于根据大小或定时触发来保存数据。

对于需要读取优化的高性能分析的策划或建模区域，Parquet 和 Databricks Delta 等列格式将是利用谓词下推和列修剪来节省时间和成本的理想选择。

表 3-2 捕获了一些样本文件类型及其功能和大概的压缩率。

表 3-2

数据湖文件类型、功能和压缩率

样本文件类型

能力

压缩比

| | --- | --- | --- | | 欧罗欧欧欧罗欧欧欧欧欧欧欧欧欧欧欧欧欧欧欧欧欧欧欧欧欧欧欧欧欧欧欧欧欧欧欧欧欧 | 可压缩；可拆分的；将架构存储在文件中；适用于非结构化和模式差异数据 | ~91.24% | | 镶木地板 | 分栏格式；可压缩的 | ~97.5% | | CSV/文本 | 几乎在每个组织中都普遍使用；容易解析；通常是批量处理的良好用例；根据使用情况，并不总是 Spark 的最佳选择 | |

表 3-3 列出了一些示例压缩类型及其功能和示例用途。

表 3-3

数据湖压缩类型、功能和示例用途

压缩类型

能力

样本使用

| | --- | --- | --- | | Bzip2 | 高压缩；低速；适用于存档目的，而不是 HDFS 查询 | 归档用例；对于某些文件类型，比 Gzip 压缩得更好 | | Gzip | 中压缩；中速 | 不经常访问的 Avro/Parquet 格式的冷数据 | | 机场 | 高速；较低的压缩；适用于文本文件 | 经常被访问的文本格式的热数据 | | 时髦的 | 高速；低压缩 | 经常访问的 Avro/Parquet 格式的热数据 |

安全

保护数据是设计数据湖的一个重要组成部分。在您的数据湖中可以配置许多权限和控制。以下部分描述了设计数据湖时必须考虑的安全特性。

控制平面权限

控制平面基于角色的访问控制(RBAC)权限旨在仅在 Azure 资源级别授予安全主体权限，不包括任何数据操作。授予用户“读取者”角色不会授予对存储帐户数据的访问权限，因为需要额外的 ACL 或数据平面 RBAC 权限。一种好的做法是将控制平面 RBAC 与文件夹/文件级 ACL 结合使用。

数据平面权限

为安全主体处理数据平面 RBAC 权限时，所有其他 ACL 都将被忽略，并且将阻止对文件和文件夹分配权限。

数据平面 RBAC 权限可以应用到低至存储帐户级别。

可以分配的内置数据平面 RBAC 角色列表包括:

**存储 Blob 数据所有者:**用于为 Azure Data Lake 存储二代设置所有权和管理 POSIX 访问控制。
**存储 Blob 数据贡献者:**用于授予对 Blob 存储资源的读/写/删除权限。
**存储 Blob 数据读取器:**用于授予对 Blob 存储资源的只读权限。
**存储队列数据贡献者:**用于向 Azure 队列授予读/写/删除权限。
**存储队列数据读取器:**用于向 Azure 队列授予只读权限。
**存储队列数据消息处理器:**用于授予查看、检索和删除 Azure 存储队列中消息的权限。
**存储队列数据消息发送方:**用于向 Azure 存储队列中的消息授予添加权限。

类似 POSIX 的访问控制列表

ADLS 第二代中的文件和文件夹级访问由 ACL 授予。无论 ACL 权限如何，控制平面 RBAC 权限都需要与 ACL 结合使用。作为最佳做法，建议在存储帐户/容器级别为安全主体分配一个 RBAC 读者角色，然后在文件和文件夹级别继续执行限制性和选择性 ACL。

这两种类型的 ACL 包括访问 ACL，它控制对文件或文件夹的访问，以及默认 ACL，它由子文件或文件夹中分配的访问 ACL 继承。

共享访问签名

共享访问签名(SAS)支持有限的访问能力，例如用户对容器的读、写或更新。此外，可以应用时间盒来确定签名何时有效。这允许临时访问您的存储帐户，并方便地管理组织内外用户的不同访问级别。

数据加密

数据在移动和静止时都是安全的，ADLS Gen2 自动管理数据加密、数据解密和数据放置。ADLS Gen2 还提供了允许数据湖管理员管理加密的功能。

Azure Data Lake 使用存储在 Azure 密钥库中的主加密密钥来加密和解密数据。用户管理的密钥提供了额外的控制和灵活性，但是除非有令人信服的原因，否则建议将加密留给数据湖服务来管理。

网络传输

配置网络规则后，只有通过指定网络集请求数据的应用才能访问存储帐户。对您的存储帐户的访问可以限制为来自指定 IP 地址或 IP 范围的请求，或者来自 Azure 虚拟网络(VNet)中的子网列表的请求。

可以为您的存储帐户创建一个专用端点，该端点将您的虚拟网络中的专用 IP 地址分配给存储帐户，并保护虚拟网络和存储帐户之间通过专用链路的所有流量。

摘要

在本章中，我介绍了如何设计、实现和保护 Azure Data Lake Storage Gen2 帐户。随着我们在后续章节中开始探索 Azure Data Factory 和 Databricks ELT 模式，对如何实现和保护您的数据湖有了这样的理解和基线，将在我们读取和/或写入数据湖时提供有用的考虑。

四、将 SQL 数据库动态加载到数据湖存储二代

将数据从内部 SQL Server 或 Azure SQL Server 移动到 Azure 数据湖存储的过程已经成为许多企业日益增长的重要需求。拥有大量内部 SQL 服务器以及这些服务器中数百个数据库的客户有兴趣利用 Azure 的数据服务来构建 ELT 和/或 ETL 流程，以便在结构化、分区和可重复的流程中为其 SQL Server 生态系统中的一个或多个服务器完全加载其 Azure 数据湖存储帐户和内部 SQL Server 数据库和表。获取本地关系数据，将它们放入数据湖中的文件，然后使用分布式和多云计算技术(如 Databricks ),这是一种流行的现代架构模式。这种模式通过引入 AI 和 ML 等高级分析功能，使客户能够提高他们在 Azure 云之旅中的成熟度级别，这些功能在他们现有的内部环境中无法轻松实现。

Azure Data Factory 已经成为许多使用 Azure 数据平台的数据工程师选择的重要 ELT 和 ETL 工具。在 ADF 管道中利用动态 SQL 和参数的能力允许无缝的数据工程和可伸缩性。在本章中，您将通过一个实际练习来了解这一过程，即创建一个端到端的数据工厂管道，通过几个利用动态参数的管道将所有本地 SQL Server 对象(包括数据库和表)移动到 Azure Data Lake Storage Gen2。

Azure 必备资源

在第三章中，我讨论了如何设计和实现 Azure 数据湖存储二代账户。本章将基于这些知识，使用数据湖作为最终需要加载到 Azure SQL 数据库中的 transit parquet 文件的目标目的地(sink)。

Parquet 是 Hadoop 生态系统中提供的一种文件格式，与基于行的文件(如 CSV 或 TSV 文件)相比，它旨在提供高性能、高效率的数据平面列存储格式。

Parquet 没有使用简单的嵌套名称空间展平，而是使用记录分解和组装算法。Parquet 具有不同的高效数据压缩方式和编码类型，并针对批量处理复杂数据进行了优化。这种方法最适合需要从大型表中读取某些列的查询。Parquet 只能读取需要的列，这样可以最小化 I/O。

图 4-1 展示了从源 SQL 数据库(Azure 或内部部署)到数据湖的数据流架构。此外，图 4-1 中也描述了所需的部件。

img/511918_1_En_4_Fig1_HTML.png

图 4-1

SQL 数据库到数据湖存储第二代体系结构图

在设计和实现图 4-1 中的架构时，将需要以下 Azure 资源列表。微软提供了许多教程、演示和资源，描述如何通过 Azure 门户 UI 或代码创建这些资源。通读以下列表，了解图 4-1 中列出的每个组件的用途，并在本章构建 ADF 管道之前在您的 Azure 订阅中创建它们:

Azure Data Lake Storage Gen2 :数据将存储在 Azure Data Lake Storage gen 2(ADLS gen 2)中。为此，你需要创建一个 Azure Data Lake Storage Gen2 存储帐户。创建此帐户时，请记住应用第三章中的知识，并确保在帐户创建设置中启用“分层名称空间”，以便创建第二代帐户。
Azure Data Factory V2:Azure Data Factory(ADF)将被用作 ETL 编排工具，因此您可以使用 Azure Data Factory UI 创建数据工厂。
Azure Data Factory 自托管 IR :如果您决定使用本地 SQL Server，那么自托管 IR 将在本地资源和 Azure 服务之间创建一个链接，并允许您连接到 ADF 中的本地数据。还需要注意的是，将托管自托管 IR 的本地机器必须安装有64 位 Java 运行时环境(JRE)来创建 parquet 文件。
本地 SQL Server 或 Azure SQL 数据库:在本练习中，为了简单起见，源数据可以存储在本地 SQL Server 或 Azure SQL 数据库中，并演示端到端架构流程。您可以随时在本地或云中试用 SQL Server以获得 SQL Server 的开发版、快速版或试用版。
Azure SQL 数据库(标准):最后，使用 Azure SQL 数据库(ASQLDB)来存放 ADF 管道参数值、控制和日志表。此外，这个 Azure SQL 数据库也可以用作这个练习的源。在为此流程创建 Azure SQL 数据库时，记得将您的客户端 IP 地址授予 SQL Server 的防火墙规则。此外，请记住允许 Azure 服务和资源访问此服务器。

准备并验证 SQL Server 数据库对象

通过将示例 SQL 数据库导入 Azure SQL 数据库来准备流程。有许多免费的 SQL Server 数据库示例版本可供下载，如 AdventureWorks 或 WideWorldImporters 示例数据库( https://github.com/microsoft/sql-server-samples/tree/master/samples/databases ) *)。*添加示例数据库后，导航到 SQL Server Management Studio (SSMS)并连接到包含两个 OLTP SQL 数据库的本地 SQL Server(或 Azure SQL 数据库)，如图 4-2 所示。

img/511918_1_En_4_Fig2_HTML.jpg

图 4-2

OLTP 源数据库的 SSMS 视图

图 4-3 展开了 WideWorldImporters 数据库中的详细信息，以验证两个数据库中都有表。

img/511918_1_En_4_Fig3_HTML.jpg

图 4-3

源 WideWorldImporters 数据库中的表列表

同样，图 4-4 扩展了 AdventureWorksLT 数据库中的详细信息，以验证两个数据库中都有表。

img/511918_1_En_4_Fig4_HTML.jpg

图 4-4

源 AdventureWorksLT 数据库中的表列表

准备并验证 Azure SQL 数据库对象

接下来，创建一个名为 ADF_DB 的 Azure SQL 数据库以及一个表来存储表名、目录名和进程标志，这将在运行时驱动管道配置。该表也称为控制表。以下代码可用于创建元数据驱动的 ELT 流程所需的基表:

USE [ADF_DB]

go

SET ansi_nulls ON

go

SET quoted_identifier ON

go

CREATE TABLE [dbo].[pipeline_parameter1]
  (
     [parameter_id]  [INT] IDENTITY(1, 1) NOT NULL,
     [table_name]    NVARCHAR NULL,
     [table_catalog] NVARCHAR NULL,
     [process_type]  NVARCHAR NULL,
     PRIMARY KEY CLUSTERED ( [parameter_id] ASC )WITH (pad_index = OFF,
     statistics_norecompute = OFF, ignore_dup_key = OFF, allow_row_locks = on,
     allow_page_locks = on, optimize_for_sequential_key = OFF) ON [PRIMARY]
  )
ON [PRIMARY]

go

图 4-5 显示了创建数据库和表后，这些数据库对象在 SSMS 中的外观。

img/511918_1_En_4_Fig5_HTML.jpg

图 4-5

SSMS ADF _ DB 数据库和表格视图

准备 Azure 数据湖存储二代容器

还需要一个用于根级别层次结构的 ADLS Gen2 容器和文件夹，它将位于服务器级别。因此，根文件夹将是服务器的名称，如图 4-6 所示。

img/511918_1_En_4_Fig6_HTML.jpg

图 4-6

Azure 数据湖容器和根文件夹

图 4-7 确认服务器级文件夹中没有现有数据。

img/511918_1_En_4_Fig7_HTML.jpg

图 4-7

深入根文件夹以确认不存在任何数据。

创建 Azure 数据工厂管道资源

此时，通过导航到 Azure 数据工厂资源并单击 Author & Monitor，开始创建 Azure 数据工厂管道，如图 4-8 所示。

img/511918_1_En_4_Fig8_HTML.jpg

图 4-8

Azure 数据工厂管道创建的作者和监控者

一旦 Azure 数据工厂画布加载，点击“创建管道”，如图 4-9 所示，将创建一个新的管道和空白画布。

img/511918_1_En_4_Fig9_HTML.jpg

图 4-9

创建 Azure 数据工厂管道。

创建自托管集成运行时

如果您将本地服务器链接到 ADF，那么您将需要创建一个自托管集成运行时( https://docs.microsoft.com/en-us/azure/data-factory/create-self-hosted-integration-runtime )。

自托管集成运行时可以在云数据存储和私有网络中的数据存储之间运行复制活动。它还可以针对内部网络或 Azure 虚拟网络中的计算资源分派转换活动。自托管集成运行时的安装需要一台本地机器或私有网络中的一台虚拟机。

创建自托管 IR 后，验证其是否处于“运行”状态，如图 4-10 所示。自托管集成运行时可以在云数据存储和私有网络中的数据存储之间运行复制活动。

img/511918_1_En_4_Fig10_HTML.png

图 4-10

自承载集成运行时处于运行状态

创建链接服务

创建自托管 IR 后，可以创建所有需要的链接服务，包括 SQL Server、Azure SQL 数据库和 Azure Data Lake Storage Gen2，如图 4-11 所示。

img/511918_1_En_4_Fig11_HTML.jpg

图 4-11

已创建的链接服务

创建数据集

ADF 管道将需要三个数据集，如图 4-12 所示。第一个 DS_ADLS2 数据集将连接到接收器 Azure 数据湖存储二代帐户。第二个 DS_ASQLDB_PIPELINE_PARAMETER 数据集将连接到 ADF_DB 中的pipeline_parameter表。最后，第三个 DS_SQLSERVER 数据集将是到源 SQLSERVER 数据库的连接。

img/511918_1_En_4_Fig12_HTML.jpg

图 4-12

已经创建的数据集

S7-1200 可编程控制器

接下来，将 ADLS 第二代数据集配置为拼花格式，如图 4-13 所示。此外，图中是参数化的文件路径连接，允许我们通过“文件路径”部分中列出的YY-MM-DD-HH对数据进行分区。下页也提供了该代码。还要记住将压缩类型设置为“snappy ”,以提高性能。请忽略黄色警告图标，因为这种图标经常出现在动态和参数化的内容中，尤其是当字符串可能比预期的要长时。

img/511918_1_En_4_Fig13_HTML.png

图 4-13

ADLS2 数据集连接属性

作为参考，图 4-13 中的 ADLS 第二代数据集连接属性使用了以下代码。

@concat('rl-sql001/',dataset().table_catalog)

@{item().Table_Name}/@{formatDateTime(utcnow(),'yyyy')}/@{formatDateTime(utcnow(),'MM')}/@{formatDateTime(utcnow(),'dd')}/@{item().Table_Name}@{formatDateTime(utcnow(),'HH')}

ADF 允许您解释字符串中的表达式，以便轻松地将计算、参数和字段值作为字符串的一部分。现在，通过字符串插值，您可以使用类似这些示例的表达式生成超级简单的字符串求值。ADF 管道中实现的许多代码都使用字符串插值。

在该数据集中，添加图 4-14 所示的参数，这些参数将在稍后阶段使用。

img/511918_1_En_4_Fig14_HTML.png

图 4-14

需要 ADLS2 参数

DS_SQLSERVER

接下来，将数据集连接添加到本地 SQL Server。同样将表设置为“None ”,以允许遍历 SQL Server 中的所有表，如图 4-15 所示。

img/511918_1_En_4_Fig15_HTML.jpg

图 4-15

SQL Server 连接属性

DS _ ASQLDB _ 管道参数

最终的数据集将连接到 Azure SQL 数据库中的pipeline_parameter表，如图 4-16 所示。这就完成了继续创建管道所需的所有数据集。

img/511918_1_En_4_Fig16_HTML.jpg

图 4-16

Azure SQL 数据库连接属性

创建 Azure 数据工厂管道

既然已经创建了数据集，那么是时候创建 ADF 管道来将数据移动到湖中了。该管道将利用到内部网络的自托管 IR 连接来转换 SQL 数据库表，并将它们作为 parquet 文件动态加载到数据湖中。此外，此管道将使用 ADF_DB，它是在本章的上一节中创建的。

p _ 插入 _ 基本 _ 表格 _ 信息

P_Insert_Base_Table_Info 管道将查询本地information_Schema.tables作为其来源，以获取表和数据库名称，然后将结果输出到 Azure SQL 数据库中的基本参数表。这个过程的目的是填充pipeline_parameter表，通过它的元数据字段驱动其余的管道。

要创建此管道，请向管道画布添加一个复制活动，如图 4-17 所示，并将源设置为本地 SQL Server 数据集。还要添加本节中包含的查询代码作为源代码。该查询将查询指定的数据库，并列出可在其余过程中使用的表。现在，为每个需要加载和执行管道的数据库手动更改数据库名称。图 4-17 显示了该代码块在复制活动中的位置。

img/511918_1_En_4_Fig17_HTML.png

图 4-17

ADF 复制活动中的源查询

以下是添加到图 4-17 中查询部分的 SQL 查询:

USE adventureworkslt

SELECT Quotename(table_schema) + '.'
       + Quotename(table_name) AS Table_Name,
       table_catalog
FROM   information_schema.tables
WHERE  table_type = 'BASE TABLE'

尽管出于本练习的目的，我们手动将代码粘贴到 ADF 中的 source query 部分，但作为产品开发和部署的最佳实践，我建议在 SQL 数据库环境中创建存储过程，然后在 ADF 源代码中将它们作为存储过程而不是硬编码查询来调用。这将有助于简化代码的维护和管理。此外，在添加活动和设计 ADF 管道时，一定要正确、合理地命名 ADF 活动、管道和数据集。

作为命名 ADF 管道、活动和数据集的最佳实践，我建议遵循表 4-1 中列出的命名约定。

表 4-1

Azure 数据工厂命名约定

| 管道 | PL_ | | 映射数据流 | MDF_ | | 复制活动 | C_ | | 链接服务 | LS_ | | 资料组 | DS_ | | ForEach 循环 | FE_ | | 检查 | L_ | | 存储过程 | SP_ |

现在您已经配置了源，是时候通过切换到 sink 选项卡并选择您在上一节中配置的pipeline_parameter数据集来设置 Sink 了，如图 4-18 所示。

img/511918_1_En_4_Fig18_HTML.jpg

图 4-18

接收器数据集属性

最后，确保源到目的地的映射是准确的，如图 4-19 所示。通常，源和目标之间的一对一命名约定会自动映射，您也可以手动改变这种映射。

img/511918_1_En_4_Fig19_HTML.jpg

图 4-19

ADF 管道源到目标的映射

产生的pipeline_parameter Azure SQL 表应该看起来类似于图 4-20 中所示的插图。

img/511918_1_En_4_Fig20_HTML.jpg

图 4-20

ADF 管道参数表

P_SQL_to_ADLS

在下一部分中，我们的目标是创建 SQL Server 到 ADLS Gen2 数据编排管道。将 Lookup 和 ForEach 活动添加到管道画布中，如图 4-21 所示。

img/511918_1_En_4_Fig21_HTML.png

图 4-21

ADF 管道查找和 ForEach 活动

查找活动只是查找先前管道中填充的管道参数表，如图 4-22 所示的设置。

img/511918_1_En_4_Fig22_HTML.jpg

图 4-22

查找活动的 ADF 管道设置

接下来，在 ForEach 活动的设置中，确保将“Items”设置为以下内容:

@activity('Get-Tables').output.value

此外，确保“顺序”保持未选中状态，以便表可以并行执行。目前，ForEach 活动支持最多 50 个批次的顺序批次计数，留空时默认批次计数为 20，如图 4-23 所示。

img/511918_1_En_4_Fig23_HTML.jpg

图 4-23

ForEach 活动的 ADF 管道设置

此外，在 ForEach 活动的“活动”选项卡中，添加一个复制活动。点击【编辑活动】查看详细信息，如图 4-24 所示。

img/511918_1_En_4_Fig24_HTML.jpg

图 4-24

ForEach 活动的 ADF 管道活动

源数据集是如图 4-25 所示的本地 SQL 服务器。

img/511918_1_En_4_Fig25_HTML.jpg

图 4-25

复制活动的 ADF 管道源和查询

此外，使用下面的动态源查询，它将查找Table_Catalog和Table_Name:

USE @{item().Table_Catalog} SELECT * FROM @{item().Table_Name}

您的接收器数据集将是指定的 ADLS Gen2 帐户容器和文件夹结构，在本章的上一节中已创建为 DS_ADLS2。另外在创建 DS_ADLS2 数据集时已经配置好的数据集属性中添加@{item().Table_Name}和@{item().Table_Catalog}，如图 4-26 所示。

img/511918_1_En_4_Fig26_HTML.jpg

图 4-26

ADF 管道接收器数据集属性

这个管道现在已经完成，可以运行了。

运行数据工厂管道并验证 Azure 数据湖存储二代对象

运行 ADF 管道，从图 4-27 中可以看到，已经为两个数据库创建了两个数据库级文件夹。

img/511918_1_En_4_Fig27_HTML.jpg

图 4-27

ADLS2 数据库级文件夹

还要注意，在您的 ADLS Gen2 帐户中已经创建了适当的表格级文件夹，如图 4-28 所示。

img/511918_1_En_4_Fig28_HTML.jpg

图 4-28

ADLS2 表级文件夹

此外，在钻取文件夹时，请注意图 4-29 中的文件夹被适当地进行了时间分区，并且已经创建了 Parquet 格式的表格级文件。

img/511918_1_En_4_Fig29_HTML.jpg

图 4-29

ADLS 第二代实木复合地板文件

摘要

在这一章中，我介绍了 Azure Data Factory 及其在现代基于云的 ETL 环境中的作用，它是许多使用 Azure 数据平台的数据工程师的首选工具。我还演示了一个实际的例子，说明如何创建一个端到端的数据工厂管道，用几个利用动态参数的管道将所有本地 SQL Server 对象(包括数据库和表)移动到 Azure Data Lake Storage Gen2。

这个练习展示了 ADF 为大容量、高速度和多种多样的数据接收构建动态的、元数据驱动的 ELT 管道的能力。这些管道可以将数据从各种源系统(包括内部数据库)移动到 ADLS Gen2 和其他低成本存储帐户，以进行进一步处理、清理、转换、探索和高级分析使用案例。

五、使用`COPY INTO`加载 Synapse Analytics 专用 SQL 池

在第四章中，我向您展示了如何将 snappy 压缩的 parquet 文件从本地 SQL Server 加载到 Data Lake Storage Gen2 中。本章将向您介绍用于将 ADLS Gen2 的拼花文件完全加载到 Synapse Analytics 专用 SQL 池表中的基本机制和技术。此外，本章还将详细介绍 COPY INTO 命令，该命令提供了许多好处，包括消除了数据加载过程中的多个步骤，并减少了该过程所需的数据库对象的数量。此外，与 PolyBase 不同，COPY INTO 不需要对 sink SQL pool 表的控制访问权限，只需要插入和管理数据库批量操作权限。本章将演示使用复制到命令的一些常见场景。

Azure Synapse Analytics 是一项无限的分析服务，它将数据集成、企业数据仓库和大数据分析结合在一起。它让你可以自由地使用无服务器或专用资源来查询数据。Azure Synapse 将这些世界与统一的体验结合在一起，以摄取、探索、准备、管理和提供数据，满足即时的 BI 和机器学习需求。专用 SQL 池指的是 Azure Synapse Analytics 中提供的企业数据仓库功能。专用 SQL 池代表使用 Synapse SQL 时调配的分析资源的集合。

图 5-1 类似于第四章 4 的图 4-1 所示的架构图，增加了一个 Synapse Analytics 专用 SQL 池作为我们创建并存储在 ADLS Gen2 中的拼花文件的最终目的地。

img/511918_1_En_5_Fig1_HTML.png

图 5-1

第二代数据湖存储到 Synapse 专用 SQL 池架构图

在更详细地研究 COPY INTO 命令之前，您需要为目标 SQL 数据仓库创建一个 Synapse Analytics 专用 SQL 池。此外，您需要创建一个与列名、列顺序和列数据类型相匹配的目标表。您可以使用 Azure Portal 在S ynapse Analytics 中创建一个专用 SQL 池。

`COPY INTO`命令的功能

Synapse Analytics 中的复制命令功能为用户提供了一个简单、灵活、快速的界面，用于 SQL 工作负载的高吞吐量数据接收。COPY INTO命令支持以下参数:

FILE_TYPE = {'CSV' | 'PARQUET' | 'ORC'}
FILE_FORMAT = EXTERNAL FILE FORMAT OBJECT
CREDENTIAL = (AZURE CREDENTIAL)
ERRORFILE = http(s)://storageaccount/container]/errorfile_directory[/]
ERRORFILE_CREDENTIAL = (AZURE CREDENTIAL)
MAXERRORS = max_errors
COMPRESSION = { 'Gzip' | 'DefaultCodec'|'Snappy'}
FIELDQUOTE = 'string_delimiter'
FIELDTERMINATOR =  'field_terminator'
ROWTERMINATOR = 'row_terminator'
FIRSTROW = first_row
DATEFORMAT = 'date_format'
ENCODING = {'UTF8'|'UTF16'}
IDENTITY_INSERT = {'ON' | 'OFF'}

请注意，在这个列表中，可以在每个参数中选择和配置多个选项来定制配置参数。

数据准备技巧

在 ADLS Gen2 中创建拼花文件之前，源数据的数据准备将是一个重要且必要的过程。以下部分提供了一些数据准备技巧，以确保拼花文件可以通过 COPY INTO 命令运行。

技巧 1:删除列名中的空格

在将拼花文件加载到 Synapse Analytics 专用 SQL 池时，经常会出现包含空格的列名的明显问题。如果您正在使用内部源系统，则可以通过创建一个视图并为名称中包含空格的列分配一个别名来处理这些列名称中的空格。

或者，作为一个更复杂的解决方案，可以通过利用 sys columns 和 sys tables 以及下面的脚本来删除所有表中的列名中的空格，从而从多个源表中删除列名空格:

SELECT 'EXEC SP_RENAME ''' + B.NAME + '.' + A.NAME
       + ''', ''' + Replace(A.NAME, ' ', '')
       + ''', ''COLUMN'''
FROM   sys.columns A
       INNER JOIN sys.tables B
               ON A.object_id = B.object_id
                  AND Objectproperty(b.object_id, N'IsUserTable') = 1
WHERE  system_type_id IN (SELECT system_type_id
                          FROM   sys.types)
       AND Charindex(' ', a.NAME) <> 0

如果您的源系统是基于云的源，那么您可能还想考虑使用映射数据流，通过模式、规则和派生列动态地从源列中删除空格。我将在第十一章和第十二章讲述映射数据流的一些附加功能。

技巧 2:将 VARCHAR(MAX)转换为 VARCHAR(4000)

通常，需要考虑数据类型的容量限制和各种其他组件的最大值。请注意，随着时间的推移，这可能会改变并成为受支持的功能。Azure Synapse Analytics 过去不支持 varchar(max)数据类型，因此，您可以根据需要在源系统上使用以下 CONVERT 函数将 varchar(max)数据类型转换为 varchar(4000)数据类型。这可以通过使用以下 CONVERT 函数将源表的数据类型转换为 select 语句或视图来实现:

CONVERT(VARCHAR(length) , varchar_column_name)

SELECT column1                                AS column1,
       column2                                AS column2,
       CONVERT(VARCHAR(4000), varchar_column) AS varchar_column
FROM   dbo.table_name

使用拼花文件复制到

对于大数据工作负载，使用 COPY INTO 命令的首选方法是使用 snappy Parquet 作为定义的 FILE_FORMAT 来读取 parquet (snappy compressed)文件。此外，对于这种情况，请使用托管身份凭据。

以下是 snappy parquet 文件的复制到 SQL 语法，您必须在 Synapse Analytics 专用 SQL 池中运行这些文件:

COPY INTO [Table1]
FROM 'https://lake.dfs.core.windows.net/lake/staging/Table1/parquet/*.parquet'
WITH (
    FILE_FORMAT = [snappyparquet],
    CREDENTIAL = (IDENTITY='Managed Identity')
)

运行该命令，您会注意到 snappy parquet 文件从 ADLS Gen2 复制到 Synapse Analytics 专用的 SQL 池表中，每 100 万行大约需要 30 秒。

此外，在执行数据准备步骤后，以下数据类型没有错误:DATETIME、INT、NVARCHAR(4000)。此外，文本字段中的空整数、逗号和引号对于这种时髦的拼花格式来说不是问题。

使用 CSV 文件复制到

某些情况下可能需要 CSV 格式的源文件。对于这种情况，需要对源数据集进行更多的设置。

首先在数据工厂中配置一个 CSV 数据集，并选择以下连接属性，如图 5-2 所示:

img/511918_1_En_5_Fig2_HTML.png

图 5-2

ADF 中的 CSV 连接配置属性

列分隔符:逗号(，)。
行分隔符:自动检测。
编码:默认(UTF-8)。这需要为 CSV 文件设置。或者，可以在 COPY INTO 命令语法中指定“编码”。
转义字符:' '(注意，该设置允许文本字段中使用双引号和逗号)。
引号字符:双引号(")(注意，该设置允许文本字段中使用双引号和逗号)。
Null 值 : @concat(')(注意这个设置将允许 NULL int 数据类型)。

确保 ADF 源连接选项卡包含所有配置的属性。

图 5-2 中用于动态文件路径的代码如下:

@{item().dst_folder}

@{item().dst_name}/csv/@{formatDateTime(utcnow(),'yyyy-MM-dd')}/ @{item().dst_name}.csv

以下示例代码显示了可在 Synapse Analytics 专用 SQL 池中运行的 CSV 文件的复制到 SQL 语法。请注意，语法中的编码被指定为 UTF8，逗号作为字段终止符:

COPY INTO [Table1]
FROM 'https://sdslake.dfs.core.windows.net/lake/staging/Table1/csv/*.csv'
WITH (
    FILE_TYPE = 'CSV',
    CREDENTIAL = (IDENTITY='Managed Identity'),
    ENCODING = 'UTF8',
    FIELDTERMINATOR = ','
)

类似于使用 snappy Parquet 语法复制到，运行命令并注意 CSV 文件在大约 12 秒内从 ADLS Gen2 复制到 Azure Synapse Analytics 专用 SQL 池表，用于 300K 行。此外，在执行数据准备步骤后，以下数据类型没有遇到错误:DATETIME、INT、NVARCHAR(4000)。此外，在配置 CSV 数据集属性后，文本字段中的空整数、逗号和引号对于此 CSV 文件类型来说不是问题。

使用从数据工厂复制到

要使用数据工厂中的 COPY INTO 命令，请创建 Synapse Analytics 专用 SQL 池数据集，以及包含复制活动的新管道，如图 5-3 所示。将源设置为包含 ADLS Gen2 存储帐户的数据集，将接收器设置为 Synapse Analytics 专用 SQL 池数据集。

img/511918_1_En_5_Fig3_HTML.png

图 5-3

用于复制到配置中的 ADF 源设置

图 5-4 显示了将接收器数据集配置到 Synapse Analytics 专用 SQL 池数据集后将显示的各种复制方法。

img/511918_1_En_5_Fig4_HTML.png

图 5-4

用于复制到配置中的 ADF 接收器设置

请注意，选项包括聚合库、复制命令和批量插入。通过选择“Copy command”，请注意，如果您可能需要在完全重新加载之前截断临时表，可以选择添加预拷贝脚本。此外，还有一个“自动创建表格”选项。当 ADF 管道运行时，来自 ADLS Gen2 的 snappy parquet 文件将从数据工厂管道加载到 Synapse Analytics 专用 SQL 池。

摘要

在本章中，我讨论了用于将 ADLS Gen2 的拼花文件完全加载到 Synapse Analytics 专用 SQL 池表中的基本机制和技术。此外，我还演示了 COPY INTO 命令的用法，该命令消除了数据加载过程中的多个步骤，并减少了该过程所需的数据库对象的数量。

六、将数据湖存储二代文件加载到 Synapse Analytics 专用 SQL 池中

第四章向您展示了如何创建一个动态的、参数化的、元数据驱动的流程，将数据从本地 SQL Server 完全加载到 Azure Data Lake Storage Gen2。本章将演示如何将所有来自 ADLS Gen2 的 snappy 压缩拼花数据文件完全加载到 Azure 专用 SQL 池中。

Azure Data Factory 的 sink Copy 活动允许三种不同的复制方法将数据加载到 Azure 专用 SQL 池中，这是 Azure Synapse 分析生态系统的一部分。本章将使用动态和参数化的 ADF 管道来探索这三种方法:

聚合碱
复制命令
批量插入

首先，要做一些准备工作，创建本章前几节中演示的数据集和管道。在本章的最后，将讨论和演示前面列出的三种加载数据的方法。

重新创建管道参数表

首先在 ADF_DB 中重新创建您在第四章中创建的pipeline_parameter表，使其更加健壮，为本章将要构建的 ADF 管道做准备。

下面是重新创建该表的代码。如果该表存在，它首先删除该表。然后，它重新创建表:

USE [ADF_DB]

go

/****** Object:  Table [dbo].[pipeline_parameter]    ******/
IF EXISTS (SELECT *
           FROM   sys.objects
           WHERE  object_id = Object_id(N'[dbo].[pipeline_parameter]')
                  AND type IN ( N'U' ))
  DROP TABLE [dbo].[pipeline_parameter]

go

/****** Object:  Table [dbo].[pipeline_parameter]  ******/
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_schema]                         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

从代码中列出的列可以看出，已经添加了相当多的新元数据字段，可以通过创建动态数据集和管道在 ADF 管道中捕获这些字段。

创建数据集

在下一节中，为 ADLS Gen2 snappy 压缩拼花文件创建一个源数据集，为 Azure 专用 SQL 池创建一个接收数据集。

首先创建三个数据集，并将数据集命名如下:

ADLS 到突触
ADLS 至突触
DS_SYNAPSE_ANALYTICS_DW

接下来的小节将展示如何创建它们。

ADLS 到突触

首先创建一个带有参数化路径的源 ADLS Gen2 数据集。请记住，pipeline_date已经添加到您在第四章中创建的pipeline_parameter表中，因为pipeline_date捕获了数据加载到 ADLS Gen2 的日期。在此步骤中，您将从 ADLS Gen2 向 Synapse Analytics 专用 SQL 池加载数据。您可以从第四章重新运行管道，或者在此pipeline_date栏中手动输入日期，该日期最好包含最新的文件夹日期。第八章将讨论如何自动将最大文件夹日期插入到此pipeline_date列中，以确保此列始终具有可以传递到参数化 ADF 管道中的最新和最大文件夹日期。这将通过使用在复制活动成功后立即运行的存储过程活动来实现。

图 6-1 说明了如何设置参数化连接属性，以读取由pipeline_parameter控制表驱动的源 ADLS Gen2 拼花目录和文件夹。

img/511918_1_En_6_Fig1_HTML.jpg

图 6-1

ADLS Gen2 拼花文件夹和文件的 ADF 参数化连接

以下是添加到图 6-1 中文件路径部分的代码:

@{item().dst_folder}

@{item().dst_name}/parquet/ @{item().pipeline_date}/ @{item().dst_name}.parquet

图 6-2 显示了如何添加所需的参数。

img/511918_1_En_6_Fig2_HTML.jpg

图 6-2

ADLS Gen2 实木复合地板文件夹和文件的 ADF 参数

图 6-3 中说明了链接的服务细节。Azure 密钥库用于存储凭据机密。当管道开始运行时，如果发现任何身份验证错误，这将与后面的部分相关。

img/511918_1_En_6_Fig3_HTML.jpg

图 6-3

使用 Azure Key Vault 的 ADF 链接服务连接属性

ADLS 至突触

图 6-4 中的 ADF 数据集连接使用托管身份连接凭据。图 6-4 中显示的数据集与上一个数据集的区别在于，这个链接的服务连接不使用 Azure Key Vault。稍后发现错误时，使用此选项来测试密钥存储库连接和非密钥存储库连接并在两者之间切换。

img/511918_1_En_6_Fig4_HTML.jpg

图 6-4

使用托管身份的 ADF 数据集连接属性

以下是添加到图 6-4 中文件路径部分的代码:

@{item().dst_folder}

@{item().dst_name}/parquet/ @{item().pipeline_date}/ @{item().dst_name}.parquet

与之前的数据集类似，添加如图 6-5 所示的参数。

img/511918_1_En_6_Fig5_HTML.jpg

图 6-5

ADLS 第二代实木复合地板文件夹和文件的 ADF 参数-托管身份

链接的服务详情如图 6-6 所示。这里没有使用 Azure Key Vault。同样，当执行管道时，如果发现任何身份验证错误，这将与后面的部分相关。

img/511918_1_En_6_Fig6_HTML.jpg

图 6-6

ADF 链接服务连接

在此部分中，已使用托管身份创建了新的 ADF 链接服务连接。

DS_SYNAPSE_ANALYTICS_DW

接收器连接将连接到 Azure Synapse Analytics 专用的 SQL 池，如图 6-7 所示。此外，参数用于指定来自pipeline_parameter表的模式和表名。当 ForEach 循环活动将用于使用同一个接收数据集创建多个表时，这将是一个很好的特性。

img/511918_1_En_6_Fig7_HTML.png

图 6-7

ADF Synapse DW 链接的服务连接属性

以下是添加到图 6-7 中文件路径部分的代码:

@{item().src_schema}

@{item().dst_name}

创建管道

既然已经创建了数据集，那么还要创建一个新的管道。这样做时，添加一个连接到 ForEach 循环活动的查找活动，如图 6-8 所示。

img/511918_1_En_6_Fig8_HTML.png

图 6-8

包含查找和 ForEach 循环活动的 ADF 管道画布

图 6-9 中显示的查找查询将获得需要加载到 Azure Synapse Analytics 专用 SQL 池的表列表。请注意，目前有一个过滤器应用于查询，它将只包括load_synapse = 1 的记录。

img/511918_1_En_6_Fig9_HTML.jpg

图 6-9

ADF 查找活动查询设置

图 6-9 中包含的代码片段如下:

SELECT [server_name],
       [src_type],
       [src_schema],
       [src_db],
       [src_name],
       [dst_type],
       [dst_name],
       [include_pipeline_flag],
       [partition_field],
       [process_type],
       [priority_lane],
       [pipeline_date],
       [pipeline_status],
       [dst_folder],
       [file_type]
FROM   [dbo].[pipeline_parameter]
WHERE  load_synapse = 1

在 ForEach 循环活动的设置中，添加查找活动的输出值，如图 6-10 所示。记住不要选中“顺序”框，以确保多个表并行处理。如果留空，默认“批次计数”为 20，最大值为 50。

img/511918_1_En_6_Fig10_HTML.png

图 6-10

ADF ForEach 活动设置

还要在 ForEach 循环活动中添加一个 Copy 活动，如图 6-11 所示。单击铅笔图标查看复制活动。

img/511918_1_En_6_Fig11_HTML.png

图 6-11

ADF ForEach 活动

源设置为 DS_ADLS_TO_SYNAPSE，它在链接的服务连接中使用 Azure 密钥库。添加所需的动态参数。请注意，参数是在数据集中定义的。图 6-12 显示了如何以及在哪里添加这些值。

img/511918_1_En_6_Fig12_HTML.jpg

图 6-12

ADF 复制活动源数据集属性

最后，选择 DS_SYNAPSE_ANALYTICS_DW 数据集作为 sink，并在启用“自动创建表”选项的情况下选择“批量插入”，如图 6-13 所示。

img/511918_1_En_6_Fig13_HTML.png

图 6-13

ADF 复制活动接收器数据集属性

基于管道的当前配置，由于它是由pipeline_parameter表驱动的，当(n)个表/记录被添加到管道参数表并且load_synapse标志被设置为 1 时，管道将基于所选择的复制方法并行地执行和加载所有表到 Azure Synapse Analytics 专用 SQL 池。

选择复制方法

现在，我们终于到了选择复制方法的时候了。接收器复制方法有三个选项。批量插入、聚合库和复制命令都是您将在本节中学习使用的选项。

批量插入

SQL Server 提供了 BULK INSERT 语句，以便使用 T-SQL 高效、快速地将大量数据导入 SQL Server，并且只需最少的日志记录操作。

在 ADF 复制活动的 Sink 选项卡中，将复制方法设置为 Bulk insert。如果表不存在，则“自动创建表”会使用源文件中的模式自动创建该表。当接收器指定存储过程或复制活动配备了分段设置时，不支持这种情况。对于这个场景，源文件是一个 parquet snappy 压缩文件，不包含 VARCHAR(MAX)等不兼容的数据类型，因此“自动创建表”选项应该没有问题。

请注意，预复制脚本将在创建表之前运行，因此在使用“自动创建表”的场景中，当表不存在时，首先在没有预复制脚本的情况下运行它，以防止出现错误，然后在为正在进行的完整加载创建表之后，再添加预复制脚本。图 6-14 显示了接收器设置以及添加任何预拷贝脚本(如截断脚本)的位置。

img/511918_1_En_6_Fig14_HTML.png

图 6-14

ADF 复制活动接收器复制前脚本

如果默认的“自动创建表”选项不能满足基于表的自定义分发的分发需求，那么可以利用“添加动态内容”来使用在每个表的管道参数表中指定的分发方法。

以下是添加到图 6-14 中的预拷贝脚本部分的代码:

TRUNCATE TABLE @{item().src_schema}.@{item().dst_name}

运行管道后，使用批量插入复制方法成功，如图 6-15 所示的活动运行监视器所示。

img/511918_1_En_6_Fig15_HTML.png

图 6-15

大容量插入的 ADF 管道成功

图 6-16 显示了批量插入复制管道状态的详细信息。

img/511918_1_En_6_Fig16_HTML.jpg

图 6-16

大容量插入的 ADF 管道运行详细信息

查询 Synapse 表后，注意表中有相同数量的行，如图 6-17 所示。

img/511918_1_En_6_Fig17_HTML.jpg

图 6-17

查询 Synapse Analytics 专用 SQL 池表以验证批量插入 ADF 管道结果

批量插入方法也适用于内部 SQL Server 作为源，Synapse Analytics 专用 SQL 池作为接收器。

聚合碱

使用 PolyBase 是以高吞吐量将大量数据加载到 Azure Synapse Analytics 的有效方式。通过使用 PolyBase 代替默认的 Bulk insert 机制，您将会看到吞吐量的大幅提高。

在下一个练习中，选择 PolyBase ，如图 6-18 所示，以测试该复制方法。

img/511918_1_En_6_Fig18_HTML.png

图 6-18

用于选择聚合库的 ADF 管道接收器数据集属性

PolyBase 将需要托管身份凭证来提供 Azure AD 并授予数据工厂对数据库的完全访问权限。

有关验证访问的更多详细信息，请查看并在 Synapse Analytics 专用 SQL 池上运行以下查询:

select * from sys.database_scoped_credentials
select * from sys.database_role_members
select * from sys.database_principals

此外，当需要创建外部表、数据源和文件格式时，以下查询有助于验证所需的对象是否已创建:

select * from sys.external_tables
select * from sys.external_data_sources
select * from sys.external_file_formats

配置并运行管道后，您可能会注意到管道失败，并出现以下错误:

"ErrorCode=FailedDbOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Error happened when loading data into SQL Data Warehouse.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=External file access failed due to internal error: 'Error occurred while accessing HDFS: Java exception raised on call to HdfsBridge_IsDirExist. Java exception message:\r\nHdfsBridge::isDirExist - Unexpected error encountered checking whether directory exists or not: AbfsRestOperationException: Operation failed: \"This request is not authorized to perform this operation.\", 403, HEAD, https://lake.dfs.core.windows.net/lake     //?upn=false&action=getAccessControl&timeout=90',Source=.Net SqlClient Data Provider,SqlErrorNumber=105019,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=105019,State=1,Message=External file access failed due to internal error: 'Error occurred while accessing HDFS: Java exception raised on call to HdfsBridge_IsDirExist. Java exception message:\r\nHdfsBridge::isDirExist - Unexpected error encountered checking whether directory exists or not: AbfsRestOperationException: Operation failed: \"This request is not authorized to perform this operation.\", 403, HEAD, https://lake.dfs.core.windows.net/lake     //?upn=false&action=getAccessControl&timeout=90',},],'",

在研究错误后，原因是因为源数据集 DS_ADLS_TO_SYNAPSE 的原始 Azure 数据湖存储链接服务正在使用 Azure 密钥库来存储身份验证凭据，这是目前不支持的托管身份验证方法，用于使用 PolyBase 和 Copy 命令。

将源数据集更改为 DS_ADLS_TO_SYNAPSE_MI，它不再使用 Azure 密钥库，请注意图 6-19 中管道使用 PolyBase 复制方法成功。

img/511918_1_En_6_Fig19_HTML.jpg

图 6-19

更改为托管身份后，ADF 管道执行显示成功

复制命令

Copy 命令的功能与 PolyBase 相似，因此 PolyBase 所需的权限对于 Copy 命令来说也是绰绰有余的。有关复制到的更多信息，请重新访问第五章，其中涵盖了权限、用例以及复制到的 SQL 语法的详细信息。图 6-20 显示了如何在 ADF 接收活动中配置复制命令。

img/511918_1_En_6_Fig20_HTML.png

图 6-20

在 ADF 接收器中配置复制命令

与使用 Azure Key Vault 的 PolyBase 复制方法类似，您会注意到以下略有不同的错误消息:

ErrorCode=UserErrorSqlDWCopyCommandError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=SQL DW Copy Command operation failed with error 'Not able to validate external location because The remote server returned an error: (403) Forbidden.',Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=Not able to validate external location because The remote server returned an error: (403) Forbidden.,Source=.Net SqlClient Data Provider,SqlErrorNumber=105215,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=105215,State=1,Message=Not able to validate external location because The remote server returned an error: (403) Forbidden.,},],'", "failureType": "UserError", "target": "Copy data1", "details": []

切换到不使用 Azure Key Vault 的链接服务，注意管道成功，如图 6-21 所示。

img/511918_1_En_6_Fig21_HTML.jpg

图 6-21

ADF 管道执行结果

请注意，一旦创建并测试了 ADF 管道，考虑调度和触发它们是很重要的。触发器根据触发器类型和触发器中定义的标准确定何时触发管道执行。有三种主要类型的 Azure 数据工厂触发器:根据挂钟时间表执行管道的时间表触发器，定期执行管道并保持管道状态的翻转窗口触发器，以及响应 blob 相关事件的基于事件的触发器。此外，ADF 具有警报功能，可监控管道和触发故障，并通过电子邮件、文本等发送通知。

摘要

在本章中，我向您展示了如何创建源 Azure 数据湖存储二代数据集和 sink Synapse Analytics 专用 SQL 池数据集，以及由参数表驱动的 Azure 数据工厂管道。您还学习了如何使用三种复制方法将 snappy 压缩的 parquet 文件加载到 Synapse Analytics 专用的 SQL 池中:批量插入、聚合库和复制命令。本章向您介绍了 Azure Data Factory 中可用的各种摄取选项。

七、动态创建和加载 Synapse Analytics 专用 SQL 池表

在第六章中，您学习了如何使用数据工厂将数据湖文件加载到 Synapse Analytics 专用的 SQL 池中，方法是使用 COPY INTO 命令作为加载选项之一。现在，您已经设计并开发了一个动态流程，可以自动创建 ETL 模式表并将其加载到 Synapse Analytics 专用的 SQL 池中，该 SQL 池具有 snappy 压缩的 parquet 文件，让我们来探索创建表并将其加载到管理模式中的选项，您可以在运行时动态定义模式和分布类型，以创建管理模式表。请注意，在许多现代的基于云的数据架构模式中，暂存和监管更多地发生在数据湖中。然而，本章将展示通过使用几个简单的 ADF 管道将大量表持久化到数据仓库中的巨大能力。尽管 Delta Lake 有很多好处，我们将在第十五章中更详细地介绍，但是客户仍然有兴趣将他们最终的生产就绪的、可信的和精选的数据保存到 SQL 数据仓库中，原因有很多，包括易于分析查询、易于连接 Power BI 和其他报告工具等等。

在第四章中，我介绍了管道参数表的概念来跟踪和控制所有的 SQL Server 表、服务器、模式等等。本质上，这个管道参数表是为了驱动数据工厂编排过程而设置的。为了动态地定义分布类型和管理模式，我将在这个管道参数表中引入几个新列:[distribution_type]、[dst_schema]和[dst_name]。这些新列可以在数据工厂管道中使用，以便从 ETL 模式中动态创建和加载管理的表。

使用 ADF 复制前脚本动态创建和加载新表

将表从源数据湖存储帐户加载到 Synapse Analytics DW 表的 ADF 管道流程将从使用查询查找图 7-1 中所示的管道参数表开始，在查询中您可以适当地指定您的标志和过滤器。

img/511918_1_En_7_Fig1_HTML.jpg

图 7-1

显示从 pipeline_parameter 表中选择的查询的 ADF 查找设置

请注意，图 7-1 添加了源 SQL select 语句作为本练习的查询。作为最佳实践，我建议考虑将这个 SQL 语句转换为存储过程，然后通过将源查询设置为存储过程，通过 ADF 管道调用代码。这将允许在 ADF 环境之外更容易地维护代码。

一旦添加了 source lookup 查询，过滤器pipeline_status = 'success'就可以跟踪文件是否成功到达 lake，这是通过 SQL 存储过程完成的。此外，值得注意的是，这个管道参数中有相当多的列有助于跟踪整个端到端流程的步骤。出于本练习的目的，我们对列[dst_schema]、[dst_schema]和[distribution_type]感兴趣。

将以下代码添加到图 7-1 所示的 ADF 查找活动的源查询部分:

SELECT [id],
       [server_name],
       [src_type],
       [src_schema],
       [src_db],
       [src_name],
       [dst_type],
       [dst_name],
       [include_pipeline_flag],
       [partition_field],
       [process_type],
       [priority_lane],
       [pipeline_date],
       [pipeline_status],
       [load_synapse],
       [load_frequency],
       [dst_folder],
       [file_type],
       [lake_dst_folder],
       [spark_flag],
       [data_sources_id],
       [dst_schema],
       [distribution_type],
       [load_sqldw_etl_pipeline_date],
       [load_sqldw_etl_pipeline_status],
       [load_sqldw_curated_pipeline_date],
       [load_sqldw_curated_pipeline_status],
       [load_delta_pipeline_date],
       [load_delta_pipeline_status]
FROM   [dbo].[pipeline_parameter]
WHERE  load_synapse = 1
       AND pipeline_status = 'success'
       AND include_pipeline_flag = 1
       AND process_type = 'full'
       AND load_frequency = 'daily'

例如，pipeline_ parameter表中的dst_schema和distribution_type可能如下图 7-2 所示。

img/511918_1_En_7_Fig2_HTML.jpg

图 7-2

pipeline_parameter 表中的 dst_schema 和 distribution_type

当您继续进行图 7-3 所示的 ForEach 循环活动时，请确保正确填写设置选项卡中的项目字段，以获得查找活动的输出。

img/511918_1_En_7_Fig3_HTML.png

图 7-3

管道的 ADF ForEach 设置

下面是您需要添加到图 7-3 中 ForEach 循环活动的 Items 字段中的代码:

@activity('L_Get_Tables').output.value

深入 ForEach 活动。图 7-4 中显示了复制数据活动以及所需的数据集属性。

img/511918_1_En_7_Fig4_HTML.jpg

图 7-4

ADF 复制活动源设置和数据集属性

如图 7-4 所示的源数据集属性的名称和值如下:

名字

价值

| | --- | --- | | dst_name | @{item().dst_name} | | src_schema | @{item().src_schema} | | distribution_type | @{item().distribution_type} | | load_sqldw_etl_pipeline_date | @{item().load_sqldw_etl_pipeline_date} | | load_sqldw_etl_pipeline_status | @{item().load_sqldw_etl_pipeline_status} | | load_sqldw_curated_pipeline_date | @{item().load_sqldw_curated_pipeline_date} | | load_sqldw_curated_pipeline_status | @{item().load_sqldw_curated_pipeline_status} | | dst_schema | @{item().dst_schema} |

为 Synapse Analytics 专用 SQL 池 ETL 模式配置源数据集连接，如图 7-5 所示。注意，etl 模式是硬编码的。但是，表名来自于pipeline_parameter表。

img/511918_1_En_7_Fig5_HTML.jpg

图 7-5

ADF 源数据集连接属性

以下是您需要在图 7-5 中的表格连接设置中输入的代码:

etl.@{item().dst_name}

图 7-6 中所示的汇数据集被定义为管理模式，其中您需要参数化目标模式和名称。请注意，源数据集包含源模式所需的参数。但是，接收器数据集不包含任何参数。

img/511918_1_En_7_Fig6_HTML.jpg

图 7-6

ADF 接收器数据集连接属性

以下是您需要输入图 7-6 中的表格连接设置的代码:

@{item().dst_schema}.@{item().dst_name}

创建数据集后，仔细看看拷贝前脚本。请注意，批量插入被用作复制方法，因为数据当前存在于 Synapse Analytics 专用 SQL 池中的 ETL 模式中，并且必须加载到管理的模式中。

如图 7-7 所示，还将表选项设置为“无”，因为表将使用以下预拷贝脚本创建，这基本上是一个动态的 Create Table as Select (CTAS)语法，它引用目标模式和名称以及来自pipeline_parameter表的分布类型，特别是图 7-2 所示的部分。此外，脚本中使用了 SELECT TOP (0 ),因为我们只想使用此步骤创建表，并使用 ADF Copy 活动加载它们。

img/511918_1_En_7_Fig7_HTML.jpg

图 7-7

ADF 复制数据接收器设置

以下是在图 7-7 中的接收器预拷贝脚本中使用的代码:

CREATE TABLE @{item().dst_schema}.@{item().dst_name}
WITH
    (
     CLUSTERED COLUMNSTORE INDEX,
     DISTRIBUTION = @{item().distribution_type}
    )
AS SELECT TOP (0) * FROM etl.@{item().dst_name}
OPTION (LABEL = 'CTAS : @{item().dst_name}');

请注意，图 7-7 中所示的预拷贝脚本只是一个示例，向您展示了可用于预拷贝脚本的功能范围。在这个场景中，我演示了如何在一个预拷贝 SQL 语句中添加使用元数据驱动的管道参数的动态字符串插值函数。最佳实践是，尽量避免将代码直接嵌入到 ADF 管道活动中，并且仅在产品功能存在明显限制时才考虑此类选项，这可能需要使用此类定制配置。

运行管道后，将在 Synapse Analytics 专用 SQL 池中使用适当的目标模式、名称和分布类型创建所有管理的表。

使用 ADF 预复制脚本动态截断和加载现有表

在您可能需要动态截断和加载现有表而不是重新创建表的场景中，通过简单地截断目标表来完成该任务，如图 7-8 所示。这种方法将是与之前管道的唯一显著变化。

img/511918_1_En_7_Fig8_HTML.png

图 7-8

ADF 复制数据接收器设置，预复制脚本更改为截断

以下是在图 7-8 中的接收器预拷贝脚本中使用的代码:

TRUNCATE TABLE @{item().dst_schema}.@{item().dst_name}

使用存储过程动态删除、创建和加载表

最后，让我们探索一个选项，使用 Synapse Analytics 专用 SQL 池中的存储过程来删除和创建管理的表。

管道设计将与之前的管道非常相似，从查找开始，然后流入 ForEach 循环活动，如图 7-9 所示。

img/511918_1_En_7_Fig9_HTML.jpg

图 7-9

包含查找和 ForEach 活动的 ADF 管道流

在 ForEach 循环活动中，有一个名为 CTAS 的存储过程活动，来自图 7-10 所示的管道参数。该存储过程是在 Synapse Analytics 专用 SQL 池中创建的，基于动态 Create Table as Select (CTAS)语句，我将在本节中进一步提供其代码。此外，目的地名称和模式已被定义为存储过程参数，其值来自管道参数表并被传递给存储过程，如图 7-10 所示。

img/511918_1_En_7_Fig10_HTML.png

图 7-10

ADF 存储过程详细信息和参数

它使用可以从pipeline_parameter表传递到存储过程的动态参数，该存储过程将由 ADF 管道调用。这里是在 ForEach 循环活动中调用的[etl].[Ctas_from_pipeline_parameter] ADF 存储过程中使用的源代码，如图 7-10 所示。

SET ansi_nulls ON

go

SET quoted_identifier ON

go

CREATE PROC [etl].[Ctas_from_pipeline_parameter] @schema            VARCHAR,
                                                 @name              VARCHAR,
                                                 @distribution_type VARCHAR
AS
  BEGIN
      DECLARE @table VARCHAR(255)
      DECLARE @table_stage VARCHAR(255)
      DECLARE @table_etl VARCHAR(255)
      DECLARE @sql VARCHAR(max)

      SET @table = @schema + '.' + @name
      SET @table_stage = @table + '_stage'
      SET @table_etl = 'etl.' + @name
      SET @sql = 'if object_id (''' + @table_stage
                 + ''',''U'') is not null drop table '
                 + @table_stage + '; CREATE TABLE ' + @table_stage
                 + ' WITH ( DISTRIBUTION = ' + @distribution_type
                 + ' ,CLUSTERED COLUMNSTORE INDEX ) AS SELECT  * FROM    ' + @table_etl + '; if object_id ('''
                 + @table
                 + ''',''U'') is not null drop table '
                 + @table + '; RENAME OBJECT ' + @table_stage + ' TO '
                 + @name + ';'

      EXEC(@sql)
  END

go

在转向 SSMS，然后编写存储过程脚本之后，请注意前面的脚本执行了以下操作

动态声明和设置distribution_type以及 ETL、管理和模式/表名
删除curated_stage表(如果存在)
将创建阶段表的 SQL 语法设置为从动态设置分布类型的 etl 表中选择所有数据
删除实际/原始管理的表
将curated_stage重命名为实际/原始策划表

在您可能希望重命名原始管理的表而不是删除原始管理的表的情况下，请在 ADF 管道内的存储过程活动中使用以下脚本:

SET ansi_nulls ON

go

SET quoted_identifier ON

go

CREATE PROC [etl].[Ctas_from_pipeline_parameter] @schema            VARCHAR,
                                                 @name              VARCHAR,
                                                 @distribution_type VARCHAR
AS
  BEGIN
      DECLARE @table VARCHAR(255)
      DECLARE @table_stage VARCHAR(255)
      DECLARE @table_drop VARCHAR(255)
      DECLARE @table_etl VARCHAR(255)
      DECLARE @schematable_drop VARCHAR(255)
      DECLARE @sql VARCHAR(max)

      SET @table = @schema + '.' + @name
      SET @table_stage = @table + '_stage'
      SET @table_drop = @name + '_drop'
      SET @table_etl = 'etl.' + @name
      SET @schematable_drop = @table + '_drop'
      SET @sql = 'if object_id (''' + @table_stage
                 + ''',''U'') is not null drop table '
                 + @table_stage + '; CREATE TABLE ' + @table_stage
                 + ' WITH ( DISTRIBUTION = ' + @distribution_type
                 + ' ,CLUSTERED COLUMNSTORE INDEX ) AS SELECT  * FROM    ' + @table_etl + '; if object_id ('''
                 + @table
                 + ''',''U'') is not null rename object '
                 + @table + ' TO ' + @table_drop + '; RENAME OBJECT '
                 + @table_stage + ' TO ' + @name + '; if object_id ('''
                 + @schematable_drop
                 + ''',''U'') is not null drop table '
                 + @schematable_drop + ';'

      EXEC(@sql)
  END

go

最后，需要注意的是，在 Synapse Analytics 专用 SQL 池中，如果您尝试删除或重命名一个表，而该表具有与已创建的实体化视图相关联的依赖项，则删除和重命名脚本可能会失败。

摘要

在本章中，我概述了如何通过在复制活动中使用 ADF 的预复制脚本来动态创建新表并将其加载到 Synapse Analytics 专用 SQL 池中的步骤。此外，我还介绍了如何使用 ADF 的预复制脚本动态截断和加载现有表，最后，我演示了如何使用存储在 Synapse Analytics 专用 SQL 池中的 SQL 存储过程动态删除、创建和加载 Synapse Analytics 专用 SQL 池表。

我在本章中演示的一些示例可能并不完全适用于您的特定场景或用例，但可能有助于进一步加深您对 ADF 功能的理解，以及如何构建和利用定制的动态 SQL 脚本和存储过程来适应可能无法通过 ADF 中的现成功能获得的特定用例。我希望这些例子对你有所帮助。在接下来的两章中，您将进一步了解如何通过构建自定义审计和错误日志记录流程，在 ADF 管道完成运行后，在 SQL 数据库表中捕获和保存与管道相关的指标，从而使您所了解和构建的这些 ADF 管道更加健壮。