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

Attach filesAttach ScreenshotVotersWatch issueWatchersCreate sub-taskLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    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

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

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

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment