尝试加入表和流时出错 [英] Error when trying to join a table and a stream

问题描述

我正在尝试加入一个表和一个流并创建另一个表，如下所示:

I am trying to join a table and a stream and create another table as shown below:

CREATE TABLE table_fx_latest AS
   SELECT t1.currencyid,
          t1.maxtimestamp,
          t2.midprice 
  FROM stream_fx2 t2 LEFT JOIN table_fx_latest3 t1 
  ON t1.currencyid = t2.currencyid AND 
     t1.timestamp = t2.maxtimestamp 
  GROUP BY t1.currencyid, 
           t1.maxtimestamp, 
           t2.midprice;

但是报如下错误:

Cannot RUN execution plan for this statement, CreateTableAsSelect{name=TABLE_FX_LATEST_PRICE6, query=Query{queryBody=QuerySpecification{select=Select{distinct=false, selectItems=[T1.CURRENCYID T1_CURRENCYID, T1.MAXTIMESTAMP MAXTIMESTAMP, T2.MIDPRICE MIDPRICE]}, from=Join{type=LEFT, left=AliasedRelation{relation=STREAM_FX2, alias=T2}, right=AliasedRelation{relation=TABLE_FX_LATEST3, alias=T1}, criteria=Optional[JoinOn{((T1.CURRENCYID = T2.CURRENCYID) AND (T2.TIMESTAMP = T1.MAXTIMESTAMP))}]}, =null, where=null, groupBy=Optional[GroupBy{isDistinct=false, groupingElements=[SimpleGroupBy{columns=[T1.CURRENCYID]}, SimpleGroupBy{columns=[T1.MAXTIMESTAMP]}, SimpleGroupBy{columns=[T2.MIDPRICE]}]}], having=null, orderBy=[], limit=null}, orderBy=[]}, notExists=false, properties={}}
Caused by: io.confluent.ksql.parser.tree.LogicalBinaryExpression cannot be cast to io.confluent.ksql.parser.tree.ComparisonExpression

这里是 stream_fx2 流和 table_fx_latest3 表的描述:

And here's the description for both stream_fx2 stream and table_fx_latest3 table:

ksql> describe stream_fx2;

Field      | Type
----------------------------------------
ROWTIME    | BIGINT           (system)
ROWKEY     | VARCHAR(STRING)  (system)
ID         | INTEGER
CURRENCY   | VARCHAR(STRING)
CURRENCYID | INTEGER
TIMESTAMP  | BIGINT
BIDPRICE   | DOUBLE
MIDPRICE   | DOUBLE
OFFERPRICE | DOUBLE

ksql> describe table_fx_latest3;

Field        | Type
------------------------------------------
ROWTIME      | BIGINT           (system)
ROWKEY       | VARCHAR(STRING)  (system)
CURRENCYID   | INTEGER          (key)
MAXTIMESTAMP | BIGINT
------------------------------------------

我猜这可能是 KSQL 的一个错误(仍在开发者预览版中)，但我想确保我没有遗漏任何东西.任何帮助将非常感激.

I guess that this might be a bug of KSQL (still in developer preview) but I wanted to make sure that I am not missing anything. Any help would be much appreciated.

推荐答案

JOIN 条件应该只是键相等.如果您有任何其他条件，您需要将它们放在 WHERE 子句中.请尝试以下操作:

The JOIN criteria should only be key equality. If you have any other criteria you need to put them in the WHERE clause. Try the following:

CREATE TABLE table_fx_latest AS
  SELECT t1.currencyid,
         t1.maxtimestamp,
         t2.midprice 
  FROM stream_fx2 t2 LEFT JOIN table_fx_latest3 t1 
    ON t1.currencyid = t2.currencyid
  WHERE 
    t1.timestamp = t2.maxtimestamp 
  GROUP BY t1.currencyid, 
           t1.maxtimestamp, 
           t2.midprice;

告诉我这是否适合您.

这篇关于尝试加入表和流时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！