Generate text by using the ML.GENERATE_TEXT function

Console

1. In the Google Cloud console, go to the BigQuerypage.

   Go to the BigQuery page

2. In the Explorerpane, click your project name.

3. Click more_vert View actions > Create dataset.

4. On the Create datasetpage, do the following:

   • For Dataset ID, type a name for the dataset.

   • For Location type, select a location for the dataset.

   • Click Create dataset.

bq

1. To create a new dataset, use the bq mk command with the --location flag:

   bq --location= LOCATION 
   mk -d DATASET_ID 
   

   Replace the following:

   • LOCATION : the dataset's location .
   • DATASET_ID is the ID of the dataset that you're creating.

2. Confirm that the dataset was created:

   bq  
   ls

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 Business Applications.

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

4. In the Featured data sourcessection, click Vertex AI.

5. Click the Vertex AI Models: BigQuery Federationsolution card.

6. In the Connection typelist, select Vertex AI remote models, remote functions and BigLake (Cloud Resource).

7. In the Connection IDfield, enter a name for your connection.

8. Click Create connection.

9. Click Go to connection.

10. In the Connection infopane, 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"}

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. Go to the IAM & Adminpage.

   Go to IAM & Admin

2. Click person_add Add.

   The Add principalsdialog opens.

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

4. In the Select a rolefield, select Vertex AI, and then select Vertex AI User.

5. Click Save.

gcloud

Use the gcloud projects add-iam-policy-binding command .

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

Replace the following:

• PROJECT_NUMBER : your project number
• MEMBER : the service account ID that you copied earlier

Console

1. Go to the IAM & Adminpage.

   Go to IAM & Admin

2. Click person_add Add.

   The Add principalsdialog opens.

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

4. In the Select a rolefield, select Vertex AI, and then select Vertex AI User.

5. Click Save.

gcloud

Use the gcloud projects add-iam-policy-binding command .

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

Replace the following:

• PROJECT_NUMBER : your project number
• MEMBER : the service account ID that you copied earlier

Open models

1. In the Google Cloud console, go to the BigQuerypage.

   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 
     
    { 
    DEFAULT 
     
    | 
     
    ` PROJECT_ID 
   . REGION 
   . CONNECTION_ID 
   ` 
    } 
    OPTIONS 
     
    ( 
    ENDPOINT 
     
    = 
     
    'https:// ENDPOINT_REGION 
   -aiplatform.googleapis.com/v1/projects/ ENDPOINT_PROJECT_ID 
   /locations/ ENDPOINT_REGION 
   /endpoints/ ENDPOINT_ID 
   ' 
    ); 
   

   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.

     You can get this value by viewing the connection details in the Google Cloud console and copying 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_REGION : the region in which the open model is deployed.
   • ENDPOINT_PROJECT_ID : the project in which the open model is deployed.
   • ENDPOINT_ID : the ID of the HTTPS endpoint used by the open model. You can get the endpoint ID by locating the open model on the Online prediction page and copying the value in the ID field.

All other models

1. In the Google Cloud console, go to the BigQuerypage.

   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.

     You can get this value by viewing the connection details in the Google Cloud console and copying 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 endpoint of the Vertex AI model to use.

     For pre-trained Vertex AI models, Claude models, and Mistral AI models, specify the name of the model. For some of these models, you can specify a particular version of the model as part of the name. For supported Gemini models, you can specify the global endpoint to improve availability.

     For Llama models, specify an OpenAI API endpoint in the format openapi/<publisher_name>/<model_name> . For example, openapi/meta/llama-3.1-405b-instruct-maas .

     For information about supported model names and versions, see ENDPOINT .

     The Vertex AI model that you specify must be available in the location where you are creating the remote model. For more information, see Locations .

