Generate structured data by using the AI.GENERATE_TABLE function

This document shows you how to generate structured data using a Gemini Pro 1.5, Gemini Flash 1.5, or Gemini Flash 2.0 model, and then format the model's response using a SQL schema.

You do this by completing the following tasks:

• Creating a BigQuery ML remote model that represents a hosted Vertex AI Gemini 1.5 or 2.0 model.
• Using the model with the AI.GENERATE_TABLE function to generate structured data based on data from standard tables.

Required permissions

• To create a connection, you need membership in the following Identity and Access Management (IAM) role:

  • roles/bigquery.connectionAdmin

• To grant permissions to the connection's service account, you need the following permission:

  • resourcemanager.projects.setIamPolicy

• To create the model using BigQuery ML, you need the following IAM permissions:

  • bigquery.jobs.create
  • bigquery.models.create
  • bigquery.models.getData
  • bigquery.models.updateData
  • bigquery.models.updateMetadata

• To run inference, you need the following permissions:

  • bigquery.tables.getData on the table
  • bigquery.models.getData on the model
  • bigquery.jobs.create

Before you begin

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

   Go to project selector

2. Make sure that billing is enabled for your Google Cloud project.

3. Enable the BigQuery, BigQuery Connection, and Vertex AI APIs.

   Enable the APIs

Create a connection

Create a Cloud resource connection and get the connection's service account.

Select one of the following options:

# 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 Vertex AI User role.

If you plan to specify the endpoint as a URL when you create the remote model, for example endpoint = 'https://us-central1-aiplatform.googleapis.com/v1/projects/myproject/locations/us-central1/publishers/google/models/gemini-1.5-flash', grant this role in the same project you specify in the URL.

If you plan to specify the endpoint by using the model name when you create the remote model, for example endpoint = 'gemini-1.5-flash', grant this role in the same project where you plan to create the remote model.

Granting the role in a different project results in the error bqcx-1234567890-xxxx@gcp-sa-bigquery-condel.iam.gserviceaccount.com does not have the permission to access resource.

To grant the role, follow these steps:

gcloud projects add-iam-policy-binding 'PROJECT_NUMBER' --member='serviceAccount:MEMBER' --role='roles/aiplatform.user' --condition=None

Create a BigQuery ML remote model

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

   Go to BigQuery

2. Using the SQL editor, create a remote model:

   CREATE OR REPLACE MODEL
   `PROJECT_ID.DATASET_ID.MODEL_NAME`
   REMOTE WITH CONNECTION `PROJECT_ID.REGION.CONNECTION_ID`
   OPTIONS (ENDPOINT = 'ENDPOINT');

   Replace the following:

   • PROJECT_ID: your project ID
   • DATASET_ID: the ID of the dataset to contain the model. This dataset must be in the same location as the connection that you are using
   • MODEL_NAME: the name of the model
   • REGION: the region used by the connection
   • CONNECTION_ID: the ID of your BigQuery connection

     When you view the connection details in the Google Cloud console, this is the value in the last section of the fully qualified connection ID that is shown in Connection ID, for example projects/myproject/locations/connection_location/connections/myconnection

   • ENDPOINT: the name of the Gemini model to use. The following models are supported:
     • gemini-2.0-flash-001
     • gemini-1.5-flash-001
     • gemini-1.5-flash-002
     • gemini-1.5-pro-001
     • gemini-1.5-pro-002
     For more information, see ENDPOINT.

Generate structured data

Generate structured data by using the AI.GENERATE_TABLE function with a remote model, and using prompt data from a table column:

SELECT *
FROM AI.GENERATE_TABLE(
  MODEL `PROJECT_ID.DATASET_ID.MODEL_NAME`,
  [TABLE `PROJECT_ID.DATASET_ID.TABLE_NAME` / (PROMPT_QUERY)],
  STRUCT(TOKENS AS max_output_tokens, TEMPERATURE AS temperature,
  TOP_P AS top_p, STOP_SEQUENCES AS stop_sequences,
  GROUND_WITH_GOOGLE_SEARCH AS ground_with_google_search,
  SAFETY_SETTINGS AS safety_settings,
  OUTPUT_SCHEMA AS output_schema)
);

Replace the following:

