Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-15229

'like any' and 'like all' operators in hive

    XMLWordPrintableJSON

    Details

    • Type: New Feature
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 3.0.0
    • Component/s: Operators
    • Labels:
    • Hadoop Flags:
      Reviewed

      Description

      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.

      Result:

      1. 'LIKE ANY' operator return true if a text(column value) matches to any pattern.
      2. 'LIKE ALL' operator return true if a text(column value) matches to all patterns.
      3. 'LIKE ANY' and 'LIKE ALL' returns NULL not only if the expression on the left hand side is NULL, but also if one of the pattern in the list is NULL.

        Attachments

        1. HIVE-15229.6.patch
          45 kB
          Simanchal Das
        2. HIVE-15229.5.patch
          45 kB
          Simanchal Das
        3. HIVE-15229.4.patch
          57 kB
          Simanchal Das
        4. HIVE-15229.3.patch
          57 kB
          Simanchal Das
        5. HIVE-15229.2.patch
          57 kB
          Simanchal Das
        6. HIVE-15229.1.patch
          43 kB
          Simanchal Das

          Activity

            People

            • Assignee:
              simanchal Simanchal Das
              Reporter:
              simanchal Simanchal Das
            • Votes:
              0 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: