You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@doris.apache.org by yangzhg <ya...@apache.org> on 2022/06/20 06:12:04 UTC
[Discuss][DSIP] Support Multi-table materialized views
I'd like to propose the support for Multi-table materialized view.
At present, materialized views can be created on a single table, and can
use pre-computed results to achieve query acceleration, but support for
multi-table scenarios cannot be realized. Many query scenarios are
relatively simple and the data update frequency is not high. Materialized
views can effectively improve query performance and reduce data calculation
The associated issue: https://github.com/apache/doris/issues/7503
The DSIP WIKI:
https://cwiki.apache.org/confluence/display/DORIS/DSIP-017%3A+Support+Multi-table+materialized+views
Re: [Discuss][DSIP] Support Multi-table materialized views
Posted by zhg yang <ya...@gmail.com>.
The full update does not increase the complexity of the user's use. Under
normal circumstances, the user also needs to create a view, and the normal
view also needs to be calculated in time. Using a multi-table materialized
view is equivalent to caching some results. In order to reduce the user's
dependence on using external systems, the user can also specify on-demand
refresh. When accessing this materialized view, if the materialized view
expires, it can be degenerated into ordinary view access, and a refresh
task is generated at the same time. Incremental updates of other databases
all rely on a unique primary key, such as rowid or primary key. This
primary key does not exist in doris, and other table data except duplicate
is imported and changed continuously, so incremental implementation is
implemented in doris The update has certain difficulties for you. I think
we can divide it into two stages. In the first stage, the county can
achieve full update.
Thanks
Yang Zhengguo
陈明雨 <mo...@163.com> 于2022年6月26日周日 11:55写道：
> I have read the DSIP, and I think the Instructions for use are clearer,
> but we need more detail designs.
> For example:
> 1. how to "refresh" the mv
> If we use "insert into select", Is there any difference between this
> and the user using a crontab script outside to refresh. Does this increase
> in complexity match the benefits to users?
>
> 2. What about incremental update
> I think this is what the user actually needs. If it is just a full
> update, users can fully support it through other T+1 system, and there is
> no need to put such a large amount of data into Doris for processing.
>
>
> 3. Does this function have some pre-request feature that need to be
> designed first?
> 1. Resource isolation to make sure that the background refresh
> processing will not affect other queries.
> 2. Support update/upsert for table, so that we can reflect the
> "change-data" from upstream to downstream materialized views.
>
>
>
>
> --
>
> 此致！Best Regards
> 陈明雨 Mingyu Chen
>
> Email:
> morningman@apache.org
>
>
>
>
>
> At 2022-06-20 14:12:04, "yangzhg" <ya...@apache.org> wrote:
> >I'd like to propose the support for Multi-table materialized view.
> >
> >At present, materialized views can be created on a single table, and can
> >use pre-computed results to achieve query acceleration, but support for
> >multi-table scenarios cannot be realized. Many query scenarios are
> >relatively simple and the data update frequency is not high. Materialized
> >views can effectively improve query performance and reduce data
> calculation
> >
> >The associated issue: https://github.com/apache/doris/issues/7503
> >The DSIP WIKI:
> >
> https://cwiki.apache.org/confluence/display/DORIS/DSIP-017%3A+Support+Multi-table+materialized+views
>
Re: [Discuss][DSIP] Support Multi-table materialized views
Posted by zhg yang <ya...@gmail.com>.
And pre-request feature may be we should improve the performance of insert
into select ，but this may be not pre， post may be also fine
Thanks
Yang Zhengguo
陈明雨 <mo...@163.com> 于2022年6月26日周日 11:55写道：
> I have read the DSIP, and I think the Instructions for use are clearer,
> but we need more detail designs.
> For example:
> 1. how to "refresh" the mv
> If we use "insert into select", Is there any difference between this
> and the user using a crontab script outside to refresh. Does this increase
> in complexity match the benefits to users?
>
> 2. What about incremental update
> I think this is what the user actually needs. If it is just a full
> update, users can fully support it through other T+1 system, and there is
> no need to put such a large amount of data into Doris for processing.
>
>
> 3. Does this function have some pre-request feature that need to be
> designed first?
> 1. Resource isolation to make sure that the background refresh
> processing will not affect other queries.
> 2. Support update/upsert for table, so that we can reflect the
> "change-data" from upstream to downstream materialized views.
>
>
>
>
> --
>
> 此致！Best Regards
> 陈明雨 Mingyu Chen
>
> Email:
> morningman@apache.org
>
>
>
>
>
> At 2022-06-20 14:12:04, "yangzhg" <ya...@apache.org> wrote:
> >I'd like to propose the support for Multi-table materialized view.
> >
> >At present, materialized views can be created on a single table, and can
> >use pre-computed results to achieve query acceleration, but support for
> >multi-table scenarios cannot be realized. Many query scenarios are
> >relatively simple and the data update frequency is not high. Materialized
> >views can effectively improve query performance and reduce data
> calculation
> >
> >The associated issue: https://github.com/apache/doris/issues/7503
> >The DSIP WIKI:
> >
> https://cwiki.apache.org/confluence/display/DORIS/DSIP-017%3A+Support+Multi-table+materialized+views
>
Re:[Discuss][DSIP] Support Multi-table materialized views
Posted by 陈明雨 <mo...@163.com>.
I have read the DSIP, and I think the Instructions for use are clearer, but we need more detail designs.
For example:
1. how to "refresh" the mv
If we use "insert into select", Is there any difference between this and the user using a crontab script outside to refresh. Does this increase in complexity match the benefits to users?
2. What about incremental update
I think this is what the user actually needs. If it is just a full update, users can fully support it through other T+1 system, and there is no need to put such a large amount of data into Doris for processing.
3. Does this function have some pre-request feature that need to be designed first?
1. Resource isolation to make sure that the background refresh processing will not affect other queries.
2. Support update/upsert for table, so that we can reflect the "change-data" from upstream to downstream materialized views.
--
此致！Best Regards
陈明雨 Mingyu Chen
Email:
morningman@apache.org
At 2022-06-20 14:12:04, "yangzhg" <ya...@apache.org> wrote:
>I'd like to propose the support for Multi-table materialized view.
>
>At present, materialized views can be created on a single table, and can
>use pre-computed results to achieve query acceleration, but support for
>multi-table scenarios cannot be realized. Many query scenarios are
>relatively simple and the data update frequency is not high. Materialized
>views can effectively improve query performance and reduce data calculation
>
>The associated issue: https://github.com/apache/doris/issues/7503
>The DSIP WIKI:
>https://cwiki.apache.org/confluence/display/DORIS/DSIP-017%3A+Support+Multi-table+materialized+views