CODEWITHSUNDEEP
csvhive
Florian
Florian

Reputation: 139

Hive - How can I store non-standard timestamps

I have a csv file containing timestamps like:

2018-01-01T12:13:14.000+01:00

I would like to store them as timestamp in Hive, is it possible to directly do it, or should I preprocess the csv file in order to have "better" timestamps ?

The following query is not able to correctly store them:

CREATE EXTERNAL TABLE IF NOT EXISTS test_timestamps(
    timestamp TIMESTAMP, 
    name STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
location '/test_timestamps/';

Thank you

Upvotes: 0

Views: 59

Answers (1)

nobody
nobody

Reputation: 11090

If you want to retain the format, store it as timestamp STRING and use the DATE functions to convert it to required format when you select from the table.

Note: All Hive keywords are case-insensitive,you might want to use a a proper name for the column instead of "timestamp".

select date_format(timestamp, "yyyy-MM-dd'T'hh:mm:ss.SSS'Z'"),name from test_timestamps;

Upvotes: 1

Related Questions