Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 Search Hadoop and all its subprojects:

Switch to Plain View
Hive >> mail # user >> Table Wrapper

Peter Marron 2013-06-25, 14:29
Stephen Sprague 2013-06-25, 17:36
Peter Marron 2013-06-27, 08:46
Nitin Pawar 2013-06-27, 08:51
Copy link to this message
RE: Table Wrapper

I have thought about a map-only join, but as I understand it this is still going
to do a full table scan on my large data file. If this is billions of records then it's
still going to be slow, even if it only returns a handful of records.

Also I don't know of any way to get Hive to do a join without performing a
Map/Reduce. And, as I mentioned before, just the overheads of setting
up a Map/Reduce, even if it's map only and does practically nothing, makes the
elapsed time too high. I want it to be interactive. (I guess that something Tez
when it becomes available might solve this problem...)

My ugly "hack" approach works in seconds, the overhead for setting up Map/Reduce takes this into minutes.

Indexing looks promising but, as far as I can see, it can't be done without a Map/Reduce.

If I could find a way to perform a join or use indexing without a Map/Reduce I would be happy to use that approach.

Partitioning and ORC would be helpful but I can't assume anything about the original data format.


From: Nitin Pawar [mailto:[EMAIL PROTECTED]]
Sent: 27 June 2013 09:52
Subject: Re: Table Wrapper

few thoughts:
If you have a smaller file (in size of MB's) have you tried considering map only join?
also if you are interested in particular records from a table and do not want to go through entire table to find them, then partitioning + indexing will be handy.

ORCFile Format (still very new) can help you in this regard as well.

On Thu, Jun 27, 2013 at 2:16 PM, Peter Marron <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>> wrote:
Well, I'm not very good at keeping things brief, unfortunately.
But I'll have a go, trying to keep things simple.

Suppose that I have a data table in Hive and it has many rows - say billions.
I have another file stored in HDFS (it can be a Hive table too if it helps)
and this file is small and contains file offsets into the data, Stored as binary,
8 bytes per offset. Now suppose that I want to read the records from the data
defined by the offsets in the small file, in the order defined in the small file.

How can I do that?

The obvious way is to turn the small file into a Hive table and provide a custom
InputFormat which can read the binary. I've done that, that's the easy part and
then I could form a query like this:

                SELECT * FROM data JOIN small ON data. ON data.BLOCK__OFFSET__INSIDE__FILE = small.offset;

But, when it works, this performs awfully.

The approach that I have taken is to create a "copy" of the data table which is "hacked" to use a custom input
format which knows about the small file and which overrides the record reader to use the offsets
as seeks before it reads the records. This is awkward, for various reasons, but it works well. I can
avoid a full table scan, in fact I can suppress any Map/Reduce and so the query runs very quickly.

So I was just trying to "wrap" the data table so that I didn't have to create the copy.

I hope that you don't regret asking too much.



From: Stephen Sprague [mailto:[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>]
Sent: 25 June 2013 18:37
Subject: Re: Table Wrapper

Good luck, bro. :)   May i ask why are you doing this to yourself? I think your instincts are correct going down the path you describe sounds a tad more painful than just hitting yourself in the head with a hammer. Different strokes for different folks though.
so can we back up? what - briefly if possible - do you want to achieve with a "wrapper"? (i'm going to regret asking that i know.)
On Tue, Jun 25, 2013 at 7:29 AM, Peter Marron <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>> wrote:

Running Hive 0.11.0 over Hadoop 1.0.4.

I would like to be able to "wrap" a Hive table.

So, if I have table "X" which uses SerDe "s" and InputFormat "i"
then I would like to be able to create a table "Y" which has a
SerDe "ws" which is a wrapper of "s" (and so can encapsulate an instance of "s")
and an InputFormat "wi" which is a wrapper of "I" (and similarly encapsulates an
instance of "i").  So far I have done this by creating a table like this

CREATE TABLE Y (... copy of underlying table's columns...)
'serialization.format'='|',              'field.delim'='|'
  OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'

I have to add the names of the underlying classes "s" and "I"
into the table properties so that I know what to instantiate.
I also have to replicate all the column details of the wrapped table
to ensure the correct information is passed down to the underlying SerDe
when I instantiate it. I also have to know the output format. I have to
explicitly add the default SerDe properties to get it to work.
I have to explicitly provide the default output format too.
If any changes are made to the underlying table then I need to reflect
those changes in my "wrapper" table.
It's a mess.

What I'd like to be able to do is to just parameterise my wrapper
table with the name of the underlying table and using that name
be able to instantiate the correct SerDe and InputFormat.

Is there an easier way to do this?

Any pointers appreciated.


Nitin Pawar
Jan Dolinár 2013-06-27, 09:59
Peter Marron 2013-06-27, 10:14
Stephen Sprague 2013-06-27, 15:39
Peter Marron 2013-06-28, 08:52
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB