hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Nitin Pawar <nitinpawar...@gmail.com>
Subject Re: Table Wrapper
Date Thu, 27 Jun 2013 08:51:45 GMT
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 <
Peter.Marron@trilliumsoftware.com> 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.****
>
> ** **
>
> Regards,****
>
> ** **
>
> Z****
>
> ** **
>
> *From:* Stephen Sprague [mailto:spragues@gmail.com]
> *Sent:* 25 June 2013 18:37
> *To:* user@hive.apache.org
> *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 <
> Peter.Marron@trilliumsoftware.com> wrote:****
>
> Hi,****
>
>  ****
>
> 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...)****
>
> ROW FORMAT SERDE 'ws'****
>
> WITH SERDEPROPERTIES (…****
>
> 'wrapped.serde.name'='org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe',
> ****
>
> ‘wrapped.inputformat.name’=’TextInputFormat’,****
>
> 'serialization.format'='|',              'field.delim'='|'****
>
> )****
>
> STORED AS****
>
>   INPUTFORMAT 'wi'****
>
>   OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
> ****
>
> TBLPROPERTIES (…);****
>
>  ****
>
> 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.****
>
>  ****
>
> Z****
>
>  ****
>
> ** **
>



-- 
Nitin Pawar

Mime
View raw message