Gemini

 SELECT 
  
 * 
 FROM 
  
 ML 
 . 
 GENERATE_TEXT 
 ( 
  
 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 
 , 
  
  FLATTEN_JSON 
 
  
 AS 
  
 flatten_json_output 
 , 
  
  STOP_SEQUENCES 
 
  
 AS 
  
 stop_sequences 
 , 
  
  GROUND_WITH_GOOGLE_SEARCH 
 
  
 AS 
  
 ground_with_google_search 
 , 
  
  SAFETY_SETTINGS 
 
  
 AS 
  
 safety_settings 
 , 
  
  REQUEST_TYPE 
 
  
 AS 
  
 request_type 
 ) 
 ); 

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 : a query that provides the prompt data. This query must produce a column that's named 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,1.0] that controls the degree of randomness in token selection. The default is 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 .
• FLATTEN_JSON : a BOOL value that determines whether to return the generated text and the safety attributes in separate columns. The default is FALSE .
• 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](/vertex-ai/generative-ai/docs/grounding/overview#ground-public) 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. When both flatten_json_output and this field are set to True , an additional ml_generate_text_grounding_result column is included in the results, providing the sources that the model used to gather additional information. 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, refer to the definition of safety category and blocking threshold .
• REQUEST_TYPE : a STRING value that specifies the type of inference request to send to the Gemini model. The request type determines what quota the request uses. Valid values are as follows:
  • DEDICATED : The ML.GENERATE_TEXT function only uses Provisioned Throughput quota. The ML.GENERATE_TEXT function returns the error Provisioned throughput is not purchased or is not active if Provisioned Throughput quota isn't available.
  • SHARED : The ML.GENERATE_TEXT function only uses dynamic shared quota (DSQ) , even if you have purchased Provisioned Throughput quota.
  • UNSPECIFIED : The ML.GENERATE_TEXT function uses quota as follows:
    • If you haven't purchased Provisioned Throughput quota, the ML.GENERATE_TEXT function uses DSQ quota.
    • If you have purchased Provisioned Throughput quota, the ML.GENERATE_TEXT function uses the Provisioned Throughput quota first. If requests exceed the Provisioned Throughput quota, the overflow traffic uses DSQ quota.

  The default value is UNSPECIFIED .

  For more information, see Use Vertex AI Provisioned Throughput .

  Example 1

  The following example shows a request with these characteristics:

  • Prompts for a summary of the text in the body column of the articles table.
  • Parses the JSON response from the model into separate columns.

   SELECT 
    
   * 
   FROM 
    
   ML 
   . 
   GENERATE_TEXT 
   ( 
    
   MODEL 
    
   `mydataset.text_model` 
   , 
    
   ( 
    
   SELECT 
    
   CONCAT 
   ( 
   'Summarize this text' 
   , 
    
   body 
   ) 
    
   AS 
    
   prompt 
    
   FROM 
    
   mydataset 
   . 
   articles 
    
   ), 
    
   STRUCT 
   ( 
   TRUE 
    
   AS 
    
   flatten_json_output 
   )); 
  

  Example 2

  The following example shows a request with these characteristics:

  • Uses a query to create the prompt data by concatenating strings that provide prompt prefixes with table columns.
  • Returns a short response.
  • Doesn't parse the JSON response from the model into separate columns.

   SELECT 
    
   * 
   FROM 
    
   ML 
   . 
   GENERATE_TEXT 
   ( 
    
   MODEL 
    
   `mydataset.text_model` 
   , 
    
   ( 
    
   SELECT 
    
   CONCAT 
   ( 
   question 
   , 
    
   'Text:' 
   , 
    
   description 
   , 
    
   'Category' 
   ) 
    
   AS 
    
   prompt 
    
   FROM 
    
   mydataset 
   . 
   input_table 
    
   ), 
    
   STRUCT 
   ( 
    
   100 
    
   AS 
    
   max_output_tokens 
   , 
    
   FALSE 
    
   AS 
    
   flatten_json_output 
   )); 
  

  Example 3

  The following example shows a request with these characteristics:

  • Uses the prompt column of the prompts table for the prompt.
  • Parses the JSON response from the model into separate columns.

   SELECT 
    
   * 
   FROM 
    
   ML 
   . 
   GENERATE_TEXT 
   ( 
    
   MODEL 
    
   `mydataset.text_model` 
   , 
    
   TABLE 
    
   mydataset 
   . 
   prompts 
   , 
    
   STRUCT 
   ( 
   TRUE 
    
   AS 
    
   flatten_json_output 
   )); 
  

  Example 4

  The following example shows a request with these characteristics:

  • Uses the prompt column of the prompts table for the prompt.
  • Returns a short response.
  • Flattens the JSON response into separate columns.
  • Retrieves and returns public web data for response grounding.
  • Filters out unsafe responses by using two safety settings.

   SELECT 
    
   * 
   FROM 
    
   ML 
   . 
   GENERATE_TEXT 
   ( 
    
   MODEL 
    
   `mydataset.text_model` 
   , 
    
   TABLE 
    
   mydataset 
   . 
   prompts 
   , 
    
   STRUCT 
   ( 
    
   100 
    
   AS 
    
   max_output_tokens 
   , 
    
   0.5 
    
   AS 
    
   top_p 
   , 
    
   TRUE 
    
   AS 
    
   flatten_json_output 
   , 
    
   TRUE 
    
   AS 
    
   ground_with_google_search 
   , 
    
   [ 
   STRUCT 
   ( 
   'HARM_CATEGORY_HATE_SPEECH' 
    
   AS 
    
   category 
   , 
    
   'BLOCK_LOW_AND_ABOVE' 
    
   AS 
    
   threshold 
   ), 
    
   STRUCT 
   ( 
   'HARM_CATEGORY_DANGEROUS_CONTENT' 
    
   AS 
    
   category 
   , 
    
   'BLOCK_MEDIUM_AND_ABOVE' 
    
   AS 
    
   threshold 
   ) 
   ] 
    
   AS 
    
   safety_settings 
   )); 
  

  Example 5

  The following example shows a request with these characteristics:

  • Uses the prompt column of the prompts table for the prompt.
  • Returns a longer response.
  • Flattens the JSON response into separate columns.

   SELECT 
    
   * 
   FROM 
    
   ML 
   . 
   GENERATE_TEXT 
   ( 
    
   MODEL 
    
   `mydataset.flash_2_model` 
   , 
    
   TABLE 
    
   mydataset 
   . 
   prompts 
   , 
    
   STRUCT 
   ( 
    
   0.4 
    
   AS 
    
   temperature 
   , 
    
   8192 
    
   AS 
    
   max_output_tokens 
   , 
    
   TRUE 
    
   AS 
    
   flatten_json_output 
   )); 
  

  Example 6

  The following example shows a request with these characteristics:

  • Prompts for a summary of the text in the body column of the articles table.
  • Flattens the JSON response into separate columns.
  • Retrieves and returns public web data for response grounding.
  • Filters out unsafe responses by using two safety settings.

   SELECT 
    
   * 
   FROM 
    
   ML 
   . 
   GENERATE_TEXT 
   ( 
    
   MODEL 
    
   `mydataset.text_model` 
   , 
    
   ( 
    
   SELECT 
    
   CONCAT 
   ( 
   'Summarize this text' 
   , 
    
   body 
   ) 
    
   AS 
    
   prompt 
    
   FROM 
    
   mydataset 
   . 
   articles 
    
   ), 
    
   STRUCT 
   ( 
    
   .1 
    
   AS 
    
   TEMPERATURE 
   , 
    
   TRUE 
    
   AS 
    
   flatten_json_output 
   , 
    
   TRUE 
    
   AS 
    
   ground_with_google_search 
   , 
    
   [ 
   STRUCT 
   ( 
   'HARM_CATEGORY_HATE_SPEECH' 
    
   AS 
    
   category 
   , 
    
   'BLOCK_LOW_AND_ABOVE' 
    
   AS 
    
   threshold 
   ), 
    
   STRUCT 
   ( 
   'HARM_CATEGORY_DANGEROUS_CONTENT' 
    
   AS 
    
   category 
   , 
    
   'BLOCK_MEDIUM_AND_ABOVE' 
    
   AS 
    
   threshold 
   ) 
   ] 
    
   AS 
    
   safety_settings 
   )); 
  

Claude

 SELECT 
  
 * 
 FROM 
  
 ML 
 . 
 GENERATE_TEXT 
 ( 
  
 MODEL 
  
 ` PROJECT_ID 
. DATASET_ID 
. MODEL_NAME 
` 
 , 
  
 { 
 TABLE 
  
  PROJECT_ID 
 
 . 
  DATASET_ID 
 
 . 
  TABLE_NAME 
 
  
 | 
  
 ( 
  PROMPT_QUERY 
 
 ) 
 } 
 , 
  
 STRUCT 
 ( 
  TOKENS 
 
  
 AS 
  
 max_output_tokens 
 , 
  
  TOP_K 
 
  
 AS 
  
 top_k 
 , 
  
  TOP_P 
 
  
 AS 
  
 top_p 
 , 
  
  FLATTEN_JSON 
 
  
 AS 
  
 flatten_json_output 
 ) 
 ); 

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 : a query that provides the prompt data. This query must produce a column that's named 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,4096] . Specify a lower value for shorter responses and a higher value for longer responses. The default is 128 .
• TOP_K : an INT64 value in the range [1,40] that determines the initial pool of tokens the model considers for selection. Specify a lower value for less random responses and a higher value for more random responses. If you don't specify a value, the model determines an appropriate value.
• 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. If you don't specify a value, the model determines an appropriate value.
• FLATTEN_JSON : a BOOL value that determines whether to return the generated text and the safety attributes in separate columns. The default is FALSE .

Example 1

The following example shows a request with these characteristics:

• Prompts for a summary of the text in the body column of the articles table.
• Parses the JSON response from the model into separate columns.

 SELECT 
  
 * 
 FROM 
  
 ML 
 . 
 GENERATE_TEXT 
 ( 
  
 MODEL 
  
 `mydataset.text_model` 
 , 
  
 ( 
  
 SELECT 
  
 CONCAT 
 ( 
 'Summarize this text' 
 , 
  
 body 
 ) 
  
 AS 
  
 prompt 
  
 FROM 
  
 mydataset 
 . 
 articles 
  
 ), 
  
 STRUCT 
 ( 
 TRUE 
  
 AS 
  
 flatten_json_output 
 )); 

Example 2

The following example shows a request with these characteristics:

• Uses a query to create the prompt data by concatenating strings that provide prompt prefixes with table columns.
• Returns a short response.
• Doesn't parse the JSON response from the model into separate columns.

 SELECT 
  
 * 
 FROM 
  
 ML 
 . 
 GENERATE_TEXT 
 ( 
  
 MODEL 
  
 `mydataset.text_model` 
 , 
  
 ( 
  
 SELECT 
  
 CONCAT 
 ( 
 question 
 , 
  
 'Text:' 
 , 
  
 description 
 , 
  
 'Category' 
 ) 
  
 AS 
  
 prompt 
  
 FROM 
  
 mydataset 
 . 
 input_table 
  
 ), 
  
 STRUCT 
 ( 
  
 100 
  
 AS 
  
 max_output_tokens 
 , 
  
 FALSE 
  
 AS 
  
 flatten_json_output 
 )); 

Example 3

The following example shows a request with these characteristics:

• Uses the prompt column of the prompts table for the prompt.
• Parses the JSON response from the model into separate columns.

 SELECT 
  
 * 
 FROM 
  
 ML 
 . 
 GENERATE_TEXT 
 ( 
  
 MODEL 
  
 `mydataset.text_model` 
 , 
  
 TABLE 
  
 mydataset 
 . 
 prompts 
 , 
  
 STRUCT 
 ( 
 TRUE 
  
 AS 
  
 flatten_json_output 
 )); 

Llama

 SELECT 
  
 * 
 FROM 
  
 ML 
 . 
 GENERATE_TEXT 
 ( 
  
 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 
 , 
  
  FLATTEN_JSON 
 
  
 AS 
  
 flatten_json_output 
 , 
  
  STOP_SEQUENCES 
 
  
 AS 
  
 stop_sequences 
 ) 
 ); 

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 : a query that provides the prompt data. This query must produce a column that's named 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,4096] . 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,1.0] that controls the degree of randomness in token selection. The default is 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 .
• FLATTEN_JSON : a BOOL value that determines whether to return the generated text and the safety attributes in separate columns. The default is FALSE .
• 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.

