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

适用于： SQL Server（仅限 Windows） Azure SQL 数据库 Azure Synapse Analytics 分析平台系统 (PDW)

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

先决条件

如果尚未安装 PolyBase，请参阅 PolyBase 安装。 这篇安装文章介绍了安装的先决条件。

SQL Server 2022

在 SQL Server 2022 (16.x) 中，将外部数据源配置为在连接到 Azure 存储时使用新连接器。 下表汇总了更改：

外部数据源	源	功能
Azure Blob 存储	wasb[s]	abs
ADLS Gen 2	abfs[s]	adls

配置 Azure Blob 存储连接

首先，配置 SQL Server PolyBase 以使用 Azure Blob 存储。

1. 使用 services.msc 重启 SQL Server。 重启 SQL Server 会重启这些服务：

   • SQL Server PolyBase 数据移动服务
   • SQL Server PolyBase 引擎

   

配置外部表

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

1. 在数据库上创建主密钥。 需要主密钥来加密凭据机密。

   CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';  
   

2. 使用共享访问签名 (SAS) 为 Azure Blob 存储创建数据库范围的凭据；IDENTITY 可以是任何内容，因为不会使用它。

   CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
   WITH
   IDENTITY = 'SHARED ACCESS SIGNATURE',
   -- Remove ? from the beginning of the SAS token
   SECRET = '<azure_shared_access_signature>' ;
   

3. 使用 CREATE EXTERNAL DATA SOURCE 创建外部数据源。 请注意，通过 WASB[s] 连接器连接到 Azure 存储时，使用共享访问签名 (SAS) 进行身份验证。

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

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

   -- FORMAT TYPE: Type of format in Hadoop (DELIMITEDTEXT,  RCFILE, ORC, PARQUET).
   CREATE EXTERNAL FILE FORMAT TextFileFormat WITH (  
         FORMAT_TYPE = DELIMITEDTEXT,
         FORMAT_OPTIONS (FIELD_TERMINATOR ='|',
               USE_TYPE_DEFAULT = TRUE))  
   

5. 使用 CREATE EXTERNAL TABLE 创建指向 Azure 存储中存储的数据的外部表。 本示例中的外部数据包含汽车传感器数据；LOCATION 不能为 /，而 /Demo/ 在此示例中之前不需要存在。

   -- 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  
   );  
   

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

   CREATE STATISTICS StatsForSensors on CarSensor_Data(CustomerKey, Speed)  
   

PolyBase Queries

PolyBase 适用于三个函数：

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

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

即席查询

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

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  
OPTION (FORCE EXTERNALPUSHDOWN);   -- or OPTION (DISABLE EXTERNALPUSHDOWN)  

使用 PolyBase 导入数据

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

SELECT DISTINCT
      Insured_Customers.FirstName, Insured_Customers.LastName,   
      Insured_Customers.YearlyIncome, Insured_Customers.MaritalStatus  
INTO Fast_Customers from Insured_Customers INNER JOIN   
(  
      SELECT * FROM CarSensor_Data where Speed > 35   
) AS SensorD  
ON Insured_Customers.CustomerKey = SensorD.CustomerKey  
ORDER BY YearlyIncome  
  
CREATE CLUSTERED COLUMNSTORE INDEX CCI_FastCustomers ON Fast_Customers;  

使用 PolyBase 导出数据

以下查询将数据从 SQL Server 导出到 Azure Blob 存储。 首先启用 PolyBase 导出。 然后，在向目标导出数据之前，为目标创建一个外部表。

-- Enable INSERT into external table  
sp_configure 'allow polybase export', 1;  
reconfigure  
  
-- Create an external table.
CREATE EXTERNAL TABLE [dbo].[FastCustomers2009] (  
      [FirstName] char(25) NOT NULL,
      [LastName] char(25) NOT NULL,
      [YearlyIncome] float NULL,
      [MaritalStatus] char(1) NOT NULL  
)  
WITH (  
      LOCATION='/old_data/2009/customerdata',  
      DATA_SOURCE = HadoopHDP2,  
      FILE_FORMAT = TextFileFormat,  
      REJECT_TYPE = VALUE,  
      REJECT_VALUE = 0  
);  

-- Export data: Move old data to Hadoop while keeping it query-able via an external table.  
INSERT INTO dbo.FastCustomer2009  
SELECT T.* FROM Insured_Customers T1 JOIN CarSensor_Data T2  
ON (T1.CustomerKey = T2.CustomerKey)  
WHERE T2.YearMeasured = 2009 and T2.Speed > 40;  

使用此方法执行 PolyBase 导出时，可能会创建多个文件。

查看 SSMS 中的 PolyBase 对象

在 SSMS 中，外部表在单独的文件夹“外部表” 中显示。 外部数据源和外部文件格式位于“外部资源” 下的子文件夹中。

后续步骤

有关为各种数据源创建外部数据源和外部表的更多教程，请参阅 PolyBase Transact-SQL 参考。

在以下文章中了解更多使用和监视 PolyBase 的方式：

• PolyBase 横向扩展组。
• PolyBase 故障排除。
• CREATE EXTERNAL DATA SOURCE