Return-Path: 
 <user-return-15615-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 81D3910BD4
	for <apmail-hive-user-archive@www.apache.org>;
 Thu, 20 Feb 2014 09:48:00 +0000 (UTC)
Received: (qmail 28603 invoked by uid 500); 20 Feb 2014 09:47:57 -0000
Delivered-To: apmail-hive-user-archive@hive.apache.org
Received: (qmail 28510 invoked by uid 500); 20 Feb 2014 09:47:56 -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 28500 invoked by uid 99); 20 Feb 2014 09:47:55 -0000
Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136)
    by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 20 Feb 2014 09:47:55 +0000
X-ASF-Spam-Status: No, hits=1.7 required=5.0
	tests=FREEMAIL_ENVFROM_END_DIGIT,HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS
X-Spam-Check-By: apache.org
Received-SPF: pass (athena.apache.org: domain of manjumohapatra1945@gmail.com
 designates 209.85.212.195 as permitted sender)
Received: from [209.85.212.195] (HELO mail-wi0-f195.google.com)
 (209.85.212.195)
    by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 20 Feb 2014 09:47:49 +0000
Received: by mail-wi0-f195.google.com with SMTP id f8so515390wiw.2
        for <user@hive.apache.org>; Thu, 20 Feb 2014 01:47:28 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
        d=gmail.com; s=20120113;
        h=mime-version:date:message-id:subject:from:to:content-type;
        bh=mKINr4d+E3f7g2N9NsBbj4024zbg6gQytNqlOi8MS5w=;
        b=vL/zkr6IZU21XvLX4dJs6+OJz4wnKOmxlhTAUCtnb/TB+Z4YPJ956P0WFo1X5orup/
         mSH/yJoB5r0j3Zzidaz5UVb8kq5tlAA2GpxrZXbli+SVLilekhwV280wsKDhuB409DPi
         cDi8d5p6Zntko6yVuk/DzbXDSCw38hKwGnAsUVA561hDkN3/thOTcO9TSTUQ18dkLOd2
         UoYDSWqL+UswFZwJD1WRIOb1miNWm6mRdfdO28MZ+3Mu5AaJDS76qC6UbO+ALnojNJpJ
         bfoiBBfdO0LW7Hy19TxOPhhlVVugK1kUsdygZ/rVc9c8HUMQKucAlksDOKmYGBbrOIbk
         qyEw==
MIME-Version: 1.0
X-Received: by 10.180.98.71 with SMTP id eg7mr1457753wib.31.1392889648514;
 Thu, 20 Feb 2014 01:47:28 -0800 (PST)
Received: by 10.194.163.37 with HTTP; Thu, 20 Feb 2014 01:47:28 -0800 (PST)
Date: Thu, 20 Feb 2014 01:47:28 -0800
Message-ID: 
 <CAMZ5_b-0MUYqv_vz4MU1u=ytKwQEThRkF5BLx41RHvQ62=W+wA@mail.gmail.com>
Subject: Tuning Hive queries that uses underlying HBase Table
From: Manjula mohapatra <manjumohapatra1945@gmail.com>
To: user@hive.apache.org
Content-Type: multipart/alternative; boundary=f46d04428070b609c504f2d3648f
X-Virus-Checked: Checked by ClamAV on apache.org

--f46d04428070b609c504f2d3648f
Content-Type: text/plain; charset=ISO-8859-1

I am querying Hive table ( mapped to HBase Table ) .

What are the techniques to tune the Hive query and to avoid HBase scans.

Query uses multiple SPLIT and SUBSTR functions and WHERE  condition
something like

select  col1, col2, ...,count(*)
from hiveTable

where split( col1)[0] > timestamp1  and split( col1)[0]<timestamp2
group by ....

--f46d04428070b609c504f2d3648f
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><span style=3D"font-family:arial,sans-serif;font-size:13px=
">I am querying Hive table ( mapped to HBase Table ) .</span><div style=3D"=
font-family:arial,sans-serif;font-size:13px"><br></div><div style=3D"font-f=
amily:arial,sans-serif;font-size:13px">
What are the techniques to tune the Hive query and to avoid HBase scans.</d=
iv><div style=3D"font-family:arial,sans-serif;font-size:13px"><br></div><di=
v style=3D"font-family:arial,sans-serif;font-size:13px">Query uses multiple=
 SPLIT and SUBSTR functions and WHERE =A0condition something like</div>
<div style=3D"font-family:arial,sans-serif;font-size:13px"><br></div><div s=
tyle=3D"font-family:arial,sans-serif;font-size:13px">select =A0col1, col2, =
...,count(*)=A0</div><div style=3D"font-family:arial,sans-serif;font-size:1=
3px">from hiveTable</div>
<div style=3D"font-family:arial,sans-serif;font-size:13px"><br></div><div s=
tyle=3D"font-family:arial,sans-serif;font-size:13px">where split( col1)[0] =
&gt; timestamp1 =A0and split( col1)[0]&lt;timestamp2</div><div style=3D"fon=
t-family:arial,sans-serif;font-size:13px">
group by ....</div></div>

--f46d04428070b609c504f2d3648f--