Google Big Query & Cloud SQL

Question

I have a Cloud SQL managed DB. Also, i have a read replica attached to the same.

I would like to my big query connected to Cloud SQL. Is it possible to connect Google Big Query with Cloud SQL Read replica?

Answer 1

Yes, it is possible.

To make queries in BigQuery over data residing in Cloud SQL you can use Federated Queries which are queries for data not residing in BigQuery, but registered as an external Data Source.

To perform these queries you can use the following syntax:

SELECT * FROM EXTERNAL_QUERY(<CONNECTION_ID>, <EXTERNAL_DATABASE_QUERY>);

The CONNECTION_ID is the one given in Big Query when creating the external datasource connection with the following steps:

• Go to the Big Query Console
• Click on +Add Data and select external data source
• A menu will appear on the right side of your window, fill the form there with the data of your cloud SQL read replica instance.
• On connection ID select a string that you can remember as it will be the one used for the federated queries
• Create Connection

These steps will allow you to create the connection between Big Query and Cloud SQL. Once the connection is created you can perform federated queries to consult data from cloud SQL instances.

The EXTERNAL_DATABASE_QUERY is the query you would have used in CloudSQL to get this data.