COPY機能でSynapse SQLプールにデータを投入してみた

11042 ワード
SQLServer copy Azure SynapseAnalytics SQLDatawarehouse SQLServer テキストリンク

Synapse SQLプールでは新たにCOPY機能という機能が実装されました。COPY機能により、PolyBaseのように高速にSynapse SQLプールにデータのロードが可能となります。一方、2020年9月時点では、まだプレビュー中であり高速なロードができないようですが、すでに機能として利用はできる状態ですので試してみました。（GAされるタイミングで性能が改善され高速なロードが可能となる模様です。）

COPYの概要

PolyBaseでは比較的多くのステップを踏まないと、Synapse SQLプールへデータの投入ができませんでした。
PolyBaseでSynapse SQLプールにデータを投入してみた

一方で、このCOPY機能を使う事でPolyBaseに比べて、非常に少ないステップでSynapse SQLプールへデータの投入が可能です。また、現時点（2020年9月）ではまだプレビュー版とされており、高速なデータロードは出来ないようですが、2020年の年末までにはGAされ、このタイミングでロードの性能は改善されるとのことです。

まずはよく使うオプションに関して、紹介したいと思います。詳細については以下が参加になります。
https://docs.microsoft.com/ja-jp/sql/t-sql/statements/copy-into-transact-sql?view=azure-sqldw-latest

取り込み可能なフォーマット

FILE_TYPE = { 'CSV' | 'PARQUET' | 'ORC' }

COPYコマンドで投入するファイルのフォーマットを指定します。現在COPYコマンドで取込可能なフォーマットは以下の通りです。

  • CSV（デフォルト）:コンマ区切りのファイルとなりますが、FIELDTERMINATORオプションを指定すればコンマ区切りでなくても問題ないです。
  • PARQUET: Parquet 形式を指定します。
  • ORC: 最適化行多桁式 (ORC) 形式を指定します。

認証メカニズム

CREDENTIAL (IDENTITY = '', SECRET = '')

外部ストレージにアクセスする際の認証メカニズムに関しては、外部ストレージの種類と取り込むファイルフォーマットによって異なります。

CSV Parquet ORC
Azure Blob Storage ・SAS
・MSI
・サービス プリンシパル
・キー
・AAD		 ・SAS
・キー		 ・SAS
・キー
Azure Data Lake Gen2 ・SAS
・MSI
・サービス プリンシパル
・キー
・AAD		 ・SAS
・MSI
・サービス プリンシパル
・キー
・AAD		 ・SAS
・MSI
・サービス プリンシパル
・キー
・AAD

設定の方法は以下の通りです。

  • Shared Access Signatures (SAS) を使用した認証
    IDENTITY: "Shared Access Signature" という固定の値を入力
    SECRET: Shared Access SignatureのSAS トークンを指定します。
    最低限必要な権限: READ および LIST）

  • サービス プリンシパルを使用した認証
    IDENTITY: @<OAuth_2.0_Token_EndPoint>
    SECRET: AAD サービス プリンシパル アプリケーション キー
    最低限必要な RBAC ロール: ストレージ BLOB データ共同作成者、ストレージ BLOB データ所有者、またはストレージ BLOB データ閲覧者

  • ストレージ アカウント キーを使用した認証
    IDENTITY: "Storage Account Key" という固定の値を入力
    SECRET: ストレージアカウントのアクセス キー

  • マネージド ID (VNet サービス エンドポイント) を使用した認証
    IDENTITY: "マネージド ID" の値が含まれている定数
    最低限必要な RBAC ロール: AAD 登録済み SQL Database サーバーに対する「ストレージ BLOB データ共同作成者」または「ストレージ BLOB データ所有者」

  • AAD ユーザーを使用した認証
    CREDENTIAL は必須ではありません
    最低限必要な RBAC ロール: AAD ユーザーに対するストレージ BLOB データ共同作成者またはストレージ BLOB データ所有者

フィールドのデリミタ

ROW TERMINATOR = 'row_terminator'

ファイルフォーマットがCSVの場合このオプションが利用可能です。デフォルトの値は（,）。
また16進数表記で指定する必要があります。

行のターミネータ

ROW TERMINATOR = 'row_terminator'

ファイルフォーマットがCSVの場合このオプションが利用可能です。デフォルトは「\r\n（CR+LF）」。
また16進数表記で指定する必要があり、「\n(LF)」の場合、「0x0A」と指定することに注意が必要です。

使用例

以下の記事で作成したTPC-Hのデータを使って、COPYコマンドにてSynapse SQLプールへのデータの投入を行います。
TPC-Hを使ってテスト環境を作成する（Synapse SQLプール）

lineitem.tbl

