putSql处理器后executeSql失败 [英] executeSql failing after putSql processor

问题描述

我的用例是查询一组表中的一组数据并将其插入一个表中.所以我的nifi处理器基本上是这样的:

my use case is to query a set of data for bunch of table and insert it into one table. so my nifi processor basically is like this:

executeSql(数据集的查询集)>>> convertAvrotoJson >>>> convetJSONtoSQL(转换为insert语句)>>>> putSQL(插入语句)>>> executeSQL(删除与set中的第一个处理器查询关联的行)表格)

executeSql(query set of dataset) >>> convertAvrotoJson >>>> convetJSONtoSQL(convert to insert statement) >>>> putSQL (insert statement) >>> executeSQL(delete a row associated with first processor query in set of table)

问题出在最后一个executeSQL处理器上，当它不执行我的sql时，它会继续尝试运行putSQL查询.

the problem is in the last executeSQL processor, when its not executing my sql, instead it keep trying to run putSQL query.

为了测试(我在executeSQL中)，我只用普通的select替换了我的delete查询:

i replace my delete query with just a normal select for the sake of testing (this is in the executeSQL) :

这是executeSQL的数据来源，我们可以清楚地看到它仍然试图执行来自putSQL的insert语句，即使我已经指定要执行查询:

and this is the data provenance for executeSQL, we can clearly see its still trying to execute the insert statement coming from putSQL even i already specified to just do a query:

这是错误，错误清楚地表明我没有提供参数，而即时通讯显然不希望它执行插入语句:

and this is the error, the error clearly say i didnt provide parameter when im clearly dont want it to execute an insert statement:

在表A中成功插入记录后，如何在表B上执行delete语句?请帮助，因为即时通讯卡在这一点上.请不要与图像混淆，因为我只是使用常规select测试它，以确保nifi可以正确执行我的查询. 我正在使用mysql 5.7

how to execute a delete statement on table B after i successfully insert a record in Table A? please help as im stuck at this. please dont get confuse with the image, as im just testing it with normal select to ensure that nifi can execute my query correctly. im using mysql 5.7

推荐答案

如果流文件上有sql.args属性，ExecuteSQL将尝试使用它们来填充带有参数的PreparedStatement.这是因为指定的查询(无论是通过flowfile主体还是通过SQL select query属性进入)都可以具有?参数，以用流文件属性来填充.对于上面的流程，您可以在PutSQL和ExecuteSQL之间放置一个UpdateAttribute处理器，将Delete Attributes Expression属性设置为sql\.args\..*或类似的东西，以删除参数.

If there are sql.args attributes on the flow file, ExecuteSQL will attempt to use them to populate a PreparedStatement with parameters. This is because the specified query (whether coming in via the flowfile body or the SQL select query property) can have ? parameters, to be filled in by the flow file attributes. For your flow above, you could put an UpdateAttribute processor between the PutSQL and ExecuteSQL, setting the Delete Attributes Expression property to sql\.args\..* or something, to remove the parameters.

话虽如此，我建议您将整个流程替换为ExecuteSQL-> PutDatabaseRecord-> ExecuteSQL.这将绕过转换逻辑，并且SQL生成/执行在内部完成(因此不需要sql.args属性).

Having said that, I recommend you replace your whole flow with ExecuteSQL -> PutDatabaseRecord -> ExecuteSQL. This bypasses the conversion logic and the SQL generation/execution is done internally (so no need for sql.args attributes).

这篇关于putSql处理器后executeSql失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！