copy datetime from csv

jukanyajukanya
in General Discussion

Hello! I try to copy data from csv. But it fail with a strange massage

'SQL Error [2035] [22V04]: [Vertica]VJDBC ERROR: COPY: Input record 1 has been rejected (Invalid timestamp format '' for column 2 (mt_ins_dttm).Invalid input syntax for timestamp: "")
[Vertica]VJDBC ERROR: COPY: Input record 1 has been rejected (Invalid timestamp format '' for column 2 (mt_ins_dttm).Invalid input syntax for timestamp: "")
com.vertica.support.exceptions.DataException: [Vertica]VJDBC ERROR: COPY: Input record 1 has been rejected (Invalid timestamp format '' for column 2 (mt_ins_dttm).Invalid input syntax for timestamp: "")
'

Here is my csv
mt_process_id,mt_ins_dttm
973654,2017-08-28 21:52:06.2455712
970574,2017-07-05 21:43:54.6108347
973654,2017-07-10 21:57:38.3974173
970574,2017-07-10 21:57:38.3974173
970574,2017-07-18 22:04:19.2596357

Here is my script
CREATE TABLE sandbox_tableau.test_datetime(
mt_process_id bigint,
mt_ins_dttm datetime );

COPY sandbox_tableau.test_datetime
(
mt_process_id,
mt_ins_dttm
)
from local 'E:\TestVertica\test_datetime.csv' skip 1 ABORT ON ERROR REJECTMAX 0 delimiter ',';

What do I wrong?
Anna

Comments

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    Hi,

    Look like you are running this on Windows?

    It works for me...

    C:\Users\knicely>vsql -h verticademos.com -U dbadmin
Password:
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit

dbadmin=> create schema sandbox_tableau;
CREATE SCHEMA

