Is it possible to use multiple left join in Confluent KSQL query? tried to join stream with more than 1 tables , if not then whats the solution?

Question

Stream :

describe ammas;

 Field   | Type                        
-------------------------------------  
 ROWTIME | BIGINT           (system)  
 ROWKEY  | VARCHAR(STRING)  (system)   
 ID      | INTEGER                      
 -------------------------------------

For runtime statistics and query details run: DESCRIBE EXTENDED <Stream,Table>;

Table-01 :

ksql> show tables;

Table Name | Kafka Topic | Format    | Windowed 
-------------------------------------------------
ANNAT      | anna        | DELIMITED | false    
APPAT      | appa        | DELIMITED | false    
-------------------------------------------------

Trying to Join stream vs. table-01 is working as expected. Create stream finalstream as select a.id from ammas a left join appat b on a.id = b.id where b.id is null.

But when I tried to join more than one table with stream based on the following query:

ksql> SELECT * FROM ammas cd LEFT JOIN appat ab ON ab.id = cd.id LEFT JOIN annat aa ON aa.id =cd.id;
ServerError:io.confluent.ksql.parser.exception.ParseFailedException
Caused by: null

What is going wrong? Thanks.

Tubb

Based on my analysis: multiple joins in a single statement is currently not supported. you can accomplish the same thing by writing multiple KSQL statements:CREATE STREAM intermediate AS SELECT * FROM ammas cd LEFT JOIN appat ab ON ab.id = cd.id SELECT * FROM intermediate cd LEFT JOIN annat aa ON aa.id=cd.id – Tubb

Answer 1

KSQL currently (v5.3) only supports a single join operation per statement. If you want to perform multiple joins you have to "daisy-chain" them in multiple statements, e.g.

CREATE STREAM out1 AS 
  SELECT * FROM ammas cd 
           LEFT JOIN appat ab 
             ON ab.id = cd.id 

CREATE STREAM out_final AS 
  SELECT * FROM out1 o 
           LEFT JOIN annat aa ON o.id =cd.id;

---

Update November 2020: ksqlDB supports multi-way joins were added in ksqlDB v0.9. Confluent Platform 6.0 includes ksqlDB 0.10.2 and therefore also includes this functionality. See the release blog and documentation for details.

Tessellation

Is this still true with v0.13.0? It seems like that... – Kesha

thanks. I was on the wrong track anyway, seem to have rather a connect than a KSQL problem... – Kesha

Answer 2

Multiple join having stream and tables is supported in the actual version (Confluent Platform 7.x at the moment I'm writing)

Please see this document https://developer.confluent.io/tutorials/multi-joins/ksql.html

The example is with 1 stream and 2 tables Currently I don't know if it's supported with 3 or more tables

The important thing is that the join with the right side must be with its primary key

Thermosetting