Comments

• Sandeep_Thool

  March 2014

  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;
  

  0
• Sajan_Sangraula

  March 2014

  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

  0
• Sandeep_Thool

  March 2014

  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

  0