Support Questions

Find answers, ask questions, and share your expertise

Parquet Timestamp Inconsistent values Hive Vs AWS S3

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.

1 REPLY 1

Expert Contributor

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") ;
Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.