【Snowflake】Snowflake ~ Removeコマンド ~

■ はじめに

https://dk521123.hatenablog.com/entry/2022/07/04/172738

の続き。

 Snowflakeで実行に失敗した場合、
再度実行するのに、冪等性(べきとうせい)を保ちたい。
で、Data Unload する際に、前の実行結果を削除するために
使用する Removeコマンド について扱ったのでメモ。

目次

【1】REMOVEコマンド
 1)一口メモ
 2)使用上の注意
 3)オプション「PATTERN」
【2】サンプル
【3】補足
 1)Snowflakeの可変パラメータ
 2)データロード時の冪等性

【1】REMOVEコマンド

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

より抜粋
~~~~~~~~~
外部(外部クラウドストレージ)または
内部(つまり、Snowflake)ステージからファイルを削除します
~~~~~~~~~

1)一口メモ

* Snowflake から、S3バケット上のファイル削除に使える

USE DATABASE demo_bank;
USE SCHEMA public;

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

-- ★注目:data.csvファイルが削除される★
REMOVE @demo_bank.public.demo_stage_for_unload/insertion_date=20220704/data.csv;

2)使用上の注意

https://docs.snowflake.com/ja/sql-reference/sql/remove.html#usage-notes

より抜粋
~~~~
* 外部ステージからファイルを削除するには、
 クラウドストレージアカウントのSnowflakeに
 次のロールまたは許可を付与する必要があります。
クラウドストレージサービス ロールまたは許可
Amazon S3 s3:DeleteObject

3)オプション「PATTERN」

https://docs.snowflake.com/ja/sql-reference/sql/remove.html#optional-parameters
PATTERN = 'regex_pattern'

* 削除するファイルをフィルタリングするための正規表現パターンを指定します。
 このコマンドは、指定された path 内のすべてのファイルを一覧表示し、
 見つかった各ファイルに正規表現パターンを適用します。

-- パターン *work* に一致するファイルを削除
REMOVE @demo_bank.public.demo_stage_for_unload/
PATTERN='.*work.*';

【2】サンプル

-- 使用するDB/Schemaを指定
USE DATABASE demo_db;
USE SCHEMA demo_schema;

-- ステージ作成
CREATE STAGE IF NOT EXISTS demo_db.demo_schema.demo_stage
  URL='your-s3-bucket-name/demo/for_unload/'
  STORAGE_INTEGRATION=demo_storage_integration
;

-- REMOVEコマンドにより、アンロード前にデータ削除(★注目★)
REMOVE @demo_db.demo_schema.demo_stage/insertion_date=2022-09-26/data.csv;

-- アンロード (CSV)
COPY INTO @demo_db.public.demo_stage/insertion_date=2022-09-26/data.csv
FROM (
  SELECT * FROM demo_db.demo_schema.demo_table WHERE insertion_date='2022-09-26'
)
FILE_FORMAT = (
  TYPE=CSV
  FIELD_DELIMITER = ','
  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
;

【3】補足

1)Snowflakeの可変パラメータ

* 本来であれば、日付部分を可変にしなくてはならないのだが
 Snowflake でいまいち使いやすい可変パラメータがない
 => 以下のようなSQL文の変数などが用意されているが、
  「REMOVE @demo_db.demo_schema.demo_stage/insertion_date=$TARGET_DATE/data.csv」
  みたいなことはできなかった
  (そのまま「insertion_date=$TARGET_DATE」をパスとして認識)

https://docs.snowflake.com/ja/sql-reference/session-variables.html#using-variables-in-sql

2)データロード時の冪等性

* これは、普通に DELETE文 をかましてあげればいい
 => 注意点としては、データロードの際に「FORCE=TRUE」を指定

-- 使用するDB/Schemaを指定
USE DATABASE demo_db;
USE SCHEMA demo_schema;

-- テーブル作成
CREATE TABLE demo_db.demo_schema.demo_load_table IF NOT EXISTS (
  id VARCHAR NOT NULL,
  name VARCHAR NOT NULL,
  remarks VARCHAR,
  insertion_date DATE NOT NULL
);

-- ステージ作成
CREATE OR REPLACE TEMPORARY STAGE demo_db.demo_schema.demo_temp_stage
  URL='your-s3-bucket-name/demo/for_load/'
  STORAGE_INTEGRATION=demo_storage_integration
;

-- DELETE文により、ロード前にデータ削除(★注目★)
DELETE FROM demo_db.demo_schema.demo_load_table WHERE insertion_date='2022-09-26';

-- アンロード (CSV)
COPY INTO demo_db.demo_schema.demo_load_table
FROM (
  SELECT
    $1,
    $2,
    $3,
    TO_DATE('$TARGET_DATE')
  FROM @demo_db.demo_schema.demo_temp_stage WHERE insertion_date='2022-09-26'
)
FORCE=TRUE
;

関連記事

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
Snowflakeアンロード の 使用上の注意
https://dk521123.hatenablog.com/entry/2022/07/06/145724
Snowflake ~ ストレージ統合 ~
https://dk521123.hatenablog.com/entry/2022/06/29/221037
COPY INTO したがデータが格納されていない
https://dk521123.hatenablog.com/entry/2022/12/20/152040