以前、Synapse SQLプールへPolyBaseを使って、DataLakeからデータのロードを行いました。
PolyBaseでSynapse SQLプールにデータを投入してみた
今回はその逆でSynapse SQLプールからPolyBaseをつかってDataLake Storageへデータを出力します。
全体の流れ
以下の流れでデータの出力を行います。
1. 認証情報の作成(CREATE MASTER KEY
とCREATE DATABASE SCOPED CREDENTIAL
)
2. 外部データソースの作成
3. ファイルフォーマットの作成
4. CREATE EXTERNAL TABLE AS SELECT
でデータを出力
また、1、2に関しては以前に紹介した記事に記載したものをそのまま利用していますので、今回は説明は割愛します。
ファイルフォーマットの指定
ファイルフォーマットを指定することで、出力するファイルの形式をカスタマイズすることが可能です。よく利用ものを記載します。詳しくは以下を参照してください。
https://docs.microsoft.com/ja-jp/sql/t-sql/statements/create-external-file-format-transact-sql?view=sql-server-ver15&tabs=delimited
FORMAT_TYPE
出力するファイルの形式を選択することが可能です。Parquet形式、ORC形式、RCFILE形式、DELIMITEDTEXT(CSV)形式などが選択可能です。DATA_COMPRESSION
出力するデータの圧縮形式を指定します。出力するファイルのFORMAT_TYPEによって指定方法が異なるので注意してください。
FORMAT_TYPE | DATA_COMPRESSION |
---|---|
Parquet | 'org.apache.hadoop.io.compress.GzipCodec' 'org.apache.hadoop.io.compress.SnappyCodec' |
ORC | 'org.apache.hadoop.io.compress.DefaultCodec' 'org.apache.hadoop.io.compress.SnappyCodec' |
RC | 'org.apache.hadoop.io.compress.DefaultCodec' |
DELIMITEDTEXT | 'org.apache.hadoop.io.compress.DefaultCodec' 'org.apache.hadoop.io.compress.GzipCodec' |
FIELD_TERMINATOR
区切り位置を指定します。デフォルトは|
STRING_DELIMITER
出力するデータの文字型のデータに対するフィールドターミネータを指定しますDATE_FORMAT
これはEXPORT時には機能しないようなので使う事はありませんが、注意として記載します。
今回はCSVで出力したいので以下のExportTpchData
というファイルフォーマットを作成しました。
CREATE EXTERNAL FILE FORMAT ExportTpchData
WITH (
FORMAT_TYPE = DELIMITEDTEXT,
FORMAT_OPTIONS (
FIELD_TERMINATOR = ',',
STRING_DELIMITER = '"'
),
DATA_COMPRESSION = 'org.apache.hadoop.io.compress.GzipCodec'
);
データの出力
データのエクスポートはCREATE EXTERNAL TABLE AS SELECT
というSQLで実行します。イメージ的には外部表をCTASで作成するようなイメージとなります。今回はEXPORT_LINEITEM
という外部テーブル名で作成しています。
CREATE EXTERNAL TABLE EXPORT_LINEITEM
WITH
(
LOCATION='\export_lineitem\',
DATA_SOURCE = AzureStorage,
FILE_FORMAT = ExportTpchData
)
AS
SELECT
*
FROM LINEITEM;
LOCATION
外部データソースで指定したコンテナ上のフォルダをしています。フォルダが無ければ自動で作成されます。今回はexport_lineitem
というフォルダ指定しています。DATA_SOURCE
外部データソースを指定します。(作成方法は過去の記事を参照してください。)FILE_FORMAT
先ほど作成したファイルフォーマットのExportTpchData
を指定しています。
SQLの実行が完了すると指定の場所へファイルが分割して出力されています。
作成した外部表はSELECTを行い読み込むことが可能です。一方で、Synapse SQLプールではsp_configure
がサポートされていない関係上、外部表へ追加のデータのINSERTなどは行うことができないようです。再度EXPORTしたい場合は、外部表をDROPして再作成しなおす必要があります。
おまけ
PolyBaseでデータを出力した場合、ファイルは分割されて出力されますが、分割数はcDWUによって異なるようです。
DW100cにつき6ファイルで出力されているようです。
以下検証結果を記載します。