Run federated queries with Data Boost

Console

1. Go to Create Spanner connections in the BigQuery documentation and follow the instructions on the Console tab.

2. In the External data source pane, do the following:

   • Select Read data in parallel.
   • Select Use Spanner Data Boost.

bq

1. Go to Create Spanner connections in the BigQuery documentation and follow instructions on the bq* tab.

2. Set the following connection properties to true:

   • useParallelism
   • useDataBoost

The following example uses the bq mk command to create a new connection named my_connection with the two required properties for Data Boost:

bq mk --connection --connection_type='CLOUD_SPANNER' --location='us' \
--properties='{"database":"projects/my-project/instances/my-instance/databases/my-database", "useParallelism":true, "useDataBoost": true}' my_connection

Console

1. Go to the BigQuery page.

   Go to BigQuery

2. In the left pane, click explore Explorer:

   

   If you don't see the left pane, click last_page Expand left pane to open the pane.

3. In the Explorer pane, expand your project name, and then click Connections.

4. On the Connections page, click Create connection.

5. For Connection type, choose Vertex AI remote models, remote functions, BigLake and Spanner (Cloud Resource).

6. In the Connection ID field, enter a name for your connection.

7. For Location type, select a location for your connection. The connection should be colocated with your other resources such as datasets.

8. Click Create connection.

9. Click Go to connection.

10. In the Connection info pane, copy the service account ID for use in a later step.

bq

1. In a command-line environment, create a connection:

   bq mk --connection --location=REGION --project_id=PROJECT_ID \
       --connection_type=CLOUD_RESOURCE CONNECTION_ID

   The --project_id parameter overrides the default project.

   Replace the following:

   • REGION: your connection region
   • PROJECT_ID: your Google Cloud project ID
   • CONNECTION_ID: an ID for your connection

   When you create a connection resource, BigQuery creates a unique system service account and associates it with the connection.

   Troubleshooting: If you get the following connection error, update the Google Cloud SDK:

   Flags parsing error: flag --connection_type=CLOUD_RESOURCE: value should be one of...
   

2. Retrieve and copy the service account ID for use in a later step:

   bq show --connection PROJECT_ID.REGION.CONNECTION_ID

   The output is similar to the following:

   name                          properties
   1234.REGION.CONNECTION_ID     {"serviceAccountId": "connection-1234-9u56h9@gcp-sa-bigquery-condel.iam.gserviceaccount.com"}
   

Python

import google.api_core.exceptions
from google.cloud import bigquery_connection_v1

client = bigquery_connection_v1.ConnectionServiceClient()


def create_connection(
    project_id: str,
    location: str,
    connection_id: str,
):
    """Creates a BigQuery connection to a Cloud Resource.

    Cloud Resource connection creates a service account which can then be
    granted access to other Google Cloud resources for federated queries.

    Args:
        project_id: The Google Cloud project ID.
        location: The location of the connection (for example, "us-central1").
        connection_id: The ID of the connection to create.
    """

    parent = client.common_location_path(project_id, location)

    connection = bigquery_connection_v1.Connection(
        friendly_name="Example Connection",
        description="A sample connection for a Cloud Resource.",
        cloud_resource=bigquery_connection_v1.CloudResourceProperties(),
    )

    try:
        created_connection = client.create_connection(
            parent=parent, connection_id=connection_id, connection=connection
        )
        print(f"Successfully created connection: {created_connection.name}")
        print(f"Friendly name: {created_connection.friendly_name}")
        print(
            f"Service Account: {created_connection.cloud_resource.service_account_id}"
        )

    except google.api_core.exceptions.AlreadyExists:
        print(f"Connection with ID '{connection_id}' already exists.")
        print("Please use a different connection ID.")
    except Exception as e:
        print(f"An unexpected error occurred while creating the connection: {e}")

Node.js

const {ConnectionServiceClient} =
  require('@google-cloud/bigquery-connection').v1;
const {status} = require('@grpc/grpc-js');

const client = new ConnectionServiceClient();

/**
 * Creates a new BigQuery connection to a Cloud Resource.
 *
 * A Cloud Resource connection creates a service account that can be granted access
 * to other Google Cloud resources.
 *
 * @param {string} projectId The Google Cloud project ID. for example, 'example-project-id'
 * @param {string} location The location of the project to create the connection in. for example, 'us-central1'
 * @param {string} connectionId The ID of the connection to create. for example, 'example-connection-id'
 */
