Example: joining Kafka and Kudu tables

Using lookup join, you can join Kafka and Kudu tables to enrich the streaming data of Kafka with information from the Kudu tables. In the this example, Orders of a Kafka streaming table are enriched with metadata information from a Kudu table.

As a prerequisite for the example, the following steps were completed in SQL Stream Builder:
  • Registering Kafka as a Data Source.
  • Registering Kudu as a Catalog.
  • Creating Orders Kafka table.
  • Creating ItemMeta Kudu table.
  • Generating data for Kafka and Kudu tables.
The tables in the example contain the following information:
Tables Columns
Kafka - Orders
  • order_number
  • price
  • order_time
  • item_id
Kudu - ItemMeta
  • id
  • info
In the scope of this example, the Orders table will be joined with latest version of the ItemMeta table based on the item id, and the selected information is sampled under the Results tab of the Streaming SQL Console:
SELECT order_time, item_id, info, price
FROM Orders
JOIN kudu.default_database.ItemMeta FOR SYSTEM_TIME AS OF o.proc_time AS k
ON item_id = id

After running the SQL query, the results are continuously sampled to the Streaming SQL Console, the rows of order_time, item_id and price are enriched with info column from the ItemMeta Kudu table.