copy datetime from csv

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)

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

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,

@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.

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.

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

Cool! Glad you got it to work