Manage connections

This document describes how to view, list, share, edit, delete, and troubleshoot a BigQuery connection.

As a BigQuery administrator, you can create and manage connections that are used to connect to services and external data sources. BigQuery analysts use these connections to submit queries against external data sources without moving or copying data into BigQuery. You can create the following types of connections:

Before you begin

Required roles

To get the permissions that you need to manage connections, ask your administrator to grant you the following IAM roles:

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.

These predefined roles contain the permissions required to perform the tasks in this document. To see the exact permissions that are required, expand the Required permissions section:

Required permissions

  • View connection details: bigquery.connections.get
  • List all connections: bigquery.connections.list
  • Edit and delete a connection: bigquery.connections.update
  • Share a connection: bigquery.connections.setIamPolicy

You might also be able to get these permissions with custom roles or other predefined roles.

List all connections

Select one of the following options:

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 to see a list of all connections.

bq

Enter the bq ls command and specify the --connection flag. Optionally, specify the --project_id and --location flags to identify the project and location of the connections to be listed.

bq ls --connection --project_id=PROJECT_ID --location=REGION

Replace the following:

API

Use the projects.locations.connections.list method in the REST API reference section.

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. 

import com.google.cloud.bigquery.connection.v1.ListConnectionsRequest;
import com.google.cloud.bigquery.connection.v1.LocationName;
import com.google.cloud.bigqueryconnection.v1.ConnectionServiceClient;
import java.io.IOException;

// Sample to get list of connections
public class ListConnections {

  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";
    listConnections(projectId, location);
  }

  static void listConnections(String projectId, String location) throws IOException {
    try (ConnectionServiceClient client = ConnectionServiceClient.create()) {
      LocationName parent = LocationName.of(projectId, location);
      int pageSize = 10;
      ListConnectionsRequest request =
          ListConnectionsRequest.newBuilder()
              .setParent(parent.toString())
              .setPageSize(pageSize)
              .build();
      client
          .listConnections(request)
          .iterateAll()
          .forEach(con -> System.out.println("Connection Id :" + con.getName()));
    }
  }
}

View connection details

After you create a connection, you can get information about the connection's configuration. The configuration includes the values you supplied when you created the transfer.

Select one of the following options:

Console

  1. Go to the BigQuery page.

    Go to BigQuery

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

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

bq

Enter the bq show command and specify the --connection flag. Optionally, qualify the connection ID with the project ID and region of the connection.

bq show --connection PROJECT_ID.REGION.CONNECTION_ID

Replace the following:

  • PROJECT_ID: your Google Cloud project ID
  • REGION: the connection region
  • CONNECTION_I: the connection ID

API

Use the projects.locations.connections.get method in the REST API reference section.

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. 

import com.google.cloud.bigquery.connection.v1.Connection;
import com.google.cloud.bigquery.connection.v1.ConnectionName;
import com.google.cloud.bigquery.connection.v1.GetConnectionRequest;
import com.google.cloud.bigqueryconnection.v1.ConnectionServiceClient;
import java.io.IOException;

// Sample to get connection
public class GetConnection {

  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";
    getConnection(projectId, location, connectionId);
  }

  static void getConnection(String projectId, String location, String connectionId)
      throws IOException {
    try (ConnectionServiceClient client = ConnectionServiceClient.create()) {
      ConnectionName name = ConnectionName.of(projectId, location, connectionId);
      GetConnectionRequest request =
          GetConnectionRequest.newBuilder().setName(name.toString()).build();
      Connection response = client.getConnection(request);
      System.out.println("Connection info retrieved successfully :" + response.getName());
    }
  }
}

Share a connection 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

  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

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. 

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:[email protected]")
              .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");
    }
  }
}

Edit a connection

A connection uses the credentials of the user who created it. If you need to change the user attached to a connection, you can update the user's credentials. This is useful if the user who created the connection is no longer with your organization.

You cannot edit the following elements of a connection:

  • Connection type
  • Connection ID
  • Location

Select one of the following options:

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, to edit details, click Edit details. Then do the following:

    1. In the Edit connection dialog, edit the connection details including the user credentials.

    2. Click Update connection.

