Cloud SQL federated queries
As a data analyst, you can query data in Cloud SQL from BigQuery using federated queries.
BigQuery Cloud SQL federation enables BigQuery to query data residing in Cloud SQL in real time, without copying or moving data. Query federation supports both MySQL (2nd generation) and PostgreSQL instances in Cloud SQL.
Alternatively, to replicate data into BigQuery, you can also use Cloud Data Fusion or Datastream. For more about using Cloud Data Fusion, see Replicating data from MySQL to BigQuery.
Before you begin
- Ensure that your BigQuery administrator has created a Cloud SQL connection and shared it with you.
To get the permissions that you need to query a Cloud SQL instance, ask your administrator to grant you the BigQuery Connection User (
roles/bigquery.connectionUser) IAM role on your project. For more information about granting roles, see Manage access.
You might also be able to get the required permissions through custom roles or other predefined roles.
To send a federated query to Cloud SQL from a
GoogleSQL query, use the
Suppose that you store a customer table in BigQuery, while
storing a sales table in Cloud SQL, and want to join the two tables in
a single query. The following example makes a federated query to a
Cloud SQL table named
orders and joins the results with a
BigQuery table named
SELECT c.customer_id, c.name, rq.first_order_date FROM mydataset.customers AS c LEFT OUTER JOIN EXTERNAL_QUERY( 'us.connection_id', '''SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id''') AS rq ON rq.customer_id = c.customer_id GROUP BY c.customer_id, c.name, rq.first_order_date;
The example query includes 3 parts:
- Run the external query
SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_idin the operational PostgreSQL database to get the first order date for each customer through the
- Join the external query result table with the customers table in
- Select customer information and first order date.
View a Cloud SQL table schema
You can use the
EXTERNAL_QUERY() function to query information_schema tables
to access database metadata, such as list all tables in the database or show
table schema. The following example information_schema queries work in both
MySQL and PostgreSQL. You can learn more from
MySQL information_schema tables
PostgreSQL information_schema tables.
-- List all tables in a database. SELECT * FROM EXTERNAL_QUERY("connection_id", "select * from information_schema.tables;");
-- List all columns in a table. SELECT * FROM EXTERNAL_QUERY("connection_id", "select * from information_schema.columns where table_name='x';");
The following table shows the Cloud SQL connection properties:
||string||Name of the connection resource in the format: project_id.location_id.connection_id.|
||string||Location of the connection, which is the same as the Cloud SQL instance location with the following exceptions: Cloud SQL
||string||A user-friendly display name for the connection.|
||string||Description of the connection.|
||string||Can be "POSTGRES" or "MYSQL".|
||string||Name of the Cloud SQL instance, usually in the format of:
You can find the instance ID in the Cloud SQL instance detail page.
||string||The Cloud SQL database that you want to connect to.|
||string||The service account configured to access the Cloud SQL database.|
The following table shows the properties for the Cloud SQL instance credential:
This section helps you troubleshoot issues you might encounter when sending a federated query to Cloud SQL.
Issue: Failed to connect to database server. If you are querying a MySQL database, you might encounter 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.
Alternatively, if you are querying a PostgreSQL database, you might encounter the following error:
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.
- Resolution: Ensure that valid credentials were used and all prerequisites
were followed to create the connection for Cloud SQL.
Check if the
service account that is automatically created
when a connection to Cloud SQL is
created has the Cloud SQL Client (
roles/cloudsql.client) role. The service account is of the following format:
service-PROJECT_NUMBER@gcp-sa-bigqueryconnection.iam.gserviceaccount.com. For detailed instructions, see Grant access to the service account.
- Learn about federated queries.
- Learn about MySQL to BigQuery data type mapping.
- Learn about PostgreSQL to BigQuery data type mapping.
- Learn about unsupported data types.