流计算流数据连表mysql数据库的表数据数据库的表更新流计算变动的汇率 JOIN org.apache.flink.table.api.TableException: Cannot generate a valid execution plan for the given query

实践：

借助维表，将mysql中的数据变动同步到flink的计算中

CREATE TEMPORARY TABLE 与 CREATE TABLE 区别结论建议：一个是创建作业运行使用的临时表，一个是创建Flink项目空间的表。执行CREATE TABLE创建的表可以在元数据这里看到，后续作业中可以直接指定查询写入该表。

Dynamic Tables | Apache Flink https://nightlies.apache.org/flink/flink-docs-release-1.17/docs/dev/table/concepts/dynamic_tables/

Temporal Joins #

A Temporal table is a table that evolves over time - otherwise known in Flink as a dynamic table. Rows in a temporal table are associated with one or more temporal periods and all Flink tables are temporal(dynamic). The temporal table contains one or more versioned table snapshots, it can be a changing history table which tracks the changes(e.g. database changelog, contains all snapshots) or a changing dimensioned table which materializes the changes(e.g. database table which contains the latest snapshot).

Event Time Temporal Join #

Event Time temporal joins allow joining against a versioned table. This means a table can be enriched with changing metadata and retrieve its value at a certain point in time.

Temporal joins take an arbitrary table (left input/probe site) and correlate each row to the corresponding row’s relevant version in the versioned table (right input/build side). Flink uses the SQL syntax of FOR SYSTEM_TIME AS OF to perform this operation from the SQL:2011 standard. The syntax of a temporal join is as follows;

SELECT [column_list]
FROM table1 [AS <alias1>]
[LEFT] JOIN table2 FOR SYSTEM_TIME AS OF table1.{ proctime | rowtime } [AS <alias2>]
ON table1.column-name1 = table2.column-name1

With an event-time attribute (i.e., a rowtime attribute), it is possible to retrieve the value of a key as it was at some point in the past. This allows for joining the two tables at a common point in time. The versioned table will store all versions - identified by time - since the last watermark.

For example, suppose we have a table of orders, each with prices in different currencies. To properly normalize this table to a single currency, such as USD, each order needs to be joined with the proper currency conversion rate from the point-in-time when the order was placed.

实践：

1、

-- 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;