問題描述

我的用例是查詢一組表的一組數據并將其插入到一個表中.所以我的 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失敗的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！