Mailing-List: contact user-help@hive.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@hive.apache.org
MIME-Version: 1.0
In-Reply-To: 
 <CAOnya2U3EEBdn=fxV6tKTqwaWmx1-jXhzWzwFPm6DC8CKVmffA@mail.gmail.com>
References: 
 <CAOnya2U3EEBdn=fxV6tKTqwaWmx1-jXhzWzwFPm6DC8CKVmffA@mail.gmail.com>
From: Noam Hasson <noam.hasson@kenshoo.com>
Date: Thu, 20 Aug 2015 11:49:34 +0300
Message-ID: 
 <CAG97e2H1PC_Dt411bfDZmskkVF4fNyfxdyLA3erGx=EqFW8FcA@mail.gmail.com>
Subject: Re: HIVE:1.2, Query taking huge time
To: user@hive.apache.org
Cc: dev@hive.apache.org
Content-Type: multipart/alternative; boundary=089e0111d6b028c27e051dba3cd1

--089e0111d6b028c27e051dba3cd1
Content-Type: text/plain; charset=UTF-8

Hi,

Have you look at counters in Hadoop side? It's possible you are dealing
with a bad join which causes multiplication of items, if you see huge
number of record input/output in map/reduce phase and keeps increasing
that's probably the case.

Another thing I would try is to divide the job into several different
smaller queries, for example start with filter only, after than join and so
on.

Noam.

On Thu, Aug 20, 2015 at 10:55 AM, Nishant Aggarwal <nishant.agg@gmail.com>
wrote:

> Dear Hive Users,
>
> I am in process of running over a poc to one of my customer demonstrating
> the huge performance benefits of Hadoop BigData using Hive.
>
> Following is the problem statement i am stuck with.
>
> I have generate a large table with 28 columns( all are double). Table size
> on disk is 70GB (i ultimately created compressed table using ORC format to
> save disk space bringing down the table size to < 1GB) with more than
> 450Million records.
>
> In order to demonstrate a complex use case i joined this table with
> itself. Following are the queries i have used to create table and  join
> query i am using.
>
> *Create Table and Loading Data, Hive parameters settigs:*
> set hive.vectorized.execution.enabled = true;
> set hive.vectorized.execution.reduce.enabled = true;
> set mapred.max.split.size=100000000;
> set mapred.min.split.size=1000000;
> set hive.auto.convert.join=false;
> set hive.enforce.sorting=true;
> set hive.enforce.bucketing=true;
> set hive.exec.dynamic.partition=true;
> set hive.exec.dynamic.partition.mode=nonstrict;
> set mapreduce.reduce.input.limit=-1;
> set hive.exec.parallel = true;
>
> CREATE TABLE huge_numeric_table_orc2(col1 double,col2 double,col3
> double,col4 double,col5 double,col6 double,col7 double,col8 double,col9
> double,col10 double,col11 double,col12 double,col13 double,col14
> double,col15 double,col16 double,col17 double,col18 double,col19
> double,col20 double,col21 double,col22 double,col23 double,col24
> double,col25 double,col26 double,col27 double,col28 double)
> clustered by (col1) sorted by (col1) into 240 buckets
> STORED AS ORC tblproperties ("orc.compress"="SNAPPY");
>
> from huge_numeric_table insert overwrite table huge_numeric_table_orc2
> select * sort by col1;
>
>
> *JOIN QUERY:*
>
> select (avg(t1.col1)*avg(t1.col6))/(avg(t1.col11)*avg(t1.col16)) as AVG5
> from huge_numeric_table_orc2 t1 left outer join huge_numeric_table_orc2 t2
> on t1.col1=t2.col1 where (t1.col1) > 34.11 and (t2.col1) >10.12
>
>
> *The problem is that this query gets stuck at reducers :80-85%. and goes
> in a loop and never finishes. *
>
> Version of Hive is 1.2.
>
> Please help.
>
>
> Thanks and Regards
> Nishant Aggarwal, PMP
> Cell No:- +91 99588 94305
>
>

-- 
This e-mail, as well as any attached document, may contain material which 
is confidential and privileged and may include trademark, copyright and 
other intellectual property rights that are proprietary to Kenshoo Ltd, 
 its subsidiaries or affiliates ("Kenshoo"). This e-mail and its 
attachments may be read, copied and used only by the addressee for the 
purpose(s) for which it was disclosed herein. If you have received it in 
error, please destroy the message and any attachment, and contact us 
immediately. If you are not the intended recipient, be aware that any 
review, reliance, disclosure, copying, distribution or use of the contents 
of this message without Kenshoo's express permission is strictly prohibited.

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

<div dir=3D"ltr">Hi,<div><br></div><div>Have you look at counters in Hadoop=
 side? It&#39;s possible you are dealing with a bad join which causes multi=
