Import a csv with 6496 obs. of 188 variables in Vertica

Carlos_1

I'm trying to import a csv file with 6496 observations and 188 variables, the size is about 4,7Mb.

The examples I saw have more simple tables, then I don't know how can I do it. Also I want to know how to manage huge csv and import them to Vertica.

Thanks!

Ian_Rubenstein

Is there a reason why this is so different than any other csv? There are some time saving things you can do, like building the create table statement by transposing the header of the csv and concatenating it with the data type like:

concat(",", B1, "as ",(if(type(b1)=1, "bigint", "string"))

you can use similar tricks to speed up the load local and quickly write the insert statement. There wont be a really fast way to handle any logic applied to the data as its getting in (like date formatting or conditionals) so you will have to go through them. It will be a long process of trial and error until all the fields and values load properly. 

Carlos_1

I've tried to insert the csv file using R and the sqlSave command, but it's to slow to insert the info from the client to Vertica.

The sqlSave command don't use any parser to create the sql insert, that it's a little bit complex with this number of variables.

I don't know if exist other way more quicker to import the csv file.

Lokesh_1

Hi Carlos,

Did you try copy with direct option ? If not, it might help to insert data quicker by loading data directly into ROS.

Carlos_1

no I didn't, but the examples Vertica Professionals explain always have the insert sentence. I have this file in csv format, and I can not load the data direct into ROS, well this is what I really want loaded from this file :-) directly.

Lokesh_1

If you have bulk data to insert then for fast loading (insert) of data, you should use "direct" keyword.

It case be used at the end of statement like

COPY fact FROM 'fact.txt' DELIMITER '|' EXCEPTIONS ' except.log' REJECTED DATA 'rejects.log' DIRECT ;

Or you can use insert with direct hint 

http://my.vertica.com/docs/5.0/HTML/Master/1342.htm

One of the above or both should make insert faster.