NiFi 1.10 PutSQL Error : query must be like INSERT

Question

I'm trying to execute the SQL command "Truncate Table". All the post I've seen recommand using PutSQL for that.

I've the error message :

ERROR
PutSQL[id=a154204d-016e-1000-d9c9-d0d79519e70e] Failed to update database for
[StandardFlowFileRecord[uuid=6c92c3bc-a219-4017-9641-dc4d21ab061a,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1574662524581-1, container=default, section=1], offset=3900, length=35],offset=0,name=6c92c3bc-a219-4017-9641-dc4d21ab061a,size=35]] 
due to Query must be like 'INSERT INTO [db.]table [(c1, c2, c3)] VALUES (?, ?, ?)'. 
Got: truncate table SBST.geographicalcat; 
routing to failure: java.sql.SQLSyntaxErrorException: Query must be like 'INSERT INTO [db.]table [(c1, c2, c3)] VALUES (?, ?, ?)'. 
Got: truncate table SBST.geographicalcat

I use all default parameters from NiFi 1.10 processor. I've try with the truncate query as constant in the SQL Statement parameter or this one empty and the SQL comming from the incoming flow file.

The database is Clickhouse, using last JDBC. The truncate command work when in the Pre-SQL of the ExecuteSQL processor. So NiFi and the JDBC should be able to run it.

The NiFi doc explain that PutSQL is only for INSERT or UPDATE. It look like PutSQL is not allowing anything else than Insert / Update, or is there a parameter that I should change ? Any other solution ?

Answer 1

If the target database is Clickhouse, you can use http-interface instead of JDBC.

NiFi processor InvokeHTTP doc:

https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-standard-nar/1.5.0/org.apache.nifi.processors.standard.InvokeHTTP/

Use POST-method for data modifications (GET is only for selects)

Clickhouse http-interface docs:

https://clickhouse.yandex/docs/en/interfaces/http/

Answer 2

For the moment I've done it with a workaround, but I need to try Eduard Golubov proposition that should also work.

From the GetFile, a first Success branch go to the Execute SQL that have a Pre-Query that do the Truncate, and a Query that do a simple select query (here a select count() of the truncated table that should return 0 and could be used as check), then that go to a Notify to inform that the truncate is done.

From the GetFile, a Second Success branch go to the Wait (for the truncate to be done), than move to the PutDatabaseRecord (with a UpdateAttibute before to set the schema.name).

---

Answer 3

This might be a Clickhouse bug, this appears to be a related issue. Also what is your setting for Batch Size? If > 1, try setting it to 1. Also what is the value of Support Fragmented Transactions? That should be false as well as Obtain Generated Keys.