使用 PolyBase 虚拟化 S3 兼容对象存储中 parquet 文件

项目
07/15/2024

适用于： SQL Server 2022 (16.x)

SQL Server 2022 (16.x) 可以虚拟化 parquet 文件中的数据。此过程允许将数据保留在其原始位置，但可以使用 T-SQL 命令从 SQL Server 实例进行查询（如同任何其他表一样）。此功能使用 PolyBase 连接器，可最大程度减少对提取、转换和加载 (ETL) 过程的需求。

在下面的示例中，我们将虚拟化存储在 S3 兼容对象存储上的 parquet 文件。

有关数据虚拟化的详细信息，请参阅利用 PolyBase 引入数据虚拟化。

先决条件

要使用 S3 兼容对象存储集成功能，需要以下工具和资源：

安装适用于 SQL Server 的 PolyBase 功能。
安装 SQL Server Management Studio (SSMS) 或 Azure Data Studio。
S3 兼容存储。
创建 S3 桶。无法从 SQL Server 创建或配置桶。
用户 (Access Key ID) 和机密 (Secret Key ID)，且该用户已知。需要同时对 S3 对象存储终结点进行身份验证。
S3 用户的 ListBucket 权限。
S3 用户的 ReadOnly 权限。
必须已配置 TLS。假设所有连接通过 HTTPS 而不是 HTTP 进行安全传输。终结点将由安装在 SQL Server OS 主机上的证书进行验证。

权限

为了让代理用户读取 S3 存储桶的内容，需要允许用户对 S3 端点执行以下操作：

ListBucket；
ReadOnly；

预配置

在 sp_configure 中启用 PolyBase：

exec sp_configure @configname = 'polybase enabled', @configvalue = 1
;
RECONFIGURE
;
exec sp_configure @configname = 'polybase enabled'
;

创建数据库范围凭据之前，用户数据库必须具有用于保护凭据的主密钥。有关详细信息，请参阅 CREATE MASTER KEY。

创建数据库范围的凭据

以下示例脚本在 SQL Server 的源用户数据库中创建数据库范围的凭据 s3-dc。有关详细信息，请参阅 CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)。

IF NOT EXISTS(SELECT * FROM sys.credentials WHERE name = 's3_dc')
BEGIN
 CREATE DATABASE SCOPED CREDENTIAL s3_dc
 WITH IDENTITY = 'S3 Access Key',
 SECRET = '<AccessKeyID>:<SecretKeyID>' ;
END

使用 sys.database_scoped_credentials (Transact-SQL) 验证新的数据库范围的凭据：

SELECT * FROM sys.database_scoped_credentials;

创建外部数据源

以下示例脚本在 SQL Server 的源用户数据库中创建外部数据源 s3_ds。外部数据源引用 s3_dc 数据库范围的凭据。有关详细信息，请参阅 CREATE EXTERNAL DATA SOURCE。

CREATE EXTERNAL DATA SOURCE s3_ds
WITH
(   LOCATION = 's3://<ip_address>:<port>/'
,   CREDENTIAL = s3_dc
);
GO

使用 sys.external_data_sources 验证新的外部数据源。

SELECT * FROM sys.external_data_sources;

虚拟托管 URL

某些与 S3 兼容的存储系统（如 Amazon Web Services）利用 virtual_hosted 样式 URL 在 S3 存储桶中实现文件夹结构。添加以下 CONNECTION_OPTIONS 以允许创建指向 S3 存储桶中的文件夹位置的外部表，例如 CONNECTION_OPTIONS = '{"s3":{"url_style":"virtual_hosted"}}'。

如果没有该 CONNECTION_OPTIONS 设置，则查询指向文件夹的外部表时，可能会看到以下错误：

Msg 13807, Level 16, State 1, Line 23  
Content of directory on path '/<folder_name>/' cannot be listed.

parquet 文件中使用 OPENROWSET 的 SELECT

以下示例演示如何使用 T-SQL 通过 OPENROWSET 查询来查询存储在 S3 兼容的对象存储中的 parquet 文件。有关详细信息，请参阅 OPENROWSET (Transact-SQL)。

由于这是 parquet 文件，因此将自动发生两个重要事项：

SQL Server 将从文件本身读取架构，因此无需定义表、列或数据类型。
无需声明要读取文件的压缩类型。

SELECT  * 
FROM    OPENROWSET
        (   BULK '/<bucket>/<parquet_folder>'
        ,   FORMAT       = 'PARQUET'
        ,   DATA_SOURCE  = 's3_ds'
        ) AS [cc];

通过外部表查询 S3 兼容对象存储

以下示例演示如何使用 T-SQL 通过查询外部表查询存储在 S3 兼容的对象存储中的 parquet 文件。示例使用外部数据源中的相对路径。

CREATE EXTERNAL FILE FORMAT ParquetFileFormat WITH(FORMAT_TYPE = PARQUET);
GO

CREATE EXTERNAL TABLE Region (
r_regionkey BIGINT,
r_name CHAR(25),
r_comment VARCHAR(152) )
WITH (LOCATION = '/region/', DATA_SOURCE = 's3_ds', 
FILE_FORMAT = ParquetFileFormat);
GO

SELECT * FROM [Region];

有关详细信息，请参阅：

限制

由 S3 兼容的存储支持的外部表上的 SQL Server 查询限制为每个前缀 1000 个对象。这是因为 S3 兼容的对象列表的每个前缀限制为 1000 个对象键。
对于 S3 兼容的对象存储，不允许客户创建包含 : 字符的访问密钥 ID。
URL 总长度限制为 259 个字符。这意味着 s3://<hostname>/<objectkey> 不应超过 259 个字符。 s3:// 计数包含在此限制内，因此路径长度不能超过 259-5 = 254 个字符。
SQL 凭据名称的限制为 128 个 UTF-16 格式的字符。
创建的凭据名称必须包含桶名称，除非此凭据用于新的外部数据源。
访问密钥 ID 和密钥 ID 只能包含字母数字值。

后续步骤

如需了解有关 PolyBase 的详细信息，请参阅 SQL Server PolyBase 的概述
配置 PolyBase 以访问 S3 兼容的对象存储中的外部数据

通过