尝试连接表和流时发生错误 [英] 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 表：

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;

让我知道这是否对您有用。

Let me know if this works for you.

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