You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Martin Matula <ma...@gmail.com> on 2008/12/14 15:53:43 UTC
OLAP with Hive
Hi,
Is Hive capable of indexing the data and storing them in a way optimized for
querying (like a columnar database - bitmap indexes, compression, etc.)?
I need to be able to get decent response times for queries (up to a few
seconds) over huge amounts of analytical data. Is that achievable (with
appropriate number of machines in a cluster)? I saw the
serialization/deserialization of tables is pluggable. Is that the way to
make the storage more efficient? Any existing implementation (either ready
or in progress) that would be targeted at this? Or any hints on what I may
want to take a look at among the things that are currently available in
Hive/Hadoop?
Thanks,
Martin
Re: OLAP with Hive
Posted by Jeff Hammerbacher <ha...@cloudera.com>.
Er, lost the end of my first sentence: "analytical data [that are expected
to return in seconds]"
On Sun, Dec 14, 2008 at 3:14 PM, Jeff Hammerbacher <ha...@cloudera.com>wrote:
> Hey Martin,
>
> MapReduce was designed to perform serial scans over large quantities of
> data, so it's not clear that the programming paradigm will prove useful for
> queries over "huge" amounts of analytical data. You'll most likely need to
> materialize frequent and complex queries and get creative with table
> statistics and indexing to achieve your purposes.
>
> That being said, the Hive query language was designed to naturally
> incorporate Map and Reduce code written in any language via Hadoop
> Streaming. See the "Custom Map Reduce Scripts" section of
> http://wiki.apache.org/hadoop/Hive/HiveQL. Note that the syntax is
> evolving and may change, as indicated in
> https://issues.apache.org/jira/browse/HIVE-37.
>
> Regards,
> Jeff
>
>
> On Sun, Dec 14, 2008 at 1:32 PM, Martin Matula <ma...@gmail.com> wrote:
>
>> So is Hive not suitable for this at all and I should rather look for
>> something else or maybe try to build something on top of Hadoop and HBase?
>> Or would it make any sense for me to check out the pluggable serialization
>> in Hive? I am assuming I would also need to add my own MapReduce routines so
>> the value of using Hive would be questionable, right?
>> Thanks,
>> Martin
>>
>>
>> On Sun, Dec 14, 2008 at 10:06 PM, Joydeep Sen Sarma <jssarma@facebook.com
>> > wrote:
>>
>>> We have done some preliminary work with indexing – but that's not the
>>> focus right now and no code is available in the open source trunk for this
>>> purpose. I think it's fair to say that hive is not optimized for online
>>> processing right now. (and we are quite some ways off from columnar
>>> storage).
>>>
>>>
>>> ------------------------------
>>>
>>> *From:* Martin Matula [mailto:matulic@gmail.com]
>>> *Sent:* Sunday, December 14, 2008 6:54 AM
>>> *To:* hive-user@hadoop.apache.org
>>> *Subject:* OLAP with Hive
>>>
>>>
>>>
>>> Hi,
>>> Is Hive capable of indexing the data and storing them in a way optimized
>>> for querying (like a columnar database - bitmap indexes, compression, etc.)?
>>> I need to be able to get decent response times for queries (up to a few
>>> seconds) over huge amounts of analytical data. Is that achievable (with
>>> appropriate number of machines in a cluster)? I saw the
>>> serialization/deserialization of tables is pluggable. Is that the way to
>>> make the storage more efficient? Any existing implementation (either ready
>>> or in progress) that would be targeted at this? Or any hints on what I may
>>> want to take a look at among the things that are currently available in
>>> Hive/Hadoop?
>>> Thanks,
>>> Martin
>>>
>>
>>
>
Re: OLAP with Hive
Posted by Jeff Hammerbacher <ha...@cloudera.com>.
Hey Martin,
MapReduce was designed to perform serial scans over large quantities of
data, so it's not clear that the programming paradigm will prove useful for
queries over "huge" amounts of analytical data. You'll most likely need to
materialize frequent and complex queries and get creative with table
statistics and indexing to achieve your purposes.
That being said, the Hive query language was designed to naturally
incorporate Map and Reduce code written in any language via Hadoop
Streaming. See the "Custom Map Reduce Scripts" section of
http://wiki.apache.org/hadoop/Hive/HiveQL. Note that the syntax is evolving
and may change, as indicated in
https://issues.apache.org/jira/browse/HIVE-37.
Regards,
Jeff
On Sun, Dec 14, 2008 at 1:32 PM, Martin Matula <ma...@gmail.com> wrote:
> So is Hive not suitable for this at all and I should rather look for
> something else or maybe try to build something on top of Hadoop and HBase?
> Or would it make any sense for me to check out the pluggable serialization
> in Hive? I am assuming I would also need to add my own MapReduce routines so
> the value of using Hive would be questionable, right?
> Thanks,
> Martin
>
>
> On Sun, Dec 14, 2008 at 10:06 PM, Joydeep Sen Sarma <js...@facebook.com>wrote:
>
>> We have done some preliminary work with indexing – but that's not the
>> focus right now and no code is available in the open source trunk for this
>> purpose. I think it's fair to say that hive is not optimized for online
>> processing right now. (and we are quite some ways off from columnar
>> storage).
>>
>>
>> ------------------------------
>>
>> *From:* Martin Matula [mailto:matulic@gmail.com]
>> *Sent:* Sunday, December 14, 2008 6:54 AM
>> *To:* hive-user@hadoop.apache.org
>> *Subject:* OLAP with Hive
>>
>>
>>
>> Hi,
>> Is Hive capable of indexing the data and storing them in a way optimized
>> for querying (like a columnar database - bitmap indexes, compression, etc.)?
>> I need to be able to get decent response times for queries (up to a few
>> seconds) over huge amounts of analytical data. Is that achievable (with
>> appropriate number of machines in a cluster)? I saw the
>> serialization/deserialization of tables is pluggable. Is that the way to
>> make the storage more efficient? Any existing implementation (either ready
>> or in progress) that would be targeted at this? Or any hints on what I may
>> want to take a look at among the things that are currently available in
>> Hive/Hadoop?
>> Thanks,
>> Martin
>>
>
>
Re: OLAP with Hive
Posted by Martin Matula <ma...@gmail.com>.
So is Hive not suitable for this at all and I should rather look for
something else or maybe try to build something on top of Hadoop and HBase?
Or would it make any sense for me to check out the pluggable serialization
in Hive? I am assuming I would also need to add my own MapReduce routines so
the value of using Hive would be questionable, right?
Thanks,
Martin
On Sun, Dec 14, 2008 at 10:06 PM, Joydeep Sen Sarma <js...@facebook.com>wrote:
> We have done some preliminary work with indexing – but that's not the
> focus right now and no code is available in the open source trunk for this
> purpose. I think it's fair to say that hive is not optimized for online
> processing right now. (and we are quite some ways off from columnar
> storage).
>
>
> ------------------------------
>
> *From:* Martin Matula [mailto:matulic@gmail.com]
> *Sent:* Sunday, December 14, 2008 6:54 AM
> *To:* hive-user@hadoop.apache.org
> *Subject:* OLAP with Hive
>
>
>
> Hi,
> Is Hive capable of indexing the data and storing them in a way optimized
> for querying (like a columnar database - bitmap indexes, compression, etc.)?
> I need to be able to get decent response times for queries (up to a few
> seconds) over huge amounts of analytical data. Is that achievable (with
> appropriate number of machines in a cluster)? I saw the
> serialization/deserialization of tables is pluggable. Is that the way to
> make the storage more efficient? Any existing implementation (either ready
> or in progress) that would be targeted at this? Or any hints on what I may
> want to take a look at among the things that are currently available in
> Hive/Hadoop?
> Thanks,
> Martin
>
Re: OLAP with Hive
Posted by Josh Ferguson <jo...@besquared.net>.
I agree, that would be nice. Since the concept of partitioning is
fundamental you could materialize in small chunks, that could keep
materialization times down. You're especially lucky If one of the
partition dimensions is time-based because you're highly likely to
only materialize new data in that case.
One problem I run into a lot is that I have to aggregate into some
intermediate table to then do some kind of final output. It would be
nice to have that intermediate table always materialized. Right now I
just run them on hourly or daily schedules.
I think the main thing right now is just to get all the normal bugs
worked out, I feel like when I just create random queries it's a toss
up as to whether it will work or not..:) I'd like to fix that.
Josh
On Dec 15, 2008, at 7:10 PM, David Phillips wrote:
> On Mon, Dec 15, 2008 at 1:15 PM, Ashish Thusoo
> <at...@facebook.com> wrote:
>> There is a lot of work done on a technology called materialized
>> views which
>> can provide a lot of these benefits in a very useable manner,
>> though at the
>> expense of using more disk storage. Essentially, the idea is to
>> materialize
>> views of data and keep them updated when data changes.
>
> The other part is how they are used. The database can rewrite queries
> to use the materialized view instead of the base tables. This rewrite
> happens automatically, without any code changes or requiring users to
> know about the materialized views. A database tool can analyze
> queries and suggest materialized views to create.
Re: OLAP with Hive
Posted by David Phillips <da...@acz.org>.
On Mon, Dec 15, 2008 at 1:15 PM, Ashish Thusoo <at...@facebook.com> wrote:
> There is a lot of work done on a technology called materialized views which
> can provide a lot of these benefits in a very useable manner, though at the
> expense of using more disk storage. Essentially, the idea is to materialize
> views of data and keep them updated when data changes.
The other part is how they are used. The database can rewrite queries
to use the materialized view instead of the base tables. This rewrite
happens automatically, without any code changes or requiring users to
know about the materialized views. A database tool can analyze
queries and suggest materialized views to create.
RE: OLAP with Hive
Posted by Ashish Thusoo <at...@facebook.com>.
There is a lot of work done on a technology called materialized views which can provide a lot of these benefits in a very useable manner, though at the expense of using more disk storage. Essentially, the idea is to materialize views of data and keep them updated when data changes. Sometimes this is easy to do, at other times the entire view has to be reconstructed from scratch. These work really well for read intensive data that does not change very much with time. I would think that when the time comes, we should look at that more closely than simply columnar storage.
Ashish
________________________________
From: Josh Ferguson [mailto:josh@besquared.net]
Sent: Sunday, December 14, 2008 8:12 PM
To: hive-user@hadoop.apache.org
Subject: Re: OLAP with Hive
If you assume all the rows match across all of the column files (using null values where values don't exist) it is indeed very fast and quite simple in concept to do.. I think ease of use could be (and probably will be) debated for a long time. I like the idea of being able to choose which method is best for a particular table. I guess we can discuss it when the time comes.
Josh
On Dec 14, 2008, at 6:14 PM, Joydeep Sen Sarma wrote:
Regular joins are very expensive (hive doesn't implement either map-side or joins optimized for pre-sorted data yet). Joining corresponding rows of multiple files together is very cheap (it would be linear if disks are not a bottleneck). It should be better than merge join of multiple files (using unix join for example) since we would just read the head of each file and concatenate ..
Ease of use is the other fact. if performance/compaction can be transparently improved - then it's better for everyone .. (but views can get us there too).
Like Zheng says - disk seeks are a little bit of a wildcard (but our cluster, for example, is not disk iops bound and with good buffering/prefetching - one should be able to hide these costs to a large extent). And clearly things start falling apart with a high enough number of columns.
________________________________
From: Josh Ferguson [mailto:josh@besquared.net]
Sent: Sunday, December 14, 2008 4:26 PM
To: hive-user@hadoop.apache.org<ma...@hadoop.apache.org>
Subject: Re: OLAP with Hive
So before I was using hive I was doing this on a normal file system using the standard unix "join" command line application, which requires the two files to be sorted. It was indeed great when you didn't have to deal with more than one or two columns of data at a time.. After that the performance started degrading pretty rapidly since the running time of doing a join of M rows N times grows quickly.
I think what you'd want is the idea of a column family or grouping that were always stored together so that you could keep columns you use regularly together. The ones not used regularly could be joined later if a query called for them. Also if you created identifiers (by way of file name perhaps) on the column family and not the individual columns you only have to join by family not per-column. So this is sort of the middle-ground between having it all in one file and all in separate files.
Of course you can sort of already do this by just having separate tables and using the built in join functionality, so you could implement this with implicit table names like tablename_familyname or even by just adding an implicit partition to the file scheme. Then you could either make the user responsible for knowing which partitions to select from to get the proper columns or do some magic in the background.
Anyways, I'm sure you knew all of this, I was just stating it for the sake of the group..:)
Josh
On Dec 14, 2008, at 4:07 PM, Zheng Shao wrote:
For example, we can store one field (or a set of fields) of a table in one directory.
So table (string a, string b) can be stored like this:
a/part-00000
a/part-00001
a/part-00002
...
b/part-00000
b/part-00001
b/part-00002
...
The columnar organization can give us a huge performance gain, when the table is so wide, and most of the queries only access a very small number of columns.
The drawback is when we do need to access a lot of fields, we need to concatenate the fields backs into a row by reading multiple files at the same time. This will increase the number of disk seeks that we do, and some of the data need to go through the network. There was a discussion about whether we want to co-locate the file system blocks of the different fields of the same rows, but it's not very clear what's the best way to do that.
Zheng
On Sun, Dec 14, 2008 at 3:51 PM, Josh Ferguson <jo...@besquared.net>> wrote:
What would columnar organization look like and what are the benefits and drawbacks to this?
Josh
On Dec 14, 2008, at 3:13 PM, Joydeep Sen Sarma wrote:
That's a hard one. We can wish whatever we want to - but I guess it's all a question of who has the resources to contribute to it and what they want from Hive.
I can speak a little bit about Facebook. The reason we invested in indexing was not that it was the primary usage (or even a bottleneck for, say, performance optimization) - but because once you have so much data in one place - chances are that someone will come along and want to have quick lookups over some part of it (and u don't want to kill ur cluster by doing scans all the time). So that definitely makes indexing useful. We are also seeing that with dimensional analysis - where there is a need to drill down into detailed data - multidimensional indexes can be very useful. So in the long term - I think this is one of the desired features.
That doesn't make it akin to hbase though (in the sense that we still wouldn't have row level updates or real-time index updates). Katta may be complimentary and we were actually interested in investigating it for indexing (instead of rolling things from scratch).
Columnar organization is also very interesting. With all the hooks in hadoop (inputformatters) and hive(serdes) - I think it's fairly tractable to do this ..
________________________________
From: Josh Ferguson [mailto:josh@besquared.net]
Sent: Sunday, December 14, 2008 1:20 PM
To: hive-user@hadoop.apache.org<ma...@hadoop.apache.org>
Subject: Re: OLAP with Hive
I'd honestly like to see hive remain a partitioned flat file store. I don't think indexing what's inside the files is too incredibly useful in most situations where you'd use hive. I also think this kind of store is just the right fit for the hadoop and large scale analytics situation. I don't want to see hive go toward hbase or katta. What is the long term vision for hive?
Josh
On Dec 14, 2008, at 1:06 PM, Joydeep Sen Sarma wrote:
We have done some preliminary work with indexing - but that's not the focus right now and no code is available in the open source trunk for this purpose. I think it's fair to say that hive is not optimized for online processing right now. (and we are quite some ways off from columnar storage).
________________________________
From: Martin Matula [mailto:matulic@gmail.com]
Sent: Sunday, December 14, 2008 6:54 AM
To: hive-user@hadoop.apache.org<ma...@hadoop.apache.org>
Subject: OLAP with Hive
Hi,
Is Hive capable of indexing the data and storing them in a way optimized for querying (like a columnar database - bitmap indexes, compression, etc.)?
I need to be able to get decent response times for queries (up to a few seconds) over huge amounts of analytical data. Is that achievable (with appropriate number of machines in a cluster)? I saw the serialization/deserialization of tables is pluggable. Is that the way to make the storage more efficient? Any existing implementation (either ready or in progress) that would be targeted at this? Or any hints on what I may want to take a look at among the things that are currently available in Hive/Hadoop?
Thanks,
Martin
--
Yours,
Zheng
Re: OLAP with Hive
Posted by Josh Ferguson <jo...@besquared.net>.
If you assume all the rows match across all of the column files (using
null values where values don't exist) it is indeed very fast and quite
simple in concept to do.. I think ease of use could be (and probably
will be) debated for a long time. I like the idea of being able to
choose which method is best for a particular table. I guess we can
discuss it when the time comes.
Josh
On Dec 14, 2008, at 6:14 PM, Joydeep Sen Sarma wrote:
> Regular joins are very expensive (hive doesn’t implement either map-
> side or joins optimized for pre-sorted data yet). Joining
> corresponding rows of multiple files together is very cheap (it
> would be linear if disks are not a bottleneck). It should be better
> than merge join of multiple files (using unix join for example)
> since we would just read the head of each file and concatenate ..
>
> Ease of use is the other fact. if performance/compaction can be
> transparently improved – then it’s better for everyone .. (but views
> can get us there too).
>
> Like Zheng says – disk seeks are a little bit of a wildcard (but our
> cluster, for example, is not disk iops bound and with good buffering/
> prefetching – one should be able to hide these costs to a large
> extent). And clearly things start falling apart with a high enough
> number of columns.
>
>
> From: Josh Ferguson [mailto:josh@besquared.net]
> Sent: Sunday, December 14, 2008 4:26 PM
> To: hive-user@hadoop.apache.org
> Subject: Re: OLAP with Hive
>
> So before I was using hive I was doing this on a normal file system
> using the standard unix "join" command line application, which
> requires the two files to be sorted. It was indeed great when you
> didn't have to deal with more than one or two columns of data at a
> time.. After that the performance started degrading pretty rapidly
> since the running time of doing a join of M rows N times grows
> quickly.
>
> I think what you'd want is the idea of a column family or grouping
> that were always stored together so that you could keep columns you
> use regularly together. The ones not used regularly could be joined
> later if a query called for them. Also if you created identifiers
> (by way of file name perhaps) on the column family and not the
> individual columns you only have to join by family not per-column.
> So this is sort of the middle-ground between having it all in one
> file and all in separate files.
>
> Of course you can sort of already do this by just having separate
> tables and using the built in join functionality, so you could
> implement this with implicit table names like tablename_familyname
> or even by just adding an implicit partition to the file scheme.
> Then you could either make the user responsible for knowing which
> partitions to select from to get the proper columns or do some magic
> in the background.
>
> Anyways, I'm sure you knew all of this, I was just stating it for
> the sake of the group..:)
>
> Josh
>
>
> On Dec 14, 2008, at 4:07 PM, Zheng Shao wrote:
>
>
> For example, we can store one field (or a set of fields) of a table
> in one directory.
> So table (string a, string b) can be stored like this:
> a/part-00000
> a/part-00001
> a/part-00002
> ...
> b/part-00000
> b/part-00001
> b/part-00002
> ...
>
> The columnar organization can give us a huge performance gain, when
> the table is so wide, and most of the queries only access a very
> small number of columns.
>
> The drawback is when we do need to access a lot of fields, we need
> to concatenate the fields backs into a row by reading multiple files
> at the same time. This will increase the number of disk seeks that
> we do, and some of the data need to go through the network. There
> was a discussion about whether we want to co-locate the file system
> blocks of the different fields of the same rows, but it's not very
> clear what's the best way to do that.
>
> Zheng
> On Sun, Dec 14, 2008 at 3:51 PM, Josh Ferguson <jo...@besquared.net>
> wrote:
> What would columnar organization look like and what are the benefits
> and drawbacks to this?
>
> Josh
>
> On Dec 14, 2008, at 3:13 PM, Joydeep Sen Sarma wrote:
>
>
> That's a hard one. We can wish whatever we want to – but I guess
> it's all a question of who has the resources to contribute to it and
> what they want from Hive.
>
> I can speak a little bit about Facebook. The reason we invested in
> indexing was not that it was the primary usage (or even a bottleneck
> for, say, performance optimization) – but because once you have so
> much data in one place – chances are that someone will come along
> and want to have quick lookups over some part of it (and u don't
> want to kill ur cluster by doing scans all the time). So that
> definitely makes indexing useful. We are also seeing that with
> dimensional analysis – where there is a need to drill down into
> detailed data – multidimensional indexes can be very useful. So in
> the long term – I think this is one of the desired features.
>
> That doesn't make it akin to hbase though (in the sense that we
> still wouldn't have row level updates or real-time index updates).
> Katta may be complimentary and we were actually interested in
> investigating it for indexing (instead of rolling things from
> scratch).
>
> Columnar organization is also very interesting. With all the hooks
> in hadoop (inputformatters) and hive(serdes) – I think it's fairly
> tractable to do this ..
>
> From: Josh Ferguson [mailto:josh@besquared.net]
> Sent: Sunday, December 14, 2008 1:20 PM
> To: hive-user@hadoop.apache.org
> Subject: Re: OLAP with Hive
>
> I'd honestly like to see hive remain a partitioned flat file store.
> I don't think indexing what's inside the files is too incredibly
> useful in most situations where you'd use hive. I also think this
> kind of store is just the right fit for the hadoop and large scale
> analytics situation. I don't want to see hive go toward hbase or
> katta. What is the long term vision for hive?
>
> Josh
>
> On Dec 14, 2008, at 1:06 PM, Joydeep Sen Sarma wrote:
>
> We have done some preliminary work with indexing – but that's not
> the focus right now and no code is available in the open source
> trunk for this purpose. I think it's fair to say that hive is not
> optimized for online processing right now. (and we are quite some
> ways off from columnar storage).
>
> From: Martin Matula [mailto:matulic@gmail.com]
> Sent: Sunday, December 14, 2008 6:54 AM
> To: hive-user@hadoop.apache.org
> Subject: OLAP with Hive
>
> Hi,
> Is Hive capable of indexing the data and storing them in a way
> optimized for querying (like a columnar database - bitmap indexes,
> compression, etc.)?
> I need to be able to get decent response times for queries (up to a
> few seconds) over huge amounts of analytical data. Is that
> achievable (with appropriate number of machines in a cluster)? I saw
> the serialization/deserialization of tables is pluggable. Is that
> the way to make the storage more efficient? Any existing
> implementation (either ready or in progress) that would be targeted
> at this? Or any hints on what I may want to take a look at among the
> things that are currently available in Hive/Hadoop?
> Thanks,
> Martin
>
>
>
>
>
> --
> Yours,
> Zheng
>
RE: OLAP with Hive
Posted by Joydeep Sen Sarma <js...@facebook.com>.
Regular joins are very expensive (hive doesn't implement either map-side or joins optimized for pre-sorted data yet). Joining corresponding rows of multiple files together is very cheap (it would be linear if disks are not a bottleneck). It should be better than merge join of multiple files (using unix join for example) since we would just read the head of each file and concatenate ..
Ease of use is the other fact. if performance/compaction can be transparently improved - then it's better for everyone .. (but views can get us there too).
Like Zheng says - disk seeks are a little bit of a wildcard (but our cluster, for example, is not disk iops bound and with good buffering/prefetching - one should be able to hide these costs to a large extent). And clearly things start falling apart with a high enough number of columns.
________________________________
From: Josh Ferguson [mailto:josh@besquared.net]
Sent: Sunday, December 14, 2008 4:26 PM
To: hive-user@hadoop.apache.org
Subject: Re: OLAP with Hive
So before I was using hive I was doing this on a normal file system using the standard unix "join" command line application, which requires the two files to be sorted. It was indeed great when you didn't have to deal with more than one or two columns of data at a time.. After that the performance started degrading pretty rapidly since the running time of doing a join of M rows N times grows quickly.
I think what you'd want is the idea of a column family or grouping that were always stored together so that you could keep columns you use regularly together. The ones not used regularly could be joined later if a query called for them. Also if you created identifiers (by way of file name perhaps) on the column family and not the individual columns you only have to join by family not per-column. So this is sort of the middle-ground between having it all in one file and all in separate files.
Of course you can sort of already do this by just having separate tables and using the built in join functionality, so you could implement this with implicit table names like tablename_familyname or even by just adding an implicit partition to the file scheme. Then you could either make the user responsible for knowing which partitions to select from to get the proper columns or do some magic in the background.
Anyways, I'm sure you knew all of this, I was just stating it for the sake of the group..:)
Josh
On Dec 14, 2008, at 4:07 PM, Zheng Shao wrote:
For example, we can store one field (or a set of fields) of a table in one directory.
So table (string a, string b) can be stored like this:
a/part-00000
a/part-00001
a/part-00002
...
b/part-00000
b/part-00001
b/part-00002
...
The columnar organization can give us a huge performance gain, when the table is so wide, and most of the queries only access a very small number of columns.
The drawback is when we do need to access a lot of fields, we need to concatenate the fields backs into a row by reading multiple files at the same time. This will increase the number of disk seeks that we do, and some of the data need to go through the network. There was a discussion about whether we want to co-locate the file system blocks of the different fields of the same rows, but it's not very clear what's the best way to do that.
Zheng
On Sun, Dec 14, 2008 at 3:51 PM, Josh Ferguson <jo...@besquared.net>> wrote:
What would columnar organization look like and what are the benefits and drawbacks to this?
Josh
On Dec 14, 2008, at 3:13 PM, Joydeep Sen Sarma wrote:
That's a hard one. We can wish whatever we want to - but I guess it's all a question of who has the resources to contribute to it and what they want from Hive.
I can speak a little bit about Facebook. The reason we invested in indexing was not that it was the primary usage (or even a bottleneck for, say, performance optimization) - but because once you have so much data in one place - chances are that someone will come along and want to have quick lookups over some part of it (and u don't want to kill ur cluster by doing scans all the time). So that definitely makes indexing useful. We are also seeing that with dimensional analysis - where there is a need to drill down into detailed data - multidimensional indexes can be very useful. So in the long term - I think this is one of the desired features.
That doesn't make it akin to hbase though (in the sense that we still wouldn't have row level updates or real-time index updates). Katta may be complimentary and we were actually interested in investigating it for indexing (instead of rolling things from scratch).
Columnar organization is also very interesting. With all the hooks in hadoop (inputformatters) and hive(serdes) - I think it's fairly tractable to do this ..
________________________________
From: Josh Ferguson [mailto:josh@besquared.net]
Sent: Sunday, December 14, 2008 1:20 PM
To: hive-user@hadoop.apache.org<ma...@hadoop.apache.org>
Subject: Re: OLAP with Hive
I'd honestly like to see hive remain a partitioned flat file store. I don't think indexing what's inside the files is too incredibly useful in most situations where you'd use hive. I also think this kind of store is just the right fit for the hadoop and large scale analytics situation. I don't want to see hive go toward hbase or katta. What is the long term vision for hive?
Josh
On Dec 14, 2008, at 1:06 PM, Joydeep Sen Sarma wrote:
We have done some preliminary work with indexing - but that's not the focus right now and no code is available in the open source trunk for this purpose. I think it's fair to say that hive is not optimized for online processing right now. (and we are quite some ways off from columnar storage).
________________________________
From: Martin Matula [mailto:matulic@gmail.com]
Sent: Sunday, December 14, 2008 6:54 AM
To: hive-user@hadoop.apache.org<ma...@hadoop.apache.org>
Subject: OLAP with Hive
Hi,
Is Hive capable of indexing the data and storing them in a way optimized for querying (like a columnar database - bitmap indexes, compression, etc.)?
I need to be able to get decent response times for queries (up to a few seconds) over huge amounts of analytical data. Is that achievable (with appropriate number of machines in a cluster)? I saw the serialization/deserialization of tables is pluggable. Is that the way to make the storage more efficient? Any existing implementation (either ready or in progress) that would be targeted at this? Or any hints on what I may want to take a look at among the things that are currently available in Hive/Hadoop?
Thanks,
Martin
--
Yours,
Zheng
Re: OLAP with Hive
Posted by Josh Ferguson <jo...@besquared.net>.
So before I was using hive I was doing this on a normal file system
using the standard unix "join" command line application, which
requires the two files to be sorted. It was indeed great when you
didn't have to deal with more than one or two columns of data at a
time.. After that the performance started degrading pretty rapidly
since the running time of doing a join of M rows N times grows quickly.
I think what you'd want is the idea of a column family or grouping
that were always stored together so that you could keep columns you
use regularly together. The ones not used regularly could be joined
later if a query called for them. Also if you created identifiers (by
way of file name perhaps) on the column family and not the individual
columns you only have to join by family not per-column. So this is
sort of the middle-ground between having it all in one file and all in
separate files.
Of course you can sort of already do this by just having separate
tables and using the built in join functionality, so you could
implement this with implicit table names like tablename_familyname or
even by just adding an implicit partition to the file scheme. Then you
could either make the user responsible for knowing which partitions to
select from to get the proper columns or do some magic in the
background.
Anyways, I'm sure you knew all of this, I was just stating it for the
sake of the group..:)
Josh
On Dec 14, 2008, at 4:07 PM, Zheng Shao wrote:
> For example, we can store one field (or a set of fields) of a table
> in one directory.
> So table (string a, string b) can be stored like this:
> a/part-00000
> a/part-00001
> a/part-00002
> ...
> b/part-00000
> b/part-00001
> b/part-00002
> ...
>
> The columnar organization can give us a huge performance gain, when
> the table is so wide, and most of the queries only access a very
> small number of columns.
>
> The drawback is when we do need to access a lot of fields, we need
> to concatenate the fields backs into a row by reading multiple files
> at the same time. This will increase the number of disk seeks that
> we do, and some of the data need to go through the network. There
> was a discussion about whether we want to co-locate the file system
> blocks of the different fields of the same rows, but it's not very
> clear what's the best way to do that.
>
> Zheng
>
> On Sun, Dec 14, 2008 at 3:51 PM, Josh Ferguson <jo...@besquared.net>
> wrote:
> What would columnar organization look like and what are the benefits
> and drawbacks to this?
>
> Josh
>
> On Dec 14, 2008, at 3:13 PM, Joydeep Sen Sarma wrote:
>
>> That's a hard one. We can wish whatever we want to – but I guess
>> it's all a question of who has the resources to contribute to it
>> and what they want from Hive.
>>
>> I can speak a little bit about Facebook. The reason we invested in
>> indexing was not that it was the primary usage (or even a
>> bottleneck for, say, performance optimization) – but because once
>> you have so much data in one place – chances are that someone will
>> come along and want to have quick lookups over some part of it (and
>> u don't want to kill ur cluster by doing scans all the time). So
>> that definitely makes indexing useful. We are also seeing that with
>> dimensional analysis – where there is a need to drill down into
>> detailed data – multidimensional indexes can be very useful. So in
>> the long term – I think this is one of the desired features.
>>
>> That doesn't make it akin to hbase though (in the sense that we
>> still wouldn't have row level updates or real-time index updates).
>> Katta may be complimentary and we were actually interested in
>> investigating it for indexing (instead of rolling things from
>> scratch).
>>
>> Columnar organization is also very interesting. With all the hooks
>> in hadoop (inputformatters) and hive(serdes) – I think it's fairly
>> tractable to do this ..
>>
>> From: Josh Ferguson [mailto:josh@besquared.net]
>> Sent: Sunday, December 14, 2008 1:20 PM
>> To: hive-user@hadoop.apache.org
>> Subject: Re: OLAP with Hive
>>
>> I'd honestly like to see hive remain a partitioned flat file store.
>> I don't think indexing what's inside the files is too incredibly
>> useful in most situations where you'd use hive. I also think this
>> kind of store is just the right fit for the hadoop and large scale
>> analytics situation. I don't want to see hive go toward hbase or
>> katta. What is the long term vision for hive?
>>
>> Josh
>>
>> On Dec 14, 2008, at 1:06 PM, Joydeep Sen Sarma wrote:
>>
>>
>> We have done some preliminary work with indexing – but that's not
>> the focus right now and no code is available in the open source
>> trunk for this purpose. I think it's fair to say that hive is not
>> optimized for online processing right now. (and we are quite some
>> ways off from columnar storage).
>>
>> From: Martin Matula [mailto:matulic@gmail.com]
>> Sent: Sunday, December 14, 2008 6:54 AM
>> To: hive-user@hadoop.apache.org
>> Subject: OLAP with Hive
>>
>> Hi,
>> Is Hive capable of indexing the data and storing them in a way
>> optimized for querying (like a columnar database - bitmap indexes,
>> compression, etc.)?
>> I need to be able to get decent response times for queries (up to a
>> few seconds) over huge amounts of analytical data. Is that
>> achievable (with appropriate number of machines in a cluster)? I
>> saw the serialization/deserialization of tables is pluggable. Is
>> that the way to make the storage more efficient? Any existing
>> implementation (either ready or in progress) that would be targeted
>> at this? Or any hints on what I may want to take a look at among
>> the things that are currently available in Hive/Hadoop?
>> Thanks,
>> Martin
>>
>
>
>
>
> --
> Yours,
> Zheng
Re: OLAP with Hive
Posted by Zheng Shao <zs...@gmail.com>.
For example, we can store one field (or a set of fields) of a table in one
directory.
So table (string a, string b) can be stored like this:
a/part-00000
a/part-00001
a/part-00002
...
b/part-00000
b/part-00001
b/part-00002
...
The columnar organization can give us a huge performance gain, when the
table is so wide, and most of the queries only access a very small number of
columns.
The drawback is when we do need to access a lot of fields, we need to
concatenate the fields backs into a row by reading multiple files at the
same time. This will increase the number of disk seeks that we do, and some
of the data need to go through the network. There was a discussion about
whether we want to co-locate the file system blocks of the different fields
of the same rows, but it's not very clear what's the best way to do that.
Zheng
On Sun, Dec 14, 2008 at 3:51 PM, Josh Ferguson <jo...@besquared.net> wrote:
> What would columnar organization look like and what are the benefits and
> drawbacks to this?
> Josh
>
> On Dec 14, 2008, at 3:13 PM, Joydeep Sen Sarma wrote:
>
> That's a hard one. We can wish whatever we want to – but I guess it's all a
> question of who has the resources to contribute to it and what they want
> from Hive.
>
> I can speak a little bit about Facebook. The reason we invested in indexing
> was not that it was the primary usage (or even a bottleneck for, say,
> performance optimization) – but because once you have so much data in one
> place – chances are that someone will come along and want to have quick
> lookups over some part of it (and u don't want to kill ur cluster by doing
> scans all the time). So that definitely makes indexing useful. We are also
> seeing that with dimensional analysis – where there is a need to drill down
> into detailed data – multidimensional indexes can be very useful. So in the
> long term – I think this is one of the desired features.
>
> That doesn't make it akin to hbase though (in the sense that we still
> wouldn't have row level updates or real-time index updates). Katta may be
> complimentary and we were actually interested in investigating it for
> indexing (instead of rolling things from scratch).
>
> Columnar organization is also very interesting. With all the hooks in
> hadoop (inputformatters) and hive(serdes) – I think it's fairly tractable
> to do this ..
>
> ------------------------------
> *From:* Josh Ferguson [mailto:josh@besquared.net <jo...@besquared.net>]
> *Sent:* Sunday, December 14, 2008 1:20 PM
> *To:* hive-user@hadoop.apache.org
> *Subject:* Re: OLAP with Hive
>
> I'd honestly like to see hive remain a partitioned flat file store. I
> don't think indexing what's inside the files is too incredibly useful in
> most situations where you'd use hive. I also think this kind of store is
> just the right fit for the hadoop and large scale analytics situation. I
> don't want to see hive go toward hbase or katta. What is the long term
> vision for hive?
>
> Josh
>
> On Dec 14, 2008, at 1:06 PM, Joydeep Sen Sarma wrote:
>
>
> We have done some preliminary work with indexing – but that's not the focus
> right now and no code is available in the open source trunk for this
> purpose. I think it's fair to say that hive is not optimized for online
> processing right now. (and we are quite some ways off from columnar
> storage).
>
> ------------------------------
> *From:* Martin Matula [mailto:matulic@gmail.com <ma...@gmail.com>]
> *Sent:* Sunday, December 14, 2008 6:54 AM
> *To:* hive-user@hadoop.apache.org
> *Subject:* OLAP with Hive
>
> Hi,
> Is Hive capable of indexing the data and storing them in a way optimized
> for querying (like a columnar database - bitmap indexes, compression, etc.)?
> I need to be able to get decent response times for queries (up to a few
> seconds) over huge amounts of analytical data. Is that achievable (with
> appropriate number of machines in a cluster)? I saw the
> serialization/deserialization of tables is pluggable. Is that the way to
> make the storage more efficient? Any existing implementation (either ready
> or in progress) that would be targeted at this? Or any hints on what I may
> want to take a look at among the things that are currently available in
> Hive/Hadoop?
> Thanks,
> Martin
>
>
>
>
--
Yours,
Zheng
Re: OLAP with Hive
Posted by Josh Ferguson <jo...@besquared.net>.
What would columnar organization look like and what are the benefits
and drawbacks to this?
Josh
On Dec 14, 2008, at 3:13 PM, Joydeep Sen Sarma wrote:
> That’s a hard one. We can wish whatever we want to – but I guess
> it’s all a question of who has the resources to contribute to it and
> what they want from Hive.
>
> I can speak a little bit about Facebook. The reason we invested in
> indexing was not that it was the primary usage (or even a bottleneck
> for, say, performance optimization) – but because once you have so
> much data in one place – chances are that someone will come along
> and want to have quick lookups over some part of it (and u don’t
> want to kill ur cluster by doing scans all the time). So that
> definitely makes indexing useful. We are also seeing that with
> dimensional analysis – where there is a need to drill down into
> detailed data – multidimensional indexes can be very useful. So in
> the long term – I think this is one of the desired features.
>
> That doesn’t make it akin to hbase though (in the sense that we
> still wouldn’t have row level updates or real-time index updates).
> Katta may be complimentary and we were actually interested in
> investigating it for indexing (instead of rolling things from
> scratch).
>
> Columnar organization is also very interesting. With all the hooks
> in hadoop (inputformatters) and hive(serdes) – I think it’s fairly
> tractable to do this ..
>
> From: Josh Ferguson [mailto:josh@besquared.net]
> Sent: Sunday, December 14, 2008 1:20 PM
> To: hive-user@hadoop.apache.org
> Subject: Re: OLAP with Hive
>
> I'd honestly like to see hive remain a partitioned flat file store.
> I don't think indexing what's inside the files is too incredibly
> useful in most situations where you'd use hive. I also think this
> kind of store is just the right fit for the hadoop and large scale
> analytics situation. I don't want to see hive go toward hbase or
> katta. What is the long term vision for hive?
>
> Josh
>
> On Dec 14, 2008, at 1:06 PM, Joydeep Sen Sarma wrote:
>
>
> We have done some preliminary work with indexing – but that’s not
> the focus right now and no code is available in the open source
> trunk for this purpose. I think it’s fair to say that hive is not
> optimized for online processing right now. (and we are quite some
> ways off from columnar storage).
>
> From: Martin Matula [mailto:matulic@gmail.com]
> Sent: Sunday, December 14, 2008 6:54 AM
> To: hive-user@hadoop.apache.org
> Subject: OLAP with Hive
>
> Hi,
> Is Hive capable of indexing the data and storing them in a way
> optimized for querying (like a columnar database - bitmap indexes,
> compression, etc.)?
> I need to be able to get decent response times for queries (up to a
> few seconds) over huge amounts of analytical data. Is that
> achievable (with appropriate number of machines in a cluster)? I saw
> the serialization/deserialization of tables is pluggable. Is that
> the way to make the storage more efficient? Any existing
> implementation (either ready or in progress) that would be targeted
> at this? Or any hints on what I may want to take a look at among the
> things that are currently available in Hive/Hadoop?
> Thanks,
> Martin
>
RE: OLAP with Hive
Posted by Joydeep Sen Sarma <js...@facebook.com>.
That's a hard one. We can wish whatever we want to - but I guess it's all a question of who has the resources to contribute to it and what they want from Hive.
I can speak a little bit about Facebook. The reason we invested in indexing was not that it was the primary usage (or even a bottleneck for, say, performance optimization) - but because once you have so much data in one place - chances are that someone will come along and want to have quick lookups over some part of it (and u don't want to kill ur cluster by doing scans all the time). So that definitely makes indexing useful. We are also seeing that with dimensional analysis - where there is a need to drill down into detailed data - multidimensional indexes can be very useful. So in the long term - I think this is one of the desired features.
That doesn't make it akin to hbase though (in the sense that we still wouldn't have row level updates or real-time index updates). Katta may be complimentary and we were actually interested in investigating it for indexing (instead of rolling things from scratch).
Columnar organization is also very interesting. With all the hooks in hadoop (inputformatters) and hive(serdes) - I think it's fairly tractable to do this ..
________________________________
From: Josh Ferguson [mailto:josh@besquared.net]
Sent: Sunday, December 14, 2008 1:20 PM
To: hive-user@hadoop.apache.org
Subject: Re: OLAP with Hive
I'd honestly like to see hive remain a partitioned flat file store. I don't think indexing what's inside the files is too incredibly useful in most situations where you'd use hive. I also think this kind of store is just the right fit for the hadoop and large scale analytics situation. I don't want to see hive go toward hbase or katta. What is the long term vision for hive?
Josh
On Dec 14, 2008, at 1:06 PM, Joydeep Sen Sarma wrote:
We have done some preliminary work with indexing - but that's not the focus right now and no code is available in the open source trunk for this purpose. I think it's fair to say that hive is not optimized for online processing right now. (and we are quite some ways off from columnar storage).
________________________________
From: Martin Matula [mailto:matulic@gmail.com]
Sent: Sunday, December 14, 2008 6:54 AM
To: hive-user@hadoop.apache.org<ma...@hadoop.apache.org>
Subject: OLAP with Hive
Hi,
Is Hive capable of indexing the data and storing them in a way optimized for querying (like a columnar database - bitmap indexes, compression, etc.)?
I need to be able to get decent response times for queries (up to a few seconds) over huge amounts of analytical data. Is that achievable (with appropriate number of machines in a cluster)? I saw the serialization/deserialization of tables is pluggable. Is that the way to make the storage more efficient? Any existing implementation (either ready or in progress) that would be targeted at this? Or any hints on what I may want to take a look at among the things that are currently available in Hive/Hadoop?
Thanks,
Martin
Re: OLAP with Hive
Posted by Josh Ferguson <jo...@besquared.net>.
I'd honestly like to see hive remain a partitioned flat file store. I
don't think indexing what's inside the files is too incredibly useful
in most situations where you'd use hive. I also think this kind of
store is just the right fit for the hadoop and large scale analytics
situation. I don't want to see hive go toward hbase or katta. What is
the long term vision for hive?
Josh
On Dec 14, 2008, at 1:06 PM, Joydeep Sen Sarma wrote:
> We have done some preliminary work with indexing – but that’s not
> the focus right now and no code is available in the open source
> trunk for this purpose. I think it’s fair to say that hive is not
> optimized for online processing right now. (and we are quite some
> ways off from columnar storage).
>
> From: Martin Matula [mailto:matulic@gmail.com]
> Sent: Sunday, December 14, 2008 6:54 AM
> To: hive-user@hadoop.apache.org
> Subject: OLAP with Hive
>
> Hi,
> Is Hive capable of indexing the data and storing them in a way
> optimized for querying (like a columnar database - bitmap indexes,
> compression, etc.)?
> I need to be able to get decent response times for queries (up to a
> few seconds) over huge amounts of analytical data. Is that
> achievable (with appropriate number of machines in a cluster)? I saw
> the serialization/deserialization of tables is pluggable. Is that
> the way to make the storage more efficient? Any existing
> implementation (either ready or in progress) that would be targeted
> at this? Or any hints on what I may want to take a look at among the
> things that are currently available in Hive/Hadoop?
> Thanks,
> Martin
RE: OLAP with Hive
Posted by Joydeep Sen Sarma <js...@facebook.com>.
We have done some preliminary work with indexing - but that's not the focus right now and no code is available in the open source trunk for this purpose. I think it's fair to say that hive is not optimized for online processing right now. (and we are quite some ways off from columnar storage).
________________________________
From: Martin Matula [mailto:matulic@gmail.com]
Sent: Sunday, December 14, 2008 6:54 AM
To: hive-user@hadoop.apache.org
Subject: OLAP with Hive
Hi,
Is Hive capable of indexing the data and storing them in a way optimized for querying (like a columnar database - bitmap indexes, compression, etc.)?
I need to be able to get decent response times for queries (up to a few seconds) over huge amounts of analytical data. Is that achievable (with appropriate number of machines in a cluster)? I saw the serialization/deserialization of tables is pluggable. Is that the way to make the storage more efficient? Any existing implementation (either ready or in progress) that would be targeted at this? Or any hints on what I may want to take a look at among the things that are currently available in Hive/Hadoop?
Thanks,
Martin