LogoLogo
Start Trial
  • Overview
    • What is DeltaStream?
    • Core Concepts
      • Access Control
      • Compute Pools
      • Data Store
      • Database
      • Function
      • Query
      • SQL
      • Visualizing Data Lineage
  • Getting Started
    • Free Trial Quick Start
    • Starting with the Web App
    • Starting with the CLI
  • How do I...?
    • Create and Manage Data Stores
      • Create Data Stores for Streaming Data
      • Explore Data Store and Topic Details
      • Use Multiple Data Stores in Queries
    • Manage 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
    • Create DeltaStream Objects to Structure Raw Data
    • Use Namespacing for Organizing Data
    • Create and Query Materialized Views
    • Create a Compute Pool to Work with Iceberg
    • Create a Function
    • Secure my Connection to a Data Store
      • 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
    • Serialize my Data
      • Working with ProtoBuf Serialized Data and DeltaStream Descriptors
      • Working with Avro Serialized Data and Schema Registries
      • Configuring Deserialization Error Handling
  • Integrations
    • Setting up Data Store Integrations
      • AWS S3
      • ClickHouse
      • Confluent Cloud
      • Databricks
      • Iceberg REST Catalog
      • PostgreSQL
      • Snowflake
      • WarpStream
  • Setting up Enterprise Security Integrations
    • Okta SAML Integration
    • Okta SCIM Integration
  • use cases
    • Using an AWS S3 Store as a Source to Feed an MSK Topic
  • Reference
    • 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 COMPUTE_POOLS
        • LIST DATABASES
        • LIST DESCRIPTORS
        • LIST DESCRIPTOR_SOURCES
        • LIST ENTITIES
        • LIST FUNCTIONS
        • LIST FUNCTION_SOURCES
        • LIST INVITATIONS
        • LIST METRICS INTEGRATIONS
        • LIST ORGANIZATIONS
        • LIST QUERIES
        • 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
        • START COMPUTE_POOL
        • STOP COMPUTE_POOL
      • DDL
        • ALTER API_TOKEN
        • ALTER SECURITY INTEGRATION
        • CREATE API_TOKEN
        • CREATE CHANGELOG
        • CREATE COMPUTE_POOL
        • 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 COMPUTE_POOL
        • 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
        • START/STOP COMPUTE_POOL
        • UPDATE COMPUTE_POOL
        • 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
    • DeltaStream OpenAPI
      • Deltastream
      • Models
Powered by GitBook
On this page
  • Syntax
  • Description
  • Arguments
  • Materialized View Parameters
  • Examples
  1. Reference
  2. SQL Syntax
  3. Query
  4. Materialized View

CREATE MATERIALIZED VIEW AS

PreviousMaterialized ViewNextSELECT (FROM MATERIALIZED VIEW)

Last updated 2 months ago

Syntax

CREATE MATERIALIZED VIEW
  materialized_view_name
  [WITH (materialized_view_parameter = value [, ... ])]
AS select_statement;

Description

CREATE MATERIALIZED VIEW AS is a statement that:

  • Generates a DDL statement to create a new .

  • Launches a new query to write the results of the SELECT statement into the newly created Materialized View.

The specified select_statement generates records with columns of a primitive data type. See SELECT for the statement’s parameters and properties.

If the result of the statement is a , new records are added to the Materialized View in the append mode, where new records are appended and there is no update of the existing records. Otherwise, if the statement creates a , new records are added to the Materialized View in the upsert mode, where a new record updates an existing record if there is one with matching value(s) on the PRIMARY KEY column(s). See CREATE CHANGELOG for further information on PRIMARY KEY column(s).

How to control Materialized View's size?

When creating a Materialized View, you can define a retention period for the rows to determine how long the Materialized View retains a row before deleting it. By default, a Materialized View keeps all the rows and never deletes any of them. Therefore, size of the Materialized View keeps growing as new rows are added. Defining an appropriate retention period for the Materialized View allows you to control its size and let the older rows be purged automatically. Once a row is expired (according to the retention period) and purged, it is deleted forever and its purging can not be reverted. Currently, retention period has to be defined at the creation time for a Materialized View and user can not enable/disable purging, or change the retention period later. See Materialized View Parameters to see how retention period can be defined.

