ksql查询以显示接收方和发送方信息

pkmbmrz7 于 2021-06-04 发布在 Kafka

关注(0)|答案(2)|浏览(307)

我有一张table叫 users 看起来是这样的：

+----+----------------+
 | id | name           | 
 +----+----------------+
 |  1 | Blake          | 
 |  2 | Jenn           | 
 +----+----------------+

我有一条小溪叫 transactions 看起来是这样的：

+----+----------------+----------------+
 | id | sender         | receiver       | 
 +----+----------------+----------------+
 |  1 | 1              | 2              |
 |  2 | 2              | 1              |
 +----+----------------+----------------+

所以基本上我想要的结果是这样的：

+----+----------------+----------------+----------------+----------------+
 | id | sender         | sender_name    | receiver       |receiver_name  |
 +----+----------------+----------------+----------------+----------------+
 |  1 | 1              | Blake          | 2              | Jenn           |
 |  2 | 2              | Jenn           | 1              | Blake          |
 +----+----------------+----------------+----------------+----------------+

我只加入了由发送方或接收方分区的流和表，因此只能获取发送方信息或接收方信息。

apache-kafka ksqldb

来源：https://stackoverflow.com/questions/57725537/ksql-query-to-display-receiver-and-sender-info

2条答案

按热度按时间

mzaanser1#

你可以用两次连在一起 users 表以获得预期结果：

SELECT TR.id, TR.sender, SE.name AS sender_name, TR.receiver, RE.name AS receiver_name
FROM transactions TR
JOIN users SE ON SE.id = TR.sender
JOIN users RE ON RE.id = TR.receiver

赞(0）回复(0）举报 2021-06-05

ulydmbyx2#

关键是要创建一个流来显示它，我已经尝试了您实际描述的方法，但我认为它在ksql上不起作用，因为它需要一个“；”在第一次内部连接之后。

Statement: create stream  userstransactionsjoinedfinal as select t.txid,t.sender,    
up1.firstname as senderfirstname,up1.lastname as senderlastname,up1.phonenumber as 
senderphonenumber,up1.email as senderemail,t.receiver,up2.firstname as 
receiverfirstname,up2.lastname as receiverlastname,up2.phonenumber as 
receiverphonenumber,up2.email as receiveremail,  t.SENDERWALLETID,  
t.RECEIVERWALLETID,t.status,t.type,t.amount,t.totalfee from transactionsrekeyed 
inner join usersnow up1 on up1.id=t.sender inner join usersnow up2 on up2.id = 
t.receiver;
Caused by: line 1:483: mismatched input 'inner' expecting ';'
Caused by: org.antlr.v4.runtime.InputMismatchException

赞(0）回复(0）举报 2021-06-04

我来回答

ksql查询以显示接收方和发送方信息

2条答案

相关问题

热门标签

最新问答