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:

Amazon S3 connections
Apache Spark connections
Blob Storage connections
Cloud resource connections to connect to Cloud Storage data and to implement remote functions
Spanner connections
Cloud SQL connections
AlloyDB connections

To create a default connection for a project, see the Default connection overview.

Before you begin

Ensure that you have a working connection. Connections are type-specific and depend on the connected external data source.
Enable the BigQuery Connection API.

Enable the API
Ensure that you can view a list of service accounts in your project. BigQuery creates and uses a service account to connect to your external data source. When you create a connection, a Google Cloud–managed Identity and Access Management (IAM) service account is created on your behalf. To view the service account attached to a particular connection, view the connection details.

Required roles

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

View connection details: BigQuery Connection User (roles/bigquery.connectionUser) on your dataset
List all connections: BigQuery Connection User (roles/bigquery.connectionUser) on your dataset
Share a connection: BigQuery Connection Admin (roles/bigquery.connectionAdmin) on your connection
Edit a connection: BigQuery Connection Admin (roles/bigquery.connectionAdmin) on your connection
Delete a connection: BigQuery Connection Admin (roles/bigquery.connectionAdmin) on your connection

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.

For information about the roles needed to create and use a default connection, see Required roles and permissions.

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

List all connections

Select one of the following options:

Console

Go to the BigQuery page.

Go to BigQuery

Connections are listed in your project, in a group called External connections.
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:

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

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

Go to the BigQuery page.

Go to BigQuery

Connections are listed in your project, in a group called External connections.
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());
    }
  }
}

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.

Go to BigQuery

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:
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: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");
    }
  }
}

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

Go to the BigQuery page.

Go to BigQuery

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, 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

Go to the BigQuery page.

Go to BigQuery

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 Delete to delete the connection.
In the Delete connection? dialog, enter delete to confirm deletion.
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

Learn how to use remote functions.
Learn how to use stored procedures for Spark.

Manage connections

Before you begin

Required roles

Required permissions

List all connections

Console

bq

API

Java

View connection details

Console

bq

API

Java

Share a connection with users

Console

bq

API

Java

Edit a connection

Console

bq

API

Java

Delete a connection

Console

bq

API

Java

What's next