Lookup Join 通常用于通过连接外部表(维度表)补充信息,要求一个表具有处理时间属性,另一个表使 Lookup Source Connector(查看 Connector 特性)。
Lookup join 使用上面的处理时间 Temporal Join 语法,右侧的表支持 Lookup Source Connector。
-- Customers is backed by the JDBC connector and can be used for lookup joins CREATE TEMPORARY TABLE Customers ( id INT, name STRING, country STRING, zip STRING ) WITH ( 'connector' = 'jdbc', 'url' = 'jdbc:mysql://mysqlhost:3306/customerdb', 'table-name' = 'customers' ); -- enrich each order with customer information SELECT o.order_id, o.total, c.country, c.zip FROM Orders AS o JOIN Customers FOR SYSTEM_TIME AS OF o.proc_time AS c ON o.customer_id = c.id;
在上面的示例中,Orders 表使用 MySQL 的 Customers 表的数据连接维度信息。在 Customer 的行更新时,联接结果不会更新。Lookup join 连接还需要一个强制的相等连接谓词(o.customer_id = c.id)。
参考:
https://www.cnblogs.com/Springmoon-venn/p/14862865.html