Page History

...

1. The lookup sources that can be connected as streams (can use other types of join)
2. Fixed delayed processing for all input data (should use other lighter ways to solve)
3. Do not support retry on exception (let the sql connectors handle it)

ref:

1. FLIP-232: Add Retry Support For Async I/O In DataStream API
2. FLINK-27625:  Add query hint for async lookup join
3. FLIP-204: Introduce Hash Lookup Join

...

New query hints: 'ASYNC_LOOKUP_MISS_RETRY'

kv form only

ASYNC_LOOKUP_MISS_RETRY('table'='tableName', 'predicate'='retry-predicate', 'strategy'='retry-strategy', 'delay'='duration','max-attempts'='int')
options:
table: table name of lookup source
predicate: empty-result|exception|empty-result-or-exception
strategy: fixed-delay
delay: duration, e.g., 10s
max-attempts: integer, e.g., 3

...

ASYNC_LOOKUP_MISS_RETRY('table'='dim1', 'predicate'='empty-result', 'strategy'='fixed-delay', 'delay'='10s','max-attempts'='3')

...

ASYNC_LOOKUP_MISS_RETRY('table'='dim1', 'output-mode'='allow-unordered', 'capacity'='100', 'timeout'='180s', 'trigger'='empty-result', 'strategy'='fixed-delay', 'delay'='10s','max-attempts'='3')

...

Why not merge the two hints into single one?

1. Add 'miss_retry' postfix to the hint name can reduce the option name length(avoid use retry prefix for every option)

   1. compare the two hints:

   Code Block
   language sql
   ASYNC_LOOKUP_MISS_RETRY('table'='dim1', 'predicatestrategy'='emptyfixed-resultdelay', 'strategydelay'='fixed-delay10s'... Vs ASYNC_LOOKUP('table'='dim1', 'miss-retry-predicatestrategy'='emptyfixed-resultdelay', 'miss-retry-strategydelay'='fixed-delay10s'...

   
   

2. Consider the option length, there's still a chance that we can support simple form option later for ASYNC_LOOKUP without retry if needed, but no chance for the one with retry since too many options. So use two hints to keep the chance for future.

...

-- async retry triggered by empty result, using 10s fixed-delay strategy, max attempts 3. 
SELECT /*+ ASYNC_LOOKUP_MISS_RETRY('table'='Customers', 'predicate'='empty-result', 'strategy'='fixed-delay',  '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;

...

1. Define Hint Strategy, add 'ASYNC_LOOKUP_MISS_RETRY' and related hint parsing for simple and kv form hint options. (Original hints propagate via FlinkLogicalJoin)
2. Hint validation and convert to AsyncRetryStrategy in exec lookup join node.

...