how to secure the connection between bigquery and cloudsql

[papael]

Hi All,

I'd like to ask, I have a condition that I want to use BQ feature to make some query into my cloudsql instance, but as I know from couple of literature, BQ is a public endpoint which is outside.

I have to open my cloudsql to the internet (port postresql and IP address). One way to secure both connection is restrict the access from BQ toward cloudsql in VPC network rule, but no idea, I dont know bigquery IP address/network.

Is there anyone here to help my case? or if any other best way suggest to me? Thanks in advance!

[RC1]

@papael 

Check out this  https://cloud.google.com/bigquery/docs/cloud-sql-federated-queries#public_ip

---------------------------------------------------------------------------------------------------------------------------

BigQuery Cloud SQL federation only supports Cloud SQL instances with public IP connectivity. Configure public IP connectivity for your Cloud SQL instance.

To secure your Cloud SQL instances, you can add public IP connectivity without an authorized address. This method makes your instance inaccessible from the public internet but accessible to query from BigQuery.

[Andre_Fiesco]

Have you tried using Private services access? The private connection enables VM instances in your VPC network and the services that you access to communicate exclusively by using internal IP addresses.  This way, the connection will be internal and will not have outside connections. VM instances don't need Internet access or external IP addresses to reach services that are available through private services access.      

Here is more information that can be useful for understanding the Private services access

[MichiGPA]

 Private services access is useful while connecting from VPC (Vms) , but the question is how to connect securely (private access) from managed services like BigQuery

[totoleon]

I believe it's recently supported now. According to https://cloud.google.com/bigquery/docs/connect-to-sql#create-sql-connection

To let BigQuery access Cloud SQL data over a private connection, configure private IP connectivity for a new or an existing Cloud SQL instance, and then select the Private path for Google Cloud services checkbox. This service uses an internal direct path instead of the private IP address inside of the Virtual Private Cloud.

So if you enable this flag on the Cloud SQL instance, BigQuery should be able to connect to it without a public IP.

[DataEngineer_1]

Hi, 

Thanks for sharing the Private IP option. Can I check if we are able to connect to a CloudSQL inside one GCP Project from a BigQuery in a different GCP Project?

Or this would need some sort of VPC peering to be enabled?