Details

    • Type: New Feature New Feature
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 0.9.0
    • Fix Version/s: None
    • Component/s: UDF
    • Labels:

      Description

      Current releases of Hive lacks a function which would return the day name corresponding to a date / timestamp value which might be a part of a column.
      The function -DAYNAME (date) would return the day name from a date / timestamp or column which would be useful while using HiveQL. This would find its use in various business sectors like retail, which would help in identifying the trends and sales datails for a particular weekday for entire year,month or week.

      Functionality :-

      Function Name: DAYNAME (date)

      Returns the name of the weekday for date.
      Example: hive> SELECT DAYNAME('2012-07-25');
      -> 'Wednesday'

      Usage :-

      Case 1 : To find DAY NAME corresponding to a particular date

      hive> SELECT DAYNAME('2012-07-25');

      -> 'Wednesday'

      Case 2 : To query a table to find details based on a particular day name
      Table :-
      date |item id|store id |value|unit|price

      01/07/2012|110001|0011111111003|0.99|1.00|0.99

      02/07/2012|110001|0011111111008|0.99|0.00|0.00

      03/07/2012|110001|0011111111009|0.99|0.00|0.00

      04/07/2012|110001|0011111112002|0.99|0.00|0.00

      05/07/2012|110001|0011111112003|0.99|0.00|0.00

      06/07/2012|110001|0011111112006|0.99|1.00|0.99

      07/07/2012|110001|0011111112007|0.99|0.00|0.00

      08/07/2012|110001|0011111112008|0.99|0.00|0.00

      09/07/2012|110001|0011111112009|0.99|0.00|0.00

      10/07/2012|110001|0011111112010|0.99|0.00|0.00

      11/07/2012|110001|0011111113003|0.99|0.00|0.00

      12/07/2012|110001|0011111113006|0.99|0.00|0.00

      13/07/2012|110001|0011111113008|0.99|0.00|0.00

      14/07/2012|110001|0011111113010|0.99|0.00|0.00

      15/07/2012|110001|0011111114002|0.99|0.00|0.00

      16/07/2012|110001|0011111114004|0.99|1.00|0.99

      17/07/2012|110001|0011111114005|0.99|0.00|0.00

      18/07/2012|110001|0011111121004|0.99|0.00|0.00

      Query : select * from sales where dayname(date)='wednesday';

      Result :-
      04/07/2012|110001|0011111112002|0.99|0.00|0.00

      11/07/2012|110001|0011111113003|0.99|0.00|0.00

      18/07/2012|110001|0011111121004|0.99|0.00|0.00

      1. Hive-3299_Testcase.doc
        26 kB
        Namitha Babychan
      2. HIVE-3299.1.patch.txt
        14 kB
        Namitha Babychan
      3. HIVE-3299.patch.txt
        11 kB
        Ann Dominic
      4. udf_dayname.q
        0.6 kB
        Namitha Babychan
      5. udf_dayname.q.out
        4 kB
        Namitha Babychan

        Activity

        Hide
        shalish added a comment -

        Current releases of Hive lacks a function which would return the day name corresponding to a date / timestamp value which might be a part of a column.
        The function -DAYNAME (date) would return the day name from a date / timestamp or column which would be useful while using HiveQL. This would find its use in various business sectors like retail, which would help in identifying the trends and sales datails for a particular weekday for entire year,month or week.

        Functionality :-

        Function Name: DAYNAME (date)

        Returns the name of the weekday for date.
        Example: hive> SELECT DAYNAME('2012-07-25');
        -> 'Wednesday'

        Usage :-

        Case 1 : To find DAY NAME corresponding to a particular date

        hive> SELECT DAYNAME('2012-07-25');

        -> 'Wednesday'

        Case 2 : To query a table to find details based on a particular day name
        Table :-
        date |item id|store id |value|unit|price

        01/07/2012|110001|0011111111003|0.99|1.00|0.99

        02/07/2012|110001|0011111111008|0.99|0.00|0.00

        03/07/2012|110001|0011111111009|0.99|0.00|0.00

        04/07/2012|110001|0011111112002|0.99|0.00|0.00

        05/07/2012|110001|0011111112003|0.99|0.00|0.00

        06/07/2012|110001|0011111112006|0.99|1.00|0.99

        07/07/2012|110001|0011111112007|0.99|0.00|0.00

        08/07/2012|110001|0011111112008|0.99|0.00|0.00

        09/07/2012|110001|0011111112009|0.99|0.00|0.00

        10/07/2012|110001|0011111112010|0.99|0.00|0.00

        11/07/2012|110001|0011111113003|0.99|0.00|0.00

        12/07/2012|110001|0011111113006|0.99|0.00|0.00

        13/07/2012|110001|0011111113008|0.99|0.00|0.00

        14/07/2012|110001|0011111113010|0.99|0.00|0.00

        15/07/2012|110001|0011111114002|0.99|0.00|0.00

        16/07/2012|110001|0011111114004|0.99|1.00|0.99

        17/07/2012|110001|0011111114005|0.99|0.00|0.00

        18/07/2012|110001|0011111121004|0.99|0.00|0.00

        Query : select * from sales where dayname(date)='wednesday';

        Result :-
        04/07/2012|110001|0011111112002|0.99|0.00|0.00

        11/07/2012|110001|0011111113003|0.99|0.00|0.00

        18/07/2012|110001|0011111121004|0.99|0.00|0.00

        Show
        shalish added a comment - Current releases of Hive lacks a function which would return the day name corresponding to a date / timestamp value which might be a part of a column. The function -DAYNAME (date) would return the day name from a date / timestamp or column which would be useful while using HiveQL. This would find its use in various business sectors like retail, which would help in identifying the trends and sales datails for a particular weekday for entire year,month or week. Functionality :- Function Name: DAYNAME (date) Returns the name of the weekday for date. Example: hive> SELECT DAYNAME('2012-07-25'); -> 'Wednesday' Usage :- Case 1 : To find DAY NAME corresponding to a particular date hive> SELECT DAYNAME('2012-07-25'); -> 'Wednesday' Case 2 : To query a table to find details based on a particular day name Table :- date |item id|store id |value|unit|price 01/07/2012|110001|0011111111003|0.99|1.00|0.99 02/07/2012|110001|0011111111008|0.99|0.00|0.00 03/07/2012|110001|0011111111009|0.99|0.00|0.00 04/07/2012|110001|0011111112002|0.99|0.00|0.00 05/07/2012|110001|0011111112003|0.99|0.00|0.00 06/07/2012|110001|0011111112006|0.99|1.00|0.99 07/07/2012|110001|0011111112007|0.99|0.00|0.00 08/07/2012|110001|0011111112008|0.99|0.00|0.00 09/07/2012|110001|0011111112009|0.99|0.00|0.00 10/07/2012|110001|0011111112010|0.99|0.00|0.00 11/07/2012|110001|0011111113003|0.99|0.00|0.00 12/07/2012|110001|0011111113006|0.99|0.00|0.00 13/07/2012|110001|0011111113008|0.99|0.00|0.00 14/07/2012|110001|0011111113010|0.99|0.00|0.00 15/07/2012|110001|0011111114002|0.99|0.00|0.00 16/07/2012|110001|0011111114004|0.99|1.00|0.99 17/07/2012|110001|0011111114005|0.99|0.00|0.00 18/07/2012|110001|0011111121004|0.99|0.00|0.00 Query : select * from sales where dayname(date)='wednesday'; Result :- 04/07/2012|110001|0011111112002|0.99|0.00|0.00 11/07/2012|110001|0011111113003|0.99|0.00|0.00 18/07/2012|110001|0011111121004|0.99|0.00|0.00
        Hide
        Bennie Schut added a comment -

        Currently you can get this trough a unix_timestamp function so if you don't want to wait for an implementation do something like this.
        select from_unixtime(unix_timestamp() , 'EEEE') from dummytable limit 1;
        or
        select from_unixtime(unix_timestamp('2012-07-25 00:00:00'), 'EEEE') from dummytable limit 1;

        However simply having a nice udf for it would be nice, like they have in mysql: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_dayname

        Show
        Bennie Schut added a comment - Currently you can get this trough a unix_timestamp function so if you don't want to wait for an implementation do something like this. select from_unixtime(unix_timestamp() , 'EEEE') from dummytable limit 1; or select from_unixtime(unix_timestamp('2012-07-25 00:00:00'), 'EEEE') from dummytable limit 1; However simply having a nice udf for it would be nice, like they have in mysql: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_dayname
        Hide
        shalish added a comment -

        Hi,

        Our team here is working on this UDF and would fix it ASAP.

        Show
        shalish added a comment - Hi, Our team here is working on this UDF and would fix it ASAP.
        Hide
        Namitha Babychan added a comment -

        patch for the issue hive-3299 is attached.

        Show
        Namitha Babychan added a comment - patch for the issue hive-3299 is attached.
        Hide
        Namitha Babychan added a comment -

        patch for the hive-3299 is available in the file hive-3299.1.patch

        Show
        Namitha Babychan added a comment - patch for the hive-3299 is available in the file hive-3299.1.patch
        Hide
        Carl Steinbach added a comment -

        @Namitha: The patch needs to apply cleanly with 'patch -p0 < patch.txt'. Please review the other tips for contributing patches here: https://cwiki.apache.org/confluence/display/Hive/HowToContribute

        Also, can you please add a testcase? Thanks.

        Show
        Carl Steinbach added a comment - @Namitha: The patch needs to apply cleanly with 'patch -p0 < patch.txt'. Please review the other tips for contributing patches here: https://cwiki.apache.org/confluence/display/Hive/HowToContribute Also, can you please add a testcase? Thanks.
        Hide
        Namitha Babychan added a comment -

        The patch for the issue Hive-3299 is attached

        Show
        Namitha Babychan added a comment - The patch for the issue Hive-3299 is attached
        Hide
        Namitha Babychan added a comment -

        The patch for issue Hive -3299 is attached

        Show
        Namitha Babychan added a comment - The patch for issue Hive -3299 is attached
        Hide
        Namitha Babychan added a comment -

        Testcases of the issue Hive-3299 is attached

        Show
        Namitha Babychan added a comment - Testcases of the issue Hive-3299 is attached
        Hide
        Namitha Babychan added a comment -

        @Carl Steinbach : I have attached the patch for the issue. Please review and let me know if any modifications has to be made. Thanks.

        Show
        Namitha Babychan added a comment - @Carl Steinbach : I have attached the patch for the issue. Please review and let me know if any modifications has to be made. Thanks.
        Hide
        Carl Steinbach added a comment -

        @Namitha: Thanks for adding the test doc. However, we prefer that each patch include an executable testcase. In this case it's OK to add a qfile testcase. Please take a look at the UDF qfile testcases in ql/src/test/queries/clientpositive/udf_*.q. For this patch you should add a new testcase called udf_dayname.q, and then generate test results for the test by running the following command:

        % ant test -Dtestcase=TestCliDriver -Dqfile=udf_dayname.q -Doverwrite=true

        More details are available in the HowToContribute document I referenced earlier.

        Thanks!

        Show
        Carl Steinbach added a comment - @Namitha: Thanks for adding the test doc. However, we prefer that each patch include an executable testcase. In this case it's OK to add a qfile testcase. Please take a look at the UDF qfile testcases in ql/src/test/queries/clientpositive/udf_*.q. For this patch you should add a new testcase called udf_dayname.q, and then generate test results for the test by running the following command: % ant test -Dtestcase=TestCliDriver -Dqfile=udf_dayname.q -Doverwrite=true More details are available in the HowToContribute document I referenced earlier. Thanks!
        Hide
        Namitha Babychan added a comment -

        Thanks for the guidelines.I am attaching the patch and the sample executable test queries and their outputs.

        Show
        Namitha Babychan added a comment - Thanks for the guidelines.I am attaching the patch and the sample executable test queries and their outputs.
        Hide
        Namitha Babychan added a comment -

        Hi Carl, Thanks alot..Please review and provide your valuable comments.

        Show
        Namitha Babychan added a comment - Hi Carl, Thanks alot..Please review and provide your valuable comments.
        Hide
        Carl Steinbach added a comment -

        @Namitha: A couple comments about the patch:

        • The patch causes a test failure in show_functions.q. It looks like the output for this test needs to be updated.
        • FunctionRegistry was modified to include UDFRpad twice. This looks like a cut-and-paste error. Please remove.
        • The formatting in UDFDayName doesn't adhere to the project guidelines (i.e. indent with two spaces, no TABS, comments on their own lines, etc).
        • In the future please submit review requests using either reviewboard (reviews.apache.org) or phabricator. Directions for the latter option are linked off of the HowToContribute wiki page.

        Thanks.

        Show
        Carl Steinbach added a comment - @Namitha: A couple comments about the patch: The patch causes a test failure in show_functions.q. It looks like the output for this test needs to be updated. FunctionRegistry was modified to include UDFRpad twice. This looks like a cut-and-paste error. Please remove. The formatting in UDFDayName doesn't adhere to the project guidelines (i.e. indent with two spaces, no TABS, comments on their own lines, etc). In the future please submit review requests using either reviewboard (reviews.apache.org) or phabricator. Directions for the latter option are linked off of the HowToContribute wiki page. Thanks.
        Hide
        Aniket Mokashi added a comment -

        @Carl, are we not considering adding new UDFs to builtin instead? There are several interesting udfs here - https://issues.apache.org/jira/browse/HIVE-2361.

        Show
        Aniket Mokashi added a comment - @Carl, are we not considering adding new UDFs to builtin instead? There are several interesting udfs here - https://issues.apache.org/jira/browse/HIVE-2361 .
        Hide
        Carl Steinbach added a comment -

        @Aniket: it would be great to get HIVE-2361 committed. At this point someone still needs to take the attached code from 2361 and submit a proper patch with testcases. As for the builtins subproject, at this point we haven't really started using that mechanism for new UDFs, and I'm a little worried that we may have remove some code rust before we can get it to work. If you have time to look into it that would great.

        Show
        Carl Steinbach added a comment - @Aniket: it would be great to get HIVE-2361 committed. At this point someone still needs to take the attached code from 2361 and submit a proper patch with testcases. As for the builtins subproject, at this point we haven't really started using that mechanism for new UDFs, and I'm a little worried that we may have remove some code rust before we can get it to work. If you have time to look into it that would great.
        Hide
        Arun A K added a comment -

        This is a quite useful UDF and hope it shall be taken into account. Please review the same- https://reviews.apache.org/r/9176/

        Show
        Arun A K added a comment - This is a quite useful UDF and hope it shall be taken into account. Please review the same- https://reviews.apache.org/r/9176/
        Hide
        Ann Dominic added a comment -

        The patch for the issue has been attached on 06/02/2013

        Show
        Ann Dominic added a comment - The patch for the issue has been attached on 06/02/2013
        Hide
        Kostiantyn Kudriavtsev added a comment -

        Please, take into account this is very specific user case. It makes sense to create as general as possible public to cover wide range of use cases. A few days ago I create ticket HIVE-6046 and working on that currently
        I'm pretty sure HIVE-6046 gives possibility to get name from date as described in the current ticket

        Show
        Kostiantyn Kudriavtsev added a comment - Please, take into account this is very specific user case. It makes sense to create as general as possible public to cover wide range of use cases. A few days ago I create ticket HIVE-6046 and working on that currently I'm pretty sure HIVE-6046 gives possibility to get name from date as described in the current ticket

          People

          • Assignee:
            Carl Steinbach
            Reporter:
            Namitha Babychan
          • Votes:
            5 Vote for this issue
            Watchers:
            13 Start watching this issue

            Dates

            • Created:
              Updated:

              Development