Timestamp is different for the same table in hive-cli & presto-cli

user_13581322 0
Viewed 312

I am getting different timestamps for the same table in hive-cli & presto-cli.

table structure for the table:

+----------------------------------------------------+
|                   createtab_stmt                   |
+----------------------------------------------------+
| CREATE EXTERNAL TABLE `mea_req_201`(               |
|   `mer_id` bigint,                                 |
|   `mer_from_dttm` timestamp,                       |
|   `mer_to_dttm` timestamp,                         |
|   `_c0` bigint,                                    |
|   `a_number` string,                               |
|   `b_number` string,                               |
|   `time_stamp` timestamp,                          |
|   `duration` bigint)                               |
| PARTITIONED BY (                                   |
|   `partition_col` bigint)                          |
| ROW FORMAT SERDE                                   |
|   'org.apache.hadoop.hive.ql.io.orc.OrcSerde'      |
| STORED AS INPUTFORMAT                              |
|   'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'  |
| OUTPUTFORMAT                                       |
|   'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat' |
| LOCATION                                           |
|   'hdfs://hadoop5:8020/warehouse/tablespace/external/hive/mea_req_201' |
| TBLPROPERTIES (                                    |
|   'TRANSLATED_TO_EXTERNAL'='TRUE',                 |
|   'bucketing_version'='2',                         |
|   'external.table.purge'='TRUE',                   |
|   'spark.sql.create.version'='2.4.0.7.1.4.0-203',  |
|   'spark.sql.sources.schema.numPartCols'='1',      |
|   'spark.sql.sources.schema.numParts'='1',         |
|   'transient_lastDdlTime'='1625496239')            |
+----------------------------------------------------+

While running from hive-cli the output is:

enter image description here

While from presto-cli:

enter image description here

in mer_from_dttm col, there's a time difference but for other timestamps columns, dates are exactly the same. Note this time difference behaviour is the same when done from presto-jdbc also. I believe this got nothing to do with timezone because if it was timezone, the time difference should be across all timestamp columns, not just one. Please provide some resolution.

Some Facts:

  • Presto server version: 0.180

  • Presto Jdbc version: 0.180

  • hive.time-zone=Asia/Calcutta

  • In Presto jvm.config: -Duser.timezone=Asia/Calcutta

  • Client TimeZone: Asia/Calcutta

Edit 1: Sorted the query with mer_id to ensure both queries are outputting the same set of rows, However, the erroneous behavior still remains the same.

  • While Running from hive-cli: enter image description here

  • While Running from presto-cli: enter image description here

presto hive hadoop
2 Answers

Presto 0.180 is really old. It was released in 2017, and many bugs have been fixed along the way.

I would suggest you try with a recent version. In particular, recent versions of Trino (formerly known as PrestoSQL) had a lot work done around handling of timestamp data.

user_29587520

Use order by to see exact rows in each of clients.

SELECT `mer_id`,`mer_from_dttm`, `mer_to_dttm`, `time_stamp` FROM mea_req_201 ORDER BY `mer_id`;
user_25274580
Related