I’ve updated to KNIME 3.6.0, and I’m still seeing the same error.
This is very likely a bug in the JDBC driver and not in KNIME since the error occurs is in their code. You can submit a bug report with them using the stack trace you pasted above.
Thank you the quick reply thor. I’ve posted this issue on their forum:
Thor, Can you please chime on this thread: https://community.dremio.com/t/knime-error-when-loading-dremio-jdbc-driver/1655/4
But like I said before, that class is not the Dremio JDBC driver class, so you should check with KNIME documentation on how to set the Driver class to be com.dremio.jdbc.Driver (DBeaver has such an option).
Or it might be that KNIME has some kind of discovery process which involves finding all the drivers classes in a jar, instantiating them and trying to get information from them. Unfortunately the class you mentioned above is a special one, which is not supposed to be used as-is, which is why it’s not advertised by the jar using Java Service provider framework (which is how JDBC drivers are usually discovered, see https://docs.oracle.com/javase/7/docs/api/java/sql/DriverManager.html). I would recommend working with KNIME support/community to resolve that issue then.
I don’t have an account there. We scan the driver Jar for potential driver classes and therefore instantiate any class that implements java.sql.Driver. It seems that this particular class is only partially functional and throws an NPE when calling a simple getter method. We catch all kinds of exception when querying the driver but certainly not a NPE. I would argue that this is still a bug in their driver.
One thing you can try is using our new database integration that is currently in Labs. We basically rewrote everything and this particular driver issue may be handled differently.
I just installed the new KNIME Labs Database bundle. When I click on “Find driver classes”, as you can see, I’m getting:
ERROR DriverEditorDialog java.lang.NullPointerException
I have no problem loading this JDBC driver in dbeaver, it has a similar dialog and allows me to specify the class name as com.dremio.jdbc.Driver which works without any issues.
DBeaver probably doesn’t attempt to get the major version from the instantiated driver which is a perfectly valid operation and must not throw a NPE.
I have created a ticket to make the driver lookup in the new DB framework more robust against problematic driver implementations.
Thank you, Tobias.
DBeaver has a very robust implementation that is also Apache 2.0 license. You be able to reuse some of this code:
Hi there @allansene,
welcome to KNIME Community Forum!
It should work fine. Have you tried it out? Your feedback is appreciated
I am trying to use the Dremio JDBC driver in KNIME for Mac. I added the driver in the preferences and in the logs I get this:
2019-07-17 11:43:06,896 : DEBUG : main : DatabaseDriverLoader : : : Load driver from file: /Downloads/dremio-jdbc-driver-3.2.4-201906051751050278-1bcce62.jar
However, the driver doesn’t show up in the driver selection dropdown list in the database reader node.
There is no error in the logs. However, I noticed that for other drivers (e.g., jtds, postgres), the logs always have a “Load driver from file” entry followed by a “Driver loaded successful from file” entry and a “Adding driver to buildin map” entry. For the Dremio driver I only find the first entry in the logs, not the other two.
How can I further debug this?
Edit: FYI - I am using KNIME 3.7.2
can you please try it with KNIME 4.0 and the new database framework. For more details on how to register and use the driver with the new db framework have a look at the database documentation.
In the new framework we changed the driver registration which should solve the problem with the dremio driver.
thanks for the quick reply. I tested this but cannot connect due to the following error:
2019-07-17 14:27:18,604 : ERROR : KNIME-Worker-9 : : Node : DB Connector : 2:2 : Execute failed: DB Session 0d53f7d4-1961-4574-b972-fcd82006ec9f is invalid. null
java.lang.IllegalStateException: DB Session 0d53f7d4-1961-4574-b972-fcd82006ec9f is invalid. null
Caused by: java.sql.SQLFeatureNotSupportedException
… 15 more
I can rule out a couple of potential errors because connecting to Dremio via ODBC (Python) from the same machine using the same Dremio host and same credentials works.
For reference, this is what my KNIME configuration dialogs look like:
- Dremio connect string from here: https://docs.dremio.com/drivers/dremio-jdbc-driver.html
- Dremio JDBC driver from here: https://www.dremio.com/drivers/
- This is on KNIME 4 now
it seems as if the Dremio driver does not support the isValid() method. You can force KNIME to use a query to test the connection validity instead of using the not supported API method. To do so open the dialog of the connector node and go to the Advanced tab. In the tab add any valid Dremio query e.g. SELECT 0 that executes successfully as value of the Validation query setting.
You might also want to check the other advanced settings to see if they are compatible with Dremios capacities e.g. if it supports CASE statements and the identifier delimiter. It might also help you to identify problems if you enable the JDBC Logger in the beginning which log all statements to the KNIME log.
thanks for the hint! This helped. I can now connect to the database (i.e., the DB Connector turns green). However, when running a query, I get the following error message from Dremio:
ERROR DB Reader 0:3 Execute failed: Can’t turn off auto-committing; transactions are not supported. (Dremio is not transactional.)
From the following Dremio help topic, I gather that a possible reason might be that KNIME disables autocommit in the JDBC connection: https://community.dremio.com/t/exception-during-pool-initialization-cant-turn-off-auto-committing-transactions-are-not-supported-dremio-is-not-transactional/1656/3
Is this the case?
I didn’t find autocommit as a setting in KNIME. Is there a way to enable it for this connection?
Same problem here.
[EDIT]: Related discussion on Dremio’s Community:
this is unfortunate. Currently it is not possible to disable the transaction handling in KNIME. However we already have a ticket to allow the user to disable transaction handling. I’m sorry to say this but until this is fixed you won’t be able to use the new DB nodes with Dremio. I will keep you updated as soon as this is fixed.
we have just released KNIME 4.0.1 which now allows to disable transactions in the new DB framework (AP-12114).
To disable transactions simply open the dialog of the DB Connector node and go to the Advanced tab where you can disable transactions
For further details about the release see the changelog. To update simply open KNIME and go to File->Update KNIME…
the driver registration problem has been fixed with the 4.0 release of the KNIME Analytics Platform.