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
Copy .tsv file into a table.
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Copy .tsv file into a table.
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)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
-
- Newbie
- Posts: 17
- Joined: Tue Apr 21, 2015 8:35 pm
Re: Copy .tsv file into a table.
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.
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
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Copy .tsv file into a table.
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:
I couldn't load the string values into column C1 because it has a data type of INT...
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
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
-
- Newbie
- Posts: 17
- Joined: Tue Apr 21, 2015 8:35 pm
Re: Copy .tsv file into a table.
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
... 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
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Copy .tsv file into a table.
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:
But it works fine without the hyphen:
You can use the FILLER option of the COPY command to format the string properly.
Example:
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"
Code: Select all
dbadmin=> select '02-01-2015 00:37:07'::timestamp;
?column?
---------------------
2015-02-01 00:37:07
(1 row)
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)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
-
- Newbie
- Posts: 17
- Joined: Tue Apr 21, 2015 8:35 pm
Re: Copy .tsv file into a table.
Yes the issue was with Timestamp column . I used COLUMN OPTION to overcome the problem.
thanks a lot!!
thanks a lot!!