Example 1

The following example shows a request with these characteristics:

• Prompts for a summary of the text in the body column of the articles table.
• Parses the JSON response from the model into separate columns.

 SELECT 
  
 * 
 FROM 
  
 ML 
 . 
 GENERATE_TEXT 
 ( 
  
 MODEL 
  
 `mydataset.text_model` 
 , 
  
 ( 
  
 SELECT 
  
 CONCAT 
 ( 
 'Summarize this text' 
 , 
  
 body 
 ) 
  
 AS 
  
 prompt 
  
 FROM 
  
 mydataset 
 . 
 articles 
  
 ), 
  
 STRUCT 
 ( 
 TRUE 
  
 AS 
  
 flatten_json_output 
 )); 

Example 2

The following example shows a request with these characteristics:

• Uses a query to create the prompt data by concatenating strings that provide prompt prefixes with table columns.
• Returns a short response.
• Doesn't parse the JSON response from the model into separate columns.

 SELECT 
  
 * 
 FROM 
  
 ML 
 . 
 GENERATE_TEXT 
 ( 
  
 MODEL 
  
 `mydataset.text_model` 
 , 
  
 ( 
  
 SELECT 
  
 CONCAT 
 ( 
 question 
 , 
  
 'Text:' 
 , 
  
 description 
 , 
  
 'Category' 
 ) 
  
 AS 
  
 prompt 
  
 FROM 
  
 mydataset 
 . 
 input_table 
  
 ), 
  
 STRUCT 
 ( 
  
 100 
  
 AS 
  
 max_output_tokens 
 , 
  
 FALSE 
  
 AS 
  
 flatten_json_output 
 )); 

