分享方式:


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

適用於: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

預先設定

  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. 

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

下一步