1|155190|7706|1|17|21168.23|0.04|0.02|N|O|1996-03-13|1996-02-12|1996-03-22|DELIVER IN PERSON|TRUCK|egular courts above the|
1|67310|7311|2|36|45983.16|0.09|0.06|N|O|1996-04-12|1996-02-28|1996-04-20|TAKE BACK RETURN|MAIL|ly final dependencies: slyly bold |
1|63700|3701|3|8|13309.60|0.10|0.02|N|O|1996-01-29|1996-03-05|1996-01-31|TAKE BACK RETURN|REG AIR|riously. regular, express dep|
1|2132|4633|4|28|28955.64|0.09|0.06|N|O|1996-04-21|1996-03-30|1996-05-16|NONE|AIR|lites. fluffily even de|
1|24027|1534|5|24|22824.48|0.10|0.04|N|O|1996-03-30|1996-03-14|1996-04-01|NONE|FOB| pending foxes. slyly re|
1|15635|638|6|32|49620.16|0.07|0.02|N|O|1996-01-30|1996-02-07|1996-02-03|DELIVER IN PERSON|MAIL|arefully slyly ex|
2|106170|1191|1|38|44694.46|0.00|0.05|N|O|1997-01-28|1997-01-14|1997-02-02|TAKE BACK RETURN|RAIL|ven requests. deposits breach a|
3|4297|1798|1|45|54058.05|0.06|0.00|R|F|1994-02-02|1994-01-04|1994-02-23|NONE|AIR|ongside of the furiously brave acco|
3|19036|6540|2|49|46796.47|0.10|0.00|R|F|1993-11-09|1993-12-20|1993-11-24|TAKE BACK RETURN|RAIL| unusual accounts. eve|
3|128449|3474|3|27|39890.88|0.06|0.07|A|F|1994-01-16|1993-11-22|1994-01-23|DELIVER IN PERSON|SHIP|nal foxes wake. |
3|29380|1883|4|2|2618.76|0.01|0.06|A|F|1993-12-04|1994-01-07|1994-01-01|NONE|TRUCK|y. fluffily pending d|
3|183095|650|5|28|32986.52|0.04|0.00|R|F|1993-12-14|1994-01-10|1994-01-01|TAKE BACK RETURN|FOB|ages nag slyly pending|
3|62143|9662|6|26|28733.64|0.10|0.02|A|F|1993-10-29|1993-12-18|1993-11-04|TAKE BACK RETURN|RAIL|ges sleep after the care
・・・

今回は、ストレージ アカウント キーを使用した認証を用いてデータのCOPYコマンドを実行しました。

COPY INTO dbo.LINEITEM　--ロードするテーブル名を指定
FROM 'https://<ストレージアカウント>.blob.core.windows.net/<コンテナ名>/<ディレクトリ名>/<ファイル名>'　--ロードするファイル指定
WITH (
    FILE_TYPE = 'CSV',　--ファイルフォーマット指定
    CREDENTIAL = (IDENTITY= 'Storage Account Key', SECRET='ストレージアカウントキー'),　--ストレージアカウントキーで認証
    FIELDTERMINATOR='|',　--デリミタ（|）
    ROWTERMINATOR = '0x0A' --改行コード（LF）
);

以下の通りロードが完了しました。

ちなみに、ロードするファイル名を指定する際に「*」などを使用することで分割されているファイルを一括でロード可能です。

COPY INTO dbo.LINEITEM　--ロードするテーブル名を指定
FROM 'https://<ストレージアカウント>.blob.core.windows.net/<コンテナ名>/<ディレクトリ名>/<ファイルプレフィクス>*'　--ロードするファイル指定
WITH (
    FILE_TYPE = 'CSV',　--ファイルフォーマット指定
    CREDENTIAL = (IDENTITY= 'Storage Account Key', SECRET='ストレージアカウントキー'),　--ストレージアカウントキーで認証
    FIELDTERMINATOR='|',　--デリミタ（|）
    ROWTERMINATOR = '0x0A' --改行コード（LF）
);

COPYコマンド実行時のファイル分割

COPYコマンド実行時にロードするファイルが非常に大きい場合、以下のcDWU毎の推奨ファイル数に分割すると最大のパフォーマンスを得ることができます。
この表からも読み取れるようにコンピュートノード1台当たり60ファイルに分割する事が推奨されているようです。

cDWU 分割ファイル数
100 60
200 60
300 60
400 60
500 60
1000 120
1500 180
2000 240
2500 300
3000 360
5000 600
6000 720
7500 900
10000 1200
15000 1800
30000 3600

さいごに

COPYコマンドを使えば簡単にSynapse SQLプールへデータのロードが行えそうです。