【Snowflake】Snowflake ～ データ アンロード ～

Snowflake

■ はじめに

https://dk521123.hatenablog.com/entry/2021/11/15/221245

で、Snowflake の データロードについて扱ったが、
今回は、業務でデータアンロード について扱いそうなので
復習も兼ねて、まとめておく。

目次

【１】アンロード（Unload）とは？
【２】Snowflakeによるアンロード
　１）ステージ（Stage）
【３】AWS S3 へのアンロード
　１）必要な権限
　２）S3バケットへの出力設定
　３）使用上の注意
【４】主な出力フォーマット
【５】サンプル
　１）AWS S3 へのアンロード (TSV)

【１】アンロード（Unload）とは？

（そもそも）アンロード（Unload）とは

https://docs.snowflake.com/ja/user-guide-data-unload.html

より抜粋
~~~~~~~~~
Snowflakeテーブルからデータをアンロード（つまり、エクスポート）
~~~~~~~~~
 => つまり、Snowflakeテーブルからデータをファイル出力すること

補足：Redshiftにおいて 

* Snowflake 特有ではなく、Redshiftにも同じ機能があった

https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c_unloading_data.html

【２】Snowflakeによるアンロード

* COPY INTO <場所>で可能

https://docs.snowflake.com/ja/user-guide/data-unload-overview.html
https://docs.snowflake.com/ja/sql-reference/sql/copy-into-location.html

 => Redshift のように、アンロード専用なコマンド（UNLOAD）ではない

https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_UNLOAD.html

１）ステージ（Stage）

https://dk521123.hatenablog.com/entry/2021/11/02/130111

より抜粋
~~~~~
* ファイルからデータをロードする際に、そのファイルを置く場所
~~~~~

【３】AWS S3 へのアンロード

* 以下の公式ドキュメントを参照しながら、やるとよさそう。

https://docs.snowflake.com/ja/user-guide/data-unload-s3.html

１）必要な権限

https://docs.snowflake.com/ja/user-guide/data-unload-s3.html#configuring-an-s3-bucket-for-unloading-data

より抜粋
~~~~~~~~~~~~~~~
Snowflakeには、フォルダー（およびすべてのサブフォルダー）に
新しいファイルを作成するため、S3バケットおよびフォルダーに対する
次の権限が必要です。
~~~~~~
* s3:DeleteObject
* s3:PutObject
~~~~~~

ベストプラクティスとして、Snowflakeは、外部クラウドストレージの認証責任を
Snowflake IDおよびアクセス管理（IAM）エンティティに委任するように、
ストレージ統合オブジェクトを構成することをお勧めします。
~~~~~~~~~~~~~~~

* 「ストレージ統合オブジェクト」については、以下の関連記事を参照のこと

Snowflake ～ ストレージ統合 ～
https://dk521123.hatenablog.com/entry/2022/06/29/221037

２）S3バケットへの出力設定

* COPY INTO コマンドでは、以下を選択する必要がある
~~~~~~
[1] S3バケットを参照する名前付き外部ステージオブジェクトを指定するか（推奨）
[2] バケットの URI /ストレージ統合の いずれか を指定して、バケットに直接アンロード
~~~~~~
 => 以下の公式ドキュメントの記述や構成図を読んでおくといいかも。

https://docs.snowflake.com/ja/user-guide/data-unload-s3.html

３）使用上の注意

* 以下の関連記事を参照のこと。

Snowflakeアンロード の 使用上の注意
https://dk521123.hatenablog.com/entry/2022/07/06/145724

【４】主な出力フォーマット

* フォーマットである程度、出力する形式はコントロールできるが
　項目ごとの指定ではないので、なかなか難しい、、、

１）Type = CSV

https://docs.snowflake.com/ja/sql-reference/sql/create-file-format#type-csv

* FIELD_DELIMITER = 'character' | NONE
 => 区切り文字
* ESCAPE = 'character' | NONE
 => エスケープの際の文字指定
* ESCAPE_UNENCLOSED_FIELD = 'character' | NONE
 => 囲い文字なしの際のエスケープ
* FIELD_OPTIONALLY_ENCLOSED_BY = 'character' | NONE
 => 囲み文字の指定

【５】サンプル

* 以下の「COPY INTO <場所>」のAPI仕様を見ながら作成するといいかも。

https://docs.snowflake.com/ja/sql-reference/sql/copy-into-location.html

１）AWS S3 へのアンロード (TSV)

-- 専用のDBを作成
CREATE DATABASE demo_bank;

-- 使用するDB/Schemaを指定
USE DATABASE demo_bank;
USE SCHEMA public;

-- ストレージ統合を作成
-- 「TYPE = EXTERNAL_STAGE」で外部ステージオブジェクトを指定
CREATE STORAGE INTEGRATION storage_integration_for_unload
    TYPE = EXTERNAL_STAGE
    STORAGE_PROVIDER = S3
    ENABLED = TRUE
    STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::11111111:role/role-for-unload'
    STORAGE_ALLOWED_LOCATIONS = ('s3://your-s3-bucket-name/demo/')
;

-- ステージ作成
CREATE STAGE IF NOT EXISTS demo_bank.public.demo_stage_for_unload
  URL='s3://your-s3-bucket-name/demo/for_unload/'
  STORAGE_INTEGRATION=storage_integration_for_unload
;

-- アンロード前にデータ削除
-- （後述「補足１：REMOVEコマンド」参照）
REMOVE @demo_bank.public.demo_stage_for_unload/insert=20220704/;

-- アンロード (TSV)
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'
  DATE_FORMAT = 'YYYY/MM/DD'
  TIME_FORMAT = 'HH24:MI:SS.FF'
  TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF'
  FIELD_OPTIONALLY_ENCLOSED_BY = '"'
  NULL_IF = ( '' )
  COMPRESSION = NONE
)
SINGLE=TRUE
HEADER = TRUE
;

補足１：REMOVEコマンド 

* 以下の関連記事を参照のこと。

https://dk521123.hatenablog.com/entry/2022/09/26/150259

関連記事

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/06/29/221037
Snowflake ～ Removeコマンド ～
https://dk521123.hatenablog.com/entry/2022/09/26/150259
Snowflakeアンロード の 使用上の注意
https://dk521123.hatenablog.com/entry/2022/07/06/145724
指定した日付分アンロードするSQLを生成するPythonコード
https://dk521123.hatenablog.com/entry/2023/08/09/000000