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

虚拟托管 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 文件,因此将自动发生两个重要事项:

  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 只能包含字母数字值。

后续步骤