Hi Martijn, Thanks a lot for your attention. I'm sorry I didn't explain the motivation clearly. I would like to explain it in detail, and then give response on your questions. A lookup join is typically used to enrich a table with data that is queried from an external system. Many Lookup table sources introduce cache in order to reduce the RPC call, such as JDBC, CSV, HBase connectors. For those connectors, we could raise cache hit ratio by routing the same lookup keys to the same task instance. This is the purpose of https://cwiki.apache.org/confluence/display/FLINK/FLIP-204%3A+Introduce+Hash+Lookup+Join . Other cases might benefit from Hash distribution, such as batch hash join as you mentioned. It is a cool idea, however it is not the purpose of this FLIP, we could discuss this in FLINK-20670 <https://issues.apache.org/jira/browse/FLINK-20670>.😀
> - When I was reading about this topic [1] I was wondering if this feature would be more beneficial for bounded use cases and not so much for unbounded use cases. What do you think? As mentioned before, the purpose of Hash Lookup Join is to increase the cache hit ratio which is different from Oracle Hash Join. However we could use the similar hint syntax. > - If I look at the current documentation for SQL Hints in Flink [2], I notice that all of the hints there are located at the end of the SQL statement. In the FLIP, the use_hash is defined directly after the 'SELECT' keyword. Can we somehow make this consistent for the user? Or should the user be able to specify hints anywhere in its SQL statement? Calcite supports hints in two locations [3]: Query Hint: right after the SELECT keyword; Table Hint: right after the referenced table name. Now Flink has supported dynamic table options based on the Hint framework of Calcite which is mentioned in doc[2]. Besides, query hints are also important, it could give a hint for optimizers to choose a better plan. Almost all popular databases and big-data engines support sql query hints, such as oracle, hive, spark and so on. I think using query hints in this case is more natural for users, WDYT? I have updated the motivation part in the FLIP, Thanks for the feedback! [1] https://logicalread.com/oracle-11g-hash-joins-mc02/#.V5Wm4_mnoUI [2] https://nightlies.apache.org/flink/flink-docs-stable/docs/dev/table/sql/queries/hints/ [3] https://calcite.apache.org/docs/reference.html#sql-hints Best, Jing Zhang Martijn Visser <mart...@ververica.com> 于2021年12月28日周二 22:02写道： > Hi Jing, > > Thanks a lot for the explanation and the FLIP. I definitely learned > something when reading more about `use_hash`. My interpretation would be > that the primary benefit of a hash lookup join would be improved > performance by allowing the user to explicitly optimise the planner. > > I have a couple of questions: > > - When I was reading about this topic [1] I was wondering if this feature > would be more beneficial for bounded use cases and not so much for > unbounded use cases. What do you think? > - If I look at the current documentation for SQL Hints in Flink [2], I > notice that all of the hints there are located at the end of the SQL > statement. In the FLIP, the use_hash is defined directly after the 'SELECT' > keyword. Can we somehow make this consistent for the user? Or should the > user be able to specify hints anywhere in its SQL statement? > > Best regards, > > Martijn > > [1] https://logicalread.com/oracle-11g-hash-joins-mc02/#.V5Wm4_mnoUI > [2] > > https://nightlies.apache.org/flink/flink-docs-stable/docs/dev/table/sql/queries/hints/ > > > On Tue, 28 Dec 2021 at 08:17, Jing Zhang <beyond1...@gmail.com> wrote: > > > Hi everyone, > > Look up join > > < > > > https://nightlies.apache.org/flink/flink-docs-master/docs/dev/table/sql/queries/joins/#lookup-join > > >[1] > > is > > commonly used feature in Flink SQL. We have received many optimization > > requirements on look up join. For example: > > 1. Enforces left side of lookup join do a hash partitioner to raise cache > > hint ratio > > 2. Solves the data skew problem after introduces hash lookup join > > 3. Enables mini-batch optimization to reduce RPC call > > > > Next we will solve these problems one by one. Firstly, we would focus on > > point 1, and continue to discuss point 2 and point 3 later. > > > > There are many similar requirements from user mail list and JIRA about > hash > > Lookup Join, for example: > > 1. FLINK-23687 <https://issues.apache.org/jira/browse/FLINK-23687> - > > Introduce partitioned lookup join to enforce input of LookupJoin to hash > > shuffle by lookup keys > > 2. FLINK-25396 <https://issues.apache.org/jira/browse/FLINK-25396> - > > lookupjoin source table for pre-partitioning > > 3. FLINK-25262 <https://issues.apache.org/jira/browse/FLINK-25262> - > > Support to send data to lookup table for KeyGroupStreamPartitioner way > for > > SQL. > > > > In this FLIP, I would like to start a discussion about Hash Lookup Join. > > The core idea is introducing a 'USE_HASH' hint in query. This syntax is > > directly user-oriented and therefore requires careful design. > > There are two ways about how to propagate this hint to LookupJoin in > > optimizer. We need further discussion to do final decide. Anyway, the > > difference between the two solution is only about the internal > > implementation and has no impact on the user. > > > > For more detail on the proposal: > > > > > https://cwiki.apache.org/confluence/display/FLINK/FLIP-204%3A+Introduce+Hash+Lookup+Join > > > > > > Looking forward to your feedback, thanks. > > > > Best, > > Jing Zhang > > > > [1] > > > > > https://nightlies.apache.org/flink/flink-docs-master/docs/dev/table/sql/queries/joins/#lookup-join > > >
