SQL request

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
Report Content as Inappropriate

SQL request

Arnaud G
Hi all,

I just have 2 small questions regarding SQL in Nifi.

1) What is the best way to execute arbitrary SQL request? For instance, I have a lot of use cases where I'm doing a couple of file transformation/deduplication that require to initiate copy of one table to another another with complex join, truncate/delete a table etc. and I would like to be able to do that directly in SQL.
I know that there is an executeSQL processor but I don't think that the name reflect the function as in fact it only really supports SELECT operation. If I want to do an INSERT from SELECT it won't work. I was able to use the putSQL processor to trigger delete but again I think that I'm hacking the expected behaviour. My current workaround is to use groovy scripts but I feel that this is not the optimal solution.

2) The current PutSQL processor wants to disable autocommit at JDBC level. This is fine for database that support commit, but for instance we are using PrestoDB (with Terradata JDBC driver) and the driver doesn't support to disable autocommit and it ends with an error. Do you think that it will be possible to put that as an option in the processor?