How to use ERROR TOLERANCE parameter in Vertica SQL?

Moderator: NorbertKrupa

Post Reply
atul.pdm@gmail.com
Newbie
Newbie
Posts: 7
Joined: Wed Oct 26, 2016 5:12 pm

How to use ERROR TOLERANCE parameter in Vertica SQL?

Post by atul.pdm@gmail.com » Wed Feb 22, 2017 5:56 pm

Hi,

I am getting error "Duplicate key values violates constraint 'public.table_name.C_UNIQUE' " while uploading the data to vertica table.
I need to use the ERROR TOLERANCE parameter in sql statement so that it treats each source during execution independently when loading dataThe invalid source is skipped and the load continues

I am using below query to upload the data to table,could you please suggest the correct syntax to use ERROR TOLERANCE parameter?

Code: Select all

vsql  -c "copy table_name (inputfile as 'file_name', hostname,groups, owner, job_name, job_number, unix_timestamp FILLER VARCHAR(15),submission_time AS TO_TIMESTAMPTZ(unix_timestamp) )  from local '/work/file_name' DELIMITER '|' NULL ''DIRECT REJECTED DATA '/tmp/test' TRAILING NULLCOLS;"

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: How to use ERROR TOLERANCE parameter in Vertica SQL?

Post by JimKnicely » Thu Feb 23, 2017 3:41 pm

Hi,

The ERROR TOLERANCE option of the COPY command is used when your are loading from multiple sources, i.e. more than one file.

It looks like you are loading from a single file. There is a REJECTMAX parameter which specifies a maximum number of logical records that can be rejected before a load fails. However, constraint errors (i.e. UK violations) are not considered rejects.

If you really want to load the data you will have to fix the data (remove dups) or disable the UK:

Code: Select all

ALTER TABLE public.table_name.C_UNIQUE ALTER CONSTRAINT C_UNIQUE DISABLED;

Once the data is loaded, there is a function that will help you find the duplicates:

Code: Select all

select analyze_constraints('public.table_name');
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “New to Vertica SQL”