async function createConnection(projectId, location, connectionId) {
  const parent = client.locationPath(projectId, location);

  const connection = {
    friendlyName: 'Example Connection',
    description: 'A sample connection for a Cloud Resource',
    // The service account for this cloudResource will be created by the API.
    // Its ID will be available in the response.
    cloudResource: {},
  };

  const request = {
    parent,
    connectionId,
    connection,
  };

  try {
    const [response] = await client.createConnection(request);

    console.log(`Successfully created connection: ${response.name}`);
    console.log(`Friendly name: ${response.friendlyName}`);

    console.log(`Service Account: ${response.cloudResource.serviceAccountId}`);
  } catch (err) {
    if (err.code === status.ALREADY_EXISTS) {
      console.log(`Connection '${connectionId}' already exists.`);
    } else {
      console.error(`Error creating connection: ${err.message}`);
    }
  }
}

Terraform

Use the google_bigquery_connection resource.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

The following example creates a Cloud resource connection named my_cloud_resource_connection in the US region:

# This queries the provider for project information.
data "google_project" "default" {}

# This creates a cloud resource connection in the US region named my_cloud_resource_connection.
# Note: The cloud resource nested object has only one output field - serviceAccountId.
resource "google_bigquery_connection" "default" {
  connection_id = "my_cloud_resource_connection"
  project       = data.google_project.default.project_id
  location      = "US"
  cloud_resource {}
}

To apply your Terraform configuration in a Google Cloud project, complete the steps in the following sections.

Prepare Cloud Shell

1. Launch Cloud Shell.

2. Set the default Google Cloud project where you want to apply your Terraform configurations.

   You only need to run this command once per project, and you can run it in any directory.

   export GOOGLE_CLOUD_PROJECT=PROJECT_ID

   Environment variables are overridden if you set explicit values in the Terraform configuration file.

Prepare the directory

Each Terraform configuration file must have its own directory (also called a root module).

1. In Cloud Shell, create a directory and a new file within that directory. The filename must have the .tf extension—for example main.tf. In this tutorial, the file is referred to as main.tf.

   mkdir DIRECTORY && cd DIRECTORY && touch main.tf

2. If you are following a tutorial, you can copy the sample code in each section or step.

   Copy the sample code into the newly created main.tf.

   Optionally, copy the code from GitHub. This is recommended when the Terraform snippet is part of an end-to-end solution.

3. Review and modify the sample parameters to apply to your environment.
4. Save your changes.
5. Initialize Terraform. You only need to do this once per directory.

   terraform init

   Optionally, to use the latest Google provider version, include the -upgrade option:

   terraform init -upgrade

Apply the changes

1. Review the configuration and verify that the resources that Terraform is going to create or update match your expectations:

   terraform plan

   Make corrections to the configuration as necessary.

2. Apply the Terraform configuration by running the following command and entering yes at the prompt:

   terraform apply

   Wait until Terraform displays the "Apply complete!" message.

3. Open your Google Cloud project to view the results. In the Google Cloud console, navigate to your resources in the UI to make sure that Terraform has created or updated them.

Console

1. Open the BigQuery page in the Google Cloud console.

   Go to the BigQuery page

2. In the left pane, click explore Explorer:

   

   If you don't see the left pane, click last_page Expand left pane to open the pane.

3. In the Explorer pane, select the project where you want to create the dataset.

4. Click more_vert View actions, and then click Create dataset.

5. On the Create dataset page, do the following:

   • For Dataset ID, enter a unique dataset name.
   • For Location type, choose a location for the dataset, such as us-central1 or multiregion us. After you create a dataset, the location can't be changed.

   • For External Dataset, do the following:

     • Check the box next to Link to an external dataset.
     • For External dataset type, select Spanner.
     • For External source, enter the full identifier of your Spanner database in the following format: projects/PROJECT_ID/instances/INSTANCE/databases/DATABASE. For example: projects/my_project/instances/my_instance/databases/my_database.
     • Optionally, for Database role enter the name of a Spanner database role. For more information read about Database roles used for creating Spanner Connections
     • Optionally, check the box next to Use a Cloud Resource connection to create the external dataset with a connection.

   • Leave the other default settings as they are.

6. Click Create dataset.

SQL

