Querying Cloud SQL data in BigQuery
Prerequisites
GCP account
Open Console
Open Menu > API & Services > Library
Search BigQuery connection API.
Open It.
Click on Enable to enable API
menu > SQL
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.
Querying Cloud SQL data in BigQuery
