Verified
Status Update
No update yet.
Comments
bl...@google.com
eu...@google.com
I think it makes a perfect sense as a feature request and is indeed distinct from other issues you referenced. Thanks for filing!
eu...@google.com
I have CCed stakeholders and leaving the feature request assigned to myself for now.
r....@gmail.com
Thanks for accepting this request, having this would be fantastic in my opinion (and my team's)!
eu...@google.com
RE:"Thanks for accepting this request": this is just being brought for consideration. Thanks again for filing the feature request.
[Deleted User] <[Deleted User]>
Any progress on this?
This request is similar tohttps://issuetracker.google.com/issues/72224267
Why are we so interested in this?
Our goal is to build an easy to maintain, basically 'NoETL' datawarehouse pipeline. Everywhere where we have to 'fall back' to Python programming makes the whole pipeline more complex to maintain. Having (parametrized) materialized views that could do a full or partial refresh would really be THE enabler for this.
Basically it would be something like Looker's Persistent Direived Tables, but then with the added abilities to:
-Refresh incrementally
-Fully use time partitioning and table suffixing.
This request is similar to
Why are we so interested in this?
Our goal is to build an easy to maintain, basically 'NoETL' datawarehouse pipeline. Everywhere where we have to 'fall back' to Python programming makes the whole pipeline more complex to maintain. Having (parametrized) materialized views that could do a full or partial refresh would really be THE enabler for this.
Basically it would be something like Looker's Persistent Direived Tables, but then with the added abilities to:
-Refresh incrementally
-Fully use time partitioning and table suffixing.
eu...@google.com
ha...@google.com
zh...@google.com
What types of queries do you want to use in the MV?
Using aggregation views to optimize query performance and cost is a different use case than using MV as a workaround to ELT.
Using aggregation views to optimize query performance and cost is a different use case than using MV as a workaround to ELT.
[Deleted User] <[Deleted User]>
#7, for us the use cases would be both ETL and costs optimization queries (fyi, I am the original poster of these request)
IMO the main difference between the two patterns are:
1) ETL type MV's have more joins
2) Cost optimization MV's are simpler / aggregate style queries
In the end, what is important is that it supports the following refresh patterns:
1) easy: a full refresh (truncate / insert pattern). Oracle calls this a full refresh
2) more complex: incremental update, for this to work the insert/update/delete (or merge) has to be driven by a key. Oracle calls this the fast refresh option.
IMO this functionality could / should replace BQ scheduled queries, which I guess won't be a real focus anymore now that cloud composer is here...
IMO the main difference between the two patterns are:
1) ETL type MV's have more joins
2) Cost optimization MV's are simpler / aggregate style queries
In the end, what is important is that it supports the following refresh patterns:
1) easy: a full refresh (truncate / insert pattern). Oracle calls this a full refresh
2) more complex: incremental update, for this to work the insert/update/delete (or merge) has to be driven by a key. Oracle calls this the fast refresh option.
IMO this functionality could / should replace BQ scheduled queries, which I guess won't be a real focus anymore now that cloud composer is here...
zh...@google.com
tj...@wakr.com
Is there any progress with regards to this topic? Having materialised views in BigQuery would be the trigger for us to move away from our current solution, onto the BQ platform....
vt...@google.com
Tjeerd - please sync up with your Google Cloud account team to share roadmap under an NDA.
[Deleted User] <[Deleted User]>
Is materialized view in early-access stage?
We just saw it in gcloud CLI and would like to try this feature.
We just saw it in gcloud CLI and would like to try this feature.
ma...@tix.com.au
How do we get access to the experimental materialized view feature?
vt...@google.com
please sync up with your Google Cloud account team to share roadmap under an NDA.
cc...@sperdegroot.nl
Had hoped something would be announced at Google Next '19, guess we'll have to wait :-(
[Deleted User] <[Deleted User]>
Yep, would love this.
p....@gmail.com
We are looking for this feature too, and it often comes up when we evaluate bq vs snowflake. Would be great if bq can support MVs.
vt...@google.com
[Deleted User] <[Deleted User]>
with bq mk this is sort of possible (transfer jobs), something like:
bq mk \
--transfer_config \
--target_dataset='target_dataset' \
--display_name='schedule_name' \
--params='{"query":"SELECT xyz FROM table","destination_table_name_template":"target_table","write_disposition":"WRITE_APPEND"}' \
--data_source='scheduled_query' \
--schedule='every day 01:00'
bq mk \
--transfer_config \
--target_dataset='target_dataset' \
--display_name='schedule_name' \
--params='{"query":"SELECT xyz FROM table","destination_table_name_template":"target_table","write_disposition":"WRITE_APPEND"}' \
--data_source='scheduled_query' \
--schedule='every day 01:00'
mi...@gmail.com
nothing announced in Google Next 19 UK :(
mi...@gmail.com
was reading a blog about ML from google , and they dropped this info that materialized views are already in Alpha !!!!!!
ku...@xiatech.co.uk
Any update on this BQ team? If it is in alpha, let us know ASAP!
am...@gmail.com
are they still working on this? the issue is created on "Jun 1, 2017 12:47PM" , But still can't see any update on Materialized view.
fr...@google.com
Another customer expressed interest in this capability.
lo...@google.com
Hi,
I have a customer that is very interested in joining early access for this functionality.
Can I help them by signing up somewhere?
Thanks,
Lorin
I have a customer that is very interested in joining early access for this functionality.
Can I help them by signing up somewhere?
Thanks,
Lorin
mi...@gmail.com
Ok, Good news, I asked the product manager of BigQuery in twitter, and he said, Google next 2020, which will be in a couple of weeks.
re...@pup-eeze.com
+1
Ch...@walmart.com
+1
[Deleted User] <[Deleted User]>
+1
bv...@google.com
We will announce support for materialized views in BQ soon. Stay tuned!
ma...@gmail.com
Is there any scope to extend support to unnesting within the same table for MV's on the near horizon?
bw...@google.com
Thanks everyone for the suggestions. Materialized Views V2 will be coming soon.
pe...@b6tp.com
Thank you! Do you have reference/list of features to expect on V2?
bw...@google.com
The upcoming version of materialized views will add JOIN support to allow multi-table views as well as table projections.
Description
Of course we can do this by writing the result of a view to a new table and scheduling this refresh with Airflow (or something else), but having properly Materialized views (MV) implemented in BigQuery 'the oracle way' would really reduce this amount of work:
Suggested modes for refreshing:
1) Automatic / scheduled: add metadata to a view (cron like) where you can configure the refresh time / frequency
2) Automatic / triggered: based on a source table, used in the view where data that is added / changed
2) Manual, similar to using DBMS_MVIEW.REFRESH in Oracle
Furthermore, having the option to refersh a complete dependency tree of materialized views would also be great (something similar to DBMS_MVIEW.REFRESH_DEPENDENT in Oracle)
Having this kind of functionality will really make Bigquery an even more NoOps system. After all, people building the following kind of extra functionality is not NoOps, right?:
I realize this issue is related to