Connect to Cloud SQL
As a BigQuery administrator, you can create a connection to access Cloud SQL data. This connection enables data analysts to query data in Cloud SQL. To connect to Cloud SQL, you must follow these steps:
Before you begin
- Select the project that contains the Cloud SQL database.
- Enable the BigQuery Connection API.
- Ensure that the Cloud SQL instance has a
public IP connection or a private connection:
To secure your Cloud SQL instances, you can add public IP connectivity without an authorized address. This makes the instance inaccessible from the public internet but accessible to queries from BigQuery.
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.
-
To get the permissions that you need to create a Cloud SQL connection, ask your administrator to grant you the BigQuery Connection Admin (
roles/bigquery.connectionAdmin
) IAM role on the project. For more information about granting roles, see Manage access to projects, folders, and organizations.You might also be able to get the required permissions through custom roles or other predefined roles.
Create Cloud SQL connections
As a best practice, use connections to handle database credentials when you are connecting to Cloud SQL. Connections are encrypted and stored securely in the BigQuery connection service. If the user credentials are valid for other data in the source, you can re-use the connection. For example, you might be able to use one connection to query multiple databases residing in the same Cloud SQL instance.
Select one of the following options to create a Cloud SQL connection:
Console
Go to the BigQuery page.
In the Explorer pane, click
Add data.In the Add data dialog, click Connections to external data sources:
In the External data source dialog, enter the following information:
- For Connection type, select the type of source, for example MySQL or Postgres.
- For Connection ID, enter an identifier for the connection
resource. Letter, numbers, and underscores are allowed. For example,
bq_sql_connection
. - For Data location, select a BigQuery location (or region) that is compatible with your external data source region.
- Optional: For Friendly name, enter a user-friendly name for
the connection, such as
My connection resource
. The friendly name can be any value that helps you identify the connection resource if you need to modify it later. - Optional: For Description, enter a description for this connection resource.
- Optional: Encryption If you want to use a customer-managed encryption key (CMEK) to encrypt your credentials, select Customer-managed encryption key (CMEK) and then select a customer-managed key. Otherwise, your credentials are protected by the default Google-owned and Google-managed key.
- If you chose Cloud SQL MySQL or Postgres for the connection type,
for Cloud SQL connection name, enter the full
name of the Cloud SQL instance,
usually in the format
project-id:location-id:instance-id
. You can find the instance ID on the detail page of the Cloud SQL instance you want to query. - For Database name, enter the name of the database.
- For Database username, enter the username for the database.
For Database password, enter the password for the database.
- Optional: To see the password, click Show password.
Click Create connection.
Click Go to connection.
In the Connection info pane, copy the service account ID for use in a following step.
bq
Enter the bq mk
command and supply the connection flag:
--connection
. The following flags are also required:
--connection_type
--properties
--connection_credential
--project_id
--location
The following flags are optional:
--display_name
The friendly name for the connection.--description
A description of the connection.
The connection_id
is an optional parameter that can be added as the last argument of the command which
is used for storage internally. If a connection ID is not provided a unique ID is automatically generated.
The connection_id
can contain letters, numbers, and underscores.
bq mk --connection --display_name='friendly name' --connection_type=TYPE \
--properties=PROPERTIES --connection_credential=CREDENTIALS \
--project_id=PROJECT_ID --location=LOCATION \
CONNECTION_ID
Replace the following:
TYPE
: the type of the external data source.PROPERTIES
: the parameters for the created connection in JSON format. For example:--properties='{"param":"param_value"}'
. For creating a connection resource, you must supply theinstanceID
,database
, andtype
parameters.CREDENTIALS
: the parametersusername
andpassword
.PROJECT_ID
: your project ID.LOCATION
: the region your Cloud SQL instance is located in.CONNECTION_ID
: the connection identifier.
For example, the following command creates a new connection resource
named my_new_connection (friendly name: "My new connection") in a project
with the ID federation-test
.
bq mk --connection --display_name='friendly name' --connection_type='CLOUD_SQL' \
--properties='{"instanceId":"federation-test:us-central1:mytestsql","database":"mydatabase","type":"MYSQL"}' \
--connection_credential='{"username":"myusername", "password":"mypassword"}' \
--project_id=federation-test --location=us my_connection_id
API
Within the BigQuery Connection API, you can invoke CreateConnection
within
the ConnectionService
to instantiate a connection. See the client library page for more details.
Java
Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Java API reference documentation.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
Grant access to the service agent
A service agent is automatically created when you create the first connection to Cloud SQL within the project. The service agent's name is BigQuery Connection Service Agent. To get the service agent ID, view your connection details. The service agent ID is of the following format:
service-PROJECT_NUMBER@gcp-sa-bigqueryconnection.iam.gserviceaccount.com
.
To connect to Cloud SQL, you must give the new connection read-only access to Cloud SQL so that BigQuery can access files on behalf of users. The service agent must have the following permissions:
cloudsql.instances.connect
cloudsql.instances.get
You can grant the service agent associated with the connection the
Cloud SQL Client IAM role
(roles/cloudsql.client
), which has these permissions assigned.
You can skip the following steps if the service agent already has the required
permissions.
Console
Go to the IAM & Admin page.
Click
Grant Access.The Add principals dialog opens.
In the New principals field, enter the service agent name BigQuery Connection Service Agent or the service agent ID taken from the connection information.
In the Select a role field, select Cloud SQL, and then select Cloud SQL Client.
Click Save.
gcloud
Use the
gcloud projects add-iam-policy-binding
command:
gcloud projects add-iam-policy-binding PROJECT_ID --member=serviceAccount:SERVICE_AGENT_ID --role=roles/cloudsql.client
Provide the following values:
PROJECT_ID
: Your Google Cloud project ID.SERVICE_AGENT_ID
: The service agent ID taken from the connection information.
Share connections with users
You can grant the following roles to let users query data and manage connections:
roles/bigquery.connectionUser
: enables users to use connections to connect with external data sources and run queries on them.roles/bigquery.connectionAdmin
: enables users to manage connections.
For more information about IAM roles and permissions in BigQuery, see Predefined roles and permissions.
Select one of the following options:
Console
Go to the BigQuery page.
Connections are listed in your project, in a group called External connections.
In the Explorer pane, click your project name > External connections > connection.
In the Details pane, click Share to share a connection. Then do the following:
In the Connection permissions dialog, share the connection with other principals by adding or editing principals.
Click Save.
bq
You cannot share a connection with the bq command-line tool. To share a connection, use the Google Cloud console or the BigQuery Connections API method to share a connection.
API
Use the
projects.locations.connections.setIAM
method
in the BigQuery Connections REST API reference section, and
supply an instance of the policy
resource.
Java
Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Java API reference documentation.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
What's next
- Learn about different connection types.
- Learn about managing connections.
- Learn about federated queries.
- Learn how to query Cloud SQL data.