temporal join against versioned table

[Fruzsina Piroska Nagy <fr...@cloudera.com>]

Hi,

I have a question about event time temporal joins against versioned tables.

I am going through this trainging: https://github.com/ververica/sql-training/wiki/Introduction-to-SQL-on-Flink <https://github.com/ververica/sql-training/wiki/Introduction-to-SQL-on-Flink>
without the Flink SQL Client and having trouble solving the below temporal table join excercise:
https://github.com/ververica/sql-training/wiki/Joining-Dynamic-Tables#temporal-table-joins <https://github.com/ververica/sql-training/wiki/Joining-Dynamic-Tables#temporal-table-joins>

Since I cannot define a Temporal Table Function — provided by the original training environment — in SQL, first I created a Versioned Table View as described here: https://nightlies.apache.org/flink/flink-docs-release-1.14/docs/dev/table/concepts/versioned_tables/#versioned-table-views <https://nightlies.apache.org/flink/flink-docs-release-1.14/docs/dev/table/concepts/versioned_tables/#versioned-table-views>

CREATE VIEW drivers AS (SELECT *
FROM (
   SELECT *,
     ROW_NUMBER() OVER (PARTITION BY taxiId
       ORDER BY eventTime DESC) AS rownum
   FROM driver_changes)
WHERE rownum = 1)

And then — in order to enrich the `rides` with the `driverId` — I tried the below query to join the table `rides` against the versioned view based on this description: 
https://nightlies.apache.org/flink/flink-docs-release-1.14/docs/dev/table/sql/queries/joins/#event-time-temporal-join <https://nightlies.apache.org/flink/flink-docs-release-1.14/docs/dev/table/sql/queries/joins/#event-time-temporal-join>

SELECT * FROM rides
LEFT JOIN drivers FOR SYSTEM_TIME AS OF rides.eventTime
ON rides.taxiId = drivers.taxiId

Then I get the following error message: [ERROR] Job execution failed: Event-Time Temporal Table Join requires both primary key and row time attribute in versioned table, but no row time attribute can be found.

Do you have any idea, why the versioned view doesn’t have the required time attribute?

Thank you in advance.

Fruzsina