create_materialized_view_w_flattened_json_column¶
create_materialized_view_w_flattened_json_column(fully_qualified_table, fully_qualified_materialized_view, json_column)
Description¶
Create a Materialized view of a table with json_column flattened
.
It creates a materialized view with:
- query given by sql_to_flatten_json_column function
- same partition as
fully_qualified_table
Usage¶
Call or Deploy create_materialized_view_w_flattened_json_column ?
Call create_materialized_view_w_flattened_json_column directly
The easiest way to use bigfunctions
create_materialized_view_w_flattened_json_columnfunction is deployed in 39 public datasets for all of the 39 BigQuery regions.- It can be called by anyone. Just copy / paste examples below in your BigQuery console. It just works!
- (You need to use the dataset in the same region as your datasets otherwise you may have a function not found error)
Public BigFunctions Datasets
| Region | Dataset |
|---|---|
eu |
bigfunctions.eu |
us |
bigfunctions.us |
europe-west1 |
bigfunctions.europe_west1 |
asia-east1 |
bigfunctions.asia_east1 |
| ... | ... |
Deploy create_materialized_view_w_flattened_json_column in your project
Why deploy?
- You may prefer to deploy
create_materialized_view_w_flattened_json_columnin your own project to build and manage your own catalog of functions. - This is particularly useful if you want to create private functions (for example calling your internal APIs).
- Get started by reading the framework page
Deployment
create_materialized_view_w_flattened_json_column function can be deployed with:
pip install bigfunctions
bigfun get create_materialized_view_w_flattened_json_column
bigfun deploy create_materialized_view_w_flattened_json_column
Examples¶
call bigfunctions.eu.create_materialized_view_w_flattened_json_column("your_project.your_dataset.your_table", "your_project.your_dataset.your_materialized_view", "data");
call bigfunctions.us.create_materialized_view_w_flattened_json_column("your_project.your_dataset.your_table", "your_project.your_dataset.your_materialized_view", "data");
call bigfunctions.europe_west1.create_materialized_view_w_flattened_json_column("your_project.your_dataset.your_table", "your_project.your_dataset.your_materialized_view", "data");
Use cases¶
You have a BigQuery table containing a JSON column called data that stores user activity logs. The JSON structure varies slightly between records, making it difficult to query specific attributes efficiently. You want to create a materialized view that flattens this JSON column, allowing simpler and faster queries on these attributes.
Use Case:
Let's say your table your_project.your_dataset.your_table looks like this:
| user_id | event_timestamp | data |
|---|---|---|
| 1 | 2024-07-26 10:00 | {"event_type":"page_view", "page":"/home"} |
| 2 | 2024-07-26 10:01 | {"event_type":"purchase", "item_id": 123} |
| 1 | 2024-07-26 10:02 | {"event_type":"page_view", "page":"/products"} |
You can use the create_materialized_view_w_flattened_json_column function to create a materialized view your_project.your_dataset.your_materialized_view:
call bigfunctions.us.create_materialized_view_w_flattened_json_column('your_project.your_dataset.your_table', 'your_project.your_dataset.your_materialized_view', 'data');
This will create a materialized view with columns for user_id, event_timestamp, and the flattened JSON attributes, like event_type, page, and item_id. The resulting materialized view might look something like this (depending on the actual data and the function's implementation):
| user_id | event_timestamp | event_type | page | item_id |
|---|---|---|---|---|
| 1 | 2024-07-26 10:00 | page_view | /home | NULL |
| 2 | 2024-07-26 10:01 | purchase | NULL | 123 |
| 1 | 2024-07-26 10:02 | page_view | /products | NULL |
Now, querying for all page views becomes significantly easier:
SELECT * FROM your_project.your_dataset.your_materialized_view WHERE event_type = 'page_view';
This query will be much faster than querying the original table and parsing the JSON within the WHERE clause. This improved query performance is the key benefit of using a materialized view with a flattened JSON column.
Need help or Found a bug?
Get help using create_materialized_view_w_flattened_json_column
The community can help! Engage the conversation on Slack
We also provide professional suppport.
Report a bug about create_materialized_view_w_flattened_json_column
If the function does not work as expected, please
- report a bug so that it can be improved.
- or open the discussion with the community on Slack.
We also provide professional suppport.
by adding a 
on