使用 SQL Server 2022 数据湖仓的数据科学与 AI

Microsoft SQL Server 2022 是最常被采用的企业级关系型数据库之一。许多全球最成功的公司，无论其所属行业，都拥有重要的 SQL Server 部署。数千家公司数十年来一直依赖 SQL Server。

在过去十年中，Microsoft 在拥抱开源和符合标准的技术方面取得了巨大进步。因此，他们不再花费开发人员的精力构建将用户锁定在自身产品上的功能，而是将大量资源投入到开发新的云原生软件以及增强现有软件的云原生功能中。

其中一项功能是 SQL Server 2022 中查询存储在对象存储中的数据的能力。这通过 S3 连接器和 Polybase 数据虚拟化实现。这为人工智能提供了极大的助力。企业现在可以使用 SQL Server 2022 对存储在对象存储中的海量数据进行建模。数据可以存放在 MinIO 运行的任何地方，并可用于人工智能 - 公有云、私有云、本地、边缘。

人工智能需要大量数据进行训练。大型语言模型 (LLM) 需要相当于整个图书馆的数据进行训练，这些数据来自各种来源，例如内部公司数据库、公共数据库、书籍、在线文章、电子邮件等等，所有这些数据都存储在 MinIO 中。你为模型提供的数据越多，它的价值就越大。 人工智能需要底层软件，这些软件要具有可扩展性、可用性、安全性、性能、弹性和易用性。

MinIO 是数据湖仓的最佳选择，可以为你的数据科学和人工智能工作负载提供动力，因为它可以在任何地方运行，而当企业的数据遍布各地时，这是一个极大的优势。保存在 MinIO 中的数据是持久且经过擦除编码的，而领先的 S3 API 兼容性 意味着每个应用程序都按预期运行。作为 全球最快的对象存储，没有比 MinIO 更好的方法来构建人工智能数据湖仓。

这篇博文将向你展示如何将数据存储在 MinIO 中，并使用 SQL Server 2022 查询这些数据，以用于人工智能程序。它基于 Microsoft 的 SQL Server 2022 工作坊。

创建自签名证书

你需要一个证书（私钥/公钥），以便运行 SQL Server 2022 和 MinIO Server 的操作系统可以相互信任。现在让我们生成它。

使用 openssl（适用于 Linux）或 Windows 创建密钥。

这些说明适用于 Linux。请参阅 文档，了解 Windows 的说明。使用以下命令生成 openssl.cnf 文件，该文件将在生成证书时使用。

cat <<EOF | cat > openssl.cnf
[req]
distinguished_name = s3.example.com
x509_extensions = v3_req
prompt = no

[s3.example.com]
C = US
ST = CA
L = Monterey
O = IT
OU = DBATeam
CN = s3.example.com

[v3_req]
subjectAltName = @alt_names

[alt_names]
IP.1 = 127.0.0.1
DNS.1 = localhost
IP.2 = your-minio-ip-address
DNS.2 = s3.example.com
EOF

使用 openssl 生成证书，使用我们上面创建的配置。

openssl req -x509 -nodes -days 3650 -newkey rsa:2048 -keyout private.key -out public.crt -config openssl.cnf

配置 MinIO 使用证书

我们需要将 MinIO 配置为使用我们刚刚创建的密钥和证书。复制密钥和证书，通过将这些证书放在以下 MinIO 配置路径之一下，为这些证书添加对 MinIO Server 的信任

• Linux: ~/.minio/certs/CAs/

确保在复制证书后重新启动 MinIO 服务器。

配置自签名证书

我们需要告诉运行 SQL Server 2022 的操作系统信任我们为 MinIO 生成的证书。如果你在 Linux 上，请将证书复制到已知 CA 目录中，设置文件的权限，然后重新启动。

mkdir /usr/local/share/ca-certificates/mssql-ca-certificates/
cp public.crt /usr/local/share/ca-certificates/mssql-ca-certificates/
chown 10001:10001 -R /usr/local/share/ca-certificates/mssql-ca-certificates/

