Syntax

Parameters

Examples

RisingWave

RisingWave Cloud

Demos

Integrations

SQL & UDFs

SDKs

Changelog

FAQs

Sign up free

Blog

Community

v2.0 and earlier

Use the `CREATE MATERIALIZED VIEW` command to create a materialized view. A materialized view can be created based on sources, tables, materialized views, or indexes.

CREATE MATERIALIZED VIEW

RisingWave is a Postgres-compatible SQL database engineered to offer the _**simplest**_ and _**most cost-effective**_ approach for **processing**, **analyzing**, and **managing** real-time event streaming data.

What is RisingWave?

RisingWave excels in a variety of real-time data processing scenarios, making it an ideal choice for several categories of use cases, including:

RisingWave use cases

This guide aims to provide a quick and easy way to get started with RisingWave.

Quick start

This topic introduces the RisingWave Premium Edition and offers a complete list of all premium features.

Premium edition

RisingWave Premium Edition

As RisingWave is wire-compatible with PostgreSQL, you have the flexibility to utilize third-party PostgreSQL drivers to seamlessly interact with RisingWave from your applications.

Overview

Use RisingWave in your Node.js application

Node.js

Use RisingWave in your Python application

Python

Ruby

Java

RisingWave supports a variety of data ingestion methods.

Overview of data ingestion

Change data capture (CDC) refers to the process of identifying and capturing data changes in a database and then delivering the changes to a downstream service in real-time.

CDC overview

Change data capture with RisingWave

The built-in load generator can generate mock data, which can be used in demos and tests. It provides an easy way to simulate the data stream without connecting to an actual external data source.

Load generator

Generate test data

When creating a source or table using a connector, you need to specify the `FORMAT` and `ENCODE` section of the [CREATE SOURCE](/docs/current/sql-create-source/) or [CREATE TABLE](/docs/current/sql-create-source/) statement. This topic provides an overview of the formats and encoding options. For the complete list of formats we support, see [Supported sources and formats](/docs/current/supported-sources-and-formats/)

Formats and encoding

FORMAT and ENCODE parameters

Supported sources and formats

This topic describes how to modify the schema of a RisingWave source or table.

Modify source or table schemas

This topic describes how to use the `INCLUDE` clause when creating a table or source with an external connector to extract fields not included in the payload as separate columns. The payload refers to the actual content or information carried by a message, event, or record, defined in the schema when creating a source or table.

Ingest additional source fields

Ingest additional fields with INCLUDE clause

Data processing is a crucial step in converting raw data into valuable insights.

Overview of data processing

Similar to traditional databases, RisingWave supports data updates and deletions within tables. Additionally, when data in a table is modified or deleted, any materialized views built on that table will be updated accordingly.

Deletes and updates

This guide introduces how to maintain a wide table whose columns come from different sources. Traditional data warehouses or ETL use a join query for this purpose. However, streaming join brings issues such as low efficiency and high memory consumption.

Maintain wide table with table sinks

In stream processing, watermarks are integral when using event time processing logic with event time based operations. Watermarks are like markers or signals that track the progress of event time, allowing you to process events within their corresponding time windows. A watermark is an estimate of the maximum event time observed so far, or a threshold indicating that events received so far have a timestamp later than or equal to the current watermark. Events that arrive with a timestamp earlier than the current watermark are considered late and are not processed within its time window.

Watermarks

Emit on window close

Indexes in a database are designed to increase the speed at which the database management system (DBMS) can locate and retrieve the desired data from a table or a materialized view.

Indexes

This guide describes how to leverage the time travel feature. This feature helps access historical data at a specific time.

Time travel queries

RisingWave supports delivering data to downstream systems via its sink connectors.

Overview of data delivery

A foreign data wrapper in PostgreSQL allows you to directly virtualize data stored in an external database as a local external table, also known as a foreign table. This tutorial will demonstrate how to interact between PostgreSQL and RisingWave. In this example, RisingWave will use CDC (Change Data Capture) to extract data from PostgreSQL, and analyze it using a materialized view. Then PostgreSQL will directly retrieve the computation results stored in RisingWave.

RisingWave as Postgres FDW

