PolyBase を使用してデルタ テーブルを仮想化する

  • [アーティクル]

適用対象: SQL Server 2022 (16.x) 以降のバージョン

SQL Server 2022 (16.x) では、デルタ テーブル フォルダーから直接データのクエリを実行できます。 一般的にデータの仮想化と呼ばれるこのコンセプトでは、データを元の場所に維持したまま、他のテーブルと同様に T-SQL コマンドを使用して SQL Server インスタンスからクエリを実行できます。 この機能では PolyBase コネクタを使用して、ETL プロセスを介したデータ コピーの必要性を最小限に抑えます。

次の例では、デルタ テーブル フォルダーは Azure Blob Storageに格納され、OPENROWSETまたは外部テーブルを介してアクセスされます。

データの仮想化に関する詳細については、「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 ID メソッドを使用します。 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_eds の下に構成された S3 互換オブジェクト ストレージです。 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.

この原因は、Delta ファイル メタデータ クエリに追加できる QUERYTRACEON クエリ ヒントがあり、サーバー ロールの実行には sysadmin が必要なことです。 これが発生した場合は、トレース フラグ 14073 をグローバルに有効にすることで問題を解決できます。これにより、クエリ ヒントが追加されなくなります。

関連するコンテンツ