Ksql:左联接显示流中的列，但不显示表中的列 [英] Ksql: Left Join Displays columns from stream but not tables

问题描述

我在KSQL中有一个蒸汽和一个表，如下所述:

I have one steam and a table in KSQL as mentioned below:

流名称:DEAL_STREAM

Stream name: DEAL_STREAM

表名:EXPENSE_TABLE

Table name: EXPENSE_TABLE

当我运行以下查询时，它仅显示流中的列，但不显示任何表列.

When I run the below queries it displays only columns from the stream but no table columns are being displays.

这是预期的输出.如果不是我做错了什么?

Is this the expected output. If not am I doing something wrong?

SELECT TD.EXPENSE_CODE, TD.BRANCH_CODE, TE.EXPENSE_DESC
FROM DEAL_STREAM TD
LEFT JOIN EXPENSE_TABLE TE ON TD.EXPENSE_CODE = TE.EXPENSE_CODE
WHERE TD.EXPENSE_CODE LIKE '%NL%' AND TD.BRANCH_CODE LIKE '%AM%';

查询的输出如下所示.

NL8232@#0          | AM   | null
NL0232@#0          | AM   | null
NL6232@!0          | AM   | null
NL5232^%0          | AM   | null

推荐答案

当我运行以下查询时，它仅显示流中的列，但不显示任何表列.

When I run the below queries it displays only columns from the stream but no table columns are being displays.

在流表(左侧)联接中，如果联接/时表中没有匹配的记录，则输出记录将包含 null 列(用于表侧列)查找.

In a stream-table (left) join, the output records will contain null columns (for table-side columns) if there is not matching record in the table at the time of the join/lookup.

这是预期的输出.如果不是我做错了什么?

Is this the expected output. If not am I doing something wrong?

例如，是否有可能在将(2)输入数据写入表之前先将(1)输入数据写入流中?如果是这样，那么当表中尚无此类查找数据时，流表联接查询将在(1)时尝试执行表查找(因为稍后在时间(2)发生).因为没有可用的此类表数据，所以联接在表端列为 null 的地方写入了输出记录.

Is it possible that, for example, you wrote the (1) input data into the stream before you wrote (2) the input data into the table? If so, then the stream-table join query would have attempted to perform table-lookups at the time of (1) when no such lookup data was available in the table yet (because that happened later at time (2)). Because there was no such table data available, the join wrote output records where the table-side columns were null.

注意:KSQL中的这种流表联接(以及扩展的Apache Kafka的Streams API(基于KSQL构建))是流世界中联接的基本规范.在这里，只有流表连接的流侧将触发下游连接输出，如果在连接新输入记录时表侧的流记录不匹配，则表侧列将为 null .但是，由于这是造成用户混乱的常见原因，因此我们目前正在努力将表输出的联接输出触发器添加到Apache Kafka的Streams API和KSQL中.如果有此功能可用，那么上面的问题将不再发生.

Note: This stream-table join in KSQL (and, by extension, Apache Kafka's Streams API, on which KSQL is built) is the pretty much the norm for joins in the streaming world. Here, only the stream-side of the stream-table join will trigger downstream join outputs, and if there's no matching for a stream record on the table-side at the time when a new input record is being joined, then the table-side columns will be null. Since this is, however, a common cause of user confusion, we are currently working on adding table-side triggering of join output to Apache Kafka's Streams API and KSQL. When such a feature is available, then your issue above would not happen anymore.

这篇关于Ksql:左联接显示流中的列，但不显示表中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！