THIS IS A TEST INSTANCE. ALL YOUR CHANGES WILL BE LOST!!!!
...
| option type | option name | optional | value type | default value | description |
|---|---|---|---|---|---|
| table name | table | N | string | N/A | the table name of the lookup source |
async | async | Y | boolean | N/A | value can be 'true' or 'false' to suggest the planner choose the corresponding lookup function. If the backend lookup source does not support the suggested lookup mode, it will take no effect. |
| output-mode | Y | string | ordered | value can be 'ordered' or 'allow-_unordered'. 'allow-_unordered' means if users allow unordered result, it will attempt to use AsyncDataStream.OutputMode.UNORDERED when it does not affect the correctness of the result, otherwise ORDERED will be still used. It is consistent with `ExecutionConfigOptions#TABLE_EXEC_ASYNC_LOOKUP_OUTPUT_MODE`. | |
| capacity | Y | integer | 100 | the buffer capacity for the backend asyncWaitOperator of the lookup join operator. | |
| timeout | Y | duration | 300s | timeout from first invoke to final completion of asynchronous operation, may include multiple retries, and will be reset in case of failover | |
retry | retry-predicate | Y | string | N/A | can be 'lookup-_miss' which will enable retry if lookup result is empty. |
| retry-strategy | Y | string | N/A | can be 'fixed-_delay' | |
| fixed-delay | Y | duration | N/A | delay time for the 'fixed-_delay' strategy strategy | |
| max-attempts | Y | integer | N/A | max attempt number of the 'fixed-_delay' strategy strategy |
For these connectors which can have both capabilities of async and sync lookup, our advice for the connector developers are implementing both sync and async interfaces if both capabilities have suitable use cases, the planner will prefer the async one by default, and users can give different option value 'async'='true|false' via the LOOKUP query hint to suggest the planner, otherwise choose one interface to implement.
...
| Code Block | ||
|---|---|---|
| ||
LOOKUP('table'='dim1', 'async'='true', 'output-mode'='allow-_unordered', 'capacity'='100', 'timeout'='180s') |
...
| Code Block | ||
|---|---|---|
| ||
1. LOOKUP('table'='dim1', 'async'='true', 'output-mode'='allow-_unordered')
2. LOOKUP('table'='dim1', 'async'='true', 'timeout'='300s') |
...
| Code Block | ||
|---|---|---|
| ||
1. LOOKUP('table'='dim1', 'async'='true', 'output-mode'='allow-_unordered', 'capacity'='100', 'timeout'='180s')
2. LOOKUP('table'='dim1', 'async'='true', 'output-mode'='ordered', 'capacity'='100', 'timeout'='300s') |
...
The hint option 'retry-predicate'='lookup-_miss' can enable retry on both sync and async lookup. Retry related hint options:
| Code Block | ||
|---|---|---|
| ||
'retry-strategy'='fixed-_delay' 'fixed-delay'='10s' 'max-attempts'='3' |
...
| Code Block | ||
|---|---|---|
| ||
LOOKUP('table'='dim1', 'async'='true', 'retry-predicate'='lookup-_miss', 'retry-strategy'='fixed-_delay', 'fixed-delay'='10s','max-attempts'='3') |
...
| Code Block | ||
|---|---|---|
| ||
LOOKUP('table'='dim1', 'async'='false', 'retry-predicate'='lookup-_miss', 'retry-strategy'='fixed-_delay', 'fixed-delay'='10s','max-attempts'='3') |
...
| Code Block | ||
|---|---|---|
| ||
LOOKUP('table'='dim1', 'retry-predicate'='lookup-_miss', 'retry-strategy'='fixed-_delay', 'fixed-delay'='10s','max-attempts'='3') |
For the retry strategy, we plan to support a fixed-_delay retry strategy first, and this can be extended in the future.
...
| Code Block | ||
|---|---|---|
| ||
-- retry triggered by empty result, using 10s fixed-delay strategy, max attempts 3.
SELECT /*+ LOOKUP('table'='Customers', 'retry-predicate'='lookup-_miss', 'retry-strategy'='fixed-_delay', 'fixed-delay'='10s','max-attempts'='3') */
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; |
...
| Code Block | ||
|---|---|---|
| ||
-- 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', -- lookup-_miss.retry-strategy 'lookup-_miss.retry-strategy'='fixed-delay', -- 'none' by default 'lookup-_miss.retry-strategy.fixed-delay.delay' = '10 s', 'lookup-_miss.retry-strategy.fixed-delay.max-attempts' = '3', ); -- join operation stays the same. 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; |
...