Course: ksqlDB 101

Hands On: Stateful Aggregations (Materialized Views)

2 min
Allison WaltherIntegration Architect (Course Presenter)

Hands On: Stateful Aggregations (Materialized Views)

Learn about ksqlDB's materialized views in this exercise.

Create a New Stream and Insert Data

  1. Create a stream MOVEMENTS:

    If you have a stream named MOVEMENTS from a previous lesson you can drop it with:

    DROP STREAM MOVEMENTS DELETE TOPIC;

    Now create the new stream:

    CREATE STREAM MOVEMENTS(PERSON VARCHAR KEY, LOCATION VARCHAR)
    	WITH (VALUE_FORMAT='JSON', PARTITIONS=1, KAFKA_TOPIC='movements');

    Click Run query.

  2. Insert data into MOVEMENTS:

    INSERT INTO MOVEMENTS VALUES ('Robin', 'York');
    INSERT INTO MOVEMENTS VALUES ('Robin', 'Leeds');
    INSERT INTO MOVEMENTS VALUES ('Allison', 'Denver');
    INSERT INTO MOVEMENTS VALUES ('Robin', 'Ilkley');
    INSERT INTO MOVEMENTS VALUES ('Allison', 'Boulder');

    Click Run query.

Run Queries with Aggregated Calculations

  1. To see the number of movements per person in the stream, enter the following query:

    SELECT PERSON, COUNT (*)
    FROM MOVEMENTS GROUP BY PERSON EMIT CHANGES;

    Click Run query.

  2. Edit the query to count the unique locations that each person has visited:

    SELECT PERSON, COUNT_DISTINCT(LOCATION)
    FROM MOVEMENTS GROUP BY PERSON EMIT CHANGES;

    Click Run query.

Create a Materialized View Table with Your Aggregated Calculations

  1. Now create a table that shows both aggregated calculations, combining PERSON, COUNT(*) and COUNT_DISTINCT(LOCATION):

    CREATE TABLE PERSON_STATS AS
    SELECT PERSON,
    		LATEST_BY_OFFSET(LOCATION) AS LATEST_LOCATION,
    		COUNT(*) AS LOCATION_CHANGES,
    		COUNT_DISTINCT(LOCATION) AS UNIQUE_LOCATIONS
    	FROM MOVEMENTS
    GROUP BY PERSON
    EMIT CHANGES;

    Click Run query.

Insert Additional Data and Query Your Table

  1. Insert more data into your MOVEMENTS stream to make the calculation more interesting:

    INSERT INTO MOVEMENTS VALUES('Robin', 'Manchester');
    INSERT INTO MOVEMENTS VALUES('Allison', 'Loveland');
    INSERT INTO MOVEMENTS VALUES('Robin', 'London');
    INSERT INTO MOVEMENTS VALUES('Allison', 'Aspen');
    INSERT INTO MOVEMENTS VALUES('Robin', 'Ilkley');
    INSERT INTO MOVEMENTS VALUES('Allison', 'Vail');
    INSERT INTO MOVEMENTS VALUES('Robin', 'York');

    Click Run query.

  2. SELECT a person’s name to see how many times they have changed locations and how many unique locations they have visited:

    SELECT * FROM PERSON_STATS WHERE PERSON = 'Allison';

    Click Run query.

Use the promo code KSQLDB101 to get $101 of free Confluent Cloud usage

Be the first to get updates and new content

We will only share developer content and updates, including notifications when new content is added. We will never send you sales emails. 🙂 By subscribing, you understand we will process your personal information in accordance with our Privacy Statement.