Generate text with the AI.GENERATE function

This tutorial shows you how to generate text from text or multimodal databy using theAI.GENERATE function.With theAI.GENERATE function, you use aCloud resource connectionto connect to a hosted Gemini model so that you don't have tocreate and maintain a model of your own.

This tutorial shows you how to complete the following tasks:

• Summarize text content and output results in the function's defaultformat.
• Summarize text content and output structured results.
• Transcribe and translate video content.
• Analyze audio file content.

Costs

In this document, you use the following billable components of Google Cloud:

• BigQuery ML: You incur costs for the data that you process in BigQuery.
• Vertex AI: You incur costs for calls to the Vertex AI model.

To generate a cost estimate based on your projected usage, use thepricing calculator.

For more information about BigQuery pricing, seeBigQuery pricing inthe BigQuery documentation.

For more information about Vertex AI generative AI pricing,see theVertex AI pricingpage.

Before you begin

Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains theresourcemanager.projects.create permission.Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains theresourcemanager.projects.create permission.Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

1. BigQuery is automatically enabled in new projects. To activate BigQuery in a pre-existing project, go to

   Enable the BigQuery API.

   Roles required to enable APIs

   To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains theserviceusage.services.enable permission.Learn how to grant roles.

   Enable the API

Required roles

To use theAI.GENERATE function, you need thefollowing Identity and Access Management (IAM) roles:

• Create and use BigQuery datasets and tables:BigQuery Data Editor (roles/bigquery.dataEditor) on your project.
• Create, delegate, and use BigQuery connections:BigQuery Connections Admin (roles/bigquery.connectionsAdmin) on yourproject.
• Grant permissions to the connection's service account: Project IAM Admin(roles/resourcemanager.projectIamAdmin) on the project that contains theVertex AI endpoint.
• Create BigQuery jobs: BigQuery Job User(roles/bigquery.jobUser) on your project.

These predefined roles contain the permissions required to perform the tasks inthis document. To see the exact permissions that are required, expand theRequired permissions section:

You might also be able to get these permissions withcustom roles or otherpredefined roles.

Create a dataset

Create a BigQuery dataset to store your ML model.

{"datasetReference":{"datasetId":"bqml_tutorial"}}

importgoogle.cloud.bigquerybqclient=google.cloud.bigquery.Client()bqclient.create_dataset("bqml_tutorial",exists_ok=True)

Create a connection

Create aCloud resource connectionand get the connection's service account. Create the connection in the samelocation as the dataset that you created in theprevious step.

Follow these steps to create a connection:

1. Go to theBigQuery page.

   Go to BigQuery

2. In theExplorer pane, clickaddAdd data:

   

   TheAdd data dialog opens.

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

   Alternatively, in theSearch for data sources field, you can enterVertex AI.

4. In theFeatured data sources section, clickVertex AI.

5. Click theVertex AI Models: BigQuery Federation solution card.

6. In theConnection type list, selectVertex AI remote models, remote functions, BigLake and Spanner (Cloud Resource).

7. In theConnection ID field, typetest_connection.

8. ClickCreate connection.

9. ClickGo to connection.

10. In theConnection info pane, copy the service account ID for use in thenext step.

Give the service account access

Grant the connection's service account the Vertex AI User role.

To grant the role, follow these steps:

1. Go to theIAM & Admin page.

   Go to IAM & Admin

2. Clickperson_addAdd.

   TheAdd principals dialog opens.

3. In theNew principals field, enter the service account ID that youcopied earlier.

4. In theSelect a role field, selectVertex AI, and then selectVertex AI User.

5. ClickAdd another role.

6. In theSelect a role field, chooseCloud Storage, and thenselectStorage Object Viewer.

7. ClickSave.

Summarize text and use the default output format

Follow these steps to generate text using theAI.GENERATE function,and output the results in theAI.GENERATE function's default format:

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

   Go to BigQuery

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

   WITHbbc_newsAS(SELECTbodyFROM`bigquery-public-data.bbc_news.fulltext`LIMIT5)SELECTAI.GENERATE(body,connection_id=>'us.test_connection')ASnewsFROMbbc_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 theAI.GENERATE function, and usetheAI.GENERATE function'soutput_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:

   WITHbbc_newsAS(SELECTbodyFROM`bigquery-public-data`.bbc_news.fulltextLIMIT5)SELECTnews.good_sentiment,news.summaryFROMbbc_news,UNNEST(ARRAY[AI.GENERATE(body,connection_id=>'us.test',output_schema=>'summary STRING, good_sentiment BOOL')])ASnews;

   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..       |+----------------+--------------------------------------------+| true           | A review of sports games, focusing on the  ||                | rivalry between EA Sports and ESPN, and    ||                | the recent deal where EA acquired the..    |+----------------+--------------------------------------------+| ...            | ...                                        |+----------------+--------------------------------------------+

Transcribe and translate video content

Follow these steps to create an object table over public video content, and thentranscribe and translate a video:

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:

   CREATEORREPLACEEXTERNALTABLE`bqml_tutorial.video`WITHCONNECTION`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 translatethepixel8.mp4file:

   SELECTAI.GENERATE((OBJ.GET_ACCESS_URL(ref,'r'),'Transcribe the video in Japanese and then translate to English.'),connection_id=>'us.test_connection',endpoint=>'gemini-2.5-flash',output_schema=>'japanese_transcript STRING, english_translation STRING').*EXCEPT(full_response,status)FROM`bqml_tutorial.video`WHEREREGEXP_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 thenanalyze 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:

   CREATEORREPLACEEXTERNALTABLE`bqml_tutorial.audio`WITHCONNECTION`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:

   SELECTAI.GENERATE((OBJ.GET_ACCESS_URL(ref,'r'),'Summarize the content of this audio file.'),connection_id=>'us.test_connection',endpoint=>'gemini-2.5-flash',output_schema=>'topic ARRAY<STRING>, summary STRING').*EXCEPT(full_response,status),uriFROM`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      |                                      |+--------------------------------------------+--------------------+--------------------------------------+| ...                                        | ...                | ...                                  |+--------------------------------------------+--------------------+--------------------------------------+

Clean up

Caution: Deleting a project has the following effects:
• Everything in the project is deleted. If you used an existing project for the tasks in this document, when you delete it, you also delete any other work you've done in the project.
• Custom project IDs are lost. When you created this project, you might have created a custom project ID that you want to use in the future. To preserve the URLs that use the project ID, such as anappspot.com URL, delete selected resources inside the project instead of deleting the whole project.

If you plan to explore multiple architectures, tutorials, or quickstarts, reusing projects can help you avoid exceeding project quota limits.

1. In the Google Cloud console, go to theManage resources page.

   Go to Manage resources

2. In the project list, select the project that you want to delete, and then clickDelete.
3. In the dialog, type the project ID, and then clickShut down to delete the project.