💡

Snowflakeデータロード時のエラー対応

2022/10/20に公開

error

DWH

Snowflake

tech

今回は外部テーブルからデータをロードする際のエラーについてです。
前回の記事の続きです。

ロード時のエラーオプション

ON_ERROR = 'CONTINUE'　→　エラー行以外をロードする
ON_ERROR = 'ABORT_STATEMENT'　→　エラーの時点でロードを停止
ON_ERROR = 'SKIP_FILE'　→　エラーがあったファイルだけスキップ
ON_ERROR = 'SKIP_FILE={number}'　→　エラーがあった行数でスキップorロード
ON_ERROR = 'SKIP_FILE={number}%'　→　エラーがあった行数の割合でスキップorロード

ON_ERROR = 'CONTINUE'

わざとエラーを起こしてみる

 CREATE OR REPLACE STAGE MANAGE_DB.external_stages.aws_stage_errorex
    url='s3://bucketsnowflakes4'
 
 CREATE OR REPLACE TABLE OUR_FIRST_DB.PUBLIC.ORDERS_EX (
    ORDER_ID VARCHAR(30),
    AMOUNT INT,
    PROFIT INT,
    QUANTITY INT,
    CATEGORY VARCHAR(30),
    SUBCATEGORY VARCHAR(30));
 
  COPY INTO OUR_FIRST_DB.PUBLIC.ORDERS_EX
    FROM @MANAGE_DB.external_stages.aws_stage_errorex
    file_format= (type = csv field_delimiter=',' skip_header=1)
    files = ('OrderDetails_error.csv');

エラー内容

Numeric value 'one thousand' is not recognized ファイル「OrderDetails_error.csv」、行 2、文字 14 行 1、列 "ORDERS_EX"["PROFIT":3] エラーが発生してもロードを継続したい場合は、ON_ERRORオプションに「SKIP_FILE」または「CONTINUE」などの別の値を使用します。ロードのオプションの詳細については、SQLクライアントで「info loading_data」を実行してください。

Udemyに講座を受けていただければよりエラーが起きた原因が分かるのですが、
端的に言うとINT型で用意したカラムにOne thousandという文字列型がきており読み込めませんってエラーです。

エラー時のデフォルトの動作

エラーが発生した場合、下図の通りデータは読み込まれません。

ON_ERROR = 'CONTINUE'でエラー行をスキップ

ON_ERROR = 'CONTINUE'オプションを利用すると、エラー行をスキップしてくれる動作となります。
確認してみます。

COPY INTO OUR_FIRST_DB.PUBLIC.ORDERS_EX
    FROM @MANAGE_DB.external_stages.aws_stage_errorex
    file_format= (type = csv field_delimiter=',' skip_header=1)
    files = ('OrderDetails_error.csv')
    ON_ERROR = 'CONTINUE';

ロードの結果を見てみると、rows_loadedが1498で2行読み込めていないことが分かります。
first_errorではエラー内容を確認できます。今回は「Numeric value 'one thousand' is not recognized」となっていました。

テーブルの確認

SELECT * FROM OUR_FIRST_DB.PUBLIC.ORDERS_EX

取り込み業が1498となっているのでエラーの2行だけ取り込まれずそれ以外は取り込まれた感じですね。

ON_ERROR = 'ABORT_STATEMENT'

ON_ERROR = 'ABORT_STATEMENT'を利用します

一旦先ほど取り込んだデータはトランケートしておきます。

TRUNCATE TABLE OUR_FIRST_DB.PUBLIC.ORDERS_EX;

ON_ERROR = 'ABORT_STATEMENT'を使って再度取り込み実施

COPY INTO OUR_FIRST_DB.PUBLIC.ORDERS_EX
    FROM @MANAGE_DB.external_stages.aws_stage_errorex
    file_format= (type = csv field_delimiter=',' skip_header=1)
    files = ('OrderDetails_error.csv','OrderDetails_error2.csv')
    ON_ERROR = 'ABORT_STATEMENT';

実行結果
御覧の通りON_ERRORオプション無しの時と同じエラーです。つまりABORT_STATEMENTは付けても付けなくても動作は同じということです。
また取り込み対象データが複数あった場合、1つ目のファイルでエラーとなればそれ以降のファイルでのコピーは停止します。

Numeric value 'one thousand' is not recognized ファイル「OrderDetails_error.csv」、行 2、文字 14 行 1、列 "ORDERS_EX"["PROFIT":3] エラーが発生してもロードを継続したい場合は、ON_ERRORオプションに「SKIP_FILE」または「CONTINUE」などの別の値を使用します。ロードのオプションの詳細については、SQLクライアントで「info loading_data」を実行してください。

ON_ERROR = 'SKIP_FILE'

ON_ERROR = 'SKIP_FILE'を利用します

COPY INTO OUR_FIRST_DB.PUBLIC.ORDERS_EX
    FROM @MANAGE_DB.external_stages.aws_stage_errorex
    file_format= (type = csv field_delimiter=',' skip_header=1)
    files = ('OrderDetails_error.csv','OrderDetails_error2.csv')
    ON_ERROR = 'SKIP_FILE';

結果を確認してみると、status欄でLOAD_FAILEDとなっているファイルはrows_loadedが0なのでこのファイルはエラーが起きロードをスキップしたようです。
反対にstatus欄でLOADEDとなっているファイルは285行読み込まれています。

ON_ERROR = 'SKIP_FILE={number}'

ON_ERROR = 'SKIP_FILE={number}'を利用します

SKIP_FILE_2の場合エラー行が2行以上ならスキップし、2行以下ならエラー行以外を読み込みます。

COPY INTO OUR_FIRST_DB.PUBLIC.ORDERS_EX
    FROM @MANAGE_DB.external_stages.aws_stage_errorex
    file_format= (type = csv field_delimiter=',' skip_header=1)
    files = ('OrderDetails_error.csv','OrderDetails_error2.csv')
    ON_ERROR = 'SKIP_FILE_2';

なので今回のデータは2行のエラーがありますので、下図の通りエラーがあったファイルはスキップされます。

ではSKIP_FILE_3にするとどうなるか？
エラー行数が2行なのでスキップされずにエラー行以外が読み込まれるはずです。

COPY INTO OUR_FIRST_DB.PUBLIC.ORDERS_EX
    FROM @MANAGE_DB.external_stages.aws_stage_errorex
    file_format= (type = csv field_delimiter=',' skip_header=1)
    files = ('OrderDetails_error.csv','OrderDetails_error2.csv')
    ON_ERROR = 'SKIP_FILE_3';

結果は・・・想定通りでした。

行数判定ではなく、割合判定もあります。

ON_ERROR = 'SKIP_FILE_0.5%'とすることで、ファイル内で見つかったエラー行の割合が指定された割合を超えた場合は、ファイルをスキップします。