CODEWITHSUNDEEP
hadoophiveemrparquetpresto
uday sharma
uday sharma

Reputation: 109

Timestamp field shows 1970-01-01 in Presto 0.170 on EMR

I have an external Hive table pointing to a Parquet file written via a Spark job on s3, it has date, timestamp fields, when I query via hive, I get correct dates

CREATE EXTERNAL TABLE events(
event_date date, 
event_timestamp timestamp, 
event_name string, 
event_category string
PARTITIONED BY ( 
dateid  int, 
STORED AS PARQUET
LOCATION 's3a://somebucket/events'

hive> SELECT event_timestamp, event_date from events limit 10; 
2017-01-02 13:40:23 2017-01-02
2017-01-02 13:40:23.013 2017-01-02
2017-01-02 13:40:23.419 2017-01-02
2017-01-02 18:51:57.637 2017-01-02
2017-01-02 18:52:03.512 2017-01-02
2017-01-02 18:52:03.769 2017-01-02
2017-01-02 18:52:30.945 2017-01-02
2017-01-02 18:52:32.757 2017-01-02
2017-01-02 18:52:37.083 2017-01-02
2017-01-02 18:52:38.099 2017-01-02

However, when I run it via presto (version 0.170) running on EMR cluster version( emr-5.6.0) I see all dates as 1970-01-01

 presto-cli --catalog hive --schema default
presto:default> SELECT event_timestamp, event_date from events limit 10; 
 1970-01-01 00:00:17.197 | 1970-01-01 
 1970-01-01 00:00:17.197 | 1970-01-01 
 1970-01-01 00:00:17.197 | 1970-01-01 
 1970-01-01 00:00:17.197 | 1970-01-01 
 1970-01-01 00:00:17.197 | 1970-01-01 
 1970-01-01 00:00:17.197 | 1970-01-01 
 1970-01-01 00:00:17.197 | 1970-01-01 
 1970-01-01 00:00:17.197 | 1970-01-01 
 1970-01-01 00:00:17.197 | 1970-01-01 
 1970-01-01 00:00:17.197 | 1970-01-01

Is there any open issue with timestamp fields in Hive with Parquet querying via Presto?

Upvotes: 0

Views: 607

Answers (1)

uday sharma
uday sharma

Reputation: 109

After all online research and getting nowhere, I did comparison on order of fields in parquet file and hive DDL statement, it appears that during Spark job development the order of fields got changed. While hive was able to read columns via name, presto was going by the order. Thus a stupid mistake lead to a wild goose chase. Anyways, shamelessly answering my own question here to close the thread.

Upvotes: 1

Related Questions