ksqlDB中的n路表连接

Question

我有三个ksqldb表，其关系如下图所示。我必须加入他们。

此查询将导致错误：

CREATE TABLE `reviewer-email-user`
    AS SELECT *
    FROM USER
        INNER JOIN REVIEWER ON USER.USERID = REVIEWER.USERID
        INNER JOIN EMAILADDRESS ON USER.USERID = EMAILADDRESS.USERID
EMIT CHANGES;

错误是：

由于错误，

无法确定查询的输出架构:无效的连接条件:表表联接要求在右输入表的主键上联接。Got USER.USERID = REVIEWER.USERID

​

​

那么，如何连接这三个ksqldb表呢？谢谢。

Answer 1

表表联接期望连接条件在右侧具有主键，因此，以下内容将无法工作：

ksql> CREATE TABLE reviewer_user
> AS SELECT *
> FROM REVIEWER
>      INNER JOIN USER ON USER.user_id = REVIEWER.user_id
>EMIT CHANGES;
Could not determine output schema for query due to error: Cannot add table 'REVIEWER_USER': A table with the same name already exists
Statement: CREATE TABLE REVIEWER_USER WITH (KAFKA_TOPIC='REVIEWER_USER', PARTITIONS=2, REPLICAS=1) AS SELECT *
FROM REVIEWER REVIEWER
INNER JOIN USER USER ON ((USER.USER_ID = REVIEWER.USER_ID))
EMIT CHANGES;

但是，下面的查询确实有效(请注意，我已经翻转了联接的左侧和右侧)

ksql> CREATE TABLE reviewer_user
> AS SELECT *
> FROM REVIEWER
>      INNER JOIN USER ON REVIEWER.user_id = USER.user_id
>EMIT CHANGES;

 Message
---------------------------------------------
 Created query with ID CTAS_REVIEWER_USER_11
---------------------------------------------

表表联接的另一个限制是它不支持n路连接，因此您必须按照上面的建议执行联接来创建2个新表(reviewer_user和email_user)，然后对它们执行一个连接以获得最终结果。