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

适用于： SQL Server 2016 （13.x） 及更高版本 - 仅限 Windows

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

先决条件

如果尚未安装 PolyBase，请参阅 在 Windows 上安装 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. 在数据库上创建数据库主密钥（DMK）。 加密凭据的秘密需要使用 DMK。

   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 (
       DATA_SOURCE = AzureStorage,
       LOCATION = '/Demo/',
       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
EXECUTE sp_configure 'allow polybase export', 1;
RECONFIGURE;
GO

-- 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 (
    DATA_SOURCE = HadoopHDP2,
    LOCATION = '/old_data/2009/customerdata',
    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 AS T1
     INNER JOIN CarSensor_Data AS T2
         ON (T1.CustomerKey = T2.CustomerKey)
WHERE T2.YearMeasured = 2009
      AND T2.Speed > 40;

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

查看 SSMS 中的 PolyBase 对象

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

相关内容

• PolyBase Transact-SQL 参考
• PolyBase 横向扩展组
• 监视 PolyBase 并对其进行故障排除
• CREATE EXTERNAL DATA SOURCE （Transact-SQL）