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

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

#COPYの概要
PolyBaseでは比較的多くのステップを踏まないと、Synapse SQLプールへデータの投入ができませんでした。
[PolyBaseでSynapse SQLプールにデータを投入してみた](https://qiita.com/masahiro-yamaguchi/items/1a0d3706209b266c5676)

一方で、この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<br>・MSI<br>・サービス プリンシパル<br>・キー<br>・AAD|・SAS<br>・キー|・SAS<br>・キー|
|Azure Data Lake Gen2|・SAS<br>・MSI<br>・サービス プリンシパル<br>・キー<br>・AAD|・SAS<br>・MSI<br>・サービス プリンシパル<br>・キー<br>・AAD|・SAS<br>・MSI<br>・サービス プリンシパル<br>・キー<br>・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プール）](https://qiita.com/masahiro-yamaguchi/items/7a1b0d185b9a01b63344)

lineitem.tbl

```CSV
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コマンドを実行しました。

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

以下の通りロードが完了しました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/813261b0-fbae-1a65-6e4b-367dfba6d25b.png)

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

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