配置 PolyBase 以访问 Azure Blob 存储中的外部数据

项目
12/11/2023

本文介绍如何使用 SQL Server 实例上的 PolyBase 来查询 Azure Blob 存储中的外部数据。

注意

APS 目前仅支持标准常规用途 v1 本地冗余 (LRS) Azure Blob 存储。

先决条件

订阅中的 Azure Blob 存储。
Azure Blob 存储中创建的容器。

配置 Azure Blob 存储连接

首先，将 APS 配置为使用 Azure Blob 存储。

运行 sp_configure，将“hadoop 连接”设置为 Azure Blob 存储提供程序。若要查找提供程序的值，请参阅 PolyBase 连接配置。

-- Values map to various external data sources.  
-- Example: value 7 stands for Hortonworks HDP 2.1 to 2.6 on Linux,
-- 2.1 to 2.3 on Windows Server, and Azure Blob Storage  
sp_configure @configname = 'hadoop connectivity', @configvalue = 7;
GO

RECONFIGURE
GO

使用 Appliance Configuration Manager 上的“服务状态”页重启 APS 区域。

配置外部表

若要查询 Azure Blob 存储中的数据，必须定义外部表以在 Transact-SQL 查询中使用。以下步骤介绍如何配置外部表。

在数据库上创建主密钥。这是加密凭据密钥所必需的。
```
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'S0me!nfo';  
```

为 Azure Blob 存储创建数据库范围的凭据。

-- IDENTITY: any string (this is not used for authentication to Azure storage).  
-- SECRET: your Azure storage account key.  
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH IDENTITY = 'user', Secret = '<azure_storage_account_key>';

使用 CREATE EXTERNAL DATA SOURCE 创建外部数据源。

-- LOCATION:  Azure account storage account name and blob container name.  
-- CREDENTIAL: The database scoped credential created above.  
CREATE EXTERNAL DATA SOURCE AzureStorage with (  
      TYPE = HADOOP,
      LOCATION ='wasbs://<blob_container_name>@<azure_storage_account_name>.blob.core.windows.net',  
      CREDENTIAL = AzureStorageCredential  
);

使用 CREATE EXTERNAL FILE FORMAT 创建外部文件格式。

-- FORMAT TYPE: Type of format in Azure Blob Storage (DELIMITEDTEXT,  RCFILE, ORC, PARQUET).
-- In this example, the files are pipe (|) delimited
CREATE EXTERNAL FILE FORMAT TextFileFormat WITH (  
      FORMAT_TYPE = DELIMITEDTEXT,
      FORMAT_OPTIONS (FIELD_TERMINATOR ='|',
            USE_TYPE_DEFAULT = TRUE)

使用 CREATE EXTERNAL TABLE 创建指向 Azure 存储中存储的数据的外部表。在此示例中，外部数据包含汽车传感器数据。

-- LOCATION: path to file or directory that contains the data (relative to HDFS root).  
CREATE EXTERNAL TABLE [dbo].[CarSensor_Data] (  
      [SensorKey] int NOT NULL,
      [CustomerKey] int NOT NULL,
      [GeographyKey] int NULL,
      [Speed] float NOT NULL,
      [YearMeasured] int NOT NULL  
)  
WITH (LOCATION='/Demo/',
      DATA_SOURCE = AzureStorage,  
      FILE_FORMAT = TextFileFormat  
);

在外部表上创建统计信息。

CREATE STATISTICS StatsForSensors on CarSensor_Data(CustomerKey, Speed)

PolyBase Queries

PolyBase 适用于三个函数：

对外部表的即席查询。
导入数据。
导出数据。

下面的查询提供了虚构汽车传感器数据示例。

即席查询

下面的即席查询联接与 Azure Blob 存储中数据的关系。它选择驾驶速度超过 35 mph 的客户，将 SQL Server 中存储的结构化客户数据与 Azure Blob 存储中存储的汽车传感器数据相联接。

SELECT DISTINCT Insured_Customers.FirstName,Insured_Customers.LastName,
       Insured_Customers. YearlyIncome, CarSensor_Data.Speed  
FROM Insured_Customers, CarSensor_Data  
WHERE Insured_Customers.CustomerKey = CarSensor_Data.CustomerKey and CarSensor_Data.Speed > 35
ORDER BY CarSensor_Data.Speed DESC

导入数据

下面的查询将外部数据导入 APS。此示例将快速驾驶员数据导入 APS 以进一步深入分析。为提高性能，它利用了 APS 中的列存储技术。

CREATE TABLE Fast_Customers
WITH
(CLUSTERED COLUMNSTORE INDEX, DISTRIBUTION = HASH (CustomerKey))
AS
SELECT DISTINCT
      Insured_Customers.CustomerKey, Insured_Customers.FirstName, Insured_Customers.LastName,   
      Insured_Customers.YearlyIncome, Insured_Customers.MaritalStatus  
from Insured_Customers INNER JOIN   
(  
      SELECT * FROM CarSensor_Data where Speed > 35   
) AS SensorD  
ON Insured_Customers.CustomerKey = SensorD.CustomerKey

导出数据

以下查询将数据从 APS 导出到 Azure Blob 存储。它可用于将关系数据存档到 Azure Blob 存储，同时仍可对其进行查询。

-- Export data: Move old data to Azure Blob Storage while keeping it query-able via an external table.  
CREATE EXTERNAL TABLE [dbo].[FastCustomers2009] 
WITH (  
      LOCATION='/archive/customer/2009',  
      DATA_SOURCE = AzureStorage,  
      FILE_FORMAT = TextFileFormat
)  
AS
SELECT T.* FROM Insured_Customers T1 JOIN CarSensor_Data T2  
ON (T1.CustomerKey = T2.CustomerKey)  
WHERE T2.YearMeasured = 2009 and T2.Speed > 40;

查看 SSMS 中的 PolyBase 对象

在 SQL Server Data Tools (SSDT) 中，外部表在单独的文件夹“外部表”中显示。外部数据源和外部文件格式位于“外部资源” 下的子文件夹中。

A screenshot of PolyBase objects in the object explorer of SSDT.

下一步

通过 PolyBase 引入数据虚拟化

通过