You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@kylin.apache.org by Sarnath <st...@gmail.com> on 2015/12/11 17:27:49 UTC
Re: Group by + where clause
Here is the Sunday afternoon cuppa tea that I promised. Sorry about the
delay. I have tried to be as fair as possible and have advised pinch if
salt where necessary....
http://www.hcltech.com/blogs/engineering-and-rd-services/olap-cubing-big-data
Thanks,
Best,
Sarnath & Big data CoE from HCL
Re: Group by + where clause
Posted by Sarnath <st...@gmail.com>.
Okay... I think I finally understood some of your perspective. The
aggregations are seen as a table. Since each type of aggregation can
aggregate different type of functions/metrics, the columns can potentially
be different.. So from such a perspective, HBase looks like a good option.
Also, the rowkey approach helps to fold all kinds of things into it and
serves as an index for the cube data.
Now I understand, what Julian was coming from.
This is good - at least I got to understand the perspective from which you
are looking at the problem. Thanks!
Re: Group by + where clause
Posted by Sarnath <st...@gmail.com>.
Yes, the experiment is small. But is a good proof of concept - that
Inverted index based search can avoid some unnecessary scans. On the scale
side, MR and ElasticSearch are proven solutions. We will build a better
experiment around Mar 2016. We are not a data company. So we will have to
find some way for that.
Best,
Sarnath
Re: Group by + where clause
Posted by Li Yang <li...@apache.org>.
The experiment in the blog didn't address the big data problem, which is
the key challenge. Kylin is designed for big data that cannot fit in
memory. That's why the pre-aggregate approach, and also a big reason why
HBase is selected.
But still the exploration of alternative storage is inspiring. I had a
similar hunch like Julian's that a hybrid of indexed and aggregated might
work out very well. Kylin is always open for a better storage that features
1) big data; 2) fast range scan; 3) read-time coprocessor; 4) secondary
index. HBase's shortcoming is 4).
On Sat, Dec 12, 2015 at 9:49 AM, Sarnath <st...@gmail.com> wrote:
> Hi Luke,
>
> Few points:
>
> 1)
> As I mentioned above, the KV pairs corresponding to an aggregation are
> stored as 1 elastic search document. ES indexes on all fields and takes
> care of the REST API DSL.
> The KV pairs are same as what kylin stores in hbase. Kylin, as per my
> understanding, breaks the KV pairs among rowkey and columns. The dimensions
> go to rowkey and metrics go to columns. And I believe that's the reason why
> Kylin will do full-scan for the query doled out by Seshu. ES does not
> differentiate between metrics, dimensions. It indexes everything. Hence the
> range queries mentioned by Seshu should also run pretty fast with ES. We
> will experiment that and report here as well.
>
> 2)We don't do SQL to REST API conversion yet. The entire REST API DSL is
> provided by ES. So we don't sweat anything on the REST API.
>
> 3)
> In the Blog, we only claim on the fluctuation in performance, while
> filtering group-by on different dimensions. We don't claim on performance.
> But we will get there soon.
>
> 4)
> Druid, as I understood from Julian's email, does not build cube. It stores
> raw data in a sorted order so that OLAP queries (group by) can be answered
> fast without building cube.
>
> Best,
> Sarnath
> On Dec 12, 2015 5:43 AM, "Luke Han" <lu...@gmail.com> wrote:
>
> > Would you mind to share more detail about how you indexing these
> > aggregations and how your query will convert to ES API?
> >
> > BTW, does this similar to Druid doing?
> >
> >
> > >Multiple indexing is what we take advantage of. ES, by default indexes
> on
> > >all fields of a document. We store a multidimensional aggregation as an
> ES
> > >document whose fields are the various dimensions and metrics associated
> > >with the aggregation.
> >
> >
> > Best Regards!
> > ---------------------
> >
> > Luke Han
> >
> > On Sat, Dec 12, 2015 at 3:05 AM, Sarnath <st...@gmail.com> wrote:
> >
> > > >>>> Sorted indexes are a viable approach to OLAP storage — Druid[1]
> does
> > > it, and so does SAP HANA. The idea is that if you sort and compress
> your
> > > data it becomes very compact, so you can do very fast scans. So fast
> that
> > > you don’t need to pre-aggregate it.
> > >
> > > Yes, the problem (which I think you have covered below) is that you can
> > > only sort on a column of interest... And you can sort again on other
> > > columns among all rows where the first column has the same value....
> But
> > > then, if you were to filter by second column - you will still need to
> > scan
> > > entire table. Very similar to the analogy in our blog.(search for all
> > > English words whose second letter is 'a')
> > > And, as your filtering query becomes complex, it becomes very
> difficult.
> > I
> > > believe Druid is optimized for time series analytics (how much by
> minute,
> > > hour, day etc..). Not sure about multidimensional aggregations...
> > >
> > > >>>> Elasticsearch is an index but it is not an OLAP index - their use
> > case
> > > does not call for compressing numeric data, and they optimize for point
> > > lookups rather than scans.
> > >
> > > We use ES only to serve pre-aggregated cube data and not to index the
> raw
> > > data to produce OLAP cubes.
> > >
> > > >>>>> The best OLAP indexes are able to combine multiple indexes. E.g.
> > take
> > > two not-very-selective conditions and make a selective condition. The
> > > poorer ones can only use one index, so to get coverage you need to
> build
> > > more indexes.
> > >
> > > Can you elaborate on Not-so-selective condition? I am a bit lost on the
> > > context.
> > >
> > > Multiple indexing is what we take advantage of. ES, by default indexes
> on
> > > all fields of a document. We store a multidimensional aggregation as an
> > ES
> > > document whose fields are the various dimensions and metrics associated
> > > with the aggregation. Thus the cube can be sliced and diced on any
> > > dimension and filtered on metrics as well.. And again, this indexing is
> > > completely different from indexing on raw data or table data. We are
> > > dealing with data cubes here.
> > >
> > > Best,
> > > Sarnath
> > >
> >
>
Re: Group by + where clause
Posted by Sarnath <st...@gmail.com>.
Hi Luke,
Few points:
1)
As I mentioned above, the KV pairs corresponding to an aggregation are
stored as 1 elastic search document. ES indexes on all fields and takes
care of the REST API DSL.
The KV pairs are same as what kylin stores in hbase. Kylin, as per my
understanding, breaks the KV pairs among rowkey and columns. The dimensions
go to rowkey and metrics go to columns. And I believe that's the reason why
Kylin will do full-scan for the query doled out by Seshu. ES does not
differentiate between metrics, dimensions. It indexes everything. Hence the
range queries mentioned by Seshu should also run pretty fast with ES. We
will experiment that and report here as well.
2)We don't do SQL to REST API conversion yet. The entire REST API DSL is
provided by ES. So we don't sweat anything on the REST API.
3)
In the Blog, we only claim on the fluctuation in performance, while
filtering group-by on different dimensions. We don't claim on performance.
But we will get there soon.
4)
Druid, as I understood from Julian's email, does not build cube. It stores
raw data in a sorted order so that OLAP queries (group by) can be answered
fast without building cube.
Best,
Sarnath
On Dec 12, 2015 5:43 AM, "Luke Han" <lu...@gmail.com> wrote:
> Would you mind to share more detail about how you indexing these
> aggregations and how your query will convert to ES API?
>
> BTW, does this similar to Druid doing?
>
>
> >Multiple indexing is what we take advantage of. ES, by default indexes on
> >all fields of a document. We store a multidimensional aggregation as an ES
> >document whose fields are the various dimensions and metrics associated
> >with the aggregation.
>
>
> Best Regards!
> ---------------------
>
> Luke Han
>
> On Sat, Dec 12, 2015 at 3:05 AM, Sarnath <st...@gmail.com> wrote:
>
> > >>>> Sorted indexes are a viable approach to OLAP storage — Druid[1] does
> > it, and so does SAP HANA. The idea is that if you sort and compress your
> > data it becomes very compact, so you can do very fast scans. So fast that
> > you don’t need to pre-aggregate it.
> >
> > Yes, the problem (which I think you have covered below) is that you can
> > only sort on a column of interest... And you can sort again on other
> > columns among all rows where the first column has the same value.... But
> > then, if you were to filter by second column - you will still need to
> scan
> > entire table. Very similar to the analogy in our blog.(search for all
> > English words whose second letter is 'a')
> > And, as your filtering query becomes complex, it becomes very difficult.
> I
> > believe Druid is optimized for time series analytics (how much by minute,
> > hour, day etc..). Not sure about multidimensional aggregations...
> >
> > >>>> Elasticsearch is an index but it is not an OLAP index - their use
> case
> > does not call for compressing numeric data, and they optimize for point
> > lookups rather than scans.
> >
> > We use ES only to serve pre-aggregated cube data and not to index the raw
> > data to produce OLAP cubes.
> >
> > >>>>> The best OLAP indexes are able to combine multiple indexes. E.g.
> take
> > two not-very-selective conditions and make a selective condition. The
> > poorer ones can only use one index, so to get coverage you need to build
> > more indexes.
> >
> > Can you elaborate on Not-so-selective condition? I am a bit lost on the
> > context.
> >
> > Multiple indexing is what we take advantage of. ES, by default indexes on
> > all fields of a document. We store a multidimensional aggregation as an
> ES
> > document whose fields are the various dimensions and metrics associated
> > with the aggregation. Thus the cube can be sliced and diced on any
> > dimension and filtered on metrics as well.. And again, this indexing is
> > completely different from indexing on raw data or table data. We are
> > dealing with data cubes here.
> >
> > Best,
> > Sarnath
> >
>
Re: Group by + where clause
Posted by Luke Han <lu...@gmail.com>.
Would you mind to share more detail about how you indexing these
aggregations and how your query will convert to ES API?
BTW, does this similar to Druid doing?
>Multiple indexing is what we take advantage of. ES, by default indexes on
>all fields of a document. We store a multidimensional aggregation as an ES
>document whose fields are the various dimensions and metrics associated
>with the aggregation.
Best Regards!
---------------------
Luke Han
On Sat, Dec 12, 2015 at 3:05 AM, Sarnath <st...@gmail.com> wrote:
> >>>> Sorted indexes are a viable approach to OLAP storage — Druid[1] does
> it, and so does SAP HANA. The idea is that if you sort and compress your
> data it becomes very compact, so you can do very fast scans. So fast that
> you don’t need to pre-aggregate it.
>
> Yes, the problem (which I think you have covered below) is that you can
> only sort on a column of interest... And you can sort again on other
> columns among all rows where the first column has the same value.... But
> then, if you were to filter by second column - you will still need to scan
> entire table. Very similar to the analogy in our blog.(search for all
> English words whose second letter is 'a')
> And, as your filtering query becomes complex, it becomes very difficult. I
> believe Druid is optimized for time series analytics (how much by minute,
> hour, day etc..). Not sure about multidimensional aggregations...
>
> >>>> Elasticsearch is an index but it is not an OLAP index - their use case
> does not call for compressing numeric data, and they optimize for point
> lookups rather than scans.
>
> We use ES only to serve pre-aggregated cube data and not to index the raw
> data to produce OLAP cubes.
>
> >>>>> The best OLAP indexes are able to combine multiple indexes. E.g. take
> two not-very-selective conditions and make a selective condition. The
> poorer ones can only use one index, so to get coverage you need to build
> more indexes.
>
> Can you elaborate on Not-so-selective condition? I am a bit lost on the
> context.
>
> Multiple indexing is what we take advantage of. ES, by default indexes on
> all fields of a document. We store a multidimensional aggregation as an ES
> document whose fields are the various dimensions and metrics associated
> with the aggregation. Thus the cube can be sliced and diced on any
> dimension and filtered on metrics as well.. And again, this indexing is
> completely different from indexing on raw data or table data. We are
> dealing with data cubes here.
>
> Best,
> Sarnath
>
Re: Group by + where clause
Posted by Sarnath <st...@gmail.com>.
>>>> Sorted indexes are a viable approach to OLAP storage — Druid[1] does
it, and so does SAP HANA. The idea is that if you sort and compress your
data it becomes very compact, so you can do very fast scans. So fast that
you don’t need to pre-aggregate it.
Yes, the problem (which I think you have covered below) is that you can
only sort on a column of interest... And you can sort again on other
columns among all rows where the first column has the same value.... But
then, if you were to filter by second column - you will still need to scan
entire table. Very similar to the analogy in our blog.(search for all
English words whose second letter is 'a')
And, as your filtering query becomes complex, it becomes very difficult. I
believe Druid is optimized for time series analytics (how much by minute,
hour, day etc..). Not sure about multidimensional aggregations...
>>>> Elasticsearch is an index but it is not an OLAP index - their use case
does not call for compressing numeric data, and they optimize for point
lookups rather than scans.
We use ES only to serve pre-aggregated cube data and not to index the raw
data to produce OLAP cubes.
>>>>> The best OLAP indexes are able to combine multiple indexes. E.g. take
two not-very-selective conditions and make a selective condition. The
poorer ones can only use one index, so to get coverage you need to build
more indexes.
Can you elaborate on Not-so-selective condition? I am a bit lost on the
context.
Multiple indexing is what we take advantage of. ES, by default indexes on
all fields of a document. We store a multidimensional aggregation as an ES
document whose fields are the various dimensions and metrics associated
with the aggregation. Thus the cube can be sliced and diced on any
dimension and filtered on metrics as well.. And again, this indexing is
completely different from indexing on raw data or table data. We are
dealing with data cubes here.
Best,
Sarnath
Re: Group by + where clause
Posted by Julian Hyde <jh...@apache.org>.
Sorted indexes are a viable approach to OLAP storage — Druid[1] does it, and so does SAP HANA. The idea is that if you sort and compress your data it becomes very compact, so you can do very fast scans. So fast that you don’t need to pre-aggregate it.
OLAP indexes require that the attributes you want to partition on are present in the table — therefore they cannot handle joins (i.e. star schemas). So, for a OLAP traditional star schema, I’d go for aggregation.
I suspect that they don’t handle updates too well, but I may be wrong.
Elasticsearch is an index but it is not an OLAP index - their use case does not call for compressing numeric data, and they optimize for point lookups rather than scans.
The best OLAP indexes are able to combine multiple indexes. E.g. take two not-very-selective conditions and make a selective condition. The poorer ones can only use one index, so to get coverage you need to build more indexes.
I am still pondering the relationship between index-based OLAP and aggregate-based OLAP. My hunch is that the ideal would be a hybrid system, using aggregates for high-level queries and using indexes for time-series like queries, queries over a narrow time period, and queries on recent data.
Julian
[1] http://druid.io/
> On Dec 11, 2015, at 9:39 AM, Sarnath <st...@gmail.com> wrote:
>
> By default, ES indexes on all fields.. And that includes metric as well :)
>
> Check this:
> https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-range-query.html
>
> BTW... How do you do that in HBase today?
Re: Group by + where clause
Posted by Sarnath <st...@gmail.com>.
By default, ES indexes on all fields.. And that includes metric as well :)
Check this:
https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-range-query.html
BTW... How do you do that in HBase today?
Re: Group by + where clause
Posted by Luke Han <lu...@gmail.com>.
Well, I think it's more for your talk than Sunday tea:-)
As I read your blog, the testing data and case is quite simple, only 2
dimensions and 1 metrics without any join and so small dataset.
As previous discussion, it's really not apple to apple, as mentioned in the
blog, query over Kylin's REST API compare to ElasticSearch REST API is not
exactly same level, would like to suggest another testing to between HBase
scan vs ElasticSearch API.
Thanks.
Best Regards!
---------------------
Luke Han
On Sat, Dec 12, 2015 at 12:27 AM, Sarnath <st...@gmail.com> wrote:
> Here is the Sunday afternoon cuppa tea that I promised. Sorry about the
> delay. I have tried to be as fair as possible and have advised pinch if
> salt where necessary....
>
>
> http://www.hcltech.com/blogs/engineering-and-rd-services/olap-cubing-big-data
>
> Thanks,
> Best,
> Sarnath & Big data CoE from HCL
>
Re: Group by + where clause
Posted by "Adunuthula, Seshu" <sa...@ebay.com>.
Sarnath,
This is great info and a lot more fun discussion to have, as the quote
goes ³if we are going by the opinions lets use mine, otherwise lets look
at the dataŠ²
‹‹‹‹
Our work is essentially similar to what Apache Kylin
<http://kylin.apache.org/> does. Kylin uses HBase as their store and it
uses carefully designed Row-Keys for searching data in Cubes. If we
understand right, the row-keys are made up of a bitmask representing the
dimensions that are grouped followed by values of each dimension. The
values corresponding to the row-key are the different metrics calculated
for that combination of dimensions.
In our opinion, Row-key based search in HBase is essentially a search on
lexicographically ordered data and this can cause un-necessary lags in
OLAP Cube Search (especially when you are slicing and dicing the cube).
For e.g. Let us say we want to search for all words in an English
dictionary where second letter is Œa¹. We still need to go through all
chapters of a ³dictionary². Inside each chapter, we still need to ³scan²
until we find our results. Our solution uses a Search mechanism powered by
inverted-index (Courtesy: ElasticSearch). Inverted index does not require
such nearly-full-scans and should be able to retrieve data much faster. In
our case, ElasticSearch lifts this burden and additionally we don¹t have
to worry about
‹‹‹
I am still parsing this information, but how well does an inverted index
perform for a range query, get me all sales for a region where sales is <
10M?
On 12/11/15, 8:27 AM, "Sarnath" <st...@gmail.com> wrote:
>Here is the Sunday afternoon cuppa tea that I promised. Sorry about the
>delay. I have tried to be as fair as possible and have advised pinch if
>salt where necessary....
>
>http://www.hcltech.com/blogs/engineering-and-rd-services/olap-cubing-big-d
>ata
>
>Thanks,
>Best,
>Sarnath & Big data CoE from HCL