LogoLogo
Start Trial
  • Overview
    • What is DeltaStream?
    • Core Concepts
      • Access Control
      • Region
      • SQL
      • Store
      • Database
      • Query
      • Visualizing Data Lineage
      • Function
  • Getting Started
    • Free Trial Quick Start
    • Starting with the Web App
    • Starting with the CLI
  • Tutorials
    • Managing Users and User Roles
      • Inviting Users to an Organization
      • Administering Users in your Organization
      • Using the CLI to Manage User Roles
      • Example: Setting Up Custom Roles for Production and Stage
    • Creating Stores for Streaming Data
    • Using Multiple Stores in Queries
    • Creating Relations to Structure Raw Data
    • Namespacing with Database and Schema
    • Creating and Querying Materialized Views
    • Creating a Function
    • Securing Your Connections to Data Stores
      • Introducing DeltaStream Private Links
      • Creating an AWS Private Link from DeltaStream to your Confluent Kafka Dedicated Cluster
      • Enabling Private Link Connectivity to Confluent Enterprise Cluster and Schema Registry
      • Creating a Private Link from DeltaStream to Amazon MSK
      • Creating a Private Link for RDS Databases
      • Deleting a Private Link
    • Integrations
      • Connecting to Confluent Cloud
      • Databricks
      • PostgreSQL
      • Snowflake
      • WarpStream
    • Serialization
      • Working with ProtoBuf Serialized Data and DeltaStream Descriptors
      • Working with Avro Serialized Data and Schema Registries
      • Configuring Deserialization Error Handling
  • Reference
    • Enterprise Security Integrations
      • Okta SAML Integration
      • Okta SCIM Integration
    • Metrics
      • Prometheus Integration
      • Built-In Metrics
      • Custom Metrics in Functions
    • SQL Syntax
      • Data Formats (Serialization)
        • Serializing with JSON
        • Serializing with Primitive Data Types
        • Serializing with Protobuf
      • Data Types
      • Identifiers and Keywords
      • Command
        • ACCEPT INVITATION
        • CAN I
        • COPY DESCRIPTOR_SOURCE
        • COPY FUNCTION_SOURCE
        • DESCRIBE ENTITY
        • DESCRIBE QUERY
        • DESCRIBE QUERY METRICS
        • DESCRIBE QUERY EVENTS
        • DESCRIBE QUERY STATE
        • DESCRIBE RELATION
        • DESCRIBE RELATION COLUMNS
        • DESCRIBE ROLE
        • DESCRIBE SECURITY INTEGRATION
        • DESCRIBE <statement>
        • DESCRIBE STORE
        • DESCRIBE USER
        • GENERATE COLUMNS
        • GENERATE TEMPLATE
        • GRANT OWNERSHIP
        • GRANT PRIVILEGES
        • GRANT ROLE
        • INVITE USER
        • LIST API_TOKENS
        • LIST DATABASES
        • LIST DESCRIPTORS
        • LIST DESCRIPTOR_SOURCES
        • LIST ENTITIES
        • LIST FUNCTIONS
        • LIST FUNCTION_SOURCES
        • LIST INVITATIONS
        • LIST METRICS INTEGRATIONS
        • LIST ORGANIZATIONS
        • LIST QUERIES
        • LIST REGIONS
        • LIST RELATIONS
        • LIST ROLES
        • LIST SCHEMAS
        • LIST SCHEMA_REGISTRIES
        • LIST SECRETS
        • LIST SECURITY INTEGRATIONS
        • LIST STORES
        • LIST USERS
        • PRINT ENTITY
        • REJECT INVITATION
        • REVOKE INVITATION
        • REVOKE PRIVILEGES
        • REVOKE ROLE
        • SET DEFAULT
        • USE
      • DDL
        • ALTER API_TOKEN
        • ALTER SECURITY INTEGRATION
        • CREATE API_TOKEN
        • CREATE CHANGELOG
        • CREATE DATABASE
        • CREATE DESCRIPTOR_SOURCE
        • CREATE ENTITY
        • CREATE FUNCTION_SOURCE
        • CREATE FUNCTION
        • CREATE INDEX
        • CREATE METRICS INTEGRATION
        • CREATE ORGANIZATION
        • CREATE ROLE
        • CREATE SCHEMA_REGISTRY
        • CREATE SCHEMA
        • CREATE SECRET
        • CREATE SECURITY INTEGRATION
        • CREATE STORE
        • CREATE STREAM
        • CREATE TABLE
        • DROP API_TOKEN
        • DROP CHANGELOG
        • DROP DATABASE
        • DROP DESCRIPTOR_SOURCE
        • DROP ENTITY
        • DROP FUNCTION_SOURCE
        • DROP FUNCTION
        • DROP METRICS INTEGRATION
        • DROP RELATION
        • DROP ROLE
        • DROP SCHEMA
        • DROP SCHEMA_REGISTRY
        • DROP SECRET
        • DROP SECURITY INTEGRATION
        • DROP STORE
        • DROP STREAM
        • DROP USER
        • UPDATE ENTITY
        • UPDATE SCHEMA_REGISTRY
        • UPDATE SECRET
        • UPDATE STORE
      • Query
        • APPLICATION
        • Change Data Capture (CDC)
        • CREATE CHANGELOG AS SELECT
        • CREATE STREAM AS SELECT
        • CREATE TABLE AS SELECT
        • Function
          • Built-in Functions
          • Row Metadata Functions
        • INSERT INTO
        • Materialized View
          • CREATE MATERIALIZED VIEW AS
          • SELECT (FROM MATERIALIZED VIEW)
        • Query Name and Version
        • Resume Query
        • RESTART QUERY
        • SELECT
          • FROM
          • JOIN
          • MATCH_RECOGNIZE
          • WITH (Common Table Expression)
        • TERMINATE QUERY
      • Sandbox
        • START SANDBOX
        • DESCRIBE SANDBOX
        • STOP SANDBOX
      • Row Key Definition
    • Rest API
Powered by GitBook
On this page
  • Create a Materialized View
  • Example 1: Top-K query
  • Example 2. Combine two streams’ records
  1. Tutorials

Creating and Querying Materialized Views

PreviousNamespacing with Database and SchemaNextCreating a Function

Last updated 6 months ago

A 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).

This tutorial 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 when you use the SELECT query.

Example 1: Top-K query

Create a materialized view using window functions

In this first example, create a called mv_pageviews on the data stored in the ds_pageviews topic in your default 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 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;

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. Results are ordered 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:

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

Example 2. Combine two streams’ records

In the second 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 along with 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

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 would look like this:

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 would look like this:

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

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 query and store the result in a materialized view called visits_info.

#interval-join-stream-stream
#window-function
#_stream
Materialized View
Primitive Data Types
Drilling down from Catalog to display streams
Result of the query.
Output of a sample query.
Sample query results.