Parquet Timestamp Inconsistent values Hive Vs AWS S3

bramhanaelem — Thu, 01 Jul 2021 18:14:50 GMT

Hi -

We are laoding data from sqoop to Hive table in parquest format where the dates are showing properly in hadoop.

We are distcping the data to AWS S3 and creating the tables in Athean using Glue Crawler.

When we query both tables(Hadoop & AWS) we are seeing different values for timestamp & date columns.

Hadoop Query Data:

SELECT effective_dt FROM "dbname"."table" where Pk_id='78393904'

output

0001-01-03 07:00:00.000

Athean Query Data

SELECT effective_dt FROM "dbname"."table" where Pk_id='78393904'

output

0001-01-01 12:00:00.000

Any suggestions how to overcome this issue.

Re: Parquet Timestamp Inconsistent values Hive Vs AWS S3

asish — Tue, 06 Jul 2021 05:47:25 GMT

By default hive displays in UTC. If you want to use specific timestamp,you can run below command

 SELECT from_utc_timestamp(cast(from_unixtime(cast(1623943533 AS bigint)) as TIMESTAMP),"Asia/Kolkata") ;