Problem while copying a file of size around 62GB to vertica


I am trying to copy a pipe delimited file which is around 62GB size into a table.

I did 2 things.
1. Wrote the copy command into a sql file and tried running it using \i option.
2. Pasted the copy command directly in the sql window and tried executing it.

For both the operations I have used 'DIRECT' option.

For the 2nd option, it takes around 15 to 20 mins to load the data into the target table.

For the 1st option, I waited for around 1 hr but it did not come through neither it throws any error. I have tried this multiple times but no luck.

Want to understand what is the best way to load such huge volume of data from a file to vertica.

Thanks in advance.


  • Options
    Which version of Vertica are you using ?
    And what is the network stats for this cluster ?

    And projections for target table , are those segmented or replicated ?
  • Options
    Vertica version is vertica-7.1.0-2.x86_64

    No segmentation/replication of the target table is done as of now.

    How do I get network stats for the cluster?
  • Options
    Please share the default super projection definition .
    output of below query :

    select export_objects('','table_name');

  • Options
    Hi Prativa, you seem to doing the right thing. If you have the option to zip the file, Vertica can load a compressed file directly and that could be faster. Also, you can avoid loading all the columns by specifying them as part of the copy statement and using fillers for columns that you dont need. 
  • Options
    But vertica is not loading when I put the copy command in the .sql file and execute it with \i command from the sql prompt. What could be going wrong?

  • Options
    Hello Prativa, Is there any Identity column in your table and is it increment value set to default?
  • Options
    Yes Siddarth there is an identity column for which the increment value is set to default.
  • Options
    Prativa, can you increase this default value(250K) to something bigger and then try to reload the file?

Leave a Comment

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