使用 PolyBase 將 S3 相容物件儲存體中的 parquet 檔案虛擬化

文章
07/15/2024

適用於：SQL Server 2022 (16.x)

SQL Server 2022 (16.x) 可以將來自 parquet 檔案的資料虛擬化。此程序允許資料保留在原始位置，但可從 SQL Server 執行個體使用 T-SQL 進行查詢，就像任何其他資料表一樣。此功能使用 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 主機上的憑證進行驗證。

權限

為了讓 Proxy 使用者能夠讀取 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.

使用 OPENROWSET 從 parquet 檔案中進行 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];

如需詳細資訊，請參閱

限制

SQL Server S3 相容儲存體支援的外部表格，其上的查詢限制為每個前置詞 1,000 個物件。這是因為 S3 相容物件清單限制為每個前置詞 1,000 個物件索引鍵。
對於 S3 相容物件儲存體，不允許客戶建立其中含 : 字元的存取金鑰識別元。
URL 總長度會限制在 259 個字元內。這表示 s3://<hostname>/<objectkey> 不應超過 259 個字元。 s3:// 會計入此限制，因此路徑長度不能超過 259-5 = 254 個字元。
在 UTF-16 格式中，SQL 認證名稱受制於 128 個字元。
除非此認證適用於新的外部資料來源，否則所建立的認證名稱必須包含貯體名稱。
存取金鑰識別碼和秘密金鑰識別碼只能包含英數字元值。

下一步

若要深入了解 PolyBase，請參閱 SQL Server PolyBase 概觀
設定 PolyBase 以存取 S3 相容物件儲存體中的外部資料

分享方式：