Example 3

The following example shows a request with these characteristics:

• Uses the prompt column of the prompts table for the prompt.
• Parses the JSON response from the model into separate columns.

 SELECT 
  
 * 
 FROM 
  
 ML 
 . 
 GENERATE_TEXT 
 ( 
  
 MODEL 
  
 `mydataset.text_model` 
 , 
  
 TABLE 
  
 mydataset 
 . 
 prompts 
 , 
  
 STRUCT 
 ( 
 TRUE 
  
 AS 
  
 flatten_json_output 
 )); 

Mistral AI

 SELECT 
  
 * 
 FROM 
  
 ML 
 . 
 GENERATE_TEXT 
 ( 
  
 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 
 , 
  
  FLATTEN_JSON 
 
  
 AS 
  
 flatten_json_output 
 , 
  
  STOP_SEQUENCES 
 
  
 AS 
  
 stop_sequences 
 ) 
 ); 

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 : a query that provides the prompt data. This query must produce a column that's named 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,4096] . 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,1.0] that controls the degree of randomness in token selection. The default is 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 .
• FLATTEN_JSON : a BOOL value that determines whether to return the generated text and the safety attributes in separate columns. The default is FALSE .
• 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.

Example 1

The following example shows a request with these characteristics:

• Prompts for a summary of the text in the body column of the articles table.
• Parses the JSON response from the model into separate columns.

 SELECT 
  
 * 
 FROM 
  
 ML 
 . 
 GENERATE_TEXT 
 ( 
  
 MODEL 
  
 `mydataset.text_model` 
 , 
  
 ( 
  
 SELECT 
  
 CONCAT 
 ( 
 'Summarize this text' 
 , 
  
 body 
 ) 
  
 AS 
  
 prompt 
  
 FROM 
  
 mydataset 
 . 
 articles 
  
 ), 
  
 STRUCT 
 ( 
 TRUE 
  
 AS 
  
 flatten_json_output 
 )); 

