BigQuery Connector Error

alfred_kc_tang · November 9, 2022, 6:50am

Hello fellas,

I can connect to BigQuery using the BigQuery Connector in my company laptop. However, our KNIME server fails to connect with the following HttpTransport IO error:

2022-11-01 18:01:06,044 : DEBUG : main :  : ExecuteAction :  :  : Creating execution job for 1 node(s)...
2022-11-01 18:01:06,044 : DEBUG : main :  : NodeContainer :  :  : Google BigQuery Connector 3:7 has new state: CONFIGURED_MARKEDFOREXEC
2022-11-01 18:01:06,045 : DEBUG : main :  : NodeContainer :  :  : Google BigQuery Connector 3:7 has new state: CONFIGURED_QUEUED
2022-11-01 18:01:06,046 : DEBUG : main :  : NodeContainer :  :  : BigQuery_Connector_Workflow 3 has new state: EXECUTING
2022-11-01 18:01:06,047 : DEBUG : KNIME-Worker-8-Google BigQuery Connector 3:7 :  : WorkflowManager : Google BigQuery Connector : 3:7 : Google BigQuery Connector 3:7 doBeforePreExecution
2022-11-01 18:01:06,047 : DEBUG : KNIME-Worker-8-Google BigQuery Connector 3:7 :  : NodeContainer : Google BigQuery Connector : 3:7 : Google BigQuery Connector 3:7 has new state: PREEXECUTE
2022-11-01 18:01:06,047 : DEBUG : KNIME-Worker-8-Google BigQuery Connector 3:7 :  : WorkflowDataRepository : Google BigQuery Connector : 3:7 : Adding handler 253e6b0d-7fbd-4c20-a428-5181481c6e7f (Google BigQuery Connector 3:7: <no directory>) - 3 in total
2022-11-01 18:01:06,048 : DEBUG : KNIME-Worker-8-Google BigQuery Connector 3:7 :  : WorkflowManager : Google BigQuery Connector : 3:7 : Google BigQuery Connector 3:7 doBeforeExecution
2022-11-01 18:01:06,048 : DEBUG : KNIME-Worker-8-Google BigQuery Connector 3:7 :  : NodeContainer : Google BigQuery Connector : 3:7 : Google BigQuery Connector 3:7 has new state: EXECUTING
2022-11-01 18:01:06,048 : DEBUG : KNIME-Worker-8-Google BigQuery Connector 3:7 :  : LocalNodeExecutionJob : Google BigQuery Connector : 3:7 : Google BigQuery Connector 3:7 Start execute
2022-11-01 18:01:06,545 : DEBUG : KNIME-Worker-8-Google BigQuery Connector 3:7 :  : DBSessionCache : Google BigQuery Connector : 3:7 : New database session: DefaultDBSessionInformation(id=8fa7ee78-1ca6-4599-9329-fce9a1820bae, dbType=DBType(id=bigquery, name=Google BigQuery, description=Google BigQuery), driverDefinition=DBDriverDefinition(id=BQGCP120, name=BQGCP120, version=1.2.0, driverClass=com.simba.googlebigquery.jdbc42.Driver, dbType=DBType(id=bigquery, name=Google BigQuery, description=Google BigQuery), description=, origin=USER), connectionController=org.knime.database.extension.bigquery.connection.GoogleOAuthDBConnectionController=(url=jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=ric-eu-apacanalytics-nprd), dialectId=bigquery, attributeValues={knime.db.dialect.sql.identifier.replace.character.non_word.enabled=false})
2022-11-01 18:01:23,138 : DEBUG : main :  : NodeContainerEditPart :  :  : Google BigQuery Connector 3:7 (EXECUTING)
2022-11-01 18:01:27,231 : INFO  : KNIME-Database-Connection-Creator-4 :  : UrlDBConnectionController : Google BigQuery Connector : 3:7 : Could not create connection to database using URL: jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=ric-eu-apacanalytics-nprd and parameters: [OAuthType, OAuthServiceAcctEmail, OAuthPvtKeyPath]. Exception: [Simba][BigQueryJDBCDriver](100004) HttpTransport IO error : {0}.
2022-11-01 18:01:27,232 : DEBUG : KNIME-Worker-8-Google BigQuery Connector 3:7 :  : Node : Google BigQuery Connector : 3:7 : reset
2022-11-01 18:01:27,232 : ERROR : KNIME-Worker-8-Google BigQuery Connector 3:7 :  : Node : Google BigQuery Connector : 3:7 : Execute failed: [Simba][BigQueryJDBCDriver](100004) HttpTransport IO error : {0}.
java.sql.SQLException: [Simba][BigQueryJDBCDriver](100004) HttpTransport IO error : {0}.
	at com.simba.googlebigquery.googlebigquery.client.BQClient.createAndTestClient(Unknown Source)
	at com.simba.googlebigquery.googlebigquery.client.BQClient.serviceAccountOAuth(Unknown Source)
	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)
