PolyBaseを使ってDataLakeへデータをEXPORTしてみた

以前、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ファイルで出力されているようです。
以下検証結果を記載します。

DW200c(12ファイル)

DW500c(30ファイル)