Snowflake VALIDATION_MODEについて

今回はCOPYコマンドのVALIDATION_MODEについて触れていきます。

VALIDATION_MODEとは

ドキュメントより抜粋

指定したテーブルにデータファイルをロードする代わりに
COPY コマンドにデータファイルを検証するよう指示する文字列（定数）。
つまり、COPY コマンドはファイルのエラーをテストしますが、ロードしません。
このコマンドは、ロードされるデータを検証し
指定された検証オプションに基づいて結果を返します。

各モードについて

モード	説明
RETURN_n_ROWS	エラーが発生しない場合、指定された行数を検証します。そうでない場合、行で最初に発生したエラーで不合格となります。
RETURN_ERRORS	COPY ステートメントで指定されたすべてのファイルにわたるすべてのエラー（解析、変換など）を返します。
RETURN_ALL_ERRORS	ロード中に ON_ERROR コピーオプションが CONTINUE に設定されていたため、以前のロード中に部分的にロードされたエラーを含む、 COPY ステートメントで指定されたすべてのファイルにわたるすべてのエラーを返します。

エラーが起きないときの動作確認

環境の準備

DB作成

CREATE OR REPLACE DATABASE COPY_DB;

テーブル作成

CREATE OR REPLACE TABLE  COPY_DB.PUBLIC.ORDERS (
    ORDER_ID VARCHAR(30),
    AMOUNT VARCHAR(30),
    PROFIT INT,
    QUANTITY INT,
    CATEGORY VARCHAR(30),
    SUBCATEGORY VARCHAR(30));

外部ステージ作成

CREATE OR REPLACE STAGE COPY_DB.PUBLIC.aws_stage_copy
    url='s3://snowflakebucket-copyoption/size/';

RETURN_ERRORSの動作確認

実行してみますがエラーは帰ってきません。これは想定通りです。

COPY INTO COPY_DB.PUBLIC.ORDERS
    FROM @aws_stage_copy
    file_format= (type = csv field_delimiter=',' skip_header=1)
    pattern='.*Order.*'
    VALIDATION_MODE = RETURN_ERRORS

COPYコマンドなのでORDERSテーブルにコピーされていると思い確認してみますが、下記の通りテーブルは空っぽでした。

select * from COPY_DB.PUBLIC.ORDERS;

ドキュメントに記載のとおり、VALIDATION_MODEオプションを指定するとロード（コピー）は行われません。

RETURN_n_ROWSの動作確認

次はRETURN_n_ROWSオプションを利用してみます。
ここではRETURN_5_ROWSとし、先頭5行を表示するようにしています。
これの使いどころとしては、ロードせずに結果を見たい時などに使えると思います。

COPY INTO COPY_DB.PUBLIC.ORDERS
    FROM @aws_stage_copy
    file_format= (type = csv field_delimiter=',' skip_header=1)
    pattern='.*Order.*'
    VALIDATION_MODE = RETURN_5_ROWS 

ORDER_ID	AMOUNT	PROFIT	QUANTITY	CATEGORY	SUBCATEGORY
B-25601	1275	-1148	7	Furniture	Bookcases
B-25601	66	-12	5	Clothing	Stole
B-25601	8	-2	3	Clothing	Hankerchief
B-25601	80	-56	4	Electronics	Electronic Games
B-25602	168	-111	2	Electronics	Phones
もちろん実際にはロードされていません。

select * from COPY_DB.PUBLIC.ORDERS;

エラーが起きるときの動作確認

外部ステージの作成

エラー発生用の外部ステージを定義します。

CREATE OR REPLACE STAGE COPY_DB.PUBLIC.aws_stage_copy
    url='s3://snowflakebucket-copyoption/returnfailed/';

RETURN_ERRORSの動作確認

COPY INTO COPY_DB.PUBLIC.ORDERS
    FROM @aws_stage_copy
    file_format= (type = csv field_delimiter=',' skip_header=1)
    pattern='.*Order.*'
    VALIDATION_MODE = RETURN_ERRORS

実行したところ4つエラーがありました。

ERROR	FILE	LINE	CHARACTER	BYTE_OFFSET	CATEGORY	CODE	SQL_STATE	COLUMN_NAME	ROW_NUMBER	ROW_START_LINE	REJECTED_RECORD
Numeric value 'one thousand' is not recognized	returnfailed/OrderDetails_error.csv	2	14	68	conversion	100038	22018	ORDERS""[""PROFIT"":3]	1	2	B-25601,1275,one thousand,7,Furniture,Bookcases
Numeric value 'two hundred twenty' is not recognized	returnfailed/OrderDetails_error.csv	3	12	115	conversion	100038	22018	ORDERS""[""PROFIT"":3]	2	3	B-25601,66,two hundred twenty,5,Clothing,Stole
Numeric value '7-' is not recognized	returnfailed/OrderDetails_error2 - Copy.csv	2	17	71	conversion	100038	22018	ORDERS""[""QUANTITY"":4]	1	2	B-30601,1275,10,7-,Furniture,Bookcases
Numeric value '3a' is not recognized	returnfailed/OrderDetails_error2 - Copy.csv	4	16	143	conversion	100038	22018	ORDERS""[""QUANTITY"":4]	3	4	B-30601,8,-244,3a,Clothing,Hankerchief

エラーの原因切り分け

1行目だけエラー内容から原因を切り分けてみます。

ERROR	FILE	LINE	CHARACTER	BYTE_OFFSET	CATEGORY	CODE	SQL_STATE	COLUMN_NAME	ROW_NUMBER	ROW_START_LINE	REJECTED_RECORD
Numeric value 'one thousand' is not recognized	returnfailed/OrderDetails_error.csv	2	14	68	conversion	100038	22018	ORDERS""[""PROFIT"":3]	1	2	B-25601,1275,one thousand,7,Furniture,Bookcases
上記エラー内容から'one thousand'が認識されていない事が根本原因のようです。
また読み込もうとしたファイルは'OrderDetails_error.csv'ということが分かります。
その他、読み込もうとして失敗したデータは'B-25601,1275,one thousand,7,Furniture,Bookcases'であり、本来数値でなければならいはずが文字列の'one thousand'であるため起きたエラーと切り分けられます。

RETURN_n_ROWSの動作確認

RETURN_1_rowsで1行だけ表示させてみます。

COPY INTO COPY_DB.PUBLIC.ORDERS
    FROM @aws_stage_copy
    file_format= (type = csv field_delimiter=',' skip_header=1)
    pattern='.*Order.*'
    VALIDATION_MODE = RETURN_1_rows

RETURN_1_rowsの通り1行だけエラーが帰ってきました。

Numeric value '7-' is not recognized ファイル「returnfailed/OrderDetails_error2 - Copy.csv」、行 2、文字 17 行 1、列 "ORDERS"["QUANTITY":4]