dbadmin=> CREATE TABLE sandbox_tableau.test_datetime(
dbadmin(> mt_process_id bigint,
dbadmin(> mt_ins_dttm datetime );
CREATE TABLE

dbadmin=> COPY sandbox_tableau.test_datetime
dbadmin-> (
dbadmin(> mt_process_id,
dbadmin(> mt_ins_dttm
dbadmin(> )
dbadmin-> from local 'C:\test_datetime.csv' skip 1 ABORT ON ERROR REJECTMAX 0 delimiter ',';
 Rows Loaded
-------------
           5
(1 row)

dbadmin=> select * from sandbox_tableau.test_datetime;
 mt_process_id |        mt_ins_dttm
---------------+----------------------------
        970574 | 2017-07-05 21:43:54.610835
        970574 | 2017-07-10 21:57:38.397417
        970574 | 2017-07-18 22:04:19.259636
        973654 | 2017-07-10 21:57:38.397417
        973654 | 2017-08-28 21:52:06.245571
(5 rows)
  • s_crossmans_crossman Vertica Employee Employee

    Anna,

    Based on a quick test it appears that whatever tool you are using on the client side with JDBC maybe stripping the "skip 1" so your header in the source file is being treated liked a record. In vsql I removed the skip 1 and got the same error, with skip 1 it loaded fine. I tested it in SQuirreL using JDBC from my Windows client and got the same behavior, with skip 1 it worked and without it gave the error you are seeing.

    dbadmin=> COPY test_datetime(mt_process_id,mt_ins_dttm) from local 'time.csv' ABORT ON ERROR REJECTMAX 0 delimiter ',';
    ERROR 2035: COPY: Input record 1 has been rejected (Invalid integer format 'mt_process_id' for column 1 (mt_process_id))

    dbadmin=> COPY test_datetime(mt_process_id,mt_ins_dttm) from local 'time.csv' skip 1 ABORT ON ERROR REJECTMAX 0 delimiter ',';

    Rows Loaded

           5

    (1 row)

    In vsql you can look at the sessions table to see if the 'skip 1' does or doesn't get to Vertica. Below shows my run of the copy via SQuirreL and 'skip 1' is in the last statement for my session.

    dbadmin=> select transaction_start,user_name,client_hostname,last_statement from sessions;
    transaction_start | user_name | client_hostname | last_statement
    -------------------------------+-----------+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------
    2018-03-28 10:35:20.022885-04 | dbadmin | ::1:38402 | select * from sessions;
    2018-03-28 10:47:04.766302-04 | dbadmin | 10.10.244.150:50858 | COPY test_datetime(mt_process_id,mt_ins_dttm) from local 'c:/Users/crossman/Downloads/time.csv' skip 1 ABORT ON ERROR REJECTMAX 0 delimiter ','
    (2 rows)

    I'd recommend also checking the logs of the client tool you are using to see if they help debug this further.

    I hope it helps

  • s_crossmans_crossman Vertica Employee Employee

    Based on a quick test it appears your client side tool may be stripping the skip 1, so copy is trying to read the label row in your file. I tested in vsql and in SQuirreL, a JDBC Windows client. Both gave the same results. They both loaded the rows correctly, and both gave the error you are seeing if I took out 'skip 1'.

    dbadmin=> COPY test_datetime(mt_process_id,mt_ins_dttm) from local 'time.csv' ABORT ON ERROR REJECTMAX 0 delimiter ',';
    ERROR 2035: COPY: Input record 1 has been rejected (Invalid integer format 'mt_process_id' for column 1 (mt_process_id))

    dbadmin=> COPY test_datetime(mt_process_id,mt_ins_dttm) from local 'time.csv' skip 1 ABORT ON ERROR REJECTMAX 0 delimiter ',';

    Rows Loaded

           5

    (1 row)

    You can look in the sessions table in Vertica to see what the syntax of the command it got was. Example below shows my COPY statement included the 'skip 1'.

    dbadmin=> select transaction_start,user_name,client_hostname,last_statement from sessions;
    transaction_start | user_name | client_hostname | last_statement
    -------------------------------+-----------+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------
    2018-03-28 10:35:20.022885-04 | dbadmin | ::1:38402 | select * from sessions;
    2018-03-28 10:47:04.766302-04 | dbadmin | 10.10.244.150:50858 | COPY test_datetime(mt_process_id,mt_ins_dttm) from local 'c:/Users/crossman/Downloads/time.csv' skip 1 ABORT ON ERROR REJECTMAX 0 delimiter ','
    (2 rows)

    I'd recommend using the above and any logging your client side tool has to debug if skip 1 is being stripped, and why.

    I hope it helps,

  • jukanyajukanya
    edited March 2018

    @Jim_Knicely Yes on WINDOWS , I dont understand what is the difference with mine.....
    @s_crossman I tried dbeaver and dbvizualizer
    if i miss 'scip 1' I get another massage
    SQL Error [2035] [22V04]: [Vertica]VJDBC ERROR: COPY: Input record 1 has been rejected (Invalid integer format '��m' for column 1 (mt_process_id))
    [Vertica]VJDBC ERROR: COPY: Input record 1 has been rejected (Invalid integer format '��m' for column 1 (mt_process_id))
    com.vertica.support.exceptions.DataException: [Vertica]VJDBC ERROR: COPY: Input record 1 has been rejected (Invalid integer format '��m' for column 1 (mt_process_id))

    Your query shows that skip 1 was got/

    So i think its ok with skipping.

    Also without datetime field my script works.

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited March 2018

    You can remove the header line in the data file to verify that it's not the issue.

    Are you sure the file is in the right format (i.e. Windows DOS)?

    I attached the version I loaded successfully in both vsql (on Windows) and DbVisualizer.

  • jukanyajukanya

    it was encoarding brpblem thank you! converting to utf-8 helped

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    Cool! Glad you got it to work :)

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Format
SpoilerCodeQuote
Heading 2Heading 1
Emoji
:):D:(;):/:o:s:p:'(:|B):#:*<3o:)>:)
Url
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file
Home • General DiscussionComment As ...