You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@flink.apache.org by Luan Cooper <gc...@gmail.com> on 2021/06/07 10:56:51 UTC
sql multisink explain result is more expensive than expected
Hi
We're using multi sink in sql with view, the TestCase is
"""java
@Test
def testJoinTemporalTableWithViewWithFilterPushDown(): Unit = {
createLookupTable("LookupTableAsync1", new AsyncTableFunction1)
util.addTable(
"""
|CREATE TEMPORARY VIEW v_vvv AS
|SELECT * FROM MyTable AS T
|JOIN LookupTableAsync1 FOR SYSTEM_TIME AS OF T.proctime AS D
|ON T.a = D.id
|""".stripMargin)
val stmtSet = util.tableEnv.createStatementSet()
val appendSink1 = util.createRetractTableSink(
Array("a", "b","id","name"),
Array(INT, STRING, INT, STRING))
util.tableEnv.asInstanceOf[TableEnvironmentInternal].registerTableSinkInternal(
"appendSink1", appendSink1)
stmtSet.addInsert("appendSink1", util.tableEnv.sqlQuery("""
|SELECT
a,b,id,name FROM v_vvv
|WHERE age =
10
""".stripMargin))
stmtSet.addInsert("appendSink1", util.tableEnv.sqlQuery("""
|SELECT
a,b,id,name FROM v_vvv
|WHERE age =
30
""".stripMargin))
// util.verifyPlan(stmtSet)
util.verifyExecPlan(stmtSet)
}
class AsyncTableFunction1 extends AsyncTableFunction[RowData] {
def eval(resultFuture: CompletableFuture[JCollection[RowData]], a:
Integer): Unit = {
}
}
"""
the optimized exec plan is
"""
Calc(select=[a, b])(reuse_id=[1])
+- DataStreamScan(table=[[default_catalog, default_database, MyTable]],
fields=[a, b, c, proctime, rowtime])
LegacySink(name=[`default_catalog`.`default_database`.`appendSink1`],
fields=[a, b, id, name])
+- LookupJoin(table=[default_catalog.default_database.LookupTableAsync1],
joinType=[InnerJoin], async=[true], lookup=[age=10, id=a], where=[(age =
10)], select=[a, b, id, name])
+- Reused(reference_id=[1])
LegacySink(name=[`default_catalog`.`default_database`.`appendSink2`],
fields=[a, b, id, name])
+- LookupJoin(table=[default_catalog.default_database.LookupTableAsync1],
joinType=[InnerJoin], async=[true], lookup=[age=30, id=a], where=[(age =
30)], select=[a, b, id, name])
+- Reused(reference_id=[1])
"""
*I have 2 questions*
*1. The lookup table function execute twice, which is very expensive*
*2. the age filter is push down to LookupJoin with lookup=[age=10, id=a],
which result to function signature mismatch (exception follows blow)*
org.apache.flink.table.api.ValidationException: Could not find an
implementation method 'eval' in class
'org.apache.flink.table.planner.plan.utils.AsyncTableFunction1' for
function 'default_catalog.default_database.LookupTableAsync1, source:
[TestInvalidTemporalTable(id, name, age, ts)]' that matches the following
signature:
void eval(java.util.concurrent.CompletableFuture, java.lang.Integer,
java.lang.Integer)
Is the optimizer wrong or I'm wrong ?
Cooper.Luan
Re: sql multisink explain result is more expensive than expected
Posted by JING ZHANG <be...@gmail.com>.
Hi Cooper Luan,
As we discussed before, the root cause of exception is LookupJoin wants to
find a method that matches two lookup keys, 'age, id' after the optimizer
pushes down age=10 into LookupJoin.
There is a possible improvement to avoid throwing an exception: if not
found a matched method with two lookup keys, try to match the method with
one lookup key. Dimension tables return all records that match 'id=a', then
filter output by 'age=10' later Calc.
But in this way, we need to know whether the only argument in the `eval`
method represents 'age' or 'id'. The hint is missing in the current API. As
discussed with Jark Wu
<https://issues.apache.org/jira/secure/ViewProfile.jspa?name=jark> , We
think this improvement would involve refactoring `LookupTableSource`.
We prefer let the user handle this case by adding a `eval` method that
matches lookup keys. for example
// first solution with one eval method with variable arguments
length@SerialVersionUID(1L)class AsyncTableFunction1 extends
AsyncTableFunction[RowData] {
@varargs
def eval(resultFuture: CompletableFuture[JCollection[RowData]], a:
Integer*): Unit = {
}
}
// second solution with multiple eval method, each method with fixed
arguments length@SerialVersionUID(1L)class AsyncTableFunction1 extends
AsyncTableFunction[RowData] {
def eval(resultFuture: CompletableFuture[JCollection[RowData]], a:
Integer): Unit = {
}
def eval(resultFuture: CompletableFuture[JCollection[RowData]], a:
Integer, b: Integer): Unit = {
}
}
What do you think, Cooper Luan
<https://issues.apache.org/jira/secure/ViewProfile.jspa?name=gsavl>.
JING ZHANG <be...@gmail.com> 于2021年6月10日周四 下午4:17写道:
> Great, thanks for fixing the issue. I've attached this email to the JIRA.
>
> Luan Cooper <gc...@gmail.com> 于2021年6月10日周四 下午2:28写道:
>
>> Thanks for your explanation, it helps
>> I created a issue for this
>> https://issues.apache.org/jira/browse/FLINK-22955
>>
>>
>> On Wed, Jun 9, 2021 at 3:50 PM JING ZHANG <be...@gmail.com> wrote:
>>
>>> Hi Cooper.Luan,
>>> Sorry for the late response. I apply your patch locally.
>>> *> 1. The lookup table function execute twice, which is very expensive*
>>> As mentioned before, use `createTemporaryView` like
>>> `testJoinTemporalTableWithMultisinkWithFilterPushDown2`, could reuse
>>> LookupJoin.
>>>
>>> Then why does this work?
>>> In Flink, there is a SubGraphBasedOptimizer which decomposes the RelNode
>>> DAG into multiple common sub-graphs, and each sub-graph is a tree, and can
>>> be optimized independently by Calcite Optimizer.
>>> In `testJoinTemporalTableWithMultisinkWithFilterPushDown2`, RelNodes
>>> would be decomposed into 3 sub-graph. The first sub-graph contains
>>> LookupJoin, the second sub-graph contains sink1, the third sub-graph
>>> contains sink2. Those three sub-graph would be optimized by Calcite planner
>>> dependently.
>>>
>>> *> 2. Why *SubGraphBasedOptimizer seem not work for* `*testJoinTemporalTableWithMultisinkWithFilterPushDown1`
>>> ?
>>> StreamCommonSubGraphBasedOptimizer would skip if only one sink block,
>>> because the whole RelNode DAG is a RelNode tree itself.
>>> So `LookupJoin` and 'Calc(Filter: age=10)' belong to the same sub-gprah.
>>>
>>> *> 3. After push down to LookupJoin with lookup=[age=10, id=a], result
>>> to function signature mismatch (exception follows blow)*
>>> After push down 'age=10', optimizer wants to find a method that matches
>>> two lookup keys, 'age, id' .
>>> We could improve the case to avoid throwing an exception: if not found
>>> the most match case, try to load all records from the right table that
>>> matches 'id=a', then filter output by 'age=10' later. I would add a JIRA
>>> for this improvement.
>>> However, it is better if you could add a method that matches void
>>> eval(java.util.concurrent.CompletableFuture, java.lang.Integer,
>>> java.lang.Integer), because it is equivalent with push down predicate
>>> to right table which would reduce transfer data.
>>>
>>> Best regards,
>>> JING ZHANG
>>>
>>> Luan Cooper <gc...@gmail.com> 于2021年6月9日周三 下午2:17写道:
>>>
>>>> I should use patch instead of git repo
>>>> sorry for the inconvenience
>>>>
>>>>
>>>> On Tue, Jun 8, 2021 at 2:49 PM Luan Cooper <gc...@gmail.com> wrote:
>>>>
>>>>> I attach the testcase in
>>>>> https://github.com/CooperLuan/flink/blob/lookup_optimize/flink-table/flink-table-planner-blink/src/test/scala/org/apache/flink/table/planner/plan/stream/sql/join/LookupJoinTest.scala#L334-L399
>>>>>
>>>>> On Tue, Jun 8, 2021 at 1:50 PM JING ZHANG <be...@gmail.com>
>>>>> wrote:
>>>>>
>>>>>> Hi Cooper.Luan,
>>>>>> > *I have 2 questions*
>>>>>> * 1. The lookup table function execute twice, which is very
>>>>>> expensive*
>>>>>> * 2. the age filter is push down to LookupJoin with lookup=[age=10,
>>>>>> id=a], which result to function signature mismatch (exception follows blow)*
>>>>>>
>>>>>> [image: image.png]
>>>>>>
>>>>>> If you need reuse LookupJoin like the above plan, you could use `
>>>>>> createTemporaryView` to register LookupJoin result as a temp view,
>>>>>> the code like the following:
>>>>>> '''
>>>>>>
>>>>> you're right, the multi insert statement reuse lookup result as
>>>>> expected
>>>>> #testJoinTemporalTableWithMultisinkWithFilterPushDown2 *show right
>>>>> result*
>>>>> [image: image.png]
>>>>>
>>>>> but testJoinTemporalTableWithMultisinkWithFilterPushDown1 *is a bad
>>>>> case*
>>>>> [image: image.png]
>>>>> which result to lookup function signature mismatch
>>>>>
>>>>> diff between testJoinTemporalTableWithMultisinkWithFilterPushDown1
>>>>> and testJoinTemporalTableWithMultisinkWithFilterPushDown2
>>>>> testJoinTemporalTableWithMultisinkWithFilterPushDown1: *statement set
>>>>> with 1 insert*
>>>>> testJoinTemporalTableWithMultisinkWithFilterPushDown2: *statement set
>>>>> with 2 insert*
>>>>>
>>>>>> @Test
>>>>>> def testJoinTemporalTableWithViewWithFilterPushDown(): Unit = {
>>>>>> createLookupTable("LookupTableAsync1", new AsyncTableFunction1)
>>>>>> // util.addTable(
>>>>>> // """
>>>>>> // |CREATE TEMPORARY VIEW v_vvv AS
>>>>>> // |SELECT * FROM MyTable AS T
>>>>>> // |JOIN LookupTableAsync1 FOR SYSTEM_TIME AS OF T.proctime AS D
>>>>>> // |ON T.a = D.id
>>>>>> // |""".stripMargin)
>>>>>> val table = util.tableEnv.sqlQuery(
>>>>>> """
>>>>>> |SELECT * FROM MyTable AS T
>>>>>> |JOIN LookupTableAsync1 FOR SYSTEM_TIME AS OF T.proctime AS D
>>>>>> |ON T.a = D.id
>>>>>> |""".stripMargin)
>>>>>>
>>>>>> util.tableEnv.createTemporaryView("v_vvv", table)
>>>>>> val stmtSet = util.tableEnv.createStatementSet()
>>>>>>
>>>>>> val appendSink1 = util.createRetractTableSink(
>>>>>> Array("a", "b","id","name"),
>>>>>> Array(INT, STRING, INT, STRING))
>>>>>> util.tableEnv.asInstanceOf[TableEnvironmentInternal].registerTableSinkInternal(
>>>>>> "appendSink1", appendSink1)
>>>>>> stmtSet.addInsert("appendSink1", util.tableEnv.sqlQuery("""
>>>>>> |SELECT a,b,id,name FROM v_vvv
>>>>>> |WHERE age = 10
>>>>>> """.stripMargin))
>>>>>>
>>>>>> stmtSet.addInsert("appendSink1", util.tableEnv.sqlQuery("""
>>>>>> |SELECT a,b,id,name FROM v_vvv
>>>>>> |WHERE age = 30
>>>>>> """.stripMargin))
>>>>>>
>>>>>> // util.verifyPlan(stmtSet)
>>>>>> util.verifyExecPlan(stmtSet)
>>>>>>
>>>>>> }'''
>>>>>>
>>>>>>
>>>>>> > org.apache.flink.table.api.ValidationException: Could not find an
>>>>>> implementation method 'eval' in class
>>>>>> 'org.apache.flink.table.planner.plan.utils.AsyncTableFunction1' for
>>>>>> function 'default_catalog.default_database.LookupTableAsync1, source:
>>>>>> [TestInvalidTemporalTable(id, name, age, ts)]' that matches the following
>>>>>> signature:
>>>>>> void eval(java.util.concurrent.CompletableFuture, java.lang.Integer,
>>>>>> java.lang.Integer)
>>>>>>
>>>>>> Would you please provide complete code to produce the exception, I
>>>>>> didn't find the exception in my local environment.
>>>>>>
>>>>>> Best regards,
>>>>>> JING ZHANG
>>>>>>
>>>>>>
>>>>>> Luan Cooper <gc...@gmail.com> 于2021年6月8日周二 上午10:42写道:
>>>>>>
>>>>>>> should I post in Dev user list ?
>>>>>>>
>>>>>>> On Mon, 7 Jun 2021 at 18:56 Luan Cooper <gc...@gmail.com> wrote:
>>>>>>>
>>>>>>>> Hi
>>>>>>>>
>>>>>>>> We're using multi sink in sql with view, the TestCase is
>>>>>>>>
>>>>>>>> """java
>>>>>>>> @Test
>>>>>>>> def testJoinTemporalTableWithViewWithFilterPushDown(): Unit = {
>>>>>>>> createLookupTable("LookupTableAsync1", new AsyncTableFunction1)
>>>>>>>>
>>>>>>>> util.addTable(
>>>>>>>> """
>>>>>>>> |CREATE TEMPORARY VIEW v_vvv AS
>>>>>>>> |SELECT * FROM MyTable AS T
>>>>>>>> |JOIN LookupTableAsync1 FOR SYSTEM_TIME AS OF T.proctime AS
>>>>>>>> D
>>>>>>>> |ON T.a = D.id
>>>>>>>> |""".stripMargin)
>>>>>>>>
>>>>>>>> val stmtSet = util.tableEnv.createStatementSet()
>>>>>>>>
>>>>>>>> val appendSink1 = util.createRetractTableSink(
>>>>>>>> Array("a", "b","id","name"),
>>>>>>>> Array(INT, STRING, INT, STRING))
>>>>>>>>
>>>>>>>> util.tableEnv.asInstanceOf[TableEnvironmentInternal].registerTableSinkInternal(
>>>>>>>> "appendSink1", appendSink1)
>>>>>>>> stmtSet.addInsert("appendSink1", util.tableEnv.sqlQuery("""
>>>>>>>>
>>>>>>>> |SELECT a,b,id,name FROM v_vvv
>>>>>>>>
>>>>>>>> |WHERE age = 10
>>>>>>>> """.stripMargin))
>>>>>>>>
>>>>>>>> stmtSet.addInsert("appendSink1", util.tableEnv.sqlQuery("""
>>>>>>>>
>>>>>>>> |SELECT a,b,id,name FROM v_vvv
>>>>>>>>
>>>>>>>> |WHERE age = 30
>>>>>>>>
>>>>>>>> """.stripMargin))
>>>>>>>>
>>>>>>>> // util.verifyPlan(stmtSet)
>>>>>>>> util.verifyExecPlan(stmtSet)
>>>>>>>> }
>>>>>>>>
>>>>>>>> class AsyncTableFunction1 extends AsyncTableFunction[RowData] {
>>>>>>>> def eval(resultFuture: CompletableFuture[JCollection[RowData]],
>>>>>>>> a: Integer): Unit = {
>>>>>>>> }
>>>>>>>> }
>>>>>>>> """
>>>>>>>>
>>>>>>>> the optimized exec plan is
>>>>>>>>
>>>>>>>> """
>>>>>>>> Calc(select=[a, b])(reuse_id=[1])
>>>>>>>> +- DataStreamScan(table=[[default_catalog, default_database,
>>>>>>>> MyTable]], fields=[a, b, c, proctime, rowtime])
>>>>>>>>
>>>>>>>> LegacySink(name=[`default_catalog`.`default_database`.`appendSink1`],
>>>>>>>> fields=[a, b, id, name])
>>>>>>>> +-
>>>>>>>> LookupJoin(table=[default_catalog.default_database.LookupTableAsync1],
>>>>>>>> joinType=[InnerJoin], async=[true], lookup=[age=10, id=a], where=[(age =
>>>>>>>> 10)], select=[a, b, id, name])
>>>>>>>> +- Reused(reference_id=[1])
>>>>>>>>
>>>>>>>> LegacySink(name=[`default_catalog`.`default_database`.`appendSink2`],
>>>>>>>> fields=[a, b, id, name])
>>>>>>>> +-
>>>>>>>> LookupJoin(table=[default_catalog.default_database.LookupTableAsync1],
>>>>>>>> joinType=[InnerJoin], async=[true], lookup=[age=30, id=a], where=[(age =
>>>>>>>> 30)], select=[a, b, id, name])
>>>>>>>> +- Reused(reference_id=[1])
>>>>>>>> """
>>>>>>>>
>>>>>>>> *I have 2 questions*
>>>>>>>> *1. The lookup table function execute twice, which is very
>>>>>>>> expensive*
>>>>>>>> *2. the age filter is push down to LookupJoin with lookup=[age=10,
>>>>>>>> id=a], which result to function signature mismatch (exception follows blow)*
>>>>>>>>
>>>>>>>> org.apache.flink.table.api.ValidationException: Could not find an
>>>>>>>> implementation method 'eval' in class
>>>>>>>> 'org.apache.flink.table.planner.plan.utils.AsyncTableFunction1' for
>>>>>>>> function 'default_catalog.default_database.LookupTableAsync1, source:
>>>>>>>> [TestInvalidTemporalTable(id, name, age, ts)]' that matches the following
>>>>>>>> signature:
>>>>>>>> void eval(java.util.concurrent.CompletableFuture,
>>>>>>>> java.lang.Integer, java.lang.Integer)
>>>>>>>>
>>>>>>>> Is the optimizer wrong or I'm wrong ?
>>>>>>>>
>>>>>>>> Cooper.Luan
>>>>>>>>
>>>>>>>>
Re: sql multisink explain result is more expensive than expected
Posted by JING ZHANG <be...@gmail.com>.
Great, thanks for fixing the issue. I've attached this email to the JIRA.
Luan Cooper <gc...@gmail.com> 于2021年6月10日周四 下午2:28写道:
> Thanks for your explanation, it helps
> I created a issue for this
> https://issues.apache.org/jira/browse/FLINK-22955
>
>
> On Wed, Jun 9, 2021 at 3:50 PM JING ZHANG <be...@gmail.com> wrote:
>
>> Hi Cooper.Luan,
>> Sorry for the late response. I apply your patch locally.
>> *> 1. The lookup table function execute twice, which is very expensive*
>> As mentioned before, use `createTemporaryView` like
>> `testJoinTemporalTableWithMultisinkWithFilterPushDown2`, could reuse
>> LookupJoin.
>>
>> Then why does this work?
>> In Flink, there is a SubGraphBasedOptimizer which decomposes the RelNode
>> DAG into multiple common sub-graphs, and each sub-graph is a tree, and can
>> be optimized independently by Calcite Optimizer.
>> In `testJoinTemporalTableWithMultisinkWithFilterPushDown2`, RelNodes
>> would be decomposed into 3 sub-graph. The first sub-graph contains
>> LookupJoin, the second sub-graph contains sink1, the third sub-graph
>> contains sink2. Those three sub-graph would be optimized by Calcite planner
>> dependently.
>>
>> *> 2. Why *SubGraphBasedOptimizer seem not work for* `*testJoinTemporalTableWithMultisinkWithFilterPushDown1`
>> ?
>> StreamCommonSubGraphBasedOptimizer would skip if only one sink block,
>> because the whole RelNode DAG is a RelNode tree itself.
>> So `LookupJoin` and 'Calc(Filter: age=10)' belong to the same sub-gprah.
>>
>> *> 3. After push down to LookupJoin with lookup=[age=10, id=a], result to
>> function signature mismatch (exception follows blow)*
>> After push down 'age=10', optimizer wants to find a method that matches
>> two lookup keys, 'age, id' .
>> We could improve the case to avoid throwing an exception: if not found
>> the most match case, try to load all records from the right table that
>> matches 'id=a', then filter output by 'age=10' later. I would add a JIRA
>> for this improvement.
>> However, it is better if you could add a method that matches void
>> eval(java.util.concurrent.CompletableFuture, java.lang.Integer,
>> java.lang.Integer), because it is equivalent with push down predicate to
>> right table which would reduce transfer data.
>>
>> Best regards,
>> JING ZHANG
>>
>> Luan Cooper <gc...@gmail.com> 于2021年6月9日周三 下午2:17写道:
>>
>>> I should use patch instead of git repo
>>> sorry for the inconvenience
>>>
>>>
>>> On Tue, Jun 8, 2021 at 2:49 PM Luan Cooper <gc...@gmail.com> wrote:
>>>
>>>> I attach the testcase in
>>>> https://github.com/CooperLuan/flink/blob/lookup_optimize/flink-table/flink-table-planner-blink/src/test/scala/org/apache/flink/table/planner/plan/stream/sql/join/LookupJoinTest.scala#L334-L399
>>>>
>>>> On Tue, Jun 8, 2021 at 1:50 PM JING ZHANG <be...@gmail.com> wrote:
>>>>
>>>>> Hi Cooper.Luan,
>>>>> > *I have 2 questions*
>>>>> * 1. The lookup table function execute twice, which is very expensive*
>>>>> * 2. the age filter is push down to LookupJoin with lookup=[age=10,
>>>>> id=a], which result to function signature mismatch (exception follows blow)*
>>>>>
>>>>> [image: image.png]
>>>>>
>>>>> If you need reuse LookupJoin like the above plan, you could use `
>>>>> createTemporaryView` to register LookupJoin result as a temp view,
>>>>> the code like the following:
>>>>> '''
>>>>>
>>>> you're right, the multi insert statement reuse lookup result as expected
>>>> #testJoinTemporalTableWithMultisinkWithFilterPushDown2 *show right
>>>> result*
>>>> [image: image.png]
>>>>
>>>> but testJoinTemporalTableWithMultisinkWithFilterPushDown1 *is a bad
>>>> case*
>>>> [image: image.png]
>>>> which result to lookup function signature mismatch
>>>>
>>>> diff between testJoinTemporalTableWithMultisinkWithFilterPushDown1
>>>> and testJoinTemporalTableWithMultisinkWithFilterPushDown2
>>>> testJoinTemporalTableWithMultisinkWithFilterPushDown1: *statement set
>>>> with 1 insert*
>>>> testJoinTemporalTableWithMultisinkWithFilterPushDown2: *statement set
>>>> with 2 insert*
>>>>
>>>>> @Test
>>>>> def testJoinTemporalTableWithViewWithFilterPushDown(): Unit = {
>>>>> createLookupTable("LookupTableAsync1", new AsyncTableFunction1)
>>>>> // util.addTable(
>>>>> // """
>>>>> // |CREATE TEMPORARY VIEW v_vvv AS
>>>>> // |SELECT * FROM MyTable AS T
>>>>> // |JOIN LookupTableAsync1 FOR SYSTEM_TIME AS OF T.proctime AS D
>>>>> // |ON T.a = D.id
>>>>> // |""".stripMargin)
>>>>> val table = util.tableEnv.sqlQuery(
>>>>> """
>>>>> |SELECT * FROM MyTable AS T
>>>>> |JOIN LookupTableAsync1 FOR SYSTEM_TIME AS OF T.proctime AS D
>>>>> |ON T.a = D.id
>>>>> |""".stripMargin)
>>>>>
>>>>> util.tableEnv.createTemporaryView("v_vvv", table)
>>>>> val stmtSet = util.tableEnv.createStatementSet()
>>>>>
>>>>> val appendSink1 = util.createRetractTableSink(
>>>>> Array("a", "b","id","name"),
>>>>> Array(INT, STRING, INT, STRING))
>>>>> util.tableEnv.asInstanceOf[TableEnvironmentInternal].registerTableSinkInternal(
>>>>> "appendSink1", appendSink1)
>>>>> stmtSet.addInsert("appendSink1", util.tableEnv.sqlQuery("""
>>>>> |SELECT a,b,id,name FROM v_vvv
>>>>> |WHERE age = 10
>>>>> """.stripMargin))
>>>>>
>>>>> stmtSet.addInsert("appendSink1", util.tableEnv.sqlQuery("""
>>>>> |SELECT a,b,id,name FROM v_vvv
>>>>> |WHERE age = 30
>>>>> """.stripMargin))
>>>>>
>>>>> // util.verifyPlan(stmtSet)
>>>>> util.verifyExecPlan(stmtSet)
>>>>>
>>>>> }'''
>>>>>
>>>>>
>>>>> > org.apache.flink.table.api.ValidationException: Could not find an
>>>>> implementation method 'eval' in class
>>>>> 'org.apache.flink.table.planner.plan.utils.AsyncTableFunction1' for
>>>>> function 'default_catalog.default_database.LookupTableAsync1, source:
>>>>> [TestInvalidTemporalTable(id, name, age, ts)]' that matches the following
>>>>> signature:
>>>>> void eval(java.util.concurrent.CompletableFuture, java.lang.Integer,
>>>>> java.lang.Integer)
>>>>>
>>>>> Would you please provide complete code to produce the exception, I
>>>>> didn't find the exception in my local environment.
>>>>>
>>>>> Best regards,
>>>>> JING ZHANG
>>>>>
>>>>>
>>>>> Luan Cooper <gc...@gmail.com> 于2021年6月8日周二 上午10:42写道:
>>>>>
>>>>>> should I post in Dev user list ?
>>>>>>
>>>>>> On Mon, 7 Jun 2021 at 18:56 Luan Cooper <gc...@gmail.com> wrote:
>>>>>>
>>>>>>> Hi
>>>>>>>
>>>>>>> We're using multi sink in sql with view, the TestCase is
>>>>>>>
>>>>>>> """java
>>>>>>> @Test
>>>>>>> def testJoinTemporalTableWithViewWithFilterPushDown(): Unit = {
>>>>>>> createLookupTable("LookupTableAsync1", new AsyncTableFunction1)
>>>>>>>
>>>>>>> util.addTable(
>>>>>>> """
>>>>>>> |CREATE TEMPORARY VIEW v_vvv AS
>>>>>>> |SELECT * FROM MyTable AS T
>>>>>>> |JOIN LookupTableAsync1 FOR SYSTEM_TIME AS OF T.proctime AS D
>>>>>>> |ON T.a = D.id
>>>>>>> |""".stripMargin)
>>>>>>>
>>>>>>> val stmtSet = util.tableEnv.createStatementSet()
>>>>>>>
>>>>>>> val appendSink1 = util.createRetractTableSink(
>>>>>>> Array("a", "b","id","name"),
>>>>>>> Array(INT, STRING, INT, STRING))
>>>>>>>
>>>>>>> util.tableEnv.asInstanceOf[TableEnvironmentInternal].registerTableSinkInternal(
>>>>>>> "appendSink1", appendSink1)
>>>>>>> stmtSet.addInsert("appendSink1", util.tableEnv.sqlQuery("""
>>>>>>>
>>>>>>> |SELECT a,b,id,name FROM v_vvv
>>>>>>> |WHERE
>>>>>>> age = 10
>>>>>>> """.stripMargin))
>>>>>>>
>>>>>>> stmtSet.addInsert("appendSink1", util.tableEnv.sqlQuery("""
>>>>>>>
>>>>>>> |SELECT a,b,id,name FROM v_vvv
>>>>>>> |WHERE
>>>>>>> age = 30
>>>>>>>
>>>>>>> """.stripMargin))
>>>>>>>
>>>>>>> // util.verifyPlan(stmtSet)
>>>>>>> util.verifyExecPlan(stmtSet)
>>>>>>> }
>>>>>>>
>>>>>>> class AsyncTableFunction1 extends AsyncTableFunction[RowData] {
>>>>>>> def eval(resultFuture: CompletableFuture[JCollection[RowData]], a:
>>>>>>> Integer): Unit = {
>>>>>>> }
>>>>>>> }
>>>>>>> """
>>>>>>>
>>>>>>> the optimized exec plan is
>>>>>>>
>>>>>>> """
>>>>>>> Calc(select=[a, b])(reuse_id=[1])
>>>>>>> +- DataStreamScan(table=[[default_catalog, default_database,
>>>>>>> MyTable]], fields=[a, b, c, proctime, rowtime])
>>>>>>>
>>>>>>> LegacySink(name=[`default_catalog`.`default_database`.`appendSink1`],
>>>>>>> fields=[a, b, id, name])
>>>>>>> +-
>>>>>>> LookupJoin(table=[default_catalog.default_database.LookupTableAsync1],
>>>>>>> joinType=[InnerJoin], async=[true], lookup=[age=10, id=a], where=[(age =
>>>>>>> 10)], select=[a, b, id, name])
>>>>>>> +- Reused(reference_id=[1])
>>>>>>>
>>>>>>> LegacySink(name=[`default_catalog`.`default_database`.`appendSink2`],
>>>>>>> fields=[a, b, id, name])
>>>>>>> +-
>>>>>>> LookupJoin(table=[default_catalog.default_database.LookupTableAsync1],
>>>>>>> joinType=[InnerJoin], async=[true], lookup=[age=30, id=a], where=[(age =
>>>>>>> 30)], select=[a, b, id, name])
>>>>>>> +- Reused(reference_id=[1])
>>>>>>> """
>>>>>>>
>>>>>>> *I have 2 questions*
>>>>>>> *1. The lookup table function execute twice, which is very expensive*
>>>>>>> *2. the age filter is push down to LookupJoin with lookup=[age=10,
>>>>>>> id=a], which result to function signature mismatch (exception follows blow)*
>>>>>>>
>>>>>>> org.apache.flink.table.api.ValidationException: Could not find an
>>>>>>> implementation method 'eval' in class
>>>>>>> 'org.apache.flink.table.planner.plan.utils.AsyncTableFunction1' for
>>>>>>> function 'default_catalog.default_database.LookupTableAsync1, source:
>>>>>>> [TestInvalidTemporalTable(id, name, age, ts)]' that matches the following
>>>>>>> signature:
>>>>>>> void eval(java.util.concurrent.CompletableFuture, java.lang.Integer,
>>>>>>> java.lang.Integer)
>>>>>>>
>>>>>>> Is the optimizer wrong or I'm wrong ?
>>>>>>>
>>>>>>> Cooper.Luan
>>>>>>>
>>>>>>>
Re: sql multisink explain result is more expensive than expected
Posted by Luan Cooper <gc...@gmail.com>.
Thanks for your explanation, it helps
I created a issue for this https://issues.apache.org/jira/browse/FLINK-22955
On Wed, Jun 9, 2021 at 3:50 PM JING ZHANG <be...@gmail.com> wrote:
> Hi Cooper.Luan,
> Sorry for the late response. I apply your patch locally.
> *> 1. The lookup table function execute twice, which is very expensive*
> As mentioned before, use `createTemporaryView` like
> `testJoinTemporalTableWithMultisinkWithFilterPushDown2`, could reuse
> LookupJoin.
>
> Then why does this work?
> In Flink, there is a SubGraphBasedOptimizer which decomposes the RelNode
> DAG into multiple common sub-graphs, and each sub-graph is a tree, and can
> be optimized independently by Calcite Optimizer.
> In `testJoinTemporalTableWithMultisinkWithFilterPushDown2`, RelNodes would
> be decomposed into 3 sub-graph. The first sub-graph contains LookupJoin,
> the second sub-graph contains sink1, the third sub-graph contains sink2.
> Those three sub-graph would be optimized by Calcite planner dependently.
>
> *> 2. Why *SubGraphBasedOptimizer seem not work for* `*testJoinTemporalTableWithMultisinkWithFilterPushDown1`
> ?
> StreamCommonSubGraphBasedOptimizer would skip if only one sink block,
> because the whole RelNode DAG is a RelNode tree itself.
> So `LookupJoin` and 'Calc(Filter: age=10)' belong to the same sub-gprah.
>
> *> 3. After push down to LookupJoin with lookup=[age=10, id=a], result to
> function signature mismatch (exception follows blow)*
> After push down 'age=10', optimizer wants to find a method that matches
> two lookup keys, 'age, id' .
> We could improve the case to avoid throwing an exception: if not found
> the most match case, try to load all records from the right table that
> matches 'id=a', then filter output by 'age=10' later. I would add a JIRA
> for this improvement.
> However, it is better if you could add a method that matches void
> eval(java.util.concurrent.CompletableFuture, java.lang.Integer,
> java.lang.Integer), because it is equivalent with push down predicate to
> right table which would reduce transfer data.
>
> Best regards,
> JING ZHANG
>
> Luan Cooper <gc...@gmail.com> 于2021年6月9日周三 下午2:17写道:
>
>> I should use patch instead of git repo
>> sorry for the inconvenience
>>
>>
>> On Tue, Jun 8, 2021 at 2:49 PM Luan Cooper <gc...@gmail.com> wrote:
>>
>>> I attach the testcase in
>>> https://github.com/CooperLuan/flink/blob/lookup_optimize/flink-table/flink-table-planner-blink/src/test/scala/org/apache/flink/table/planner/plan/stream/sql/join/LookupJoinTest.scala#L334-L399
>>>
>>> On Tue, Jun 8, 2021 at 1:50 PM JING ZHANG <be...@gmail.com> wrote:
>>>
>>>> Hi Cooper.Luan,
>>>> > *I have 2 questions*
>>>> * 1. The lookup table function execute twice, which is very expensive*
>>>> * 2. the age filter is push down to LookupJoin with lookup=[age=10,
>>>> id=a], which result to function signature mismatch (exception follows blow)*
>>>>
>>>> [image: image.png]
>>>>
>>>> If you need reuse LookupJoin like the above plan, you could use `
>>>> createTemporaryView` to register LookupJoin result as a temp view, the
>>>> code like the following:
>>>> '''
>>>>
>>> you're right, the multi insert statement reuse lookup result as expected
>>> #testJoinTemporalTableWithMultisinkWithFilterPushDown2 *show right
>>> result*
>>> [image: image.png]
>>>
>>> but testJoinTemporalTableWithMultisinkWithFilterPushDown1 *is a bad
>>> case*
>>> [image: image.png]
>>> which result to lookup function signature mismatch
>>>
>>> diff between testJoinTemporalTableWithMultisinkWithFilterPushDown1
>>> and testJoinTemporalTableWithMultisinkWithFilterPushDown2
>>> testJoinTemporalTableWithMultisinkWithFilterPushDown1: *statement set
>>> with 1 insert*
>>> testJoinTemporalTableWithMultisinkWithFilterPushDown2: *statement set
>>> with 2 insert*
>>>
>>>> @Test
>>>> def testJoinTemporalTableWithViewWithFilterPushDown(): Unit = {
>>>> createLookupTable("LookupTableAsync1", new AsyncTableFunction1)
>>>> // util.addTable(
>>>> // """
>>>> // |CREATE TEMPORARY VIEW v_vvv AS
>>>> // |SELECT * FROM MyTable AS T
>>>> // |JOIN LookupTableAsync1 FOR SYSTEM_TIME AS OF T.proctime AS D
>>>> // |ON T.a = D.id
>>>> // |""".stripMargin)
>>>> val table = util.tableEnv.sqlQuery(
>>>> """
>>>> |SELECT * FROM MyTable AS T
>>>> |JOIN LookupTableAsync1 FOR SYSTEM_TIME AS OF T.proctime AS D
>>>> |ON T.a = D.id
>>>> |""".stripMargin)
>>>>
>>>> util.tableEnv.createTemporaryView("v_vvv", table)
>>>> val stmtSet = util.tableEnv.createStatementSet()
>>>>
>>>> val appendSink1 = util.createRetractTableSink(
>>>> Array("a", "b","id","name"),
>>>> Array(INT, STRING, INT, STRING))
>>>> util.tableEnv.asInstanceOf[TableEnvironmentInternal].registerTableSinkInternal(
>>>> "appendSink1", appendSink1)
>>>> stmtSet.addInsert("appendSink1", util.tableEnv.sqlQuery("""
>>>> |SELECT a,b,id,name FROM v_vvv
>>>> |WHERE age = 10
>>>> """.stripMargin))
>>>>
>>>> stmtSet.addInsert("appendSink1", util.tableEnv.sqlQuery("""
>>>> |SELECT a,b,id,name FROM v_vvv
>>>> |WHERE age = 30
>>>> """.stripMargin))
>>>>
>>>> // util.verifyPlan(stmtSet)
>>>> util.verifyExecPlan(stmtSet)
>>>>
>>>> }'''
>>>>
>>>>
>>>> > org.apache.flink.table.api.ValidationException: Could not find an
>>>> implementation method 'eval' in class
>>>> 'org.apache.flink.table.planner.plan.utils.AsyncTableFunction1' for
>>>> function 'default_catalog.default_database.LookupTableAsync1, source:
>>>> [TestInvalidTemporalTable(id, name, age, ts)]' that matches the following
>>>> signature:
>>>> void eval(java.util.concurrent.CompletableFuture, java.lang.Integer,
>>>> java.lang.Integer)
>>>>
>>>> Would you please provide complete code to produce the exception, I
>>>> didn't find the exception in my local environment.
>>>>
>>>> Best regards,
>>>> JING ZHANG
>>>>
>>>>
>>>> Luan Cooper <gc...@gmail.com> 于2021年6月8日周二 上午10:42写道:
>>>>
>>>>> should I post in Dev user list ?
>>>>>
>>>>> On Mon, 7 Jun 2021 at 18:56 Luan Cooper <gc...@gmail.com> wrote:
>>>>>
>>>>>> Hi
>>>>>>
>>>>>> We're using multi sink in sql with view, the TestCase is
>>>>>>
>>>>>> """java
>>>>>> @Test
>>>>>> def testJoinTemporalTableWithViewWithFilterPushDown(): Unit = {
>>>>>> createLookupTable("LookupTableAsync1", new AsyncTableFunction1)
>>>>>>
>>>>>> util.addTable(
>>>>>> """
>>>>>> |CREATE TEMPORARY VIEW v_vvv AS
>>>>>> |SELECT * FROM MyTable AS T
>>>>>> |JOIN LookupTableAsync1 FOR SYSTEM_TIME AS OF T.proctime AS D
>>>>>> |ON T.a = D.id
>>>>>> |""".stripMargin)
>>>>>>
>>>>>> val stmtSet = util.tableEnv.createStatementSet()
>>>>>>
>>>>>> val appendSink1 = util.createRetractTableSink(
>>>>>> Array("a", "b","id","name"),
>>>>>> Array(INT, STRING, INT, STRING))
>>>>>>
>>>>>> util.tableEnv.asInstanceOf[TableEnvironmentInternal].registerTableSinkInternal(
>>>>>> "appendSink1", appendSink1)
>>>>>> stmtSet.addInsert("appendSink1", util.tableEnv.sqlQuery("""
>>>>>> |SELECT
>>>>>> a,b,id,name FROM v_vvv
>>>>>> |WHERE
>>>>>> age = 10
>>>>>> """.stripMargin))
>>>>>>
>>>>>> stmtSet.addInsert("appendSink1", util.tableEnv.sqlQuery("""
>>>>>> |SELECT
>>>>>> a,b,id,name FROM v_vvv
>>>>>> |WHERE
>>>>>> age = 30
>>>>>>
>>>>>> """.stripMargin))
>>>>>>
>>>>>> // util.verifyPlan(stmtSet)
>>>>>> util.verifyExecPlan(stmtSet)
>>>>>> }
>>>>>>
>>>>>> class AsyncTableFunction1 extends AsyncTableFunction[RowData] {
>>>>>> def eval(resultFuture: CompletableFuture[JCollection[RowData]], a:
>>>>>> Integer): Unit = {
>>>>>> }
>>>>>> }
>>>>>> """
>>>>>>
>>>>>> the optimized exec plan is
>>>>>>
>>>>>> """
>>>>>> Calc(select=[a, b])(reuse_id=[1])
>>>>>> +- DataStreamScan(table=[[default_catalog, default_database,
>>>>>> MyTable]], fields=[a, b, c, proctime, rowtime])
>>>>>>
>>>>>> LegacySink(name=[`default_catalog`.`default_database`.`appendSink1`],
>>>>>> fields=[a, b, id, name])
>>>>>> +-
>>>>>> LookupJoin(table=[default_catalog.default_database.LookupTableAsync1],
>>>>>> joinType=[InnerJoin], async=[true], lookup=[age=10, id=a], where=[(age =
>>>>>> 10)], select=[a, b, id, name])
>>>>>> +- Reused(reference_id=[1])
>>>>>>
>>>>>> LegacySink(name=[`default_catalog`.`default_database`.`appendSink2`],
>>>>>> fields=[a, b, id, name])
>>>>>> +-
>>>>>> LookupJoin(table=[default_catalog.default_database.LookupTableAsync1],
>>>>>> joinType=[InnerJoin], async=[true], lookup=[age=30, id=a], where=[(age =
>>>>>> 30)], select=[a, b, id, name])
>>>>>> +- Reused(reference_id=[1])
>>>>>> """
>>>>>>
>>>>>> *I have 2 questions*
>>>>>> *1. The lookup table function execute twice, which is very expensive*
>>>>>> *2. the age filter is push down to LookupJoin with lookup=[age=10,
>>>>>> id=a], which result to function signature mismatch (exception follows blow)*
>>>>>>
>>>>>> org.apache.flink.table.api.ValidationException: Could not find an
>>>>>> implementation method 'eval' in class
>>>>>> 'org.apache.flink.table.planner.plan.utils.AsyncTableFunction1' for
>>>>>> function 'default_catalog.default_database.LookupTableAsync1, source:
>>>>>> [TestInvalidTemporalTable(id, name, age, ts)]' that matches the following
>>>>>> signature:
>>>>>> void eval(java.util.concurrent.CompletableFuture, java.lang.Integer,
>>>>>> java.lang.Integer)
>>>>>>
>>>>>> Is the optimizer wrong or I'm wrong ?
>>>>>>
>>>>>> Cooper.Luan
>>>>>>
>>>>>>
Re: sql multisink explain result is more expensive than expected
Posted by JING ZHANG <be...@gmail.com>.
Hi Cooper.Luan,
Sorry for the late response. I apply your patch locally.
*> 1. The lookup table function execute twice, which is very expensive*
As mentioned before, use `createTemporaryView` like
`testJoinTemporalTableWithMultisinkWithFilterPushDown2`, could reuse
LookupJoin.
Then why does this work?
In Flink, there is a SubGraphBasedOptimizer which decomposes the RelNode
DAG into multiple common sub-graphs, and each sub-graph is a tree, and can
be optimized independently by Calcite Optimizer.
In `testJoinTemporalTableWithMultisinkWithFilterPushDown2`, RelNodes would
be decomposed into 3 sub-graph. The first sub-graph contains LookupJoin,
the second sub-graph contains sink1, the third sub-graph contains sink2.
Those three sub-graph would be optimized by Calcite planner dependently.
*> 2. Why *SubGraphBasedOptimizer seem not work for*
`*testJoinTemporalTableWithMultisinkWithFilterPushDown1`
?
StreamCommonSubGraphBasedOptimizer would skip if only one sink block,
because the whole RelNode DAG is a RelNode tree itself.
So `LookupJoin` and 'Calc(Filter: age=10)' belong to the same sub-gprah.
*> 3. After push down to LookupJoin with lookup=[age=10, id=a], result to
function signature mismatch (exception follows blow)*
After push down 'age=10', optimizer wants to find a method that matches two
lookup keys, 'age, id' .
We could improve the case to avoid throwing an exception: if not found the
most match case, try to load all records from the right table that matches
'id=a', then filter output by 'age=10' later. I would add a JIRA for this
improvement.
However, it is better if you could add a method that matches void
eval(java.util.concurrent.CompletableFuture, java.lang.Integer,
java.lang.Integer), because it is equivalent with push down predicate to
right table which would reduce transfer data.
Best regards,
JING ZHANG
Luan Cooper <gc...@gmail.com> 于2021年6月9日周三 下午2:17写道:
> I should use patch instead of git repo
> sorry for the inconvenience
>
>
> On Tue, Jun 8, 2021 at 2:49 PM Luan Cooper <gc...@gmail.com> wrote:
>
>> I attach the testcase in
>> https://github.com/CooperLuan/flink/blob/lookup_optimize/flink-table/flink-table-planner-blink/src/test/scala/org/apache/flink/table/planner/plan/stream/sql/join/LookupJoinTest.scala#L334-L399
>>
>> On Tue, Jun 8, 2021 at 1:50 PM JING ZHANG <be...@gmail.com> wrote:
>>
>>> Hi Cooper.Luan,
>>> > *I have 2 questions*
>>> * 1. The lookup table function execute twice, which is very expensive*
>>> * 2. the age filter is push down to LookupJoin with lookup=[age=10,
>>> id=a], which result to function signature mismatch (exception follows blow)*
>>>
>>> [image: image.png]
>>>
>>> If you need reuse LookupJoin like the above plan, you could use `
>>> createTemporaryView` to register LookupJoin result as a temp view, the
>>> code like the following:
>>> '''
>>>
>> you're right, the multi insert statement reuse lookup result as expected
>> #testJoinTemporalTableWithMultisinkWithFilterPushDown2 *show right
>> result*
>> [image: image.png]
>>
>> but testJoinTemporalTableWithMultisinkWithFilterPushDown1 *is a bad case*
>> [image: image.png]
>> which result to lookup function signature mismatch
>>
>> diff between testJoinTemporalTableWithMultisinkWithFilterPushDown1
>> and testJoinTemporalTableWithMultisinkWithFilterPushDown2
>> testJoinTemporalTableWithMultisinkWithFilterPushDown1: *statement set
>> with 1 insert*
>> testJoinTemporalTableWithMultisinkWithFilterPushDown2: *statement set
>> with 2 insert*
>>
>>> @Test
>>> def testJoinTemporalTableWithViewWithFilterPushDown(): Unit = {
>>> createLookupTable("LookupTableAsync1", new AsyncTableFunction1)
>>> // util.addTable(
>>> // """
>>> // |CREATE TEMPORARY VIEW v_vvv AS
>>> // |SELECT * FROM MyTable AS T
>>> // |JOIN LookupTableAsync1 FOR SYSTEM_TIME AS OF T.proctime AS D
>>> // |ON T.a = D.id
>>> // |""".stripMargin)
>>> val table = util.tableEnv.sqlQuery(
>>> """
>>> |SELECT * FROM MyTable AS T
>>> |JOIN LookupTableAsync1 FOR SYSTEM_TIME AS OF T.proctime AS D
>>> |ON T.a = D.id
>>> |""".stripMargin)
>>>
>>> util.tableEnv.createTemporaryView("v_vvv", table)
>>> val stmtSet = util.tableEnv.createStatementSet()
>>>
>>> val appendSink1 = util.createRetractTableSink(
>>> Array("a", "b","id","name"),
>>> Array(INT, STRING, INT, STRING))
>>> util.tableEnv.asInstanceOf[TableEnvironmentInternal].registerTableSinkInternal(
>>> "appendSink1", appendSink1)
>>> stmtSet.addInsert("appendSink1", util.tableEnv.sqlQuery("""
>>> |SELECT a,b,id,name FROM v_vvv
>>> |WHERE age = 10
>>> """.stripMargin))
>>>
>>> stmtSet.addInsert("appendSink1", util.tableEnv.sqlQuery("""
>>> |SELECT a,b,id,name FROM v_vvv
>>> |WHERE age = 30
>>> """.stripMargin))
>>>
>>> // util.verifyPlan(stmtSet)
>>> util.verifyExecPlan(stmtSet)
>>>
>>> }'''
>>>
>>>
>>> > org.apache.flink.table.api.ValidationException: Could not find an
>>> implementation method 'eval' in class
>>> 'org.apache.flink.table.planner.plan.utils.AsyncTableFunction1' for
>>> function 'default_catalog.default_database.LookupTableAsync1, source:
>>> [TestInvalidTemporalTable(id, name, age, ts)]' that matches the following
>>> signature:
>>> void eval(java.util.concurrent.CompletableFuture, java.lang.Integer,
>>> java.lang.Integer)
>>>
>>> Would you please provide complete code to produce the exception, I
>>> didn't find the exception in my local environment.
>>>
>>> Best regards,
>>> JING ZHANG
>>>
>>>
>>> Luan Cooper <gc...@gmail.com> 于2021年6月8日周二 上午10:42写道:
>>>
>>>> should I post in Dev user list ?
>>>>
>>>> On Mon, 7 Jun 2021 at 18:56 Luan Cooper <gc...@gmail.com> wrote:
>>>>
>>>>> Hi
>>>>>
>>>>> We're using multi sink in sql with view, the TestCase is
>>>>>
>>>>> """java
>>>>> @Test
>>>>> def testJoinTemporalTableWithViewWithFilterPushDown(): Unit = {
>>>>> createLookupTable("LookupTableAsync1", new AsyncTableFunction1)
>>>>>
>>>>> util.addTable(
>>>>> """
>>>>> |CREATE TEMPORARY VIEW v_vvv AS
>>>>> |SELECT * FROM MyTable AS T
>>>>> |JOIN LookupTableAsync1 FOR SYSTEM_TIME AS OF T.proctime AS D
>>>>> |ON T.a = D.id
>>>>> |""".stripMargin)
>>>>>
>>>>> val stmtSet = util.tableEnv.createStatementSet()
>>>>>
>>>>> val appendSink1 = util.createRetractTableSink(
>>>>> Array("a", "b","id","name"),
>>>>> Array(INT, STRING, INT, STRING))
>>>>>
>>>>> util.tableEnv.asInstanceOf[TableEnvironmentInternal].registerTableSinkInternal(
>>>>> "appendSink1", appendSink1)
>>>>> stmtSet.addInsert("appendSink1", util.tableEnv.sqlQuery("""
>>>>> |SELECT
>>>>> a,b,id,name FROM v_vvv
>>>>> |WHERE
>>>>> age = 10
>>>>> """.stripMargin))
>>>>>
>>>>> stmtSet.addInsert("appendSink1", util.tableEnv.sqlQuery("""
>>>>> |SELECT
>>>>> a,b,id,name FROM v_vvv
>>>>> |WHERE
>>>>> age = 30
>>>>>
>>>>> """.stripMargin))
>>>>>
>>>>> // util.verifyPlan(stmtSet)
>>>>> util.verifyExecPlan(stmtSet)
>>>>> }
>>>>>
>>>>> class AsyncTableFunction1 extends AsyncTableFunction[RowData] {
>>>>> def eval(resultFuture: CompletableFuture[JCollection[RowData]], a:
>>>>> Integer): Unit = {
>>>>> }
>>>>> }
>>>>> """
>>>>>
>>>>> the optimized exec plan is
>>>>>
>>>>> """
>>>>> Calc(select=[a, b])(reuse_id=[1])
>>>>> +- DataStreamScan(table=[[default_catalog, default_database,
>>>>> MyTable]], fields=[a, b, c, proctime, rowtime])
>>>>>
>>>>> LegacySink(name=[`default_catalog`.`default_database`.`appendSink1`],
>>>>> fields=[a, b, id, name])
>>>>> +-
>>>>> LookupJoin(table=[default_catalog.default_database.LookupTableAsync1],
>>>>> joinType=[InnerJoin], async=[true], lookup=[age=10, id=a], where=[(age =
>>>>> 10)], select=[a, b, id, name])
>>>>> +- Reused(reference_id=[1])
>>>>>
>>>>> LegacySink(name=[`default_catalog`.`default_database`.`appendSink2`],
>>>>> fields=[a, b, id, name])
>>>>> +-
>>>>> LookupJoin(table=[default_catalog.default_database.LookupTableAsync1],
>>>>> joinType=[InnerJoin], async=[true], lookup=[age=30, id=a], where=[(age =
>>>>> 30)], select=[a, b, id, name])
>>>>> +- Reused(reference_id=[1])
>>>>> """
>>>>>
>>>>> *I have 2 questions*
>>>>> *1. The lookup table function execute twice, which is very expensive*
>>>>> *2. the age filter is push down to LookupJoin with lookup=[age=10,
>>>>> id=a], which result to function signature mismatch (exception follows blow)*
>>>>>
>>>>> org.apache.flink.table.api.ValidationException: Could not find an
>>>>> implementation method 'eval' in class
>>>>> 'org.apache.flink.table.planner.plan.utils.AsyncTableFunction1' for
>>>>> function 'default_catalog.default_database.LookupTableAsync1, source:
>>>>> [TestInvalidTemporalTable(id, name, age, ts)]' that matches the following
>>>>> signature:
>>>>> void eval(java.util.concurrent.CompletableFuture, java.lang.Integer,
>>>>> java.lang.Integer)
>>>>>
>>>>> Is the optimizer wrong or I'm wrong ?
>>>>>
>>>>> Cooper.Luan
>>>>>
>>>>>
Re: sql multisink explain result is more expensive than expected
Posted by Luan Cooper <gc...@gmail.com>.
I should use patch instead of git repo
sorry for the inconvenience
On Tue, Jun 8, 2021 at 2:49 PM Luan Cooper <gc...@gmail.com> wrote:
> I attach the testcase in
> https://github.com/CooperLuan/flink/blob/lookup_optimize/flink-table/flink-table-planner-blink/src/test/scala/org/apache/flink/table/planner/plan/stream/sql/join/LookupJoinTest.scala#L334-L399
>
> On Tue, Jun 8, 2021 at 1:50 PM JING ZHANG <be...@gmail.com> wrote:
>
>> Hi Cooper.Luan,
>> > *I have 2 questions*
>> * 1. The lookup table function execute twice, which is very expensive*
>> * 2. the age filter is push down to LookupJoin with lookup=[age=10,
>> id=a], which result to function signature mismatch (exception follows blow)*
>>
>> [image: image.png]
>>
>> If you need reuse LookupJoin like the above plan, you could use `
>> createTemporaryView` to register LookupJoin result as a temp view, the
>> code like the following:
>> '''
>>
> you're right, the multi insert statement reuse lookup result as expected
> #testJoinTemporalTableWithMultisinkWithFilterPushDown2 *show right result*
> [image: image.png]
>
> but testJoinTemporalTableWithMultisinkWithFilterPushDown1 *is a bad case*
> [image: image.png]
> which result to lookup function signature mismatch
>
> diff between testJoinTemporalTableWithMultisinkWithFilterPushDown1
> and testJoinTemporalTableWithMultisinkWithFilterPushDown2
> testJoinTemporalTableWithMultisinkWithFilterPushDown1: *statement set
> with 1 insert*
> testJoinTemporalTableWithMultisinkWithFilterPushDown2: *statement set
> with 2 insert*
>
>> @Test
>> def testJoinTemporalTableWithViewWithFilterPushDown(): Unit = {
>> createLookupTable("LookupTableAsync1", new AsyncTableFunction1)
>> // util.addTable(
>> // """
>> // |CREATE TEMPORARY VIEW v_vvv AS
>> // |SELECT * FROM MyTable AS T
>> // |JOIN LookupTableAsync1 FOR SYSTEM_TIME AS OF T.proctime AS D
>> // |ON T.a = D.id
>> // |""".stripMargin)
>> val table = util.tableEnv.sqlQuery(
>> """
>> |SELECT * FROM MyTable AS T
>> |JOIN LookupTableAsync1 FOR SYSTEM_TIME AS OF T.proctime AS D
>> |ON T.a = D.id
>> |""".stripMargin)
>>
>> util.tableEnv.createTemporaryView("v_vvv", table)
>> val stmtSet = util.tableEnv.createStatementSet()
>>
>> val appendSink1 = util.createRetractTableSink(
>> Array("a", "b","id","name"),
>> Array(INT, STRING, INT, STRING))
>> util.tableEnv.asInstanceOf[TableEnvironmentInternal].registerTableSinkInternal(
>> "appendSink1", appendSink1)
>> stmtSet.addInsert("appendSink1", util.tableEnv.sqlQuery("""
>> |SELECT a,b,id,name FROM v_vvv
>> |WHERE age = 10
>> """.stripMargin))
>>
>> stmtSet.addInsert("appendSink1", util.tableEnv.sqlQuery("""
>> |SELECT a,b,id,name FROM v_vvv
>> |WHERE age = 30
>> """.stripMargin))
>>
>> // util.verifyPlan(stmtSet)
>> util.verifyExecPlan(stmtSet)
>>
>> }'''
>>
>>
>> > org.apache.flink.table.api.ValidationException: Could not find an
>> implementation method 'eval' in class
>> 'org.apache.flink.table.planner.plan.utils.AsyncTableFunction1' for
>> function 'default_catalog.default_database.LookupTableAsync1, source:
>> [TestInvalidTemporalTable(id, name, age, ts)]' that matches the following
>> signature:
>> void eval(java.util.concurrent.CompletableFuture, java.lang.Integer,
>> java.lang.Integer)
>>
>> Would you please provide complete code to produce the exception, I didn't
>> find the exception in my local environment.
>>
>> Best regards,
>> JING ZHANG
>>
>>
>> Luan Cooper <gc...@gmail.com> 于2021年6月8日周二 上午10:42写道:
>>
>>> should I post in Dev user list ?
>>>
>>> On Mon, 7 Jun 2021 at 18:56 Luan Cooper <gc...@gmail.com> wrote:
>>>
>>>> Hi
>>>>
>>>> We're using multi sink in sql with view, the TestCase is
>>>>
>>>> """java
>>>> @Test
>>>> def testJoinTemporalTableWithViewWithFilterPushDown(): Unit = {
>>>> createLookupTable("LookupTableAsync1", new AsyncTableFunction1)
>>>>
>>>> util.addTable(
>>>> """
>>>> |CREATE TEMPORARY VIEW v_vvv AS
>>>> |SELECT * FROM MyTable AS T
>>>> |JOIN LookupTableAsync1 FOR SYSTEM_TIME AS OF T.proctime AS D
>>>> |ON T.a = D.id
>>>> |""".stripMargin)
>>>>
>>>> val stmtSet = util.tableEnv.createStatementSet()
>>>>
>>>> val appendSink1 = util.createRetractTableSink(
>>>> Array("a", "b","id","name"),
>>>> Array(INT, STRING, INT, STRING))
>>>>
>>>> util.tableEnv.asInstanceOf[TableEnvironmentInternal].registerTableSinkInternal(
>>>> "appendSink1", appendSink1)
>>>> stmtSet.addInsert("appendSink1", util.tableEnv.sqlQuery("""
>>>> |SELECT
>>>> a,b,id,name FROM v_vvv
>>>> |WHERE
>>>> age = 10
>>>> """.stripMargin))
>>>>
>>>> stmtSet.addInsert("appendSink1", util.tableEnv.sqlQuery("""
>>>> |SELECT
>>>> a,b,id,name FROM v_vvv
>>>> |WHERE
>>>> age = 30
>>>>
>>>> """.stripMargin))
>>>>
>>>> // util.verifyPlan(stmtSet)
>>>> util.verifyExecPlan(stmtSet)
>>>> }
>>>>
>>>> class AsyncTableFunction1 extends AsyncTableFunction[RowData] {
>>>> def eval(resultFuture: CompletableFuture[JCollection[RowData]], a:
>>>> Integer): Unit = {
>>>> }
>>>> }
>>>> """
>>>>
>>>> the optimized exec plan is
>>>>
>>>> """
>>>> Calc(select=[a, b])(reuse_id=[1])
>>>> +- DataStreamScan(table=[[default_catalog, default_database, MyTable]],
>>>> fields=[a, b, c, proctime, rowtime])
>>>>
>>>> LegacySink(name=[`default_catalog`.`default_database`.`appendSink1`],
>>>> fields=[a, b, id, name])
>>>> +-
>>>> LookupJoin(table=[default_catalog.default_database.LookupTableAsync1],
>>>> joinType=[InnerJoin], async=[true], lookup=[age=10, id=a], where=[(age =
>>>> 10)], select=[a, b, id, name])
>>>> +- Reused(reference_id=[1])
>>>>
>>>> LegacySink(name=[`default_catalog`.`default_database`.`appendSink2`],
>>>> fields=[a, b, id, name])
>>>> +-
>>>> LookupJoin(table=[default_catalog.default_database.LookupTableAsync1],
>>>> joinType=[InnerJoin], async=[true], lookup=[age=30, id=a], where=[(age =
>>>> 30)], select=[a, b, id, name])
>>>> +- Reused(reference_id=[1])
>>>> """
>>>>
>>>> *I have 2 questions*
>>>> *1. The lookup table function execute twice, which is very expensive*
>>>> *2. the age filter is push down to LookupJoin with lookup=[age=10,
>>>> id=a], which result to function signature mismatch (exception follows blow)*
>>>>
>>>> org.apache.flink.table.api.ValidationException: Could not find an
>>>> implementation method 'eval' in class
>>>> 'org.apache.flink.table.planner.plan.utils.AsyncTableFunction1' for
>>>> function 'default_catalog.default_database.LookupTableAsync1, source:
>>>> [TestInvalidTemporalTable(id, name, age, ts)]' that matches the following
>>>> signature:
>>>> void eval(java.util.concurrent.CompletableFuture, java.lang.Integer,
>>>> java.lang.Integer)
>>>>
>>>> Is the optimizer wrong or I'm wrong ?
>>>>
>>>> Cooper.Luan
>>>>
>>>>
Re: sql multisink explain result is more expensive than expected
Posted by Luan Cooper <gc...@gmail.com>.
I attach the testcase in
https://github.com/CooperLuan/flink/blob/lookup_optimize/flink-table/flink-table-planner-blink/src/test/scala/org/apache/flink/table/planner/plan/stream/sql/join/LookupJoinTest.scala#L334-L399
On Tue, Jun 8, 2021 at 1:50 PM JING ZHANG <be...@gmail.com> wrote:
> Hi Cooper.Luan,
> > *I have 2 questions*
> * 1. The lookup table function execute twice, which is very expensive*
> * 2. the age filter is push down to LookupJoin with lookup=[age=10,
> id=a], which result to function signature mismatch (exception follows blow)*
>
> [image: image.png]
>
> If you need reuse LookupJoin like the above plan, you could use `
> createTemporaryView` to register LookupJoin result as a temp view, the
> code like the following:
> '''
>
you're right, the multi insert statement reuse lookup result as expected
#testJoinTemporalTableWithMultisinkWithFilterPushDown2 *show right result*
[image: image.png]
but testJoinTemporalTableWithMultisinkWithFilterPushDown1 *is a bad case*
[image: image.png]
which result to lookup function signature mismatch
diff between testJoinTemporalTableWithMultisinkWithFilterPushDown1
and testJoinTemporalTableWithMultisinkWithFilterPushDown2
testJoinTemporalTableWithMultisinkWithFilterPushDown1: *statement set with
1 insert*
testJoinTemporalTableWithMultisinkWithFilterPushDown2: *statement set with
2 insert*
> @Test
> def testJoinTemporalTableWithViewWithFilterPushDown(): Unit = {
> createLookupTable("LookupTableAsync1", new AsyncTableFunction1)
> // util.addTable(
> // """
> // |CREATE TEMPORARY VIEW v_vvv AS
> // |SELECT * FROM MyTable AS T
> // |JOIN LookupTableAsync1 FOR SYSTEM_TIME AS OF T.proctime AS D
> // |ON T.a = D.id
> // |""".stripMargin)
> val table = util.tableEnv.sqlQuery(
> """
> |SELECT * FROM MyTable AS T
> |JOIN LookupTableAsync1 FOR SYSTEM_TIME AS OF T.proctime AS D
> |ON T.a = D.id
> |""".stripMargin)
>
> util.tableEnv.createTemporaryView("v_vvv", table)
> val stmtSet = util.tableEnv.createStatementSet()
>
> val appendSink1 = util.createRetractTableSink(
> Array("a", "b","id","name"),
> Array(INT, STRING, INT, STRING))
> util.tableEnv.asInstanceOf[TableEnvironmentInternal].registerTableSinkInternal(
> "appendSink1", appendSink1)
> stmtSet.addInsert("appendSink1", util.tableEnv.sqlQuery("""
> |SELECT a,b,id,name FROM v_vvv
> |WHERE age = 10
> """.stripMargin))
>
> stmtSet.addInsert("appendSink1", util.tableEnv.sqlQuery("""
> |SELECT a,b,id,name FROM v_vvv
> |WHERE age = 30
> """.stripMargin))
>
> // util.verifyPlan(stmtSet)
> util.verifyExecPlan(stmtSet)
>
> }'''
>
>
> > org.apache.flink.table.api.ValidationException: Could not find an
> implementation method 'eval' in class
> 'org.apache.flink.table.planner.plan.utils.AsyncTableFunction1' for
> function 'default_catalog.default_database.LookupTableAsync1, source:
> [TestInvalidTemporalTable(id, name, age, ts)]' that matches the following
> signature:
> void eval(java.util.concurrent.CompletableFuture, java.lang.Integer,
> java.lang.Integer)
>
> Would you please provide complete code to produce the exception, I didn't
> find the exception in my local environment.
>
> Best regards,
> JING ZHANG
>
>
> Luan Cooper <gc...@gmail.com> 于2021年6月8日周二 上午10:42写道:
>
>> should I post in Dev user list ?
>>
>> On Mon, 7 Jun 2021 at 18:56 Luan Cooper <gc...@gmail.com> wrote:
>>
>>> Hi
>>>
>>> We're using multi sink in sql with view, the TestCase is
>>>
>>> """java
>>> @Test
>>> def testJoinTemporalTableWithViewWithFilterPushDown(): Unit = {
>>> createLookupTable("LookupTableAsync1", new AsyncTableFunction1)
>>>
>>> util.addTable(
>>> """
>>> |CREATE TEMPORARY VIEW v_vvv AS
>>> |SELECT * FROM MyTable AS T
>>> |JOIN LookupTableAsync1 FOR SYSTEM_TIME AS OF T.proctime AS D
>>> |ON T.a = D.id
>>> |""".stripMargin)
>>>
>>> val stmtSet = util.tableEnv.createStatementSet()
>>>
>>> val appendSink1 = util.createRetractTableSink(
>>> Array("a", "b","id","name"),
>>> Array(INT, STRING, INT, STRING))
>>>
>>> util.tableEnv.asInstanceOf[TableEnvironmentInternal].registerTableSinkInternal(
>>> "appendSink1", appendSink1)
>>> stmtSet.addInsert("appendSink1", util.tableEnv.sqlQuery("""
>>> |SELECT
>>> a,b,id,name FROM v_vvv
>>> |WHERE age
>>> = 10
>>> """.stripMargin))
>>>
>>> stmtSet.addInsert("appendSink1", util.tableEnv.sqlQuery("""
>>> |SELECT
>>> a,b,id,name FROM v_vvv
>>> |WHERE age
>>> = 30
>>>
>>> """.stripMargin))
>>>
>>> // util.verifyPlan(stmtSet)
>>> util.verifyExecPlan(stmtSet)
>>> }
>>>
>>> class AsyncTableFunction1 extends AsyncTableFunction[RowData] {
>>> def eval(resultFuture: CompletableFuture[JCollection[RowData]], a:
>>> Integer): Unit = {
>>> }
>>> }
>>> """
>>>
>>> the optimized exec plan is
>>>
>>> """
>>> Calc(select=[a, b])(reuse_id=[1])
>>> +- DataStreamScan(table=[[default_catalog, default_database, MyTable]],
>>> fields=[a, b, c, proctime, rowtime])
>>>
>>> LegacySink(name=[`default_catalog`.`default_database`.`appendSink1`],
>>> fields=[a, b, id, name])
>>> +-
>>> LookupJoin(table=[default_catalog.default_database.LookupTableAsync1],
>>> joinType=[InnerJoin], async=[true], lookup=[age=10, id=a], where=[(age =
>>> 10)], select=[a, b, id, name])
>>> +- Reused(reference_id=[1])
>>>
>>> LegacySink(name=[`default_catalog`.`default_database`.`appendSink2`],
>>> fields=[a, b, id, name])
>>> +-
>>> LookupJoin(table=[default_catalog.default_database.LookupTableAsync1],
>>> joinType=[InnerJoin], async=[true], lookup=[age=30, id=a], where=[(age =
>>> 30)], select=[a, b, id, name])
>>> +- Reused(reference_id=[1])
>>> """
>>>
>>> *I have 2 questions*
>>> *1. The lookup table function execute twice, which is very expensive*
>>> *2. the age filter is push down to LookupJoin with lookup=[age=10,
>>> id=a], which result to function signature mismatch (exception follows blow)*
>>>
>>> org.apache.flink.table.api.ValidationException: Could not find an
>>> implementation method 'eval' in class
>>> 'org.apache.flink.table.planner.plan.utils.AsyncTableFunction1' for
>>> function 'default_catalog.default_database.LookupTableAsync1, source:
>>> [TestInvalidTemporalTable(id, name, age, ts)]' that matches the following
>>> signature:
>>> void eval(java.util.concurrent.CompletableFuture, java.lang.Integer,
>>> java.lang.Integer)
>>>
>>> Is the optimizer wrong or I'm wrong ?
>>>
>>> Cooper.Luan
>>>
>>>
Re: sql multisink explain result is more expensive than expected
Posted by JING ZHANG <be...@gmail.com>.
Hi Cooper.Luan,
> *I have 2 questions*
* 1. The lookup table function execute twice, which is very expensive*
* 2. the age filter is push down to LookupJoin with lookup=[age=10, id=a],
which result to function signature mismatch (exception follows blow)*
[image: image.png]
If you need reuse LookupJoin like the above plan, you could use `
createTemporaryView` to register LookupJoin result as a temp view, the code
like the following:
'''
@Test
def testJoinTemporalTableWithViewWithFilterPushDown(): Unit = {
createLookupTable("LookupTableAsync1", new AsyncTableFunction1)
// util.addTable(
// """
// |CREATE TEMPORARY VIEW v_vvv AS
// |SELECT * FROM MyTable AS T
// |JOIN LookupTableAsync1 FOR SYSTEM_TIME AS OF T.proctime AS D
// |ON T.a = D.id
// |""".stripMargin)
val table = util.tableEnv.sqlQuery(
"""
|SELECT * FROM MyTable AS T
|JOIN LookupTableAsync1 FOR SYSTEM_TIME AS OF T.proctime AS D
|ON T.a = D.id
|""".stripMargin)
util.tableEnv.createTemporaryView("v_vvv", table)
val stmtSet = util.tableEnv.createStatementSet()
val appendSink1 = util.createRetractTableSink(
Array("a", "b","id","name"),
Array(INT, STRING, INT, STRING))
util.tableEnv.asInstanceOf[TableEnvironmentInternal].registerTableSinkInternal(
"appendSink1", appendSink1)
stmtSet.addInsert("appendSink1", util.tableEnv.sqlQuery("""
|SELECT
a,b,id,name FROM v_vvv
|WHERE age = 10
""".stripMargin))
stmtSet.addInsert("appendSink1", util.tableEnv.sqlQuery("""
|SELECT
a,b,id,name FROM v_vvv
|WHERE age = 30
""".stripMargin))
// util.verifyPlan(stmtSet)
util.verifyExecPlan(stmtSet)
}'''
> org.apache.flink.table.api.ValidationException: Could not find an
implementation method 'eval' in class
'org.apache.flink.table.planner.plan.utils.AsyncTableFunction1' for
function 'default_catalog.default_database.LookupTableAsync1, source:
[TestInvalidTemporalTable(id, name, age, ts)]' that matches the following
signature:
void eval(java.util.concurrent.CompletableFuture, java.lang.Integer,
java.lang.Integer)
Would you please provide complete code to produce the exception, I didn't
find the exception in my local environment.
Best regards,
JING ZHANG
Luan Cooper <gc...@gmail.com> 于2021年6月8日周二 上午10:42写道:
> should I post in Dev user list ?
>
> On Mon, 7 Jun 2021 at 18:56 Luan Cooper <gc...@gmail.com> wrote:
>
>> Hi
>>
>> We're using multi sink in sql with view, the TestCase is
>>
>> """java
>> @Test
>> def testJoinTemporalTableWithViewWithFilterPushDown(): Unit = {
>> createLookupTable("LookupTableAsync1", new AsyncTableFunction1)
>>
>> util.addTable(
>> """
>> |CREATE TEMPORARY VIEW v_vvv AS
>> |SELECT * FROM MyTable AS T
>> |JOIN LookupTableAsync1 FOR SYSTEM_TIME AS OF T.proctime AS D
>> |ON T.a = D.id
>> |""".stripMargin)
>>
>> val stmtSet = util.tableEnv.createStatementSet()
>>
>> val appendSink1 = util.createRetractTableSink(
>> Array("a", "b","id","name"),
>> Array(INT, STRING, INT, STRING))
>>
>> util.tableEnv.asInstanceOf[TableEnvironmentInternal].registerTableSinkInternal(
>> "appendSink1", appendSink1)
>> stmtSet.addInsert("appendSink1", util.tableEnv.sqlQuery("""
>> |SELECT
>> a,b,id,name FROM v_vvv
>> |WHERE age
>> = 10
>> """.stripMargin))
>>
>> stmtSet.addInsert("appendSink1", util.tableEnv.sqlQuery("""
>> |SELECT
>> a,b,id,name FROM v_vvv
>> |WHERE age
>> = 30
>>
>> """.stripMargin))
>>
>> // util.verifyPlan(stmtSet)
>> util.verifyExecPlan(stmtSet)
>> }
>>
>> class AsyncTableFunction1 extends AsyncTableFunction[RowData] {
>> def eval(resultFuture: CompletableFuture[JCollection[RowData]], a:
>> Integer): Unit = {
>> }
>> }
>> """
>>
>> the optimized exec plan is
>>
>> """
>> Calc(select=[a, b])(reuse_id=[1])
>> +- DataStreamScan(table=[[default_catalog, default_database, MyTable]],
>> fields=[a, b, c, proctime, rowtime])
>>
>> LegacySink(name=[`default_catalog`.`default_database`.`appendSink1`],
>> fields=[a, b, id, name])
>> +- LookupJoin(table=[default_catalog.default_database.LookupTableAsync1],
>> joinType=[InnerJoin], async=[true], lookup=[age=10, id=a], where=[(age =
>> 10)], select=[a, b, id, name])
>> +- Reused(reference_id=[1])
>>
>> LegacySink(name=[`default_catalog`.`default_database`.`appendSink2`],
>> fields=[a, b, id, name])
>> +- LookupJoin(table=[default_catalog.default_database.LookupTableAsync1],
>> joinType=[InnerJoin], async=[true], lookup=[age=30, id=a], where=[(age =
>> 30)], select=[a, b, id, name])
>> +- Reused(reference_id=[1])
>> """
>>
>> *I have 2 questions*
>> *1. The lookup table function execute twice, which is very expensive*
>> *2. the age filter is push down to LookupJoin with lookup=[age=10, id=a],
>> which result to function signature mismatch (exception follows blow)*
>>
>> org.apache.flink.table.api.ValidationException: Could not find an
>> implementation method 'eval' in class
>> 'org.apache.flink.table.planner.plan.utils.AsyncTableFunction1' for
>> function 'default_catalog.default_database.LookupTableAsync1, source:
>> [TestInvalidTemporalTable(id, name, age, ts)]' that matches the following
>> signature:
>> void eval(java.util.concurrent.CompletableFuture, java.lang.Integer,
>> java.lang.Integer)
>>
>> Is the optimizer wrong or I'm wrong ?
>>
>> Cooper.Luan
>>
>>
Re: sql multisink explain result is more expensive than expected
Posted by Luan Cooper <gc...@gmail.com>.
should I post in Dev user list ?
On Mon, 7 Jun 2021 at 18:56 Luan Cooper <gc...@gmail.com> wrote:
> Hi
>
> We're using multi sink in sql with view, the TestCase is
>
> """java
> @Test
> def testJoinTemporalTableWithViewWithFilterPushDown(): Unit = {
> createLookupTable("LookupTableAsync1", new AsyncTableFunction1)
>
> util.addTable(
> """
> |CREATE TEMPORARY VIEW v_vvv AS
> |SELECT * FROM MyTable AS T
> |JOIN LookupTableAsync1 FOR SYSTEM_TIME AS OF T.proctime AS D
> |ON T.a = D.id
> |""".stripMargin)
>
> val stmtSet = util.tableEnv.createStatementSet()
>
> val appendSink1 = util.createRetractTableSink(
> Array("a", "b","id","name"),
> Array(INT, STRING, INT, STRING))
>
> util.tableEnv.asInstanceOf[TableEnvironmentInternal].registerTableSinkInternal(
> "appendSink1", appendSink1)
> stmtSet.addInsert("appendSink1", util.tableEnv.sqlQuery("""
> |SELECT
> a,b,id,name FROM v_vvv
> |WHERE age =
> 10
> """.stripMargin))
>
> stmtSet.addInsert("appendSink1", util.tableEnv.sqlQuery("""
> |SELECT
> a,b,id,name FROM v_vvv
> |WHERE age =
> 30
>
> """.stripMargin))
>
> // util.verifyPlan(stmtSet)
> util.verifyExecPlan(stmtSet)
> }
>
> class AsyncTableFunction1 extends AsyncTableFunction[RowData] {
> def eval(resultFuture: CompletableFuture[JCollection[RowData]], a:
> Integer): Unit = {
> }
> }
> """
>
> the optimized exec plan is
>
> """
> Calc(select=[a, b])(reuse_id=[1])
> +- DataStreamScan(table=[[default_catalog, default_database, MyTable]],
> fields=[a, b, c, proctime, rowtime])
>
> LegacySink(name=[`default_catalog`.`default_database`.`appendSink1`],
> fields=[a, b, id, name])
> +- LookupJoin(table=[default_catalog.default_database.LookupTableAsync1],
> joinType=[InnerJoin], async=[true], lookup=[age=10, id=a], where=[(age =
> 10)], select=[a, b, id, name])
> +- Reused(reference_id=[1])
>
> LegacySink(name=[`default_catalog`.`default_database`.`appendSink2`],
> fields=[a, b, id, name])
> +- LookupJoin(table=[default_catalog.default_database.LookupTableAsync1],
> joinType=[InnerJoin], async=[true], lookup=[age=30, id=a], where=[(age =
> 30)], select=[a, b, id, name])
> +- Reused(reference_id=[1])
> """
>
> *I have 2 questions*
> *1. The lookup table function execute twice, which is very expensive*
> *2. the age filter is push down to LookupJoin with lookup=[age=10, id=a],
> which result to function signature mismatch (exception follows blow)*
>
> org.apache.flink.table.api.ValidationException: Could not find an
> implementation method 'eval' in class
> 'org.apache.flink.table.planner.plan.utils.AsyncTableFunction1' for
> function 'default_catalog.default_database.LookupTableAsync1, source:
> [TestInvalidTemporalTable(id, name, age, ts)]' that matches the following
> signature:
> void eval(java.util.concurrent.CompletableFuture, java.lang.Integer,
> java.lang.Integer)
>
> Is the optimizer wrong or I'm wrong ?
>
> Cooper.Luan
>
>