RisingWave as a PostgreSQL foreign data wrapper

Subscription is used to pull data change records for a specific table or materialized view (MV).

Subscription

SQL references

Overview of the SQL commands supported by RisingWave.

SQL commands

The `ALTER CONNECTION` command modifies the definition of a connection. To use this command, you must own the connection.

ALTER CONNECTION

The `ALTER DATABASE` command modifies the definition of a database.

ALTER DATABASE

The `ALTER FUNCTION` command modifies the definition of a function. To use this command, you must own the function.

ALTER FUNCTION

The `ALTER INDEX` command modifies the definition of an index.

ALTER INDEX

The `ALTER MATERIALIZED VIEW` command modifies the metadata of a materialized view. To use this command, you must own the materialized view.

ALTER MATERIALIZED VIEW

The `ALTER SCHEMA` command modifies the definition of a schema. To use this command, you must own the schema.

ALTER SCHEMA

The `ALTER SINK` command modifies the metadata of a sink.

ALTER SINK

The `ALTER SOURCE` command modifies the definition of a source.

ALTER SOURCE

The `ALTER SYSTEM` command modifies the value of a server configuration parameter.

ALTER SYSTEM

The `ALTER TABLE` command modifies the definition of a table.

ALTER TABLE

Use the `ALTER USER` command to modify the name, password, system permissions, and other properties of an existing user.

ALTER USER

The `ALTER VIEW` command modifies the metadata of a view. To use this command, you must own the view.

ALTER VIEW

Use the `AS CHANGELOG` clause to convert a changelog operation in a stream into a column.

AS CHANGELOG

RisingWave supports read-only transactions. You can use the `BEGIN READ ONLY` command to start a read-only transaction.

BEGIN

Use `CANCEL JOBS/JOB` to cancel specific streaming jobs that are in progress.

CANCEL JOBS

You can use the `COMMENT ON` command to add comments on tables or columns in RisingWave. Comments are stored as metadata of tables or columns.

COMMENT ON

RisingWave supports read-only transactions. You can use the `COMMIT` command to commit the current transaction.

COMMIT

The `CREATE AGGREGATE` command can be used to create [user-defined aggregate functions](/docs/current/user-defined-functions/) (UDAFs). Currently, UDAFs are only supported in Python and JavaScript as embedded UDFs.

CREATE AGGREGATE

Use the `CREATE CONNECTION` command to create an AWS PrivateLink connection for a Kafka connector.

CREATE CONNECTION

Use the `CREATE DATABASE` command to create a new database.

CREATE DATABASE

The `CREATE FUNCTION` command can be used to create [user-defined functions](/docs/current/user-defined-functions/) (UDFs).

CREATE FUNCTION

Use the `CREATE INDEX` command to construct an [index](/docs/current/indexes/) on a table or a materialized view.

CREATE INDEX

Use the `CREATE SCHEMA` command to create a new schema.

CREATE SCHEMA

Use the `CREATE SECRET` command to create secrets to store sensitive credentials securely.

CREATE SECRET

Use the `CREATE SINK INTO` command to create a sink into RisingWave's table.

CREATE SINK INTO

Use the `CREATE SINK` command to create a sink. A sink is an external target where you can send data processed in RisingWave. You can create a sink from a table or a materialized view.

CREATE SINK

A source is a resource that RisingWave can read data from. You can create a source in RisingWave using the `CREATE SOURCE` command.

CREATE SOURCE

Use the `CREATE TABLE` command to create a new table. Tables consist of fixed columns and insertable rows. 

CREATE TABLE

Use the `CREATE USER` command to create a new user account in RisingWave.

CREATE USER

Use the `CREATE VIEW` command to create a non-materialized view, which runs every time the view is referenced in a query. A non-materialized view can be created based on sources, tables, views, or indexes.

CREATE VIEW

Use the `DELETE` command to permanently remove rows from a table.

DELETE

Use the `DESCRIBE` command to view columns in the specified table, source, sink, view, or materialized view.

DESCRIBE

Use the `DISCARD` command to reset the state of current session

DISCARD

