はじめに

本記事では、COPYでFILLER関数を使用してデータ変換時にエラーが発生した場合、処理を継続させる方法をご紹介します。
FILLER関数に関しては、以下の情報を参照ください。

「データロード時に関数などを使用しロードする方法」
https://www.ashisuto.co.jp/cm/analytics-database/data_load_function.html

「CopyFaultTolerantExpressions」パラメータ

Verticaデータベースの「CopyFaultTolerantExpressions」パラメータを設定することでデータ変換でエラーが発生した場合も処理を継続することが可能です。

実行例

CopyFaultTolerantExpressionsの値が0(デフォルト)の場合

■ロードするデータを確認

cat test1.csv
1|TEST1
2|
3|TEST3
A|TEST4
5|TEST5
B|
6|

■パラメータの値を確認

dbadmin=> select parameter_name,current_value from configuration_parameters where parameter_name = 'CopyFaultTolerantExpressions';
        parameter_name        | current_value
------------------------------+---------------
 CopyFaultTolerantExpressions | 0
(1 row)

■テーブルの確認

dbadmin=> \d test1
                                   List of Fields by Tables
 Schema | Table | Column |    Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+-------------+------+---------+----------+-------------+-------------
 public | test1 | col1   | int         |    8 |         | f        | f           |
 public | test1 | col2   | varchar(10) |   10 |         | t        | f           |

■データを変換するCOPYを実行

dbadmin=> COPY test1 (col1,col2_filler FILLER VARCHAR(10),col2 AS LOWER(col2_filler)) from '/home/dbadmin/test1.csv' REJECTED DATA TABLE test1_bad;
ERROR 2501:  Cannot set a NOT NULL column (col2) to a NULL value in COPY statement
★データ変換でエラーが発生した場合は、エラーが表示されCOPY処理は中止されます。

■ロードしたテーブルを確認

dbadmin=> select * from test1;
 col1 | col2
------+------
(0 rows)
★COPY処理は中止されデータはテーブルに格納されません。

■ロード時に発生したエラーを確認

dbadmin=> select * from test1_bad;
     node_name      |        file_name        |             session_id             |  transaction_id   | statement_id | batch_number | row_number | rejected_data | rejected_data_orig_length |                rejected_reason
--------------------+-------------------------+------------------------------------+-------------------+--------------+--------------+------------+---------------+---------------------------+------------------------------------------------
 v_vertica_node0001 | /home/dbadmin/test1.csv | v_vertica_node0001-206662:0x33d64b | 45035996275162509 |            3 |            0 |          4 | A|TEST4       |                         7 | Invalid integer format 'A' for column 1 (col1)
 v_vertica_node0001 | /home/dbadmin/test1.csv | v_vertica_node0001-206662:0x33d64b | 45035996275162509 |            3 |            0 |          6 | B|            |                         2 | Invalid integer format 'B' for column 1 (col1)
(2 rows)
★データ変換時のエラーは出力されません。

CopyFaultTolerantExpressionsの値が1の場合

■パラメータの値を設定

dbadmin=> ALTER SESSION SET CopyFaultTolerantExpressions = 1;
ALTER SESSION

■パラメータの値を確認

dbadmin=> select parameter_name,current_value from configuration_parameters where parameter_name = 'CopyFaultTolerantExpressions';
        parameter_name        | current_value
------------------------------+---------------
 CopyFaultTolerantExpressions | 1
(1 row)

■データを変換するCOPYを実行

dbadmin=> COPY test1 (col1,col2_filler FILLER VARCHAR(10),col2 AS LOWER(col2_filler)) from '/home/dbadmin/test1.csv' REJECTED DATA TABLE test1_bad;
 Rows Loaded
-------------
           3
(1 row)
★COPY処理は正常に終了します。

■ロードしたテーブルを確認

dbadmin=> select * from test1;
 col1 |  col2
------+--------
    1 | test1
    3 | test3
    5 | test5
(3 rows)
★エラーとなったデータ以外はテーブルに格納されます。

■ロード時に発生したエラーを確認

dbadmin=> select * from test1_bad;
     node_name      |        file_name        |             session_id             |  transaction_id   | statement_id | batch_number | row_number | rejected_data  | rejected_data_orig_length |                                  rejected_reason
--------------------+-------------------------+------------------------------------+-------------------+--------------+--------------+------------+----------------+---------------------------+------------------------------------------------------------------------------------
 v_vertica_node0001 | /home/dbadmin/test1.csv | v_vertica_node0001-206662:0x33d64b | 45035996275162525 |            1 |            0 |          4 | A|TEST4        |                         7 | Invalid integer format 'A' for column 1 (col1)
 v_vertica_node0001 | /home/dbadmin/test1.csv | v_vertica_node0001-206662:0x33d64b | 45035996275162525 |            1 |            0 |          6 | B|             |                         2 | Invalid integer format 'B' for column 1 (col1)
 v_vertica_node0001 | /home/dbadmin/test1.csv | v_vertica_node0001-206662:0x33d64b | 45035996275162525 |            1 |            0 |          2 | Tuple (2,Null) |                        14 | ERROR 2501:  Cannot set a NOT NULL column (col2) to a NULL value in COPY statement
 v_vertica_node0001 | /home/dbadmin/test1.csv | v_vertica_node0001-206662:0x33d64b | 45035996275162525 |            1 |            0 |          7 | Tuple (6,Null) |                        14 | ERROR 2501:  Cannot set a NOT NULL column (col2) to a NULL value in COPY statement
(4 rows)
★データ変換時のエラーも出力されます。

参考情報

General Parameters
https://www.vertica.com/docs/11.0.x/HTML/Content/Authoring/AdministratorsGuide/ConfiguringTheDB/GeneralParameters.htm#CopyFaultTolerantExpressions

検証バージョンについて

この記事の内容はVertica 11.0で確認しています。

更新履歴

2022/03/31　本記事を公開