Caused by: com.simba.googlebigquery.support.exceptions.GeneralException: [Simba][BigQueryJDBCDriver](100004) HttpTransport IO error : {0}.
	... 13 more
Caused by: com.simba.googlebigquery.support.exceptions.GeneralException: EXEC_JOB_TIMEOUT_ERR
	at com.simba.googlebigquery.googlebigquery.client.BQClient.waitToRetryOrStop(Unknown Source)
	at com.simba.googlebigquery.googlebigquery.client.BQClient.insertJob(Unknown Source)
	at com.simba.googlebigquery.googlebigquery.client.BQClient.prepare(Unknown Source)
	... 13 more
2022-11-01 18:01:27,233 : DEBUG : KNIME-Worker-8-Google BigQuery Connector 3:7 :  : WorkflowManager : Google BigQuery Connector : 3:7 : Google BigQuery Connector 3:7 doBeforePostExecution
2022-11-01 18:01:27,233 : DEBUG : KNIME-Worker-8-Google BigQuery Connector 3:7 :  : NodeContainer : Google BigQuery Connector : 3:7 : Google BigQuery Connector 3:7 has new state: POSTEXECUTE
2022-11-01 18:01:27,233 : DEBUG : KNIME-Worker-8-Google BigQuery Connector 3:7 :  : WorkflowManager : Google BigQuery Connector : 3:7 : Google BigQuery Connector 3:7 doAfterExecute - failure
2022-11-01 18:01:27,233 : DEBUG : KNIME-Worker-8-Google BigQuery Connector 3:7 :  : Node : Google BigQuery Connector : 3:7 : reset
2022-11-01 18:01:27,234 : DEBUG : KNIME-Worker-8-Google BigQuery Connector 3:7 :  : Node : Google BigQuery Connector : 3:7 : clean output ports.
2022-11-01 18:01:27,234 : DEBUG : KNIME-Worker-8-Google BigQuery Connector 3:7 :  : WorkflowDataRepository : Google BigQuery Connector : 3:7 : Removing handler 253e6b0d-7fbd-4c20-a428-5181481c6e7f (Google BigQuery Connector 3:7: <no directory>) - 2 remaining
2022-11-01 18:01:27,234 : DEBUG : KNIME-Worker-8-Google BigQuery Connector 3:7 :  : NodeContainer : Google BigQuery Connector : 3:7 : Google BigQuery Connector 3:7 has new state: IDLE
2022-11-01 18:01:27,235 : DEBUG : KNIME-Worker-8-Google BigQuery Connector 3:7 :  : Node : Google BigQuery Connector : 3:7 : Configure succeeded. (Google BigQuery Connector)
2022-11-01 18:01:27,235 : DEBUG : KNIME-Worker-8-Google BigQuery Connector 3:7 :  : NodeContainer : Google BigQuery Connector : 3:7 : Google BigQuery Connector 3:7 has new state: CONFIGURED
2022-11-01 18:01:27,236 : DEBUG : KNIME-Worker-8-Google BigQuery Connector 3:7 :  : NodeContainer : Google BigQuery Connector : 3:7 : BigQuery_Connector_Workflow 3 has new state: IDLE

As far as I know, the network settings are the same. Would there be any solution to connect with BigQuery? Thanks a lot!

