We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Add sequence numbers to a bulk load in vertica — Vertica Forum

Add sequence numbers to a bulk load in vertica

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

Comments

  • 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. 

     

  • 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;

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file