Console

1. Go to theBigQuery page.

   Go to BigQuery

2. In theExplorer pane, clickaddAdd.

   TheAdd data dialog opens.

3. In theFilter By pane, in theData Source Type section, selectDatabases.

   Alternatively, in theSearch for data sources field, you can entermysql.

4. In theFeatured data sources section, clickMySQL.

5. Click theCloudSQL (MySQL): BigQuery Federation solution card.

6. In theExternal data source dialog, enter the following information:

   • ForConnection type, select the type of source, for exampleMySQL orPostgreSQL.
   • ForConnection ID, enter an identifier for the connectionresource. Letter, numbers, and underscores are allowed. For example,bq_sql_connection.
   • ForData location, select a BigQuerylocation (or region) that iscompatible with your external data source region.
   • Optional: ForFriendly name, enter a user-friendly name forthe connection, such asMy connection resource. The friendly namecan be any value that helps you identify the connectionresource if you need to modify it later.
   • Optional: ForDescription, enter a description for thisconnection resource.
   • Optional:Encryption If you want to use acustomer-managed encryption key (CMEK) to encrypt your credentials, selectCustomer-managed encryption key (CMEK) and then select a customer-managed key. Otherwise, your credentials are protected by the default Google-owned and Google-managed encryption key.
   • If you chose Cloud SQL MySQL or Postgres for the connection type,forCloud SQL connection name, enter the fullname of the Cloud SQL instance,usually in the formatproject-id:location-id:instance-id.You can find the instance ID on the detail page of theCloud SQL instanceyou want to query.
   • ForDatabase name, enter the name of the database.
   • ForDatabase username, enter the username for the database.

   • ForDatabase password, enter the password for the database.

     • Optional: To see the password, clickvisibility_offShow password.
     Note: If the same user credentials are valid for other databases inthe external data source, that user can query those databasesthrough the same connection resource.

7. ClickCreate connection.

8. ClickGo to connection.

9. In theConnection info pane, copy the service account ID for use in afollowing step.

bq

Enter thebq mk command and supply the connection flag:--connection. The following flags are also required:

• --connection_type
• --properties
• --connection_credential
• --project_id
• --location

The following flags are optional:

• --display_name: The friendly name for the connection.
• --description: A description of the connection.
• --kms_key_name: A customer-managed encryption key. If omitted, credentials are protected by the default Google-owned and Google-managed encryption key.

Theconnection_id is an optional parameter that can be added as the last argument of the command whichis used for storage internally. If a connection ID is not provided a unique ID is automatically generated.Theconnection_id can contain letters, numbers, and underscores.

bqmk--connection --display_name='friendly name' --connection_type=TYPE \--properties=PROPERTIES --connection_credential=CREDENTIALS \--project_id=PROJECT_ID --location=LOCATION \CONNECTION_ID

bqmk--connection --display_name='friendly name' --connection_type='CLOUD_SQL' \--properties='{"instanceId":"federation-test:us-central1:mytestsql","database":"mydatabase","type":"MYSQL"}' \--connection_credential='{"username":"myusername", "password":"mypassword"}' \--project_id=federation-test --location=us my_connection_id

API

Within the BigQuery Connection API, you can invokeCreateConnection withintheConnectionService to instantiate a connection. See theclient library page for more details.

Java

