Thank you for your response. But I already find the root cause, according to https://github.com/ververica/flink-sql-cookbook/blob/main/joins/04_lookup_joins/04_lookup_joins.md .
The join requires one table to have a processing time attribute <https://docs.ververica.com/user_guide/sql_development/table_view.html#processing-time-attributes> and the other table to be backed by a lookup source connector <https://docs.ververica.com/user_guide/sql_development/connectors.html#id1>, like the JDBC connector. yuxia <luoyu...@alumni.sjtu.edu.cn> 于2022年11月14日周一 09:25写道： > Could you please show us the detail exception? You can find it in > FLINK_HOME/log > > Best regards, > Yuxia > > ------------------------------ > *发件人: *"Si-li Liu" <unix...@gmail.com> > *收件人: *"User" <user@flink.apache.org> > *发送时间: *星期六, 2022年 11 月 12日 下午 4:27:54 > *主题: *How to use lookup join sql hint > > I try to use this sql to insert my data to doris, and my Flink version is > 1.16.0. After check > https://nightlies.apache.org/flink/flink-docs-release-1.16/docs/dev/table/sql/queries/hints/#join-hints > . > Table scrm_admin_dept_user and table scrm_admin_dept use jdbc conector and > scrm_admin_user use mysql-cdc connector. > > insert into dim_scrm_user_dept_doris_sink > select /*+ > LOOKUP('table'='scrm_admin_dept_user'),LOOKUP('table'='scrm_admin_dept') */ > a.id, b.dept_id, a.name as name, c.name as dept_name, a.tenant_id from > scrm_admin_user a > join scrm_admin_dept_user b > on a.id = b.user_id > join scrm_admin_dept c > on b.dept_id = c.id > where a.deleted = false and b.deleted = false and c.deleted = false; > > But flink reject my sql with this error message: > [ERROR] Could not execute SQL statement. Reason: > org.apache.flink.table.client.gateway.SqlExecutionException: Could not > execute SQL statement. > > Afer I remove this sql hint, the sql can be submitted successfully. Did I > get anything wrong with this lookup join hint? And also, I'm new to Flink > SQL, does this pipeline diagram mean I already use lookup join?[image: > 截屏2022-11-12 下午4.20.48.png] > > -- > Best regards > > Sili Liu > > -- Best regards Sili Liu
