Get Started Free
‹ Back to courses
course: ksqlDB 101

Hands On: Filtering with ksqlDB

1 min
Allison

Allison Walther

Integration Architect (Presenter)

Hands On: Filtering with ksqlDB

This exercise will teach you how to filter streams in ksqlDB for Confluent Cloud.

Create a Stream and Insert Data

  1. Begin by creating a stream for orders in the editor (make sure to set auto.offset.reset = earliest):

    Create-Filtered-Stream

    CREATE stream orders (id INTEGER KEY, item VARCHAR, address STRUCT <  
city  VARCHAR, state VARCHAR >)
WITH (KAFKA_TOPIC='orders', VALUE_FORMAT='json', partitions=1);

    Click Run query.

  2. Next, insert orders into the new orders stream:

    INSERT INTO orders(id, item, address)
VALUES(140, 'Mauve Widget', STRUCT(city:='Ithaca', state:='NY'));
INSERT INTO orders(id, item, address)
VALUES(141, 'Teal Widget', STRUCT(city:='Dallas', state:='TX'));
INSERT INTO orders(id, item, address)
VALUES(142, 'Violet Widget', STRUCT(city:='Pasadena', state:='CA'));
INSERT INTO orders(id, item, address)
VALUES(143, 'Purple Widget', STRUCT(city:='Yonkers', state:='NY'));
INSERT INTO orders(id, item, address)
VALUES(144, 'Tan Widget', STRUCT(city:='Amarillo', state:='TX'));

    Click Run query.

Create a Filtered Stream

  1. Set the auto.offset.reset property to earliest so that the new filtered stream we are about to create will include the data we inserted in the previous step.

  2. Next, create a stream for only New York orders:

    CREATE STREAM ny_orders AS SELECT * FROM ORDERS WHERE
 ADDRESS->STATE='NY' EMIT CHANGES;

    Click Run query.

Query the Filtered Stream

  1. To validate the filtered stream, run a query against your ny_orders topic:

    SELECT * FROM ny_orders EMIT CHANGES;

    Click Run query.

    Scroll through the results and see that all of the records are from New York.

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

Get Started

Get started with Confluent Cloud

Try free

Total 28

  1. Introduction to ksqlDB
    2 min
  2. Interacting with ksqlDB
    2 min
  3. Hands On: Interacting with ksqlDB
    5 min
  4. Creating, Exporting, and Importing Data
    2 min
  5. Filtering with ksqlDB
    1 min
  6. Hands On: Filtering with ksqlDB
    1 min
  7. Lookups and Joins with ksqlDB
    2 min
  8. Hands On: Lookups and Joins with ksqlDB
    2 min
  9. Transforming Data with ksqlDB
    2 min
  10. Hands On: Transforming Data with ksqlDB
    2 min
  11. Flatten Nested Records with ksqlDB
    1 min
  12. Hands On: Flatten Nested Records with ksqlDB
    2 min
  13. Converting Data Formats with ksqlDB
    2 min
  14. Hands On: Converting Data Formats with ksqlDB
    2 min
  15. Merging Two Streams with ksqlDB
    1 min
  16. Hands On: Merging Two Streams with ksqlDB
    2 min
  17. Splitting Two Streams with ksqlDB
    2 min
  18. Hands On: Splitting Two Streams with ksqlDB
    2 min
  19. Streams and Tables
    2 min
  20. Stateful Aggregations (Materialized Views)
    2 min
  21. Hands On: Stateful Aggregations (Materialized Views)
    2 min
  22. Push Queries and Pull Queries
    2 min
  23. Hands On: Push Queries and Pull Queries
    2 min
  24. Under the Covers of ksqlDB
    2 min
  25. ksqlDB Code Lifecycle
    2 min
  26. Apply Lambda Functions to Arrays and Maps
    2 min
  27. Hands On: Apply Lambda Functions to Arrays and Maps
    2 min
  28. Monitoring ksqlDB
    1 min

Related Resources

Presentation

Inside ksqlDB

Tutorial

How to Filter a Stream of Events Using ksqlDB

Tutorial

How to Find Distinct Values in a Stream of Events

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.

Hands On: Filtering with ksqlDB

Let's jump into an exercise about filtering streams. We're gonna start off by creating a stream for orders. This order stream will contain data from various different places. Let's go ahead and run this query. Next, let's insert some orders into our order stream. Run that insert query. Next, we're gonna create a stream for only New York orders. Go ahead and run the query. To validate, we'll run a push query against our New York order stream to validate that there's only orders from New York in our stream. Go ahead and run the push query. Below, you should be able to scroll through results and see that all of the records are from New York and New York only. Now you know how to filter streams.