Get Started Free
‹ Back to courses
course: ksqlDB 101

Hands On: Transforming Data with ksqlDB

2 min
Allison

Allison Walther

Integration Architect (Presenter)

Hands On: Transforming Data with ksqlDB

Errata

  • In the video, there are a few commands to run before being able to follow Allison's instructions. These are just below, in the first step.

Continuing with the orders stream from previous exercises, this exercise teaches you how to transform data in ksqlDB.

Query an Existing Stream and Inspect the Data

  1. Begin by deleting the streams created previously:

    DROP STREAM orders_enriched DELETE TOPIC;
DROP STREAM orders DELETE TOPIC;

    then, create another orders stream with:

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

    finally, insert some data into it:

    INSERT INTO orders VALUES (1620504934723, 70, 'item_5', 1,
STRUCT(street:='210 West Veterans Drive', city:='Sacramento', state:='California Foo2'));
INSERT INTO orders VALUES (16205059321941, 72, 'item_6', 9,
STRUCT(street:='10043 Bella Vista Blvd', city:='Oakland', state:='California'));
INSERT INTO orders VALUES (1620503083019, 77, 'item_7', 12,
STRUCT(street:='10083 Garvey Ave', city:='Rosemead', state:='California'));

  2. Next, select everything from the orders stream:

    SELECT * FROM orders EMIT CHANGES;

    Click Run query.

  3. Scroll down to see the returned records. View the structure of one of the records by clicking on the caret in its upper left-hand corner.

ksqldb_101_hands_on_transform_data

Now Stop your orders query.

Create a Persistent Transformation

  1. Create a persistent transformation with no address data:

    CREATE STREAM orders_no_address AS
SELECT TIMESTAMPTOSTRING(ordertime, 'yyyy-MM-dd HH:mm:ss') AS order_timestamp, orderid, itemid, orderunits
FROM orders EMIT CHANGES;

Inspect the Transformed Stream

  1. Now select from the transformed stream:

    SELECT * FROM orders_no_address EMIT CHANGES;

    Click Run query.

    Expand the record to see the transformed data.

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 Transform a Stream of Events

Docs

CREATE STREAM AS SELECT

Docs

How to Use a Custom Timestamp Column

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: Transforming Data with ksqlDB

In this exercise, we're gonna transform data. We're continue on with this order stream that we had used in a previous example. First, let's start off by selecting everything from the order stream. Go ahead and run the query. When you scroll down, you'll see a set of records. Go ahead and expand one of those records to see the structure. Next, stop our orders query. Let's create a persistent transformation. Go ahead and append create stream as to the statement before select. Run the query. Now we'll go ahead and select from our transformed stream. Run that query. Expand the record to see how the transformed data looks. That's it; you've created a set of transformed records that will be persistent, even as new records show up.