Hive
  1. Hive
  2. HIVE-6046

add UDF for converting date time from one presentation to another

    Details

    • Type: New Feature New Feature
    • Status: Patch Available
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 0.13.0
    • Fix Version/s: None
    • Component/s: UDF
    • Labels:
      None

      Description

      it'd be nice to have function for converting datetime to different formats, for example:
      format_date('2013-12-12 00:00:00.0', 'yyyy-MM-dd HH:mm:ss.S', 'yyyy/MM/dd')
      There are two signatures to facilitate further using:
      format_date(datetime, fromFormat, toFormat)
      format_date(timestamp, toFormat)

      1. Hive-6046.patch
        11 kB
        Kostiantyn Kudriavtsev
      2. HIVE-6046.patch
        11 kB
        Xuefu Zhang
      3. Hive-6046-Feb15.patch
        18 kB
        Kostiantyn Kudriavtsev

        Issue Links

          Activity

          Hide
          Xuefu Zhang added a comment -

          Kostiantyn Kudriavtsev Thanks for updating the patch.

          1. Please generate and name the patch, and post review according to the guidelines. For reference, here is hive "how to contribute" guideline: https://cwiki.apache.org/confluence/display/Hive/HowToContribute

          2. UDF str_to_date is expected to convert a given string of a given format to a Date object. Your implementation seems to return another string, which is not what the UDF is expected to do.

          3. let's not confuse Timestamp type with Date type. If the udf is named with date in it, we probably want to stick to Date data type, although it might be beneficial if it can take timestamp as input.

          Show
          Xuefu Zhang added a comment - Kostiantyn Kudriavtsev Thanks for updating the patch. 1. Please generate and name the patch, and post review according to the guidelines. For reference, here is hive "how to contribute" guideline: https://cwiki.apache.org/confluence/display/Hive/HowToContribute 2. UDF str_to_date is expected to convert a given string of a given format to a Date object. Your implementation seems to return another string, which is not what the UDF is expected to do. 3. let's not confuse Timestamp type with Date type. If the udf is named with date in it, we probably want to stick to Date data type, although it might be beneficial if it can take timestamp as input.
          Hide
          Kostiantyn Kudriavtsev added a comment -

          last patch

          Show
          Kostiantyn Kudriavtsev added a comment - last patch
          Hide
          Kostiantyn Kudriavtsev added a comment -

          Xuefu Zhang thank you for your assistance, will implement these two functions instead of original one soon

          Show
          Kostiantyn Kudriavtsev added a comment - Xuefu Zhang thank you for your assistance, will implement these two functions instead of original one soon
          Hide
          Xuefu Zhang added a comment -

          Kostiantyn Kudriavtsev Thanks for working on this. The code changes seem fine, but I have a couple of high-level questions:

          1. The UDF in the patch takes only a date in a string form, which seems too restrictive as Hive has a the data type of Date. Hive should be able to output the date to any format the user specifies. The udf isn't able to achieve this.

          2. The name of the UDF, format_date is questionable, because major DBs provide date_format() that does a different thing. This can cause unnecessary confusion.

          Thus, to have a bigger usage, I'd suggest the following:
          1. provide a UDF, such as to_date(string dateString, String format), that converts a given date in the given format to a Date date. to_date is also available in Oracle, while mysql has STR_TO_DATE.
          2. provide UDF, date_format(Date date, String format) as what Oracle or MySQL has.

          With these two, you should be able to achieve what you want while retaining bigger flexibility and usage.

          Show
          Xuefu Zhang added a comment - Kostiantyn Kudriavtsev Thanks for working on this. The code changes seem fine, but I have a couple of high-level questions: 1. The UDF in the patch takes only a date in a string form, which seems too restrictive as Hive has a the data type of Date. Hive should be able to output the date to any format the user specifies. The udf isn't able to achieve this. 2. The name of the UDF, format_date is questionable, because major DBs provide date_format() that does a different thing. This can cause unnecessary confusion. Thus, to have a bigger usage, I'd suggest the following: 1. provide a UDF, such as to_date(string dateString, String format), that converts a given date in the given format to a Date date. to_date is also available in Oracle, while mysql has STR_TO_DATE. 2. provide UDF, date_format(Date date, String format) as what Oracle or MySQL has. With these two, you should be able to achieve what you want while retaining bigger flexibility and usage.
          Hide
          Xuefu Zhang added a comment -

          I created a RB request: https://reviews.apache.org/r/18143/

          Show
          Xuefu Zhang added a comment - I created a RB request: https://reviews.apache.org/r/18143/
          Hide
          Xuefu Zhang added a comment -

          Regenerated patch.

          Show
          Xuefu Zhang added a comment - Regenerated patch.
          Hide
          Hive QA added a comment -

          Overall: -1 at least one tests failed

          Here are the results of testing the latest attachment:
          https://issues.apache.org/jira/secure/attachment/12628857/Hive-6046.patch

          ERROR: -1 due to 2 failed/errored test(s), 5098 tests executed
          Failed tests:

          org.apache.hadoop.hive.cli.TestMinimrCliDriver.testCliDriver_auto_sortmerge_join_16
          org.apache.hadoop.hive.cli.TestMinimrCliDriver.testCliDriver_bucketmapjoin6
          

          Test results: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/1318/testReport
          Console output: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/1318/console

          Messages:

          Executing org.apache.hive.ptest.execution.PrepPhase
          Executing org.apache.hive.ptest.execution.ExecutionPhase
          Executing org.apache.hive.ptest.execution.ReportingPhase
          Tests exited with: TestsFailedException: 2 tests failed
          

          This message is automatically generated.

          ATTACHMENT ID: 12628857

          Show
          Hive QA added a comment - Overall : -1 at least one tests failed Here are the results of testing the latest attachment: https://issues.apache.org/jira/secure/attachment/12628857/Hive-6046.patch ERROR: -1 due to 2 failed/errored test(s), 5098 tests executed Failed tests: org.apache.hadoop.hive.cli.TestMinimrCliDriver.testCliDriver_auto_sortmerge_join_16 org.apache.hadoop.hive.cli.TestMinimrCliDriver.testCliDriver_bucketmapjoin6 Test results: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/1318/testReport Console output: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/1318/console Messages: Executing org.apache.hive.ptest.execution.PrepPhase Executing org.apache.hive.ptest.execution.ExecutionPhase Executing org.apache.hive.ptest.execution.ReportingPhase Tests exited with: TestsFailedException: 2 tests failed This message is automatically generated. ATTACHMENT ID: 12628857
          Hide
          Kostiantyn Kudriavtsev added a comment -

          Xuefu Zhang I tried "git diff --no-prefix", but result is not accepted by ReviewBoard. It seems there is well known issue: https://code.google.com/p/reviewboard/issues/detail?id=1918 So, I attached file as regular

          Show
          Kostiantyn Kudriavtsev added a comment - Xuefu Zhang I tried "git diff --no-prefix", but result is not accepted by ReviewBoard. It seems there is well known issue: https://code.google.com/p/reviewboard/issues/detail?id=1918 So, I attached file as regular
          Hide
          Xuefu Zhang added a comment -

          Kostiantyn Kudriavtsev I'm wondering if you're still interested in moving this forward. As pointed above, you need to upload your patch to the review board so that people can commend on. Also, it seemed that your patch needed to be rebased with the latest trunk. When you generate your patch, please use "git diff --no-prefix" if you're using git. Thanks.

          Show
          Xuefu Zhang added a comment - Kostiantyn Kudriavtsev I'm wondering if you're still interested in moving this forward. As pointed above, you need to upload your patch to the review board so that people can commend on. Also, it seemed that your patch needed to be rebased with the latest trunk. When you generate your patch, please use "git diff --no-prefix" if you're using git. Thanks.
          Hide
          Xuefu Zhang added a comment -

          Kostiantyn Kudriavtsev You will need to upload the changes you made to the review board so that review can begin. The link you provided shows no diff.

          Show
          Xuefu Zhang added a comment - Kostiantyn Kudriavtsev You will need to upload the changes you made to the review board so that review can begin. The link you provided shows no diff.
          Hide
          Kostiantyn Kudriavtsev added a comment -

          Hi all, any progress with moving forward?
          because I don't see any activities w/ this issue/review

          Show
          Kostiantyn Kudriavtsev added a comment - Hi all, any progress with moving forward? because I don't see any activities w/ this issue/review
          Show
          Kostiantyn Kudriavtsev added a comment - https://reviews.apache.org/r/16679/
          Hide
          Xuefu Zhang added a comment -

          Kostiantyn Kudriavtsev Thanks for working on this. Could you please create a review board entry for your changes?

          Show
          Xuefu Zhang added a comment - Kostiantyn Kudriavtsev Thanks for working on this. Could you please create a review board entry for your changes?
          Hide
          Kostiantyn Kudriavtsev added a comment -

          Hi guys! Could you please review these changes and proceed with it?

          Show
          Kostiantyn Kudriavtsev added a comment - Hi guys! Could you please review these changes and proceed with it?
          Hide
          Kostiantyn Kudriavtsev added a comment -

          Hi all!
          The fix is available and all tests are passed. Could you please add it to Review Board and proceed w/ this issue?

          Show
          Kostiantyn Kudriavtsev added a comment - Hi all! The fix is available and all tests are passed. Could you please add it to Review Board and proceed w/ this issue?
          Hide
          Hive QA added a comment -

          Overall: +1 all checks pass

          Here are the results of testing the latest attachment:
          https://issues.apache.org/jira/secure/attachment/12620249/hive-6046.patch

          SUCCESS: +1 4814 tests passed

          Test results: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/735/testReport
          Console output: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/735/console

          Messages:

          Executing org.apache.hive.ptest.execution.PrepPhase
          Executing org.apache.hive.ptest.execution.ExecutionPhase
          Executing org.apache.hive.ptest.execution.ReportingPhase
          

          This message is automatically generated.

          ATTACHMENT ID: 12620249

          Show
          Hive QA added a comment - Overall : +1 all checks pass Here are the results of testing the latest attachment: https://issues.apache.org/jira/secure/attachment/12620249/hive-6046.patch SUCCESS: +1 4814 tests passed Test results: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/735/testReport Console output: http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/735/console Messages: Executing org.apache.hive.ptest.execution.PrepPhase Executing org.apache.hive.ptest.execution.ExecutionPhase Executing org.apache.hive.ptest.execution.ReportingPhase This message is automatically generated. ATTACHMENT ID: 12620249
          Hide
          Kostiantyn Kudriavtsev added a comment -

          Path is available, please review

          Show
          Kostiantyn Kudriavtsev added a comment - Path is available, please review
          Hide
          Kostiantyn Kudriavtsev added a comment -

          just start working on that, your comments are welcomed

          Show
          Kostiantyn Kudriavtsev added a comment - just start working on that, your comments are welcomed

            People

            • Assignee:
              Kostiantyn Kudriavtsev
              Reporter:
              Kostiantyn Kudriavtsev
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:

                Development