Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.