flink sql lookup join中维表不可以是视图吗？
lookup join用的维表需要从两张mysql表做关联后得到，因此创建了一个视图。但发现flink sql不支持lookup join关联视图，会抛 Temporal Table Join requires primary key in versioned table, but no primary key can be found.
请问这种情况要怎么解决？
CREATE VIEW query_mer_view (mer_cust_id, update_time) AS SELECT a.mer_cust_id, k.update_time FROM ka_mer_info k INNER JOIN adp_mer_user_info a on k.mer_cust_id = a.mer_cust_id where k.mer_cust_id <> '';
SELECT DATE_FORMAT(c.create_time, 'yyyy-MM-dd') AS payment_date, c.mer_cust_id,
c.trans_amt, CASE c.trans_stat WHEN 'S' THEN c.trans_amt ELSE 0 END as succ_amt ,
1 as trans_cnt, CASE c.trans_stat WHEN 'S' THEN 1 ELSE 0 END as succ_cnt , CASE c.trans_stat WHEN 'F' THEN 1 ELSE 0 END as fail_cnt
FROM charge_log as c LEFT JOIN query_mer_view FOR SYSTEM_TIME AS OF c.proc_time AS q ON c.mer_cust_id = q.mer_cust_id;*来自志愿者整理的flink
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
-
Hi, 如果兩次 left join 的話是否滿足你的需求呢？ 然後在取 temporal table 的字段時，用 IF 去判斷取值。參考 SQL 如下
SELECT c.mer_cust_id, IF(k.mer_cust_id IS NOT NULL AND a.mercust_id IS NOT NULL AND k.mer_cust_id <> '', k.update_time, NULL) AS update_time FROM charge_log as c LEFT JOIN ka_mer_info FOR SYSTEM_TIME AS OF c.proc_time AS k ON c.mer_cust_id = k.mer_cust_id LEFT JOIN adp_mer_user_info FOR SYSTEM_TIME AS OF c.proc_time AS a ON c.mer_cust_id = a.mer_cust_id
不過，這種寫法只能適用在兩張 MySQL 表都保證 mer_cust_id 是唯一主鍵的狀況下。如果 mer_cust_id 不是唯一的話，輸出的結果數量會跟原本提供的 SQL 期望的輸出不一致 比如說 ka_mer_info 有 0 筆數據， adp_mer_user_info 有 2 筆數據，原先的 SQL 會得到 1 筆 left join 沒成功的數據，上面提供的 SQL 則會輸出 2 筆。*来自志愿者整理的flink
2021-12-08 19:45:52赞同 展开评论