Mailing-List: contact dev-help@hbase.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@hbase.apache.org
Received-SPF: neutral (nike.apache.org: local policy)
References: 
 <CADSCZ4TK8PkM0jxMZk9E+DBqfo8Zgk8NHa1Gp86bGYi4fYwe=w@mail.gmail.com>
 <CAGpTDNeLTGUJ1PkJ=OV7bAeS+6HYr3+YPwt+i2ur_J6oVUnHww@mail.gmail.com>
 <A0B699C30D50C84883E79752AEA74B88302F2FB93A@NAMBX01.corp.adobe.com>
 <CADSCZ4TOKpWsupikDxae-5gSBvj2fWxoEpQ6doXoWW_=VYCuTg@mail.gmail.com>
 <CADSCZ4Rg72vd6Y4Q3-s3tVK-h-qbW8fgdt+bjFYQkPaPS5DJSA@mail.gmail.com>
Message-ID: <1317401374.83957.YahooMailNeo@web65510.mail.ac4.yahoo.com>
Date: Fri, 30 Sep 2011 09:49:34 -0700 (PDT)
From: Andrew Purtell <apurtell@apache.org>
Reply-To: Andrew Purtell <apurtell@apache.org>
Subject: Re: Hbase-Hive integration performance issues
To: "dev@hbase.apache.org" <dev@hbase.apache.org>,
  HBase User <user@hbase.apache.org>
In-Reply-To: 
 <CADSCZ4Rg72vd6Y4Q3-s3tVK-h-qbW8fgdt+bjFYQkPaPS5DJSA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="-146594385-1113819418-1317401374=:83957"

---146594385-1113819418-1317401374=:83957
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

I believe this is the latest status:=0A=0A=A0 =A0 https://issues.apache.org=
/jira/browse/HIVE-1643?page=3Dcom.atlassian.jira.plugin.system.issuetabpane=
ls:all-tabpanel=0A=0ASuggest following up to dev@hive.apache.org and/or use=
r@hive.apache.org.=0A=0ABest regards,=0A=0A=0A=A0 =A0- Andy=0A=0AProblems w=
orthy of attack prove their worth by hitting back. - Piet Hein (via Tom Whi=
te)=0A=0A=0A>________________________________=0A>From: Matthew Tovbin <matt=
hew@tovbin.com>=0A>To: HBase User <user@hbase.apache.org>=0A>Cc: Hbase Dev =
<dev@hbase.apache.org>=0A>Sent: Friday, September 30, 2011 5:49 AM=0A>Subje=
ct: Re: Hbase-Hive integration performance issues=0A>=0A>Hello guys,=0A>=0A=
>Any updates on the issue? Anyone?! ;))=0A>=0A>Best regards,=0A>=A0  Matthe=
w Tovbin =3D)=0A>=0A>=0A>=0A>On Tue, Sep 20, 2011 at 09:41, Matthew Tovbin =
<matthew@tovbin.com> wrote:=0A>=0A>>=A0 Thanks Jean and Sandy.=0A>>=0A>>=A0=
 =A0 I have hive 0.7.1, and according to this patch=0A>> https://issues.apa=
che.org/jira/browse/HIVE-1226 at least exact match=0A>> queries like=A0 "..=
.where id =3D '12345'-123' " or partial pushdown "...where id=0A>> like "12=
345%" should work, but I didn't notice it.=0A>>=0A>> Matthew.=0A>>=0A>>=0A>=
>=0A>> On Mon, Sep 19, 2011 at 20:37, Sandy Pratt <prattrs@adobe.com> wrote=
:=0A>>=0A>>> I suffered the same let down a little while ago.=A0 I believe =
this is the=0A>>> relevant JIRA:=0A>>>=0A>>> https://issues.apache.org/jira=
/browse/HIVE-1643=0A>>>=0A>>> I'd also like to see Hive be able to limit sc=
ans to particular HBase=0A>>> version ranges, but I don't know if that's ev=
en planned.=0A>>>=0A>>> Sandy=0A>>>=0A>>> > -----Original Message-----=0A>>=
> > From: jdcryans@gmail.com [mailto:jdcryans@gmail.com] On Behalf Of Jean-=
=0A>>> > Daniel Cryans=0A>>> > Sent: Monday, September 19, 2011 09:58=0A>>>=
 > To: user@hbase.apache.org=0A>>> > Subject: Re: Hbase-Hive integration pe=
