PolyBase を使用して CSV ファイルを仮想化する

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

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

以下の例では、CSV ファイルは Azure Blob Storageに格納され、OPENROWSETまたは外部テーブルを介してアクセスされます。

データの仮想化に関する詳細については、「PolyBase によるデータ仮想化の概要」を参照してください。

事前設定

1. sp_configure で PolyBase を有効にする

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

RECONFIGURE;

2. ユーザー データベースを作成する

この演習では、既定の設定と場所を使用してサンプル データベースを作成します。 この空のサンプル データベースを使用して、データを操作し、スコープの資格情報を保存します。 この例では、CSV_Demoという名前の新しい空のデータベースが使用されます。

CREATE DATABASE [CSV_Demo];

3. マスター キーとデータベース スコープの資格情報を作成する

ユーザー データベースのデータベース マスター キーは、データベース スコープの資格情報シークレット blob_storage の暗号化に必須です。

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

CREATE DATABASE SCOPED CREDENTIAL blob_storage
WITH IDENTITY = '<user_name>', Secret = '<password>';

4. 外部データ ソースを作成する

データベース スコープ資格情報は、外部データ ソース用に使用されます。 この例では、CSV ファイルは Azure Blob Storage 内に存在するため、プレフィックス abs と SHARED ACCESS SIGNATURE ID メソッドを使用します。 SQL Server 2022 (16.x) の新しい設定など、コネクタとプレフィックスの詳細については、「CREATE EXTERNAL DATA SOURCE」を参照してください。

CREATE EXTERNAL DATA SOURCE Blob_CSV
WITH
(
 LOCATION = 'abs://<container>@<storage_account>.blob.core.windows.net'
,CREDENTIAL = blob_storage
);

たとえば、ストレージ アカウントの名前が s3sampledata で、コンテナーの名前が import である場合、コードは次のようになります。

CREATE EXTERNAL DATA SOURCE Blob_CSV
WITH
(
 LOCATION = 'abs://import@s3sampledata.blob.core.windows.net'
,CREDENTIAL = blob_storage
)

OPENROWSET を使用してデータにアクセスする

この例では、ファイルの名前はcall_center.csvで、データは 2 行目から始まります。

外部データ ソース Blob_CSV はコンテナー レベルにマッピングされるためです。 call_center.csv は、コンテナーのルートにある 2022 と呼ばれるサブフォルダーにあります。 フォルダー構造内のファイルに対してクエリを実行するには、外部データ ソースの LOCATION パラメーターを基準としたフォルダー マッピングを指定します。

SELECT * FROM OPENROWSET
(
    BULK '/2022/call_center.csv',
    FORMAT = 'CSV',
    DATA_SOURCE = 'Blob_CSV',
    FIRSTROW = 2
)
WITH (
    cc_call_center_sk INT,
    cc_call_center_id CHAR(16),
    cc_rec_start_date DATE,
    cc_rec_end_date DATE,
    cc_closed_date_sk INT,
    cc_open_date_sk INT,
    cc_name VARCHAR(50),
    cc_class VARCHAR(50),
    cc_employees INT,
    cc_sq_ft INT,
    cc_hours CHAR(20),
    cc_manager VARCHAR(40),
    cc_mkt_id INT,
    cc_mkt_class CHAR(50),
    cc_mkt_desc VARCHAR(100),
    cc_market_manager VARCHAR(40),
    cc_division INT,
    cc_division_name VARCHAR(50),
    cc_company INT,
    cc_company_name CHAR(50),
    cc_street_number CHAR(10),
    cc_street_name VARCHAR(60),
    cc_street_type CHAR(15),
    cc_suite_number CHAR(10),
    cc_city VARCHAR(60),
    cc_county VARCHAR(30),
    cc_state CHAR(2),
    cc_zip CHAR(10),
    cc_country VARCHAR(20),
    cc_gmt_offset DECIMAL(5, 2),
    cc_tax_percentage DECIMAL(5, 2)
) AS [cc];

外部テーブルでデータのクエリを実行する

CREATE EXTERNAL TABLE を使用して、SQL Server の CSV データを仮想化することもできます。 列を定義し、厳密に型指定する必要があります。 外部テーブルの作成にはより多くの労力がかかりますが、OPENROWSET を使用して外部データ ソースに対してクエリを実行するのに比べて利点もあります。 次のことを実行できます。

• 特定の列のデータ型指定の定義を強化する
• NULL可能性を定義する
• 照合順序を定義する
• 列の統計を作成して、クエリ プランの品質を最適化する
• セキュリティ モデルを強化するために、データ アクセス用のより詳細なモデルをSQL Server内に作成する

詳細については、「CREATE EXTERNAL TABLE」を参照してください。

次の例では、同じデータ ソースが使用されます。

1. 外部のファイル形式を作成する

ファイル形式を定義するには、外部ファイル形式が必要です。 再利用性のため、外部ファイル形式もお勧めします。

次の例では、データは 2 行目から始まります。

CREATE EXTERNAL FILE FORMAT csv_ff
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS(
        FIELD_TERMINATOR = ',',
        STRING_DELIMITER = '"',
        FIRST_ROW = 2)
);

2. 外部テーブルを作成する

LOCATION はフォルダーで、DATA_SOURCE で定義される外部データ ソース内の場所のパスを基準とした call_center.csv ファイルのファイル パスです。 この場合、ファイルは 2022 という名前のサブフォルダーにあります。 FILE_FORMAT を使用して、SQL Server の csv_ff 外部ファイル形式へのパスを指定します。

CREATE EXTERNAL TABLE extCall_Center_csv (
    cc_call_center_sk INT NOT NULL,
    cc_call_center_id CHAR(16) NOT NULL,
    cc_rec_start_date DATE,
    cc_rec_end_date DATE,
    cc_closed_date_sk INT,
    cc_open_date_sk INT,
    cc_name VARCHAR(50),
    cc_class VARCHAR(50),
    cc_employees INT,
    cc_sq_ft INT,
    cc_hours CHAR(20),
    cc_manager VARCHAR(40),
    cc_mkt_id INT,
    cc_mkt_class CHAR(50),
    cc_mkt_desc VARCHAR(100),
    cc_market_manager VARCHAR(MAX),
    cc_division VARCHAR(50),
    cc_division_name VARCHAR(50),
    cc_company VARCHAR(60),
    cc_company_name CHAR(50),
    cc_street_number CHAR(10),
    cc_street_name VARCHAR(60),
    cc_street_type CHAR(15),
    cc_suite_number CHAR(10),
    cc_city VARCHAR(60),
    cc_county VARCHAR(30),
    cc_state CHAR(20),
    cc_zip CHAR(20),
    cc_country VARCHAR(MAX),
    cc_gmt_offset DECIMAL(5, 2),
    cc_tax_percentage DECIMAL(5, 2)
    )
WITH (
    LOCATION = '/2022/call_center.csv',
    DATA_SOURCE = Blob_CSV,
    FILE_FORMAT = csv_ff
);
GO

関連するコンテンツ

• PolyBase Transact-SQLリファレンス
• CREATE EXTERNAL DATA SOURCE (Transact-SQL)
• CREATE EXTERNAL FILE FORMAT (Transact-SQL)
• CREATE EXTERNAL TABLE (Transact-SQL)