bq

Enter the bq update command and supply the connection flag: --connection. The fully qualified connection_id is required.

  bq update --connection --connection_type='CLOUD_SQL'
      --properties='{"instanceId" : "INSTANCE",
      "database" : "DATABASE", "type" : "MYSQL" }'
      --connection_credential='{"username":"USERNAME", "password":"PASSWORD"}'
      PROJECT.REGION.CONNECTION_ID

Replace the following:

  • INSTANCE: the Cloud SQL instance
  • DATABASE: the database name
  • USERNAME: the username of your Cloud SQL database
  • PASSWORD: the password to your Cloud SQL database
  • PROJECT: the Google Cloud project ID
  • REGION: the connection region
  • CONNECTION_ID: the connection ID

For example, the following command updates the connection in a project with the ID federation-test and connection ID test-mysql.

bq update --connection --connection_type='CLOUD_SQL'
    --properties='{"instanceId" : "federation-test:us-central1:new-mysql",
    "database" : "imdb2", "type" : "MYSQL" }'
    --connection_credential='{"username":"my_username",
    "password":"my_password"}' federation-test.us.test-mysql

API

See the projects.locations.connections.patch method in the REST API reference section, and supply an instance of the connection.

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. 

import com.google.cloud.bigquery.connection.v1.Connection;
import com.google.cloud.bigquery.connection.v1.ConnectionName;
import com.google.cloud.bigquery.connection.v1.UpdateConnectionRequest;
import com.google.cloud.bigqueryconnection.v1.ConnectionServiceClient;
import com.google.protobuf.FieldMask;
import com.google.protobuf.util.FieldMaskUtil;
import java.io.IOException;

// Sample to update connection
public class UpdateConnection {

  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 description = "MY_DESCRIPTION";
    Connection connection = Connection.newBuilder().setDescription(description).build();
    updateConnection(projectId, location, connectionId, connection);
  }

  static void updateConnection(
      String projectId, String location, String connectionId, Connection connection)
      throws IOException {
    try (ConnectionServiceClient client = ConnectionServiceClient.create()) {
      ConnectionName name = ConnectionName.of(projectId, location, connectionId);
      FieldMask updateMask = FieldMaskUtil.fromString("description");
      UpdateConnectionRequest request =
          UpdateConnectionRequest.newBuilder()
              .setName(name.toString())
              .setConnection(connection)
              .setUpdateMask(updateMask)
              .build();
      Connection response = client.updateConnection(request);
      System.out.println("Connection updated successfully :" + response.getDescription());
    }
  }
}

Delete a connection

Select one of the following options:

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 Delete to delete the connection.

  4. In the Delete connection? dialog, enter delete to confirm deletion.

  5. Click Delete.

bq

Enter the bq rm command and supply the connection flag: --connection. The fully qualified connection_id is required.

bq rm --connection PROJECT_ID.REGION.CONNECTION_ID

Replace the following:

  • PROJECT_ID: your Google Cloud project ID
  • REGION: the connection region
  • CONNECTION_ID: the connection ID

API

See the projects.locations.connections.delete method in the REST API reference section.

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. 

import com.google.cloud.bigquery.connection.v1.ConnectionName;
import com.google.cloud.bigquery.connection.v1.DeleteConnectionRequest;
import com.google.cloud.bigqueryconnection.v1.ConnectionServiceClient;
import java.io.IOException;

// Sample to delete a connection
public class DeleteConnection {

  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 connectionName = "MY_CONNECTION_NAME";
    deleteConnection(projectId, location, connectionName);
  }

  static void deleteConnection(String projectId, String location, String connectionName)
      throws IOException {
    try (ConnectionServiceClient client = ConnectionServiceClient.create()) {
      ConnectionName name = ConnectionName.of(projectId, location, connectionName);
      DeleteConnectionRequest request =
          DeleteConnectionRequest.newBuilder().setName(name.toString()).build();
      client.deleteConnection(request);
      System.out.println("Connection deleted successfully");
    }
  }
}

What's next