🎃

Snowflake VALIDATION_MODEについて

2022/10/22に公開

今回は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]

Discussion