Question

COPY Statement not rejecting records when FILLER is being used

Forum|Forum|7 years ago
November 30, 2018
4 replies
19 views

abdulrehman86
New Participant

Hi,
We are reading data from a CSV and loading it into a vertica table using COPY command. The target table has a column set to NOT NULL. In COPY statement, we are using FILLER to transform data. This transformed data is then loaded into NOT NULL column. If there is null in CSV, the query fails with error like 'Cannot insert into NOT NULL column'.

However when we remove the FILLER and run the query, it sends the NULL value row to rejected table.

Can we have the same functionality while using FILLER in COPY query?

Below are the queries

CREATE TABLE CUST_CONTACT
(
Name varchar(80),
Contact varchar(15) NOT NULL
);

COPY CUST_CONTACT
(
Name,
v2 filler VARCHAR(15),
Contact as REGEXP_REPLACE(v2, '(\d{5})(\d{7})(\d{1})','\1-\2-\3')
) from 'Test5.csv' skip 1 delimiter ','
rejected data as table errortest3;

Thanks.

+2

Jim_Knicely
Participating Frequently
Forum|Forum|7 years ago
November 30, 2018

Set the CopyFaultTolerantExpressions parameter to 1!

Example:

dbadmin=> \! cat /home/dbadmin/Test5.csv
TEST,TEST
TEST,

dbadmin=> COPY CUST_CONTACT
dbadmin-> (
dbadmin(> Name,
dbadmin(> v2 filler VARCHAR(15),
dbadmin(> Contact as REGEXP_REPLACE(v2, '(\d{5})(\d{7})(\d{1})','\1-\2-\3')
dbadmin(> ) from '/home/dbadmin/Test5.csv' skip 1 delimiter ','
dbadmin-> rejected data as table errortest3;
ERROR 2501:  Cannot set a NOT NULL column (Contact) to a NULL value in COPY statement

dbadmin=> select * from errortest3;
 node_name | file_name | session_id | transaction_id | statement_id | batch_number | row_number | rejected_data | rejected_data_orig_length | rejected_reason
-----------+-----------+------------+----------------+--------------+--------------+------------+---------------+---------------------------+-----------------
(0 rows)

dbadmin=> SELECT current_value, default_value, description FROM configuration_parameters WHERE parameter_name = 'CopyFaultTolerantExpressions';
 current_value | default_value |                                      description
---------------+---------------+---------------------------------------------------------------------------------------
 0             | 0             | Expression evaluation erros during COPY are rejected instead of rolling back the COPY
(1 row)

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

dbadmin=> COPY CUST_CONTACT
dbadmin-> (
dbadmin(> Name,
dbadmin(> v2 filler VARCHAR(15),
dbadmin(> Contact as REGEXP_REPLACE(v2, '(\d{5})(\d{7})(\d{1})','\1-\2-\3')
dbadmin(> ) from '/home/dbadmin/Test5.csv' skip 1 delimiter ','
dbadmin-> rejected data as table errortest3;
 Rows Loaded
-------------
           1
(1 row)

dbadmin=> SELECT * FROM errortest3;
     node_name      |        file_name        |            session_id             |  transaction_id   | statement_id | batch_number | row_number |   rejected_data   | rejected_data_orig_length |                                    rejected_reason           
--------------------+-------------------------+-----------------------------------+-------------------+--------------+--------------+------------+-------------------+---------------------------+---------------------------------------------------------------------------------------
 v_test_db_node0001 | /home/dbadmin/Test5.csv | v_test_db_node0001-292870:0x24805 | 45035996274662197 |            1 |            0 |          1 | Tuple (TEST,Null) |                        17 | ERROR 2501:  Cannot set a NOT NULL column (Contact) to a NULL value in COPY statement
(1 row)

See:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/BulkLoadCOPY/CapturingLoadExceptionsAndRejections.htm