importcom.google.cloud.bigquery.connection.v1.CloudSqlCredential;importcom.google.cloud.bigquery.connection.v1.CloudSqlProperties;importcom.google.cloud.bigquery.connection.v1.Connection;importcom.google.cloud.bigquery.connection.v1.CreateConnectionRequest;importcom.google.cloud.bigquery.connection.v1.LocationName;importcom.google.cloud.bigqueryconnection.v1.ConnectionServiceClient;importjava.io.IOException;// Sample to create a connection with cloud MySql databasepublicclassCreateConnection{publicstaticvoidmain(String[]args)throwsIOException{// TODO(developer): Replace these variables before running the sample.StringprojectId="MY_PROJECT_ID";Stringlocation="MY_LOCATION";StringconnectionId="MY_CONNECTION_ID";Stringdatabase="MY_DATABASE";Stringinstance="MY_INSTANCE";StringinstanceLocation="MY_INSTANCE_LOCATION";Stringusername="MY_USERNAME";Stringpassword="MY_PASSWORD";StringinstanceId=String.format("%s:%s:%s",projectId,instanceLocation,instance);CloudSqlCredentialcloudSqlCredential=CloudSqlCredential.newBuilder().setUsername(username).setPassword(password).build();CloudSqlPropertiescloudSqlProperties=CloudSqlProperties.newBuilder().setType(CloudSqlProperties.DatabaseType.MYSQL).setDatabase(database).setInstanceId(instanceId).setCredential(cloudSqlCredential).build();Connectionconnection=Connection.newBuilder().setCloudSql(cloudSqlProperties).build();createConnection(projectId,location,connectionId,connection);}staticvoidcreateConnection(StringprojectId,Stringlocation,StringconnectionId,Connectionconnection)throwsIOException{try(ConnectionServiceClientclient=ConnectionServiceClient.create()){LocationNameparent=LocationName.of(projectId,location);CreateConnectionRequestrequest=CreateConnectionRequest.newBuilder().setParent(parent.toString()).setConnection(connection).setConnectionId(connectionId).build();Connectionresponse=client.createConnection(request);System.out.println("Connection created successfully :"+response.getName());}}}

Console

1. Go to theIAM & Admin page.

   Go to IAM & Admin

2. Clickperson_addGrant Access.

   TheAdd principals dialog opens.

3. In theNew principals field, enter the service agent nameBigQuery Connection Service Agent or the service agent ID taken fromtheconnection information.

4. In theSelect a role field, selectCloud SQL, and then selectCloud SQL Client.

5. ClickSave.

gcloud

Use thegcloud projects add-iam-policy-bindingcommand:

gcloud projects add-iam-policy-bindingPROJECT_ID --member=serviceAccount:SERVICE_AGENT_ID --role=roles/cloudsql.client

Provide the following values:

• PROJECT_ID: Your Google Cloud project ID.
• SERVICE_AGENT_ID: The service agent ID takenfrom theconnection information.

Console

1. Go to theBigQuery page.

   Go to BigQuery

   Connections are listed in your project, in a group calledConnections.

2. In the left pane, clickexploreExplorer:

   

   If you don't see the left pane, clicklast_pageExpand left pane to open the pane.

3. Click your project, clickConnections, and then select a connection.

4. In theDetails pane, clickShare to share a connection.Then do the following:

   1. In theConnection permissions dialog, share theconnection with other principals by adding or editingprincipals.

   2. ClickSave.

bq

You cannot share a connection with the bq command-line tool.To share a connection, use the Google Cloud console orthe BigQuery Connections API method to share a connection.

API

Use theprojects.locations.connections.setIAM methodin the BigQuery Connections REST API reference section, andsupply an instance of thepolicy resource.

Java

importcom.google.api.resourcenames.ResourceName;importcom.google.cloud.bigquery.connection.v1.ConnectionName;importcom.google.cloud.bigqueryconnection.v1.ConnectionServiceClient;importcom.google.iam.v1.Binding;importcom.google.iam.v1.Policy;importcom.google.iam.v1.SetIamPolicyRequest;importjava.io.IOException;// Sample to share connectionspublicclassShareConnection{publicstaticvoidmain(String[]args)throwsIOException{// TODO(developer): Replace these variables before running the sample.StringprojectId="MY_PROJECT_ID";Stringlocation="MY_LOCATION";StringconnectionId="MY_CONNECTION_ID";shareConnection(projectId,location,connectionId);}staticvoidshareConnection(StringprojectId,Stringlocation,StringconnectionId)throwsIOException{try(ConnectionServiceClientclient=ConnectionServiceClient.create()){ResourceNameresource=ConnectionName.of(projectId,location,connectionId);Bindingbinding=Binding.newBuilder().addMembers("group:example-analyst-group@google.com").setRole("roles/bigquery.connectionUser").build();Policypolicy=Policy.newBuilder().addBindings(binding).build();SetIamPolicyRequestrequest=SetIamPolicyRequest.newBuilder().setResource(resource.toString()).setPolicy(policy).build();client.setIamPolicy(request);System.out.println("Connection shared successfully");}}}