KB486199: Google BigQuery JDBC driver experiences timeout issues when using the Storage API on a proxy server

Quality Engineer, Senior • MicroStrategy

This article explains how to resolve a timeout issue with the Google BigQuery JDBC driver when using the Storage API on a proxy server.

Symptom

The Google BigQuery JDBC driver supports the Storage API on a proxy server since Strategy ONE Update 12, by adding the connection parameters “ProxyHost”, “ProxyPort”, “ProxyUID”, and “ProxyPWD”. The usage of this API is recorded in the logs as “Retrieving data using the Storage API." Once you start using this API, you should notice improved performance.

However, you may encounter a timeout issue if your proxy server does not require credentials. The driver may take ten or more minutes to communicate with the proxy servers and then return your results set.

Root Cause

This issue has been confirmed as a driver defect when using a proxy setting with no authentication. The driver experiences issues when the proxy uid and pwd are empty, causing a null pointer error.
Sample error:


Nov 17 04:07:40.981 INFO  242 com.Strategy.googlebigquery.googlebigquery.client.requests.jobs.JobsGetRequest.execute: executed request jobs.get. id:Strategy-sr:US.job_xQpHuPFcFHFtbPiIeHjZ6rAPjXP-, query:SELECT * FROM GTA.CUSTOMER LIMIT 20000, status:DONE 
Nov 17 04:17:41.005 INFO  242 com.Strategy.googlebigquery.googlebigquery.dataengine.BQBufferManager.processTheFirstPage: STORAGE_API_INIT_ERR
Caused by: com.Strategy.googlebigquery.shaded.google.api.gax.rpc.DeadlineExceededException: com.Strategy.googlebigquery.shaded.io.grpc.StatusRuntimeException: DEADLINE_EXCEEDED: Deadline CallOptions will be exceeded in 599.999048500s.

Workaround

Give the ProxyUid and ProxyPwd parameters an empty or dummy value.


eg: ProxyHost=xxx; ProxyPort=xxx; ProxyUid=<dummy>; ProxyPwd=<dummy>

Sample connection string:


JDBC; DRIVER={com.Strategy.googlebigquery.jdbc42.Driver}; URL={ jdbc:bigquery://https://www.googleapis.com/bigquery/v2; ProjectId=your project; OAuthType=2; OAuthRefreshToken=; OAuthClientId=; OAuthClientSecret=; EnableHighThroughputAPI=1; StringColumnLength=65535; Timeout=300; AllowLargeResults=0; HighThroughputActivationRatio=1; HighThroughputMinTableSize=100; MaxResults=10000;ProxyHost=xxx; ProxyPort=xxx; ProxyUid=1; ProxyPwd=1};

Comment

0 comments

Details

Knowledge Article

Published:

December 13, 2023

Last Updated:

December 15, 2023

Symptom

Root Cause

Nov 17 04:07:40.981 INFO 242 com.Strategy.googlebigquery.googlebigquery.client.requests.jobs.JobsGetRequest.execute: executed request jobs.get. id:Strategy-sr:US.job_xQpHuPFcFHFtbPiIeHjZ6rAPjXP-, query:SELECT * FROM GTA.CUSTOMER LIMIT 20000, status:DONE Nov 17 04:17:41.005 INFO 242 com.Strategy.googlebigquery.googlebigquery.dataengine.BQBufferManager.processTheFirstPage: STORAGE_API_INIT_ERR Caused by: com.Strategy.googlebigquery.shaded.google.api.gax.rpc.DeadlineExceededException: com.Strategy.googlebigquery.shaded.io.grpc.StatusRuntimeException: DEADLINE_EXCEEDED: Deadline CallOptions will be exceeded in 599.999048500s.

Workaround

Give the ProxyUid and ProxyPwd parameters an empty or dummy value.

eg: ProxyHost=xxx; ProxyPort=xxx; ProxyUid=<dummy>; ProxyPwd=<dummy>

JDBC; DRIVER={com.Strategy.googlebigquery.jdbc42.Driver}; URL={ jdbc:bigquery://https://www.googleapis.com/bigquery/v2; ProjectId=your project; OAuthType=2; OAuthRefreshToken=; OAuthClientId=; OAuthClientSecret=; EnableHighThroughputAPI=1; StringColumnLength=65535; Timeout=300; AllowLargeResults=0; HighThroughputActivationRatio=1; HighThroughputMinTableSize=100; MaxResults=10000;ProxyHost=xxx; ProxyPort=xxx; ProxyUid=1; ProxyPwd=1};