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

Synapse SQLプールへデータを高速にロードするツール「PolyBase」を使って、Synapse SQLプールへデータをロードしてみましたので記載します。

ちなみに、PolyBaseがなぜ高速にロード可能なのかは以下に情報をまとめておりますので、よろしければご確認ください。
[Azure Synapse Analytics SQLプールのPolyBaseについて](https://qiita.com/masahiro-yamaguchi/items/27d6a54d6c66f19994c3)

#PolyBaseでSynapse SQLへロードする手順
まずはPolybaseを使ってSynapse SQLへロードする手順を確認しておきます。
手順は以下の通りです。

1. データをAzure Blob Storage、Hadoop、Azure Data Lake Storage Gen2のいずれかにデータを格納
2. Synapse SQLプールにてAzure Blob Storageのデータ格納場所とデータ形式を定義し外部表を作成
3. Synapse SQLプールのテーブルへ外部表からInsert SELECTを実施 

今回は2、3を中心に記載します。


#1.認証情報の作成
PolyBaseでSynapse SQLプールにデータをロードするためには、まずはデータベーススコープの資格情報の作成が必要です。
はじめにマスターキーをSQLプールに作成します。その後、`AzureStorageCredential`という名前でデータベーススコープの資格情報を登録します。
※ユーザデータベースで実行します。`master`データベースではありません。

```SQL
CREATE MASTER KEY;

CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH
    IDENTITY = 'LoadSynapseDW',
    SECRET = '＜シークレットキー＞'
;
```
* IDENTITIY
Synapse SQLプールへデータを読み込む場合任意の値で問題ありません。

* SECRET
ストレージアカウントのアクセスキーを指定します。アクセスキーの確認方法は以下の通りです。

1. Azureポータルにログインし、`ストレージ アカウント`を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/58cf509d-3aeb-7e27-811b-df9053d98610.png)

2. 対象のストレージアカウントを選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/771a11a8-dfa7-2403-d917-169bd147b323.png)

3. アクセスキーの確認
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/da6d7e86-5640-43b8-ba34-70b21348874f.png)

#2.外部データソースの作成
`1.認証情報の作成`で作成した資格情報を使用して、AzureStorageという名前の外部データソースの作成を行います。

```SQL
CREATE EXTERNAL DATA SOURCE AzureStorage
WITH (
    TYPE = HADOOP,
	--blobの情報を入力
    LOCATION = 'wasbs://<コンテナ名>@<ストレージアカウント名>.blob.core.windows.net',
	--上で登録した認証情報
    CREDENTIAL = AzureStorageCredential
)
;
```

* TYPE
Hadoop ベースのアクセスと Azure Blob Storage ベースのアクセスともに`HADOOP`を指定します。（今回はBlobベースのアクセスとなりHADOOPを指定しています。

* LOCATION
BLOBストレージ上に作成したコンテナ名、ストレージアカウントを指定します。

* CREDENTIAL
`1.認証情報の作成`で作成した`AzureStorageCredential`を指定します。

#3.外部表の作成
外部表の作成は以下の２つのステップで行います。
3-1. ファイルフォーマットの指定
3-2. 外部表の作成

##3-1. ファイルフォーマットの指定
読み込むファイルのファイルフォーマットの指定を実施します。
今回はTPC-Hのツールで作成したデータの取り込みを行います。

TPC-Hのデータの作成方法は以下を参照してください。
[TPC-Hを使ってテスト環境を作成する（Synapse SQLプール）](https://qiita.com/masahiro-yamaguchi/items/7a1b0d185b9a01b63344)

TPC-Hで作成したデータは以下の通りデータが`|`でくぐられています。

```CSV
0|ALGERIA|0| haggle. carefully final deposits detect slyly agai|
1|ARGENTINA|1|al foxes promise slyly according to the regular accounts. bold requests alon|
2|BRAZIL|1|y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special |
3|CANADA|1|eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold|
・・・・続く
```

ファイルの合わせて、DELIMITAなどの指定を行います。
このあたりのオプションは以下のページで詳しく記載されています。
https://docs.microsoft.com/ja-jp/sql/t-sql/statements/create-external-file-format-transact-sql?view=sql-server-ver15&tabs=delimited

以下のようにファイルフォーマットの指定を行います。

```SQL
CREATE EXTERNAL FILE FORMAT TpchData
WITH (
    --ファイルタイプ指定
    FORMAT_TYPE = DelimitedText,
	--区切り文字の指定
    FORMAT_OPTIONS (FIELD_TERMINATOR = '|')
);
```

##3-2.外部表の作成
外部表の作成を行います。
ファイルのフォーマットに合わせて外部表を定義していきます。

例ではTPC-Hのnationテーブルをベースに作成しています。

```SQL
CREATE EXTERNAL TABLE dbo.EXT_NATION 
(
	N_NATIONKEY	INTEGER		NOT NULL,
	N_NAME		CHAR(25)	NOT NULL,
	N_REGIONKEY	INTEGER		NOT NULL,
	N_COMMENT	VARCHAR(152)
)
WITH (
    LOCATION='/sc1/nation.tbl',
    DATA_SOURCE=AzureStorage,
    FILE_FORMAT=TpchData
);
```

* LOCATION
外部表で読み込むファイルを指定します。今回はコンテナ内に`sc1`というディレクトリを作成しその中に、`nation.tbl`というファイルがありますのでそれを読み込んでいます。

* DATA_SOURCE
`2.外部データソースの作成`にて作成した外部データソースを指定します。

* FILE_FORMAT
`3-1. ファイルフォーマットの指定`で作成したファイルフォーマットを指定します。

外部表の作成ができれば、SELECT分などで読み込みが可能です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/1b79c0d9-0795-9d08-8bc3-795a9496479c.png)

#4.PolyBaseによるデータロード
最後にPolyBaseによりデータをロードします。
PolyBaseによるデータのロードと言っても先ほど作成した外部表をSELECTしてロードしたいテーブルへInsertを行う動作となります。(CTASでもOKです。）
以下の例ではdbo.EXT_NATIONをSELECTして、dbo.NATIONテーブルへINSERTしています。

```SQL
INSERT INTO dbo.NATION SELECT * FROM dbo.EXT_NATION;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/8b577a0e-d6fc-6cbc-6115-35132d29a839.png)

以上でPolyBaseを使ってデータをロードすることが可能です。