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 permissionssection:

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

Go to BigQuery

Connections are listed in your project, in a group called External connections.
In the Explorerpane, click your project name > External connectionsto 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 BigQuerypage.

Go to BigQuery

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

Go to BigQuery

Connections are listed in your project, in a group called External connections.
In the Explorerpane, click your project name > External connections > connection .
In the Detailspane, click Shareto share a connection. Then do the following:
1. In the Connection permissionsdialog, 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 BigQuerypage.

Go to BigQuery

Connections are listed in your project, in a group called External connections.
In the Explorerpane, click your project name > External connections > connection .
In the Detailspane, to edit details, click Edit details. Then do the following:
1. In the Edit connectiondialog, 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 BigQuerypage.

Go to BigQuery

Connections are listed in your project, in a group called External connections.
In the Explorerpane, click your project name > External connections > connection .
In the Detailspane, click Deleteto 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