在 Windows 中，双击 public.crt 文件并选择安装证书。然后选择本地计算机、将所有证书放在以下存储区中、浏览并选择受信任的根证书颁发机构。

部署 SQL Server 2022

如果你已经部署了 SQL Server 2022，那么你可以随意使用它。

如果没有，请安装以下任一版本，确保 安装 Polybase

• 适用于 Docker 的 SQL Server 2022
• 适用于 Windows 的 SQL Server 2022
• 适用于 Linux 的 SQL Server 2022

你还需要一种与 SQL Server 交互的方法

• Microsoft SQL Server Management Studio (MSSMS)
• Microsoft sqlcmd 实用程序

确保 SQL Server 正在运行，并使用上述任一工具连接到它。

为了确保一切都安装正确，请运行以下查询以获取 SQL Server 版本、版本和产品级别，如下所示。

SELECT  SERVERPROPERTY('Edition') AS SQLEdition,
        SERVERPROPERTY('ProductVersion') AS ProductVersion,
        SERVERPROPERTY('ProductLevel') AS ProductLevel;

安装和配置 MinIO Server

如果你还没有运行它，请在 裸机 或 Kubernetes 上安装 MinIO。

需要 MinIO 客户端 (mc) 来访问 MinIO Server。以下是 在本地安装 mc 的方法。

记下第一次运行 MinIO Server 时显示的凭据和 S3 端点，因为你需要它们来配置 SQL Server 的外部数据源。

使用管理凭据、IP 地址和上一步中提供的端口，使用 Web 浏览器登录 MinIO。从左侧菜单中点击访问密钥，并创建一个新的密钥。最佳实践是使用 访问密钥 为服务提供访问权限，这些密钥仅限于服务所需的最低权限。在本例中，Polybase 需要 ListBucket、ReadOnly 和 WriteOnly。

记下访问密钥和密钥，因为你需要它们来配置 SQL Server 中的数据源。

从左侧菜单中选择存储桶，然后点击创建存储桶。将存储桶命名为 externaltables，保留默认配置，然后点击创建。

导入数据

返回 SQL Server。我们将使用 Microsoft 的 WideWorldImporters (WWI) 示例数据库。WWI 是一个假设的批发新奇商品进口商和分销商，总部位于旧金山湾区。WWI 向零售客户和批发商销售商品。虽然 WWI 的所有客户目前都位于美国，但该公司正在寻求国际扩张。该公司在仓库中储存新奇商品，以及大量的包装材料。最近，WWI 开始销售必须保存在安全温度下的巧克力，因此他们使用来自物联网设备的流式数据监控冷藏室和冷藏卡车的温度。有关更多信息，请参阅 适用于 Microsoft SQL 的 Wide World Importers 示例数据库。

下载 示例数据库到本地目录。下面的脚本使用 c:\sql_sample_databases。

USE master;
GO
DROP DATABASE IF EXISTS WideWorldImporters;
GO
RESTORE DATABASE WideWorldImporters FROM DISK = 'c:\sql_sample_databases\WideWorldImporters-Full.bak' with
MOVE 'WWI_Primary' TO 'c:\sql_sample_databases\WideWorldImporters.mdf',
MOVE 'WWI_UserData' TO 'c:\sql_sample_databases\WideWorldImporters_UserData.ndf',
MOVE 'WWI_Log' TO 'c:\sql_sample_databases\WideWorldImporters.ldf',
MOVE 'WWI_InMemory_Data_1' TO 'c:\sql_sample_databases\WideWorldImporters_InMemory_Data_1',
stats=5;
GO
ALTER DATABASE WideWorldImporters SET QUERY_STORE CLEAR ALL;
GO

使用 MSSMS 或 sqlcmd 在 SQL Server 上运行上述脚本以加载演示数据。

安装和启用 PolyBase

PolyBase 使你能够在 SQL Server 中对外部数据源运行查询。PolyBase 通常与 SQL Server 一起安装，但如果它没有安装，则必须 安装 它。

确认 PolyBase 已安装，1 = 已安装

