3

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 ?

Kermit
  • 33,827
  • 13
  • 85
  • 121
roy
  • 6,344
  • 24
  • 92
  • 174
  • Before you commit the data, you can `EXTRACT(EPOCH FROM unix_timestamp` – Kermit Jul 24 '14 at 22:20
  • Sorry i didn't get your point. How do I use it into my above COPY command. – roy Jul 24 '14 at 22:23
  • Take a look at the [documentation](https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/SQLReferenceManual/Statements/COPY/COPY.htm). You need to explicitly define the column list. – Kermit Jul 24 '14 at 22:39
  • It seems as though you've decided to move away from [storing data in Unix Timestamp](http://stackoverflow.com/questions/24786367/unix-time-in-partition-by-for-vertica). Am I correct? – Kermit Jul 25 '14 at 01:40

1 Answers1

2

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
Kermit
  • 33,827
  • 13
  • 85
  • 121
  • Perfect This worked for me. COPY search_mmx2 (unix_timestamp FILLER VARCHAR(15),time_stamp AS TO_TIMESTAMPTZ(unix_timestamp),......) 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'; – roy Jul 25 '14 at 13:25
  • One quick question. How do I use partition by week on datetime:TIMESTAMPTZ – roy Jul 25 '14 at 14:32
  • 1
    @user3579198 Mind making a new question? I'll be happy to answer it. (Hint: you extract the year + week, and here's the [doc](https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/AdministratorsGuide/Partitions/DefiningPartitions.htm)) – Kermit Jul 25 '14 at 14:51
  • I have created new Q : https://stackoverflow.com/questions/24958830/vertica-partition-by-week-on-datetimetimestamptz – roy Jul 25 '14 at 15:26