• PROJECT_ID: your project ID.
• DATASET_ID: the ID of the dataset that contains the model.
• MODEL_NAME: the name of the model.
• TABLE_NAME: the name of the table that contains the prompt. This table must have a column that's named prompt, or you can use an alias to use a differently named column.
• PROMPT_QUERY: the GoogleSQL query that generates the prompt data. The prompt value itself can be pulled from a column, or you can specify it as a struct value with an arbitrary number of string and column name subfields. For example, SELECT ('Analyze the sentiment in ', feedback_column, 'using the following categories: positive, negative, neutral') AS prompt.
• TOKENS: an INT64 value that sets the maximum number of tokens that can be generated in the response. This value must be in the range [1,8192]. Specify a lower value for shorter responses and a higher value for longer responses. The default is 128.
• TEMPERATURE: a FLOAT64 value in the range [0.0,2.0] that controls the degree of randomness in token selection. The default is 1.0.

  Lower values for temperature are good for prompts that require a more deterministic and less open-ended or creative response, while higher values for temperature can lead to more diverse or creative results. A value of 0 for temperature is deterministic, meaning that the highest probability response is always selected.

• TOP_P: a FLOAT64 value in the range [0.0,1.0] helps determine the probability of the tokens selected. Specify a lower value for less random responses and a higher value for more random responses. The default is 0.95.
• STOP_SEQUENCES: an ARRAY<STRING> value that removes the specified strings if they are included in responses from the model. Strings are matched exactly, including capitalization. The default is an empty array.
• GROUND_WITH_GOOGLE_SEARCH: a BOOL value that determines whether the Vertex AI model uses Grounding with Google Search when generating responses. Grounding lets the model use additional information from the internet when generating a response, in order to make model responses more specific and factual. The default is FALSE.
• SAFETY_SETTINGS: an ARRAY<STRUCT<STRING AS category, STRING AS threshold>> value that configures content safety thresholds to filter responses. The first element in the struct specifies a harm category, and the second element in the struct specifies a corresponding blocking threshold. The model filters out content that violate these settings. You can only specify each category once. For example, you can't specify both STRUCT('HARM_CATEGORY_DANGEROUS_CONTENT' AS category, 'BLOCK_MEDIUM_AND_ABOVE' AS threshold) and STRUCT('HARM_CATEGORY_DANGEROUS_CONTENT' AS category, 'BLOCK_ONLY_HIGH' AS threshold). If there is no safety setting for a given category, the BLOCK_MEDIUM_AND_ABOVE safety setting is used.

  Supported categories are as follows:

  • HARM_CATEGORY_HATE_SPEECH
  • HARM_CATEGORY_DANGEROUS_CONTENT
  • HARM_CATEGORY_HARASSMENT
  • HARM_CATEGORY_SEXUALLY_EXPLICIT

  Supported thresholds are as follows:

  • BLOCK_NONE (Restricted)
  • BLOCK_LOW_AND_ABOVE
  • BLOCK_MEDIUM_AND_ABOVE (Default)
  • BLOCK_ONLY_HIGH
  • HARM_BLOCK_THRESHOLD_UNSPECIFIED

  For more information, see Harm categories and How to configure content filters.

• OUTPUT_SCHEMA: a STRING value that specifies the format for the model's response. The output_schema value must be a SQL schema definition, similar to that used in the CREATE TABLE statement. The following data types are supported:
  • INT64
  • FLOAT64
  • BOOL
  • STRING
  • ARRAY
  • STRUCT

  When using the output_schema argument to generate structured data based on prompts from a table, it is important to understand the prompt data in order to specify an appropriate schema.

  For example, say you are analyzing movie review content from a table that has the following fields:

  • movie_id
  • review
  • prompt

  Then you might create prompt text by running a query similar to the following:

  UPDATE mydataset.movie_review
  SET prompt = CONCAT('Extract the key words and key sentiment from the text below: ', review)
  WHERE review IS NOT NULL;

  And you might specify a output_schema value similar to "keywords ARRAY<STRING>, sentiment STRING" AS output_schema.

Examples

The following example shows a request that takes prompt data from a table and provides a SQL schema to format the model's response:

SELECT
*
FROM
AI.GENERATE_TABLE( MODEL `mydataset.gemini_model`,
  TABLE `mydataset.mytable`,
  STRUCT("keywords ARRAY<STRING>, sentiment STRING" AS output_schema));

The following example shows a request that takes prompt data from a query and provides a SQL schema to format the model's response:

SELECT *
FROM
  AI.GENERATE_TABLE(
    MODEL `mydataset.gemini_model`,
    (
      SELECT
        'John Smith is a 20-year old single man living at 1234 NW 45th St, Kirkland WA, 98033. He has two phone numbers 123-123-1234, and 234-234-2345. He is 200.5 pounds.'
          AS prompt
    ),
    STRUCT("address STRUCT<street_address STRING, city STRING, state STRING, zip_code STRING>, age INT64, is_married BOOL, name STRING, phone_number ARRAY<STRING>, weight_in_pounds FLOAT64"
        AS output_schema));