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(转换为插入语句) >>>> putSQL (插入语句) >>> executeSQL(删除与第一个处理器查询相关的行表)

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中)，我只用一个普通的选择替换了我的删除查询:

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

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

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 上执行删除语句?请帮助我坚持这一点.请不要与图像混淆，因为我只是用普通选择测试它以确保 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.这是因为指定的查询(无论是通过流文件正文还是通过 SQL 选择查询 属性传入)可以具有 ? 参数，由流文件属性填充.对于上面的流程，您可以在 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屋！