tBigQueryOutput Read TimedOut

[JamesR1]

Occasionally when running a job that extracts data from MySQL via tMySqlInput to tBigQueryOutput I get a "Read Timed Out" error when the job is loading into BigQuery from Google Cloud Storage. 

When I check BigQuery, all the data has been loaded properly. So it appears the problem is just the amount of time it takes the job to finish and the "waiting on job" polling of the tBigQueryOutput component times out.

Reading other posts, it seems it is due to an http timeout error but I am not sure where or how to adjust this setting to keep from getting the error.

I am running TOS_BD 6.3.0.20161029_1219 on MacOS 10.12.2.

Has anyone else run into this problem and have a solution? Thank you.

Here is the stack trace.

Waiting on job job_7zeTwY3AjbDDcsR3FFy5k5yXZc4 ... Current status: RUNNING
Exception in component tBigQueryOutput_1_tBQBE
java.net.SocketTimeoutException: Read timed out
at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)
at java.net.SocketInputStream.read(SocketInputStream.java:170)
at java.net.SocketInputStream.read(SocketInputStream.java:141)
at sun.security.ssl.InputRecord.readFully(InputRecord.java:465)
at sun.security.ssl.InputRecord.read(InputRecord.java:503)
at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:973)
at sun.security.ssl.SSLSocketImpl.readDataRecord(SSLSocketImpl.java:930)
at sun.security.ssl.AppInputStream.read(AppInputStream.java:105)
at java.io.BufferedInputStream.fill(BufferedInputStream.java:246)
at java.io.BufferedInputStream.read1(BufferedInputStream.java:286)
at java.io.BufferedInputStream.read(BufferedInputStream.java:345)
at sun.net.www.http.HttpClient.parseHTTPHeader(HttpClient.java:704)
at sun.net.www.http.HttpClient.parseHTTP(HttpClient.java:647)
at sun.net.www.protocol.http.HttpURLConnection.getInputStream0(HttpURLConnection.java:1536)
at sun.net.www.protocol.http.HttpURLConnection.getInputStream(HttpURLConnection.java:1441)
at java.net.HttpURLConnection.getResponseCode(HttpURLConnection.java:480)
at sun.net.www.protocol.https.HttpsURLConnectionImpl.getResponseCode(HttpsURLConnectionImpl.java:338)
at com.google.api.client.http.javanet.NetHttpResponse.<init>(NetHttpResponse.java:36)
at com.google.api.client.http.javanet.NetHttpRequest.execute(NetHttpRequest.java:94)
at com.google.api.client.http.HttpRequest.execute(HttpRequest.java:965)
at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:410)
at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:343)
at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.execute(AbstractGoogleClientRequest.java:460)
at thrust2bq.streameventsarchive_0_1.StreamEventsArchive.tMysqlInput_1Process(StreamEventsArchive.java:2964)
at thrust2bq.streameventsarchive_0_1.StreamEventsArchive.runJobInTOS(StreamEventsArchive.java:3376)
at thrust2bq.streameventsarchive_0_1.StreamEventsArchive.runJob(StreamEventsArchive.java:3241)
at thrust2bq.streamevent_archive_loop_0_1.StreamEvent_Archive_Loop.tLoop_1Process(StreamEvent_Archive_Loop.java:1454)
at thrust2bq.streamevent_archive_loop_0_1.StreamEvent_Archive_Loop.runJobInTOS(StreamEvent_Archive_Loop.java:1921)
at thrust2bq.streamevent_archive_loop_0_1.StreamEvent_Archive_Loop.main(StreamEvent_Archive_Loop.java:1727)
Exception in component tRunJob_2
java.lang.RuntimeException: Child job running failed
at thrust2bq.streamevent_archive_loop_0_1.StreamEvent_Archive_Loop.tLoop_1Process(StreamEvent_Archive_Loop.java:1485)
at thrust2bq.streamevent_archive_loop_0_1.StreamEvent_Archive_Loop.runJobInTOS(StreamEvent_Archive_Loop.java:1921)
at thrust2bq.streamevent_archive_loop_0_1.StreamEvent_Archive_Loop.main(StreamEvent_Archive_Loop.java:1727)
[statistics] disconnected

[Anonymous]

Hi,
Here is a jira issue: https://jira.talendforge.org/browse/TDI-30724.
Could you please take a look at it?
Best regards
Sabrina

[JamesR1]

Thank you for creating the Jira ticket. 

To clarify, I can execute SELECT queries without timeout.

The Read Timeout occurs when the tBigQueryOutput component is polling BigQuery for the job status on a long running load job.

After the read timeout error and the BigQuery job has finished loading, it appears all the data that was sent to BigQuery was indeed loaded.

NOTE: The error does not occur every time, but when it does it prevents the rest of my job from running.

Thank you for any information, workaround or fixes you can provide.

[Anonymous]

Hi,
Is it a random issue? It doesn't work even you set  timeout for a query according to doc: https://cloud.google.com/bigquery/docs/reference/v2/jobs/query?
Could you please give us more information to reproduce your issue so that we will make an investigation on your issue to see if there is any work around or solution?
Best regards
Sabrina

[vishalg19]

Is there any solution or workaround this

I am also facing a similar issue

by default timeout is set to 30 sec while my loading job is taking around 32-34 sec so I always get this error 

Message- com.google.cloud.bigquery.BigQueryException:com.google.api.gax.retrying.PollException: total timeout or maximum number of attempts exceeded; current settings: RetrySettings{totalTimeout=PT30S, initialRetryDelay=PT1S, retryDelayMultiplier=1.0, maxRetryDelay=PT3S, maxAttempts=0, jittered=true, initialRpcTimeout=PT0S, rpcTimeoutMultiplier=1.0, maxRpcTimeout=PT0S}

but my data is loaded always

I do not see any option to change timeout or any additional parameter

[Anonymous]

Hello,

Could you please let us know if it is what you are looking for?

https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query.

Here exists a jira issue:https://jira.talendforge.org/browse/TBD-8417

Best regards

Sabrina

[vishalg19]

 Hi Sabrina,

Yes! this is what I am referring. Right now there is no option to change Parameters in tbigqueryoutput like timeout and maxattempt.

thanks for the ticket. I will keep an eye on that.

Also is there an option available in which I can process my data in batches? so that it won't timeout

[kotvir]

Hi Guys, have you found the solution for this issue ?