{"id":9047,"date":"2026-09-16T11:09:16","date_gmt":"2026-09-16T11:09:16","guid":{"rendered":"https:\/\/prwatech.in\/blog\/?p=9047"},"modified":"2026-09-17T05:47:41","modified_gmt":"2026-09-17T05:47:41","slug":"accessing-cloud-sql-from-bigquery","status":"publish","type":"post","link":"https:\/\/prwatech.in\/blog\/google-cloud-platform\/bigquery\/accessing-cloud-sql-from-bigquery\/","title":{"rendered":"Accessing Cloud SQL from BigQuery"},"content":{"rendered":"\r\n

Querying Cloud SQL Data in BigQuery<\/h1>\r\n

BigQuery can query data sitting in a Cloud SQL instance directly, without first copying it in, using a feature called a federated query<\/b><\/strong>. You set up a connection once, then reference it inside a query using the EXTERNAL_QUERY function, and BigQuery sends your query to Cloud SQL, gets the result back, and converts it into BigQuery’s own data types.<\/p>\r\n

Step by Step Process of Querying Cloud SQL Data in BigQuery<\/h2>\r\n

Step One: Enable the BigQuery Connection API<\/h3>\r\n

In the console, open the menu, then go to APIs and Services, then Library.<\/p>\r\n\r\n\r\n\r\n

Open Menu > API & Services > Library<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

Search for the BigQuery Connection API and open it.<\/p>\r\n\r\n\r\n\r\n

Open It.<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

Click on Enable to enable API<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

Step Two: Open Your Cloud SQL Instance<\/h3>\r\n

Open the menu, then go to SQL.<\/p>\r\n

menu > SQL<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

Open your Cloud SQL instance.<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

Step Three: Choose Public or Private IP<\/h3>\r\n

Open the Connections tab.<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

For a quick test, confirm Public IP is enabled here. For anything beyond a scratch project, consider configuring private IP connectivity instead, and selecting the enable private path option on the BigQuery side when you create the connection in the next step. Private connectivity keeps traffic between BigQuery and Cloud SQL off the public internet entirely, which current Google Cloud<\/a> guidance treats as the safer default.<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n

Step Four: Start Creating the Connection in BigQuery<\/h3>\r\n

Open the menu, then go to BigQuery.<\/p>\r\n\r\n\r\n\r\n

Open Menu > BigQuery<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

Click on Add Data > External Data Source<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

Choose your connection type, matching the database engine your Cloud SQL instance runs, MySQL or PostgreSQL. Give the connection an ID and choose a data location. Pick the same location as your Cloud SQL instance, since a federated query can only run against a resource in the same region as the connection.<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n

Step Five: Copy the Cloud SQL Connection Name<\/h3>\r\n

In a new tab, open the menu, then SQL, then your instance’s Overview page, and copy its connection name.<\/p>\r\n\r\n\r\n\r\n

In new tab, Open Menu > SQL Instance > Overview Copy the connection name<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

Click on users. If your user account is there, you can skip this step. Else add the user accoount.<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

Step Six: Confirm or Add a Database User<\/h3>\r\n

Click Users. If the account you want to connect with is already listed, you can skip ahead. Otherwise, add it.<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

Step Seven: Finish Creating the Connection<\/h3>\r\n

Back in the BigQuery tab, paste the Cloud SQL instance connection name, along with the database name, username, and password for the account you just confirmed or created. Click Create Connection.<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

Step Eight: Share the Connection, if Needed<\/h3>\r\n

If other people need to view, edit, or otherwise use this connection, click Share Connection.<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

The connection will be created.<\/p>\r\n

Enter their email address, choose a role, click Add, then Done.<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

Step Nine: Run a Federated Query<\/h3>\r\n

Click Query Connection. This adds a starter query into the query editor:<\/p>\r\n\r\n\r\n\r\n

SELECT * FROM EXTERNAL_QUERY(\u201c<project-ID>.<Location>.<connection-ID>\u201d,\u201d<SQL QUERY>\u201d);<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

Change only the SQL query text inside the quotes, written in your Cloud SQL database’s own SQL dialect. Leave the EXTERNAL_QUERY function call and the project, location, and connection ID section exactly as generated. Click Run, and it executes the same as any other BigQuery query.<\/p>\r\n\r\n\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n
\"\"<\/figure>\r\n\r\n\r\n\r\n

 <\/p>\r\n

An Important Limitation: Federated Queries Are Read Only<\/h2>\r\n

A federated query can only read data out of Cloud SQL. You cannot use EXTERNAL_QUERY to insert, update, or delete rows back in the source database. If your workflow needs to write data somewhere, that write has to happen through a normal connection to Cloud SQL directly, not through this BigQuery connection.<\/p>\r\n

Common Mistakes to Avoid<\/h2>\r\n