How to handle duplicates in vertica ?

Moderator: NorbertKrupa

Post Reply
varuna.bhat
Newbie
Newbie
Posts: 5
Joined: Fri May 31, 2013 9:15 pm

How to handle duplicates in vertica ?

Post by varuna.bhat » Sat Jun 01, 2013 12:00 am

Hello,

Vertica will allow to load the same data file into table any number of time and it will create duplicate rows.

Is there any way to handle this duplicates ??

While data loading if some rows goes to error then what is the best way to handle it?

Thanks in advance.

User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Re: How to handle duplicates in vertica ?

Post by nnani » Mon Jun 03, 2013 8:03 am

Hello Varuna,

Welcome to VerticaForums.

In Vertica the constraints cannot be enforced as you do with Oracle. However there are some methodologies forllowed to get rid of duplicates.
Please go through the function ANALYZE CONSTRAINT. Maybe this can help you a bit.
Rest, this topic will definitely help
http://www.vertica-forums.com/viewtopic ... ates#p2695

The second half of your question:
While loading If there is any rejected data and you don't know the reason for it. You can check the rejected data file to see what data is rejected.
Notes
When loading data with the COPY statement, COPY considers the following data invalid:
 Missing columns (too few columns in an input line).
 Extra columns (too many columns in an input line).
 Empty columns for INTEGER or DATE/TIME data types. COPY does not use the default data values defined by the CREATE TABLE command, unless you do not supply a column option as part of the COPY statement.
 Incorrect representation of data type. For example, non-numeric data in an INTEGER column is invalid.
This type of data is considerd to be rejected data by Vertica.
The best way to handle it: Use No commit option while using COPY command. This parameter will not commit your data when it finishes the COPY statement. You can check your rejected data file for any records, If you find it. Correct the data and load (COPY) again.

Hope, this helped you.
nnani........
Long way to go

You can check out my blogs at vertica-howto

varuna.bhat
Newbie
Newbie
Posts: 5
Joined: Fri May 31, 2013 9:15 pm

Re: How to handle duplicates in vertica ?

Post by varuna.bhat » Tue Jun 04, 2013 11:58 pm

Thanks Nnani for your reply.

Post Reply

Return to “New to Vertica Database Development”