Java GCP bigquery simba jdbc connection timeout

Question

I am trying to connect GCP bigQuery from local using simba jdbc but getting error. Previously I had got Service account role permission issue , those were resolved by adding necessary roles for service account

java.sql.SQLException: [Simba][BigQueryJDBCDriver](100004) HttpTransport IO error : Error getting access token for service account: connect timed out, iss: [email protected].
    at com.simba.googlebigquery.googlebigquery.client.BQClient.createAndTestClient(Unknown Source)
    at com.simba.googlebigquery.googlebigquery.client.BQClient.authenticateAndTest(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.AbstractDataSource.getSimbaConnection(Unknown Source)
    at com.simba.googlebigquery.jdbc.common.AbstractDataSource.getConnection(Unknown Source)
Caused by: com.simba.googlebigquery.support.exceptions.GeneralException: [Simba][BigQueryJDBCDriver](100004) HttpTransport IO error : Error getting access token for service account: connect timed out, iss: [email protected].
    ... 6 more
Caused by: com.simba.googlebigquery.googlebigquery.client.exceptions.JobExecutionErrorException: EXEC_JOB_EXECUTION_ERR
    at com.simba.googlebigquery.googlebigquery.client.requests.jobs.JobsInsertRequest.throwException(Unknown Source)
    at com.simba.googlebigquery.googlebigquery.client.requests.AbstractRequestWithRetry.executeWithRetry(Unknown Source)
    at com.simba.googlebigquery.googlebigquery.client.queryclient.QueryClient.executeDryRun(Unknown Source)
    at com.simba.googlebigquery.googlebigquery.client.BQClient.prepare(Unknown Source)
    at com.simba.googlebigquery.googlebigquery.client.BQClient.createAndTestClient(Unk

import com.simba.googlebigquery.jdbc42.DataSource;

import java.sql.Connection;
import java.sql.SQLException;

public class TEstConnection {
 public static void main(String[] args) {
  System.out.println("Connection check");

  Connection connection = null;
  DataSource ds = new
          com.simba.googlebigquery.jdbc42.DataSource();
  ds.setProjectId("xxx");
  ds.setOAuthPvtKeyFilePath("C:\\Users\\xx\\UI\\GCP\\devgcp.json");
  ds.setOAuthType(0);
  ds.setOAuthServiceAcctEmail("[email protected]");
  ds.setURL("jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443");
  ds.setTimeout(40000000L);
  try {
   connection = ds.getConnection();
  } catch (SQLException e) {
    e.printStackTrace();
  }
 }
}

The service account has required role permission but still I am getting error

Answer 1

There are two ways authenticating your service account. You might have done either of the two: OAuth 2.0 and Service Account based authentication And you might want to do the authentication again.

Here is a URL to use to connect to Google BigQuery using Progress DataDirect BigQuery JDBC Connector using Service Account based authentication.

jdbc:datadirect:googlebigquery:AuthenticationMethod=serviceaccount;Project=your-project-id;Dataset=your-dataset;[email protected];ServiceAccountPrivateKey=/path/to/json-key-file

To authenticate the service account via OAuth 2.0, access and refresh tokens are needed. To get the tokens, OAuth ClientID must be created to get ClientID and Client Secret needed to get the authorization code. Here is the complete and detailed guide on how to authenticate via OAuth 2.0.