[jira] [Commented] (HIVE-9357) Create ADD_MONTHS UDF
[
https://issues.apache.org/jira/browse/HIVE-9357?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14311797#comment-14311797
]
Alexander Pivovarov commented on HIVE-9357:
---
added the function description to the wiki
Create ADD_MONTHS UDF
-
Key: HIVE-9357
URL: https://issues.apache.org/jira/browse/HIVE-9357
Project: Hive
Issue Type: Improvement
Components: UDF
Reporter: Alexander Pivovarov
Assignee: Alexander Pivovarov
Fix For: 0.15.0
Attachments: HIVE-9357.1.patch, HIVE-9357.2.patch, HIVE-9357.3.patch
ADD_MONTHS adds a number of months to startdate:
add_months('2015-01-14', 1) = '2015-02-14'
add_months('2015-01-31', 1) = '2015-02-28'
add_months('2015-02-28', 2) = '2015-04-30'
add_months('2015-02-28', 12) = '2016-02-29'
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-9357) Create ADD_MONTHS UDF
[
https://issues.apache.org/jira/browse/HIVE-9357?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14283595#comment-14283595
]
Lefty Leverenz commented on HIVE-9357:
--
Doc note: This should be documented in the Date Functions section of the UDFs
wikidoc (with version information and a link to this issue).
* [Hive Operators and UDFs -- Date Functions |
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions]
A release note would also be helpful. Does the description show the committed
functionality, or was it modified after the discussion?
Create ADD_MONTHS UDF
-
Key: HIVE-9357
URL: https://issues.apache.org/jira/browse/HIVE-9357
Project: Hive
Issue Type: Improvement
Components: UDF
Reporter: Alexander Pivovarov
Assignee: Alexander Pivovarov
Labels: TODOC15
Fix For: 0.15.0
Attachments: HIVE-9357.1.patch, HIVE-9357.2.patch, HIVE-9357.3.patch
ADD_MONTHS adds a number of months to startdate:
add_months('2015-01-14', 1) = '2015-02-14'
add_months('2015-01-31', 1) = '2015-02-28'
add_months('2015-02-28', 2) = '2015-04-30'
add_months('2015-02-28', 12) = '2016-02-29'
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-9357) Create ADD_MONTHS UDF
[
https://issues.apache.org/jira/browse/HIVE-9357?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14281262#comment-14281262
]
Hive QA commented on HIVE-9357:
---
{color:red}Overall{color}: -1 at least one tests failed
Here are the results of testing the latest attachment:
https://issues.apache.org/jira/secure/attachment/12692793/HIVE-9357.3.patch
{color:red}ERROR:{color} -1 due to 6 failed/errored test(s), 7317 tests executed
*Failed tests:*
{noformat}
org.apache.hadoop.hive.cli.TestMinimrCliDriver.testCliDriver_index_bitmap_auto
org.apache.hadoop.hive.cli.TestSparkCliDriver.testCliDriver_mergejoins_mixed
org.apache.hadoop.hive.ql.TestMTQueries.testMTQueries1
org.apache.hadoop.hive.thrift.TestHadoop20SAuthBridge.testMetastoreProxyUser
org.apache.hadoop.hive.thrift.TestHadoop20SAuthBridge.testSaslWithHiveMetaStore
org.apache.hive.spark.client.TestSparkClient.testJobSubmission
{noformat}
Test results:
http://ec2-174-129-184-35.compute-1.amazonaws.com/jenkins/job/PreCommit-HIVE-TRUNK-Build/2403/testReport
Console output:
http://ec2-174-129-184-35.compute-1.amazonaws.com/jenkins/job/PreCommit-HIVE-TRUNK-Build/2403/console
Test logs:
http://ec2-174-129-184-35.compute-1.amazonaws.com/logs/PreCommit-HIVE-TRUNK-Build-2403/
Messages:
{noformat}
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: 6 tests failed
{noformat}
This message is automatically generated.
ATTACHMENT ID: 12692793 - PreCommit-HIVE-TRUNK-Build
Create ADD_MONTHS UDF
-
Key: HIVE-9357
URL: https://issues.apache.org/jira/browse/HIVE-9357
Project: Hive
Issue Type: Improvement
Components: UDF
Reporter: Alexander Pivovarov
Assignee: Alexander Pivovarov
Attachments: HIVE-9357.1.patch, HIVE-9357.2.patch, HIVE-9357.3.patch
ADD_MONTHS adds a number of months to startdate:
add_months('2015-01-14', 1) = '2015-02-14'
add_months('2015-01-31', 1) = '2015-02-28'
add_months('2015-02-28', 2) = '2015-04-30'
add_months('2015-02-28', 12) = '2016-02-29'
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-9357) Create ADD_MONTHS UDF
[
https://issues.apache.org/jira/browse/HIVE-9357?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14280007#comment-14280007
]
Jason Dere commented on HIVE-9357:
--
Before I check this in, can you add the Apache header to
GenericUDFAddMonths.java?
Create ADD_MONTHS UDF
-
Key: HIVE-9357
URL: https://issues.apache.org/jira/browse/HIVE-9357
Project: Hive
Issue Type: Improvement
Components: UDF
Reporter: Alexander Pivovarov
Assignee: Alexander Pivovarov
Attachments: HIVE-9357.1.patch, HIVE-9357.2.patch
ADD_MONTHS adds a number of months to startdate:
add_months('2015-01-14', 1) = '2015-02-14'
add_months('2015-01-31', 1) = '2015-02-28'
add_months('2015-02-28', 2) = '2015-04-30'
add_months('2015-02-28', 12) = '2016-02-29'
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-9357) Create ADD_MONTHS UDF
[
https://issues.apache.org/jira/browse/HIVE-9357?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14279039#comment-14279039
]
Alexander Pivovarov commented on HIVE-9357:
---
build 2366 has the same 3 errors as prev build 2365
Create ADD_MONTHS UDF
-
Key: HIVE-9357
URL: https://issues.apache.org/jira/browse/HIVE-9357
Project: Hive
Issue Type: Improvement
Components: UDF
Reporter: Alexander Pivovarov
Assignee: Alexander Pivovarov
Attachments: HIVE-9357.1.patch, HIVE-9357.2.patch
ADD_MONTHS adds a number of months to startdate:
add_months('2015-01-14', 1) = '2015-02-14'
add_months('2015-01-31', 1) = '2015-02-28'
add_months('2015-02-28', 2) = '2015-04-30'
add_months('2015-02-28', 12) = '2016-02-29'
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-9357) Create ADD_MONTHS UDF
[
https://issues.apache.org/jira/browse/HIVE-9357?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14279163#comment-14279163
]
Jason Dere commented on HIVE-9357:
--
Agreed, org.apache.hadoop.hive.ql.TestMTQueries.testMTQueries1,
org.apache.hive.hcatalog.streaming.TestStreaming.testEndpointConnection have
been failing on other builds, and
TestSparkCliDriver.testCliDriver_vectorization_part passes locally for me.
+1
Create ADD_MONTHS UDF
-
Key: HIVE-9357
URL: https://issues.apache.org/jira/browse/HIVE-9357
Project: Hive
Issue Type: Improvement
Components: UDF
Reporter: Alexander Pivovarov
Assignee: Alexander Pivovarov
Attachments: HIVE-9357.1.patch, HIVE-9357.2.patch
ADD_MONTHS adds a number of months to startdate:
add_months('2015-01-14', 1) = '2015-02-14'
add_months('2015-01-31', 1) = '2015-02-28'
add_months('2015-02-28', 2) = '2015-04-30'
add_months('2015-02-28', 12) = '2016-02-29'
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-9357) Create ADD_MONTHS UDF
[
https://issues.apache.org/jira/browse/HIVE-9357?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14277700#comment-14277700
]
Alexander Pivovarov commented on HIVE-9357:
---
yes, Orcale date + interval N month works different:
{code}
SELECT to_date('28-JAN-2014') + interval '1' month from dual;
2014-02-28
--add_month also returns 2014-02-28
SELECT to_date('29-JAN-2014') + interval '1' month from dual;
Error: ORA-01839: date not valid for month specified
--add_month returns 2014-02-28
SELECT to_date('28-FEB-2014') + interval '1' month from dual;
2014-03-28
--add_month returns 2014-03-31
{code}
So, it does not have any smart logic. it just increases month and checks if
date is valid. if valid it returns new date. if not it throws error ORA-01839
Create ADD_MONTHS UDF
-
Key: HIVE-9357
URL: https://issues.apache.org/jira/browse/HIVE-9357
Project: Hive
Issue Type: Improvement
Components: UDF
Reporter: Alexander Pivovarov
Assignee: Alexander Pivovarov
Attachments: HIVE-9357.1.patch
ADD_MONTHS adds a number of months to startdate:
add_months('2015-01-14', 1) = '2015-02-14'
add_months('2015-01-31', 1) = '2015-02-28'
add_months('2015-02-28', 2) = '2015-04-30'
add_months('2015-02-28', 12) = '2016-02-29'
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-9357) Create ADD_MONTHS UDF
[
https://issues.apache.org/jira/browse/HIVE-9357?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14277509#comment-14277509
]
Jason Dere commented on HIVE-9357:
--
Interesting - and the behavior for Oracle add_months() is actually different
than that of Oracle Interval behavior.
Create ADD_MONTHS UDF
-
Key: HIVE-9357
URL: https://issues.apache.org/jira/browse/HIVE-9357
Project: Hive
Issue Type: Improvement
Components: UDF
Reporter: Alexander Pivovarov
Assignee: Alexander Pivovarov
Attachments: HIVE-9357.1.patch
ADD_MONTHS adds a number of months to startdate:
add_months('2015-01-14', 1) = '2015-02-14'
add_months('2015-01-31', 1) = '2015-02-28'
add_months('2015-02-28', 2) = '2015-04-30'
add_months('2015-02-28', 12) = '2016-02-29'
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-9357) Create ADD_MONTHS UDF
[
https://issues.apache.org/jira/browse/HIVE-9357?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14278299#comment-14278299
]
Hive QA commented on HIVE-9357:
---
{color:red}Overall{color}: -1 at least one tests failed
Here are the results of testing the latest attachment:
https://issues.apache.org/jira/secure/attachment/12692426/HIVE-9357.2.patch
{color:red}ERROR:{color} -1 due to 3 failed/errored test(s), 7315 tests executed
*Failed tests:*
{noformat}
org.apache.hadoop.hive.cli.TestSparkCliDriver.testCliDriver_vectorization_part
org.apache.hadoop.hive.ql.TestMTQueries.testMTQueries1
org.apache.hive.hcatalog.streaming.TestStreaming.testEndpointConnection
{noformat}
Test results:
http://ec2-174-129-184-35.compute-1.amazonaws.com/jenkins/job/PreCommit-HIVE-TRUNK-Build/2366/testReport
Console output:
http://ec2-174-129-184-35.compute-1.amazonaws.com/jenkins/job/PreCommit-HIVE-TRUNK-Build/2366/console
Test logs:
http://ec2-174-129-184-35.compute-1.amazonaws.com/logs/PreCommit-HIVE-TRUNK-Build-2366/
Messages:
{noformat}
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: 3 tests failed
{noformat}
This message is automatically generated.
ATTACHMENT ID: 12692426 - PreCommit-HIVE-TRUNK-Build
Create ADD_MONTHS UDF
-
Key: HIVE-9357
URL: https://issues.apache.org/jira/browse/HIVE-9357
Project: Hive
Issue Type: Improvement
Components: UDF
Reporter: Alexander Pivovarov
Assignee: Alexander Pivovarov
Attachments: HIVE-9357.1.patch, HIVE-9357.2.patch
ADD_MONTHS adds a number of months to startdate:
add_months('2015-01-14', 1) = '2015-02-14'
add_months('2015-01-31', 1) = '2015-02-28'
add_months('2015-02-28', 2) = '2015-04-30'
add_months('2015-02-28', 12) = '2016-02-29'
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-9357) Create ADD_MONTHS UDF
[
https://issues.apache.org/jira/browse/HIVE-9357?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14278183#comment-14278183
]
Alexander Pivovarov commented on HIVE-9357:
---
HIVE-9357.2.patch - removed duplicate lines as Jason suggested
Create ADD_MONTHS UDF
-
Key: HIVE-9357
URL: https://issues.apache.org/jira/browse/HIVE-9357
Project: Hive
Issue Type: Improvement
Components: UDF
Reporter: Alexander Pivovarov
Assignee: Alexander Pivovarov
Attachments: HIVE-9357.1.patch, HIVE-9357.2.patch
ADD_MONTHS adds a number of months to startdate:
add_months('2015-01-14', 1) = '2015-02-14'
add_months('2015-01-31', 1) = '2015-02-28'
add_months('2015-02-28', 2) = '2015-04-30'
add_months('2015-02-28', 12) = '2016-02-29'
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-9357) Create ADD_MONTHS UDF
[
https://issues.apache.org/jira/browse/HIVE-9357?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14276449#comment-14276449
]
Alexander Pivovarov commented on HIVE-9357:
---
add_months function went from oracle
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions004.htm
I put several ADD_MONTHS examples below
{code}
select to_date('14-JAN-2014') from_date, 1 months, add_months('14-JAN-2014', 1)
res from dual union all
select to_date('31-JAN-2014') from_date, 1 months, add_months('31-JAN-2014', 1)
res from dual union all
select to_date('28-FEB-2014') from_date, -1 months, add_months('28-FEB-2014',
-1) res from dual union all
select to_date('28-FEB-2014') from_date, 2 months, add_months('28-FEB-2014', 2)
res from dual union all
select to_date('30-APR-2014') from_date, -2 months, add_months('30-APR-2014',
-2) res from dual union all
select to_date('28-FEB-2015') from_date, 12 months, add_months('28-FEB-2015',
12) res from dual union all
select to_date('29-FEB-2016') from_date, -12 months, add_months('29-FEB-2016',
-12) res from dual union all
select to_date('29-JAN-2016') from_date, 1 months, add_months('29-JAN-2016', 1)
res from dual union all
select to_date('29-FEB-2016') from_date, -1 months, add_months('29-FEB-2016',
-1) res from dual;
from_datemonths res
2014-01-14 12014-02-14
2014-01-31 12014-02-28
2014-02-28-12014-01-31
2014-02-28 22014-04-30
2014-04-30-22014-02-28
2015-02-28122016-02-29
2016-02-29 -122015-02-28
2016-01-29 12016-02-29
2016-02-29-12016-01-31
{code}
add_month function is used in many BI projects (especially in financial
applications (e.g. to determine 36 mo loan end date)).
From my experience most of BI projects was implemented in Oracle.
Lots of BI projects are migrating to Hive now.
So, lots of projects depends on Oracle add_months function business logic.
as a separate activity existing hive udf date_add can be improved to be similar
to MySQL implementation (if smbd need it)
I belive add_months udf brings lots of business value to hive because many
companies want easier migration from oracle fo hive.
I think Oracle is used in most of enterprise big data companies.
Create ADD_MONTHS UDF
-
Key: HIVE-9357
URL: https://issues.apache.org/jira/browse/HIVE-9357
Project: Hive
Issue Type: Improvement
Components: UDF
Reporter: Alexander Pivovarov
Assignee: Alexander Pivovarov
Attachments: HIVE-9357.1.patch
ADD_MONTHS adds a number of months to startdate:
add_months('2015-01-14', 1) = '2015-02-14'
add_months('2015-01-31', 1) = '2015-02-28'
add_months('2015-02-28', 2) = '2015-04-30'
add_months('2015-02-28', 12) = '2016-02-29'
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-9357) Create ADD_MONTHS UDF
[
https://issues.apache.org/jira/browse/HIVE-9357?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14276570#comment-14276570
]
Alexander Pivovarov commented on HIVE-9357:
---
according to Quora ADD_MONTS is #1 feature gap in hive
http://www.quora.com/Apache-Hive/What-are-the-biggest-feature-gaps-between-HiveQL-and-SQL
When people say ADD_MONTHS they mean Oracle ADD_MONTHS implementation logic.
I searched ADD_MONTHS finction in google - first page (first 10 results)
mention oracle add_months
https://www.google.com/#q=add_months+function
Create ADD_MONTHS UDF
-
Key: HIVE-9357
URL: https://issues.apache.org/jira/browse/HIVE-9357
Project: Hive
Issue Type: Improvement
Components: UDF
Reporter: Alexander Pivovarov
Assignee: Alexander Pivovarov
Attachments: HIVE-9357.1.patch
ADD_MONTHS adds a number of months to startdate:
add_months('2015-01-14', 1) = '2015-02-14'
add_months('2015-01-31', 1) = '2015-02-28'
add_months('2015-02-28', 2) = '2015-04-30'
add_months('2015-02-28', 12) = '2016-02-29'
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-9357) Create ADD_MONTHS UDF
[
https://issues.apache.org/jira/browse/HIVE-9357?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14276571#comment-14276571
]
Alexander Pivovarov commented on HIVE-9357:
---
From the Oracle reference on add_months
If date is the last day of the month or if the resulting month has fewer days
than the day component of date, then the result is the last day of the
resulting month. Otherwise, the result has the same day component as date.
Create ADD_MONTHS UDF
-
Key: HIVE-9357
URL: https://issues.apache.org/jira/browse/HIVE-9357
Project: Hive
Issue Type: Improvement
Components: UDF
Reporter: Alexander Pivovarov
Assignee: Alexander Pivovarov
Attachments: HIVE-9357.1.patch
ADD_MONTHS adds a number of months to startdate:
add_months('2015-01-14', 1) = '2015-02-14'
add_months('2015-01-31', 1) = '2015-02-28'
add_months('2015-02-28', 2) = '2015-04-30'
add_months('2015-02-28', 12) = '2016-02-29'
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-9357) Create ADD_MONTHS UDF
[
https://issues.apache.org/jira/browse/HIVE-9357?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14275817#comment-14275817
]
Alexander Pivovarov commented on HIVE-9357:
---
Review Board Request https://reviews.apache.org/r/29861/
Create ADD_MONTHS UDF
-
Key: HIVE-9357
URL: https://issues.apache.org/jira/browse/HIVE-9357
Project: Hive
Issue Type: Improvement
Components: UDF
Reporter: Alexander Pivovarov
Assignee: Alexander Pivovarov
Attachments: HIVE-9357.1.patch
ADD_MONTHS adds a number of months to startdate:
add_months('2015-01-14', 1) = '2015-02-14'
add_months('2015-01-31', 1) = '2015-02-28'
add_months('2015-02-28', 2) = '2015-04-30'
add_months('2015-02-28', 12) = '2016-02-29'
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-9357) Create ADD_MONTHS UDF
[
https://issues.apache.org/jira/browse/HIVE-9357?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14276404#comment-14276404
]
Jason Dere commented on HIVE-9357:
--
Not sure I agree with these last day of month semantics, at least when going
from a shorter month to a longer month. Take a look at the examples below for
both MySQL and Postgres. We might end up adding Interval type and I'd like the
behavior to be consistent between that and this udf.
{noformat}
mysql select date_add(date '2011-01-31', interval 1 month);
+---+
| date_add(date '2011-01-31', interval 1 month) |
+---+
| 2011-02-28|
+---+
1 row in set (0.00 sec)
mysql select date_add(date '2011-02-28', interval 1 month);
+---+
| date_add(date '2011-02-28', interval 1 month) |
+---+
| 2011-03-28|
+---+
1 row in set (0.00 sec)
postgres=# select date '2011-01-31' + interval '1 month';
?column?
-
2011-02-28 00:00:00
(1 row)
postgres=# select date '2011-02-28' + interval '1 month' ;
?column?
-
2011-03-28 00:00:00
(1 row)
{noformat}
Create ADD_MONTHS UDF
-
Key: HIVE-9357
URL: https://issues.apache.org/jira/browse/HIVE-9357
Project: Hive
Issue Type: Improvement
Components: UDF
Reporter: Alexander Pivovarov
Assignee: Alexander Pivovarov
Attachments: HIVE-9357.1.patch
ADD_MONTHS adds a number of months to startdate:
add_months('2015-01-14', 1) = '2015-02-14'
add_months('2015-01-31', 1) = '2015-02-28'
add_months('2015-02-28', 2) = '2015-04-30'
add_months('2015-02-28', 12) = '2016-02-29'
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
