Add sequence numbers to a bulk load in vertica

babs4JESUS

Hi,

 

I am loading a huge csv to vertica using COPY command and I would like to include a sequence number to each record without modifying the csv file. I did not see anything in the documentation, but was wondering whether anyone has any idea !

 

Thanks

kaurora

Try setting up a column as an auto increment/Identitiy column.

Second option is to create and setup a sequence as a default on the column you want. 

Load your data using copy statement and specify your columns explicitly.

 

Be careful with the cache setting with sequences. Do NOT set it to a low value. Your bulk loads will slow down. 

 

babs4JESUS

Thanks Kaurora, you were to the point !

 

For the sake of others, I am listing explicitly what I did :

 

# Create sequence

create sequence name_seq NO MINVALUE;

 

# Bulk load along with sequence number.

COPY phlx.ob_snaps2 (seq  as name_seq.nextval, <COLUMN NAMES>) FROM LOCAL 'C:\Users\buthup\Documents\PHLX_Line3_20160624_OrderBookState.csv' DELIMITER ',' SKIP 1 ABORT ON ERROR TRAILING NULLCOLS;

 

# Drop sequence.

drop sequence name_seq;