# CROSS APPLAY
SELECT
c.customerid, c.city, o.orderid
FROM Customers c, CROSS APPLAY(
SELECT
o.orderid, o.customerid
FROM Orders o
WHERE o.customerid = c.customerid
) as o
# LATERAL
SELECT
e.NAME, e.DEPTNO, d.NAME
FROM EMPS e, LATERAL (
SELECT *
FORM DEPTS d
WHERE e.DEPTNO=d.DEPTNO
) as d;
# inner join
SELECT users, tag
FROM Orders, LATERAL TABLE(unnest_udtf(tags)) t AS tag;
# left outer join,on谓词只支持 TRUE
SELECT users, tag
FROM Orders LEFT JOIN LATERAL TABLE(unnest_udtf(tags)) t AS tag ON TRUE
temporal table function 即一种特殊的 table function,evel函数的参数为时间字段
示例:
import org.apache.flink.table.functions.TemporalTableFunction;
(...)
// Get the stream and table environments.
StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
StreamTableEnvironment tEnv = StreamTableEnvironment.create(env);
// Provide a static data set of the rates history table.
List<Tuple2<String, Long>> ratesHistoryData = new ArrayList<>();
ratesHistoryData.add(Tuple2.of("US Dollar", 102L));
ratesHistoryData.add(Tuple2.of("Euro", 114L));
ratesHistoryData.add(Tuple2.of("Yen", 1L));
ratesHistoryData.add(Tuple2.of("Euro", 116L));
ratesHistoryData.add(Tuple2.of("Euro", 119L));
// Create and register an example table using above data set.// In the real setup, you should replace this with your own table.
DataStream<Tuple2<String, Long>> ratesHistoryStream = env.fromCollection(ratesHistoryData);
Table ratesHistory = tEnv.fromDataStream(ratesHistoryStream, "r_currency, r_rate, r_proctime.proctime");
tEnv.registerTable("RatesHistory", ratesHistory);
// Create and register a temporal table function.// Define "r_proctime" as the time attribute and "r_currency" as the primary key.// 创建 TemporalTableFunction
TemporalTableFunction rates = ratesHistory.createTemporalTableFunction("r_proctime", "r_currency");
// 在环境中注册 TemporalTableFunction
tEnv.registerFunction("Rates", rates);
使用:
其中Rates()是一个已经注册好的 temporalTableFunction
SELECT o.currency, o.amount, r.rate
o.amount * r.rate AS yen_amount
FROM
Orders AS o,
LATERAL TABLE (Rates(o.rowtime)) AS r
WHERE r.currency = o.currency
NULL造成的热点:left outer join时,往往一边的流数据还没有可以join的事件,导致A LEFT JOIN B 会产生大量的 (A, NULL),同时再 left join C 时,由于B中的cCol为null,所以最终数据shuffle会在同一个节点出现大量cCol为null的数据,造成热点。