rformance issues=0A>>> >=0A>>> > (replying to user@, dev@ in BCC)=0A>>> >=
=0A>>> > AFAIK the HBase handler doesn't have the wits to understand that y=
ou are=0A>>> > doing a prefix scan and thus limit the scan to only the requ=
ired rows.=0A>>> There's=0A>>> > a bunch of optimizations like that that ne=
ed to be done.=0A>>> >=0A>>> > I'm pretty sure Pig does the same thing, but=
 don't take my word on it.=0A>>> >=0A>>> > J-D=0A>>> >=0A>>> > On Sun, Sep =
18, 2011 at 4:12 AM, Matthew Tovbin <matthew@tovbin.com>=0A>>> > wrote:=0A>=
>> > > Hi guys,=0A>>> > >=0A>>> > > I've got a table in Hbase let's say "tb=
l" and I would like to query it=0A>>> > > using Hive. Therefore I mapped a =
table to hive as follows:=0A>>> > >=0A>>> > > CREATE EXTERNAL TABLE tbl(id =
string, data map<string,string>) STORED=0A>>> > > BY 'org.apache.hadoop.hiv=
e.hbase.HBaseStorageHandler'=0A>>> > > WITH SERDEPROPERTIES ("hbase.columns=
.mapping" =3D ":key,data:")=0A>>> > > TBLPROPERTIES("hbase.table.name" =3D =
"tbl");=0A>>> > >=0A>>> > > Queries like: "select * from tbl", "select id f=
rom tbl", "select id,=0A>>> > > data from tbl" are really fast.=0A>>> > > B=
ut queries like "select id from tbl where substr(id, 0, 5) =3D "12345""=0A>=
>> > > or "select id from tbl where data["777"] IS NOT NULL" are incredibly=
=0A>>> slow.=0A>>> > >=0A>>> > > In the contrary when running from Hbase sh=
ell: "scan 'tbl', {=0A>>> > > COLUMNS=3D>'data', STARTROW=3D'12345', ENDROW=
=3D'12346'}" or "scan 'tbl', {=0A>>> > > COLUMNS=3D>'data', "FILTER" =3D>=
=0A>>> > > FilterList.new([qualifierFilter('777')])}"=0A>>> > > it is light=
ning fast!=0A>>> > >=0A>>> > > When I looked into the mapred job generated =
by hive on jobtracker I=0A>>> > > discovered that "map.input.records" count=
s ALL the items in Hbase=0A>>> > > table, meaning the job makes a full tabl=
e scan before it even starts=0A>>> any=0A>>> > mappers!!=0A>>> > > Moreover=
, I suspect it copies all the data from Hbase table to hdfs to=0A>>> > > ma=
pper tmp input folder before executuion.=0A>>> > >=0A>>> > > So, my questio=
ns are - Why hbase storage handler for hive does not=0A>>> > > translate hi=
ve queries into appropriate hbase functions? Why it scans=0A>>> > > all the=
 records and then slices them using "where" clause? How can it=0A>>> > > be=
 improved? Is Pig's integration better in this case?=0A>>> > >=0A>>> > >=0A=
>>> > > Some additional information about the tables:=0A>>> > > Table descr=
iption in Hbase:=0A>>> > > jruby-1.6.2 :011 >=A0  describe 'tbl'=0A>>> > > =
DESCRIPTION=0A>>> > >=A0 =A0 =A0 =A0 =A0 =A0 =A0 ENABLED=0A>>> > >=A0 {NAME=
 =3D> 'users', FAMILIES =3D> [{NAME =3D> 'data', BLOOMFILTER =3D>=0A>>> > >=
 'ROWCOL', REPLICATIO true=0A>>> > >=A0 N_SCOPE =3D> '0', COMPRESSION =3D> =
'LZO', VERSIONS =3D> '3', TTL =3D>=0A>>> > > '2147483647', BLOCKSIZE =3D>=
=0A>>> > >=A0 '65536', IN_MEMORY =3D> 'false', BLOCKCACHE =3D> 'true'}]}=0A=
>>> > >=0A>>> > > Table desciption in Hive:=0A>>> > > hive> describe tbl;=
=0A>>> > > OK=0A>>> > > id string from deserializer=0A>>> > > data map<stri=
ng,string> from deserializer Time taken: 0.08 seconds=0A>>> > >=0A>>> > > B=
est regards,=0A>>> > >=A0  Matthew Tovbin =3D)=0A>>> > >=0A>>>=0A>>=0A>>=0A=
>=0A>=0A>
---146594385-1113819418-1317401374=:83957--