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.
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
Identity and Access Management (IAM) roles already include the bigquery.connections.get
permission:
bigquery.admin
bigquery.connection.admin
bigquery.connection.user
For more information about IAM roles and permissions in BigQuery, see Predefined roles and permissions.
Viewing a connection resource
Console
To view the status of your connection resources, go to the BigQuery page in the Cloud Console.
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
andCloud SQL instance ID
.
bq
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.
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.
List all connection resources
Required permissions
Listing all the connection resources in a project requires the
bigquery.connections.list
permissions. The following predefined
IAM roles already include the bigquery.connections.list
permission:
bigquery.admin
bigquery.connection.admin
bigquery.connection.user
For more information about IAM roles and permissions in BigQuery, see Predefined roles and permissions.
Listing connection resources
To list all the connection resources in a project:
Console
To view the status of a connection resource, go to the BigQuery page in the Cloud Console.
Connection resources will be listed at the top level under your project, under a group called External connections.
bq
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.
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.
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 IAM.
BigQuery Connection User
The bigquery.connection.user
role is intended for users who need to run
queries with the connection resource.
This 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 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 IAM
policy on connection resources.
bigquery.connections.create
bigquery.connections.update
bigquery.connections.setIamPolicy
bigquery.connections.delete
For more information about IAM roles and permissions in BigQuery, see Predefined roles and permissions.
Sharing a connection resource
Console
To share a connection resource, go to the BigQuery page in the Cloud Console.
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.
Click on the SHARE CONNECTION button to see the permissions pane for that connection resource.
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.
Click Done.
bq
Currently, you cannot share a resource with the bq
command-line tool.
To share a connection resource, use the Cloud Console 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.
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.
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 IAM roles already include the
bigquery.connections.update
permission:
bigquery.admin
bigquery.connection.admin
For more information about IAM roles and permissions in BigQuery, see Predefined roles and permissions.
Editing a connection resource
To edit a connection:
Console
In the Cloud Console, go to the BigQuery page.
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.
Click on the EDIT CONNECTION button to see the edit pane for that connection resource.
Edit any field shown, including the user credentials.
Click Save.
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" : "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.
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.
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 IAM roles already include the
bigquery.connections.delete
permission:
bigquery.admin
bigquery.connection.admin
For more information on IAM roles and permissions in BigQuery, see Predefined roles and permissions.
Deleting a connection resource
To delete a connection:
Console
In the Google Cloud Console, go to the BigQuery page.
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.
Click the DELETE CONNECTION button to see the delete pane for that connection resource.
In the Delete connection? dialog box, enter
delete
to confirm your intention.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.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.
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.