[
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.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/timeTest')
{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.timeTest;
+------+--------------------------+--------------------------+
| _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 wrong.
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 a 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/timeTest')
{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 store.hive.maprdb_json.read_timestamp_with_timezone_offset=true;
{code}
4. Run the query on the table from Drill using hive plugin:
{code}
0: jdbc:drill:drillbit=ldevdmhn005:31010> select * from hive.default.timeTest;
+------+--------------------------+--------------------------+
| _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}
Plese note that the results for {{eot}} and {{pst}} values are wrong.
> 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
> 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.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/timeTest')
> {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.timeTest;
> +------+--------------------------+--------------------------+
> | _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 wrong.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
