Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-7150

Fix timezone conversion for timestamp from maprdb after the transition from PDT to PST

    XMLWordPrintableJSON

    Details

      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.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                volodymyr Vova Vysotskyi
                Reporter:
                volodymyr Vova Vysotskyi
                Reviewer:
                Aman Sinha
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: