You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@kylin.apache.org by 蒋旭 <ji...@qq.com> on 2015/11/16 12:20:54 UTC
回复： Group by + where clause
Kylin architecture is more focus on extreme large data set (e.g. billion+). In OLAP domain, user don't care about 100+ ms query latency and only hope to keep the big query good enough. If you want to compare the performance, please try some very large dataset that can't be kept in memory (e.g. In eBay, the data set is 100+billion). Besides query latency, you also need to test query throughput that is another side of performance.
Another thing about the benchmark, the data schema and cube design is crucial to the performance. Good or bad cube design will have very different performance result.
Thanks
Jiang Xu
------------------ 原始邮件 ------------------
发件人: "Li Yang";<li...@apache.org>;
发送时间: 2015年11月16日(星期一) 下午3:30
收件人: "dev"<de...@kylin.incubator.apache.org>;
主题: Re: Group by + where clause
The SQL overhead is around hundreds of ms, so don't worry about the ms
level difference.
I'm more interested in the HBase/ES comparison on big data load. HBase
design incorporates random read/write, while ES can optimize for batch load
and read-only. In that sense, HBase is born with a small burden.
Many have suggested storage other than HBase. Here we have another one ES.
:-)
I think that's what Kylin has plugin architecture. Anyone can implement a
new storage plugin and run Kylin on anywhere if not HBase.
On Mon, Nov 16, 2015 at 2:31 PM, Sarnath <st...@gmail.com> wrote:
> On Nov 16, 2015 5:07 AM, "Luke Han" <lu...@gmail.com> wrote:
> >
> > Hi Sarnath, It's interesting result, could you please share your full
> testing case, data and result?
>
> Thanks. I will post the result this week. Will need to cross check
>
> > And could you please share your cube engine's design even code(is it open
> source?)? So that we also could know how the comparison goes to and help
> you to understand more. Thanks.
>
> This is closed software. But we don't do any smart stuff. Rather straight
> forward...thanks!
> >
> > Regards!
> > Luke Han
> >
> > _____________________________
> > From: Sarnath <st...@gmail.com>
> > Sent: 星期一, 十一月 16, 2015 01:06
> > Subject: Re: Group by + where clause
> > To: <de...@kylin.incubator.apache.org>
> >
> >
> > A small correction: I meant data block encoding on hbase
>
Re: 回复： Group by + where clause
Posted by Sarnath <st...@gmail.com>.
Sure, thanks for the info. As per the developer ES saves lot in disk
storage for cube as well. But I will have to confirm that after
relooking... Anyway, I will share detailed report soon... Possibly through
our company blog site... I will definitely mention this thread, the apples
vs oranges and others so that it is fair... Thanks for all your time.
Re: Group by + where clause
Posted by Julian Hyde <jh...@apache.org>.
Good to meet you too.
I see you asked some questions about Calcite + Avatica earlier in the thread. Feel free to ask them on the calcite dev list (or indeed log Calcite JIRA cases) — Avatica is a sub-component of Calcite. Luke and some other Kylin committers monitor the dev list so I’m sure they’ll chime in if your questions are specific to how Kylin uses Avatica/Calcite.
Julian
> On Nov 16, 2015, at 5:56 PM, Sarnath <st...@gmail.com> wrote:
>
> Hi Julian,
> Good to connect with you. Have heard about your contributions through my
> lead who is now working in avatica...
>
> I will see if I can instrument hbase scans from hbase logs or stats.. That
> will help nail things.
>
> Best,
> Sarnath
Re: Group by + where clause
Posted by Sarnath <st...@gmail.com>.
Hi Julian,
Good to connect with you. Have heard about your contributions through my
lead who is now working in avatica...
I will see if I can instrument hbase scans from hbase logs or stats.. That
will help nail things.
Best,
Sarnath
Re: Group by + where clause
Posted by Julian Hyde <jh...@apache.org>.
Re "unproductive discussions" - I feel your pain. They are a fact of
life in Apache, I'm afraid. If you can't steer them into a useful
direction, you should just ignore them. Anyone who has read "how to
ask questions the smart way" will not be offended if you don't reply
to every email.
On Wed, Nov 18, 2015 at 10:42 AM, Adunuthula, Seshu
<sa...@ebay.com> wrote:
> Agree with the context of what you are saying and my comment about "the
> thread has to stop" was probably overboard.
>
> In my defense threads I have usually seen threads like these ³X is better
> than Y² dissolve into unproductive discussions, but I agree that we have
> not reached that state here yet.
>
> Seshu
>
> On 11/18/15, 9:09 AM, "Julian Hyde" <jh...@apache.org> wrote:
>
>>PS [1] http://www.catb.org/esr/faqs/smart-questions.html
>>
>>> On Nov 18, 2015, at 9:07 AM, Julian Hyde <jh...@apache.org> wrote:
>>>
>>>
>>>> On Nov 18, 2015, at 5:13 AM, Adunuthula, Seshu <sa...@ebay.com>
>>>>wrote:
>>>>
>>>> I think this thread has reached its level of usefulness and should
>>>>stop.
>>>
>>> If I may, a point about email etiquette. And by ³etiquette² I don¹t
>>>mean to imply that anyone was being impolite. It¹s just that there are
>>>ways to maximize the efficiency of a conversation (and the valuable time
>>>of everyone in the community) without stifling discussion.
>>>
>>> This thread does not need to stop. But Seshu is at liberty to ignore
>>>it, or to point out that it is not where his priorities lie. There may
>>>well be others in the community who are interested in Sarnath¹s proposal
>>>and would like to help.
>>>
>>> I also understand the sub-text: that Seshu works for a company that is
>>>a major contributor of resources to Kylin. While we leave our corporate
>>>affiliations at the door in Apache, I think it is fine to signal, as
>>>Seshu did, that the priorities of that team lie elsewhere. I¹d rather
>>>that such communication occur here than in some back-channel.
>>>
>>> A big touch-point for me is Eric Raymond¹s ³How to ask questions the
>>>smart way²[1]. A common theme is that it is OK to ignore questions that
>>>are inappropriate. Ignoring questions is a great way to deal with
>>>trolls, fools and time-wasters.
>>>
>>> In my opinion, the only time when someone should be asked to stop
>>>posting is when they have grossly breached the standards of the
>>>community: personal attacks, rudeness. For anything less, let the thread
>>>continue. If no one has the time or interest to reply to it, it will
>>>quickly peter out.
>>>
>>> Julian
>>>
>
Re: Group by + where clause
Posted by "Adunuthula, Seshu" <sa...@ebay.com>.
Agree with the context of what you are saying and my comment about "the
thread has to stop" was probably overboard.
In my defense threads I have usually seen threads like these ³X is better
than Y² dissolve into unproductive discussions, but I agree that we have
not reached that state here yet.
Seshu
On 11/18/15, 9:09 AM, "Julian Hyde" <jh...@apache.org> wrote:
>PS [1] http://www.catb.org/esr/faqs/smart-questions.html
>
>> On Nov 18, 2015, at 9:07 AM, Julian Hyde <jh...@apache.org> wrote:
>>
>>
>>> On Nov 18, 2015, at 5:13 AM, Adunuthula, Seshu <sa...@ebay.com>
>>>wrote:
>>>
>>> I think this thread has reached its level of usefulness and should
>>>stop.
>>
>> If I may, a point about email etiquette. And by ³etiquette² I don¹t
>>mean to imply that anyone was being impolite. It¹s just that there are
>>ways to maximize the efficiency of a conversation (and the valuable time
>>of everyone in the community) without stifling discussion.
>>
>> This thread does not need to stop. But Seshu is at liberty to ignore
>>it, or to point out that it is not where his priorities lie. There may
>>well be others in the community who are interested in Sarnath¹s proposal
>>and would like to help.
>>
>> I also understand the sub-text: that Seshu works for a company that is
>>a major contributor of resources to Kylin. While we leave our corporate
>>affiliations at the door in Apache, I think it is fine to signal, as
>>Seshu did, that the priorities of that team lie elsewhere. I¹d rather
>>that such communication occur here than in some back-channel.
>>
>> A big touch-point for me is Eric Raymond¹s ³How to ask questions the
>>smart way²[1]. A common theme is that it is OK to ignore questions that
>>are inappropriate. Ignoring questions is a great way to deal with
>>trolls, fools and time-wasters.
>>
>> In my opinion, the only time when someone should be asked to stop
>>posting is when they have grossly breached the standards of the
>>community: personal attacks, rudeness. For anything less, let the thread
>>continue. If no one has the time or interest to reply to it, it will
>>quickly peter out.
>>
>> Julian
>>
Re: Group by + where clause
Posted by Julian Hyde <jh...@apache.org>.
PS [1] http://www.catb.org/esr/faqs/smart-questions.html
> On Nov 18, 2015, at 9:07 AM, Julian Hyde <jh...@apache.org> wrote:
>
>
>> On Nov 18, 2015, at 5:13 AM, Adunuthula, Seshu <sa...@ebay.com> wrote:
>>
>> I think this thread has reached its level of usefulness and should stop.
>
> If I may, a point about email etiquette. And by “etiquette” I don’t mean to imply that anyone was being impolite. It’s just that there are ways to maximize the efficiency of a conversation (and the valuable time of everyone in the community) without stifling discussion.
>
> This thread does not need to stop. But Seshu is at liberty to ignore it, or to point out that it is not where his priorities lie. There may well be others in the community who are interested in Sarnath’s proposal and would like to help.
>
> I also understand the sub-text: that Seshu works for a company that is a major contributor of resources to Kylin. While we leave our corporate affiliations at the door in Apache, I think it is fine to signal, as Seshu did, that the priorities of that team lie elsewhere. I’d rather that such communication occur here than in some back-channel.
>
> A big touch-point for me is Eric Raymond’s “How to ask questions the smart way”[1]. A common theme is that it is OK to ignore questions that are inappropriate. Ignoring questions is a great way to deal with trolls, fools and time-wasters.
>
> In my opinion, the only time when someone should be asked to stop posting is when they have grossly breached the standards of the community: personal attacks, rudeness. For anything less, let the thread continue. If no one has the time or interest to reply to it, it will quickly peter out.
>
> Julian
>
Re: Group by + where clause
Posted by Julian Hyde <jh...@apache.org>.
> On Nov 18, 2015, at 5:13 AM, Adunuthula, Seshu <sa...@ebay.com> wrote:
>
> I think this thread has reached its level of usefulness and should stop.
If I may, a point about email etiquette. And by “etiquette” I don’t mean to imply that anyone was being impolite. It’s just that there are ways to maximize the efficiency of a conversation (and the valuable time of everyone in the community) without stifling discussion.
This thread does not need to stop. But Seshu is at liberty to ignore it, or to point out that it is not where his priorities lie. There may well be others in the community who are interested in Sarnath’s proposal and would like to help.
I also understand the sub-text: that Seshu works for a company that is a major contributor of resources to Kylin. While we leave our corporate affiliations at the door in Apache, I think it is fine to signal, as Seshu did, that the priorities of that team lie elsewhere. I’d rather that such communication occur here than in some back-channel.
A big touch-point for me is Eric Raymond’s “How to ask questions the smart way”[1]. A common theme is that it is OK to ignore questions that are inappropriate. Ignoring questions is a great way to deal with trolls, fools and time-wasters.
In my opinion, the only time when someone should be asked to stop posting is when they have grossly breached the standards of the community: personal attacks, rudeness. For anything less, let the thread continue. If no one has the time or interest to reply to it, it will quickly peter out.
Julian
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
Re: Group by + where clause
Posted by Sarnath <st...@gmail.com>.
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>.
Sure, thanks. Looking for ODBC support in Kylin. One of our customers is
pure ODBC shop.. I think this will be useful. Congrats on getting this done
from scratch!
On Nov 25, 2015 6:14 AM, "Luke Han" <lu...@gmail.com> wrote:
> I think Julian and Calcite community could give you more answer for SQL
> one.
>
> Kylin's ODBC driver is build from sketch which only support consuming Kylin
> API so far.
>
> Thanks.
>
>
> Best Regards!
> ---------------------
>
> Luke Han
>
> On Wed, Nov 25, 2015 at 3:48 AM, Sarnath <st...@gmail.com> wrote:
>
> > Just to add few more...
> > ES provides a JSON based query DSL which can be easily used for slicing,
> > dicing....and for querying the aggregations.....through REST API..So we
> > have no work to do either on storage or on retrieval.
> > We only worry about providing SQL interface....we are exploring
> avatica...
> > Do you have any advice on how to go about this? I also see that kylin 2.x
> > is getting ODBC support. Is that support coming from avatica or is it
> > something that you guys developed?
> > Best,
> > Sarnath
> >
>
Re: Group by + where clause
Posted by Luke Han <lu...@gmail.com>.
I think Julian and Calcite community could give you more answer for SQL one.
Kylin's ODBC driver is build from sketch which only support consuming Kylin
API so far.
Thanks.
Best Regards!
---------------------
Luke Han
On Wed, Nov 25, 2015 at 3:48 AM, Sarnath <st...@gmail.com> wrote:
> Just to add few more...
> ES provides a JSON based query DSL which can be easily used for slicing,
> dicing....and for querying the aggregations.....through REST API..So we
> have no work to do either on storage or on retrieval.
> We only worry about providing SQL interface....we are exploring avatica...
> Do you have any advice on how to go about this? I also see that kylin 2.x
> is getting ODBC support. Is that support coming from avatica or is it
> something that you guys developed?
> Best,
> Sarnath
>
Re: Group by + where clause
Posted by Sarnath <st...@gmail.com>.
Just to add few more...
ES provides a JSON based query DSL which can be easily used for slicing,
dicing....and for querying the aggregations.....through REST API..So we
have no work to do either on storage or on retrieval.
We only worry about providing SQL interface....we are exploring avatica...
Do you have any advice on how to go about this? I also see that kylin 2.x
is getting ODBC support. Is that support coming from avatica or is it
something that you guys developed?
Best,
Sarnath
Re: Group by + where clause
Posted by Sarnath <st...@gmail.com>.
Hi Luke,
We still have to do loads of work before we call ourselves an OLAP engine.
We played around with calcite and were able to develop an SQL interface on
top of an ES index.
BTW.. Can you explain what you mean by extreme cases? Like, overflow
problems while summation?.. If you could give some hints, we will use that
for testing....
We are contemplating to use hive olap query facility for testing. Need to
check if that would be feasible.
Thanks,
Best,
Sarnath
Re: Group by + where clause
Posted by Luke Han <lu...@gmail.com>.
Hi Sarnth,
Thanks to share this, we are using different methodologies, but the
basic idea
are same, to using KV storage to reduce query latency.
I have to say OLAP is a complex system which not only about storage,
there were some discussions about Redis/HBase/Cassandra for underline
K-V storage. HBase may not the perfect option yet but it works so far,
at least in our cases. I do not know too much about ES so that I can't give
you too much comments about that, but would like to suggest to do more
testing
with different data and scenarios, we actually made such progress with many
extreme cases to help verify and validate.
BTW, are you just using ES REST api for query? Or bring SQL on top of
your engine?
Thanks.
Luke
Best Regards!
---------------------
Luke Han
On Fri, Nov 20, 2015 at 12:01 AM, Sarnath <st...@gmail.com> wrote:
> Hi Luke,
> I will definitely post back here when we make things public.
>
> My choice of ES is based on simple reasoning:
>
> When we build a Cuboid -- we basically build Key-Value pairs. i.e.
> 1) "Country=US", "State=Texas", "City=Houston", "Quantity=500"
> 2) "Country=India", "State=Karnataka", "City=Bangalore", "Quantity=1000"
> and so on...
>
> That looked to me like an ES document (or) a SOLR document
>
> And, When we search it, We search it like this:
>
> 1) Fetch me all documents that have "Country=US", "State=Texas"
> 2) Fetch me all documents that correspond to "Country=India".
> 3) Fetch me all documents that correspond to "Country=US"
> and so on..
>
> All these are essentially search problems solved by Elastic Search/SOLR and
> they are scalable as well..
>
> So, armed with this simple insight - we started the project. Do you find
> this simple? simplistic? (or) Are we going wrong somewhere? You guys have
> already achieved a lot in this space. Looking forward to your advice.
>
> Best,
> Sarnath
>
> On Thu, Nov 19, 2015 at 9:11 PM, Luke Han <lu...@gmail.com> wrote:
>
> > Hi Sarnath,
> > You are welcome, I agree with you there are different approaches,
> > Would you mind to share more about your system design and why you
> > choose ES as storage (just curious), is there any public information I
> > could refer to?
> > And would live to have your result and please share any finding
> > which could help to improve this project:)
> >
> > Thanks.
> >
> > Luke
> >
> >
> >
> >
> > Best Regards!
> > ---------------------
> >
> > Luke Han
> >
> > On Thu, Nov 19, 2015 at 10:41 PM, Sarnath <st...@gmail.com> wrote:
> >
> > > Hi Luke,
> > >
> > > My intention is to learn. That's all. As I said there are many ways to
> > > attack a problem. And, big data offers lot of choices..so, at least, to
> > > justify to myself, I would like to run this at scale and report
> findings.
> > > If nothing, it would at least make a good read over tea on Sunday
> > > afternoon.
> > > I will write back to this thread after doing some meaningful test.
> > > Thanks for all your time and your patience.
> > >
> > > Best,
> > > Sarnath
> > >
> >
>
Re: Group by + where clause
Posted by Sarnath <st...@gmail.com>.
Hi Luke,
I will definitely post back here when we make things public.
My choice of ES is based on simple reasoning:
When we build a Cuboid -- we basically build Key-Value pairs. i.e.
1) "Country=US", "State=Texas", "City=Houston", "Quantity=500"
2) "Country=India", "State=Karnataka", "City=Bangalore", "Quantity=1000"
and so on...
That looked to me like an ES document (or) a SOLR document
And, When we search it, We search it like this:
1) Fetch me all documents that have "Country=US", "State=Texas"
2) Fetch me all documents that correspond to "Country=India".
3) Fetch me all documents that correspond to "Country=US"
and so on..
All these are essentially search problems solved by Elastic Search/SOLR and
they are scalable as well..
So, armed with this simple insight - we started the project. Do you find
this simple? simplistic? (or) Are we going wrong somewhere? You guys have
already achieved a lot in this space. Looking forward to your advice.
Best,
Sarnath
On Thu, Nov 19, 2015 at 9:11 PM, Luke Han <lu...@gmail.com> wrote:
> Hi Sarnath,
> You are welcome, I agree with you there are different approaches,
> Would you mind to share more about your system design and why you
> choose ES as storage (just curious), is there any public information I
> could refer to?
> And would live to have your result and please share any finding
> which could help to improve this project:)
>
> Thanks.
>
> Luke
>
>
>
>
> Best Regards!
> ---------------------
>
> Luke Han
>
> On Thu, Nov 19, 2015 at 10:41 PM, Sarnath <st...@gmail.com> wrote:
>
> > Hi Luke,
> >
> > My intention is to learn. That's all. As I said there are many ways to
> > attack a problem. And, big data offers lot of choices..so, at least, to
> > justify to myself, I would like to run this at scale and report findings.
> > If nothing, it would at least make a good read over tea on Sunday
> > afternoon.
> > I will write back to this thread after doing some meaningful test.
> > Thanks for all your time and your patience.
> >
> > Best,
> > Sarnath
> >
>
Re: Group by + where clause
Posted by Luke Han <lu...@gmail.com>.
Hi Sarnath,
You are welcome, I agree with you there are different approaches,
Would you mind to share more about your system design and why you
choose ES as storage (just curious), is there any public information I
could refer to?
And would live to have your result and please share any finding
which could help to improve this project:)
Thanks.
Luke
Best Regards!
---------------------
Luke Han
On Thu, Nov 19, 2015 at 10:41 PM, Sarnath <st...@gmail.com> wrote:
> Hi Luke,
>
> My intention is to learn. That's all. As I said there are many ways to
> attack a problem. And, big data offers lot of choices..so, at least, to
> justify to myself, I would like to run this at scale and report findings.
> If nothing, it would at least make a good read over tea on Sunday
> afternoon.
> I will write back to this thread after doing some meaningful test.
> Thanks for all your time and your patience.
>
> Best,
> Sarnath
>
Re: Group by + where clause
Posted by Sarnath <st...@gmail.com>.
Hi Luke,
My intention is to learn. That's all. As I said there are many ways to
attack a problem. And, big data offers lot of choices..so, at least, to
justify to myself, I would like to run this at scale and report findings.
If nothing, it would at least make a good read over tea on Sunday
afternoon.
I will write back to this thread after doing some meaningful test.
Thanks for all your time and your patience.
Best,
Sarnath
Re: Group by + where clause
Posted by Luke Han <lu...@gmail.com>.
I don't think such benchmark is necessary, it's really hard to say it's
apple to apple case. And as you said, each system has it's pros and
cons, considering your engine is based on Elasticsearch and ours
on HBase, it will (probably) directing the discussion to compare them.
My question to you is what's the purpose you would like to do
such comparison?
With all above discussion, I think you already have information. And if
you would like to propose ES is better than HBase, we are really
welcome contribution to offer alternative storage option for users as
same as we have being ask about Reddis.
It doesn't matter whether a cat is black or white as long as it can catch
mice.
--by Xiaoping Deng.
Thanks.
Best Regards!
---------------------
Luke Han
On Thu, Nov 19, 2015 at 12:37 AM, Sarnath <st...@gmail.com> wrote:
> Hi Seshu,
> I am not asking you guys help to benchmark another system. Bin said that
> the test data was small and invalid for any reasonable comparison. So I am
> merely asking for pointers to any public dataset that can be used.
> Or if you could guys could tell me, what could be desirable properties of a
> synthetic dataset, I would create that in an amazon cluster and benchmark.
> Every system has its strength and weakness. And with big data, there are so
> many ways to solve a same problem. Benchmarking is the best way to
> understand. All of us can learn from such an exercise.
> Best,
> Sarnath
>
Re: Group by + where clause
Posted by Sarnath <st...@gmail.com>.
Hi Seshu,
I am not asking you guys help to benchmark another system. Bin said that
the test data was small and invalid for any reasonable comparison. So I am
merely asking for pointers to any public dataset that can be used.
Or if you could guys could tell me, what could be desirable properties of a
synthetic dataset, I would create that in an amazon cluster and benchmark.
Every system has its strength and weakness. And with big data, there are so
many ways to solve a same problem. Benchmarking is the best way to
understand. All of us can learn from such an exercise.
Best,
Sarnath
Re: Group by + where clause
Posted by "Adunuthula, Seshu" <sa...@ebay.com>.
Sarnath,
I think this thread has reached its level of usefulness and should stop.
If you are interested in looking at the execution path in Kylin and
optimizing it for latency, we would be glad to help you, we would love you
to submit your patches, If you need help from Kylin community to help you
build, test and benchmark another system it is not the best use of
resources and time. Thank you for all your effort in making Kylin a better
product.
Regards
Seshu Adunuthula
On 11/18/15, 1:25 AM, "Sarnath" <st...@gmail.com> wrote:
>Hi,
>
>Is there any public dataset that you would recommend ? That would make for
>an interesting comparison.
>
>Best,
>Sarnath
Re: Group by + where clause
Posted by Sarnath <st...@gmail.com>.
Hi,
Is there any public dataset that you would recommend ? That would make for
an interesting comparison.
Best,
Sarnath
Re: Group by + where clause
Posted by hongbin ma <ma...@apache.org>.
The observed performance gap should have nothing to do with calcite, I
think we're missing the key factors here.
In my opinion, the key factors is in the choice of storage engine. Kylin is
using HBase for now, even though we're opening for other alternatives for
cube storage, we might not easily switch to another(like ES,Kudu) unless
we're really convinced the other one is a better choice.
ES might be good, but the benchmark @Sarnath provided is nowhere near a
practical case and thus can't be convincing.(AFAIK no practical case has
merely two dimensions and 1M cube entries), ES's high performance on tiny
cubes does not lead to the conclusion that it is a better choice than HBase
regarding moderate size cubes. Even though HBase has its burdens and
blames, it has many nice features like coprocessor to help cube scanning.
Still I want to emphasize that we have a open architecture to change
underlying cube storage engine whenever necessary.
On Tue, Nov 17, 2015 at 11:49 AM, Ted Dunning <te...@gmail.com> wrote:
> On Tue, Nov 17, 2015 at 5:16 AM, Julian Hyde <jh...@apache.org> wrote:
>
> > And before you demonize Calcite, Ted, let’s make it clear that we don’t
> > know whether Calcite is to blame here. I actually doubt that it is,
> because
> > AFAIK Kylin does not use Calcite for cuboid selection.
> >
>
> Julian,
>
> I hope my comments were not intended as demonization. I think that for all
> of the current applications of Calcite, there are costs and benefits that
> tip very much toward the benefit side, particular in the context being in
> question.
>
> It is a good idea to measure before attempting to change things. But I
> really don't think that getting the entire parsing and planning time faster
> than about 100 ms needs to be a goal of Calcite. It might be nice, but it
> just isn't needed in the current consumers I know about. So even if
> detailed measurement turns up Calcite taking about that long, I don't think
> that is a priority issue.
>
>
>
>
> >
> > Sarnath’s original remark was
> >
> > > if SQL parsing is CPU intensive, it should not really take 100ms
> > > unless some IO is being performed.
> >
> > SQL parsing absolutely should not take 100ms. It’s sometimes justified
> for
> > the whole query preparation process to take 100ms — if that’s what it
> takes
> > to find a “smart” way to answer the query, and the query would take a
> long
> > time to answer otherwise.
> >
> > But if there’s a performance problem in the query preparation process,
> > let’s log a performance bug, and identify where the time is being spent.
> >
>
--
Regards,
*Bin Mahone | 马洪宾*
Apache Kylin: http://kylin.io
Github: https://github.com/binmahone
Re: Group by + where clause
Posted by Ted Dunning <te...@gmail.com>.
On Tue, Nov 17, 2015 at 5:16 AM, Julian Hyde <jh...@apache.org> wrote:
> And before you demonize Calcite, Ted, let’s make it clear that we don’t
> know whether Calcite is to blame here. I actually doubt that it is, because
> AFAIK Kylin does not use Calcite for cuboid selection.
>
Julian,
I hope my comments were not intended as demonization. I think that for all
of the current applications of Calcite, there are costs and benefits that
tip very much toward the benefit side, particular in the context being in
question.
It is a good idea to measure before attempting to change things. But I
really don't think that getting the entire parsing and planning time faster
than about 100 ms needs to be a goal of Calcite. It might be nice, but it
just isn't needed in the current consumers I know about. So even if
detailed measurement turns up Calcite taking about that long, I don't think
that is a priority issue.
>
> Sarnath’s original remark was
>
> > if SQL parsing is CPU intensive, it should not really take 100ms
> > unless some IO is being performed.
>
> SQL parsing absolutely should not take 100ms. It’s sometimes justified for
> the whole query preparation process to take 100ms — if that’s what it takes
> to find a “smart” way to answer the query, and the query would take a long
> time to answer otherwise.
>
> But if there’s a performance problem in the query preparation process,
> let’s log a performance bug, and identify where the time is being spent.
>
Re: Group by + where clause
Posted by Julian Hyde <jh...@apache.org>.
And before you demonize Calcite, Ted, let’s make it clear that we don’t know whether Calcite is to blame here. I actually doubt that it is, because AFAIK Kylin does not use Calcite for cuboid selection.
Sarnath’s original remark was
> if SQL parsing is CPU intensive, it should not really take 100ms
> unless some IO is being performed.
SQL parsing absolutely should not take 100ms. It’s sometimes justified for the whole query preparation process to take 100ms — if that’s what it takes to find a “smart” way to answer the query, and the query would take a long time to answer otherwise.
But if there’s a performance problem in the query preparation process, let’s log a performance bug, and identify where the time is being spent.
Julian
> On Nov 16, 2015, at 9:19 AM, Ted Dunning <te...@gmail.com> wrote:
>
> On Tue, Nov 17, 2015 at 2:08 AM, Sarnath <st...@gmail.com> wrote:
>
>> Thank you Ted, Luke. I think GLB problem hits when you have not built the
>> cuboid that matches the exact group by statement in the query. That's when
>> one needs to think how to satisfy the query.
>>
>
> Yes. Exactly correct.
>
> But you still pay for carrying the optimizer around because it has a design
> that costs a bit to get started.
Re: 回复： Group by + where clause
Posted by Ted Dunning <te...@gmail.com>.
On Tue, Nov 17, 2015 at 2:08 AM, Sarnath <st...@gmail.com> wrote:
> Thank you Ted, Luke. I think GLB problem hits when you have not built the
> cuboid that matches the exact group by statement in the query. That's when
> one needs to think how to satisfy the query.
>
Yes. Exactly correct.
But you still pay for carrying the optimizer around because it has a design
that costs a bit to get started.
Re: 回复： Group by + where clause
Posted by Sarnath <st...@gmail.com>.
Thank you Ted, Luke. I think GLB problem hits when you have not built the
cuboid that matches the exact group by statement in the query. That's when
one needs to think how to satisfy the query.
I work for HCL Technologies. An MnC services company. We eek out free time
to play around with new ideas..
Cube build is through single MR. Thats where the idea started for us. And
our infra is on VMs. And all VMs get virtual disks from LVM managing 4
physical disks. I.e. IO path does not scale much due to physical
constraints. The build time was more or less like kylin. No surprises there.
Re: 回复： Group by + where clause
Posted by Luke Han <lu...@gmail.com>.
Echo to Ted's comments, second/sub-second latency for OLAP is good enough
since it's for "Human" to interactive with data source. several ms latency
is more like for OLTP or monitoring system purpose.
Beyond talking about query latency, would you like to share your cube build
time also?
BTW, which company and product you are?
Best Regards!
---------------------
Luke Han
On Tue, Nov 17, 2015 at 12:22 AM, Ted Dunning <te...@gmail.com> wrote:
> On Tue, Nov 17, 2015 at 12:59 AM, Sarnath <st...@gmail.com> wrote:
>
> > > > Also, if SQL parsing is CPU intensive, it should not really take
> 100ms
> > > > unless some IO is being performed...
> > > >
> > >
> > > It isn't the parsing. It is the combinatoric explosion in the
> optimizer.
> > >
> >
> > Hmmm... Sorry that went over my head. Calcite optimizer? In the query
> > above, which is a simple group by, what is there to optimize? One simply
> > needs to scan hbase and return back the data.. Pardon my ignorance here.
> I
> > would really like to understand this part. Can you educate me on query
> > optimization and how and what role does that play?
> >
>
> This particular case is quite simple. But if you had specified several
> elements to be grouped by and possibly had some other constraints, then the
> optimizer comes into play.
>
> Moreover, because of the focus on calcite and similar on analytical queries
> where 100ms extra is not a big concern, it was deemed acceptable to have a
> significant query startup time. This would be completely unacceptable in,
> say, an OLTP application, but isn't a big deal if the typical query takes
> seconds or more.
>
> To summarize, you pay a cost for the Calcite optimizer, even for simple
> queries.
>
> ...
> > > The number of similar aggregations also tends to increase the
> complexity
> > of
> > > the query optimization, although there are good guarantees on how this
> > > complexity will grow.
> >
> > Umm...I assume you are not talking about cube build phase rather the
> query
> > phase.. I don't understand how query optimization can depend on the
> number
> > of aggregations present in the cube...my naive thought process is -- just
> > look at what is being grouped by and what metric is being asked for...
> That
> > will tell you how to search and fetch results from hbase... So, I really
> > can't wrap my head around the optimization.
> >
>
> The issue is finding the optimal cuboid(s) to use to compute the query.
>
> This is a greatest lower bound problem on a lattice.
>
Re: 回复： Group by + where clause
Posted by Ted Dunning <te...@gmail.com>.
On Tue, Nov 17, 2015 at 12:59 AM, Sarnath <st...@gmail.com> wrote:
> > > Also, if SQL parsing is CPU intensive, it should not really take 100ms
> > > unless some IO is being performed...
> > >
> >
> > It isn't the parsing. It is the combinatoric explosion in the optimizer.
> >
>
> Hmmm... Sorry that went over my head. Calcite optimizer? In the query
> above, which is a simple group by, what is there to optimize? One simply
> needs to scan hbase and return back the data.. Pardon my ignorance here. I
> would really like to understand this part. Can you educate me on query
> optimization and how and what role does that play?
>
This particular case is quite simple. But if you had specified several
elements to be grouped by and possibly had some other constraints, then the
optimizer comes into play.
Moreover, because of the focus on calcite and similar on analytical queries
where 100ms extra is not a big concern, it was deemed acceptable to have a
significant query startup time. This would be completely unacceptable in,
say, an OLTP application, but isn't a big deal if the typical query takes
seconds or more.
To summarize, you pay a cost for the Calcite optimizer, even for simple
queries.
...
> > The number of similar aggregations also tends to increase the complexity
> of
> > the query optimization, although there are good guarantees on how this
> > complexity will grow.
>
> Umm...I assume you are not talking about cube build phase rather the query
> phase.. I don't understand how query optimization can depend on the number
> of aggregations present in the cube...my naive thought process is -- just
> look at what is being grouped by and what metric is being asked for... That
> will tell you how to search and fetch results from hbase... So, I really
> can't wrap my head around the optimization.
>
The issue is finding the optimal cuboid(s) to use to compute the query.
This is a greatest lower bound problem on a lattice.
Re: 回复： Group by + where clause
Posted by Sarnath <st...@gmail.com>.
> > Also, if SQL parsing is CPU intensive, it should not really take 100ms
> > unless some IO is being performed...
> >
>
> It isn't the parsing. It is the combinatoric explosion in the optimizer.
>
Hmmm... Sorry that went over my head. Calcite optimizer? In the query
above, which is a simple group by, what is there to optimize? One simply
needs to scan hbase and return back the data.. Pardon my ignorance here. I
would really like to understand this part. Can you educate me on query
optimization and how and what role does that play?
> Yes. The issue is that you have aggregations across many combinations of
> variables.
>
> That can mean that the number of rows in the cube datastores can be a
> significant fraction of the size of the original data. In fact, you could
> cause it to be much bigger than the original data (not that such a thing
> would make much sense).
>
Yea, I get this part.. Cardinality of dimensions get multiplied and
technically possible to have a really huge cube....
> The number of similar aggregations also tends to increase the complexity
of
> the query optimization, although there are good guarantees on how this
> complexity will grow.
Umm...I assume you are not talking about cube build phase rather the query
phase.. I don't understand how query optimization can depend on the number
of aggregations present in the cube...my naive thought process is -- just
look at what is being grouped by and what metric is being asked for... That
will tell you how to search and fetch results from hbase... So, I really
can't wrap my head around the optimization.
Best,
Sarnath
Re: 回复： Group by + where clause
Posted by Ted Dunning <te...@gmail.com>.
On Mon, Nov 16, 2015 at 9:00 PM, Sarnath <st...@gmail.com> wrote:
> Also, if SQL parsing is CPU intensive, it should not really take 100ms
> unless some IO is being performed...
>
It isn't the parsing. It is the combinatoric explosion in the optimizer.
> btw....do aggregated data also run into billions ?? How much is the size of
> aggregated data from a billion row table?
>
Yes. The issue is that you have aggregations across many combinations of
variables.
That can mean that the number of rows in the cube datastores can be a
significant fraction of the size of the original data. In fact, you could
cause it to be much bigger than the original data (not that such a thing
would make much sense).
The number of similar aggregations also tends to increase the complexity of
the query optimization, although there are good guarantees on how this
complexity will grow.
Re: 回复： Group by + where clause
Posted by Sarnath <st...@gmail.com>.
I tried with a small table of 10mln entries... with 2 dimensions and
1metric (product, branch, qty). Each dimension has 1000 unique values...
Thus 1000,000 (1 mln) combinations are possible. And that's what is being
computed as a cube.
And then, I query the cube for a particular prod-id (select.product,
branch, sum(qty) from table where product=pid group by product, branch)
The same for a particular branch in the where clause....
Also, if SQL parsing is CPU intensive, it should not really take 100ms
unless some IO is being performed...
btw....do aggregated data also run into billions ?? How much is the size of
aggregated data from a billion row table?