Accessing Cloud SQL from BigQuery

  • date 30th May, 2021 |
  • by Prwatech |
  • 0 Comments

Prerequisites

GCP account

Open Console

Open Menu > API & Services > Library

Search BigQuery connection API.

Open It.

Click on Enable to enable API

Open menu > SQL

Open the SQL instance.

Open Connections

Check Public IP is selected or not. If not, tick it.

Open Menu > BigQuery

Click on Add Data > External Data Source

Choose Connection type.

Give Connection ID and connection location.

Connection location is better if you choose the same location as the Cloud SQL Instance

In new tab, Open Menu > SQL Instance > Overview Copy the connection name

Click on users. If your user account is there, you can skip this step. Else add the user accoount.

Give Username and password. Click Add.

The Account will be added. Go back to BigQuery tab.

Paste the Cloud SQL Instance ID.

Give the Database name which you used in My SQL.

Give the username and password.

Click Create Connection.

The connection will be created.

If you want to add someone to use this connection to View, Edit, etc. Click on Share Connection.

Give the member mail ID and choose the role. Click Add then click Done

Click on Query Connection.

It will add the query in query Editor.

SELECT * FROM EXTERNAL_QUERY(“<project-ID>.<Location>.<connection-ID>”,”<SQL QUERY>”);

Modify this Query for your use.

NB: In the query which you get when you press Query Connection, Change only the SQL query. Don’t change the  EXTERNAL_QUERY(“<project-ID>.<Location>.<connection-ID>” section.

Write your query and click run. It will Execute as usual.

Quick Support

image image