Example 2

The following example shows a request with these characteristics:

• Uses a query to create the prompt data by concatenating strings that provide prompt prefixes with table columns.
• Returns a short response.
• Doesn't parse the JSON response from the model into separate columns.

 SELECT 
  
 * 
 FROM 
  
 ML 
 . 
 GENERATE_TEXT 
 ( 
  
 MODEL 
  
 `mydataset.text_model` 
 , 
  
 ( 
  
 SELECT 
  
 CONCAT 
 ( 
 question 
 , 
  
 'Text:' 
 , 
  
 description 
 , 
  
 'Category' 
 ) 
  
 AS 
  
 prompt 
  
 FROM 
  
 mydataset 
 . 
 input_table 
  
 ), 
  
 STRUCT 
 ( 
  
 100 
  
 AS 
  
 max_output_tokens 
 , 
  
 FALSE 
  
 AS 
  
 flatten_json_output 
 )); 

Example 3

The following example shows a request with these characteristics:

• Uses the prompt column of the prompts table for the prompt.
• Parses the JSON response from the model into separate columns.

 SELECT 
  
 * 
 FROM 
  
 ML 
 . 
 GENERATE_TEXT 
 ( 
  
 MODEL 
  
 `mydataset.text_model` 
 , 
  
 TABLE 
  
 mydataset 
 . 
 prompts 
 , 
  
 STRUCT 
 ( 
 TRUE 
  
 AS 
  
 flatten_json_output 
 )); 

