The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

Comments

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



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

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

  • 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)
  • THANKS!!!!
    Helped me so much.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file