Clickhouse: Pushing WHERE conditions from the view to underlying table

[754154377]

Can you add parameters to the view， If there are no parameters, then every request must be queried before filtering, resulting in unnecessary waste of computing resources. In addition, JDBC query avoids transferring a large amount of SQL code.
Look forward to your reply
thanks

[KochetovNicolai]

Do you mean that you what to push down WHERE predicate from main query to VIEW? There is a setting enable_optimize_predicate_expression. Try to enable it and check if it works for your case.

[754154377]

Do you mean that you what to push down WHERE predicate from main query to VIEW? There is a setting enable_optimize_predicate_expression. Try to enable it and check if it works for your case.

For Example: To express my thoughts, I have fabricated the following functions, which do not actually exist.
create table shop_sale (event_date Date, shop_id String, goods_id String, sale_amt Float32) ENGINE = MergeTree(event_date , (shop_id), 8192);

insert into shop_sale VALUES('2000-01-01', 'AB01', 'A', 11201), ('2000-01-01', 'AB02', 'B', 11301), ('2000-01-01', 'AB02', 'C'. 12301);

create view view_shop_sale (event_date Date, shop_id String, sale_amt Float32) as select event_date, shop_id, sale_amt from shop_sale where event_date = ::eventDate:: and shop_id = ::shopId::
;

select *
from view_shop_sale
where eventDate= '2000-01-01' and shopId= 'AB01'

[den-crane]

You don't need parameters, it works out the box

If you create view like
create view view_shop_sale as select event_date, shop_id, sale_amt from shop_sale

enable_optimize_predicate_expression = 0
select * from view_shop_sale where eventDate= '2000-01-01' and shopId= 'AB01'
will be executed as
select * from (select event_date, shop_id, sale_amt from shop_sale) where eventDate= '2000-01-01' and shopId= 'AB01'

enable_optimize_predicate_expression = 1
select * from view_shop_sale where eventDate= '2000-01-01' and shopId= 'AB01'
will be re-written and executed as
select * from shop_sale where eventDate= '2000-01-01' and shopId= 'AB01'

[754154377]

You don't need parameters, it works out the box

If you create view like
create view view_shop_sale as select event_date, shop_id, sale_amt from shop_sale

enable_optimize_predicate_expression = 0
select * from view_shop_sale where eventDate= '2000-01-01' and shopId= 'AB01'
will be executed as
select * from (select event_date, shop_id, sale_amt from shop_sale) where eventDate= '2000-01-01' and shopId= 'AB01'

enable_optimize_predicate_expression = 1
select * from view_shop_sale where eventDate= '2000-01-01' and shopId= 'AB01'
will be re-written and executed as
select * from shop_sale where eventDate= '2000-01-01' and shopId= 'AB01'

thanks ！
In addition, can replicated tables support views?
For example:
create view view_shop_sale ON CLUSTER xxx_3replicas as select event_date, shop_id, sale_amt from shop_sale ?

[KochetovNicolai]

Yes, you can create view on any replica (or on cluster itself).
If you need several shards you can also create distributed table over views.

[blinkov]

@754154377 do you have any further questions?

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.