Arguments

materialized_view_name

This specifies the name of the new Materialized View. Optionally, use <database_name>.<schema_name> as the prefix to the name to create the Relation in that scope.

WITH (<materialized_view_parameter> = <value> [, …​ ])

Optionally, this clause specifies Materialized View Parameters.

select_statement

This statement specifies the SELECT statement to run.

Materialized View Parameters

Parameter Name
Description

retention.millis

The duration (in milliseconds) to determine how long Materialized View retains rows before they are purged. If not set, the rows will not be purged.

Required: No Default value: Infinity Type: Long

timestamp

Name of the column in the Materialized View to use as the timestamp. If not set, the timestamp of the message, coming from (one of) the Materialized view's sources, is used as the timestamp.

Examples

For the following examples, assume a Stream named pageviews has been created using the below DDL statement:

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

SELECT with a filter

The following statement creates a Materialized View, named visits, by selecting userid and pageid columns from all records in the pageviews Stream except those for User_5. Note that the result of the SELECT statement is a Stream. Therefore, new rows are simply added to the Materialized View with no impact on the existing ones.

CREATE MATERIALIZED VIEW
  visits
AS SELECT
  userid, pageid
FROM pageviews
WHERE userid <> 'User_5';

The visits Materialized View will have two columns, userid and pageid, both with the VARCHAR data type. For the details on how this view can be queried, check SELECT (FROM MATERIALIZED VIEW).

Run grouping and aggregation

The following statement creates a Materialized View, named pagevisits, by grouping records from the pageviews Stream using the pageid column and reporting the total number of records in each group as cnt. The result of the SELECT statement is a Changelog with the pageid column as the Primary Key. Therefore, new rows are added to the Materialized View in the upsert mode. Each pageid value has only one row in pagevists and a new row with matching pageid value overwrites the existing one, if any.

CREATE MATERIALIZED VIEW
  pagevisits
AS SELECT
  pageid, count(userid) as cnt 
FROM pageviews
GROUP BY pageid;

The pagevisits Materialized View will have two columns, pageid with the VARCHAR data type and cnt with the BIGINT data type. For the details on how this view can be queried, check SELECT (FROM MATERIALIZED VIEW).

Run grouping and aggregation with window function

The following statement creates a Materialized View, named visit_stats, by aggregating the total number of pages a given user visited every 30 seconds. Given that the SELECT statement generates results as a Changelog with the Primary Key of (window_start, window_end, userid), new rows are added to the Materialized View in the upsert mode, and there is only one row for any given userid in each 30-second interval.

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

The visit_stats Materialized View will have four columns, window_start and window_end with the TIMESTAMP(3) data type, as well as userid with the VARCHAR data type and pgcnt with the BIGINT data type. For the details on how this view can be queried, check SELECT (FROM MATERIALIZED VIEW).

Create a Materialized View with retention period

The following statement creates a Materialized View named visits_rate, by aggregating the total number of pages a given user visits every 10 seconds. Moreover, the retention.millis parameter is set to 600000 milliseconds (i.e. 10 minutes). This means the retention period for any row is 10 minutes and the row is purged after that. This will help controlling the total size of visits_rate by deleting older rows which are no longer needed.

CREATE MATERIALIZED VIEW
  visits_rate
WITH ('retention.millis' = '600000')
AS SELECT 
  window_start AS start_time, 
  window_end AS end_time, 
  userid, 
  count(pageid) AS cnt
FROM Tumble(pageviews, size 10 seconds)
GROUP BY userid, window_start, window_end;

Required: No Default value: Record’s timestamp. Type: String Valid values: Must be of type BIGINT or TIMESTAMP. See .

Data Types
Materialized View
Stream
Changelog