RisingWave SQL 101

In this guide, we will walk you through some of the most used SQL commands in RisingWave. This is a simple yet typical data processing workflow that shows how to manipulate data with RisingWave.

RisingWave uses Postgres-compatible SQL as the interface to manage and query data. For a complete list of supported SQL commands, please navigate to SQL references → Commands.

Before we start

Ensure that you have started and connected to RisingWave.

Create a table

Now let's create a table to store data about taxi trips.

CREATE TABLE taxi_trips (
    trip_id int, 
    taxi_id int, 
    completed_at timestamp, 
    distance double precision, 
    duration double precision);

And let's add some data to the table.

INSERT INTO taxi_trips VALUES 
(1, 1001, '2022-07-01 22:00:00', 4, 6), 
(2, 1002, '2022-07-01 22:01:00', 6, 9);

In this guide, we use a user-defined table to better explain the mechanism of materialized views.
The best and most common practice is to create a source to ingest streaming data from a data source, not inserting records into a table.

Create a materialized view

We want to create a materialized view to dynamically calculate the average speed of all rides.

CREATE MATERIALIZED VIEW mv_avg_speed
AS
    SELECT COUNT(trip_id) as no_of_trips,
    SUM(distance) as total_distance,
    SUM(duration) as total_duration,
    SUM(distance) / SUM(duration) as avg_speed
    FROM taxi_trips;

For details about creating a materialized view, see CREATE MATERIALIZED VIEW.

Query a materialized view

We can now query the average speed.

SELECT * FROM mv_avg_speed;

Here is the result we get.

 no_of_trips | total_distance | total_duration |     avg_speed      
-------------+----------------+----------------+--------------------
           2 |             10 |             15 | 0.6666666666666666
(1 row)

Now let's add a new record.

INSERT INTO taxi_trips VALUES (3, 1003, '2022-07-01 22:02:00', 3, 5);

As soon as we insert the new record, the materialized view mv_avg_speed will be refreshed to re-calculate the results. Let us see if the results are updated.

SELECT * FROM mv_avg_speed;

Here is the result we get.

 no_of_trips | total_distance | total_duration | avg_speed 
-------------+----------------+----------------+-----------
           3 |             13 |             20 |      0.65
(1 row)

You can see that the results are based on the two rows of data. The calculation is performed automatically behind the scene. No matter how many more rows of data we insert, we can always get the latest results by querying the values of the materialized view.

Create a materialized view from a source

Creating a materialized view from a source is similar to creating from a table.

The following statement creates a materialized view for three columns in a connected source named debezium_json_mysql_source.

To create a materialized view from a source:

CREATE MATERIALIZED VIEW debezium_json_mysql_mv 
AS 
    SELECT COLUMN1, COLUMN2, COLUMN3 FROM debezium_json_mysql_source;

For details about creating a source, see CREATE SOURCE.

Create a materialized view on materialized views

With RisingWave, you can also create a materialized view from an existing materialized view.

To create a materialized view from existing materialized views:

CREATE MATERIALIZED VIEW m3
AS 
    SELECT m1.v1 as m1v1, m1.v2 as m1v2, m2.v1, m2.v2 
    FROM m1 
    INNER JOIN m2 ON m1.v1 = m2.v1;

Create a data sink

Finally, we can output the processed data to a data sink using CREATE SINK. A sink is an external target that we can send data to.

More to read

We also prepared several demos in the Demos section of the docs, each focusing on solving a real-world stream processing task, with simulated data.