BigQuery Connector Error - Json Exception

[npatrick]

Hi everyone, I am trying to use the BigQuery Connector, and am coming up with an error message that I am unable to diagnose. I am using 4.7.

I believe that I set up my JDBC correctly:

My Google Authentication appears to run OK:

But the BigQuery Connector fails:

Here is the error message:

And when I look at the log, this is what it says:
2023-01-20 10:24:32,226 : ERROR : KNIME-Worker-20-Google BigQuery Connector 3:7 : : Node : Google BigQuery Connector : 3:7 : Execute failed: com/google/api/client/googleapis/json/GoogleJsonResponseException
java.sql.SQLException: com/google/api/client/googleapis/json/GoogleJsonResponseException
at org.knime.database.util.Futures.throwDatabaseExecutionException(Futures.java:112)
at org.knime.database.connection.impl.DBConnectionManager.initializeConnection(DBConnectionManager.java:516)
at org.knime.database.connection.impl.DBConnectionManager.(DBConnectionManager.java:275)
at org.knime.database.session.impl.DefaultDBSession.(DefaultDBSession.java:223)
at org.knime.database.session.impl.DBSessionFactory.create(DBSessionFactory.java:81)
at org.knime.database.session.DBSessionCache.getOrCreate(DBSessionCache.java:151)
at org.knime.database.node.connector.AbstractDBConnectorNodeModel.registerSession(AbstractDBConnectorNodeModel.java:389)
at org.knime.database.node.connector.AbstractDBConnectorNodeModel.execute(AbstractDBConnectorNodeModel.java:219)
at org.knime.core.node.NodeModel.executeModel(NodeModel.java:549)
at org.knime.core.node.Node.invokeFullyNodeModelExecute(Node.java:1267)
at org.knime.core.node.Node.execute(Node.java:1041)
at org.knime.core.node.workflow.NativeNodeContainer.performExecuteNode(NativeNodeContainer.java:595)
at org.knime.core.node.exec.LocalNodeExecutionJob.mainExecute(LocalNodeExecutionJob.java:98)
at org.knime.core.node.workflow.NodeExecutionJob.internalRun(NodeExecutionJob.java:201)
at org.knime.core.node.workflow.NodeExecutionJob.run(NodeExecutionJob.java:117)
at org.knime.core.util.ThreadUtils$RunnableWithContextImpl.runWithContext(ThreadUtils.java:367)
at org.knime.core.util.ThreadUtils$RunnableWithContext.run(ThreadUtils.java:221)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
at org.knime.core.util.ThreadPool$MyFuture.run(ThreadPool.java:123)
at org.knime.core.util.ThreadPool$Worker.run(ThreadPool.java:246)
Caused by: java.lang.NoClassDefFoundError: com/google/api/client/googleapis/json/GoogleJsonResponseException
at com.simba.googlebigquery.googlebigquery.core.BQConnection.connect(Unknown Source)
at com.simba.googlebigquery.jdbc.common.BaseConnectionFactory.doConnect(Unknown Source)
at com.simba.googlebigquery.jdbc.common.AbstractDriver.connect(Unknown Source)
at org.knime.database.connection.UrlDBConnectionController$ControlledDriver.connect(UrlDBConnectionController.java:103)
at org.knime.database.connection.UrlDBConnectionController.connect(UrlDBConnectionController.java:368)
at org.knime.database.connection.UrlDBConnectionController.createConnection(UrlDBConnectionController.java:348)
at org.knime.database.connection.AbstractConnectionProvider.createConnection(AbstractConnectionProvider.java:103)
at org.knime.database.connection.impl.DBConnectionManager.lambda$2(DBConnectionManager.java:501)
at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.base/java.lang.Thread.run(Unknown Source)
Caused by: java.lang.ClassNotFoundException: com.google.api.client.googleapis.json.GoogleJsonResponseException
at java.base/java.net.URLClassLoader.findClass(Unknown Source)
at java.base/java.lang.ClassLoader.loadClass(Unknown Source)
at java.base/java.lang.ClassLoader.loadClass(Unknown Source)
… 12 more

Any thoughts?

[ipazin]

Hello @npatrick,

and welcome to KNIME Community!

It’s been a while since I connected to BigQuery but seems like scope is missing in Google Authentication (API Key) node.

Check Connecting to Google BigQuery example in DB documentation. There you’ll see they have defined scope for connection.
https://docs.knime.com/latest/db_extension_guide/index.html#connecting_examples

Br,
Ivan

[npatrick]

Thank you for your response. I did what you suggested, but it did not solve my problem.

I was able to get it to work, with the following changes:

1. I added both the directory and subdirectory for the Ddatabase driver setup:
   

   

2. I changed the database name in the Connector node to reflect the DatabaseID (unique code given by Google):
   

   

I am not sure why this made it work, but it did.

[seth_ekc]

@npatrick, Thank you. Sincerely. I’ve been struggling with this for quite some time.

Adding the subdir never occurred to me. It’s finally working. I owe you a coffee or a beer.