🗂

Snowflake ロード検証時のエラーレコードの扱いについて

2022/10/23に公開約4,700字

前回記事の続きです。
前回はVALIDATION_MODEを使ったロード時の検証に触れてました。
今回は検証時にエラーとなったレコードでの扱いに触れていきます。

環境準備

前回記事で捜査した環境から追加で下記操作を行います。

テーブルの再作成

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));

エラーレコードをSELECTで取得してみる

エラーを発生させる

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つのエラーが起きています。
このエラーをSELECTで取得してみます。

select rejected_record from table(result_scan(last_query_id()));

取得したエラーレコードをテーブルに書き込む

さらにはエラーレコードを任意のテーブルに書き込んでみます。

CREATE OR REPLACE TABLE rejected AS 
select rejected_record from table(result_scan(last_query_id()));

書き込み結果を確認してみる

SELECT * FROM rejected;
REJECTED_RECORD
B-25601,1275,one thousand,7,Furniture,Bookcases
B-25601,66,two hundred twenty,5,Clothing,Stole
B-30601,1275,10,7-,Furniture,Bookcases
B-30601,8,-244,3a,Clothing,Hankerchief

TIPS_1

VALIDATION_MODEを使わずにロードする際にエラーレコードを取得できるのでそちらを紹介します。

データを取り込む

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

結果として4行ほど取り込めなかった

file status rows_parsed rows_loaded error_limit errors_seen first_error first_error_line first_error_character first_error_column_name
s3://snowflakebucket-copyoption/returnfailed/OrderDetails_error2 - Copy.csv PARTIALLY_LOADED 285 283 285 2 Numeric value '7-' is not recognized 2 17 ORDERS""[""QUANTITY"":4]
s3://snowflakebucket-copyoption/returnfailed/OrderDetails_error.csv PARTIALLY_LOADED 1500 1498 1500 2 Numeric value 'one thousand' is not recognized 2 14 ORDERS""[""PROFIT"":3]
s3://snowflakebucket-copyoption/returnfailed/Orders2.csv LOADED 1500 1500 1500 0
s3://snowflakebucket-copyoption/returnfailed/Orders.csv LOADED 1500 1500 1500 0

この取り込めなかったエラーレコードを取得してみます。

select * from table(validate(orders, job_id => '_last'));
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

注意点
job_idで指定している'_last'ですが、これは直前に実行したクエリのクエリIDを示しています。
なので、もしいくつか前に実行したクエリで確認したい場合はクエリIDを指定できます。

TIPS_2

「何とかしてエラーになってしまったレコードをテーブルに格納したい!!」大丈夫です。できますよ。
SPLIT_PARTを利用して取得してみます。

SELECT 
SPLIT_PART(rejected_record,',',1) as ORDER_ID, 
SPLIT_PART(rejected_record,',',2) as AMOUNT, 
SPLIT_PART(rejected_record,',',3) as PROFIT, 
SPLIT_PART(rejected_record,',',4) as QUATNTITY, 
SPLIT_PART(rejected_record,',',5) as CATEGORY, 
SPLIT_PART(rejected_record,',',6) as SUBCATEGORY
FROM rejected; 
ORDER_ID AMOUNT PROFIT QUATNTITY CATEGORY SUBCATEGORY
B-25601 1275 one thousand 7 Furniture Bookcases
B-25601 66 two hundred twenty 5 Clothing Stole
B-30601 1275 10 7- Furniture Bookcases
B-30601 8 -244 3a Clothing Hankerchief

テーブルに格納する

CREATE OR REPLACE TABLE rejected_values as
SELECT 
SPLIT_PART(rejected_record,',',1) as ORDER_ID, 
SPLIT_PART(rejected_record,',',2) as AMOUNT, 
SPLIT_PART(rejected_record,',',3) as PROFIT, 
SPLIT_PART(rejected_record,',',4) as QUATNTITY, 
SPLIT_PART(rejected_record,',',5) as CATEGORY, 
SPLIT_PART(rejected_record,',',6) as SUBCATEGORY
FROM rejected; 

SELECTで確認してみる。

SELECT * FROM rejected_values;
ORDER_ID AMOUNT PROFIT QUATNTITY CATEGORY SUBCATEGORY
B-25601 1275 one thousand 7 Furniture Bookcases
B-25601 66 two hundred twenty 5 Clothing Stole
B-30601 1275 10 7- Furniture Bookcases
B-30601 8 -244 3a Clothing Hankerchief
問題なくテーブルに格納できました。
ちなみにPROFITカラムの型はVARCHARでした。

Discussion

ログインするとコメントできます