Connect to Spanner

Console

1. Go to the BigQuerypage.

   Go to BigQuery

2. In the Explorerpane, click add Add data.

   The Add datadialog opens.

3. In the Filter Bypane, in the Data Source Typesection, select Databases.

   Alternatively, in the Search for data sourcesfield, you can enter Spanner .

4. In the Featured data sourcessection, click Google Cloud Spanner.

5. Click the Google Cloud Spanner: BigQuery Federationsolution card.

6. In the External data sourcepane, enter the following information:

   • For Connection type, select Cloud Spanner.
   • For Connection ID, enter an identifier for the connection resource. Letter, numbers, and underscores are allowed.
   • For Location type, select a BigQuery location (or region) that is compatible with your external data source region .
   • Optional: For Friendly name, enter a user-friendly name for the connection, such as My connection resource . The friendly name can be any value that helps you identify the connection resource if you need to modify it later.
   • Optional: For Description, enter a description for this connection resource.
   • For Database name, enter the name of the Spanner database in the following format: "projects/ PROJECT_ID /instances/ INSTANCE /databases/ DATABASE "
   • Optional: To perform parallel reads, select Read data in parallel. Spanner can divide certain queries into smaller pieces, or partitions, and fetch the partitions in parallel. For more information, see Read data in parallel in the Spanner documentation. This option is restricted to queries whose first operator in the execution plan is a distributed union operator. Other queries return an error. To view the query execution plan for a Spanner query, see Understand how Spanner executes queries .
   • Optional: For Database role, enter the name of a Spanner database role. If not empty, this connection queries Spanner using this database role by default. Spanner fine-grained access control users who submit queries through this connection must have been granted access to this role by their administrator, and the database role must have the SELECT privilege on all schema objects specified in external queries. For information about fine-grained access control, see About fine-grained access control .
   • Optional: To enable Data Boost, select Use Spanner Data Boost. Data Boost lets you execute analytics queries and data exports with near-zero impact to existing workloads on the provisioned BigQuery instance. To enable Data Boost, select Data Boostand Read data in parallel.

7. Click Create connection.

bq

To create the connection, use the bq mk command with the --connection flag.

bq  
mk  
--connection  
 \ 
  
--connection_type = 
CLOUD_SPANNER  
 \ 
  
--properties = 
 ' PROPERTIES 
' 
  
 \ 
  
--location = 
 LOCATION 
  
 \ 
  
--display_name = 
 ' FRIENDLY_NAME 
' 
  
 \ 
  
--description  
 ' DESCRIPTION 
' 
  
 \ 
  
 CONNECTION_ID 

Replace the following:

• PROPERTIES : a JSON object with the following fields:

  • "database" : the Spanner database for the connection

    Specify as a string with the following format: "projects/ PROJECT_ID /instances/ INSTANCE /databases/ DATABASE " .

  • "use_parallelism" : (Optional) if true , this connection performs parallel reads

    The default value is false . Spanner can divide certain queries into smaller pieces, or partitions, and fetch the partitions in parallel. For more information, see Read data in parallel in the Spanner documentation. This option is restricted to queries whose first operator in the execution plan is a distributed union operator. Other queries return an error. To view the query execution plan for a Spanner query, see Understand how Spanner executes queries .

  • "database_role" : (Optional) If not empty, this connection queries Spanner using this database role by default. Spanner fine-grained access control users who submit queries through this connection must have been granted access to this role by their administrator, and the database role must have the SELECT privilege on all schema objects specified in external queries.

    If not specified, the connection authenticates with IAM predefined roles for Spanner, and the principal running queries with this connection must have been granted the roles/spanner.databaseReader IAM role.

    For information about fine-grained access control, see About fine-grained access control .

  • "useDataBoost" : (Optional) If true , this connection lets users use Data Boost . Data Boost lets users run federated queries in separate, independent, compute capacity distinct from provisioned instances to avoid impacting existing workloads. To enable Data Boost, set "useDataBoost" to true and "use_parallelism" to true .

    In order to use Data Boost, the principal running queries with this connection must have been granted the spanner.databases.useDataBoost permission. This permission is included by default in the roles/spanner.admin and roles/spanner.databaseAdmin roles.

• LOCATION : a BigQuery location that is compatible with your external data source region .

• CONNECTION_ID : an identifier for the connection resource

  The connection ID can contain letters, numbers and underscores. If you don't provide a connection ID, BigQuery automatically generates a unique ID.

  The following example creates a new connection resource named my_connection_id .

  bq  
  mk  
  --connection  
   \ 
    
  --connection_type = 
   'CLOUD_SPANNER' 
    
   \ 
    
  --properties = 
   '{"database":"projects/my_project/instances/my_instance/databases/database1"}' 
    
   \ 
    
  --project_id = 
  federation-test  
   \ 
    
  --location = 
  us  
   \ 
    
  my_connection_id

API

Call the CreateConnection method within the ConnectionService service.

Console

1. Go to the BigQuerypage.

   Go to BigQuery

   Connections are listed in your project, in a group called External connections.

2. In the Explorerpane, click your project name > External connections > connection .

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

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