SELECT SERVERPROPERTY ('IsPolyBaseInstalled') AS IsPolyBaseInstalled;

接下来，在你的配置中启用 PolyBase。即使你已经安装了 PolyBase，也需要启用导出到外部表的功能。

EXEC sp_configure 'polybase enabled', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'allow polybase export', 1;
GO
RECONFIGURE;
GO

使用 Polybase 和 MinIO 配置对外部数据的访问

切换到 WideWorldImporters 数据库的数据库上下文

USE WideWorldImporters;

创建一个数据库主密钥，它用于保护你即将创建的凭据

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'S0methingS@Str0ng!';

使用之前创建的访问密钥创建一个数据库范围的凭据。请注意，密钥包含访问密钥和密钥。

CREATE DATABASE SCOPED CREDENTIAL s3_wwi_cred
WITH IDENTITY = 'S3 Access Key',
SECRET = '<user>:<password>';
GO

在你的 s3 兼容的对象存储上创建一个外部数据源，引用它在网络上的位置 (LOCATION) 以及你刚刚定义的凭据

CREATE EXTERNAL DATA SOURCE s3_wwi
WITH
(
 LOCATION = 's3://<your local IP>:9000'
,CREDENTIAL = s3_wwi_cred
);
GO

如果你看到以下错误，请重新启动 SQL Server 并再次创建外部数据源。

Msg 46530, Level 16, State 11, Line 1 External data sources are not supported with type GENERIC.

创建和查询 Parquet 文件

在这一步中，我们将导出 SQL Server 中 WWI 数据上的查询，并将其保存为 MinIO 上的 Parquet。Parquet 与 ORC 和 AVRO 一样，是人工智能常用的格式。Parquet 是在数据之间移动数据的绝佳文件格式，因为它具有自描述性和列式存储。

创建一个用于 Parquet 的文件格式

USE WideWorldImporters;
GO
IF EXISTS (SELECT * FROM sys.external_file_formats WHERE name = 'ParquetFileFormat')
	DROP EXTERNAL FILE FORMAT ParquetFileFormat;
CREATE EXTERNAL FILE FORMAT ParquetFileFormat WITH (FORMAT_TYPE = PARQUET);
GO

在之前创建的 MinIO 存储桶中创建一个 Parquet 文件

USE WideWorldImporters;
GO
IF OBJECT_ID('wwi_customer_transactions', 'U') IS NOT NULL
	DROP EXTERNAL TABLE wwi_customer_transactions;
GO
CREATE EXTERNAL TABLE wwi_customer_transactions
WITH (
    LOCATION = '/externaltables/',
    DATA_SOURCE = s3_wwi,  
    FILE_FORMAT = ParquetFileFormat
) 
AS
SELECT * FROM Sales.CustomerTransactions;
GO

返回 MinIO 控制台，并验证 Parquet 文件是否已创建。

你也可以为 Parquet 文件（或任何 Parquet 文件）创建临时查询。

USE [WideWorldImporters];
GO
SELECT *
FROM OPENROWSET
	(BULK '/wwi/'
	, FORMAT = 'PARQUET'
	, DATA_SOURCE = 's3_wwi')
as [wwi_customer_transactions_file];
GO

如果我们想从 Parquet 文件开始，并构建一个外部表来处理 SQL Server 中的数据呢？我们甚至可以选择要包含在新的外部表中的列。

USE [WideWorldImporters];
GO
IF OBJECT_ID('wwi_customer_transactions_base', 'U') IS NOT NULL
	DROP EXTERNAL TABLE wwi_customer_transactions_base;
GO
CREATE EXTERNAL TABLE wwi_customer_transactions_base 
( 
	CustomerTransactionID int, 
	CustomerID int,
	TransactionTypeID int,
	TransactionDate date,
	TransactionAmount decimal(18,2)
)
WITH 
(
	LOCATION = '/wwi/'
    , FILE_FORMAT = ParquetFileFormat
    , DATA_SOURCE = s3_wwi
);
GO
SELECT * FROM wwi_customer_transactions_base;
GO