Best,
Alfred

sascha.wolke · November 14, 2022, 5:55pm

Hi @alfred_kc_tang,

Welcome to the KNIME community!

Not sure what the error message from the Simba driver means. Does the KNIME Server have internet access?

Maybe increasing the timeout helps on long-running jobs, see: from BigQuery How to increase the timeout value? - #7 by Ala

Best,
Sascha

alfred_kc_tang · November 22, 2022, 2:30am

Thanks for your reply, Sascha!

The KNIME Server has the internet access, and it is hosted on AWS.

I have tried increasing the timeout as you suggested, but still in vain. I have enabled the JDBC logger to log more information, here is the log:

2022-11-15 14:43:32,848 : ERROR : KNIME-Worker-9-Google BigQuery Connector 3:7 :  : Node : Google BigQuery Connector : 3:7 : Execute failed: [Simba][BigQueryJDBCDriver](100004) HttpTransport IO error : Connect timed out.
java.sql.SQLException: [Simba][BigQueryJDBCDriver](100004) HttpTransport IO error : Connect timed out.
	at com.simba.googlebigquery.googlebigquery.client.BQClient.createAndTestClient(Unknown Source)
	at com.simba.googlebigquery.googlebigquery.client.BQClient.serviceAccountOAuth(Unknown Source)
	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)
Caused by: com.simba.googlebigquery.support.exceptions.GeneralException: [Simba][BigQueryJDBCDriver](100004) HttpTransport IO error : Connect timed out.
	... 13 more
Caused by: com.simba.googlebigquery.support.exceptions.GeneralException: EXEC_JOB_EXECUTION_ERR
	at com.simba.googlebigquery.googlebigquery.client.BQClient.throwExecException(Unknown Source)
	at com.simba.googlebigquery.googlebigquery.client.BQClient.insertJob(Unknown Source)
	at com.simba.googlebigquery.googlebigquery.client.BQClient.prepare(Unknown Source)
	... 13 more
2022-11-15 14:45:08,984 : ERROR : KNIME-Worker-10-Google BigQuery Connector 3:7 :  : Node : Google BigQuery Connector : 3:7 : Execute failed: [JDBC Driver]null
java.sql.SQLException: [JDBC Driver]null
	at com.google.common.base.Preconditions.checkArgument(Preconditions.java:127)
	at com.google.api.client.util.Preconditions.checkArgument(Preconditions.java:35)
	at com.google.api.client.util.ExponentialBackOff.<init>(ExponentialBackOff.java:152)
	at com.google.api.client.util.ExponentialBackOff$Builder.build(ExponentialBackOff.java:302)
	at com.simba.googlebigquery.googlebigquery.client.BQClient.insertJob(Unknown Source)
	at com.simba.googlebigquery.googlebigquery.client.BQClient.prepare(Unknown Source)
	at com.simba.googlebigquery.googlebigquery.client.BQClient.createAndTestClient(Unknown Source)
	at com.simba.googlebigquery.googlebigquery.client.BQClient.serviceAccountOAuth(Unknown Source)
	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.IllegalArgumentException

Thank you again for the help

sascha.wolke · November 22, 2022, 9:42am

Hi @alfred_kc_tang,

Is this a KNIME Server from the AWS Marketplace or deployed by yourself?

Maybe you have to open the firewall and allow outgoing connections to port 443. You can use the HTTP(S) Connector with e.g. https://www.googleapis.com to ensure the Server can connect to the Google endpoint. Does the node work?

Cheers,
Sascha

alfred_kc_tang · November 25, 2022, 6:54am

Hi Sascha,

Thanks for your suggestion!

I tried pinging googleapis.com on both my local machine and the KNIME server, the requests also timed out for the two instances. However, the BigQuery Connector CAN connect with BigQuery on my local machine, but it cannot on our KNIME server hosted on AWS.

The KNIME Server is managed and built using the AMI from AWS. I checked with our Cloud team that there is no restriction on outgoing traffic on the AWS instance, and the firewall is only for controlling the incoming traffic into the instance.

Cheers,
Alfred

system · February 23, 2023, 6:54am

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.