大量のデータを高速にロードする

SQL Data WarehouseではSQL Serverと同様に、さまざまなデータ投入の方法がサポートされています。例えば、Integration Service(SSIS)やbcpなどは、SQL Serverでも有名なデータベースへのデータロードツールです。 一方でSQL Data Warehouseでは「大量のデータの取り扱いに特化しているデータベース」という性質上、大量のデータを高速にロードする方式が用意されています。それが、Azure Blob Storageを利用し、SQL Data WarehouseへデータをロードするPolyBaseと呼ばれるデータロードの方式です。

PolyBaseを使ったデータロードの方式は以下の通りです。

• SQL Data WarehouseでAzure Blob Storageのデータ格納場所とデータ形式を定義し、外部表を作成
• SQL Data Warehouseのテーブルへ外部表からデータをロード

PolyBaseでのデータロードが早い理由

SQL Data Warehouseはコントロールノード、コンピュートノード、ストレージの3層構造で構成されています。SQL Data Warehouseからデータを読み出す場合は、複数あるコンピュートノードに処理を分割して実行し、最後にコントロールノードで集計を行います。

逆に、SQL Data Warehouseへデータをロード(投入)する時の動作は、コントロールノードが1台しかないため、ここがボトルネックとなり、うまく分散した処理ができない場合があります。この分散して処理できないロード方式が、bcpやSSISを使ったロード方式です。

一方でPolyBaseを使ったロードは、コントロールノードをバイパスし、直接コンピュートノードを使ってパラレルロードを行います。これにより、テラバイト級のデータを高速にロードすることが可能です。

PolyBaseを使ったデータロードの時間は、SQL Data WarehouseのDWU(Data Warehouse Unit)を決定する要素にもなります。以下に、1時間当たりのPolyBaseのデータロード時間とDWUの関係を記載します。

上記の表の通り、1時間当たり約1TB程度のデータをロードする場合、1000DWU程度のSQL Data WarehouseのDWUを指定することが目安です。

PolyBaseを使ったデータロードの方法

ここからは、実際にPolyBaseを使って、SQL Data Warehouseにデータをロードしてみましょう。

今回はPolyBaseを使って、TPCHのデータをSQL Data Warehouseへ投入します。TPCHのデータはすでにAzure Blob Storageの「tpch-data-scale1000」というコンテナ上に配置してあります。TPCHのデータは未圧縮の状態で約1000GBあり、gzipで圧縮した状態でAzure Blob Storageへ格納しました。

PolyBaseでAzure Blob StorageのデータをロードするまでのSQL Data Ware house上の手順は以下の通りです。

1. Create Master Keyでマスターキーを作成
2. Create Database Scoped CredentialでAzure ストレージ アカウントの認証情報を指定
3. Create External Data SourceでAzure Blob Storageのコンテナ等の情報を指定
4. Create External File Formatでデータの形式を指定
5. Create External Tableで外部表を作成
6. 外部表からSQL Data Warehouse上のテーブルへデータをロード

以下に簡単な構成を図示します。

SQLは以下のとおりです。

--1.Create Master Keyでマスターキーを作成

CREATE MASTER KEY;

--2.Create Database Scoped CredentialでAzure ストレージ アカウントの認証情報を指定
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH
    IDENTITY = 'user',
    --blobのシークレットキー
    SECRET = '＜シークレットキー＞'
;

--3.Create External Data SourceでAzure Blob Storageのコンテナ等の情報を指定
CREATE EXTERNAL DATA SOURCE AzureStorage
WITH (
    TYPE = HADOOP,
    --blobの情報を入力
    LOCATION = 'wasbs://tpch-data-scae1000@<Blob Server エンドポイント>,
    --上で登録した認証情報
    CREDENTIAL = AzureStorageCredential
);

