关于 LEFT JOIN DimTable FOR SYSTEM_TIME AS OF 的异常求教！

[WeiXubin]

异常：column 'record_time' not found in table 'k'

异常描述：KafkaTable k 表在与维表进行 look up join 时定义了别名，之后报在 k 表中没有定义 record_time 字段。

Flink 版本： 1.12.2

// Source 表
CREATE TABLE KafkaTable (
    message STRING,
    record_time TIMESTAMP(3) METADATA FROM 'timestamp'
) WITH (
    'connector' = 'kafka',
);

// 维表
CREATE TEMPORARY TABLE DimTable (
    game_id BIGINT,
    game_name VARCHAR,
    root_game_id BIGINT,
    main_game_id BIGINT,
    platform VARCHAR
) WITH (
    'connector' = 'jdbc',
);

// 处理语句
INSERT INTO sinktable select
    // 省略字段
 from KafkaTable,LATERAL TABLE(RequestBodyColumnToRow(message,
'uid,game_id(BIGINT),platform'
)) *as k* LEFT JOIN DimTable FOR SYSTEM_TIME AS OF k.record_time as d
 ON k.game_id = d.game_id and k.platform = d.platform;



--
Sent from: http://apache-flink.147419.n8.nabble.com/

[WeiXubin]

已解决，问题在于 Kafka 不是直接 join 维表，而是先和 UDTF join，之后整体才与维表 Join。
所以之前起别名的位置有误。导致找不到字段、改造如下：

先前写法：
INSERT INTO sinktable select
    // 省略字段
 from KafkaTable,LATERAL TABLE(RequestBodyColumnToRow(message,
'uid,game_id(BIGINT),platform'
)) *as k* LEFT JOIN DimTable FOR SYSTEM_TIME AS OF k.record_time as d
 ON k.game_id = d.game_id and k.platform = d.platform;

改造写法：

INSERT INTO sinktable select
  // 省略字段
 from (
 select *
 from KafkaTable,LATERAL TABLE(RequestBodyColumnToRow(message,
'uid,game_id(BIGINT),platform'
))) as k JOIN DimTable FOR SYSTEM_TIME AS OF k.procTime as d
 ON k.game_id = d.game_id and k.platform = d.platform;




--
Sent from: http://apache-flink.147419.n8.nabble.com/