[DRILL-7150] Fix timezone conversion for timestamp from maprdb after the transition from PDT to PST

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.16.0
• Fix Version/s: 1.16.0
• Component/s: Storage - MapRDB
• Labels: ready-to-commit
• Assignee: Vova Vysotskyi
• Reporter: Vova Vysotskyi
• Reviewer: Aman Sinha
• Created: 02/Apr/19 14:40
• Updated: 05/Apr/19 23:10
• Resolved: 05/Apr/19 23:10

Description

Steps to reproduce:
0. Set PST timezone and date date +%Y%m%d -s "20190329"
1. Create the table in MaprDB shell:

create /tmp/testtimestamp
insert /tmp/testtimestamp --value '{"_id":"eot","str":"9999-01-01T23:59:59.999","ts":{"$date":"9999-01-02T07:59:59.999Z"}}'
insert /tmp/testtimestamp --value '{"_id":"pdt","str":"2019-04-01T23:59:59.999","ts":{"$date":"2019-04-02T06:59:59.999Z"}}'
insert /tmp/testtimestamp --value '{"_id":"pst","str":"2019-01-01T23:59:59.999","ts":{"$date":"2019-01-02T07:59:59.999Z"}}'
insert /tmp/testtimestamp --value '{"_id":"unk","str":"2017-07-08T20:01:49.885","ts":{"$date":"2017-07-09T03:01:49.885Z"}}'

2. Create an external hive table:

CREATE EXTERNAL TABLE default.testtimestamp
(`_id` string,
`str` string,
`ts` timestamp)
ROW FORMAT SERDE 'org.apache.hadoop.hive.maprdb.json.serde.MapRDBSerDe'  
STORED BY 'org.apache.hadoop.hive.maprdb.json.MapRDBJsonStorageHandler'  
TBLPROPERTIES ( 'maprdb.column.id'='_id', 'maprdb.table.name'='/tmp/testtimestamp')

3. Enable native reader and timezone conversion for MaprDB timestamp:

alter session set `store.hive.maprdb_json.optimize_scan_with_native_reader`=true;
alter session set `store.hive.maprdb_json.read_timestamp_with_timezone_offset`=true;

4. Run the query on the table from Drill using hive plugin:

0: jdbc:drill:drillbit=ldevdmhn005:31010> select * from hive.default.testtimestamp;
+------+--------------------------+--------------------------+
| _id  |           str            |            ts            |
+------+--------------------------+--------------------------+
| eot  | 9999-01-01T23:59:59.999  | 9999-01-02 00:59:59.999  |
| pdt  | 2019-04-01T23:59:59.999  | 2019-04-01 23:59:59.999  |
| pst  | 2019-01-01T23:59:59.999  | 2019-01-02 00:59:59.999  |
| unk  | 2017-07-08T20:01:49.885  | 2017-07-08 20:01:49.885  |
+------+--------------------------+--------------------------+
4 rows selected (0.343 seconds)

Please note that timestamps for eot and pst values are incorrect.

Issue Links

• is caused by: DRILL-6969 Inconsistent results when reading MaprDB JSON tables using hive plugin when native reader is enabled
• links to: GitHub Pull Request #1729