As I’m experimenting with the DB nodes and BigQuery, I noticed that neither DB Reader nor Query Reader work with BigQuery connections.
Also, caching a sample of rows on DB Query and other similar nodes doesn’t work.
In all cases I get the same error: Execute failed: [Simba][JDBC](10220) Driver does not support this optional feature.
I have enabled JDBC logging in the DB Connector node, but the culprit SQL doesn’t appear in the KNIME log.
The original Database nodes work fine with BigQuery, I can connect, exectue queries and read the results into KNIME.
I’m using Simba JDBC dirver, version 1.2.0
Does anyone know if there is a way to fix, or at least debug this?
1 Like
Hi Dimitri,
try to change the identifier delimiter in the DB Connector node from the default " to backtick `.
If the problem still persists you can enable the JDBC logging. Once enabled all queries are logged in the KNIME log. This way we can hopefully identify the not supported feature. To enable the query logging open the node dialog of the DB Connector node and go to the Advanced tab. Here you enable the “JDBC logger” option.
Once this option is enabled all query are logged into the KNIME log which you can open in KNIME via View->Open KNIME Log.
Bye
Tobias
1 Like
Thank you, Tobias.
Sorry, I forgot to mention that I have done both of those before reporting this issue (nothing worked at all until I replaced " with `).
Even with JDBC loggin ON I still can’t see the SQL code, all I see in the log is this:
2019-06-24 10:54:16,233 : DEBUG : KNIME-Worker-4 : LocalNodeExecutionJob : DB Query Reader (Labs) : 0:98 : DB Query Reader (Labs) 0:98 Start execute
2019-06-24 10:54:16,251 : DEBUG : KNIME-Worker-4 : DBConnectionManager : DB Query Reader (Labs) : 0:98 : Acquiring connection.
2019-06-24 10:54:16,251 : DEBUG : KNIME-Worker-4 : DBConnectionManager : DB Query Reader (Labs) : 0:98 : A connection has been acquired.
2019-06-24 10:54:16,251 : INFO : KNIME-Worker-4 : jdbc_audit : DB Query Reader (Labs) : 0:98 : 4. Connection opened for URL jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443/;<project-name-redacted>;OAuthType=1;
2019-06-24 10:54:16,258 : INFO : KNIME-Worker-4 : jdbc_audit : DB Query Reader (Labs) : 0:98 : 4. Connection closed
2019-06-24 10:54:16,258 : DEBUG : KNIME-Worker-4 : Node : DB Query Reader (Labs) : 0:98 : reset
2019-06-24 10:54:16,259 : ERROR : KNIME-Worker-4 : Node : DB Query Reader (Labs) : 0:98 : Execute failed: [Simba][JDBC](10220) Driver does not support this optional feature.
2019-06-24 10:54:16,259 : DEBUG : KNIME-Worker-4 : Node : DB Query Reader (Labs) : 0:98 : Execute failed: [Simba][JDBC](10220) Driver does not support this optional feature.
Hello Dimitri,
I have found the problem. The new framework automatically disables the auto commit mode using a JDBC function call because some databases require this. Unfortunately the Simba driver does not implement this function and instead is throwing an exception. Unfortunately there is no workaround for this in the new framework as of now. So you need to use the old framework until this is fixed in the new framework. We plan to have a dedicated Big Query Connector for the new DB framework in once of the next releases which will fix this issue and will make working with BigQuery within KNIME Analytics Platform easier.
Bye
Tobias
4 Likes
Hello,
transactions can no disabled via the Advanced tab of the DB Connector node:
Bye
Tobias
1 Like
