Help with Copy command

Moderator: NorbertKrupa

adrian.oprea
Intermediate
Intermediate
Posts: 163
Joined: Tue Jun 19, 2012 2:44 pm
Location: Rio de Janeiro
Contact:

Help with Copy command

Post by adrian.oprea » Thu Jun 21, 2012 10:17 pm

Hy everyone ,,
Can enybody help me with this error :!!!
The error is when trying to load a csv file to Vertica .

"COPY: input record has been rejected (Invalid Integer format ' ' for column ............... ) "

- i am trying to copy a table of 35.000.000 rows from an Oracle database . I had made spool in csv format with ' , ' as column delimiter.
- good to remember that i have a lot of empty values in the exported table !!

I am new to toe Vertica , i have went thru the docs but still got the answer.!!!
Thank for the help!!
trying so hard !!!

adrian.oprea
Intermediate
Intermediate
Posts: 163
Joined: Tue Jun 19, 2012 2:44 pm
Location: Rio de Janeiro
Contact:

Re: Help with Copy command

Post by adrian.oprea » Thu Jun 21, 2012 10:18 pm

P.S. - the file is 34 gb in size !!
trying so hard !!!

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Help with Copy command

Post by JimKnicely » Fri Jun 22, 2012 2:47 pm

Hi Adrian,

Welcome to the forums!

In your file how are you representing the NULL values? If it is a double single quote ('') then Vertica will interpret it as a string which will cause the error you are getting.

Code: Select all

dbadmin=> \! cat /usr/home/dbadmin/load.txt;
test1,1,test1
test2,'',test2
test3,3,test3
dbadmin=> \d test;
                                    List of Fields by Tables
 Schema | Table | Column |     Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+--------------+------+---------+----------+-------------+-------------
 public | test  | col1   | varchar(100) |  100 |         | f        | f           |
 public | test  | col2   | int          |    8 |         | f        | f           |
 public | test  | col3   | varchar(100) |  100 |         | f        | f           |
(3 rows)

dbadmin=> copy test from '/usr/home/dbadmin/load.txt' delimiter ',' exceptions '/usr/home/dbadmin/load.err';
 Rows Loaded
-------------
           2
(1 row)

dbadmin=> \! cat /usr/home/dbadmin/load.err
COPY: Input record 2 has been rejected (Invalid integer format '''' for column 2 (col2)).  Please see /usr/local/data/intersect/v_intersect_node0001_catalog/CopyErrorLogs/test-load.txt-copy-from-rejected-data, record 1 for the rejected record.
COPY: Loaded 2 rows, rejected 1 rows.
You can use no values to represent NULL values, i.e. in my example you see the double comma. These load fine:

Code: Select all

dbadmin=> \! cat /usr/home/dbadmin/load.txt;
test1,1,test1
test2,,test2
test3,3,test3
dbadmin=> copy test from '/usr/home/dbadmin/load.txt' delimiter ',' null as '' exceptions '/usr/home/dbadmin/load.err';
 Rows Loaded
-------------
           3
(1 row)
Or you can specify some sort of text, i.e. "\n" or "NULL" and then use the NULL AS '' option of the copy command:

Code: Select all

dbadmin=> \! cat /usr/home/dbadmin/load.txt;
test1,1,test1
test2,null,test2
test3,3,test3
dbadmin=> copy test from '/usr/home/dbadmin/load.txt' delimiter ',' null as 'null' exceptions '/usr/home/dbadmin/load.err';
 Rows Loaded
-------------
           3
(1 row)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

adrian.oprea
Intermediate
Intermediate
Posts: 163
Joined: Tue Jun 19, 2012 2:44 pm
Location: Rio de Janeiro
Contact:

Re: Help with Copy command

Post by adrian.oprea » Tue Jun 26, 2012 10:12 pm

Thank you Jim , that did the trick !! :)
trying so hard !!!

johnr
Newbie
Newbie
Posts: 15
Joined: Fri Jul 27, 2012 6:30 am

Re: Help with Copy command

Post by johnr » Fri Jul 27, 2012 6:57 am

Hi there,

I need some help with the COPY command.

I have the date value coming in with a "/" but Vertica accepts only 8 characters.
I want to change this to just YYYYMMDD by using the FORMAT parameter in COPY command.

What is the syntax to include this?

Also, the incoming data has "|" within the data string and the delimiter is also "|". I was wondering if we can get around this by using
ESCAPE AS.

If somebody can help with the syntax for both these parameters that can be included the COPY it would be much appreciated.

Code snippet:

COPY $schema.$tablename from local \’$staging$filename\’ DELIMITER \’|\’ REJECTED DATA ’/home/dbadmin/ACTUALS/reject_sol.txt\’ DIRECT NULL AS \’\’ TRAILING NULLCOLS EXCEPTIONS \’$ERR_LOG_FILE\’;\” /home/dbadmin/scripts/var_sql.sql

Many Thanks,
-John

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Help with Copy command

Post by id10t » Fri Jul 27, 2012 8:52 am

Hi John!

  • Table:

    Code: Select all

    CREATE TABLE public.Dates
    (
        d1 date,
        d2 date
    );
    
  • COPY statement

    Code: Select all

    test_db=> copy Dates (d1 FORMAT 'YYYYMMDD', d2  FORMAT 'DDMMYYYY') from stdin DELIMITER ',';
    Enter data to be copied followed by a newline.
    End with a backslash and a period on a line by itself.
    >> 20120130,31012012
    >> \.
    

    Code: Select all

    test_db=> copy Dates (d1 FORMAT 'YYYYMMDD',d2) from stdin DELIMITER ',';
    Enter data to be copied followed by a newline.
    End with a backslash and a period on a line by itself.
    >> 20120130,2000-01-01
    >> \.
    
  • COPY - crazy date format with error in input!!!:

    Code: Select all

    test_db=> copy Dates (d1 FORMAT 'YYYY~MM~DD',d2 FORMAT 'YYYY##MM##DD') from stdin DELIMITER ',';
    Enter data to be copied followed by a newline.
    End with a backslash and a period on a line by itself.
    >> 2000~03~09,1999##12#31
    >> \.
    
  • Result

    Code: Select all

    test_db=> select * from Dates ;
         d1     |     d2     
    ------------+------------
     2012-01-30 | 2000-01-01
     2012-01-30 | 2012-01-31
     2000-03-09 | 1999-12-01
    (3 rows)
    

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Help with Copy command

Post by JimKnicely » Fri Jul 27, 2012 1:12 pm

johnr,
Also, the incoming data has "|" within the data string and the delimiter is also "|". I was wondering if we can get around this by using
ESCAPE AS.
Please check this post and see if it helps...

viewtopic.php?f=4&t=179&p=1006#p1006
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Data Load”