KSQL join returns null fields

Question

I am trying to join the following stream

 Field     | Type
---------------------------------------
 ROWTIME   | BIGINT           (system)
 ROWKEY    | VARCHAR(STRING)  (system)
 USER_ID   | VARCHAR(STRING)
 FIRSTNAME | VARCHAR(STRING)
---------------------------------------

with the following table:

 Field     | Type
---------------------------------------
 ROWTIME   | BIGINT           (system)
 ROWKEY    | VARCHAR(STRING)  (system)
 USER_ID   | VARCHAR(STRING)
 USERNAME  | VARCHAR(STRING)
---------------------------------------

using

SELECT M.USER_ID, \
       C.USERNAME \
FROM MY_STREAM M \
LEFT JOIN MY_TABLE C \
ON (M.USER_ID = C.USER_ID);

but USERNAME contains only null values.

1 | null
2 | null
3 | null
4 | null
5 | null
6 | null
7 | null
8 | null

I am sure that there are common user_ids and that no username is null in the table. Am I missing anything here?

Perhaps you can check whether your experience matches https://stackoverflow.com/questions/50020930/ksql-left-join-displays-columns-from-stream-but-not-tables. — miguno, May 03 '18 at 13:37

score 2 · Answer 1 · answered May 03 '18 at 15:43

See my answer to this post, in which I demonstrate how to troubleshoot joins like this.

In short, you need to key the messages in your table's topic on the column on which you join.

If you're going to join on USER_ID then :

SELECT ROWKEY, USER_ID FROM MY_TABLE

should return

1 | 1 
2 | 2
3 | 3

etc

KSQL join returns null fields

1 Answers1