■ はじめに

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

の続き。

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

【１】REMOVEコマンド

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

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

１）一口メモ

* 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;

２）使用上の注意

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

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

クラウドストレージサービス	ロールまたは許可
Amazon S3	s3:DeleteObject

３）オプション「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.*';

【２】サンプル

-- 使用する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
;

【３】補足

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

２）データロード時の冪等性

* これは、普通に 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
;