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プールへデータのロードが行えそうです。