Copy .tsv file into a table.

[Vertica_grm]

I'm trying to COPY a .tsv file into a vertica table. No rows are getting affected. What could be the reason?

syntax used:

COPY <schema_name>.<tablename>(<column_list>)
FROM LOCAL '<filepath>.tsv' DELIMITER E'\t' SKIP 1 DIRECT;

Results: [COPY - 0 row(s), 4.575 secs] Command processed. No rows were affected
number of columns in source and destinations match.

Any suggestions how to load this .tsv file?
.tsv is a tab seperated value

[JimKnicely]

I wonder if you are getting an error unrelated to the TAB character? Because E'\t' as a delimiter should work:

Code: Select all

[dbadmin@vertica01 ~]$ cat /home/dbadmin/tabs.tsv
c1      c2      c3      c4
1       2       3       Line1
4       5       6       Line2

[dbadmin@vertica01 ~]$ vsql -c "select * from public.tabs;"
 c1 | c2 | c3 | c4
----+----+----+----
(0 rows)

[dbadmin@vertica01 ~]$ vsql -c "copy public.tabs from local '/home/dbadmin/tabs.tsv' delimiter E'\t' skip 1 direct;"
 Rows Loaded
-------------
           2
(1 row)

[dbadmin@vertica01 ~]$ vsql -c "select * from public.tabs;"
 c1 | c2 | c3 |  c4
----+----+----+-------
  1 |  2 |  3 | Line1
  4 |  5 |  6 | Line2
(2 rows)

[Vertica_grm]

Thanks Jim for reply.

Its not an error. The message states "[COPY - 0 row(s), 4.575 secs] Command processed. No rows were affected" .

what other reasons could be for values not copying?

file structures seems file at source and destination.
I've tried to unicode in ANSI and UTF-8.

Nothing seems to work.

[JimKnicely]

You won't see an error message. Usually 0 Rows Loaded means that all the records were rejected. Try adding an EXCEPTIONS and REJECTED DATA clause to the COPY command.

Example:

Code: Select all

[dbadmin@vertica01 ~]$ cat tabs.tsv
c1      c2      c3      c4
Bad1    2       3       Line1
Bad4    5       6       Line2
Bad7    8       9       Line3

[dbadmin@vertica01 ~]$ vsql -c "\d public.tabs;"
                                   List of Fields by Tables
 Schema | Table | Column |    Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+-------------+------+---------+----------+-------------+-------------
 public | tabs  | c1     | int         |    8 |         | f        | f           |
 public | tabs  | c2     | int         |    8 |         | f        | f           |
 public | tabs  | c3     | int         |    8 |         | f        | f           |
 public | tabs  | c4     | varchar(25) |   25 |         | f        | f           |
(4 rows)

[dbadmin@vertica01 ~]$ vsql -c "copy public.tabs from local '/home/dbadmin/tabs.tsv' delimiter E'\t' skip 1 direct exceptions '/home/dbadmin/tabs.err' rejected data '/home/dbadmin/tabs.rej';"
 Rows Loaded
-------------
           0
(1 row)

[dbadmin@vertica01 ~]$ cat /home/dbadmin/tabs.err
COPY: Input record 1 has been rejected (Invalid integer format 'Bad1' for column 1 (c1)).  Please see /home/dbadmin/tabs.rej, record 1 for the rejected record. This record was read from tabs.tsv
COPY: Input record 2 has been rejected (Invalid integer format 'Bad4' for column 1 (c1)).  Please see /home/dbadmin/tabs.rej, record 2 for the rejected record. This record was read from tabs.tsv
COPY: Input record 3 has been rejected (Invalid integer format 'Bad7' for column 1 (c1)).  Please see /home/dbadmin/tabs.rej, record 3 for the rejected record. This record was read from tabs.tsv

[dbadmin@vertica01 ~]$ cat /home/dbadmin/tabs.rej
Bad1    2       3       Line1
Bad4    5       6       Line2
Bad7    8       9       Line3

I couldn't load the string values into column C1 because it has a data type of INT...

[Vertica_grm]

After adding the two parameters i got this

... Physical database connection acquired for: 41.1.136.113 Vertica
16:59:16 [COPY - 0 row(s), 5.326 secs] Command processed. No rows were affected
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 5.326/0.000 sec [0 successful, 1 warnings, 0 errors]

command which I ran was:

COPY har.clicks FROM LOCAL 'C:\Users\user\Desktop\user7096_2015-04-17_20-04\base_2015-04-17_20-04.tsv'
DELIMITER E'\t' SKIP 1 DIRECT exceptions 'C:\Users\Users\Desktop\Users_7096_2015-04-17_20-04\records.err'
Rejected Data 'C:\Users\Users\Desktop\Users7096_2015-04-17_20-04\records1.rej';

The rej file has this comment
"Input record 1 has been rejected (Invalid timestamp format '02-01-2015-00:37:07' for column 1 (Time).Invalid input syntax for timestamp: "02-01-2015-00:37:07")."

btw thanks a lot for your help. really appreciate you putting in the time. Hope we can crack it

[JimKnicely]

Hi,

I think the issue is with your TIMESTAMP format. There is a hyphen separating the date and time.

Vertica fails at converting the string to a TIMESTAMP because of the extra hyphen:

Code: Select all

dbadmin=> select '02-01-2015-00:37:07'::timestamp;
ERROR 2005:  Invalid input syntax for timestamp: "02-01-2015-00:37:07"

But it works fine without the hyphen:

Code: Select all

dbadmin=> select '02-01-2015 00:37:07'::timestamp;
      ?column?
---------------------
 2015-02-01 00:37:07
(1 row)

You can use the FILLER option of the COPY command to format the string properly.

Example:

Code: Select all

[dbadmin@vertica01 ~]$ cat tabs.tsv
c1      c2      c3      c4
02-01-2015-00:37:07     1       2       Line1
[dbadmin@vertica01 ~]$ vsql -c "select * from public.tabs;"
 c1 | c2 | c3 | c4
----+----+----+----
(0 rows)

[dbadmin@vertica01 ~]$ vsql -c "copy public.tabs (fixed_timestamp filler varchar(50), c1 as to_timestamp(fixed_timestamp, 'MM-DD-YYYY-HH24:MI:SS'), c2, c3, c4) from '/home/dbadmin/tabs.tsv' delimiter E'\t' skip 1 direct exceptions '/home/dbadmin/tabs.exp' rejected data '/home/dbadmin/tabs.rej';"
 Rows Loaded
-------------
           1
(1 row)

[dbadmin@vertica01 ~]$ vsql -c "select * from public.tabs;"
         c1          | c2 | c3 |  c4
---------------------+----+----+-------
 2015-02-01 00:37:07 |  1 |  2 | Line1
(1 row)

[Vertica_grm]

Yes the issue was with Timestamp column . I used COLUMN OPTION to overcome the problem.

thanks a lot!!