Azure Synapse Analytics(DWH)に大量なデータをinsertする方法
最近，Azure Data FactoryでETL開発を行っていて，サンプルデータで実装した処理をテストするため，Azure Synapse Analyticsに比較的に大量な（70,000行前後）データをinsertする処理が必要になった．
最初はPythonでcsvファイルからinsert文を生成するスクリプトを書いて，クエリを投げるだけで終わるじゃんって思っていたが，うまく行かなかった
何が問題だったか
Azure Synapse AnalyticsはINSERT VALUESを使って複数のデータを挿入することができない
-- ERROR
INSERT INTO dbo.foo
(Col1, Col2, Col3)
VALUES
(1, 'x', 'a'),
(2, 'y', 'b');
SELECTとUNION ALLを使ってある程度この問題を回避できるが，データが多くなるとUNION ALLのネストが結構深くなることでエラーが出るため，良い解決策とは言えない
-- OK
INSERT INTO dbo.foo (Col1, Col2, Col3)
SELECT 1, 'x', 'a' UNION ALL
SELECT 2, 'y', 'b' UNION ALL
SELECT 3, 'z', 'c';
ちなみに，insert文で大量なデータを挿入すると，Azure Data Studioが実行できなくなる．そもそもMSはこういう行為を推奨していないだろう．Azure Data Studio以外のDBツール（例えばsqlectron）は実行できるが，かなり重くなるため，効率が非常に悪い．
解決策
先にデータをAzure Blob Storageにアップロードし，BlobからデータをAzure Synapse Analyticsにコピーすることで，上記の問題を解決できる．また，Azure Data Factoryのcopy dataというactivityを使うと同じことができる
手順
1. データのcsvファイルをAzure Blob Storageのコンテナに格納する
2. Blobの接続キーを取得する
3. Azure Synapse AnalyticsのCOPYステートメントを使って，Blobから格納したデータをAzure Synapse Analyticsにコピーする
- COPYステートメントを使うのに必要最低限の情報
挿入先のテーブル名-
csvファイルのパス Blobの接続キー
- 注意事項：csv各項目のデータ型はAzure Synapse Analyticsと一致しないといけない
-
ENCODING,DATEFORMAT,FILE_TYPEなどの引数もある．詳細は公式ドキュメントにご確認
TRUNCATE TABLE [dbo].[foo];
COPY INTO [dbo].[foo]
FROM ‘<csvファイルのパス>’
WITH (
CREDENTIAL=(IDENTITY=‘Storage Account Key’, SECRET='<Blobの接続キー>’)
);
SELECT TOP (1000) *
FROM [dbo].[foo]
ちなみに，各項目のデフォルト値もセットできる．
以上