vertica copy unix epoch into TIMESTAMPTZ

Question

I want to load data into vertica from HDFS, but time_stamp value in data is in Unix epoch format and I want to load it into vertica as TIMESTAMPTZ in Vertica.

Dont know how to use cast function on the fly in this case. Can anyone help me with this please ?

I want to add casting into following

COPY search_mmx2 SOURCE Hdfs(url='http://hadoop-namenode:50070/webhdfs/v1/tmp/exported/2014/07/15/00/SEARCHES/part-m-0000*.bz2', username='xyz') filter BZip() DELIMITER E'\t';

Or is there any other/better way to do this ?

Answer 1

You would need to explicitly define the column list, use a filler and then derive it to transform the data type on load:

CREATE TABLE public.test (
  datetime TIMESTAMPTZ NOT NULL
);

COPY public.test (unix_timestamp FILLER VARCHAR(15),
                  datetime AS TO_TIMESTAMP(unix_timestamp))
FROM STDIN;

>> 1388552400
>> 1391230800
>> \.

SELECT * FROM public.test;

This gives us

        datetime
------------------------
 2014-02-01 00:00:00-05
 2014-01-01 00:00:00-05