SnowflakeのCOPYコマンドでON_ERRORオプションを試してみた

こんにちは！エノカワです。

SnowflakeではCOPYコマンドでステージにあるファイルをテーブルにロードすることができます。
では、ファイルに何らかの異常があった場合のロード処理はどうなるのか考えたことはないでしょうか？

私はあります。　　

ということで、今回はCOPYコマンドのON_ERRORオプションを試してみました。

ON_ERRORオプション

ロード操作のエラー処理を指定することができるオプションです。
指定できるエラー処理は以下の通り。

• CONTINUE
  エラーが見つかった場合は、ファイルのロードを続行します。エラーが見つかった行はロードされません。

• SKIP_FILE
  エラーが見つかった場合はファイルをスキップします。

• SKIP_FILE_数値 （例 SKIP_FILE_10）
  ファイル内で見つかったエラー行の数が指定された数以上の場合は、ファイルをスキップします。

• SKIP_FILE_数値% （例 SKIP_FILE_10%）
  ファイル内で見つかったエラー行の割合が指定された割合を超えた場合は、ファイルをスキップします。

• ABORT_STATEMENT
  データファイルでエラーが見つかった場合は、ロード操作を中止します。

準備

今回はSnowflakeのサンプルデータTPCDS_SF10TCL.SHIP_MODEを使用して、
ON_ERRORオプション指定による挙動の違いを確認してみます。

サンプルデータをステージにアンロードする

サンプルデータをステージにアンロードします。
アンロードしたファイルは、後でロードするファイルとして使用します。

// コンテキスト設定
USE WAREHOUSE COMPUTE_WH;
USE DATABASE SNOWFLAKE_SAMPLE_DATA;

// データベース作成
CREATE DATABASE SANDBOXDB;
USE DATABASE SANDBOXDB;

// ステージ作成
CREATE STAGE MYSTAGE;

// サンプルデータ確認
select * from SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL.SHIP_MODE;

// アンロード
copy into @MYSTAGE
  from SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL.SHIP_MODE;

// ステージ確認
LIST @MYSTAGE;

data_0_0_0.csv.gzというファイル名でステージにアンロードされました。

アンロードしたファイルを編集する

アンロードしたファイルを編集してエラーを含む異常ファイルを作成します。

編集する前にアンロードしたファイルのデータを確認しておきましょう。
元となったサンプルデータTPCDS_SF10TCL.SHIP_MODEのテーブル定義は以下のようになっています。

テーブル定義

データプレビュー

1番目のフィールドSM_SHIP_MODE_SKに注目します。NUMERIC型になっていますね。
アンロードしたファイルを編集して、SM_SHIP_MODE_SKの一部のデータを文字列に変えてしまいましょう。

ファイル編集前

ファイル編集後

ファイル編集後の赤線の箇所のように、数値をシングルクォーテーションで囲って文字列に変更しました。
編集したファイルは、data_0_0_0_mod.csv.gzというファイル名で別名保存してステージに格納します。

なお、ファイルの編集はローカルマシン上で行いました。
ステージとローカルマシン間のファイルのダウンロード/アップロードは、
SnowSQLのGET/PUTコマンドを利用しました。
SnowSQLコマンドの詳細については後述の参考ドキュメントを参照ください。

ステージを確認してみましょう。

// ステージ確認
LIST @MYSTAGE;

ステージに2つのファイルが準備できました。

• data_0_0_0.csv.gz
  サンプルデータをアンロードしたファイル（正常ファイル）
• data_0_0_0_mod.csv.gz
  サンプルデータをアンロードして編集したファイル（異常ファイル）

ファイルをロードする

正常ファイル（サンプルデータをアンロードしたファイル）をロードしてみましょう。

サンプルデータと同じ定義で作成したテーブルSHIP_MODE_LOADにロードします。
ON_ERRORオプションは指定しません。

// ロード先テーブル作成
create table SHIP_MODE_LOAD like SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL.SHIP_MODE;

// ロード実行（正常ファイル）
copy into SHIP_MODE_LOAD
  from @MYSTAGE/data_0_0_0.csv.gz;

ロード結果画面が表示されました。

statusはLOADED、rows_loadedは20となっており、
20行のデータがロードされたことが確認できます。

ロードしたテーブルの中身を確認してみましょう。

// テーブル確認
select * from SHIP_MODE_LOAD;

20行のレコードが表示されました。
データプレビューで確認したデータがロードされていますね。

ABORT_STATEMENT指定