Use the CREATE EXTERNAL SCHEMA data definition language (DDL) statement.

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the query editor, enter the following statement:

   CREATE EXTERNAL SCHEMA DATASET_NAME
     OPTIONS (
       external_source = 'SPANNER_EXTERNAL_SOURCE',
       location = 'LOCATION');
   /*
     Alternatively, create with a connection:
   */
   CREATE EXTERNAL SCHEMA DATASET_NAME
     WITH CONNECTION PROJECT_ID.LOCATION.CONNECTION_NAME
     OPTIONS (
       external_source = 'SPANNER_EXTERNAL_SOURCE',
       location = 'LOCATION');

   Replace the following:

   • DATASET_NAME: the name of your new dataset in BigQuery.
   • SPANNER_EXTERNAL_SOURCE: the full, qualified Spanner database name, with a prefix identifying the source, in the following format: google-cloudspanner://[DATABASE_ROLE@]/projects/PROJECT_ID/instances/INSTANCE/databases/DATABASE. For example: google-cloudspanner://admin@/projects/my_project/instances/my_instance/databases/my_database or google-cloudspanner:/projects/my_project/instances/my_instance/databases/my_database.
   • LOCATION: the location of your new dataset in BigQuery, for example, us-central1. After you create a dataset, you can't change its location.
   • (Optional)CONNECTION_NAME: the name of your Cloud resource connection.

3. Click play_circle Run.

For more information about how to run queries, see Run an interactive query.

bq

In a command-line environment, create an external dataset by using the bq mk command:

bq --location=LOCATION mk --dataset \
    --external_source SPANNER_EXTERNAL_SOURCE \
    DATASET_NAME

Alternatively, create with a connection:

bq --location=LOCATION mk --dataset \
    --external_source SPANNER_EXTERNAL_SOURCE \
    --connection_id PROJECT_ID.LOCATION.CONNECTION_NAME \
    DATASET_NAME

Replace the following:

• LOCATION: the location of your new dataset in BigQuery—for example, us-central1. After you create a dataset, you can't change its location. You can set a default location value by using the .bigqueryrc file.
• SPANNER_EXTERNAL_SOURCE: the full, qualified Spanner database name, with a prefix identifying the source, in the following format: google-cloudspanner://[DATABASE_ROLE@]/projects/PROJECT_ID/instances/INSTANCE/databases/DATABASE. For example: google-cloudspanner://admin@/projects/my_project/instances/my_instance/databases/my_database or google-cloudspanner:/projects/my_project/instances/my_instance/databases/my_database.
• DATASET_NAME: the name of your new dataset in BigQuery. To create a dataset in a project other than your default project, add the project ID to the dataset name in the following format: PROJECT_ID:DATASET_NAME.
• (Optional)CONNECTION_NAME: the name of your Cloud resource connection.

Terraform

Use the google_bigquery_dataset resource.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

The following example creates a Spanner external dataset:

resource "google_bigquery_dataset" "default" {
  dataset_id    = "my_external_dataset"
  friendly_name = "My external dataset"
  description   = "This is a test description."
  location      = "US"
  external_dataset_reference {
    # The full identifier of your Spanner database.
    external_source = "google-cloudspanner:/projects/my_project/instances/my_instance/databases/my_database"
    # Must be empty for a Spanner external dataset.
    connection = ""
  }
}

To apply your Terraform configuration in a Google Cloud project, complete the steps in the following sections.

Prepare Cloud Shell

1. Launch Cloud Shell.

2. Set the default Google Cloud project where you want to apply your Terraform configurations.

   You only need to run this command once per project, and you can run it in any directory.

   export GOOGLE_CLOUD_PROJECT=PROJECT_ID

   Environment variables are overridden if you set explicit values in the Terraform configuration file.

Prepare the directory

Each Terraform configuration file must have its own directory (also called a root module).

1. In Cloud Shell, create a directory and a new file within that directory. The filename must have the .tf extension—for example main.tf. In this tutorial, the file is referred to as main.tf.

   mkdir DIRECTORY && cd DIRECTORY && touch main.tf

2. If you are following a tutorial, you can copy the sample code in each section or step.

   Copy the sample code into the newly created main.tf.

   Optionally, copy the code from GitHub. This is recommended when the Terraform snippet is part of an end-to-end solution.

3. Review and modify the sample parameters to apply to your environment.
4. Save your changes.
5. Initialize Terraform. You only need to do this once per directory.

   terraform init

   Optionally, to use the latest Google provider version, include the -upgrade option:

   terraform init -upgrade

Apply the changes

1. Review the configuration and verify that the resources that Terraform is going to create or update match your expectations:

   terraform plan

   Make corrections to the configuration as necessary.

2. Apply the Terraform configuration by running the following command and entering yes at the prompt:

   terraform apply

   Wait until Terraform displays the "Apply complete!" message.

3. Open your Google Cloud project to view the results. In the Google Cloud console, navigate to your resources in the UI to make sure that Terraform has created or updated them.

API

Call the datasets.insert method with a defined dataset resource and externalDatasetReference field for your Spanner database.