Return-Path: 
 <user-return-17999-apmail-hive-user-archive=hive.apache.org@hive.apache.org>
X-Original-To: apmail-hive-user-archive@www.apache.org
Delivered-To: apmail-hive-user-archive@www.apache.org
Received: from mail.apache.org (hermes.apache.org [140.211.11.3])
	by minotaur.apache.org (Postfix) with SMTP id 66E5817968
	for <apmail-hive-user-archive@www.apache.org>;
 Tue,  4 Nov 2014 07:21:11 +0000 (UTC)
Received: (qmail 95764 invoked by uid 500); 4 Nov 2014 07:21:09 -0000
Delivered-To: apmail-hive-user-archive@hive.apache.org
Received: (qmail 95692 invoked by uid 500); 4 Nov 2014 07:21:09 -0000
Mailing-List: contact user-help@hive.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:user-help@hive.apache.org>
List-Unsubscribe: <mailto:user-unsubscribe@hive.apache.org>
List-Post: <mailto:user@hive.apache.org>
List-Id: <user.hive.apache.org>
Reply-To: user@hive.apache.org
Delivered-To: mailing list user@hive.apache.org
Received: (qmail 95677 invoked by uid 99); 4 Nov 2014 07:21:09 -0000
Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136)
    by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 04 Nov 2014 07:21:09 +0000
X-ASF-Spam-Status: No, hits=1.5 required=5.0
	tests=HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS
X-Spam-Check-By: apache.org
Received-SPF: pass (athena.apache.org: domain of sanjiv.is.on@gmail.com
 designates 209.85.212.172 as permitted sender)
Received: from [209.85.212.172] (HELO mail-wi0-f172.google.com)
 (209.85.212.172)
    by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 04 Nov 2014 07:21:04 +0000
Received: by mail-wi0-f172.google.com with SMTP id bs8so8510760wib.11
        for <user@hive.apache.org>; Mon, 03 Nov 2014 23:19:58 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
        d=gmail.com; s=20120113;
        h=mime-version:reply-to:from:date:message-id:subject:to:content-type;
        bh=rR+PYwEvMLmEzy9BtM9bdXHMhldDJMywJOOaMHuoFXI=;
        b=u18V3dz3x6FsPyFKX0WG4DRjpohf7pG0CZdk5JBgzsx6a+jLIUTN+lQYAewb4AvGDu
         kMc9Vcmu6Mc8YFafNM1VaVvIOXzb1Mo0D09UcMhfGIOWpyE7/EDLMGewNdCMRXR+zUjd
         fCHsZmqYxsSkmyYnUFVnKDeoeE0Ram72imuXnMNVexx/clG+cfWtAlZUlQ6lTTnKxQFB
         D4HmNb/wydmsYjXLTJ3YNtTozgQvDcxV7uTFHyTbKGDwOXFkJSR9vLUCVUp7qHExBszc
         h1mXmcCx/CuwXToIfl7pAXPZB5UfMOmSljQd/YlyM0QAlrbkuPYGNkBdL8SFNGj6kUW8
         WigA==
X-Received: by 10.180.11.227 with SMTP id t3mr21923099wib.45.1415085597977;
 Mon, 03 Nov 2014 23:19:57 -0800 (PST)
MIME-Version: 1.0
Received: by 10.194.238.130 with HTTP; Mon, 3 Nov 2014 23:19:37 -0800 (PST)
Reply-To: sanjiv.is.on@gmail.com
From: "@Sanjiv Singh" <sanjiv.is.on@gmail.com>
Date: Tue, 4 Nov 2014 12:49:37 +0530
Message-ID: 
 <CAAef+M7vrMFSV=BMutZ1WJFGqKHSGa=EO2ujGzmk8wcjqkjGZA@mail.gmail.com>
Subject: 
 =?UTF-8?Q?Is_there_a_Hive_equivalent_of_SQL_=E2=80=9CLIKE_ANY_=28_SUBQ?=
	=?UTF-8?Q?UERY_=29=E2=80=9D?=
To: user@hive.apache.org
Content-Type: multipart/alternative; boundary=001a11c2400c656fce0507034a67
X-Virus-Checked: Checked by ClamAV on apache.org

--001a11c2400c656fce0507034a67
Content-Type: text/plain; charset=UTF-8

Hi ,

While Hive doesn't supports multi-value LIKE queries which are supported in
SQL : ex.

SELECT * FROM user_table WHERE first_name LIKE ANY ( 'root~%' , 'user~%' );

We can convert it into equivalent HIVE queries as :

SELECT * FROM user_table WHERE first_name LIKE 'root~%' OR first_name
LIKE 'user~%'

Does anyone know an equivalent solution that Hive does support in case
sub-query is used with LIKE ? Have a look at below example :

SELECT * FROM user_table WHERE first_name LIKE ANY ( SELECT expr FROM
exprTable);

As It doesn't have values in expression, I can't use same approach for
generating multiple LIKE expression separated with OR / AND operator.
Initially I thought to write HIVE UDF for it ? Can you please help me
supporting such expression and finding HIVE equivalent ?


Regards
Sanjiv Singh
Impetus Infotech
Mob :  +091 9990-447-339

--001a11c2400c656fce0507034a67
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div class=3D"" itemprop=3D"text">

        <p>Hi , <br></p><p>While Hive doesn&#39;t supports multi-value LIKE=
 queries which are supported in SQL : ex.</p>

<pre><code>SELECT * FROM user_table WHERE first_name LIKE ANY ( &#39;root~%=
&#39; , &#39;user~%&#39; );
</code></pre>

<p>We can convert it into equivalent HIVE queries as : </p>

<pre><code>SELECT * FROM user_table WHERE first_name LIKE &#39;root~%&#39; =
OR first_name LIKE &#39;user~%&#39;=20
</code></pre>

<p>Does anyone know an equivalent solution that Hive does support in=20
case sub-query is used with LIKE ? Have a look at below example : </p>

<pre><code>SELECT * FROM user_table WHERE first_name LIKE ANY ( SELECT expr=
 FROM exprTable);
</code></pre>

<p>As It doesn&#39;t have values in expression, I can&#39;t use same approa=
ch=20
for generating multiple LIKE expression separated with OR / AND=20
operator. Initially I thought to write HIVE UDF for it ?  Can you please
 help me supporting such expression and finding HIVE equivalent ? <br></p><=
p><br></p>

    </div><div><div class=3D"gmail_signature"><div dir=3D"ltr">Regards<br>S=
anjiv Singh<br>Impetus Infotech<br>Mob :=C2=A0 +091 9990-447-339<br></div><=
/div></div>
</div>

--001a11c2400c656fce0507034a67--