Generate text with the AI.GENERATE function Stay organized with collections Save and categorize content based on your preferences.

Create a dataset

Create a BigQuery dataset to contain your resources:

Create a connection

After you create the training dataset, you establish a connection to link BigQuery and external sources. Create a Cloud resource connection and get the connection's service account. Create the connection in the same location as the dataset that you created in the previous step.

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

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

# 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 {}
}

Give the service account access

Grant the connection's service account the Agent Platform User and Storage Object Viewer roles. To grant the roles, follow these steps:

1. Go to the IAM & Admin page.

   Go to IAM & Admin

2. Click person_add Add.

   The Add principals dialog opens.

3. In the New principals field, enter the service account ID that you copied earlier.

4. In the Select a role field, select Vertex AI, and then select Agent Platform User.

5. Click Add another role.

6. In the Select a role field, choose Cloud Storage, and then select Storage Object Viewer.

7. Click Save.

Summarize text and use the default output format

To summarize news articles, call the AI.GENERATE function with the article text as your prompt. By default, the output includes the generated summary text, the full response, and a status that is empty if the function returns successfully.

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

   Go to BigQuery

2. In the query editor, run the following query:

   WITH
   bbc_news AS (
     SELECT body FROM `bigquery-public-data.bbc_news.fulltext` LIMIT 5
   )
   SELECT AI.GENERATE(body, endpoint => 'gemini-2.5-pro') AS news FROM bbc_news;

   The output is similar to the following:

   +---------------------------------------------+------------------------------------+---------------+
   | news.result                                 | news.full_response                 | news.status   |
   +---------------------------------------------+------------------------------------+---------------+
   | This article presents a debate about the    | {"candidates":[{"avg_logprobs":    |               |
   | "digital divide" between rich and poor      | -0.31465074559841777, content":    |               |
   | nations. Here's a breakdown of the key...   | {"parts":[{"text":"This article... |               |
   +---------------------------------------------+------------------------------------+---------------+
   | This article discusses how advanced         | {"candidates":[{"avg_logprobs":    |               |
   | mapping technology is aiding humanitarian   | -0.21313422900091983,"content":    |               |
   | efforts in Darfur, Sudan. Here's a...       | {"parts":[{"text":"This article... |               |
   +---------------------------------------------+------------------------------------+---------------+
   | ...                                         | ...                                | ...           |
   +---------------------------------------------+------------------------------------+---------------+
   

Summarize text and output structured results

Follow these steps to generate text using the AI.GENERATE function, and use the AI.GENERATE function's output_schema argument to format the output:

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

   Go to BigQuery

2. In the query editor, run the following query:

   WITH bbc_news AS (
     SELECT
       body
     FROM
       `bigquery-public-data`.bbc_news.fulltext
     LIMIT 5
   )
   SELECT
     news.good_sentiment,
     news.summary
   FROM
     bbc_news,
     UNNEST(ARRAY[AI.GENERATE(body, endpoint => 'gemini-2.5-pro', output_schema  => 'summary STRING, good_sentiment BOOL')]) AS news;

   The output is similar to the following:

   +----------------+--------------------------------------------+
   | good_sentiment | summary                                    |
   +----------------+--------------------------------------------+
   | true           | A World Bank report suggests the digital   |
   |                | divide is rapidly closing due to increased |
   |                | access to technology in developing...      |
   +----------------+--------------------------------------------+
   | false          | A massive earthquake and subsequent        |
   |                | waves have devastated southern Asia, with  |
   |                | Sri Lanka, India, Indonesia, and...        |
   +----------------+--------------------------------------------+
   | ...            | ...                                        |
   +----------------+--------------------------------------------+
   

Transcribe and translate video content

You can process multimedia files stored in Cloud Storage by using external object tables. The following steps show how to create an object table for video files, transcribe the Japanese video content, and translate the text to English.

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

   Go to BigQuery

2. In the query editor, run the following query to create the object table:

   CREATE OR REPLACE EXTERNAL TABLE `bqml_tutorial.video`
   WITH CONNECTION `us.test_connection`
   OPTIONS (
     object_metadata = 'SIMPLE',
     uris =
       ['gs://cloud-samples-data/generative-ai/video/*']);

3. In the query editor, run the following query to transcribe and translate the pixel8.mp4 file:

   SELECT
     AI.GENERATE(
       (OBJ.GET_ACCESS_URL(ref, 'r'), 'Transcribe the video in Japanese and then translate to English.'),
       endpoint => 'gemini-2.5-pro',
       output_schema => 'japanese_transcript STRING, english_translation STRING'
     ).* EXCEPT (full_response, status)
   FROM
     `bqml_tutorial.video`
   WHERE
     REGEXP_CONTAINS(uri, 'pixel8.mp4');

   The output is similar to the following:

   +--------------------------------------------+--------------------------------+
   | english_translation                        | japanese_transcript            |
   +--------------------------------------------+--------------------------------+
   | My name is Saeka Shimada. I'm a            |            |
   | photographer in Tokyo. Tokyo has many      |            |
   | faces. The city at night is totally...     |            |
   +--------------------------------------------+--------------------------------+
   

Analyze audio file content

Follow these steps to create an object table over public audio content, and then analyze the content of the audio files.

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

   Go to BigQuery

2. In the query editor, run the following query to create the object table:

   CREATE OR REPLACE EXTERNAL TABLE `bqml_tutorial.audio`
   WITH CONNECTION `us.test_connection`
   OPTIONS (
     object_metadata = 'SIMPLE',
     uris =
       ['gs://cloud-samples-data/generative-ai/audio/*']);

3. In the query editor, run the following query to analyze the audio files:

   SELECT
     AI.GENERATE(
       (OBJ.GET_ACCESS_URL(ref, 'r'), 'Summarize the content of this audio file.'),
       endpoint => 'gemini-2.5-pro',
       output_schema => 'topic ARRAY<STRING>, summary STRING'
     ).* EXCEPT (full_response, status), uri
   FROM
     `bqml_tutorial.audio`;

   The results look similar to the following:

   +--------------------------------------------+-----------------------------------------------------------+
   | summary                                    | topic              | uri                                  |
   +--------------------------------------------+-----------------------------------------------------------+
   | The audio contains a distinctive 'beep'    | beep sound         | gs://cloud-samples-data/generativ... |
   | sound, followed by the characteristic      |                    |                                      |
   | sound of a large vehicle or bus backing..  |                    |                                      |
   +--------------------------------------------+--------------------+--------------------------------------+
   |                                            | vehicle backing up |                                      |
   |                                            +--------------------+                                      |
   |                                            | bus                |                                      |
   |                                            +--------------------+                                      |
   |                                            | alarm              |                                      |
   +--------------------------------------------+--------------------+--------------------------------------+
   | The speaker introduces themselves          | Introduction       | gs://cloud-samples-data/generativ... |
   | as Gemini and expresses their excitement   |                    |                                      |
   | and readiness to dive into something..     |                    |                                      |
   +--------------------------------------------+--------------------+--------------------------------------+
   |                                            | Readiness          |                                      |
   |                                            +--------------------+                                      |
   |                                            | Excitement         |                                      |
   |                                            +--------------------+                                      |
   |                                            | Collaboration      |                                      |
   +--------------------------------------------+--------------------+--------------------------------------+
   | ...                                        | ...                | ...                                  |
   +--------------------------------------------+--------------------+--------------------------------------+
   

Console

1. Go to the BigQuery page.

   Go to BigQuery

2. Delete the bqml_tutorial dataset. Deleting the dataset also deletes the remote model.

   1. In the Explorer pane, expand your project and click Datasets.

   2. In the Datasets list, click the bqml_tutorial dataset.

   3. In the details pane, click delete Delete.

   4. In the Delete dataset dialog, click Delete.

3. Delete the connection:

   1. In the Explorer pane, expand your project and click Connections.

   2. In the Connection ID list, click the connection that you created.

   3. In the details pane, click delete Delete.

   4. In the Delete connection dialog, enter delete to confirm deletion.

   5. Click Delete.

gcloud

1. Delete the bqml_tutorial dataset and the remote model:

   bq rm --dataset --recursive bqml_tutorial
   

2. Delete the connection.

   bq rm --connection PROJECT_ID.LOCATION.CONNECTION_ID
   

   Replace the following:

   • PROJECT_ID: your Google Cloud project ID
   • LOCATION: the connection's location
   • CONNECTION_ID: the connection ID