それでは、ここからは異常ファイル（アンロードしたファイルを編集したファイル）をロードしてみましょう。

まずはON_ERRORオプションにABORT_STATEMENTを指定します。

// ロード済みデータを削除
TRUNCATE TABLE SHIP_MODE_LOAD;

// ロード実行（異常ファイル）
copy into SHIP_MODE_LOAD
  from @MYSTAGE/data_0_0_0_mod.csv.gz;
  ON_ERROR = ABORT_STATEMENT;

ロード結果画面は表示されず、エラーメッセージが表示されました。

NUMERIC型のフィールドSM_SHIP_MODE_SKに文字列'6'をロードしようとしてコケています。
ファイルを編集してSM_SHIP_MODE_SKの一部のデータを文字列に変えていたので、想定していたエラーです。

テーブルの中身を確認してみましょう。

// テーブル確認
select * from SHIP_MODE_LOAD;

空っぽですね。

結果

ABORT_STATEMENTを指定すると、ファイルでエラーが見つかった場合はロード操作が中止されました。
→ロードするファイルにエラーが含まれていると、すべてのデータをロードしない挙動となります。

なお、ON_ERRORオプション指定なしの場合もABORT_STATEMENT指定と同じ挙動となります。

SKIP_FILE指定

お次はON_ERRORオプションにSKIP_FILEを指定します。

// ロード実行（異常ファイル）
copy into SHIP_MODE_LOAD
  from @MYSTAGE/data_0_0_0_mod.csv.gz;
  ON_ERROR = SKIP_FILE;

エラーは発生せず、ロード結果画面が表示されました。
statusはLOAD_FAILED、ros_loadedは0となっており、
ロード操作に失敗し、ロードされたデータが0件であることが分かります。

テーブルの中身を確認してみましょう。

// テーブル確認
select * from SHIP_MODE_LOAD;

空っぽですね。

では、正常ファイルと異常ファイルの両ファイルを同時にロードした場合はどうなるの　でしょうか？

FROM句をdata_0_0_0.*.csv.gzとして、両ファイルをロードしてみます。

// ロード実行（両ファイル）
copy into SHIP_MODE_LOAD
  from @MYSTAGE/data_0_0_0.*.csv.gz;
  ON_ERROR = SKIP_FILE;

エラーは発生せず、ロード結果画面が表示されました。

statusは正常ファイルはLOADED、異常ファイルはLOAD_FAILEDとなっています。
rows_loadedは正常ファイルは20、異常ファイルは0となっており、
ロードされたデータは正常ファイルの20件のみであることが分かります。

テーブルの中身を確認してみましょう。

// テーブル確認
select * from SHIP_MODE_LOAD;

20件のレコードが表示されました。
正常ファイルの方の20件ですね。

結果

SKIP_FILEを指定すると、エラーが見つかったファイルのロードはスキップされました。
→ロードするファイルにエラーが含まれていると、すべてのデータをロードしない挙動となります。

CONTINUE指定

最後はON_ERRORオプションにCONTINUEを指定します。

// ロード済みデータを削除
TRUNCATE TABLE SHIP_MODE_LOAD;

// ロード実行（異常ファイル）
copy into SHIP_MODE_LOAD
  from @MYSTAGE/data_0_0_0_mod.csv.gz;
  ON_ERROR = CONTINUE;

エラーは発生せず、ロード結果画面が表示されました。
statusはLOADED、rows_loadedは15となっており、
15行のデータがロードされたことが分かります。

テーブルの中身を確認してみましょう。

// テーブル確認
select * from SHIP_MODE_LOAD;

15件のレコードが表示されました。
ファイルを編集する際、文字列に変えたのは5件のデータだけでした。
残りの15件は正しいデータであるため、そのデータのみロードされたようです。

結果

SKIP_FILEを指定すると、エラーが見つかったファイルのロードは続行されますが、
エラーが見つかった行はロードされませんでした。
→ロードするファイルにエラーが含まれていると、エラー行以外のデータをロードする挙動となります。

まとめ

以上、ON_ERRORオプションを試してみました。

本オプションを利用することで、ロード操作でエラーが発生した場合の挙動を指定することができました。
ロード操作でのイレギュラー対応は気を使うところではありますが、
こういったオプションが用意されているのはありがたいですね。
特に大量のファイルをロードする際には重宝しそうです。

それでは！

参考

• COPY INTO <テーブル> — Snowflake Documentation | コピーオプション
• PUT — Snowflake Documentation
• GET — Snowflake Documentation