Azure Synapse Analytics 面向 SQL Server 大师 - 第二部分： 引入数据并创建 ML 模型

在本系列文章的第一篇文章中，我们了解到我们虚构的 CEO 希望通过了解出租车何时何地会发生下一次载客以及目的地来提高效率。然后，我们讨论了如何应对这一挑战。

在这里，我们将在 Azure Synapse Analytics 中构建一个机器学习模型来回答这些问题。基于这些信息，我们将创建一个部署计划，以便公司提前在需求最显著的地方部署车辆。

将我们的数据导入 Azure

要跟随本教程，您需要一个Microsoft Azure 账户。您可以使用您免费的 200 美元额度来探索本系列文章中使用的服务。

首先，我们需要将本地 SQL Server 的 SQL 架构迁移到我们无服务器池中的 Azure SQL 实例。为此，我们使用 Azure Synapse Pathway（在撰写本文时仍处于预览状态），以离线方式将数据仓库架构和数据转换为 Azure 优化格式。

我们使用 SQL Server Management Studio (SSMS) 导出我们的架构脚本。当我们启动 Azure Synapse Pathway 时，我们会找到文件并选择一个输出目录。我们也可以从 Netezza 和 Snowflake 进行转换。

Azure Synapse Pathway 的一个好处是报告输出，它显示了在翻译过程中遇到的问题。它还显示了使用该应用程序大约能节省多少工作时间。

当我们点击错误消息时，它会给出更好的解释

一旦我们纠正了错误，我们就可以在 Azure SQL 无服务器数据库上执行这些脚本。

对于本教程，我们使用一个笔记本将 SQL 数据转换为 Parquet 格式。示例会反转并上传行程数据。我们也可以在这个脚本中导入 Azure 公共数据集，例如纽约黄色出租车行程。

首先，我们打开 Azure Data Studio 并连接到我们的 SQL Server。打开上面链接中的笔记本，并选择 Python 3 内核。Azure Data Studio 可能会在必要时安装 Python。

我们需要导入 PyArrow 包才能使用 Apache Arrow 和 Parquet 库。

要导入 PyArrow 包，请在右上角单击包管理器。

在 **添加新** 选项卡中，**搜索** "pyarrow" 并单击 **安装**。

一旦 Azure Data Studio 安装了 PyArrow，我们就可以对我们的笔记本进行必要的更改。我们将指向正确的表并执行笔记本以将数据导出为 Parquet 格式。

接下来，我们登录 Azure 并单击我们存储账户中的 **容器**。在我们的容器中，我们创建一个新目录来存放我们的 Parquet 文件。我们为此创建一个用户文件夹。

我们现在可以从 Azure 中选定的目录上传我们的文件。

以下对话框打开，供我们选择本地文件

当我们选择一个文件，单击 **上传**，并且过程完成时，我们的 Parquet 文件将在目录中可用。

我们可以通过创建一个指向 Parquet 数据的外部表来通过 SQL 访问数据。然后，我们可以使用一个脚本导入上传的数据。

创建需求数据

这个脚本将数据从源表提取到我们想要的视图形式。这一步有点多余，但为了清晰起见，我们将其包含在此处。下一个脚本演示了如何使用 `CREATE EXTERNAL TABLE AS SELECT (CETAS)` 将此数据复制到外部表中。不要低估 CETAS 的实用性。我们可以在单个步骤中完成数据导入、格式化、聚合、过滤和操作。

创建机器学习模型

我们正在为需要紧急答案的问题创建一个快速解决方案。我们决定使用 Azure 中的自动机器学习解决方案，完全无需编码。

我们将按我们提出的形式格式化需求数据，然后使用 Auto ML 回归模型。我们的第一个模型预测客户的行程，第二个模型确定每个行程将发生多少次。

为了支持这两个模型，我们创建了两个包含相同行程数据的表。小时路线需求表列出了行程索引的发生次数，行程索引是行程起点和终点区域的唯一引用。小时区域需求表将一个区域的行程数量分配到小时桶中。

在第一个表中，我们将目标设为路线列，连接相关预测区域和桶中的所有行程。为此，我们使用分类模型。连接的行程索引形成一个类别，其余列形成输入数据。

我们为第二个表使用回归模型。我们将预测构成预测桶和行程索引需求的总行程数量。我们删除了对我们分类列中的信息没有贡献的列。

首先，我们需要在 Spark 中使需求表可用，这在我们在 Azure Synapse 工作室集成运行时中启用交互式创作时非常简单。为此，我们打开一个连接到 Spark 池的新笔记本，并执行以下命令

%%pyspark
spark.sql("CREATE TABLE IF NOT EXISTS nyctaxifcst.hourlyzonedemand USING Parquet LOCATION '/user/zonedemand'")
spark.sql("CREATE TABLE IF NOT EXISTS nyctaxifcst.hourlyroutdemand USING Parquet LOCATION '/user/routedemand'")

这些命令在我们的 Spark 数据库中创建一个表，指向我们 SQL 数据库中的相同数据。

如果您是 Azure 笔记本的新手，您可能不知道魔术命令。当我们创建一个笔记本时，我们可以决定使用哪个内核：Python、Scala、Spark 或 C#。我们在单元格的第一行输入我们的魔术命令，在本例中是 `%%pyspark`。无论我们的默认内核是什么，我们的单元格都将使用魔术命令指示的内核的语言。

现在我们拥有将自动机器学习任务提交到 Apache Spark 所需的所有内容。在 Azure Synapse Analytics 数据中心，我们打开 Spark 数据库表，然后右键单击其中一个需求表。从菜单中，选择 **机器学习**，然后选择 **使用新模型进行丰富**。

配置对话框将打开。我们只需要配置我们要预测的列。在我们的一个模型中，我们希望预测需求，而在另一个模型中，我们预测行程索引计数。

现在，我们选择一个模型。

尽管这些数据是时间序列数据，但我们选择回归模型。我们不确切知道自动 ML 模型做了什么。我们知道我们的数据有月度、季度和年度季节性模式，我们不希望我们的模型忽略像星期几和一天中的小时这样的模式。因此，我们选择回归模型。时间序列模型可能效果相同甚至更好，但对于我们 CEO 的紧急项目，我们没有时间进行实验。

在选择 **回归** 模型后，我们将 **主要指标** 设置为 **标准化平均绝对误差**，因为我们发现它不太容易出错。模型将运行三个小时，并且在最后期限为第二天的情况下，我们没有时间重新启动。

 

此时，我们可以提交模型，或者打开一个笔记本以进行额外的配置（或出于兴趣）。我们将提交作业，并在三个小时后重新查看。

后续步骤

我们已经使用我们一直拥有的 SQL 技能对数据进行了初步清理。然后，我们将数据分组并将其呈现给 ML 模型，到目前为止这是一个简单的过程。

Azure Synapse Analytics 拥有广泛的工具集，对于新用户来说可能有点令人生畏。我们甚至还没有触及管道和数据转换。Azure Synapse Analytics 将 Azure 中大量可用的资源整合在一起，使其对 IT 部门的大部分人群都有意义。

在这个项目中，剩下的就是使用我们的预测并将部署计划提供给 CEO。继续阅读本系列的第三篇也是最后一篇文章，了解如何查看和访问模型数据。

要了解 Azure Synapse Analytics 如何帮助您的组织充分利用数据，您还可以注册参加 Microsoft 的Azure Synapse Analytics 实操培训系列。