Can't import data from MySQL using COPY

[user]

Hello All,

I'm Vertica Community Editions 6.0 (i downloaded it a couple days ago) with default settings.


I'm trying to import data from CSV file which was created from MySQL using "select field_list into outfile 'file/name' from table".
I'm using the following command to import data from file to Vertica

Code: Select all

COPY hl.data(array_of_fields) FROM '/home/dbadmin/dev/data.sql' DELIMITER E'\t';

Data file has approx 11M rows while Vertica can import only 3.1M rows.

I tried:
- read man (i thought there is some restrictions);
- i increased wosdata/sysdata.maxmemorysize up to 20G;
- If I'm trying to import the rest of file (head | tail) on each iteration Vertica takes the smaller amount of rows (3.1 -> 1.8 -> 1.2 -> 0.8 and I stopped);
- importing the same data to MySQL is fine.

Could someone please explain what I'm doing wrong?

thanks in advance.

[JimKnicely]

Hi!

Welcome to the forums!

What do you mean that Vertica can only import 3.1 million rows? Are there rows being rejected?

Check your ../CopyErrorLogs directory for errors, or explicity write exceptions out to a file by adding the EXCEPTIONS clause to your COPY command

Something like this:

Code: Select all

COPY hl.data(array_of_fields) FROM '/home/dbadmin/dev/data.sql' DELIMITER E'\t' EXCEPTIONS '/home/usr/dbadmin/hl.err';

You may be having issues with NULL values ....

[user]

Correct!

I experienced the problems with data conversions and nulls. Thanks.

There is another thing which is not clear for me with import.

I tried to change memorymaxsize (wosdata, sysdata) from 1-2G up to 30G and back. It's seems doesn't affect speed of my import. For me it's very strange. From my MySQL background I know that buffer are very import when i'm dealing with huge imports. May be I changed wrong parameters? Are there any other parameters similar `innodb_buffer_pool_size` in MySQL?

thanks!

[pborne]

You're loading to the WOS by default. If you have 11M rows to load, load into the ROS directly. To do this, use the DIRECT keyword in your copy command (see the doc for details)

[eli]

Regarding increasing wosdata what is the size of your input file ?