■ はじめに

いまさらだが、Snowflake の ステージ (Stage) を取り扱う

【１】ステージ (Stage)
　１）内部ステージ
　２）外部ステージ
【２】ステージに関する主なSQL文
　１）CREATE IF NOT EXISTS
　２）CREATE OR REPLACE
　３）SELECT
　４）COPY INTO - SELECT
　５）DROP
　６）LIST
【３】使用上の注意
　１）STORAGE_INTEGRATION はシングルクォーテーションで囲まない
　２）$Xで「,」がなくても文法エラーにならない
【４】メタデータ
　１）METADATA$FILENAME
　２）METADATA$FILE_ROW_NUMBER
 【５】トラブル
　１）エラー「Error: Invalid value "XXXX" for property 'STORAGE_INTEGRATION'」が発生

【１】ステージ (Stage)

* ファイルからデータをロードする際に、そのファイルを置く場所
* 二種類ある
　=> とりあえず「２）外部ステージ」を抑えた方がいい

１）内部ステージ

* Snowflake内部に存在するステージ

２）外部ステージ

* 各種クラウド上（S3など）に存在するステージ
* なお、クラウドストレージにアクセスする際に使用する
　「ストレージ統合」については、以下の関連記事を参照のこと

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

【２】ステージに関する主なSQL文

https://docs.snowflake.com/ja/sql-reference/sql/create-stage.html

１）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 -- 注意点：STORAGE_INTEGRATION はシングルクォーテーションで囲まない
  FILE_FORMAT = (TYPE =PARQUET COMPRESSION = SNAPPY)
;

２）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 -- 注意点：引用なしORダブルクォーテーションで囲む
  FILE_FORMAT = (TYPE =PARQUET COMPRESSION = SNAPPY)
;

３）SELECT

-- @<stage name>で指定し、
-- 項目は、$1, $2, ... で表示できる
SELECT
  $1,
  $2,
  $3,
  $4
FROM
  @demo_temp1_stage;

JSONで値が返却された場合

-- 項目は、$1:【項目名】で表示できる
SELECT
  $1:id,
  $1:name
FROM
  @demo_temp1_stage;

４）COPY INTO - SELECT

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

-- 「３）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

より抜粋
~~~~~
以前にロードされたかどうか、ロード後に変更があったかどうかに関係なく、
すべてのファイルをロードするよう指定するブール値。
このオプションはファイルを再ロードし、テーブル内のデータを
複製する可能性があることに注意します。
~~~~~

５）DROP

* ステージの削除

https://docs.snowflake.com/ja/sql-reference/sql/drop-stage.html
構文

DROP STAGE [ IF EXISTS ] <stage_name>

６）LIST

https://docs.snowflake.com/ja/sql-reference/sql/list.html

* ステージングされたファイルのリストを返す
 + ファイル名
 + サイズ
 + md5
 + 最終更新日

例

list @demo_temp1_stage;

【３】使用上の注意

１）STORAGE_INTEGRATION はシングルクォーテーションで囲まない

* STORAGE_INTEGRATION は引用なしORダブルクォーテーションで囲む
 => シングルクォーテーションで囲まない
 =>  『【５】トラブル』の『１）エラー「Error: Invalid value "XXXX"・・・」が発生』も参照

２）$Xで「,」がなくても文法エラーにならない

-- 以下を実行しても、文法エラーにならない（はじめ、分からなかった、、、）
SELECT
  $1 -- ','がない
  $2,
  $3,
  $4
FROM
  @demo_temp1_stage;

【４】メタデータ

https://docs.snowflake.com/ja/user-guide/querying-metadata.html#metadata-columns

１）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;

２）METADATA$FILE_ROW_NUMBER

* ファイルの行番号

【５】トラブル

１）エラー「Error: Invalid value "XXXX" for property 'STORAGE_INTEGRATION'」が発生

　以下「実行してエラーになったSQL」を実行したところ、
以下のエラー内容が表示。

実行してエラーになったSQL

CREATE OR REPLACE TEMPORARY STAGE demo_stage
  URL = 's3://your-s3-bucket-name/a1/b1/c1/'
  STORAGE_INTEGRATION = 'XXXX' -- この部分でエラー
  FILE_FORMAT = (TYPE =PARQUET )
;

-- カウントをとる
SELECT COUNT(*) FROM  @demo_stage;

エラー内容

Error: Invalid value "XXXX" for property 'STORAGE_INTEGRATION'.
String literal identifier is unsupported for this property.
Please use an unquoted or double-quoted identifier. (line X)

原因

STORAGE_INTEGRATIONは、文字列定数はサポートされていないが
「STORAGE_INTEGRATION = 'XXXX'」っとシングルクォーテーションで
囲っていたため。

解決案

-- エラーメッセージ「Please use an unquoted or double-quoted identifier.」
-- にあるように、[引用なし]又は [ダブルクォーテーションを使う]
STORAGE_INTEGRATION = "XXXX"

参考文献

https://dev.classmethod.jp/articles/snowflake-stage-files-query/

プログラムの超個人的なメモ

Memo for Programming.

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

■ はじめに

目次

【１】ステージ (Stage)

１）内部ステージ

２）外部ステージ

【２】ステージに関する主なSQL文

１）CREATE IF NOT EXISTS

２）CREATE OR REPLACE

３）SELECT

４）COPY INTO - SELECT

５）DROP

６）LIST

【３】使用上の注意

１）STORAGE_INTEGRATION はシングルクォーテーションで囲まない

２）$Xで「,」がなくても文法エラーにならない

【４】メタデータ

１）METADATA$FILENAME

２）METADATA$FILE_ROW_NUMBER

【５】トラブル

１）エラー「Error: Invalid value "XXXX" for property 'STORAGE_INTEGRATION'」が発生

参考文献

関連記事