I am getting these errors —>
Execute failed: A local transaction already exists.
OR
Error during fetching data from the database: java.sql.SQLException: A local transaction already exists.
OR
TransactionContext Error: cannot rollback - no transaction is active
when trying to execute a DuckDB query in the DB nodes downstream of the DB Connector. Using Knime on macOS. What step am I missing?
Helpful links I’ve already explored: DuckDB is an in-process SQL OLAP database management system
A DuckDB team member showing screenshots connecting DuckDB to Knime via JDBC
I haven’t used DuckDB myself at all, but based on your screenshots, the SQL statement in the DB Table Selector looks wrong.
But first, are you sure you’re connected to the database? Typically you need to point to the database using the ‘Database URL’ field in the DB Connector. Stealing the screenshot from your twitter thread, pay close attention to this bit:
Then, instead of providing a filepath to your table as shown, what happens when you click the ‘Select a table’ button in the top right of the dialog? If you are connected to the database it should show you a list of available tables to choose from. At that point the default Select * from #TABLE# syntax should grab what you need.
Thanks for the response Scott. I haven’t established any tables or schema in the duckDB database. However, per the feedback from the DuckDB team I was told I could query a parquet file directly similar to querying a table.
Try “Custom Query” and something like “SELECT * FROM ‘/path/to/my/parquet_file.parquet’” in the box that it shows
I did notice that when I run the query in my above screenshots, with the path to the parquet file, Knime is able to read the columns headers of the file, but not any of the rows of data.
I will try creating a DuckDB table and see if that works, but any thoughts, why the workflow is able to read the columns but not the rows from the parquet file?
@gcraig I tried a few things and I am able to establish a connection to a duckdb which gets created and I can execute commands to create a table and also a schema but then get stuck with the error message about open transactions from your screenshot.
What I found is this passage
Note
If you absolutely must use insert statements in a loop to load data, wrap them in calls to BEGIN TRANSACTION and COMMIT.
I tried that also. The code would run (green node) but no final success.
It might be that the driver needs some sort of auto commit to actually do the transformation.
Maybe you could reach out to the developer from the Twitter post?
Hello @gcraig ,
can you try to disable the transaction handling. To do so open the Advanced tab of the DB Connector node and disable Transaction support:
I disabled the transaction option like you mentioned and I am able to query a parquet file using the DuckDB JDBC driver. For my specific use case I tried it with just an in memory DuckDB database (in the Connections Settings tab of the DB Connector, Database URL = jdbc:duckdb:
@gcraig After @tobias.koetter gave the hint I toyed around again with DuckDb and created these examples. Some SQL code needs dome adaption but in general it would work. Tested to insert a Parquet file and auto-detect a CSV structure. But to be honest the handling is not as straight forward with KNIME as with H2 and SQLite - but indeed you could use DuckDB and KNIME
