hive-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Simanchal Das <simanchal....@outlook.com>
Subject Re: Review Request 53845: 'like any' and 'like all' operators in hive
Date Thu, 17 Nov 2016 12:49:18 GMT

-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/53845/
-----------------------------------------------------------

(Updated Nov. 17, 2016, 12:49 p.m.)


Review request for hive and Carl Steinbach.


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 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


Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message