Mailing-List: contact user-help@hbase.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@hbase.apache.org
Received-SPF: pass (nike.apache.org: domain of lars.george@gmail.com
 designates 209.85.214.41 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=mime-version:in-reply-to:references:date:message-id:subject:from:to
         :content-type:content-transfer-encoding;
        b=NFNG2wt7J7m/9YG6Wx0DWsAGbczUufQucWPBnbRPgV2DYes5p83aQ5BRQVBGU4Gxeb
         nIZtuL1a9hZMWFIRpG4ApY5QCBpRcWnVyOtMhxAMEL/urV9Cm1g1Mner1zTXhbb/X475
         oJydFPED6fs4RfOgH5zT7e06aQ61qhYC6u7Bs=
MIME-Version: 1.0
In-Reply-To: <BANLkTinYe5q32EWRrDM_9oEYXEVnaT_EmA@mail.gmail.com>
References: <BANLkTim+SrXvh5yxpQjrTRb=AnAgwC8B9Q@mail.gmail.com>
	<67680900F79B1D4F99C844EE386FC5952823B100A5@EX2K7VS03.4emm.local>
	<COL117-W1EC409006D071FFE089208F7A0@phx.gbl>
	<67680900F79B1D4F99C844EE386FC5952823B100C4@EX2K7VS03.4emm.local>
	<COL117-W1864CC97AE32D442A9CC048F7A0@phx.gbl>
	<BANLkTin9k7ivNfX4k1-B4cSHi2Y2h+nJGQ@mail.gmail.com>
	<BANLkTinY2F6+6h+myPV5_YdUqMWJQ6GgCQ@mail.gmail.com>
	<BANLkTikOU8_8A1uPB0M2QfLQtwfYSzenxw@mail.gmail.com>
	<BANLkTi=RuJXw0mpcmBQrSxy+1u6zz-j9sg@mail.gmail.com>
	<BANLkTinYe5q32EWRrDM_9oEYXEVnaT_EmA@mail.gmail.com>
Date: Wed, 1 Jun 2011 15:54:11 +0200
Message-ID: <BANLkTi=2ARujKe4HXs_XmzWUF2cZg-P4_g@mail.gmail.com>
Subject: Re: How to efficiently join HBase tables?
From: Lars George <lars.george@gmail.com>
To: user@hbase.apache.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi Jason,

This was discussed in the past, using the HFileInputFormat. The issue
is that you somehow need to flush all in-memory data *and* perform a
major compaction - or else you would need all the logic of the
ColumnTracker in the HFIF. Since that needs to scan all storage files
in parallel to achieve its job, the MR task would not really be able
to use the same approach.

Running a major compaction creates a lot of churn, so it is
questionable what the outcome is. The files do have a flag if they
were made by a major compaction, so you scan only those and ignore the
newer ones - but then you are trailing, and you still do not handle
delete markers/updates in newer files. No easy feat.

Lars

On Wed, Jun 1, 2011 at 2:41 AM, Jason Rutherglen
<jason.rutherglen@gmail.com> wrote:
>> I'd imagine that join operations do not require realtime-ness, and so
>> faster batch jobs using Hive -> frozen HBase files in HDFS could be
>> the optimal way to go?
>
> In addition to lessening the load on the perhaps live RegionServer.
> There's no Jira for this, I'm tempted to open one.
>
> On Tue, May 31, 2011 at 5:18 PM, Jason Rutherglen
> <jason.rutherglen@gmail.com> wrote:
>>> The Hive-HBase integration allows you to create Hive tables that are ba=
cked
>>> by HBase
>>
>> In addition, HBase can be made to go faster for MapReduce jobs, if the
>> HFile's could be used directly in HDFS, rather than proxying through
>> the RegionServer.
>>
>> I'd imagine that join operations do not require realtime-ness, and so
>> faster batch jobs using Hive -> frozen HBase files in HDFS could be
>> the optimal way to go?
>>
>> On Tue, May 31, 2011 at 1:41 PM, Patrick Angeles <patrick@cloudera.com> =
wrote:
>>> On Tue, May 31, 2011 at 3:19 PM, Eran Kutner <eran@gigya.com> wrote:
>>>
>>>> For my need I don't really need the general case, but even if I did I =
think
>>>> it can probably be done simpler.
>>>> The main problem is getting the data from both tables into the same MR=
 job,
>>>> without resorting to lookups. So without the theoretical
>>>> MutliTableInputFormat, I could just copy all the data from both tables=
 into
>>>> a temp table, just append the source table name to the row keys to mak=
e
>>>> sure
>>>> there are no conflicts. When all the data from both tables is in the s=
ame
>>>> temp table, run a MR job. For each row the mapper should emit a key wh=
ich
>>>> is
>>>> composed of all the values of the join fields in that row (the value c=
an be
>>>> emitted as is). This will cause all the rows from both tables, with sa=
me
>>>> join field values to arrive at the reducer together. The reducer could=
 then
>>>> iterate over them and produce the Cartesian product as needed.
>>>>
>>>> I still don't like having to copy all the data into a temp table just
>>>> because I can't feed two tables into the MR job.
>>>>
>>>
>>> Loading the smaller table in memory is called a map join, versus a
>>> reduce-side join (a.k.a. common join). One reason to prefer a map join =
is
>>> you avoid the shuffle phase which potentially involves several trips to=
 disk
>>> for the intermediate records due to spills, and also once through the
>>> network to get each intermediate KV pair to the right reducer. With a m=
ap
>>> join, everything is local, except for the part where you load the small
>>> table.
>>>
>>>
>>>>
>>>> As Jason Rutherglen mentioned above, Hive can do joins. I don't know i=
f it
>>>> can do them for HBase and it will not suit my needs, but it would be
>>>> interesting to know how is it doing them, if anyone knows.
>>>>
>>>
>>> The Hive-HBase integration allows you to create Hive tables that are ba=
cked
>>> by HBase. You can do joins on those tables (and also with standard Hive
>>> tables). It might be worth trying out in your case as it lets you easil=
y see
>>> the load characteristics and the job runtime without much coding invest=
ment.
>>>
>>> There are probably some specific optimizations that can be applied to y=
our
>>> situation, but it's hard to say without knowing your use-case.
>>>
>>> Regards,
>>>
>>> - Patrick
>>>
>>>
>>>> -eran
>>>>
>>>>
>>>>
>>>> On Tue, May 31, 2011 at 22:02, Ted Dunning <tdunning@maprtech.com> wro=
te:
>>>>
>>>> > The Cartesian product often makes an honest-to-god join not such a g=
ood
>>>> > idea
>>>> > on large data. =A0The common alternative is co-group
>>>> > which is basically like doing the hard work of the join, but involve=
s
>>>> > stopping just before emitting the cartesian product. =A0This allows
>>>> > you to inject whatever cleverness you need at this point.
>>>> >
>>>> > Common kinds of cleverness include down-sampling of problematically =
large
>>>> > sets of candidates.
>>>> >
>>>> > On Tue, May 31, 2011 at 11:56 AM, Michael Segel
>>>> > <michael_segel@hotmail.com>wrote:
>>>> >
>>>> > > So the underlying problem that the OP was trying to solve was how =
to
>>>> join
>>>> > > two tables from HBase.
>>>> > > Unfortunately I goofed.
>>>> > > I gave a quick and dirty solution that is a bit incomplete. They r=
ow
>>>> key
>>>> > in
>>>> > > the temp table has to be unique and I forgot about the Cartesian
>>>> > > product. So my solution wouldn't work in the general case.
>>>> > >
>>>> >
>>>>
>>>
>>
>