Support non-key joins.

[big-andy-coates]

With https://cwiki.apache.org/confluence/display/KAFKA/KIP-213+Support+non-key+joining+in+KTable complete, we should be able to enhance ksqlDB to support non-key joins!

[entechlog]

@big-andy-coates , @vpapavas Is support for non key table join coming to ksqlDB in 0.8.0 release ? We have a project going into production in second quarter of this year and its heavily dependent on table joins which always dont share same primary key. Without this feature GlobalKTable and custom kafka streams may be our only option (which we were trying to avoid with ksql platform)

[vpapavas]

Hi @entechlog, unfortunately foreign key joins are not coming in 0.8.0. How about creating additional tables that have as primary key the join key you are interested in? Simulate the behavior of secondary indeces basically by creating extra tables by hand that have the schema you need for each join you are interested in,

[entechlog]

@vpapavas ,
I don't think creating an additional table will always help, Unless I am missing something. Please see below example.

Here I need to join the orders table every time when an order comes in with user table and both has different PK's. I can't make USER_ID as PK in orders table, then it will retain only one order record, Unless the PK's are same ksqlDB is not letting to join the tables.

TBL_ORDERS
ORDER_ID - Primary Key
ITEM_ID
ITEM_DESC
QUANTITY
PRICE
USER_ID

TBL_USER
USER_ID - Primary Key
USER_NAME
USER_EMAIL
USER_PHONE

[PeterLindner]

@entechlog you have to convert your orders table to a stream partitioned by user ID, then you can perform a stream - table join on user ID, repartition back to order ID and convert back to a table
Note however that changes to the user table would not apply to older orders, if you need that I believe the only way to do this is to use the collect_set udaf to collect all order ids grouped by user ID (yields a table), perform a stream-table join with a user stream to get a message for each user modification and then explode an repartition on the collected order ids to propagate the modifications to the orders, then join that stream with the original orders table and insert into the orders stream and again create a table from it.
I hope you could follow 😉 note that I haven't tried that myself yet, but I will have to do something similar for my use case once I get my data into Kafka, so please tell me if it worked.

[entechlog]

@PeterLindner, Thanks for the input. We are trying several things and will keep you posted on what we end up doing. The example was just to share the idea but we are dealing with much complex with multiple self joins and self joins based on result from aggregation of all historical data.

[bellemare]

@big-andy-coates I was actually just about to email you about this, as I see you've done a lot of work on the joins to date. I contributed the PR (with some help from many fine people) for KIP-213 mentioned above, and I've been looking at how to get this into KSQL. I've been looking under the hood of KSQL and I admit that I don't have a firm handle on it yet, and was wondering where this sort of work sits in terms of priority for the Confluent folks working on this product. I'm interested in helping if I can, but I admit that I know next to nothing about KSQL's engine.

Thanks Andy!

[bellemare]

Bumping the question @big-andy-coates

[bhamur]

@PeterLindner collect_set has a limitation of just 1000 elements in the array. Did you implement non-key joins with any other work-around?

[PeterLindner]

@bhamur unfortunately not, for my use case I knew, that I'd have at most 3 records in the set

[nvaksmann1992]

@entechlog did you find a solution for the join with foreign-key ?

I have the same problem and I was wondering if you found a solution yet ? :)

[big-andy-coates]

@big-andy-coates I was actually just about to email you about this, as I see you've done a lot of work on the joins to date. I contributed the PR (with some help from many fine people) for KIP-213 mentioned above, and I've been looking at how to get this into KSQL. I've been looking under the hood of KSQL and I admit that I don't have a firm handle on it yet, and was wondering where this sort of work sits in terms of priority for the Confluent folks working on this product. I'm interested in helping if I can, but I admit that I know next to nothing about KSQL's engine.

Thanks Andy!

Hi @bellemare, sorry for the delay in answering your question!

To be honest, I'm not 100% sure where this comes in terms of priority. I know its not part of the next quarter's roadmap. Beyond that, I'm not sure. @MichaelDrogalis or @derekjn may be able to comment more.

It would be great to have you contribute, if you feel able! First steps would be to write up the design proposal: https://github.com/confluentinc/ksql/blob/master/design-proposals/README.md.

Andy

[MichaelDrogalis]

A KIP/patch for this would be 😍

[bellemare]

Unfortunately, I don't have the time to devote to this in the next 3 months due to personal obligations. We also aren't using KSql where I work at the moment, so I can't reasonably get cycles there to address it.

[big-andy-coates]

Understandable. We appreciate your contribution to Streams, and we will leverage your work by exposing it in ksqlDB within the foreseeable future, just no firm date as yet. What this space!

[bellemare]

I'll keep my eyes and ears open! That being said, if something changes, this would probably be one of the first things I would work on :)

[rtrive]

is there some updates for this feature?

[MichaelDrogalis]

Hey @rtrive, nothing yet. But patches welcome from anyone that wants to work on this. ❤️

[MichaelDrogalis]

@yuranos We're working on this right now. :)

[pragmaticivan]

Howdy! Is there a place we could track code progress for that issue?

[mjsax]

There is no PR yet... Stay tuned. Happy to link PRs to this ticket when available.

[mkoziel2000]

Glad the one-to-many is getting worked on. this specific issue is keeping us from actually treating ksql as anything other than a toy to play around with. Hoping this gets over the finish line soon...it could offset a whole lot of consumer code we currently are having to develop.

[mjsax]

First PR: #7452

[mjsax]

Second PR: #7491

[mjsax]

Third PR: #7511
Fourth PR: #7517

[mjsax]

Adding more tests: #7526

[mjsax]

Required refactoring: #7499

[bellemare]

@mjsax I am very pleased to see your work on this!

[mjsax]

It's about time to add it to kslqDB... But it's not just me, but a team effort together with @vcrfxia and @spena :)

[mjsax]

Adding more tests: #7528
Extend logical planner: #7522

[mjsax]

Logical to Physical plan translation (and trying to get the tests green): #7543

[mjsax]

More tests: #7537

[mjsax]

Some more bug fixes: #7576

[mjsax]

Bug fix: #7547
Test framework fix: #7579
Fixing long standing but expose by FK-join work: #7585

[mjsax]

Add missing test: #7588

[mjsax]

Another bug fix: #7592

[mjsax]

Enable FK-joins: #7591

[mjsax]

Enable more tests: #7593

[mjsax]

Updating docs: #7628

[shrugal]

Hey @mjsax thanks for posting these updates! Do I read it right that it's actually a N:1 join, so a row from the left table is joined with just one from the right, but one and the same row from the right table can be joined to multiple different rows from the left? For example with the tables users and orders FROM orders JOIN users ON orders.user_id = users.id is possible, but FROM users JOIN orders ON users.id = orders.user_id is not?

[mjsax]

That is correct. If you think in DW terms, the left table would be your "fact table" and you can do FK-lookups into the right "dimension table".

It would be possible of course, to enhance ksqlDB to also allow the second query (and I am sure we will do this at some point). It's similar to the missing "RIGHT JOIN" support -- we don't support t1 RIGHT JOIN t2 but you can still do the join as t2 LEFT JOIN t1... We need to enhance ksqlDB to internal "flip" left/right input to support this. It's in our backlog :)