Creating and Querying Materialized Views
A Materialized View is a Relation that captures the result of a query and is continuously updated as new records are ingested in the query’s source Relation(s).
In this tutorial, we show how Materialized Views are created and how queries can be run on them.
Create a Materialized View
Example 1. Top-K query
Create a Materialized View using window functions
You can go to Databases
> Relations
tab and check the details on the above two Relations:
Top-k query
Now that the visits_count
Materialized View is created, we can run a top-k query against it. This query retrieves the top-3 users who have visited the most number of pages so far. It uses GROUP BY
and the aggregate function SUM
to calculate the total number of pages each user has visited across all-time windows. Results are ordered based on the total page counts and the top-3 records that are returned:
Here is a sample result for the query:
Check SELECT (FROM MATERIALIZED VIEW) for more details on query capabilities on Materialized Views.
Example 2. Combine two Streams’ records
In the second example, we use a Materialized View to run different queries on JOIN
results. Assume a new Stream, called users
, is created using the DDL below. This Stream captures some information about each user, such as the time a given user has registered along with the user’s interests.
Create a Materialized View using JOIN
JOIN
Run queries on the JOIN
results
JOIN
resultsNow that the visits_info
Materialized View is created, its content keeps getting updated in real-time as new records are ingested in either the pageviews
or the users
. We can retrieve the latest records in the Materialized View by running queries directly on it.
For example, the below query finds info on pages visited by users whose top interest is gaming.
The output would look like this:
As another example, we can find top interests of all the users who have visited a specific page (such as Page_15
) so far and sort them according to the total users count per interest.
The query results would look like the below sample:
Last updated