Search code examples
mysqlgoogle-cloud-platformgoogle-bigquerycloudgoogle-cloud-sql

Bigquery federated query error : Lost connection to MySQL server during query

I have added Cloud SQL (MySQL) database as an external connection to the Bigquery on Google Cloud and selected us-central1 as a region as it is the region defined for the Cloud SQL database. However, when I use federated query it gives me the following error:

Invalid table-valued function EXTERNAL_QUERY Failed to connect to MySQL database. Error: MysqlErrorCode(2013): Lost connection to MySQL server during query at [1:15]

The query that I use is the following:

SELECT * FROM EXTERNAL_QUERY("xxx.us-central1.stg-data", "SELECT * FROM device;");

I have done the same procedure for another project and it worked fine. Can the issue be related to the region (us-central1) that might not be supported by Bigquery federated queries?

Solution

  • When you create a connection with BigQuery, a service account is created automatically. You have to grant this service account to access to the correct resource. Here Cloud SQL.

    It's the same with other resources, such as spanner, BigTable or Cloud Storage (for object tables for instance)