PolyBaseでSynapse SQLプールにデータを投入してみた
Synapse SQLプールへデータを高速にロードするツール「PolyBase」を使って、Synapse SQLプールへデータをロードしてみましたので記載します。
ちなみに、PolyBaseがなぜ高速にロード可能なのかは以下に情報をまとめておりますので、よろしければご確認ください。
Azure Synapse Analytics SQLプールのPolyBaseについて
PolyBaseでSynapse SQLへロードする手順
まずはPolybaseを使ってSynapse SQLへロードする手順を確認しておきます。
手順は以下の通りです。
- データをAzure Blob Storage、Hadoop、Azure Data Lake Storage Gen2のいずれかにデータを格納
- Synapse SQLプールにてAzure Blob Storageのデータ格納場所とデータ形式を定義し外部表を作成
- 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
ストレージアカウントのアクセスキーを指定します。アクセスキーの確認方法は以下の通りです。
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.外部表の作成
外部表の作成は以下の2つのステップで行います。
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を使ってデータをロードすることが可能です。
Author And Source
この問題について(PolyBaseでSynapse SQLプールにデータを投入してみた), 我々は、より多くの情報をここで見つけました https://qiita.com/masahiro-yamaguchi/items/1a0d3706209b266c5676著者帰属:元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。
Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .