Bulk upload - transforming UNIX epoch time to Vertica TIMESTAMP column


I'm trying to bulk upload a file using COPY command. The file contains just 1 column (for arguments sake) which contains epoch timestamp data (sample below). I want to load this data to a Vertica table with a TIMESTAMP column in it.

Sample data :

What would be the right COPY command to do this transformation on the fly during load operation. I've tried below (& many other permutations) but it doesn't seem to be working.

COPY SchemaA.TableA(hour AS TO_TIMESTAMP(hour) ) FROM '/media/data/hour.csv' ABORT ON ERROR;

PS: ('hour' is the name of the timestamp column in TableA)



  • Options
    Well, since no one replied to this one, I had to come up with a solution for this myself! So here it is....

    My database table earlier had just 1 column ('hour') of type TIMESTAMP & I was trying to upload a UNIX epoch timestamp data from a file into it. (FYI - UNIX epoch timestamp data looks like an INTEGER).

    But I realized Vertica's bulk upload utility does not directly convert UNIX epoch timestamp in the file to Vertica's TIMESTAMP in database. So I created one more column in my table of type INTEGER. The intent being to first upload the UNIX epoch timestamp data an integer to this column & then transform it to TIMESTAMP & load it onto the new column that I created.

    So now with this setup, when I fire below statement I realized it works! It basically inserts the UNIX epoch timestamp data from the file as an INTEGER in the column 'hourAsInteger' & then it transforms it on the fly (during upload) to my TIMESTAMP column 'hour'.

    COPY SchemaA.TableA ( hourAsInteger,  hour AS TO_TIMESTAMP(hourAsInteger)) FROM '/media/data/hour.csv' ABORT ON ERROR;
  • Options
    Hi Sandeep,
    If you dont need the integer time in the table, you can avoid creating the extra column by using fillers. Here's a modified version of your copy statement:

    COPY SchemaA.TableA ( hourAsInteger filler (int),  hour AS TO_TIMESTAMP(hourAsInteger)) FROM '/media/data/hour.csv' ABORT ON ERROR;

    You can find more details on fillers at https://my.vertica.com/docs/6.1.x/HTML/index.htm#12431.htm.

  • Options
    Thanks Sajan.

    I actually figured this out yesterday after posting my response. I went through some Vertica documentation (above) & some external Vertica forums (e.g.: www.vertica-forums.com) where I was able to find some samples that helped me solve this with fillers.

    Thanks anyway; appreciate you replying to my post.


Leave a Comment

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