配置 PolyBase 以访问 Azure Blob 存储中的外部数据
适用于: SQL Server(仅限 Windows) Azure SQL 数据库 Azure Synapse Analytics Analytics Platform System (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))
使用 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)
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 的方式:
反馈
https://aka.ms/ContentUserFeedback。
即将发布:在整个 2024 年,我们将逐步淘汰作为内容反馈机制的“GitHub 问题”,并将其取代为新的反馈系统。 有关详细信息,请参阅:提交和查看相关反馈