1

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(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)

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

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

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: enter image description here

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

enter image description here

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

Han Nan
  • 117
  • 3
  • 10

1 Answers1

3

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.

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).

mattyb
  • 11,693
  • 15
  • 20
  • I delete all the sql argument, and it working now... I tried to use putdatabaserecord, i couldnt find a good sample to understand how to use it... I dont understand putdatabaserecord requirement also... If u could point me to the direction – Han Nan Feb 20 '19 at 23:19
  • PutDatabaseRecord is much like ConvertJSONToSQL (except the input doesn't have to be JSON) -> PutSQL, and it doesn't use `sql.args` attributes because it will use the actual values/types from each incoming record. – mattyb Mar 25 '19 at 03:51