You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@hive.apache.org by Simanchal Das <si...@outlook.com> on 2016/12/08 12:31:45 UTC
Re: Review Request 53845: 'like any' and 'like all' operators in hive
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/53845/
-----------------------------------------------------------
(Updated Dec. 8, 2016, 12:31 p.m.)
Review request for hive, Carl Steinbach and Vineet Garg.
Repository: hive-git
Description
-------
https://issues.apache.org/jira/browse/HIVE-15229
In Teradata 'like any' and 'like all' operators are mostly used when we are matching a text field with numbers of patterns.
'like any' and 'like all' operator are equivalents of multiple like operator like example below.
--like any
select col1 from table1 where col2 like any ('%accountant%', '%accounting%', '%retail%', '%bank%', '%insurance%');
--Can be written using multiple like condition
select col1 from table1 where col2 like '%accountant%' or col2 like '%accounting%' or col2 like '%retail%' or col2 like '%bank%' or col2 like '%insurance%' ;
--like all
select col1 from table1 where col2 like all ('%accountant%', '%accounting%', '%retail%', '%bank%', '%insurance%');
--Can be written using multiple like operator
select col1 from table1 where col2 like '%accountant%' and col2 like '%accounting%' and col2 like '%retail%' and col2 like '%bank%' and col2 like '%insurance%' ;
Problem statement:
Now a days so many data warehouse projects are being migrated from Teradata to Hive.
Always Data engineer and Business analyst are searching for these two operator.
If we introduce these two operator in hive then so many scripts will be migrated smoothly instead of converting these operators to multiple like operators.
Diffs (updated)
-----
ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java 0dbbc1d
ql/src/java/org/apache/hadoop/hive/ql/parse/HiveLexer.g 4357328
ql/src/java/org/apache/hadoop/hive/ql/parse/HiveParser.g 55915a6
ql/src/java/org/apache/hadoop/hive/ql/parse/IdentifiersParser.g a82083b
ql/src/java/org/apache/hadoop/hive/ql/parse/TypeCheckProcFactory.java 5e708d3
ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFLikeAll.java PRE-CREATION
ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFLikeAny.java PRE-CREATION
ql/src/test/org/apache/hadoop/hive/ql/udf/generic/TestGenericUDFLikeAll.java PRE-CREATION
ql/src/test/org/apache/hadoop/hive/ql/udf/generic/TestGenericUDFLikeAny.java PRE-CREATION
ql/src/test/queries/clientnegative/udf_likeall_wrong1.q PRE-CREATION
ql/src/test/queries/clientnegative/udf_likeany_wrong1.q PRE-CREATION
ql/src/test/queries/clientpositive/udf_likeall.q PRE-CREATION
ql/src/test/queries/clientpositive/udf_likeany.q PRE-CREATION
ql/src/test/results/clientnegative/udf_likeall_wrong1.q.out PRE-CREATION
ql/src/test/results/clientnegative/udf_likeany_wrong1.q.out PRE-CREATION
ql/src/test/results/clientpositive/udf_like.q.out 8ffcf9b
ql/src/test/results/clientpositive/udf_likeall.q.out PRE-CREATION
ql/src/test/results/clientpositive/udf_likeany.q.out PRE-CREATION
Diff: https://reviews.apache.org/r/53845/diff/
Testing
-------
Junit test cases and query.q files are attached
Thanks,
Simanchal Das
Re: Review Request 53845: 'like any' and 'like all' operators in hive
Posted by Simanchal Das <si...@outlook.com>.
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/53845/
-----------------------------------------------------------
(Updated April 22, 2017, 11:03 a.m.)
Review request for hive, Carl Steinbach and Vineet Garg.
Repository: hive-git
Description
-------
https://issues.apache.org/jira/browse/HIVE-15229
In Teradata 'like any' and 'like all' operators are mostly used when we are matching a text field with numbers of patterns.
'like any' and 'like all' operator are equivalents of multiple like operator like example below.
--like any
select col1 from table1 where col2 like any ('%accountant%', '%accounting%', '%retail%', '%bank%', '%insurance%');
--Can be written using multiple like condition
select col1 from table1 where col2 like '%accountant%' or col2 like '%accounting%' or col2 like '%retail%' or col2 like '%bank%' or col2 like '%insurance%' ;
--like all
select col1 from table1 where col2 like all ('%accountant%', '%accounting%', '%retail%', '%bank%', '%insurance%');
--Can be written using multiple like operator
select col1 from table1 where col2 like '%accountant%' and col2 like '%accounting%' and col2 like '%retail%' and col2 like '%bank%' and col2 like '%insurance%' ;
Problem statement:
Now a days so many data warehouse projects are being migrated from Teradata to Hive.
Always Data engineer and Business analyst are searching for these two operator.
If we introduce these two operator in hive then so many scripts will be migrated smoothly instead of converting these operators to multiple like operators.
Diffs (updated)
-----
ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java 8dc5f2e
ql/src/java/org/apache/hadoop/hive/ql/parse/HiveLexer.g 0721b92
ql/src/java/org/apache/hadoop/hive/ql/parse/HiveParser.g d98a663
ql/src/java/org/apache/hadoop/hive/ql/parse/IdentifiersParser.g 8598fae
ql/src/java/org/apache/hadoop/hive/ql/parse/TypeCheckProcFactory.java 8f8eab0
ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFLikeAll.java PRE-CREATION
ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFLikeAny.java PRE-CREATION
ql/src/test/org/apache/hadoop/hive/ql/udf/generic/TestGenericUDFLikeAll.java PRE-CREATION
ql/src/test/org/apache/hadoop/hive/ql/udf/generic/TestGenericUDFLikeAny.java PRE-CREATION
ql/src/test/queries/clientnegative/udf_likeall_wrong1.q PRE-CREATION
ql/src/test/queries/clientnegative/udf_likeany_wrong1.q PRE-CREATION
ql/src/test/queries/clientpositive/udf_likeall.q PRE-CREATION
ql/src/test/queries/clientpositive/udf_likeany.q PRE-CREATION
ql/src/test/results/clientnegative/udf_likeall_wrong1.q.out PRE-CREATION
ql/src/test/results/clientnegative/udf_likeany_wrong1.q.out PRE-CREATION
ql/src/test/results/clientpositive/show_functions.q.out ac5ca41
ql/src/test/results/clientpositive/udf_likeall.q.out PRE-CREATION
ql/src/test/results/clientpositive/udf_likeany.q.out PRE-CREATION
Diff: https://reviews.apache.org/r/53845/diff/9/
Changes: https://reviews.apache.org/r/53845/diff/8-9/
Testing
-------
Junit test cases and query.q files are attached
Thanks,
Simanchal Das
Re: Review Request 53845: 'like any' and 'like all' operators in hive
Posted by Simanchal Das <si...@outlook.com>.
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/53845/
-----------------------------------------------------------
(Updated March 2, 2017, 3:26 p.m.)
Review request for hive, Carl Steinbach and Vineet Garg.
Repository: hive-git
Description
-------
https://issues.apache.org/jira/browse/HIVE-15229
In Teradata 'like any' and 'like all' operators are mostly used when we are matching a text field with numbers of patterns.
'like any' and 'like all' operator are equivalents of multiple like operator like example below.
--like any
select col1 from table1 where col2 like any ('%accountant%', '%accounting%', '%retail%', '%bank%', '%insurance%');
--Can be written using multiple like condition
select col1 from table1 where col2 like '%accountant%' or col2 like '%accounting%' or col2 like '%retail%' or col2 like '%bank%' or col2 like '%insurance%' ;
--like all
select col1 from table1 where col2 like all ('%accountant%', '%accounting%', '%retail%', '%bank%', '%insurance%');
--Can be written using multiple like operator
select col1 from table1 where col2 like '%accountant%' and col2 like '%accounting%' and col2 like '%retail%' and col2 like '%bank%' and col2 like '%insurance%' ;
Problem statement:
Now a days so many data warehouse projects are being migrated from Teradata to Hive.
Always Data engineer and Business analyst are searching for these two operator.
If we introduce these two operator in hive then so many scripts will be migrated smoothly instead of converting these operators to multiple like operators.
Diffs (updated)
-----
ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java aaf2399
ql/src/java/org/apache/hadoop/hive/ql/parse/HiveLexer.g ad61f83
ql/src/java/org/apache/hadoop/hive/ql/parse/HiveParser.g 61778f6
ql/src/java/org/apache/hadoop/hive/ql/parse/IdentifiersParser.g 81efadc
ql/src/java/org/apache/hadoop/hive/ql/parse/TypeCheckProcFactory.java f979c14
ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFLikeAll.java PRE-CREATION
ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFLikeAny.java PRE-CREATION
ql/src/test/org/apache/hadoop/hive/ql/udf/generic/TestGenericUDFLikeAll.java PRE-CREATION
ql/src/test/org/apache/hadoop/hive/ql/udf/generic/TestGenericUDFLikeAny.java PRE-CREATION
ql/src/test/queries/clientnegative/udf_likeall_wrong1.q PRE-CREATION
ql/src/test/queries/clientnegative/udf_likeany_wrong1.q PRE-CREATION
ql/src/test/queries/clientpositive/udf_likeall.q PRE-CREATION
ql/src/test/queries/clientpositive/udf_likeany.q PRE-CREATION
ql/src/test/results/clientnegative/udf_likeall_wrong1.q.out PRE-CREATION
ql/src/test/results/clientnegative/udf_likeany_wrong1.q.out PRE-CREATION
ql/src/test/results/clientpositive/show_functions.q.out 3c9bb4a
ql/src/test/results/clientpositive/udf_likeall.q.out PRE-CREATION
ql/src/test/results/clientpositive/udf_likeany.q.out PRE-CREATION
Diff: https://reviews.apache.org/r/53845/diff/8/
Changes: https://reviews.apache.org/r/53845/diff/7-8/
Testing
-------
Junit test cases and query.q files are attached
Thanks,
Simanchal Das
Re: Review Request 53845: 'like any' and 'like all' operators in hive
Posted by Simanchal Das <si...@outlook.com>.
> On March 2, 2017, 4:57 a.m., Carl Steinbach wrote:
> > ql/src/java/org/apache/hadoop/hive/ql/parse/HiveLexer.g
> > Line 2 (original), 2 (patched)
> > <https://reviews.apache.org/r/53845/diff/7/?file=1634262#file1634262line2>
> >
> > Please make whitespace fixes in a separate patch. Including them here pollutes the diff and draws the attention of reviewers away from the important parts. I saw the same problem in several other files as well.
Added new patch after fixing whitespaces.
> On March 2, 2017, 4:57 a.m., Carl Steinbach wrote:
> > ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFLikeAll.java
> > Lines 51 (patched)
> > <https://reviews.apache.org/r/53845/diff/7/?file=1634266#file1634266line51>
> >
> > Please change to "Returns NULL if the expression on the left hand side is NULL or if one of the patterns in the list is NULL."
Added this message.
- Simanchal
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/53845/#review167648
-----------------------------------------------------------
On Feb. 15, 2017, 6:47 a.m., Simanchal Das wrote:
>
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/53845/
> -----------------------------------------------------------
>
> (Updated Feb. 15, 2017, 6:47 a.m.)
>
>
> Review request for hive, Carl Steinbach and Vineet Garg.
>
>
> Repository: hive-git
>
>
> Description
> -------
>
> https://issues.apache.org/jira/browse/HIVE-15229
>
>
> In Teradata 'like any' and 'like all' operators are mostly used when we are matching a text field with numbers of patterns.
> 'like any' and 'like all' operator are equivalents of multiple like operator like example below.
> --like any
> select col1 from table1 where col2 like any ('%accountant%', '%accounting%', '%retail%', '%bank%', '%insurance%');
>
> --Can be written using multiple like condition
> select col1 from table1 where col2 like '%accountant%' or col2 like '%accounting%' or col2 like '%retail%' or col2 like '%bank%' or col2 like '%insurance%' ;
>
> --like all
> select col1 from table1 where col2 like all ('%accountant%', '%accounting%', '%retail%', '%bank%', '%insurance%');
>
> --Can be written using multiple like operator
> select col1 from table1 where col2 like '%accountant%' and col2 like '%accounting%' and col2 like '%retail%' and col2 like '%bank%' and col2 like '%insurance%' ;
>
> Problem statement:
>
> Now a days so many data warehouse projects are being migrated from Teradata to Hive.
> Always Data engineer and Business analyst are searching for these two operator.
> If we introduce these two operator in hive then so many scripts will be migrated smoothly instead of converting these operators to multiple like operators.
>
>
> Diffs
> -----
>
> ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java 0f05160
> ql/src/java/org/apache/hadoop/hive/ql/parse/HiveLexer.g f80642b
> ql/src/java/org/apache/hadoop/hive/ql/parse/HiveParser.g eb81393
> ql/src/java/org/apache/hadoop/hive/ql/parse/IdentifiersParser.g 81efadc
> ql/src/java/org/apache/hadoop/hive/ql/parse/TypeCheckProcFactory.java f979c14
> ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFLikeAll.java PRE-CREATION
> ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFLikeAny.java PRE-CREATION
> ql/src/test/org/apache/hadoop/hive/ql/udf/generic/TestGenericUDFLikeAll.java PRE-CREATION
> ql/src/test/org/apache/hadoop/hive/ql/udf/generic/TestGenericUDFLikeAny.java PRE-CREATION
> ql/src/test/queries/clientnegative/udf_likeall_wrong1.q PRE-CREATION
> ql/src/test/queries/clientnegative/udf_likeany_wrong1.q PRE-CREATION
> ql/src/test/queries/clientpositive/udf_likeall.q PRE-CREATION
> ql/src/test/queries/clientpositive/udf_likeany.q PRE-CREATION
> ql/src/test/results/clientnegative/udf_likeall_wrong1.q.out PRE-CREATION
> ql/src/test/results/clientnegative/udf_likeany_wrong1.q.out PRE-CREATION
> ql/src/test/results/clientpositive/show_functions.q.out 3c9bb4a
> ql/src/test/results/clientpositive/udf_likeall.q.out PRE-CREATION
> ql/src/test/results/clientpositive/udf_likeany.q.out PRE-CREATION
>
>
> Diff: https://reviews.apache.org/r/53845/diff/7/
>
>
> Testing
> -------
>
> Junit test cases and query.q files are attached
>
>
> Thanks,
>
> Simanchal Das
>
>
Re: Review Request 53845: 'like any' and 'like all' operators in hive
Posted by Carl Steinbach <cw...@apache.org>.
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/53845/#review167648
-----------------------------------------------------------
ql/src/java/org/apache/hadoop/hive/ql/parse/HiveLexer.g
Line 2 (original), 2 (patched)
<https://reviews.apache.org/r/53845/#comment239556>
Please make whitespace fixes in a separate patch. Including them here pollutes the diff and draws the attention of reviewers away from the important parts. I saw the same problem in several other files as well.
ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFLikeAll.java
Lines 51 (patched)
<https://reviews.apache.org/r/53845/#comment239559>
Please change to "Returns NULL if the expression on the left hand side is NULL or if one of the patterns in the list is NULL."
- Carl Steinbach
On Feb. 15, 2017, 6:47 a.m., Simanchal Das wrote:
>
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/53845/
> -----------------------------------------------------------
>
> (Updated Feb. 15, 2017, 6:47 a.m.)
>
>
> Review request for hive, Carl Steinbach and Vineet Garg.
>
>
> Repository: hive-git
>
>
> Description
> -------
>
> https://issues.apache.org/jira/browse/HIVE-15229
>
>
> In Teradata 'like any' and 'like all' operators are mostly used when we are matching a text field with numbers of patterns.
> 'like any' and 'like all' operator are equivalents of multiple like operator like example below.
> --like any
> select col1 from table1 where col2 like any ('%accountant%', '%accounting%', '%retail%', '%bank%', '%insurance%');
>
> --Can be written using multiple like condition
> select col1 from table1 where col2 like '%accountant%' or col2 like '%accounting%' or col2 like '%retail%' or col2 like '%bank%' or col2 like '%insurance%' ;
>
> --like all
> select col1 from table1 where col2 like all ('%accountant%', '%accounting%', '%retail%', '%bank%', '%insurance%');
>
> --Can be written using multiple like operator
> select col1 from table1 where col2 like '%accountant%' and col2 like '%accounting%' and col2 like '%retail%' and col2 like '%bank%' and col2 like '%insurance%' ;
>
> Problem statement:
>
> Now a days so many data warehouse projects are being migrated from Teradata to Hive.
> Always Data engineer and Business analyst are searching for these two operator.
> If we introduce these two operator in hive then so many scripts will be migrated smoothly instead of converting these operators to multiple like operators.
>
>
> Diffs
> -----
>
> ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java 0f05160
> ql/src/java/org/apache/hadoop/hive/ql/parse/HiveLexer.g f80642b
> ql/src/java/org/apache/hadoop/hive/ql/parse/HiveParser.g eb81393
> ql/src/java/org/apache/hadoop/hive/ql/parse/IdentifiersParser.g 81efadc
> ql/src/java/org/apache/hadoop/hive/ql/parse/TypeCheckProcFactory.java f979c14
> ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFLikeAll.java PRE-CREATION
> ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFLikeAny.java PRE-CREATION
> ql/src/test/org/apache/hadoop/hive/ql/udf/generic/TestGenericUDFLikeAll.java PRE-CREATION
> ql/src/test/org/apache/hadoop/hive/ql/udf/generic/TestGenericUDFLikeAny.java PRE-CREATION
> ql/src/test/queries/clientnegative/udf_likeall_wrong1.q PRE-CREATION
> ql/src/test/queries/clientnegative/udf_likeany_wrong1.q PRE-CREATION
> ql/src/test/queries/clientpositive/udf_likeall.q PRE-CREATION
> ql/src/test/queries/clientpositive/udf_likeany.q PRE-CREATION
> ql/src/test/results/clientnegative/udf_likeall_wrong1.q.out PRE-CREATION
> ql/src/test/results/clientnegative/udf_likeany_wrong1.q.out PRE-CREATION
> ql/src/test/results/clientpositive/show_functions.q.out 3c9bb4a
> ql/src/test/results/clientpositive/udf_likeall.q.out PRE-CREATION
> ql/src/test/results/clientpositive/udf_likeany.q.out PRE-CREATION
>
>
> Diff: https://reviews.apache.org/r/53845/diff/7/
>
>
> Testing
> -------
>
> Junit test cases and query.q files are attached
>
>
> Thanks,
>
> Simanchal Das
>
>
Re: Review Request 53845: 'like any' and 'like all' operators in hive
Posted by Simanchal Das <si...@outlook.com>.
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/53845/
-----------------------------------------------------------
(Updated Feb. 15, 2017, 6:47 a.m.)
Review request for hive, Carl Steinbach and Vineet Garg.
Repository: hive-git
Description
-------
https://issues.apache.org/jira/browse/HIVE-15229
In Teradata 'like any' and 'like all' operators are mostly used when we are matching a text field with numbers of patterns.
'like any' and 'like all' operator are equivalents of multiple like operator like example below.
--like any
select col1 from table1 where col2 like any ('%accountant%', '%accounting%', '%retail%', '%bank%', '%insurance%');
--Can be written using multiple like condition
select col1 from table1 where col2 like '%accountant%' or col2 like '%accounting%' or col2 like '%retail%' or col2 like '%bank%' or col2 like '%insurance%' ;
--like all
select col1 from table1 where col2 like all ('%accountant%', '%accounting%', '%retail%', '%bank%', '%insurance%');
--Can be written using multiple like operator
select col1 from table1 where col2 like '%accountant%' and col2 like '%accounting%' and col2 like '%retail%' and col2 like '%bank%' and col2 like '%insurance%' ;
Problem statement:
Now a days so many data warehouse projects are being migrated from Teradata to Hive.
Always Data engineer and Business analyst are searching for these two operator.
If we introduce these two operator in hive then so many scripts will be migrated smoothly instead of converting these operators to multiple like operators.
Diffs (updated)
-----
ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java 0f05160
ql/src/java/org/apache/hadoop/hive/ql/parse/HiveLexer.g f80642b
ql/src/java/org/apache/hadoop/hive/ql/parse/HiveParser.g eb81393
ql/src/java/org/apache/hadoop/hive/ql/parse/IdentifiersParser.g 81efadc
ql/src/java/org/apache/hadoop/hive/ql/parse/TypeCheckProcFactory.java f979c14
ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFLikeAll.java PRE-CREATION
ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFLikeAny.java PRE-CREATION
ql/src/test/org/apache/hadoop/hive/ql/udf/generic/TestGenericUDFLikeAll.java PRE-CREATION
ql/src/test/org/apache/hadoop/hive/ql/udf/generic/TestGenericUDFLikeAny.java PRE-CREATION
ql/src/test/queries/clientnegative/udf_likeall_wrong1.q PRE-CREATION
ql/src/test/queries/clientnegative/udf_likeany_wrong1.q PRE-CREATION
ql/src/test/queries/clientpositive/udf_likeall.q PRE-CREATION
ql/src/test/queries/clientpositive/udf_likeany.q PRE-CREATION
ql/src/test/results/clientnegative/udf_likeall_wrong1.q.out PRE-CREATION
ql/src/test/results/clientnegative/udf_likeany_wrong1.q.out PRE-CREATION
ql/src/test/results/clientpositive/show_functions.q.out 3c9bb4a
ql/src/test/results/clientpositive/udf_likeall.q.out PRE-CREATION
ql/src/test/results/clientpositive/udf_likeany.q.out PRE-CREATION
Diff: https://reviews.apache.org/r/53845/diff/
Testing
-------
Junit test cases and query.q files are attached
Thanks,
Simanchal Das
Re: Review Request 53845: 'like any' and 'like all' operators in hive
Posted by Simanchal Das <si...@outlook.com>.
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/53845/
-----------------------------------------------------------
(Updated Feb. 14, 2017, 12:39 p.m.)
Review request for hive, Carl Steinbach and Vineet Garg.
Repository: hive-git
Description
-------
https://issues.apache.org/jira/browse/HIVE-15229
In Teradata 'like any' and 'like all' operators are mostly used when we are matching a text field with numbers of patterns.
'like any' and 'like all' operator are equivalents of multiple like operator like example below.
--like any
select col1 from table1 where col2 like any ('%accountant%', '%accounting%', '%retail%', '%bank%', '%insurance%');
--Can be written using multiple like condition
select col1 from table1 where col2 like '%accountant%' or col2 like '%accounting%' or col2 like '%retail%' or col2 like '%bank%' or col2 like '%insurance%' ;
--like all
select col1 from table1 where col2 like all ('%accountant%', '%accounting%', '%retail%', '%bank%', '%insurance%');
--Can be written using multiple like operator
select col1 from table1 where col2 like '%accountant%' and col2 like '%accounting%' and col2 like '%retail%' and col2 like '%bank%' and col2 like '%insurance%' ;
Problem statement:
Now a days so many data warehouse projects are being migrated from Teradata to Hive.
Always Data engineer and Business analyst are searching for these two operator.
If we introduce these two operator in hive then so many scripts will be migrated smoothly instead of converting these operators to multiple like operators.
Diffs (updated)
-----
ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java 0f05160
ql/src/java/org/apache/hadoop/hive/ql/parse/HiveLexer.g f80642b
ql/src/java/org/apache/hadoop/hive/ql/parse/HiveParser.g eb81393
ql/src/java/org/apache/hadoop/hive/ql/parse/IdentifiersParser.g 81efadc
ql/src/java/org/apache/hadoop/hive/ql/parse/TypeCheckProcFactory.java f979c14
ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFLikeAll.java PRE-CREATION
ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFLikeAny.java PRE-CREATION
ql/src/test/org/apache/hadoop/hive/ql/udf/generic/TestGenericUDFLikeAll.java PRE-CREATION
ql/src/test/org/apache/hadoop/hive/ql/udf/generic/TestGenericUDFLikeAny.java PRE-CREATION
ql/src/test/queries/clientnegative/udf_likeall_wrong1.q PRE-CREATION
ql/src/test/queries/clientnegative/udf_likeany_wrong1.q PRE-CREATION
ql/src/test/queries/clientpositive/udf_likeall.q PRE-CREATION
ql/src/test/queries/clientpositive/udf_likeany.q PRE-CREATION
ql/src/test/results/clientnegative/udf_likeall_wrong1.q.out PRE-CREATION
ql/src/test/results/clientnegative/udf_likeany_wrong1.q.out PRE-CREATION
ql/src/test/results/clientpositive/show_functions.q.out 3c9bb4a
ql/src/test/results/clientpositive/udf_likeall.q.out PRE-CREATION
ql/src/test/results/clientpositive/udf_likeany.q.out PRE-CREATION
Diff: https://reviews.apache.org/r/53845/diff/
Testing
-------
Junit test cases and query.q files are attached
Thanks,
Simanchal Das
Re: Review Request 53845: 'like any' and 'like all' operators in hive
Posted by Simanchal Das <si...@outlook.com>.
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/53845/
-----------------------------------------------------------
(Updated Feb. 14, 2017, 7:28 a.m.)
Review request for hive, Carl Steinbach and Vineet Garg.
Repository: hive-git
Description
-------
https://issues.apache.org/jira/browse/HIVE-15229
In Teradata 'like any' and 'like all' operators are mostly used when we are matching a text field with numbers of patterns.
'like any' and 'like all' operator are equivalents of multiple like operator like example below.
--like any
select col1 from table1 where col2 like any ('%accountant%', '%accounting%', '%retail%', '%bank%', '%insurance%');
--Can be written using multiple like condition
select col1 from table1 where col2 like '%accountant%' or col2 like '%accounting%' or col2 like '%retail%' or col2 like '%bank%' or col2 like '%insurance%' ;
--like all
select col1 from table1 where col2 like all ('%accountant%', '%accounting%', '%retail%', '%bank%', '%insurance%');
--Can be written using multiple like operator
select col1 from table1 where col2 like '%accountant%' and col2 like '%accounting%' and col2 like '%retail%' and col2 like '%bank%' and col2 like '%insurance%' ;
Problem statement:
Now a days so many data warehouse projects are being migrated from Teradata to Hive.
Always Data engineer and Business analyst are searching for these two operator.
If we introduce these two operator in hive then so many scripts will be migrated smoothly instead of converting these operators to multiple like operators.
Diffs (updated)
-----
ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java 0f05160
ql/src/java/org/apache/hadoop/hive/ql/parse/HiveLexer.g f80642b
ql/src/java/org/apache/hadoop/hive/ql/parse/HiveParser.g eb81393
ql/src/java/org/apache/hadoop/hive/ql/parse/IdentifiersParser.g 81efadc
ql/src/java/org/apache/hadoop/hive/ql/parse/TypeCheckProcFactory.java f979c14
ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFLikeAll.java PRE-CREATION
ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFLikeAny.java PRE-CREATION
ql/src/test/org/apache/hadoop/hive/ql/udf/generic/TestGenericUDFLikeAll.java PRE-CREATION
ql/src/test/org/apache/hadoop/hive/ql/udf/generic/TestGenericUDFLikeAny.java PRE-CREATION
ql/src/test/queries/clientnegative/udf_likeall_wrong1.q PRE-CREATION
ql/src/test/queries/clientnegative/udf_likeany_wrong1.q PRE-CREATION
ql/src/test/queries/clientpositive/udf_likeall.q PRE-CREATION
ql/src/test/queries/clientpositive/udf_likeany.q PRE-CREATION
ql/src/test/results/clientnegative/udf_likeall_wrong1.q.out PRE-CREATION
ql/src/test/results/clientnegative/udf_likeany_wrong1.q.out PRE-CREATION
ql/src/test/results/clientpositive/udf_likeall.q.out PRE-CREATION
ql/src/test/results/clientpositive/udf_likeany.q.out PRE-CREATION
Diff: https://reviews.apache.org/r/53845/diff/
Testing
-------
Junit test cases and query.q files are attached
Thanks,
Simanchal Das
Re: Review Request 53845: 'like any' and 'like all' operators in hive
Posted by Simanchal Das <si...@outlook.com>.
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/53845/
-----------------------------------------------------------
(Updated Feb. 9, 2017, 9:58 a.m.)
Review request for hive, Carl Steinbach and Vineet Garg.
Repository: hive-git
Description
-------
https://issues.apache.org/jira/browse/HIVE-15229
In Teradata 'like any' and 'like all' operators are mostly used when we are matching a text field with numbers of patterns.
'like any' and 'like all' operator are equivalents of multiple like operator like example below.
--like any
select col1 from table1 where col2 like any ('%accountant%', '%accounting%', '%retail%', '%bank%', '%insurance%');
--Can be written using multiple like condition
select col1 from table1 where col2 like '%accountant%' or col2 like '%accounting%' or col2 like '%retail%' or col2 like '%bank%' or col2 like '%insurance%' ;
--like all
select col1 from table1 where col2 like all ('%accountant%', '%accounting%', '%retail%', '%bank%', '%insurance%');
--Can be written using multiple like operator
select col1 from table1 where col2 like '%accountant%' and col2 like '%accounting%' and col2 like '%retail%' and col2 like '%bank%' and col2 like '%insurance%' ;
Problem statement:
Now a days so many data warehouse projects are being migrated from Teradata to Hive.
Always Data engineer and Business analyst are searching for these two operator.
If we introduce these two operator in hive then so many scripts will be migrated smoothly instead of converting these operators to multiple like operators.
Diffs (updated)
-----
ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java 0dbbc1d
ql/src/java/org/apache/hadoop/hive/ql/parse/HiveLexer.g 4357328
ql/src/java/org/apache/hadoop/hive/ql/parse/HiveParser.g 55915a6
ql/src/java/org/apache/hadoop/hive/ql/parse/IdentifiersParser.g a82083b
ql/src/java/org/apache/hadoop/hive/ql/parse/TypeCheckProcFactory.java 5e708d3
ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFLikeAll.java PRE-CREATION
ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFLikeAny.java PRE-CREATION
ql/src/test/org/apache/hadoop/hive/ql/udf/generic/TestGenericUDFLikeAll.java PRE-CREATION
ql/src/test/org/apache/hadoop/hive/ql/udf/generic/TestGenericUDFLikeAny.java PRE-CREATION
ql/src/test/queries/clientnegative/udf_likeall_wrong1.q PRE-CREATION
ql/src/test/queries/clientnegative/udf_likeany_wrong1.q PRE-CREATION
ql/src/test/queries/clientpositive/udf_likeall.q PRE-CREATION
ql/src/test/queries/clientpositive/udf_likeany.q PRE-CREATION
ql/src/test/results/clientnegative/udf_likeall_wrong1.q.out PRE-CREATION
ql/src/test/results/clientnegative/udf_likeany_wrong1.q.out PRE-CREATION
ql/src/test/results/clientpositive/udf_likeall.q.out PRE-CREATION
ql/src/test/results/clientpositive/udf_likeany.q.out PRE-CREATION
Diff: https://reviews.apache.org/r/53845/diff/
Testing
-------
Junit test cases and query.q files are attached
Thanks,
Simanchal Das