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

  • 项目

适用于： 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；

预配置

  1. 在 sp_configure 中启用 PolyBase：
exec sp_configure @configname = 'polybase enabled', @configvalue = 1
;
RECONFIGURE
;
exec sp_configure @configname = 'polybase enabled'
;
  1. 创建数据库范围凭据之前，用户数据库必须具有用于保护凭据的主密钥。 有关详细信息，请参阅 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;

parquet 文件中使用 OPENROWSET 的 SELECT

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

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

  1. SQL Server 将从文件本身读取架构，因此无需定义表、列或数据类型。
  2. 无需声明要读取文件的压缩类型。
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];

有关详细信息，请参阅：

限制

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

后续步骤