【Snowflake】【トラブル】COPY INTO したがデータが格納されていない

Snowflake トラブル

■ はじめに

Snowflake でCOPY INTO <TABLE> した際のトラブルをメモっておく。

目次

【１】トラブル概要
　１）SQL文例
【２】原因
【３】解決案
　案１：データロードの際に「FORCE=TRUE」を指定
　案２：Tempテーブル経由でINSERTする

【１】トラブル概要

COPY INTO <TABLE> でS3からデータを定期的にロードしているのだが
データを再取り込みする際に、ロードを実行したら
Snowflakeのテーブルに意図したデータが格納されていなかった。

　なお、データ取得元のファイルは、ちゃんと存在していることを確認しており
ステージからのデータは取得できており、
（select * from @demo_database.demo_schema.demo_temp_stage;）
COPY INTO 実行してもエラーは発生していない。

１）SQL文例

USE DATABASE demo_database;
USE SCHEMA demo_schema;

-- Create temp stage
-- => s3内にあるデータを外部テーブルとして定義している
CREATE TEMPORARY STAGE IF NOT EXISTS demo_database.demo_schema.demo_temp_stage
  URL = 's3://your-s3-bucket-name/demo_warehouse/demo_database/demo_table/date=20221220/'
  STORAGE_INTEGRATION = xxxxx
  FILE_FORMAT = (TYPE =PARQUET COMPRESSION = SNAPPY)
;

-- Loading
COPY INTO demo_database.demo_schema.demo_table
FROM
(
  SELECT
    $1:id
    , $1:name
    , $1:age
    , sysdate() as insert_date
  FROM
    @demo_database.demo_schema.demo_temp_stage
);

-- データが確認（0件）
select count(*) from demo_database.demo_schema.demo_table
 where insert_date=sysdate();

【２】原因

Snowflakeは、ファイルロードのメタデータを維持している
らしいのだが(以下の公式ドキュメント参照。64日間保持？)

https://docs.snowflake.com/ja/release-notes/2017-07.html

COPY INTO <テーブル> コマンドは、データ重複を防止するために
既にテーブルにロードされたステージングされたデータファイルを
メタデータを参考に無視する

 => 詳細は、以下を読みといいかも、、、

古いファイルをロードする
https://docs.snowflake.com/ja/user-guide/data-load-considerations-load.html#label-loading-older-files
COPY INTO <テーブル>
https://docs.snowflake.com/ja/sql-reference/sql/copy-into-table.html

より抜粋
~~~~~~~~~~~~~~~~~~
コピーオプションの1つとして明示的に FORCE = TRUE を指定しない限り、
コマンドは既にテーブルにロードされたステージングされたデータファイルを無視します。
データを再ロードするには、 FORCE = TRUE を指定するか、
ファイルを変更して再度ステージングする必要があります。
これにより、新しいチェックサムが生成されます。
~~~~~~~~~~~~~~~~~~
 => ファイルやパスが変更してもダメだったような、、、

【３】解決案

以下を試してみるといいかも。
 + 案１：データロードの際に「FORCE=TRUE」を指定
 + 案２：Tempテーブル経由でINSERTする

 => なお、いずれの方法も、以前のデータがある場合は、
　　DELETEしておく必要がある
 => もし、これらで解決しなかったら別の原因の可能性あり

案１：データロードの際に「FORCE=TRUE」を指定

オプションの「FORCE=TRUE」を指定して、再実行してみる

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

修正例 

-- Loading
COPY INTO demo_database.demo_schema.demo_table
FROM
(
  SELECT
    $1:id
    , $1:name
    , $1:age
    , sysdate() as insert_date
  FROM
    @demo_database.demo_schema.demo_temp_stage
)
force=true -- ★追加
;

-- データが確認
select count(*) from demo_database.demo_schema.demo_table
 where insert_date=sysdate();

案２：Tempテーブル経由でINSERTする

オプションの「FORCE=TRUE」を指定して、再実行してみる

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

修正例 

-- Create temp table ★追加★
CREATE OR REPLACE TABLE demo_database.demo_schema.temp_demo_table (
  id BIGINT NOT NULL
  , name VARCHAR(100) NOT NULL
  , age int
  , insert_date date
);

-- Loading
COPY INTO demo_database.demo_schema.temp_demo_table
FROM
(
  SELECT
    $1:id
    , $1:name
    , $1:age
    , sysdate() as insert_date
  FROM
    @demo_database.demo_schema.demo_temp_stage
);

-- Insertする ★追加★
insert into demo_database.demo_schema.demo_table
select
  id
  , name
  , age
  , insert_date  
from
  demo_database.demo_schema.temp_demo_table
;

-- データが確認
select count(*) from demo_database.demo_schema.demo_table
 where insert_date=sysdate();

-- TempテーブルをDrop★追加★
drop table if exists demo_database.demo_schema.temp_demo_table;

関連記事

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 ～ Removeコマンド ～
https://dk521123.hatenablog.com/entry/2022/09/26/150259