Bulk upload - transforming UNIX epoch time to Vertica TIMESTAMP column

Sandeep_Thool

Hi,

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 :
1393437624
1393437625
1393437628
1393437628

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)

Thanks

Sandeep_Thool

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;

Sajan_Sangraula

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.

/Sajan

Sandeep_Thool

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.

Sandeep