How to import CSV with copy and timestamp

Buchegger_Sebas

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

Said_Montiel

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

Daniel_Leybovic

https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/AdministratorsGuide/BulkLoadCOPY/IgnoringColumnsAndFieldsInTheLoadFile.htm 

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 

Version 1:

daniel=> create table version1 (d date, t time, tm timestamp default (d || ' '|| t)::timestamp, num1 float, num2 float);
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)

Version 2

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)

Buchegger_Sebas

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.

Daniel_Leybovic

local - 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)

Buchegger_Sebas

THANKS!!!!
Helped me so much.