How to import CSV with copy and timestamp
Hello, how I can import Data from a CSV with timestamp: My CSV files are named like expMS0.csv - expMS1000. And a row looks like "06.02.2014;01:40:00;7748.78;6438.62;6003.05;104.38;55.44;394.18;1.19;27.57;44.34;152.53;34.93;101.9;46.22;14.45;66.09;2679.35;2442.12;1503.87;50.41;190.21;65.91;1545.27;17.74;836.2;144.62;-42.88;0.95;0.86;0.9;0.91;0.55;0.47;0.94;0.04;0.34;0.47;0.57;0.2;0.47;0.79;0.31;0.6;0.73;0.85;0.78;0.33;0.74;0.62;0.9;0.32;0.96;0.76;-0.24;0.26" So i need to convert the first to (06.02.2014;01:40:00) into a timestamp and insert it into "Datum" the rest should just copy to Power1-Power27 and Phi1-PhiP27. A insert statment looks like that: "INSERT INTO Daten_hour (power1,power2,power3,power4,power5,power6,power7,power8,power9,power10,power11,power12,power13,power14,power15,power16,power17,power18,power19,power20,power21,power22,power23,power24,power25,power26,power27,phi1,phi2,phi3,phi4,phi5,phi6,phi7,phi8,phi9,phi10,phi11,phi12,phi13,phi14,phi15,phi16,phi17,phi18,phi19,phi20,phi21,phi22,phi23,phi24,phi25,phi26,phi27, Datum)" How this works with the "Copy Statment" and where I need to set the files and where I need to execute the command. Thanks for help
+1Hi Sebastian!
First you need to create the target table
CREATE TABLE [tableName] (Datum TIMESTAMP, col1 Format1, col2 Format2,..., coln Formatn);
You would need to structure COPY as follows
COPY [tableName] (Datum, col1,col2,...,coln)
FROM LOCAL 'fileAddress'
WITH DELIMITER ',';
I can see you need to perform a transformation first, that you should do before trying COPY. You can choose to do this manually or scripted once, or I recall a part of the UDF documentation about LOADING UDFs.
First you need to create the target table
CREATE TABLE [tableName] (Datum TIMESTAMP, col1 Format1, col2 Format2,..., coln Formatn);
You would need to structure COPY as follows
COPY [tableName] (Datum, col1,col2,...,coln)
FROM LOCAL 'fileAddress'
WITH DELIMITER ',';
I can see you need to perform a transformation first, that you should do before trying COPY. You can choose to do this manually or scripted once, or I recall a part of the UDF documentation about LOADING UDFs.
+1https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/AdministratorsGuide/BulkLoadCOPY/IgnoringColumnsAndFieldsInTheLoadFile.htmVersion 1:
Data:
$ cat /tmp/treecol.data
06.02.2014;01:40:00;7748.78;6438.62
07.02.2014;00:12:13;1000.00;9999.99
08.09.2014;12:00:00;2000.00;8888.99
daniel=> create table version1 (d date, t time, tm timestamp default (d || ' '|| t)::timestamp, num1 float, num2 float);Version 2
CREATE TABLE
daniel=> copy version1(d format 'DD.MM.YYYY',t,num1,num2) from '/tmp/treecol.data' direct delimiter ';' abort on error;
Rows Loaded
-------------
3
(1 row)
daniel=> select * from version1;
d | t | tm | num1 | num2
------------+----------+---------------------+---------+---------
2014-02-06 | 01:40:00 | 2014-02-06 01:40:00 | 7748.78 | 6438.62
2014-02-07 | 00:12:13 | 2014-02-07 00:12:13 | 1000 | 9999.99
2014-09-08 | 12:00:00 | 2014-09-08 12:00:00 | 2000 | 8888.99
(3 rows)
daniel=> create table version2 (tm timestamp, num1 float, num2 float);
CREATE TABLE
daniel=> copy version2(datefiller filler varchar, timefiller filler varchar, tm as (datefiller || ' ' || timefiller)::timestamp,num1,num2) from '/tmp/treecol.data' direct delimiter ';' abort on error;
Rows Loaded
-------------
3
(1 row)
daniel=> select * from version2;
tm | num1 | num2
---------------------+---------+---------
2014-06-02 01:40:00 | 7748.78 | 6438.62
2014-07-02 00:12:13 | 1000 | 9999.99
2014-08-09 12:00:00 | 2000 | 8888.99
(3 rows)
Hello,
thanks for your reply.
I made my table already thats not the problem.
Also I can load the data with my own Programm line by line but I think the copy statement would be much much faster. I only need to transform the date and the time into a timestamp the rest i just need to insert.
And from local means the path from the root directory?
The rest values are all float.
thanks for your reply.
I made my table already thats not the problem.
Also I can load the data with my own Programm line by line but I think the copy statement would be much much faster. I only need to transform the date and the time into a timestamp the rest i just need to insert.
And from local means the path from the root directory?
The rest values are all float.
+1local - means path that not known and/or not mounted to Vertica cluster: remote, from client machine for example.
(read not known to Vertica executable instance/binary file)
(read not known to Vertica executable instance/binary file)
THANKS!!!!
Helped me so much.
Helped me so much.
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
