You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Thomas D'Silva <td...@twilio.com.INVALID> on 2022/01/26 18:53:46 UTC
MaterializedViewRule is not applied for queries on aggregate views that truncate timestamp
Hi,
The existing MaterializedViewRule is not applied when the materialized view
does not have a view predicate but the query contains a predicate. For eg
for the following materialized view
SELECT eventid, floor(ts to minute), count(*) as cnt
FROM events
GROUP BY eventid, floor(ts TO minute)
If we have the following query the view is not chosen in the final plan.
SELECT floor(ts to minute), count(*)
FROM events
WHERE ts >= timestamp'2018-01-01 00:01:00' AND ts < timestamp'2018-01-01
00:05:00'
GROUP BY eventid, floor(ts TO minute)
If I add the predicate "ts > timestamp'2018-01-01 00:01:00' AND ts <
timestamp'2018-01-01 00:05:00'" to the materialized view then the
materialized view is chosen in the final plan. Would it be possible to
modify the MaterializedViewRule to automatically add a view predicate based
on the query when the materialized view being considerent aggregates over a
column that contains a floor()? This predicate would also need to be
applied to the rewritten view. I would like to file a JIRA and work on this
improvement if this makes sense.
Thank you,
Thomas
Re: MaterializedViewRule is not applied for queries on aggregate views that truncate timestamp
Posted by Julian Hyde <jh...@gmail.com>.
These kinds of roll ups are very common. They have simple mathematical properties. There is a simplifying abstraction to be found here. Please find it and use it.
Julian
> On Feb 2, 2022, at 7:47 PM, Thomas D'Silva <td...@twilio.com.invalid> wrote:
>
> Hi Stamatis,
>
> Thanks for your response. Your suggestion of having use case specific rules
> that translate time ranges that align on the rollup of the materialized
> view makes sense.
> However if the time range doesn't align for eg. if we have a view that
> rolls up to the minute and the query predicate is "WHERE ts >=
> timestamp'2018-01-01 00:01:30' AND ts < timestamp'2018-01-01 00:05:00'" we
> wouldn't be able to rewrite it to apply the FLOOR() on the left side of the
> comparators.
> In this case I was trying to use the MaterializedViewRule's union rewriting
> feature to query the view for the range "ts >= timestamp'2018-01-01
> 00:02:00' AND ts < timestamp'2018-01-01 00:05:00'" and query the table for
> the range "ts >= timestamp'2018-01-01 00:01:30' AND ts <
> timestamp'2018-01-01 00:02:00'". I would appreciate any suggestions on how
> to do this.
>
> Thank you,
> Thomas
>
>
>> On Wed, Feb 2, 2022 at 3:28 AM Stamatis Zampetakis <za...@gmail.com>
>> wrote:
>>
>> Hi Thomas,
>>
>> I haven't looked into the implementation details of the rule but I assume
>> the rewriting does not kick in because there is no easy way to relate the
>> "ts" column that is used in the query with the "floor(ts to minute)" column
>> that is materialized.
>>
>> I assume that if your query is/was similar to the one below you would get a
>> rewriting.
>>
>> SELECT floor(ts to minute), count(*)
>> FROM events
>> WHERE floor(ts to minute) >= timestamp'2018-01-01 00:01:00' AND floor(ts
>> to minute) < timestamp'2018-01-01 00:05:00'
>> GROUP BY eventid, floor(ts TO minute)
>>
>> So far I don't see a limitation/problem with the MaterializedViewRule but
>> rather use-case specific rules you imply they always hold.
>>
>> If I understand correctly you say that the following are kind of
>> equivalent:
>>
>> WHERE ts >= timestamp'2018-01-01 00:01:00' AND ts <
>> timestamp'2018-01-01 00:05:00'
>> WHERE floor(ts to minute) >= timestamp'2018-01-01 00:01:00' AND floor(ts to
>> minute) < timestamp'2018-01-01 00:05:00'
>> WHERE floor(ts to minute) >= floor(timestamp'2018-01-01 00:01:00' to
>> minute) AND floor(ts to minute) < floor(timestamp'2018-01-01 00:05:00' to
>> minute)
>>
>> and I think they are based on the given timestamp literals.
>>
>> Maybe instead of changing the rules performing the view rewriting what you
>> should do is introduce other use-case specific rules that can be applied to
>> the input queries and bring them to the desired form.
>>
>> Best,
>> Stamatis
>>
>> On Fri, Jan 28, 2022 at 10:12 PM Thomas D'Silva <tdsilva@twilio.com.invalid
>>>
>> wrote:
>>
>>> Xurenhe,
>>>
>>> Thank you for the detailed response and pointers. The use case you
>>> describe for OLAP streaming queries is similar to what our team is trying
>>> to accomplish.
>>> We use calcite to query data stored in kudu [1]. We use kafka streams to
>>> maintain materialized views that contain pre-aggregated data that rolls
>>> data up to the daily or hourly granularity. One of the problems we face
>> is
>>> when a user submits a query where the time range does not align to the
>> day
>>> or hour boundary. In this case we are unable to use the materialized
>> views
>>> and have to query the raw event which is not performant because the table
>>> contains many more rows compared to the view.
>>> The reason I wanted to modify the MaterializedViewRule is to use the
>> "Union
>>> rewriting with aggregate" ability [2] this would allow us to use both the
>>> view and the table to answer such queries in the most efficient manner. I
>>> looked at the code in SubstitutionVisitor but I don't think there are
>>> existing rules there that do union rewriting with predicates.
>>>
>>> The following is an example of the query we are trying to optimize. For
>> the
>>> materialized view
>>> SELECT eventid, floor(ts to minute), count(*) as cnt
>>> FROM events
>>> GROUP BY eventid, floor(ts TO minute)
>>>
>>> and query
>>> SELECT floor(ts to minute), count(*)
>>> FROM events
>>> WHERE ts >= timestamp'2018-01-01 00:01:30' AND ts <
>> timestamp'2018-01-01
>>> 00:05:00'
>>> GROUP BY eventid, floor(ts TO minute)
>>>
>>> We would like to generate a plan that queries the table for the time
>> range
>>> [2018-01-01 00:01:30, 2018-01-01 00:02:00) and view for the time range
>>> [2018-01-01 00:02:-0, 2018-01-01 00:05:00) similar to
>>>
>>> EnumerableCalc(expr#0..2=[{inputs}], EXPR$0=[$t1], EXPR$1=[$t2])
>>> EnumerableAggregate(group=[{0, 1}], EXPR$1=[$SUM0($2)])
>>> EnumerableUnion(all=[true])
>>> EnumerableAggregate(group=[{0, 1}], EXPR$1=[COUNT()])
>>> EnumerableCalc(expr#0..1=[{inputs}], expr#2=[FLAG(MINUTE)],
>>> expr#3=[FLOOR($t1, $t2)], expr#4=[Sarg[[2018-01-01 00:01:30..2018-01-01
>>> 00:02:00)]], expr#5=[SEARCH($t1, $t4)], eventid=[$t0], $f1=[$t3],
>>> $condition=[$t5])
>>> EnumerableTableScan(table=[[hr, events]])
>>> EnumerableCalc(expr#0..2=[{inputs}], expr#3=[Sarg[[2018-01-01
>>> 00:02:00..2018-01-01 00:05:00)]], expr#4=[SEARCH($t1, $t3)],
>>> proj#0..2=[{exprs}], $condition=[$t4])
>>> EnumerableTableScan(table=[[hr, MV0]])
>>>
>>> Thank you,
>>> Thomas
>>>
>>> [1]
>> https://urldefense.com/v3/__https://github.com/twilio/calcite-kudu__;!!NCc8flgU!KhDSBwwmEP1lp8FVI18hL250_jEF94FehJiaGVmrZIs3Q79JqY5_37EGNyWrpN8$
>>> [2]
>>>
>>>
>> https://urldefense.com/v3/__https://calcite.apache.org/docs/materialized_views.html*union-rewriting-with-aggregate__;Iw!!NCc8flgU!KhDSBwwmEP1lp8FVI18hL250_jEF94FehJiaGVmrZIs3Q79JqY5_37EGqqSl83w$
>>>
>>>> On Fri, Jan 28, 2022 at 12:20 AM 徐仁和 <xu...@gmail.com> wrote:
>>>
>>>> It’s useful in OLAP.
>>>> Time ’s prediction bottom `Aggregate` could be pulled-up, because of
>>> Time’s
>>>> granularity in Aggregate’s group.
>>>> By the way, the query can be rewritten by mv, when the query's
>>> granularity
>>>> is coarser than mv’s.
>>>> eg:
>>>> -- mv:
>>>> SELECT eventid, floor(ts TO minute), count(*) as cnt
>>>> FROM events
>>>> GROUP BY eventid, floor(ts TO minute)
>>>> -- query:
>>>> SELECT floor(ts TO hour), count(*)
>>>> FROM events
>>>> GROUP BY eventid, floor(ts TO hour)
>>>>
>>>> In the past, I tried to use stream-sql’s expression to solve it.
>>>> This document is used to describe this problem, Here is a rough
>> document.
>>>> doc:
>>>>
>>>>
>>>
>> https://urldefense.com/v3/__https://docs.google.com/document/d/1LtSgxhwvnpk2uAXFQiMSX-aQjnkJ6KLrXwXJX-VbsbE/edit*heading=h.ww6moavc8lkj__;Iw!!NCc8flgU!Lx31-Z0cEnGqJQwX-7zk1Kk3NvAaCblUwfyTg4jDUg0Y69SULj4jHJKPGC2W58A$
>>>> My team has defined more `UnifyRule`s to solve it,
>>>> because SubstitutionVisitor has high-scalability.
>>>>
>>>>
>>>> Xurenhe
>>>>
>>>> Thomas D'Silva <td...@twilio.com.invalid> 于2022年1月27日周四 02:54写道：
>>>>
>>>>> Hi,
>>>>>
>>>>> The existing MaterializedViewRule is not applied when the
>> materialized
>>>> view
>>>>> does not have a view predicate but the query contains a predicate.
>> For
>>> eg
>>>>> for the following materialized view
>>>>>
>>>>> SELECT eventid, floor(ts to minute), count(*) as cnt
>>>>> FROM events
>>>>> GROUP BY eventid, floor(ts TO minute)
>>>>>
>>>>> If we have the following query the view is not chosen in the final
>>> plan.
>>>>>
>>>>> SELECT floor(ts to minute), count(*)
>>>>> FROM events
>>>>> WHERE ts >= timestamp'2018-01-01 00:01:00' AND ts <
>>>> timestamp'2018-01-01
>>>>> 00:05:00'
>>>>> GROUP BY eventid, floor(ts TO minute)
>>>>>
>>>>> If I add the predicate "ts > timestamp'2018-01-01 00:01:00' AND ts <
>>>>> timestamp'2018-01-01 00:05:00'" to the materialized view then the
>>>>> materialized view is chosen in the final plan. Would it be possible
>> to
>>>>> modify the MaterializedViewRule to automatically add a view predicate
>>>> based
>>>>> on the query when the materialized view being considerent aggregates
>>>> over a
>>>>> column that contains a floor()? This predicate would also need to be
>>>>> applied to the rewritten view. I would like to file a JIRA and work
>> on
>>>> this
>>>>> improvement if this makes sense.
>>>>>
>>>>> Thank you,
>>>>> Thomas
>>>>>
>>>>
>>>
>>
Re: MaterializedViewRule is not applied for queries on aggregate views that truncate timestamp
Posted by Thomas D'Silva <td...@twilio.com.INVALID>.
Hi Stamatis,
Thanks for your response. Your suggestion of having use case specific rules
that translate time ranges that align on the rollup of the materialized
view makes sense.
However if the time range doesn't align for eg. if we have a view that
rolls up to the minute and the query predicate is "WHERE ts >=
timestamp'2018-01-01 00:01:30' AND ts < timestamp'2018-01-01 00:05:00'" we
wouldn't be able to rewrite it to apply the FLOOR() on the left side of the
comparators.
In this case I was trying to use the MaterializedViewRule's union rewriting
feature to query the view for the range "ts >= timestamp'2018-01-01
00:02:00' AND ts < timestamp'2018-01-01 00:05:00'" and query the table for
the range "ts >= timestamp'2018-01-01 00:01:30' AND ts <
timestamp'2018-01-01 00:02:00'". I would appreciate any suggestions on how
to do this.
Thank you,
Thomas
On Wed, Feb 2, 2022 at 3:28 AM Stamatis Zampetakis <za...@gmail.com>
wrote:
> Hi Thomas,
>
> I haven't looked into the implementation details of the rule but I assume
> the rewriting does not kick in because there is no easy way to relate the
> "ts" column that is used in the query with the "floor(ts to minute)" column
> that is materialized.
>
> I assume that if your query is/was similar to the one below you would get a
> rewriting.
>
> SELECT floor(ts to minute), count(*)
> FROM events
> WHERE floor(ts to minute) >= timestamp'2018-01-01 00:01:00' AND floor(ts
> to minute) < timestamp'2018-01-01 00:05:00'
> GROUP BY eventid, floor(ts TO minute)
>
> So far I don't see a limitation/problem with the MaterializedViewRule but
> rather use-case specific rules you imply they always hold.
>
> If I understand correctly you say that the following are kind of
> equivalent:
>
> WHERE ts >= timestamp'2018-01-01 00:01:00' AND ts <
> timestamp'2018-01-01 00:05:00'
> WHERE floor(ts to minute) >= timestamp'2018-01-01 00:01:00' AND floor(ts to
> minute) < timestamp'2018-01-01 00:05:00'
> WHERE floor(ts to minute) >= floor(timestamp'2018-01-01 00:01:00' to
> minute) AND floor(ts to minute) < floor(timestamp'2018-01-01 00:05:00' to
> minute)
>
> and I think they are based on the given timestamp literals.
>
> Maybe instead of changing the rules performing the view rewriting what you
> should do is introduce other use-case specific rules that can be applied to
> the input queries and bring them to the desired form.
>
> Best,
> Stamatis
>
> On Fri, Jan 28, 2022 at 10:12 PM Thomas D'Silva <tdsilva@twilio.com.invalid
> >
> wrote:
>
> > Xurenhe,
> >
> > Thank you for the detailed response and pointers. The use case you
> > describe for OLAP streaming queries is similar to what our team is trying
> > to accomplish.
> > We use calcite to query data stored in kudu [1]. We use kafka streams to
> > maintain materialized views that contain pre-aggregated data that rolls
> > data up to the daily or hourly granularity. One of the problems we face
> is
> > when a user submits a query where the time range does not align to the
> day
> > or hour boundary. In this case we are unable to use the materialized
> views
> > and have to query the raw event which is not performant because the table
> > contains many more rows compared to the view.
> > The reason I wanted to modify the MaterializedViewRule is to use the
> "Union
> > rewriting with aggregate" ability [2] this would allow us to use both the
> > view and the table to answer such queries in the most efficient manner. I
> > looked at the code in SubstitutionVisitor but I don't think there are
> > existing rules there that do union rewriting with predicates.
> >
> > The following is an example of the query we are trying to optimize. For
> the
> > materialized view
> > SELECT eventid, floor(ts to minute), count(*) as cnt
> > FROM events
> > GROUP BY eventid, floor(ts TO minute)
> >
> > and query
> > SELECT floor(ts to minute), count(*)
> > FROM events
> > WHERE ts >= timestamp'2018-01-01 00:01:30' AND ts <
> timestamp'2018-01-01
> > 00:05:00'
> > GROUP BY eventid, floor(ts TO minute)
> >
> > We would like to generate a plan that queries the table for the time
> range
> > [2018-01-01 00:01:30, 2018-01-01 00:02:00) and view for the time range
> > [2018-01-01 00:02:-0, 2018-01-01 00:05:00) similar to
> >
> > EnumerableCalc(expr#0..2=[{inputs}], EXPR$0=[$t1], EXPR$1=[$t2])
> > EnumerableAggregate(group=[{0, 1}], EXPR$1=[$SUM0($2)])
> > EnumerableUnion(all=[true])
> > EnumerableAggregate(group=[{0, 1}], EXPR$1=[COUNT()])
> > EnumerableCalc(expr#0..1=[{inputs}], expr#2=[FLAG(MINUTE)],
> > expr#3=[FLOOR($t1, $t2)], expr#4=[Sarg[[2018-01-01 00:01:30..2018-01-01
> > 00:02:00)]], expr#5=[SEARCH($t1, $t4)], eventid=[$t0], $f1=[$t3],
> > $condition=[$t5])
> > EnumerableTableScan(table=[[hr, events]])
> > EnumerableCalc(expr#0..2=[{inputs}], expr#3=[Sarg[[2018-01-01
> > 00:02:00..2018-01-01 00:05:00)]], expr#4=[SEARCH($t1, $t3)],
> > proj#0..2=[{exprs}], $condition=[$t4])
> > EnumerableTableScan(table=[[hr, MV0]])
> >
> > Thank you,
> > Thomas
> >
> > [1]
> https://urldefense.com/v3/__https://github.com/twilio/calcite-kudu__;!!NCc8flgU!KhDSBwwmEP1lp8FVI18hL250_jEF94FehJiaGVmrZIs3Q79JqY5_37EGNyWrpN8$
> > [2]
> >
> >
> https://urldefense.com/v3/__https://calcite.apache.org/docs/materialized_views.html*union-rewriting-with-aggregate__;Iw!!NCc8flgU!KhDSBwwmEP1lp8FVI18hL250_jEF94FehJiaGVmrZIs3Q79JqY5_37EGqqSl83w$
> >
> > On Fri, Jan 28, 2022 at 12:20 AM 徐仁和 <xu...@gmail.com> wrote:
> >
> > > It’s useful in OLAP.
> > > Time ’s prediction bottom `Aggregate` could be pulled-up, because of
> > Time’s
> > > granularity in Aggregate’s group.
> > > By the way, the query can be rewritten by mv, when the query's
> > granularity
> > > is coarser than mv’s.
> > > eg:
> > > -- mv:
> > > SELECT eventid, floor(ts TO minute), count(*) as cnt
> > > FROM events
> > > GROUP BY eventid, floor(ts TO minute)
> > > -- query:
> > > SELECT floor(ts TO hour), count(*)
> > > FROM events
> > > GROUP BY eventid, floor(ts TO hour)
> > >
> > > In the past, I tried to use stream-sql’s expression to solve it.
> > > This document is used to describe this problem, Here is a rough
> document.
> > > doc:
> > >
> > >
> >
> https://urldefense.com/v3/__https://docs.google.com/document/d/1LtSgxhwvnpk2uAXFQiMSX-aQjnkJ6KLrXwXJX-VbsbE/edit*heading=h.ww6moavc8lkj__;Iw!!NCc8flgU!Lx31-Z0cEnGqJQwX-7zk1Kk3NvAaCblUwfyTg4jDUg0Y69SULj4jHJKPGC2W58A$
> > > My team has defined more `UnifyRule`s to solve it,
> > > because SubstitutionVisitor has high-scalability.
> > >
> > >
> > > Xurenhe
> > >
> > > Thomas D'Silva <td...@twilio.com.invalid> 于2022年1月27日周四 02:54写道：
> > >
> > > > Hi,
> > > >
> > > > The existing MaterializedViewRule is not applied when the
> materialized
> > > view
> > > > does not have a view predicate but the query contains a predicate.
> For
> > eg
> > > > for the following materialized view
> > > >
> > > > SELECT eventid, floor(ts to minute), count(*) as cnt
> > > > FROM events
> > > > GROUP BY eventid, floor(ts TO minute)
> > > >
> > > > If we have the following query the view is not chosen in the final
> > plan.
> > > >
> > > > SELECT floor(ts to minute), count(*)
> > > > FROM events
> > > > WHERE ts >= timestamp'2018-01-01 00:01:00' AND ts <
> > > timestamp'2018-01-01
> > > > 00:05:00'
> > > > GROUP BY eventid, floor(ts TO minute)
> > > >
> > > > If I add the predicate "ts > timestamp'2018-01-01 00:01:00' AND ts <
> > > > timestamp'2018-01-01 00:05:00'" to the materialized view then the
> > > > materialized view is chosen in the final plan. Would it be possible
> to
> > > > modify the MaterializedViewRule to automatically add a view predicate
> > > based
> > > > on the query when the materialized view being considerent aggregates
> > > over a
> > > > column that contains a floor()? This predicate would also need to be
> > > > applied to the rewritten view. I would like to file a JIRA and work
> on
> > > this
> > > > improvement if this makes sense.
> > > >
> > > > Thank you,
> > > > Thomas
> > > >
> > >
> >
>
Re: MaterializedViewRule is not applied for queries on aggregate views that truncate timestamp
Posted by Stamatis Zampetakis <za...@gmail.com>.
Hi Thomas,
I haven't looked into the implementation details of the rule but I assume
the rewriting does not kick in because there is no easy way to relate the
"ts" column that is used in the query with the "floor(ts to minute)" column
that is materialized.
I assume that if your query is/was similar to the one below you would get a
rewriting.
SELECT floor(ts to minute), count(*)
FROM events
WHERE floor(ts to minute) >= timestamp'2018-01-01 00:01:00' AND floor(ts
to minute) < timestamp'2018-01-01 00:05:00'
GROUP BY eventid, floor(ts TO minute)
So far I don't see a limitation/problem with the MaterializedViewRule but
rather use-case specific rules you imply they always hold.
If I understand correctly you say that the following are kind of equivalent:
WHERE ts >= timestamp'2018-01-01 00:01:00' AND ts <
timestamp'2018-01-01 00:05:00'
WHERE floor(ts to minute) >= timestamp'2018-01-01 00:01:00' AND floor(ts to
minute) < timestamp'2018-01-01 00:05:00'
WHERE floor(ts to minute) >= floor(timestamp'2018-01-01 00:01:00' to
minute) AND floor(ts to minute) < floor(timestamp'2018-01-01 00:05:00' to
minute)
and I think they are based on the given timestamp literals.
Maybe instead of changing the rules performing the view rewriting what you
should do is introduce other use-case specific rules that can be applied to
the input queries and bring them to the desired form.
Best,
Stamatis
On Fri, Jan 28, 2022 at 10:12 PM Thomas D'Silva <td...@twilio.com.invalid>
wrote:
> Xurenhe,
>
> Thank you for the detailed response and pointers. The use case you
> describe for OLAP streaming queries is similar to what our team is trying
> to accomplish.
> We use calcite to query data stored in kudu [1]. We use kafka streams to
> maintain materialized views that contain pre-aggregated data that rolls
> data up to the daily or hourly granularity. One of the problems we face is
> when a user submits a query where the time range does not align to the day
> or hour boundary. In this case we are unable to use the materialized views
> and have to query the raw event which is not performant because the table
> contains many more rows compared to the view.
> The reason I wanted to modify the MaterializedViewRule is to use the "Union
> rewriting with aggregate" ability [2] this would allow us to use both the
> view and the table to answer such queries in the most efficient manner. I
> looked at the code in SubstitutionVisitor but I don't think there are
> existing rules there that do union rewriting with predicates.
>
> The following is an example of the query we are trying to optimize. For the
> materialized view
> SELECT eventid, floor(ts to minute), count(*) as cnt
> FROM events
> GROUP BY eventid, floor(ts TO minute)
>
> and query
> SELECT floor(ts to minute), count(*)
> FROM events
> WHERE ts >= timestamp'2018-01-01 00:01:30' AND ts < timestamp'2018-01-01
> 00:05:00'
> GROUP BY eventid, floor(ts TO minute)
>
> We would like to generate a plan that queries the table for the time range
> [2018-01-01 00:01:30, 2018-01-01 00:02:00) and view for the time range
> [2018-01-01 00:02:-0, 2018-01-01 00:05:00) similar to
>
> EnumerableCalc(expr#0..2=[{inputs}], EXPR$0=[$t1], EXPR$1=[$t2])
> EnumerableAggregate(group=[{0, 1}], EXPR$1=[$SUM0($2)])
> EnumerableUnion(all=[true])
> EnumerableAggregate(group=[{0, 1}], EXPR$1=[COUNT()])
> EnumerableCalc(expr#0..1=[{inputs}], expr#2=[FLAG(MINUTE)],
> expr#3=[FLOOR($t1, $t2)], expr#4=[Sarg[[2018-01-01 00:01:30..2018-01-01
> 00:02:00)]], expr#5=[SEARCH($t1, $t4)], eventid=[$t0], $f1=[$t3],
> $condition=[$t5])
> EnumerableTableScan(table=[[hr, events]])
> EnumerableCalc(expr#0..2=[{inputs}], expr#3=[Sarg[[2018-01-01
> 00:02:00..2018-01-01 00:05:00)]], expr#4=[SEARCH($t1, $t3)],
> proj#0..2=[{exprs}], $condition=[$t4])
> EnumerableTableScan(table=[[hr, MV0]])
>
> Thank you,
> Thomas
>
> [1] https://github.com/twilio/calcite-kudu
> [2]
>
> https://calcite.apache.org/docs/materialized_views.html#union-rewriting-with-aggregate
>
> On Fri, Jan 28, 2022 at 12:20 AM 徐仁和 <xu...@gmail.com> wrote:
>
> > It’s useful in OLAP.
> > Time ’s prediction bottom `Aggregate` could be pulled-up, because of
> Time’s
> > granularity in Aggregate’s group.
> > By the way, the query can be rewritten by mv, when the query's
> granularity
> > is coarser than mv’s.
> > eg:
> > -- mv:
> > SELECT eventid, floor(ts TO minute), count(*) as cnt
> > FROM events
> > GROUP BY eventid, floor(ts TO minute)
> > -- query:
> > SELECT floor(ts TO hour), count(*)
> > FROM events
> > GROUP BY eventid, floor(ts TO hour)
> >
> > In the past, I tried to use stream-sql’s expression to solve it.
> > This document is used to describe this problem, Here is a rough document.
> > doc:
> >
> >
> https://urldefense.com/v3/__https://docs.google.com/document/d/1LtSgxhwvnpk2uAXFQiMSX-aQjnkJ6KLrXwXJX-VbsbE/edit*heading=h.ww6moavc8lkj__;Iw!!NCc8flgU!Lx31-Z0cEnGqJQwX-7zk1Kk3NvAaCblUwfyTg4jDUg0Y69SULj4jHJKPGC2W58A$
> > My team has defined more `UnifyRule`s to solve it,
> > because SubstitutionVisitor has high-scalability.
> >
> >
> > Xurenhe
> >
> > Thomas D'Silva <td...@twilio.com.invalid> 于2022年1月27日周四 02:54写道：
> >
> > > Hi,
> > >
> > > The existing MaterializedViewRule is not applied when the materialized
> > view
> > > does not have a view predicate but the query contains a predicate. For
> eg
> > > for the following materialized view
> > >
> > > SELECT eventid, floor(ts to minute), count(*) as cnt
> > > FROM events
> > > GROUP BY eventid, floor(ts TO minute)
> > >
> > > If we have the following query the view is not chosen in the final
> plan.
> > >
> > > SELECT floor(ts to minute), count(*)
> > > FROM events
> > > WHERE ts >= timestamp'2018-01-01 00:01:00' AND ts <
> > timestamp'2018-01-01
> > > 00:05:00'
> > > GROUP BY eventid, floor(ts TO minute)
> > >
> > > If I add the predicate "ts > timestamp'2018-01-01 00:01:00' AND ts <
> > > timestamp'2018-01-01 00:05:00'" to the materialized view then the
> > > materialized view is chosen in the final plan. Would it be possible to
> > > modify the MaterializedViewRule to automatically add a view predicate
> > based
> > > on the query when the materialized view being considerent aggregates
> > over a
> > > column that contains a floor()? This predicate would also need to be
> > > applied to the rewritten view. I would like to file a JIRA and work on
> > this
> > > improvement if this makes sense.
> > >
> > > Thank you,
> > > Thomas
> > >
> >
>
Re: MaterializedViewRule is not applied for queries on aggregate views that truncate timestamp
Posted by Thomas D'Silva <td...@twilio.com.INVALID>.
Xurenhe,
Thank you for the detailed response and pointers. The use case you
describe for OLAP streaming queries is similar to what our team is trying
to accomplish.
We use calcite to query data stored in kudu [1]. We use kafka streams to
maintain materialized views that contain pre-aggregated data that rolls
data up to the daily or hourly granularity. One of the problems we face is
when a user submits a query where the time range does not align to the day
or hour boundary. In this case we are unable to use the materialized views
and have to query the raw event which is not performant because the table
contains many more rows compared to the view.
The reason I wanted to modify the MaterializedViewRule is to use the "Union
rewriting with aggregate" ability [2] this would allow us to use both the
view and the table to answer such queries in the most efficient manner. I
looked at the code in SubstitutionVisitor but I don't think there are
existing rules there that do union rewriting with predicates.
The following is an example of the query we are trying to optimize. For the
materialized view
SELECT eventid, floor(ts to minute), count(*) as cnt
FROM events
GROUP BY eventid, floor(ts TO minute)
and query
SELECT floor(ts to minute), count(*)
FROM events
WHERE ts >= timestamp'2018-01-01 00:01:30' AND ts < timestamp'2018-01-01
00:05:00'
GROUP BY eventid, floor(ts TO minute)
We would like to generate a plan that queries the table for the time range
[2018-01-01 00:01:30, 2018-01-01 00:02:00) and view for the time range
[2018-01-01 00:02:-0, 2018-01-01 00:05:00) similar to
EnumerableCalc(expr#0..2=[{inputs}], EXPR$0=[$t1], EXPR$1=[$t2])
EnumerableAggregate(group=[{0, 1}], EXPR$1=[$SUM0($2)])
EnumerableUnion(all=[true])
EnumerableAggregate(group=[{0, 1}], EXPR$1=[COUNT()])
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[FLAG(MINUTE)],
expr#3=[FLOOR($t1, $t2)], expr#4=[Sarg[[2018-01-01 00:01:30..2018-01-01
00:02:00)]], expr#5=[SEARCH($t1, $t4)], eventid=[$t0], $f1=[$t3],
$condition=[$t5])
EnumerableTableScan(table=[[hr, events]])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[Sarg[[2018-01-01
00:02:00..2018-01-01 00:05:00)]], expr#4=[SEARCH($t1, $t3)],
proj#0..2=[{exprs}], $condition=[$t4])
EnumerableTableScan(table=[[hr, MV0]])
Thank you,
Thomas
[1] https://github.com/twilio/calcite-kudu
[2]
https://calcite.apache.org/docs/materialized_views.html#union-rewriting-with-aggregate
On Fri, Jan 28, 2022 at 12:20 AM 徐仁和 <xu...@gmail.com> wrote:
> It’s useful in OLAP.
> Time ’s prediction bottom `Aggregate` could be pulled-up, because of Time’s
> granularity in Aggregate’s group.
> By the way, the query can be rewritten by mv, when the query's granularity
> is coarser than mv’s.
> eg:
> -- mv:
> SELECT eventid, floor(ts TO minute), count(*) as cnt
> FROM events
> GROUP BY eventid, floor(ts TO minute)
> -- query:
> SELECT floor(ts TO hour), count(*)
> FROM events
> GROUP BY eventid, floor(ts TO hour)
>
> In the past, I tried to use stream-sql’s expression to solve it.
> This document is used to describe this problem, Here is a rough document.
> doc:
>
> https://urldefense.com/v3/__https://docs.google.com/document/d/1LtSgxhwvnpk2uAXFQiMSX-aQjnkJ6KLrXwXJX-VbsbE/edit*heading=h.ww6moavc8lkj__;Iw!!NCc8flgU!Lx31-Z0cEnGqJQwX-7zk1Kk3NvAaCblUwfyTg4jDUg0Y69SULj4jHJKPGC2W58A$
> My team has defined more `UnifyRule`s to solve it,
> because SubstitutionVisitor has high-scalability.
>
>
> Xurenhe
>
> Thomas D'Silva <td...@twilio.com.invalid> 于2022年1月27日周四 02:54写道：
>
> > Hi,
> >
> > The existing MaterializedViewRule is not applied when the materialized
> view
> > does not have a view predicate but the query contains a predicate. For eg
> > for the following materialized view
> >
> > SELECT eventid, floor(ts to minute), count(*) as cnt
> > FROM events
> > GROUP BY eventid, floor(ts TO minute)
> >
> > If we have the following query the view is not chosen in the final plan.
> >
> > SELECT floor(ts to minute), count(*)
> > FROM events
> > WHERE ts >= timestamp'2018-01-01 00:01:00' AND ts <
> timestamp'2018-01-01
> > 00:05:00'
> > GROUP BY eventid, floor(ts TO minute)
> >
> > If I add the predicate "ts > timestamp'2018-01-01 00:01:00' AND ts <
> > timestamp'2018-01-01 00:05:00'" to the materialized view then the
> > materialized view is chosen in the final plan. Would it be possible to
> > modify the MaterializedViewRule to automatically add a view predicate
> based
> > on the query when the materialized view being considerent aggregates
> over a
> > column that contains a floor()? This predicate would also need to be
> > applied to the rewritten view. I would like to file a JIRA and work on
> this
> > improvement if this makes sense.
> >
> > Thank you,
> > Thomas
> >
>
Re: MaterializedViewRule is not applied for queries on aggregate views that truncate timestamp
Posted by 徐仁和 <xu...@gmail.com>.
It’s useful in OLAP.
Time ’s prediction bottom `Aggregate` could be pulled-up, because of Time’s
granularity in Aggregate’s group.
By the way, the query can be rewritten by mv, when the query's granularity
is coarser than mv’s.
eg:
-- mv:
SELECT eventid, floor(ts TO minute), count(*) as cnt
FROM events
GROUP BY eventid, floor(ts TO minute)
-- query:
SELECT floor(ts TO hour), count(*)
FROM events
GROUP BY eventid, floor(ts TO hour)
In the past, I tried to use stream-sql’s expression to solve it.
This document is used to describe this problem, Here is a rough document.
doc:
https://docs.google.com/document/d/1LtSgxhwvnpk2uAXFQiMSX-aQjnkJ6KLrXwXJX-VbsbE/edit#heading=h.ww6moavc8lkj
My team has defined more `UnifyRule`s to solve it,
because SubstitutionVisitor has high-scalability.
Xurenhe
Thomas D'Silva <td...@twilio.com.invalid> 于2022年1月27日周四 02:54写道：
> Hi,
>
> The existing MaterializedViewRule is not applied when the materialized view
> does not have a view predicate but the query contains a predicate. For eg
> for the following materialized view
>
> SELECT eventid, floor(ts to minute), count(*) as cnt
> FROM events
> GROUP BY eventid, floor(ts TO minute)
>
> If we have the following query the view is not chosen in the final plan.
>
> SELECT floor(ts to minute), count(*)
> FROM events
> WHERE ts >= timestamp'2018-01-01 00:01:00' AND ts < timestamp'2018-01-01
> 00:05:00'
> GROUP BY eventid, floor(ts TO minute)
>
> If I add the predicate "ts > timestamp'2018-01-01 00:01:00' AND ts <
> timestamp'2018-01-01 00:05:00'" to the materialized view then the
> materialized view is chosen in the final plan. Would it be possible to
> modify the MaterializedViewRule to automatically add a view predicate based
> on the query when the materialized view being considerent aggregates over a
> column that contains a floor()? This predicate would also need to be
> applied to the rewritten view. I would like to file a JIRA and work on this
> improvement if this makes sense.
>
> Thank you,
> Thomas
>
Re: MaterializedViewRule is not applied for queries on aggregate views that truncate timestamp
Posted by Thomas D'Silva <td...@twilio.com.INVALID>.
Stamatis,
For the materialized view and query that I had mentioned in the earlier
email, I got the following final plan that used the table :
EnumerableCalc(expr#0..2=[{inputs}], EXPR$0=[$t1], EXPR$1=[$t2])
EnumerableAggregate(group=[{0, 1}], EXPR$1=[COUNT()])
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[FLAG(MINUTE)],
expr#3=[FLOOR($t1, $t2)], expr#4=[Sarg[[2018-01-01 00:01:00..2018-01-01
00:05:00)]], expr#5=[SEARCH($t1, $t4)], eventid=[$t0], $f1=[$t3],
$condition=[$t5])
EnumerableTableScan(table=[[hr, events]])
but I would expect that the view would be used with a plan that includes a
filter:
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[Sarg[[2018-01-01
00:01:00..2018-01-01 00:05:00)]], expr#4=[SEARCH($t1, $t3)], EXPR$0=[$t1],
EXPR$1=[$t2], $condition=[$t4])
EnumerableTableScan(table=[[hr, MV0]])
If I run the following query without a predicate on the ts column the view
is used as expected :
select floor(ts to minute), count(*)
from events
group by eventid, floor(ts to minute)
EnumerableCalc(expr#0..2=[{inputs}], EXPR$0=[$t1], EXPR$1=[$t2])
EnumerableTableScan(table=[[hr, MV0]])
Thank you,
Thomas
On Thu, Jan 27, 2022 at 1:20 AM Stamatis Zampetakis <za...@gmail.com>
wrote:
> Hey Thomas,
>
> Can you post the plan/query that you were expecting after the rewriting?
> Is the behavior you observed specific to timestamps?
>
> Best,
> Stamatis
>
> On Thu, Jan 27, 2022 at 4:07 AM Thomas D'Silva <tdsilva@twilio.com.invalid
> >
> wrote:
>
> > Justin,
> >
> > Thanks for your response. If I change the query predicate to "floor(ts to
> > minute) >= timestamp'2018-01-01 00:01:00' AND floor(ts to minute) <
> > timestamp'2018-01-01 00:05:00'" it does end up querying the view.
> > However I want to be able to query an arbitrary time range and have the
> > union rewriting work so that the view can be used along with the table to
> > return results. For eg. for query predicate "ts >= timestamp'2018-01-01
> > 00:01:30' AND ts < timestamp'2018-01-01 00:05:00'", we could do a union
> > with time range [2018-01-01 00:02:00, 2018-01-01 00:05:00) used for the
> > view and [2018-01-01 00:01:30, 2018-01-01 00:02:00) used for the table.
> >
> > Thank you,
> > Thomas
> >
> > On Wed, Jan 26, 2022 at 2:25 PM Justin Swett <js...@google.com.invalid>
> > wrote:
> >
> > > Have you experimented with flooring the query predicate?
> > >
> > > -Justin
> > >
> > > On Wed, Jan 26, 2022 at 10:54 AM Thomas D'Silva
> > > <td...@twilio.com.invalid> wrote:
> > > >
> > > > Hi,
> > > >
> > > > The existing MaterializedViewRule is not applied when the
> materialized
> > > view
> > > > does not have a view predicate but the query contains a predicate.
> For
> > eg
> > > > for the following materialized view
> > > >
> > > > SELECT eventid, floor(ts to minute), count(*) as cnt
> > > > FROM events
> > > > GROUP BY eventid, floor(ts TO minute)
> > > >
> > > > If we have the following query the view is not chosen in the final
> > plan.
> > > >
> > > > SELECT floor(ts to minute), count(*)
> > > > FROM events
> > > > WHERE ts >= timestamp'2018-01-01 00:01:00' AND ts <
> > > timestamp'2018-01-01
> > > > 00:05:00'
> > > > GROUP BY eventid, floor(ts TO minute)
> > > >
> > > > If I add the predicate "ts > timestamp'2018-01-01 00:01:00' AND ts <
> > > > timestamp'2018-01-01 00:05:00'" to the materialized view then the
> > > > materialized view is chosen in the final plan. Would it be possible
> to
> > > > modify the MaterializedViewRule to automatically add a view predicate
> > > based
> > > > on the query when the materialized view being considerent aggregates
> > > over a
> > > > column that contains a floor()? This predicate would also need to be
> > > > applied to the rewritten view. I would like to file a JIRA and work
> on
> > > this
> > > > improvement if this makes sense.
> > > >
> > > > Thank you,
> > > > Thomas
> > >
> >
>
Re: MaterializedViewRule is not applied for queries on aggregate views that truncate timestamp
Posted by Stamatis Zampetakis <za...@gmail.com>.
Hey Thomas,
Can you post the plan/query that you were expecting after the rewriting?
Is the behavior you observed specific to timestamps?
Best,
Stamatis
On Thu, Jan 27, 2022 at 4:07 AM Thomas D'Silva <td...@twilio.com.invalid>
wrote:
> Justin,
>
> Thanks for your response. If I change the query predicate to "floor(ts to
> minute) >= timestamp'2018-01-01 00:01:00' AND floor(ts to minute) <
> timestamp'2018-01-01 00:05:00'" it does end up querying the view.
> However I want to be able to query an arbitrary time range and have the
> union rewriting work so that the view can be used along with the table to
> return results. For eg. for query predicate "ts >= timestamp'2018-01-01
> 00:01:30' AND ts < timestamp'2018-01-01 00:05:00'", we could do a union
> with time range [2018-01-01 00:02:00, 2018-01-01 00:05:00) used for the
> view and [2018-01-01 00:01:30, 2018-01-01 00:02:00) used for the table.
>
> Thank you,
> Thomas
>
> On Wed, Jan 26, 2022 at 2:25 PM Justin Swett <js...@google.com.invalid>
> wrote:
>
> > Have you experimented with flooring the query predicate?
> >
> > -Justin
> >
> > On Wed, Jan 26, 2022 at 10:54 AM Thomas D'Silva
> > <td...@twilio.com.invalid> wrote:
> > >
> > > Hi,
> > >
> > > The existing MaterializedViewRule is not applied when the materialized
> > view
> > > does not have a view predicate but the query contains a predicate. For
> eg
> > > for the following materialized view
> > >
> > > SELECT eventid, floor(ts to minute), count(*) as cnt
> > > FROM events
> > > GROUP BY eventid, floor(ts TO minute)
> > >
> > > If we have the following query the view is not chosen in the final
> plan.
> > >
> > > SELECT floor(ts to minute), count(*)
> > > FROM events
> > > WHERE ts >= timestamp'2018-01-01 00:01:00' AND ts <
> > timestamp'2018-01-01
> > > 00:05:00'
> > > GROUP BY eventid, floor(ts TO minute)
> > >
> > > If I add the predicate "ts > timestamp'2018-01-01 00:01:00' AND ts <
> > > timestamp'2018-01-01 00:05:00'" to the materialized view then the
> > > materialized view is chosen in the final plan. Would it be possible to
> > > modify the MaterializedViewRule to automatically add a view predicate
> > based
> > > on the query when the materialized view being considerent aggregates
> > over a
> > > column that contains a floor()? This predicate would also need to be
> > > applied to the rewritten view. I would like to file a JIRA and work on
> > this
> > > improvement if this makes sense.
> > >
> > > Thank you,
> > > Thomas
> >
>
Re: MaterializedViewRule is not applied for queries on aggregate views that truncate timestamp
Posted by Thomas D'Silva <td...@twilio.com.INVALID>.
Justin,
Thanks for your response. If I change the query predicate to "floor(ts to
minute) >= timestamp'2018-01-01 00:01:00' AND floor(ts to minute) <
timestamp'2018-01-01 00:05:00'" it does end up querying the view.
However I want to be able to query an arbitrary time range and have the
union rewriting work so that the view can be used along with the table to
return results. For eg. for query predicate "ts >= timestamp'2018-01-01
00:01:30' AND ts < timestamp'2018-01-01 00:05:00'", we could do a union
with time range [2018-01-01 00:02:00, 2018-01-01 00:05:00) used for the
view and [2018-01-01 00:01:30, 2018-01-01 00:02:00) used for the table.
Thank you,
Thomas
On Wed, Jan 26, 2022 at 2:25 PM Justin Swett <js...@google.com.invalid>
wrote:
> Have you experimented with flooring the query predicate?
>
> -Justin
>
> On Wed, Jan 26, 2022 at 10:54 AM Thomas D'Silva
> <td...@twilio.com.invalid> wrote:
> >
> > Hi,
> >
> > The existing MaterializedViewRule is not applied when the materialized
> view
> > does not have a view predicate but the query contains a predicate. For eg
> > for the following materialized view
> >
> > SELECT eventid, floor(ts to minute), count(*) as cnt
> > FROM events
> > GROUP BY eventid, floor(ts TO minute)
> >
> > If we have the following query the view is not chosen in the final plan.
> >
> > SELECT floor(ts to minute), count(*)
> > FROM events
> > WHERE ts >= timestamp'2018-01-01 00:01:00' AND ts <
> timestamp'2018-01-01
> > 00:05:00'
> > GROUP BY eventid, floor(ts TO minute)
> >
> > If I add the predicate "ts > timestamp'2018-01-01 00:01:00' AND ts <
> > timestamp'2018-01-01 00:05:00'" to the materialized view then the
> > materialized view is chosen in the final plan. Would it be possible to
> > modify the MaterializedViewRule to automatically add a view predicate
> based
> > on the query when the materialized view being considerent aggregates
> over a
> > column that contains a floor()? This predicate would also need to be
> > applied to the rewritten view. I would like to file a JIRA and work on
> this
> > improvement if this makes sense.
> >
> > Thank you,
> > Thomas
>
Re: MaterializedViewRule is not applied for queries on aggregate views that truncate timestamp
Posted by Justin Swett <js...@google.com.INVALID>.
Have you experimented with flooring the query predicate?
-Justin
On Wed, Jan 26, 2022 at 10:54 AM Thomas D'Silva
<td...@twilio.com.invalid> wrote:
>
> Hi,
>
> The existing MaterializedViewRule is not applied when the materialized view
> does not have a view predicate but the query contains a predicate. For eg
> for the following materialized view
>
> SELECT eventid, floor(ts to minute), count(*) as cnt
> FROM events
> GROUP BY eventid, floor(ts TO minute)
>
> If we have the following query the view is not chosen in the final plan.
>
> SELECT floor(ts to minute), count(*)
> FROM events
> WHERE ts >= timestamp'2018-01-01 00:01:00' AND ts < timestamp'2018-01-01
> 00:05:00'
> GROUP BY eventid, floor(ts TO minute)
>
> If I add the predicate "ts > timestamp'2018-01-01 00:01:00' AND ts <
> timestamp'2018-01-01 00:05:00'" to the materialized view then the
> materialized view is chosen in the final plan. Would it be possible to
> modify the MaterializedViewRule to automatically add a view predicate based
> on the query when the materialized view being considerent aggregates over a
> column that contains a floor()? This predicate would also need to be
> applied to the rewritten view. I would like to file a JIRA and work on this
> improvement if this makes sense.
>
> Thank you,
> Thomas