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?