CODEWITHSUNDEEP
google-bigquerygoogle-cloud-sql
John W
John W

Reputation: 199

Connecting BigQuery to CloudSQL with private IP

I'm trying to connect BigQuery to a CloudSQL Postgres DB using an "External Data Source". After setting up the connection, I see this error in BigQuery:

Invalid table-valued function EXTERNAL_QUERY
Connect to PostgreSQL server failed: server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
 at [1:15]

I tried setting up a sandbox project, and ran into the same issue there. I then tried enabling a "Public IP" connection in the CloudSQL configuration, which appeared to fix the issue in the sandbox.

I would like to be able to do this without turning on the public IP, since I want to keep my data in my private network only. Is it possible to use a private IP CloudSQL instance with BigQuery the way that I am describing?

Upvotes: 2

Views: 3589

Answers (3)

kishn
kishn

Reputation: 129

You would have enable/check the "Enable private path" for Google Cloud services authorization in the connections section of the Cloud SQL settings in the GCP console.

Cloud SQL Connection Settings

If you're using Terraform as IaC then you would use something similar to the below block.

ip_configuration {
  ipv4_enabled       = false
  require_ssl        = true
  private_network    = var.vpc_network
  allocated_ip_range = var.sql_ip_range_name
  enable_private_path_for_google_cloud_services = true  <-- flag set to true
}

Upvotes: 2

Roger
Roger

Reputation: 166

As per documentation, connections between Cloud SQL and Big Query using private IPs are not supported, and public IPs should be used to do so.

As an alternative, you can add public IP connectivity with NO authorized address.

While this is not as secure as private IP-only, your instance will remain inaccessible from the public internet, and can be queried from BigQuery.

You can see further information regarding public IPs for Cloud SQL here.

Upvotes: 3

Nitiraj Shitole
Nitiraj Shitole

Reputation: 159

Check if service account [1] has role "cloudsql.client" in the project. Please add the service account manually if it is not found for the role "cloudsql.client"

[1] service-<project_id>@gcp-sa-bigqueryconnection.iam.gserviceaccount.com

Cause - BigQuery uses internal service account to access Cloud SQL instances. This service account is created for every customer project when they first creates BQ connection for their Cloud SQL instance. This service account has cloudsql.client role in the user project. If for any reason this service account is removed from the project , a permission denied error will be encountered.

Upvotes: 2

Related Questions