Connection to bigquery.googleapis.com sometimes times out.

[mukaiyama-t]

Hello. I am using google/cloud-bigquery: v1.23.7. I often get a timeout when executing a query and no results are returned, and I can't figure out why.

The query itself is not heavy, but is as follows.

SELECT * FROM search_trend.youtube_channel_trend_genre LIMIT 1

The error is as follows.

Google\Cloud\Core\Exception\ServiceException with message 'cURL error 28: Failed to connect to bigquery.googleapis.com port 443 after 75202 ms: Operation timed out (see https://curl.haxx.se/libcurl/c/libcurl-errors.html) for https://bigquery.googleapis.com/bigquery/v2/projects/youtube-crawler-161609/jobs?prettyPrint=false'

{
  "error": {
    "code": 401,
    "message": "Request is missing required authentication credential. Expected OAuth 2 access token, login cookie or other valid authentication credential. See https://developers.google.com/identity/sign-in/web/devconsole-project.",
    "errors": [
      {
        "message": "Login Required.",
        "domain": "global",
        "reason": "required",
        "location": "Authorization",
        "locationType": "header"
      }
    ],
    "status": "UNAUTHENTICATED",
    "details": [
      {
        "@type": "type.googleapis.com/google.rpc.ErrorInfo",
        "reason": "CREDENTIALS_MISSING",
        "domain": "googleapis.com",
        "metadata": {
          "service": "bigquery.googleapis.com",
          "method": "google.cloud.bigquery.v2.JobService.ListJobs"
        }
      }
    ]
  }
}

The contents are displayed as "UNAUTHENTICATED" and "CREDENTIALS_MISSING". Strangely enough, sometimes the results are returned. Also, I have never had a timeout error with similar connection information in my local environment.

The environment where the timeout occurs is using Fargate on Amazon ECS. We are wondering if that may have something to do with it as well.

The constructor of BigQueryClient.php specifies the following. We believe that the content is correct because sometimes the results are returned.

[
    'projectId' => config('bigquery.project_id'),
    'keyFile'   => [
        'type'                        => 'service_account',
        'project_id'                  => config('bigquery.project_id'),
        'private_key_id'              => config('bigquery.private_key_id'),
        'private_key'                 => str_replace('\n', "\n", config('bigquery.private_key')),
        'client_email'                => config('bigquery.client_email'),
        'client_id'                   => config('bigquery.client_id'),
        'auth_uri'                    => 'https://accounts.google.com/o/oauth2/auth',
        'token_uri'                   => 'https://oauth2.googleapis.com/token',
        'auth_provider_x509_cert_url' => 'https://www.googleapis.com/oauth2/v1/certs',
        'client_x509_cert_url'        => 'https://www.googleapis.com/robot/v1/metadata/x509'
            . '/youtube-crawler-161609%40appspot.gserviceaccount.com'
    ]
]

We have investigated here. It seems that there is a timeout at the $fn(Psr7\Utils::modifyRequest($request, $modify), $options); point in vendor/guzzlehttp/guzzle/src/PrepareBodyMiddleware.php

Please let me know if you can think of any possible causes. Best regards.

[mukaiyama-t]

It seems to time out at $bigQueryClient->getServiceAccount();. local environment can get it with no problem.

[mukaiyama-t]

As of 6:00 UTC, I could get it on ECS. By the way, the time in the application is 15:00 Japan time.

[mukaiyama-t]

At about 6:30 UTC, I was unable to retrieve it on ECS. Is it related to the time of day, etc.?

[saranshdhingra]

Hi @mukaiyama-t No, the response/timeout shouldn't be related to the time of the day.

Also, since you are able to get results in the local environment, it doesn't seem like a library/API issue.

Everything points to it being an environment issue. Since you're using the config helpers(likely Laravel) I would invite you to look into cases when the helper doesn't return valid values.

You could even log the values where you are initialising the keyFile to confirm if that's the case or not.

[mukaiyama-t]

Hi. I have just resolved the issue and would like to report it. The IP address of bigquery.googleapis.com changes every time I access it. It seems that it was batting against the route table set up in ECS and was causing a timeout.

I have resolved the issue and will close it. Thank you very much.