twalthr commented on a change in pull request #9545: [FLINK-13355][docs] Add
documentation for Temporal Table Join in blink planner
URL: https://github.com/apache/flink/pull/9545#discussion_r319479489
##########
File path: docs/dev/table/streaming/joins.md
##########
@@ -222,4 +222,131 @@ By definition of event time, [watermarks]({{
site.baseurl }}/dev/event_time.html
forward in time and discard versions of the build table that are no longer
necessary because no incoming row with
lower or equal timestamp is expected.
+Join with a Temporal Table
+--------------------------
+
+**Notes:** This is only supported in blink planner.
+
+A join with a temporal table joins an append/upsert/retract stream (left
input/probe side) with a temporal table (right input/build side),
+e.g., an external dimension table that changes over time. Please check the
corresponding page for more information about [temporal
tables](temporal_tables.html#temporal-table).
+
+The following example shows an `Orders` stream that should be joined with the
continuously changing currency rates table `LatestRates`.
+
+`LatestRates` is a dimension table that is materialized with the latest rate.
At time `10:15`, `10:30`, `10:52`, the content of `LatestRates` looks as
follows:
+
+{% highlight sql %}
+10:15> SELECT * FROM LatestRates;
+
+currency rate
+======== ======
+US Dollar 102
+Euro 114
+Yen 1
+
+10:30> SELECT * FROM LatestRates;
+
+currency rate
+======== ======
+US Dollar 102
+Euro 114
+Yen 1
+
+
+10:52> SELECT * FROM LatestRates;
+
+currency rate
+======== ======
+US Dollar 102
+Euro 116 <==== changed from 114 to 116
+Yen 1
+{% endhighlight %}
+
+The content of `LastestRates` at time `10:15` and `10:30` are equal. The Euro
rate has changed from 114 to 116 at `10:52`.
+
+`Orders` is an append-only table that represents payments for the given
`amount` and the given `currency`.
+For example at `10:15` there was an order for an amount of `2 Euro`.
+
+{% highlight sql %}
+SELECT * FROM Orders;
+
+amount currency
+====== =========
+ 2 Euro <== arrived at time 10:15
+ 1 US Dollar <== arrived at time 10:30
+ 2 Euro <== arrived at time 10:52
+{% endhighlight %}
+
+Given that we would like to calculate the amount of all `Orders` converted to
a common currency (`Yen`).
+
+For example, we would like to convert the following orders using the latest
rate in `LatestRates`. The result would be:
+
+{% highlight text %}
+amount currency rate amout*rate
+====== ========= ======= ============
+ 2 Euro 114 228 <== arrived at time 10:15
+ 1 US Dollar 102 102 <== arrived at time 10:30
+ 2 Euro 116 232 <== arrived at time 10:52
+{% endhighlight %}
+
+
+With the help of temporal table join, we can express such a query in SQL as:
+
+{% highlight sql %}
+SELECT
+ o.amout, o.currency, r.rate, o.amount * r.rate
+FROM
+ Orders AS o
+ JOIN LatestRates FOR SYSTEM_TIME AS OF o.proctime AS r
+ ON r.currency = o.currency
+{% endhighlight %}
+
+Each record from the probe side will be joined with the current version of the
build side table. In our example, the query is using the processing-time
notion, so a newly appended order would always be joined with the most recent
version of `LatestRates` when executing the operation.
Review comment:
Mention that the result is not deterministic for processing-time.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
With regards,
Apache Git Services
