Skip to content

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:

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_column function 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_column in 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.


Show your ❤ by adding a ⭐ on