問題描述

我的用例是查詢一組表的一組數(shù)據(jù)并將其插入到一個(gè)表中.所以我的 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(查詢數(shù)據(jù)集) >>> convertAvrotoJson >>>> convetJSONtoSQL(轉(zhuǎn)換為插入語句) >>>> putSQL (插入語句) >>> executeSQL(刪除與第一個(gè)處理器查詢相關(guān)的行表)

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)

問題出在最后一個(gè) executeSQL 處理器中，當(dāng)它沒有執(zhí)行我的 sql 時(shí)，它一直試圖運(yùn)行 putSQL 查詢.

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

為了測試(這是在executeSQL中)，我只用一個(gè)普通的選擇替換了我的刪除查詢:

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

這是 executeSQL 的數(shù)據(jù)來源，我們可以清楚地看到它仍在嘗試執(zhí)行來自 putSQL 的插入語句，即使我已經(jīng)指定只執(zhí)行查詢:

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:

這是錯(cuò)誤，錯(cuò)誤清楚地表明我沒有提供參數(shù)，而我顯然不希望它執(zhí)行插入語句:

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 上執(zhí)行刪除語句?請(qǐng)幫助我堅(jiān)持這一點(diǎn).請(qǐng)不要與圖像混淆，因?yàn)槲抑皇怯闷胀ㄟx擇測試它以確保 nifi 可以正確執(zhí)行我的查詢.我使用的是 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 將嘗試使用它們用參數(shù)填充 PreparedStatement.這是因?yàn)橹付ǖ牟樵?無論是通過流文件正文還是通過 SQL 選擇查詢 屬性傳入)可以具有 ? 參數(shù)，由流文件屬性填充.對(duì)于上面的流程，您可以在 PutSQL 和 ExecuteSQL 之間放置一個(gè) UpdateAttribute 處理器，將 Delete Attributes Expression 屬性設(shè)置為 sql\.args\..* 或其他內(nèi)容，以刪除參數(shù).

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.

話雖如此，我建議您將整個(gè)流程替換為 ExecuteSQL -> PutDatabaseRecord -> ExecuteSQL.這繞過了轉(zhuǎn)換邏輯，SQL 生成/執(zhí)行在內(nèi)部完成(因此不需要 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).

這篇關(guān)于putSql處理器后executeSql失敗的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！