Open models

 SELECT 
  
 * 
 FROM 
  
 ML 
 . 
 GENERATE_TEXT 
 ( 
  
 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_K 
 
  
 AS 
  
 top_k 
 , 
  
  TOP_P 
 
  
 AS 
  
 top_p 
 , 
  
  FLATTEN_JSON 
 
  
 AS 
  
 flatten_json_output 
 ) 
 ); 

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 : a query that provides the prompt data. This query must produce a column that's named 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,4096] . Specify a lower value for shorter responses and a higher value for longer responses. If you don't specify a value, the model determines an appropriate value.
• TEMPERATURE : a FLOAT64 value in the range [0.0,1.0] that controls the degree of randomness in token selection. If you don't specify a value, the model determines an appropriate value.

  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_K : an INT64 value in the range [1,40] that determines the initial pool of tokens the model considers for selection. Specify a lower value for less random responses and a higher value for more random responses. If you don't specify a value, the model determines an appropriate value.
• 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. If you don't specify a value, the model determines an appropriate value.
• FLATTEN_JSON : a BOOL value that determines whether to return the generated text and the safety attributes in separate columns. The default is FALSE .

Example 1

The following example shows a request with these characteristics:

• Prompts for a summary of the text in the body column of the articles table.
• Parses the JSON response from the model into separate columns.

 SELECT 
  
 * 
 FROM 
  
 ML 
 . 
 GENERATE_TEXT 
 ( 
  
 MODEL 
  
 `mydataset.text_model` 
 , 
  
 ( 
  
 SELECT 
  
 CONCAT 
 ( 
 'Summarize this text' 
 , 
  
 body 
 ) 
  
 AS 
  
 prompt 
  
 FROM 
  
 mydataset 
 . 
 articles 
  
 ), 
  
 STRUCT 
 ( 
 TRUE 
  
 AS 
  
 flatten_json_output 
 )); 

Example 2

The following example shows a request with these characteristics:

• Uses a query to create the prompt data by concatenating strings that provide prompt prefixes with table columns.
• Returns a short response.
• Doesn't parse the JSON response from the model into separate columns.

 SELECT 
  
 * 
 FROM 
  
 ML 
 . 
 GENERATE_TEXT 
 ( 
  
 MODEL 
  
 `mydataset.text_model` 
 , 
  
 ( 
  
 SELECT 
  
 CONCAT 
 ( 
 question 
 , 
  
 'Text:' 
 , 
  
 description 
 , 
  
 'Category' 
 ) 
  
 AS 
  
 prompt 
  
 FROM 
  
 mydataset 
 . 
 input_table 
  
 ), 
  
 STRUCT 
 ( 
  
 100 
  
 AS 
  
 max_output_tokens 
 , 
  
 FALSE 
  
 AS 
  
 flatten_json_output 
 )); 

Example 3

The following example shows a request with these characteristics:

• Uses the prompt column of the prompts table for the prompt.
• Parses the JSON response from the model into separate columns.

 SELECT 
  
 * 
 FROM 
  
 ML 
 . 
 GENERATE_TEXT 
 ( 
  
 MODEL 
  
 `mydataset.text_model` 
 , 
  
 TABLE 
  
 mydataset 
 . 
 prompts 
 , 
  
 STRUCT 
 ( 
 TRUE 
  
 AS 
  
 flatten_json_output 
 ));