统计信息和元数据

以下 SQL 语句将为外部表中的某列创建统计信息。外部表的查询优化统计信息比内部表和索引视图的统计信息更为有限。查询优化器将表的统计信息或索引视图的统计信息存储在统计信息对象中，该对象包含一个包含元数据的标题、一个包含统计信息表第一关键列值分布的直方图以及一个用于度量跨列相关性的密度向量。 

USE [WideWorldImporters];
GO
CREATE STATISTICS wwi_ctb_stats2 ON dbo.wwi_customer_transactions_base (CustomerID) WITH FULLSCAN;
GO

这些信息在尝试手动优化查询时非常有用。使用以下 SQL 查看统计信息

USE WideWorldImporters;
DBCC SHOW_STATISTICS ("dbo.wwi_customer_transactions", wwi_ctb_stats2);

使用以下 SQL 探索所有外部表和数据源的元数据

USE [WideWorldImporters];
GO
SELECT * FROM sys.external_data_sources;
GO
SELECT * FROM sys.external_file_formats;
GO
SELECT * FROM sys.external_tables;

探索外部数据源文件的元数据

USE [WideWorldImporters];
GO
SELECT TOP 1 wwi_customer_transactions_file.filepath(), 
wwi_customer_transactions_file.filename()
FROM OPENROWSET
	(BULK '/wwi/'
	, FORMAT = 'PARQUET'
	, DATA_SOURCE = 's3_wwi')
as [wwi_customer_transactions_file];
GO

探索 Parquet 文件元数据

USE [WideWorldImporters];
GO
EXEC sp_describe_first_result_set N'
SELECT *
FROM OPENROWSET
	(BULK ''/wwi/''
	, FORMAT = ''PARQUET''
	, DATA_SOURCE = ''s3_wwi'')
as [wwi_customer_transactions_file];';
GO

将它们整合在一起

当外部表与内部操作数据（例如交易、库存和客户）一起使用时，它们最具价值。例如，我们可以将按城市划分的收入增长与人口增长进行比较，以集中资源或检查冷藏卡车温度的趋势，以了解其是否会影响易腐巧克力的销售。这些类型的分析需要连接内部表和外部表。

查询交易的外部表，与客户名称的内部表连接，并按 total_balance 排序。我们想要找出哪些客户有未结余额，然后按未结余额排序，以便我们可以优先处理外展活动。 

USE WideWorldImporters;
GO
SELECT c.CustomerName, SUM(wct.OutstandingBalance) as total_balance
FROM wwi_customer_transactions wct
JOIN Sales.Customers c
ON wct.CustomerID = c.CustomerID
GROUP BY c.CustomerName
ORDER BY total_balance DESC;

以上示例简要介绍了在 SQL Server 2022 中使用外部表。我们运行了一个简单的查询来演示外部表在分析中的价值。外部表的另一个常见用途是 AI。

SQL Server 2022 和 MinIO 对 AI 无限可能

随着完整外部表功能的加入，SQL Server 2022 成为了一个强大的分析和机器学习平台。只需将 SQL 机器学习服务添加到现有安装中。您可以使用 Python、R 或 Java 编写代码来使用此服务。或者使用您选择的机器学习平台读取 SQL Server 外部表。

大量企业数据驻留在 SQL Server 中，甚至更大的数据量驻留在 MinIO 中。SQL Server 为您提供分析和 AI/ML，所有这些都封装在每个人最喜欢的 SQL 中。

将原始数据转化为见解，使业务决策者能够充分了解业务的各个方面，并迅速应对新情况。高级分析和 AI/ML 在现代企业中发挥着重要作用。数据科学将数据资产提炼成知识精华，为业务提供信息和自动化。将 SQL Server 的 SQL 机器学习与 MinIO 相结合，使数据科学家能够识别信贷风险、查明供应链风险、防止客户流失、最大限度地提高客户终身价值、减少设备停机时间，等等。  

立即下载 MinIO 并开始构建您的 AI 基础设施。如往常一样，如果您有任何问题，请随时在 Slack 上联系我们。