Skip to main content

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, each focusing on solving a real-world stream processing task, with simulated data.

Help us make this doc better!

Was this page helpful?

Happy React is loading...