Working with connections

Overview

BigQuery Cloud SQL federation enables BigQuery to query data residing in Cloud SQL in real time, without copying or moving data. Query federation supports both MySQL (second generation) and PostgreSQL instances in Cloud SQL.

After the initial one-time setup to create a connection resource in BigQuery, you can perform the following tasks:

• Query a connection resource by writing a query with the new SQL function EXTERNAL_QUERY()
• Get information about a connection resource
• List all connection resources
• Share a connection resource by granting permissions to another user
• Edit or update a connection resource
• Delete a connection resource

Create a connection resource

For detailed instructions about setting up a connection resource, see Setting up Cloud SQL database connections.

Key Point: Create your connection in the Google Cloud project that contains the Cloud SQL instance that you want to query.

Query a connection resource

Once a connection is established to a Cloud SQL instance, you can execute federated querying with a new function: EXTERNAL_QUERY(). See Federated query syntax for more information and sample queries.

Query another database through the same connection resource

When you create a connection to a Cloud SQL instance, you specify a database, username, and password in that instance. If the same user credentials are valid for other databases in the Cloud SQL instance, that user can query those databases through the same connection resource.

Get information about a connection resource

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

Required permissions

Getting information about a connection resource requires the bigquery.connections.get permission. The following predefined Cloud Identity and Access Management (Cloud IAM) roles already include the bigquery.connections.get permission:

• bigquery.admin
• bigquery.connection.admin
• bigquery.connection.user

For more information about Cloud IAM roles and permissions in BigQuery, see Predefined roles and permissions.

Viewing a connection resource

Console

1. To view the status of your connection resources, go to the BigQuery web UI.

   Go to the BigQuery web UI

2. Connection resources will be listed at the top level under your project, under a group called External connections. Click on a connection to see information about that connection resource, such as connection ID and Cloud SQL instance ID.

   

CLI

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

    bq show --connection project.location.connection_id

For example, the following command gets information about a connection resource named my_new_connection in a project with the ID federation-test located in the region us.

    bq show --connection federation-test.us.my_new_connection

API

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

List all connection resources

Required permissions

Listing all the connection resources in a project requires the bigquery.connections.list permissions. The following predefined Cloud IAM roles already include the bigquery.connections.list permission:

• bigquery.admin
• bigquery.connection.admin
• bigquery.connection.user

For more information about Cloud IAM roles and permissions in BigQuery, see Predefined roles and permissions.

Listing connection resources

To list all the connection resources in a project:

Console

1. To view the status of a connection resource, go to the BigQuery web UI.

   Go to the BigQuery web UI

2. Connection resources will be listed at the top level under your project, under a group called External connections.

   

CLI

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

    bq ls --connection --project_id=[project_id] --location=[location]

For example, the following command lists the connection resources in a project with the ID federation-test located in the region us.

    bq ls --connection --project_id=bigquery-federation-test --location=us

API

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

Share a connection resource

Required permissions

To allow another user to use a connection resource for Cloud SQL federated queries, the bigquery.admin user can grant the two different roles in Cloud IAM.

BigQuery Connection User

The bigquery.connection.user role is intended for users who need to run queries with the connection resource.

This Cloud IAM role includes the following permissions:

• bigquery.connections.get
• bigquery.connections.list
• bigquery.connections.use
• bigquery.connections.getIamPolicy

BigQuery Connection Admin

The bigquery.connection.admin role is intended for users who need to manage connection resources.

This Cloud IAM role includes all the same permissions as bigquery.connection.user, plus additional permissions to create, update, and delete existing connection resources, as well as to set Cloud IAM policy on connection resources.

• bigquery.connections.create
• bigquery.connections.update
• bigquery.connections.setIamPolicy
• bigquery.connections.delete

For more information about Cloud IAM roles and permissions in BigQuery, see Predefined roles and permissions.

Sharing a connection resource

Console

1. To share a connection resource, go to the BigQuery web UI.

   Go to the BigQuery web UI

2. Connection resources will be listed at the top level under your project, under a group called External connections. Click on a connection to see information about that connection resource.

   

3. Click on the SHARE CONNECTION button to see the permissions pane for that connection resource.

   

4. In the Connection permissions pane, users with the role BigQuery Admin or BigQuery Connection Admin can share the connection resource with other users by adding or editing other users' permissions.

   

5. Click Done.

CLI

Currently, you cannot share a resource with the bq command-line tool. To share a connection resource, use the BigQuery web UI or the BigQuery Connections API method to share a connection.

API

See the projects.locations.connections.setIAM method in the BigQuery Connections REST API reference section, and supply an instance of the policy resource.

Edit or update a connection resource

Once the connection resource is added, you can edit the connection resource. You can edit most of the fields populated during connection resource creation, including the username credentials.

A connection uses the credentials of the user who created it. If you need to change the user attached to a connection resource, 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 resource:

• Connection type
• Connection ID
• Location

Required permissions

Updating a connection resource requires the bigquery.connections.update permission.

The following predefined Cloud IAM roles already include the bigquery.connections.update permission:

• bigquery.admin
• bigquery.connection.admin

For more information about Cloud IAM roles and permissions in BigQuery, see Predefined roles and permissions.

Editing a connection resource

To edit a connection:

Console

1. In the Cloud Console, go to the BigQuery web UI.

   Go to the BigQuery web UI

2. Connection resources will be listed at the top level under your project, under a group called External connections. Click on a connection to see information about that connection resource.

   

3. Click on the EDIT CONNECTION button to see the edit pane for that connection resource.

   

4. Edit any field shown, including the user credentials.

   

5. Click Save.

CLI

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" : "db", "type" : "MYSQL" }'
   --connection_credential='{"username":"u", "password":"p"}'
   project:location.connection_id

For example, the following command updates the connection resources 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 resource.

Delete a connection resource

You can delete a connection resource if you have the correct permissions to do so.

Required permissions

Deleting a connection resource requires the bigquery.connections.delete permission.

The following predefined Cloud IAM roles already include the bigquery.connections.delete permission:

• bigquery.admin
• bigquery.connection.admin

For more information on Cloud IAM roles and permissions in BigQuery, see Predefined roles and permissions.

Deleting a connection resource

To delete a connection:

Console

1. In the Google Cloud Console, go to the BigQuery web UI.

   Go to the BigQuery web UI

2. Connection resources will be listed at the top level under your project, under a group called External connections. Click on a connection to see information about that connection resource.

   

3. Click the DELETE CONNECTION button to see the delete pane for that connection resource.

   

4. In the Delete connection? dialog box, enter delete to confirm your intention.

   

5. Click DELETE.

CLI

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

    bq rm --connection project:location.connection_id

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

    bq rm --connection federation-test:us.test-mysql

API

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