[ https://issues.apache.org/jira/browse/DRILL-7150?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Volodymyr Vysotskyi updated DRILL-7150: --------------------------------------- Description: Steps to reproduce: 0. Set PST timezone and date {{date +%Y%m%d -s "20190329"}} 1. Create the table in MaprDB shell: {noformat} 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"}}' {noformat} 2. Create an external hive table: {code:sql} 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') {code} 3. Enable native reader and timezone conversion for MaprDB timestamp: {code:sql} 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; {code} 4. Run the query on the table from Drill using hive plugin: {code:java} 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) {code} Please note that timestamps for {{eot}} and {{pst}} values are incorrect. was: Steps to reproduce: 0. Set PST timezone and date {{date +%Y%m%d -s "20190329"}} 1. Create the table in MaprDB shell: {noformat} 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"}}' {noformat} 2. Create an external hive table: {code:sql} CREATE EXTERNAL TABLE default.timeTest (`_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') {code} 3. Enable native reader and timezone conversion for MaprDB timestamp: {code:sql} 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; {code} 4. Run the query on the table from Drill using hive plugin: {code:java} 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) {code} Please note that timestamps for {{eot}} and {{pst}} values are incorrect. > Fix timezone conversion for timestamp from maprdb after the transition from > PDT to PST > -------------------------------------------------------------------------------------- > > Key: DRILL-7150 > URL: https://issues.apache.org/jira/browse/DRILL-7150 > Project: Apache Drill > Issue Type: Bug > Components: Storage - MapRDB > Affects Versions: 1.16.0 > Reporter: Volodymyr Vysotskyi > Assignee: Volodymyr Vysotskyi > Priority: Major > Labels: ready-to-commit > Fix For: 1.16.0 > > > Steps to reproduce: > 0. Set PST timezone and date {{date +%Y%m%d -s "20190329"}} > 1. Create the table in MaprDB shell: > {noformat} > 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"}}' > {noformat} > 2. Create an external hive table: > {code:sql} > 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') > {code} > 3. Enable native reader and timezone conversion for MaprDB timestamp: > {code:sql} > 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; > {code} > 4. Run the query on the table from Drill using hive plugin: > {code:java} > 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) > {code} > Please note that timestamps for {{eot}} and {{pst}} values are incorrect. -- This message was sent by Atlassian JIRA (v7.6.3#76005)