分享方式：

使用 PolyBase 虛擬化差異資料表

  • 文章

適用於：SQL Server 2022 (16.x) 和更新版本

SQL Server 2022 (16.x) 可以直接從差異資料表資料夾中查詢資料。 這個概念 (通常稱為資料虛擬化) 允許資料保留在其原始位置，但可以像任何其他資料表一樣使用 T-SQL 命令從 SQL Server 執行個體中查詢。 此功能使用 PolyBase 連接器，並可將透過 ETL 程序複製資料的需求降到最低。

在下列範例中，差異資料表資料夾會儲存在 Azure Blob 儲存體上，並透過 OPENROWQUERY 或外部表格進行存取。

如需資料虛擬化的相關詳細資訊，請參閱簡介使用 PolyBase 進行資料虛擬化。

預先設定

1. 在 sp_configure 啟用 PolyBase

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

RECONFIGURE;

2. 建立使用者資料庫

此動作會建立一個具有預設設定和位置的範例資料庫。 可使用此空的範例資料庫來處理資料，並儲存限定範圍的認證。 在此範例中，使用名為 Delta_demo 的新空白資料庫。

CREATE DATABASE [Delta_demo];

3. 建立主要金鑰和資料庫限定範圍的認證

需要使用者資料庫中的資料庫主要金鑰，才能加密資料庫有限範圍認證的祕密 delta_storage_dsc。 對於此範例，差異資料表位於 Azure Data Lake Storage Gen2。

USE [Delta_demo];
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

CREATE DATABASE SCOPED CREDENTIAL delta_storage_dsc
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<SAS Token>';

4. 建立外部資料來源

資料庫限定範圍的認證用於外部資料來源。 在此範例中，差異資料表位於 Azure Data Lake Storage Gen2 中，因此請使用前置詞 adls 和 SHARED ACCESS SIGNATURE 身分識別方法。 如需連接器和前置詞的詳細資訊 (包括 SQL Server 2022 (16.x) 的新設定)，請參閱 CREATE EXTERNAL DATA SOURCE (機器翻譯)。

CREATE EXTERNAL DATA SOURCE Delta_ED
WITH (
    LOCATION = 'adls://<container>@<storage_account>.dfs.core.windows.net',
    CREDENTIAL = delta_storage_dsc
);

舉例來說，如果您的儲存體帳戶命名為 delta_lake_sample，且容器命名為 sink，則程式碼會是：

CREATE EXTERNAL DATA SOURCE Delta_ED
WITH (
    LOCATION = 'adls://sink@delta_lake_sample.dfs.core.windows.net',
    CREDENTIAL = delta_storage_dsc
);

使用 OPENROWSET 存取資料

在此範例中，[資料表] 資料夾命名為 Contoso。

因為外部資料來源 Delta_ED 會對應至容器層級。 Contoso 差異資料表資料夾位於根目錄中。 若要查詢資料夾結構中的檔案，請提供與外部資料來源 LOCATION 參數相關的資料夾對應。

SELECT * FROM OPENROWSET
(
    BULK '/Contoso',
    FORMAT = 'DELTA',
    DATA_SOURCE = 'Delta_ED'
) AS [result];

使用外部表格查詢資料

CREATE EXTERNAL TABLE 也可以用來虛擬化 SQL Server 中的差異資料表資料。 必須定義資料行且設定為強類型。 雖然外部表格需要更多心力來建立，但它們也可透過使用 OPENROWSET 查詢外部資料來源來提供額外的好處。 您可以：

  • 強化指定資料行之資料類型的定義
  • 定義可 NULL 性
  • 定義定序
  • 建立資料行的統計資料，以最佳化查詢計畫的品質
  • 在 SQL Server 內建立更精細的存取資料模型，以增強安全性模型

如需詳細資訊，請參閱 CREATE EXTERNAL TABLE。

針對下列範例，會使用相同的資料來源。

1. 建立外部檔案格式

若要定義檔案的格式設定，則需要外部檔案格式。 由於再使用性，也建議使用外部檔案格式。 如需詳細資訊，請參閱 CREATE EXTERNAL FILE FORMAT。

CREATE EXTERNAL FILE FORMAT DeltaTableFormat WITH(FORMAT_TYPE = DELTA);

2. 建立外部表格

差異資料表檔案位於 /delta/Delta_yob/，而此範例的外部資料來源是 S3 相容的物件儲存體，先前設定在資料來源 s3_eds 底下。 PolyBase 可以使用差異資料表資料夾或絕對檔案本身作為 LOCATION，其位於 delta/Delta_yob/_delta_log/00000000000000000000.json。

-- Create External Table using delta
CREATE EXTERNAL TABLE extCall_Center_delta (
    id INT,
    name VARCHAR(200),
    dob DATE
)
WITH (
        LOCATION = '/delta/Delta_yob/',
        FILE_FORMAT = DeltaTableFormat,
        DATA_SOURCE = s3_eds
);
GO

限制

如果建立指向資料分割差異資料表的外部表格，則用於資料分割的資料行會在查詢外部表格時傳回 NULL。 不過，如果使用 OPENROWSET 查詢，則資料行值會正確傳回。 若要解決此問題，請在查詢 OPENROWSET 上建立檢視，然後查詢檢視以取得分割的資料行值以正確傳回。

查詢外部 Delta 資料表時，可能會遇到下列錯誤：

Msg 2571, Level 14, State 3, Line 1
User '<user>' does not have permission to run DBCC TRACEON.
Msg 16513, Level 16, State 0, Line 1
Error reading external metadata.

這種情況可能會發生，因為有一個 QUERYTRACEON 查詢提示可以新增至 Delta 檔案中繼資料查詢，而且需要 sysadmin 伺服器角色才能執行。 如果發生這種情況，可以藉由全域啟用追蹤旗標 14073 來解決此問題，這可防止新增查詢提示。

相關內容