Create and Query Materialized Views

A Materialized View is a DeltaStream object that captures the result of a query and is continuously updated as new records are ingested in the query’s source object(s).

This article demonstrates how to create materialized views and run queries on them.

Create a Materialized View

To begin, use the CREATE MATERIALIZED VIEW AS statement to create a new materialized view. Note that with this statement, you can only generate columns with Primitive Data Types when you use the SELECT query.

Example 1: Top-K query

Create a materialized view using window functions

In this first example, create a Database called mv_pageviews on the data stored in the ds_pageviews topic in your default data store. Use this DDL statement to create this stream:

CREATE STREAM mv_pageviews (
    viewtime BIGINT,
    userid VARCHAR,
    pageid VARCHAR
) WITH ('topic'='pageviews', 'value.format'='JSON');

Now, if you wish to track how many pages each user visits every 30 seconds, you can use a query with a SELECT to create a materialized view. Call this materialized view visits_count. DeltaStream keeps its content up to date in a real-time manner.

CREATE MATERIALIZED VIEW
  visits_count
AS SELECT 
  window_start, 
  window_end,
  userid, 
  COUNT(pageid) AS pgcnt 
FROM TUMBLE(pageviews, size 30 SECONDS) 
GROUP BY window_start, window_end, userid;

To review the details on the above two objects, in the lefthand navigation click Databases ( ), and drill down on the database to display your streams.

Drilling down from Database to display streams

Top-k query

When you have created the visits_count materialized view, you 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. The system orders results based on the total page counts and the top 3 records that are returned:

SELECT userid, SUM(pgcnt) AS total
FROM visits_count
GROUP BY userid
ORDER BY total DESC
LIMIT 3;

Here is a sample result for the query:

Result of the query

For more details on query capabilities on materialized views, please see SELECT (FROM MATERIALIZED VIEW)

Example 2. Combine two streams’ records

In this example, you use a materialized view to run different queries on JOIN results. Assume you have a new stream, called users, created using the DDL below. This stream captures some information about each user, such as the time a given user has registered and the user’s interests.

CREATE STREAM users_info (
  registertime BIGINT,
  userid VARCHAR,
  regionid VARCHAR,
  gender VARCHAR,
  interests ARRAY<VARCHAR>,
  contactinfo STRUCT<phone VARCHAR, city VARCHAR, "state" VARCHAR, zipcode VARCHAR>
) WITH ('topic'='ds_users', 'value.format'='json');

Create a materialized view using JOIN

Now suppose you wish to enrich the information already captured in the pageviews stream by combining it with each user’s information from users. To do this, you can run an SELECT query and store the result in a materialized view called visits_info.

CREATE MATERIALIZED VIEW
  visits_info
AS SELECT p.userid AS uid,
          p.pageid,
          u.registertime,
          u.interests[1] AS top_interest
FROM pageviews p JOIN users u
WITHIN 1 minute
ON u.userid = p.userid;

Run queries on the JOIN results

Now that the visits_info materialized view is created, DeltaStream continually updates its content in real-time as new records are ingested in either the pageviews or the users. You 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.

SELECT *
FROM visits_info
WHERE top_interest = 'Game';

The output looks like this:

Output of a sample query

As another example, you 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 user count per interest.

SELECT top_interest, count(uid) AS cnt
FROM visits_info
WHERE pageid = 'Page_15'
GROUP BY top_interest
ORDER BY cnt DESC;

The query results look like this:

Sample query results