Mailing-List: contact user-help@hive.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@hive.apache.org
Received-SPF: pass (nike.apache.org: domain of edlinuxguru@gmail.com
 designates 209.85.214.176 as permitted sender)
MIME-Version: 1.0
In-Reply-To: 
 <CAHvtZbb6c5boF=7fA43_Tu5FyRqwE-W9mTK9n543AUgk_BERKw@mail.gmail.com>
References: 
 <CAHvtZbb6c5boF=7fA43_Tu5FyRqwE-W9mTK9n543AUgk_BERKw@mail.gmail.com>
Date: Sat, 5 May 2012 17:44:49 -0400
Message-ID: 
 <CAENxBwwfZFeZBj2_2GonPkW6C-tFggrMJUpjnDUNP__F9Libhg@mail.gmail.com>
Subject: Re: Problem writing SerDe to read Nutch crawldb because Hive seems to
 ignore the Key and only reads the Value from SequenceFiles.
From: Edward Capriolo <edlinuxguru@gmail.com>
To: user@hive.apache.org, safdar.kureishy@gmail.com
Cc: user@nutch.apache.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

This is one of the things about hive the key is not easily available.
You are going to need an input format that creates a new value which
is contains the key and the value.

Like this:
<url:Text> <data:CrawlDatum> -> <null-writable>  new
MyKeyValue<<url:Text> <data:CrawlDatum>>


On Sat, May 5, 2012 at 4:05 PM, Ali Safdar Kureishy
<safdar.kureishy@gmail.com> wrote:
> Hi,
>
> I have attached a Sequence file with the following format:
> <url:Text> <data:CrawlDatum>
>
> (CrawlDatum is a custom Java type, that contains several fields that woul=
d
> be flattened into several columns by the SerDe).
>
> In other words, what I would like to do, is to expose this URL+CrawlDatum
> data via a Hive External table, with the following columns:
> || url || status || fetchtime || fetchinterval || modifiedtime || retries=
 ||
> score || metadata ||
>
> So, I was hoping that after defining a custom SerDe, I would just have to
> define the Hive table as follows:
>
> CREATE EXTERNAL TABLE crawldb
> (url STRING, status STRING, fetchtime LONG, fetchinterval LONG, modifiedt=
ime
> LONG, retries INT, score FLOAT, metadata MAP)
> ROW FORMAT SERDE 'NutchCrawlDBSequenceFileSerDe'
> STORED AS SEQUENCEFILE
> LOCATION '/user/training/deepcrawl/crawldb/current/part-00000';
>
> For example, a sample record should like like the following through a Hiv=
e
> table:
> || http://www.cnn.com || FETCHED || 125355734857 || 36000 || 12453775834 =
||
> 1 || 0.98 || {x=3D1,y=3D2,p=3D3,q=3D4} ||
>
> I would like this to be possible without having to duplicate/flatten the
> data through a separate transformation. Initially, I thought my custom Se=
rDe
> could have following definition for serialize():
>
> =A0 =A0 =A0 =A0 @override
> public Object deserialize(Writable obj) throws SerDeException {
> =A0 =A0 =A0 =A0 =A0 =A0 ...
> =A0 =A0 =A0 =A0 =A0}
>
> But the problem is that the input argument obj above is only the
> VALUE=A0portion of a Sequence record. There seems to be a limitation with=
 the