Use the `DROP AGGREGATE` command to remove an existing [user-defined aggregate function (UDAF)](/docs/current/user-defined-functions/). The usage is similar to `DROP FUNCTION`, except that it's for aggregate functions.

DROP AGGREGATE

Use the `DROP CONNECTION` command to remove a connection.

DROP CONNECTION

Use the `DROP DATABASE` command to remove a database from your RisingWave instance.

DROP DATABASE

DROP FUNCTION

Use the `DROP INDEX` command to remove an index from a table or a materialized view.

DROP INDEX

Use the `DROP MATERIALIZED VIEW` command to remove a materialized view from the database.

DROP MATERIALIZED VIEW

Use the `DROP SCHEMA` command to remove a schema from a database.

DROP SCHEMA

Use the `DROP SECRET` command to drop the secrets that store sensitive credentials.

DROP SECRET

Use the `DROP SINK` command to remove a [sink](/docs/current/sql-create-sink/) if you no longer need to deliver data to the sink.

DROP SINK

Use the `DROP SOURCE` command to remove a [source](/docs/current/sql-create-source/) if you no longer need the data inflow from the source.

DROP SOURCE

Use the `DROP TABLE` command to remove a table from the database.

DROP TABLE

Use the `DROP USER` command to remove a user from RisingWave.

DROP USER

Use the `DROP VIEW` command to remove an existing view from a particular schema.

DROP VIEW

Use the `EXPLAIN` command to show the execution plan of a statement.

EXPLAIN

The `FLUSH` command commits any pending data changes and forces RisingWave to persist updated data to storage, which guarantees subsequent reads can access the latest data.

FLUSH

Use the `GRANT` command to grant specific privileges to a user.

GRANT

Use the `INSERT` command to insert new rows into an existing table.

INSERT

Use the `RECOVER` command to trigger an ad-hoc recovery manually. This is helpful when there is a high barrier latency and you need to force a recovery to activate. By doing this, commands like `CANCEL` or `DROP` can take effect immediately.

RECOVER

Use the `REVOKE` command to revoke privileges from a user.

REVOKE

Use the `SELECT` command to retrieve rows from a table or materialized view. It returns the rows that satisfy the criteria that you specify with the clauses and conditions in your query.

SELECT

Data Definition Language (DDL) commands, such as creating materialized views, will first **backfill** historical data from the referenced relations, and completion time varies based on the volume of data to be backfilled.

SET BACKGROUND_DDL

The `RW_IMPLICIT_FLUSH` configuration option controls the behavior of implicit flushes after batch operations.

SET RW_IMPLICIT_FLUSH

Use the `SET TIME ZONE` command to specify the time zone that should be used to interpret date and time values.

SET TIME ZONE

Use the `SET` command to change a runtime parameter.

Use the `SHOW CLUSTER` command to display various details of your cluster, such as the cluster's address, type, state, and starting time.

SHOW CLUSTER

Use the `SHOW COLUMNS` command to view columns in the specified table, source, sink, view or materialized view.

SHOW COLUMNS

Use the `SHOW CONNECTIONS` command to see connections that have been created.

SHOW CONNECTIONS

Use the `SHOW CREATE INDEX` command to see what query was used to create the specified index.

SHOW CREATE INDEX

Use the `SHOW CREATE MATERIALIZED VIEW` command to see what query was used to create the specified materialized view.

SHOW CREATE MATERIALIZED VIEW

Use the `SHOW CREATE SINK` command to see the SQL statement used to create the specified sink. By using this command, you can verify the sink's settings and troubleshoot any issues.

SHOW CREATE SINK

Use the `SHOW CREATE SOURCE` command to see the SQL statement used to create the specified source. By using this command, you can verify the source's settings and troubleshoot any issues.

Parameter or clause	Description
mv_name	The name of the materialized view to be created.
select_query	A SELECT query that retrieves data for the materialized view. See SELECT for the syntax and examples of the SELECT command.

SQL reference

User-defined functions

CREATE MATERIALIZED VIEW

Syntax

Parameters

Examples

See also

Overview of data processing

SQL reference

User-defined functions

​Syntax

​Parameters

​Examples

​See also

Overview of data processing

Syntax

Parameters

Examples

See also