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

2025-01-02

适用于： 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 存储。

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

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

使用 services.msc 重启 SQL Server。重启 SQL Server 会重启这些服务：
- SQL Server PolyBase 数据移动服务
- SQL Server PolyBase 引擎

使用 services.msc 重启 SQL Server。重启 SQL Server 会重启这些服务：
- SQL Server PolyBase 数据移动服务
- SQL Server PolyBase 引擎

配置外部表

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

在数据库上创建主密钥。需要主密钥来加密凭据机密。
```
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';  
```

为 Azure Blob 存储创建数据库范围的凭据；IDENTITY 可以是任何内容，因为不会使用它。

-- 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 创建外部数据源。请注意，通过 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 (  
      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 Hadoop (DELIMITEDTEXT,  RCFILE, ORC, PARQUET).
CREATE EXTERNAL FILE FORMAT TextFileFormat WITH (  
      FORMAT_TYPE = DELIMITEDTEXT,
      FORMAT_OPTIONS (FIELD_TERMINATOR ='|',
            USE_TYPE_DEFAULT = TRUE))

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

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

CREATE STATISTICS StatsForSensors on CarSensor_Data(CustomerKey, Speed)

在数据库上创建主密钥。需要主密钥来加密凭据机密。
```
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';  
```

使用共享访问签名 (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>' ;

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

使用 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))

-- 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 适用于三个函数：

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

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

即席查询

下面的即席查询联接与 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 中，外部表在单独的文件夹“外部表” 中显示。外部数据源和外部文件格式位于“外部资源” 下的子文件夹中。

SSMS 中的 PolyBase 对象

后续步骤

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

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

反馈

此页面是否有帮助？