Course: ksqlDB 101

Merging Two Streams with ksqlDB

1 min
Allison WaltherIntegration Architect (Course Presenter)
Robin MoffattStaff Developer Advocate (Course Author)

Merging Two Streams with ksqlDB

ksqlDB can write data from multiple streams into a single target. This can be useful when you have events for the same logical entity (for example, orders) written to separate topics (perhaps originating in different Apache Kafka clusters or instances of the producing application). At the same time, you can add in or modify the data to ensure that attributes such as identifiers remain unique.

orders-uk

Taking the first stream as the source, create a target stream, including a hard-coded identifier for the source and a concatenation to ensure that the ID remains unique:

CREATE STREAM ORDERS_COMBINED AS
  SELECT 'US' AS SOURCE,
         CONCAT_WS('-','US',CAST(ORDERID AS VARCHAR)) AS ORDERID,
         ORDERTIME,
         ITEMID,
         ORDERUNITS,
         ADDRESS
    FROM ORDERS
    PARTITION BY CONCAT_WS('-','US',CAST(ORDERID AS VARCHAR));

Now add in additional sources to the same target, using INSERT INTO. This works in the same way as CREATE STREAM … AS SELECT, by reading the output of a continuous SELECT query. The only difference is that an INSERT INTO statement writes to an existing target.

INSERT INTO ORDERS_COMBINED
  SELECT 'UK' AS SOURCE,
         CONCAT_WS('-','UK',CAST(ORDERID AS VARCHAR)) AS ORDERID,
         ORDERTIME,
         ITEMID,
         ORDERUNITS,
         ADDRESS
    FROM ORDERS_UK
    PARTITION BY CONCAT_WS('-','UK',CAST(ORDERID AS VARCHAR));

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.