Get Started Free
‹ Back to courses
course: ksqlDB 101

Hands On: Merging Two Streams with ksqlDB

2 min
Allison

Allison Walther

Integration Architect (Presenter)

Hands On: Merging Two Streams with ksqlDB

This exercise will demonstrate how to merge two streams in ksqlDB.

Create and Populate Streams

  1. First, create your orders_uk stream:

    CREATE STREAM orders_uk (ordertime BIGINT, orderid INTEGER, itemid VARCHAR, orderunits INTEGER,
    address STRUCT< street VARCHAR, city VARCHAR, state VARCHAR>)
WITH (KAFKA_TOPIC='orders_uk', VALUE_FORMAT='json', PARTITIONS=1);

    Click Run query.

  2. Insert data into your orders_uk stream:

    INSERT INTO orders_uk VALUES (1620501334477, 65, 'item_7', 5,
  STRUCT(street:='234 Thorpe Street', city:='York', state:='England'));
INSERT INTO orders_uk VALUES (1620502553626, 67, 'item_3', 2,
  STRUCT(street:='2923 Alexandra Road', city:='Birmingham', state:='England'));
INSERT INTO orders_uk VALUES (1620503110659, 68, 'item_7', 7,
  STRUCT(street:='536 Chancery Lane', city:='London', state:='England'));

    Click Run query.

  3. Next, create your orders_us stream:

    CREATE STREAM orders_us (ordertime BIGINT, orderid INTEGER, itemid VARCHAR, orderunits INTEGER,
    address STRUCT< street VARCHAR, city VARCHAR, state VARCHAR>)
WITH (KAFKA_TOPIC='orders_us', VALUE_FORMAT='json', PARTITIONS=1);

    Click Run query.

  4. Insert data into your orders_us stream:

    INSERT INTO orders_us VALUES (1620501334477, 65, 'item_7', 5,
  STRUCT(street:='6743 Lake Street', city:='Los Angeles', state:='California'));
INSERT INTO orders_us VALUES (1620502553626, 67, 'item_3', 2,
  STRUCT(street:='2923 Maple Ave', city:='Mountain View', state:='California'));
INSERT INTO orders_us VALUES (1620503110659, 68, 'item_7', 7,
  STRUCT(street:='1492 Wandering Way', city:='Berkley', state:='California'));

    Click Run query.

Query and Inspect Your Existing Data

  1. First, run a query on your orders_uk stream:

    SELECT * FROM orders_uk EMIT CHANGES;

    Click Run query.

  2. Scroll down and expand a record. Notice that it's from the UK.

  3. Change your query to use the orders_us stream:

    SELECT * FROM orders_us EMIT CHANGES;

    Click Run query.

  4. Scroll down to expand another record. Notice that it's from the US.

Create a Merged Stream

  1. Merge the orders_us and orders_uk streams into a new stream, orders_combined, using the following two queries:

    CREATE STREAM orders_combined AS
SELECT 'US' AS source, ordertime, orderid, itemid, orderunits, address
FROM orders_us;

    Click Run query.

    INSERT INTO orders_combined
SELECT 'UK' AS source, ordertime, orderid, itemid, orderunits, address
FROM orders_uk;

    Click Run query.

Inspect Your Data Using the Flow Tab

  1. Now move to the Flow tab. See how orders_uk and orders_us flow in to create the orders_combined stream.

  2. Click on the orders_combined stream. On the right-hand side, look at the list of records. Notice that the stream contains records from the UK and from the US.

Use the promo code KSQLDB101 & CONFLUENTDEV1 to get $25 of free Confluent Cloud usage and skip credit card entry.

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 Merge Many Streams into One Stream

Video

ksqlDB HOWTO: Split and Merge Kafka Topics

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: Merging Two Streams with ksqlDB

In this exercise, I'll show you how to merge two streams. First, let's take a look at the orders UK stream. Go ahead and run this push query, scroll down and expand a record. You'll notice that this record is from the UK. Let's go back up and change our query to query the orders US stream. Go ahead and run that query and scroll down to expand another record. You'll see that record is from the US. The next thing that we'll do is we'll merge together the orders UK and the orders US streams. We'll name this orders combined. Run the query. To complete our merge, we'll insert records from the orders UK stream into orders combined. Go ahead and run the query. Now let's move over to the flow tab. You'll see that both orders UK and orders US flow in to create the orders combined stream. Click on the orders combined stream and on the right hand side, you'll see a list of records. You'll see that there's a record from the UK and a record from the US. And with that, we're done merging streams.