【Snowflake】Snowflake ~ 基本編 / ステージ ~

■ はじめに

いまさらだが、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 ~ ウェアハウス / 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