Federated Queries on Cloud SQL from BigQuery fail with "Invalid table-valued function"

Problem

Federated queries fail intermittently when run from BigQuery on Cloud SQL using EXTERNAL_QUERY construct with the following error:

Error Message : Invalid table-valued function EXTERNAL_QUERY Connect to PostgreSQL server failed: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request at [1:15].

Environment

  • BigQuery

Solution

  1. Check if the service account, for example, service-<project_id>@gcp-sa-bigqueryconnection.iam.gserviceaccount.com has the role cloudsql.client in the project.
  2. Add the service account manually if it is not found for the role cloudsql.client.

Cause

BigQuery uses an internal service account to access Cloud SQL instances. This service account is created for every project when they first create a BigQuery connection for their Cloud SQL instance. This service account has cloudsql.client role in the user project. If for any reason this service account is removed from the project, a permission denied error will be encountered.