Connect to Cloud SQL

Console

1. Go to the BigQuery page.

   Go to BigQuery

2. In the Explorer pane, click add Add data.

3. In the Add data dialog, click Connections to external data sources:

4. 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-managed encryption 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 visibility_off Show password.

5. Click Create connection.

6. Click Go to connection.

7. 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 the instanceID, database, and type parameters.
• CREDENTIALS: the parameters username and password.
• 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

import com.google.cloud.bigquery.connection.v1.CloudSqlCredential;
import com.google.cloud.bigquery.connection.v1.CloudSqlProperties;
import com.google.cloud.bigquery.connection.v1.Connection;
import com.google.cloud.bigquery.connection.v1.CreateConnectionRequest;
import com.google.cloud.bigquery.connection.v1.LocationName;
import com.google.cloud.bigqueryconnection.v1.ConnectionServiceClient;
import java.io.IOException;

// Sample to create a connection with cloud MySql database
public class CreateConnection {

  public static void main(String[] args) throws IOException {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String location = "MY_LOCATION";
    String connectionId = "MY_CONNECTION_ID";
    String database = "MY_DATABASE";
    String instance = "MY_INSTANCE";
    String instanceLocation = "MY_INSTANCE_LOCATION";
    String username = "MY_USERNAME";
    String password = "MY_PASSWORD";
    String instanceId = String.format("%s:%s:%s", projectId, instanceLocation, instance);
    CloudSqlCredential cloudSqlCredential =
        CloudSqlCredential.newBuilder().setUsername(username).setPassword(password).build();
    CloudSqlProperties cloudSqlProperties =
        CloudSqlProperties.newBuilder()
            .setType(CloudSqlProperties.DatabaseType.MYSQL)
            .setDatabase(database)
            .setInstanceId(instanceId)
            .setCredential(cloudSqlCredential)
            .build();
    Connection connection = Connection.newBuilder().setCloudSql(cloudSqlProperties).build();
    createConnection(projectId, location, connectionId, connection);
  }

  static void createConnection(
      String projectId, String location, String connectionId, Connection connection)
      throws IOException {
    try (ConnectionServiceClient client = ConnectionServiceClient.create()) {
      LocationName parent = LocationName.of(projectId, location);
      CreateConnectionRequest request =
          CreateConnectionRequest.newBuilder()
              .setParent(parent.toString())
              .setConnection(connection)
              .setConnectionId(connectionId)
              .build();
      Connection response = client.createConnection(request);
      System.out.println("Connection created successfully :" + response.getName());
    }
  }
}

Console

1. Go to the IAM & Admin page.

   Go to IAM & Admin

2. Click person_add Grant Access.

   The Add principals dialog opens.

3. In the New principals field, enter the service account name BigQuery Connection Service Agent or the service account ID taken from the connection information.

4. In the Select a role field, select Cloud SQL, and then select Cloud SQL Client.

5. Click Save.

gcloud

Use the gcloud projects add-iam-policy-binding command:

gcloud projects add-iam-policy-binding PROJECT_ID --member=serviceAccount:SERVICE_ACCOUNT_ID --role=roles/cloudsql.client

Provide the following values:

• PROJECT_ID: Your Google Cloud project ID.
• SERVICE_ACCOUNT_ID: The service account ID taken from the connection information.

Console

1. Go to the BigQuery page.

   Go to BigQuery

   Connections are listed in your project, in a group called External connections.

2. In the Explorer pane, click your project name > External connections > connection.

3. In the Details pane, click Share to share a connection. Then do the following:

   1. In the Connection permissions dialog, share the connection with other principals by adding or editing principals.

   2. 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

import com.google.api.resourcenames.ResourceName;
import com.google.cloud.bigquery.connection.v1.ConnectionName;
import com.google.cloud.bigqueryconnection.v1.ConnectionServiceClient;
import com.google.iam.v1.Binding;
import com.google.iam.v1.Policy;
import com.google.iam.v1.SetIamPolicyRequest;
import java.io.IOException;

// Sample to share connections
public class ShareConnection {

  public static void main(String[] args) throws IOException {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String location = "MY_LOCATION";
    String connectionId = "MY_CONNECTION_ID";
    shareConnection(projectId, location, connectionId);
  }

  static void shareConnection(String projectId, String location, String connectionId)
      throws IOException {
    try (ConnectionServiceClient client = ConnectionServiceClient.create()) {
      ResourceName resource = ConnectionName.of(projectId, location, connectionId);
      Binding binding =
          Binding.newBuilder()
              .addMembers("group:example-analyst-group@google.com")
              .setRole("roles/bigquery.connectionUser")
              .build();
      Policy policy = Policy.newBuilder().addBindings(binding).build();
      SetIamPolicyRequest request =
          SetIamPolicyRequest.newBuilder()
              .setResource(resource.toString())
              .setPolicy(policy)
              .build();
      client.setIamPolicy(request);
      System.out.println("Connection shared successfully");
    }
  }
}