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

前回記事の続きです。
前回は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でした。