Materialized Views on BigQuery: The forgotten Smart Tuning feature
BigQuery materialized views allow to reduce costs and improve performances on Google BigQuery. A common misconception is that materialized views force users to change their endpoints and queries in order to go directly to views.
We will see that thanks to the Smart Tuning feature of Google BigQuery, this is not necessary, and that there’s no impact for the end user.
A simple Materialized View
Let’s take the example of this query from the TPC-DS benchmark:
In the BigQuery console:
We create a materialized view that corresponds to this query:
Once we create the materialized view that corresponds exactly to the query, we get straight to 2 MB of scanned data.
This query will process 2.25 MB
This decrease of 80% approximately in MB scanned data has a direct impact on performance and costs.
BigQuery Smart tuning
In order to use this materialized view, it was necessary to rewrite the query. It can quickly get complicated and repetitive if it has to be done for all view-optimized queries. BigQuery’s Smart Tuning feature allows us to avoid rewriting queries in order to directly use materialized views.Indeed, if we re execute the request from the very beginning which cost 9 MB
We get the same result as having rewritten the query so that it directly asks our materialized view.
Thanks to this materialized view and Smart tuning, the experience of users who make queries on BigQuery is improved without changing anything on their side.
The same view in a more complex query
In analytics, most queries are more complex than simple SELECTs.
The query that we used in the first part is in fact a sub-query of a more complex query, still taken from TPC-DS.
Now, when we execute this query, we see that even so, the sub-query, on which we created a dedicated materialized view, is caught by the view, and the processed byte is drastically reduced.
The request without materialized view had a processed byte of 12.6 MB: here it goes to 4.73 MB.
Conclusion
Thanks to BigQuery’s Smart Tuning, creating an even simple materialized view brings immediate benefits, in a completely transparent way for the end user.
In a future part, we will see how to automate the creation of materialized views with Achilio.
References
A very good article BigQuery Hack: 1000x More Efficient Aggregation Using Materialized View by @tim-lou
Github repository with TPC-DS Benchmark for BigQuery: https://github.com/snithish/tpc-ds_big-query
Original article: https://www.achilio.com/blog/google-bigquery-the-forgotten-smart-tuning-feature