plication of items, if you see huge number of record input/output in map/re=
duce phase and keeps increasing that&#39;s probably the case.</div><div><br=
></div><div>Another thing I would try is to divide the job into several dif=
ferent smaller queries, for example start with filter only, after than join=
 and so on.</div><div><br></div><div>Noam.</div></div><div class=3D"gmail_e=
xtra"><br><div class=3D"gmail_quote">On Thu, Aug 20, 2015 at 10:55 AM, Nish=
ant Aggarwal <span dir=3D"ltr">&lt;<a href=3D"mailto:nishant.agg@gmail.com"=
 target=3D"_blank">nishant.agg@gmail.com</a>&gt;</span> wrote:<br><blockquo=
te class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc so=
lid;padding-left:1ex"><div dir=3D"ltr"><div>Dear Hive Users,<br><br></div>I=
 am in process of running over a poc to one of my customer demonstrating th=
e huge performance benefits of Hadoop BigData using Hive. <br><br>Following=
 is the problem statement i am stuck with.<br><div><div><br></div><div>I ha=
ve generate a large table with 28 columns( all are double). Table size on d=
isk is 70GB (i ultimately created compressed table using ORC format to save=
 disk space bringing down the table size to &lt; 1GB) with more than 450Mil=
lion records.<br><br></div><div>In order to demonstrate a complex use case =
i joined this table with itself. Following are the queries i have used to c=
reate table and=C2=A0 join query i am using.<br><br></div><div><b><font siz=
e=3D"4">Create Table and Loading Data, Hive parameters settigs:</font></b><=
br>set hive.vectorized.execution.enabled =3D true;<br>set hive.vectorized.e=
xecution.reduce.enabled =3D true;<br>set mapred.max.split.size=3D100000000;=
<br>set mapred.min.split.size=3D1000000;<br>set hive.auto.convert.join=3Dfa=
lse;<br>set hive.enforce.sorting=3Dtrue;<br>set hive.enforce.bucketing=3Dtr=
ue;<br>set hive.exec.dynamic.partition=3Dtrue;<br>set hive.exec.dynamic.par=
tition.mode=3Dnonstrict;<br>set mapreduce.reduce.input.limit=3D-1;<br>set h=
ive.exec.parallel =3D true;<br><br>CREATE TABLE huge_numeric_table_orc2(col=
1 double,col2 double,col3 double,col4 double,col5 double,col6 double,col7 d=
ouble,col8 double,col9 double,col10 double,col11 double,col12 double,col13 =
double,col14 double,col15 double,col16 double,col17 double,col18 double,col=
19 double,col20 double,col21 double,col22 double,col23 double,col24 double,=
col25 double,col26 double,col27 double,col28 double)=C2=A0 <br>clustered by=
 (col1) sorted by (col1) into 240 buckets <br>STORED AS ORC tblproperties (=
&quot;orc.compress&quot;=3D&quot;SNAPPY&quot;);<br><br>from huge_numeric_ta=
ble insert overwrite table huge_numeric_table_orc2 select * sort by col1;<b=
r><br><br></div><div><font size=3D"4"><b>JOIN QUERY:</b></font><br><br>sele=
ct (avg(t1.col1)*avg(t1.col6))/(avg(t1.col11)*avg(t1.col16)) as AVG5 from h=
uge_numeric_table_orc2 t1 left outer join huge_numeric_table_orc2 t2 on t1.=
col1=3Dt2.col1 where (t1.col1) &gt; 34.11 and (t2.col1) &gt;10.12<br></div>=
<div><br><br></div><div><b>The problem is that this query gets stuck at red=
ucers :80-85%. and goes in a loop and never finishes. </b><br></div><div><b=
r></div><div>Version of Hive is 1.2.<br><br></div><div>Please help.<br></di=
v><div><br></div><div><br clear=3D"all"><div><div><div dir=3D"ltr">Thanks a=
nd Regards<br>Nishant Aggarwal, PMP<br>Cell No:- <a href=3D"tel:%2B91%20995=
88%2094305" value=3D"+919958894305" target=3D"_blank">+91 99588 94305</a><b=
r><br></div></div></div>
</div></div></div>
</blockquote></div><br></div>

<br>
This e-mail, as well as any attached document, may contain material which i=
s confidential and privileged and may include trademark, copyright and othe=
r intellectual property rights that are proprietary to Kenshoo Ltd, =C2=A0i=
ts subsidiaries or affiliates (&quot;Kenshoo&quot;). This e-mail and its at=
tachments may be read, copied and used only by the addressee for the purpos=
e(s) for which it was disclosed herein. If you have received it in error, p=
lease destroy the message and any attachment, and contact us immediately. I=
f you are not the intended recipient, be aware that any review, reliance, d=
isclosure, copying, distribution or use of the contents of this message wit=
hout Kenshoo&#39;s express permission is strictly prohibited.
--089e0111d6b028c27e051dba3cd1--