■ はじめに
https://dk521123.hatenablog.com/entry/2022/07/04/172738
で行ったSnowflakeのデータ アンロード で、 使用していて、ちょこちょこ気になることが出てきたので まとめておく。
目次
【０】公式ドキュメント「データのアンロードに関する考慮事項」 【１】デフォルトでのアンロードした出力ファイル １）最大ファイルサイズ MAX_FILE_SIZE ２）単一ファイルの切替 SINGLE ３）ファイル名の指定について ４）ファイル圧縮・非圧縮 COMPRESSION 補足：非圧縮ファイルでアンロードした時に文字化けする 【２】「OVERWRITE = TRUE」について 【３】JSON形式でのアンロード １）リレーショナルテーブルをJSON形式 でアンロードする場合 ２）出力形式は「ndjson」のみ
【０】公式ドキュメント「データのアンロードに関する考慮事項」
* 以下をさらっと読んでおくといいかも
https://docs.snowflake.com/ja/user-guide/data-unload-considerations.html
【１】デフォルトでのアンロードした出力ファイル
* デフォルトでアンロードした場合、以下のようになる + 最大ファイルサイズ = 16777216 （16 MB） + 複数ファイル + ファイル名ランダム + ファイル圧縮
１）最大ファイルサイズ MAX_FILE_SIZE
* デフォルトは、16 MB * 各ファイルの最大ファイルサイズは、5GB
https://docs.snowflake.com/en/user-guide/data-unload-considerations.html#unloading-to-a-single-file
より抜粋 (「=>」は、補足説明。でないと分かりづらいので) ~~~~~~~~~ By default, COPY INTO location statements separate table data into a set of output files to take advantage of parallel operations. [訳] デフォルトでは、「COPY INTO <場所>」文は、 並行処理を有効にするために、テーブルデータを出力ファイル群のセット として分けます => デフォルトでは複数ファイルで出力される The maximum size for each file is set using the MAX_FILE_SIZE copy option. [訳] 各ファイルの最大サイズは、MAX_FILE_SIZE コピーオプションで セットして使う => 公式API仕様の「MAX_FILE_SIZE = 数値」のことを言っている
https://docs.snowflake.com/ja/sql-reference/sql/copy-into-location.html
The default value is 16777216 (16 MB) but can be increased to accommodate larger files. [訳] デフォルト値は、16 MB(16777216 = 16 * 1024 * 1024)だが、 より大きいファイルを指定して増やすことはできる The maximum file size supported is 5 GB for Amazon S3, Google Cloud Storage, or Microsoft Azure stages. [訳] S3/Google Cloud Storage/Microsoft Azure stagesでサポートしている 最大ファイルサイズは5GB(5368709120 = 5 * 1024 * 1024 * 1024)である
２）単一ファイルの切替 SINGLE
https://docs.snowflake.com/en/user-guide/data-unload-considerations.html#unloading-to-a-single-file
より抜粋 (「=>」は、補足説明。でないと分かりづらいので) ~~~~~~~~~ To unload data to a single output file (at the potential cost of decreased performance), specify the SINGLE = true copy option in your statement. [訳] （パフォーマンスを減らさず潜在的なコスト内で） 1ファイルでアンロードするためには、 「SINGLE = true」コピーオプションを指定する You can optionally specify a name for the file in the path. [訳] オプションとして、パス内に、ファイル名を指定することができる => 以下の「補足：ファイル名の指定について」を参照 ~~~~~~~~~
https://docs.snowflake.com/ja/sql-reference/sql/copy-into-location.html
~~~~~~~~~ 重要 SINGLE = TRUE の場合、 COPY は FILE_EXTENSION ファイル形式オプションを無視し、 データ という名前のファイルを出力します。 << ★注意点 ファイル拡張子を指定するには、 内部または外部ロケーション path にファイル名と拡張子を指定します。 例： copy into @mystage/data.csv ... ~~~~~~~~~
３）ファイル名の指定について
https://docs.snowflake.com/ja/sql-reference/sql/copy-into-location.html
より抜粋 ~~~~~~~~~~~~~~~~ ファイル拡張子を指定するには、内部または外部ロケーション パス に ファイル名と拡張子を指定します。 例： copy into @mystage/data.csv ... ~~~~~~~~~~~~~~~~
４）ファイル圧縮・非圧縮 COMPRESSION
* 非圧縮したくない場合は「COMPRESSION = NONE」 * Type（CSV/JSON/PARQUET）でサポートしている圧縮形式が異なるので注意 => 以下のAPI仕様を参照のこと
https://docs.snowflake.com/ja/sql-reference/sql/copy-into-location.html
補足：非圧縮ファイルでアンロードした時に文字化けする
現象概要
以下のSQL文でファイル出力した際に、 S3バケット内にアンロードしたファイルをみた時に 文字化けしてしまう
SQL文
COPY INTO @demo_bank.public.demo_stage_for_unload/insert=20220704/demo_output.tsv FROM ( SELECT * FROM demo_bank.public.demo_trips ) FILE_FORMAT = ( TYPE＝CSV FIELD_DELIMITER = '\t' FIELD_OPTIONALLY_ENCLOSED_BY = '"' NULL_IF = ( '' ) ) SINGLE=TRUE HEADER = TRUE MAX_FILE_SIZE=4831838208 ;
原因
ファイル圧縮・非圧縮 COMPRESSION を指定しなかった。 そのため、デフォルト値が使われ、デフォルトは、「COMPRESSION = AUTO」で アンロードされたファイルは、デフォルトのgzipを使用して自動的に圧縮するため。 なので、「文字化け」って書いたが、正確に述べると 圧縮したファイルをテキストで開いたので、文字化けっぽい現象になっただけ。
解決案
* 非圧縮するには、「COMPRESSION = NONE」を明示的に指定する
【２】「OVERWRITE = TRUE」について
https://docs.snowflake.com/ja/sql-reference/sql/copy-into-location.html#copy-options-copyoptions
より抜粋 ~~~~~~~~~~~~~~~~~~~~~~ 定義: COPY コマンドが、ファイルが保存されている場所にある 一致する名前を持つ既存のファイルを上書きするかどうかを指定するブール値。 このオプションは、 COPY コマンドがアンロードする ファイルの名前と一致しない既存のファイルを削除 しません 。<< ★ここ ~~~~~~~~~~~~~~~~~~~~~~ => ファイル名をしてしないと
対応案
* アンロードする前に、 REMOVEコマンドしてから、「COPY INTO <場所>」コマンドを 実行する => 以下の関連記事のサンプルを参照のこと
https://dk521123.hatenablog.com/entry/2022/07/04/172738
【３】JSON形式でのアンロード
１）リレーショナルテーブルをJSON形式 でアンロードする場合
リレーショナルテーブルをJSON形式 でアンロードする場合に エラーになる可能性がある。
JSONでのアンロード (ダメだったSQL)
https://docs.snowflake.com/ja/sql-reference/sql/copy-into-location.html#type-json
COPY INTO @demo_bank.public.demo_stage_for_unload/insert=20220704/demo_output.json FROM ( SELECT * FROM demo_bank.public.demo_trips ) FILE_FORMAT = ( TYPE＝JSON COMPRESSION = NONE ) SINGLE=TRUE ;
エラー内容
Unsupported feature 'unloading of more than one column or non-json values'
に記載されていた。以下、抜粋 ~~~~~~~ COPY コマンドと組み合わせた OBJECT_CONSTRUCT 関数を使用して、 リレーショナルテーブルの行を単一の VARIANT 列に変換し、 行をファイルにアンロードできます。 ~~~~~~~ => OBJECT_CONSTRUCT 関数については、以下の公式ドキュメントを参照。
https://docs.snowflake.com/ja/sql-reference/functions/object_construct.html
-- SELECT のところで、OBJECT_CONSTRUCT 関数を追加した COPY INTO @demo_bank.public.demo_stage_for_unload/insert=20220704/demo_output.json FROM ( SELECT OBJECT_CONSTRUCT (*) FROM demo_bank.public.demo_trips ) FILE_FORMAT = ( TYPE＝JSON COMPRESSION = NONE ) SINGLE=TRUE ;
２）出力形式は「ndjson」のみ
https://docs.snowflake.com/ja/user-guide/data-unload-prepare.html#semi-structured-data
より抜粋 ~~~~~~~~~~ JSON ファイルにアンロードするとき、 Snowflakeは ndjson（「改行区切りJSON」）標準形式で出力します。 ~~~~~~~~~~
補足：ndjson とは？
https://dk521123.hatenablog.com/entry/2022/08/30/224248
より抜粋 * ndjson(Newline Delimited JSON) = JSON値を改行文字で区切ったデータ * 区切り文字に使う改行は、「\n」 （「\r\n」でも可ってndjson の以下の公式サイトで入っている)
関連記事
Snowflake ～ 基礎知識編 ～
https://dk521123.hatenablog.com/entry/2021/11/02/130111
Snowflake ～ 入門編 / Hello world ～
https://dk521123.hatenablog.com/entry/2021/11/22/212520
Snowflake ～ 基本編 / データロード ～
https://dk521123.hatenablog.com/entry/2021/11/15/221245
Snowflake ～ データ アンロード ～
https://dk521123.hatenablog.com/entry/2022/07/04/172738
Python ～ ndjson を扱う ～
https://dk521123.hatenablog.com/entry/2022/08/30/224248