--4.Create External File Formatでデータの形式を指定
CREATE EXTERNAL FILE FORMAT TpchData
WITH (
    --ファイルタイプ指定
    FORMAT_TYPE = DelimitedText,
    --区切り文字の指定
    FORMATOPTIONS ( FIELDTERMINATOR = '|' ),
    --gzip形式への対応
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.GzipCodec'


;

--5.Create External Tableで外部表を作成
CREATE EXTERNAL TABLE dbo.EXT_LINEITEM
(
    L_ORDERKEY    BIGINT NOT NULL,
    L_PARTKEY     BIGINT NOT NULL,
    L_SUPPKEY     BIGINT NOT NULL,
    L_LINENUMBER  BIGINT NOT NULL,
    L_QUANTITY    DECIMAL(15,2) NOT NULL,
    L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL,
    L_DISCOUNT    DECIMAL(15,2) NOT NULL,
    L_TAX         DECIMAL(15,2) NOT NULL,
    L_RETURNFLAG  CHAR(1) NOT NULL,
    L_LINESTATUS  CHAR(1) NOT NULL,
    L_SHIPDATE    DATE NOT NULL,
    L_COMMITDATE  DATE NOT NULL,
    L_RECEIPTDATE DATE NOT NULL,
    L_SHIPINSTRUCT CHAR(25) NOT NULL,
    L_SHIPMODE     CHAR(10) NOT NULL,
    L_COMMENT      VARCHAR(44) NOT NULL
)
WITH (
    LOCATION='/lineitem/',
    DATA_SOURCE=AzureStorage,
    FILE_FORMAT=TpchData
);

--6.外部表からSQL Data Warehouse上のテーブルへデータをロード
CREATE TABLE dbo.LINEITEM
WITH
(
    CLUSTERED COLUMNSTORE INDEX,
    DISTRIBUTION = ROUND_ROBIN
)
AS
SELECT * FROM [dbo].[EXT_LINEITEM];

PolyBaseの注意点

PolyBaseを利用したデータロードは確かに高速ですが、以下のように、いくつか注意点があります。

(1)gzip形式などで圧縮されているファイルのパラレルロードは、ファイルの手動分割が必要

PolyBaseはAzure Blob Storageのデータをロードする時に、ファイルを512MBごとに自動で分割しパラレルロードを行おうとします。つまり、ファイルを分割しておかなくてもロードはパラレルで行ってくれます。

しかし、gzip形式などで圧縮のかかっているファイルに関しては、512MB単位に自動で分割してくれるような動きは行ってくれないので、手動でファイルを分割しパラレルロードさせる必要があります。

(2)できるだけ大きなリソースクラスで実行

SQL Data Warehouseで、PolyBaseを使用し、クラスタ化カラムストアインデックスに対してデータをロードする場合、可能な限り大きなリソースクラスで実行するようにします。SQL Data Warehouseはデフォルトでsmallrcというリソースクラスとなりますが、largercやxlargercなど可能な限り大きなリソースクラスで実行するようにします。

リソースクラスは、実行するクエリに対してメモリやCPUの割り当て量を決定する設定項目です。より大きなリソースクラスを設定することで、多くのリソース、つまり、1つのクエリに対して多くのメモリやCPUリソースが割り当てられます。

PolyBaseでデータをロードする際(特にロード先のオブジェクトがクラスタ化列ストアインデックスでハッシュ分散されているような場合)、大量のデータのロード時に多くのリソースが必要となります。このため、可能な限り大きなリソースクラスを選択し、PolyBaseでのデータロードを行うことが重要です。

(3)統計情報の取得

これはPolyBaseというより、SQL Data Warehouse全体の注意点として言えることですが、SQL Data Warehouseは統計情報を自動で作成してくれる機能は存在しません。

そのため、PolyBaseなどで大量にデータを投入した後は統計情報の作成を忘れずに行うようにしましょう。統計情報の作成を忘れると性能劣化などが発生する場合があります。

山口 正寛

1984年生まれ。大阪府出身、東京都在住。データベースエンジニア。SQL Server、Oracle、MySQL、PostgreSQLなどのデータベースで、小規模から大規模な案件まで数多く経験。現在ではクラウドの流れに逆らうことなく、「データベース×クラウド」をキーワードに案件対応、セミナー活動、執筆活動など幅広く活動中。株式会社システムサポート所属。