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

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

ちなみに、PolyBaseがなぜ高速にロード可能なのかは以下に情報をまとめておりますので、よろしければご確認ください。
Azure Synapse Analytics SQLプールのPolyBaseについて

#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データベースではありません。

CREATE MASTER KEY;

CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH
    IDENTITY = 'LoadSynapseDW',
    SECRET = '＜シークレットキー＞'
;

• IDENTITIY
  Synapse SQLプールへデータを読み込む場合任意の値で問題ありません。

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

1. Azureポータルにログインし、ストレージ アカウントを選択します。
   

2. 対象のストレージアカウントを選択
   

3. アクセスキーの確認
   

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

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プール）

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

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

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

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

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

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

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分などで読み込みが可能です。

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

INSERT INTO dbo.NATION SELECT * FROM dbo.EXT_NATION;

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