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

Prativa_Nayak

Hi

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.

MAYANK_1

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 ?

Prativa_Nayak

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?

MAYANK_1

Please share the default super projection definition .
output of below query :

select export_objects('','table_name');

Sajan_Sangraula

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. 

Prativa_Nayak

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?

Siddarth

Hello Prativa, Is there any Identity column in your table and is it increment value set to default?

Prativa_Nayak

Yes Siddarth there is an identity column for which the increment value is set to default.

Siddarth

Prativa, can you increase this default value(250K) to something bigger and then try to reload the file?