> way Hive reads Sequence files. Specifically, for each row in a sequence
> file, the KEY is ignored and only the VALUE is used by Hive. This is seen
> from the org.apache.hadoop.hive.ql.exec.FetchOperator::getNextRow() metho=
d
> below, which ignores the KEY when iterating over a RecordReader (see bold
> text below from the corresponding Hive code for
> FetchOperator::getNextRow()):
>
> =A0 /**
> =A0 =A0* Get the next row. The fetch context is modified appropriately.
> =A0 =A0*
> =A0 =A0**/
> =A0 public InspectableObject getNextRow() throws IOException {
> =A0 =A0 try {
> =A0 =A0 =A0 while (true) {
> =A0 =A0 =A0 =A0 if (currRecReader =3D=3D null) {
> =A0 =A0 =A0 =A0 =A0 currRecReader =3D getRecordReader();
> =A0 =A0 =A0 =A0 =A0 if (currRecReader =3D=3D null) {
> =A0 =A0 =A0 =A0 =A0 =A0 return null;
> =A0 =A0 =A0 =A0 =A0 }
> =A0 =A0 =A0 =A0 }
>
> =A0 =A0 =A0 =A0 boolean ret =3D currRecReader.next(key, value);
> =A0 =A0 =A0 =A0 if (ret) {
> =A0 =A0 =A0 =A0 =A0 if (this.currPart =3D=3D null) {
> =A0 =A0 =A0 =A0 =A0 =A0 Object obj =3D serde.deserialize(value);
> =A0 =A0 =A0 =A0 =A0 =A0 return new InspectableObject(obj, serde.getObject=
Inspector());
> =A0 =A0 =A0 =A0 =A0 } else {
> =A0 =A0 =A0 =A0 =A0 =A0 rowWithPart[0] =3D serde.deserialize(value);
> =A0 =A0 =A0 =A0 =A0 =A0 return new InspectableObject(rowWithPart, rowObje=
ctInspector);
> =A0 =A0 =A0 =A0 =A0 }
> =A0 =A0 =A0 =A0 } else {
> =A0 =A0 =A0 =A0 =A0 currRecReader.close();
> =A0 =A0 =A0 =A0 =A0 currRecReader =3D null;
> =A0 =A0 =A0 =A0 }
> =A0 =A0 =A0 }
> =A0 =A0 } catch (Exception e) {
> =A0 =A0 =A0 throw new IOException(e);
> =A0 =A0 }
> =A0 }
>
> As you can see, the "key" variable is ignored and never returned. The
> problem is that in the Nutch crawldb Sequence File, the KEY is the URL, a=
nd
> I need it to be displayed in the Hive table along with the fields of
> CrawlDatum. But when writing the the custom SerDe, I only see the CrawlDa=
tum
> that comes after the key, on each record...which is not sufficient.
>
> One hack could be to write a CustomSequenceFileRecordReader.java that
> returns the offset in the sequence file as the KEY, and an aggregation of
> the (Key+Value) as the VALUE. For that, perhaps I need to hack the code
> below from SequenceFileRecordReader, which will get really very messy:
> =A0 protected synchronized boolean next(K key)
> =A0 =A0 throws IOException {
> =A0 =A0 if (!more) return false;
> =A0 =A0 long pos =3D in.getPosition();
> =A0 =A0 boolean remaining =3D (in.next(key) !=3D null);
> =A0 =A0 if (pos >=3D end && in.syncSeen()) {
> =A0 =A0 =A0 more =3D false;
> =A0 =A0 } else {
> =A0 =A0 =A0 more =3D remaining;
> =A0 =A0 }
> =A0 =A0 return more;
> =A0 }
>
> This would require me to write a CustomSequenceFileRecordReader and a
> CustomSequenceFileInputFormat and then some custom SerDe, and probably ma=
ke
> several other changes as well. Is it possible to just get away with writi=
ng
> a custom SerDe and some pre-existing reader that includes the key when
> invoking SerDe.deserialize()? Unless I'm missing something, why does Hive
> have this limitation, when accessing Sequence files? I would imagine that
> the key of a sequence file record would be just as important as the
> value...so why is it left out by the FetchOperator:getNextRow() method?
>
> If this is the unfortunate reality with reading sequence files in Nutch, =
is
> there another Hive storage format I should use that works around this
> limitation? Such as "create external table ..... STORED AS
> CUSTOM_SEQUENCEFILE"? Or, let's say I write my own
> CustomHiveSequenceFileInputFormat, how do i register it with Hive and use=
 it
> in the Hive "STORED AS" definition?
>
> Any help or pointers would be greatly appreciated. I hope I'm mistaken ab=
out
> the limitation above, and if not, hopefully there is an easy way to resol=
ve
> this through a custom SerDe alone.
>
> Warm regards,
> Safdar