■ はじめに
いまさらだが、Snowflake の ステージ (Stage) を取り扱う
目次
【1】ステージ (Stage) 1)内部ステージ 2)外部ステージ 【2】ステージに関する主なSQL文 1)CREATE IF NOT EXISTS 2)CREATE OR REPLACE 3)SELECT 4)COPY INTO - SELECT 5)DROP 6)LIST 【3】メタデータ 1)METADATA$FILENAME 2)METADATA$FILE_ROW_NUMBER
【1】ステージ (Stage)
* ファイルからデータをロードする際に、そのファイルを置く場所 * 二種類ある => とりあえず「2)外部ステージ」を抑えた方がいい
1)内部ステージ
* Snowflake内部に存在するステージ
2)外部ステージ
* 各種クラウド上(S3など)に存在するステージ * なお、クラウドストレージにアクセスする際に使用する 「ストレージ統合」については、以下の関連記事を参照のこと
Snowflake ~ ストレージ統合 ~
https://dk521123.hatenablog.com/entry/2022/06/29/221037
【2】ステージに関する主なSQL文
https://docs.snowflake.com/ja/sql-reference/sql/create-stage.html
1)CREATE IF NOT EXISTS
-- For External CREATE TEMPORARY STAGE IF NOT EXISTS demo_temp_stage URL = 's3://your-s3-bucket-name/demo_warehouse/demo_database/demo_table/' -- ストレージの場所がプライベート/保護されている場合にのみ必要 STORAGE_INTEGRATION = xxxxx FILE_FORMAT = (TYPE =PARQUET COMPRESSION = SNAPPY) ;
2)CREATE OR REPLACE
-- For External CREATE OR REPLACE TEMPORARY STAGE demo_temp2_stage URL = 's3://your-s3-bucket-name/demo_warehouse/demo_database/demo_table/' -- ストレージの場所がプライベート/保護されている場合にのみ必要 STORAGE_INTEGRATION = xxxxx FILE_FORMAT = (TYPE =PARQUET COMPRESSION = SNAPPY) ;
3)SELECT
-- @<stage name>で指定し、 -- 項目は、$1, $2, ... で表示できる SELECT $1, $2, $3, $4 FROM @demo_temp1_stage;
使用上の注意 : $Xで「,」がなくても文法エラーにならない
-- 以下を実行しても、文法エラーにならない(はじめ、分からなかった、、、) SELECT $1 -- ','がない $2, $3, $4 FROM @demo_temp1_stage;
4)COPY INTO - SELECT
https://docs.snowflake.com/ja/sql-reference/sql/copy-into-table.html
-- 「3)SELECT」に、+αとして、COPY INTOを指定しただけ COPY INTO clone_table ( id, name, birth_date, update_at ) FROM ( SELECT $1, $2, $3, $4 FROM @demo_temp1_stage );
オプション「FORCE = TRUE | FALSE」
https://docs.snowflake.com/ja/sql-reference/sql/copy-into-table.html
より抜粋 ~~~~~ 以前にロードされたかどうか、ロード後に変更があったかどうかに関係なく、 すべてのファイルをロードするよう指定するブール値。 このオプションはファイルを再ロードし、テーブル内のデータを 複製する可能性があることに注意します。 ~~~~~
5)DROP
* ステージの削除
https://docs.snowflake.com/ja/sql-reference/sql/drop-stage.html
構文
DROP STAGE [ IF EXISTS ] <stage_name>
6)LIST
https://docs.snowflake.com/ja/sql-reference/sql/list.html
* ステージングされたファイルのリストを返す + ファイル名 + サイズ + md5 + 最終更新日
例
list @demo_temp1_stage;
【3】メタデータ
https://docs.snowflake.com/ja/user-guide/querying-metadata.html#metadata-columns
1)METADATA$FILENAME
* ファイル名(&パス) * 実際の使用例については、以下の関連記事を参照のこと
https://dk521123.hatenablog.com/entry/2022/10/28/000925
例
CREATE OR REPLACE TEMPORARY STAGE demo_temp3_stage URL = 's3://your-s3-bucket-name/a1/b1/c1/' STORAGE_INTEGRATION = xxxxx FILE_FORMAT = (TYPE =CSV FIELD_DELIMITER = '\t') ; -- 例:s3://your-s3-bucket-name/a1/b1/c1/test_20220901.csv SELECT METADATA$FILENAME -- ★「a1/b1/c1/test_20220901.csv」 FROM @demo_temp3_stage;
2)METADATA$FILE_ROW_NUMBER
* ファイルの行番号
参考文献
https://dev.classmethod.jp/articles/snowflake-stage-files-query/
関連記事
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 ~ 文字列操作関連 ~
https://dk521123.hatenablog.com/entry/2022/10/01/000000
Snowflake ~ Partition構成のデータを取り込む ~
https://dk521123.hatenablog.com/entry/2022/10/28/000925
Snowflake ~ Snowpipe ~
https://dk521123.hatenablog.com/entry/2023/04/15/225515
Snowflake ~ ウェアハウス / Warehouse ~
https://dk521123.hatenablog.com/entry/2022/12/04/000000
Snowflake ~ ウェアハウス負荷監視 ~
https://dk521123.hatenablog.com/entry/2022/12/06/191727
エラー「Failure using stage area. ... AccessDenied」時の対応
https://dk521123.hatenablog.com/entry/2022/10/27/195547
エラー「Error assuming AWS_ROLE」時の対応
https://dk521123.hatenablog.com/entry/2022/11/25/175912