Console
    Start free

    The AI.SCORE function

    This document describes the AI.SCORE function, which uses a Vertex AI Gemini model to rate inputs based on a scoring system that you describe and returns a FLOAT64 value. BigQuery rewrites your input prompt to generate a scoring rubric that can improve the consistency and quality of the results.

    The AI.SCORE function is commonly used with the ORDER BY clause and works well when you want to rank items. The following are common use cases:

    • Retail: Find the top 5 most negative customer reviews about a product.
    • Hiring: Find the top 10 resumes that appear most qualified for a job post.
    • Customer success: Find the top 20 best customer support interactions.

    Input

    AI.SCORE accepts the following types of input:

    When you analyze unstructured data, that data must meet the following requirements:

    This function passes your input to a Gemini model and incurs charges in Vertex AI each time it's called. For information about how to view these charges, see Track costs .

    Syntax

     AI 
 . 
 SCORE 
 ( 
  
 [ 
  
 prompt 
  
 => 
  
 ] 
  
 ' PROMPT 
' 
  
 [ 
 , 
  
 connection_id 
  
 => 
  
 ' CONNECTION 
' 
  
 ] 
  
 [ 
 , 
  
 endpoint 
  
 => 
  
 ' ENDPOINT 
' 
  
 ] 
 )

    Arguments

    AI.SCORE takes the following arguments:

    • PROMPT : a STRING or STRUCT value that specifies the PROMPT value to send to the model. The prompt must be the first argument that you specify. You can provide the value in the following ways:
      • Specify a STRING value. For example, 'This is a prompt.'
      • Specify a STRUCT value that contains one or more fields. You can use the following types of fields within the STRUCT value:
        Field type Description Examples
        STRING
        or
        ARRAY<STRING>
        		A string literal, array of string literals, or the name of a STRING column. String literal:
        'This is a prompt.'

        String column name:
        my_string_column
        ObjectRefRuntime
        or
        ARRAY<ObjectRefRuntime>

        An ObjectRefRuntime value returned by the OBJ.GET_ACCESS_URL function . The OBJ.GET_ACCESS_URL function takes an ObjectRef value as input, which you can provide by either specifying the name of a column that contains ObjectRef values, or by constructing an ObjectRef value.

        ObjectRefRuntime values must have the access_url.read_url and details.gcs_metadata.content_type elements of the JSON value populated.

        Your input can contain at most one video object.

        		 Function call with ObjectRef column:
        OBJ.GET_ACCESS_URL(my_objectref_column, 'r')

        Function call with constructed ObjectRef value:
        OBJ.GET_ACCESS_URL(OBJ.MAKE_REF('gs://image.jpg', 'myconnection'), 'r')
        The function combines STRUCT fields similarly to a CONCAT operation and concatenates the fields in their specified order. The same is true for the elements of any arrays used within the struct. The following table shows some examples of STRUCT prompt values and how they are interpreted:
        Struct field types Struct value Semantic equivalent
        STRUCT<STRING, STRING, STRING>
        		('Rate this review ', review_column, ' from 1 to 10') 'Rate this review review_column from 1 to 10'
        STRUCT<STRING, ObjectRefRuntime>
        		('Rate the following city', OBJ.GET_ACCESS_URL(image_objectref_column, 'r')) 'Rate the following city image '

    • CONNECTION : a STRING value specifying the connection to use to communicate with the model, in the format [ PROJECT_ID ]. LOCATION . CONNECTION_ID . For example, myproject.us.myconnection .

      If you don't specify a connection, then the query uses your end-user credentials .

      For information about configuring permissions, see Set permissions for BigQuery ML generative AI functions that call Vertex AI models .

    • ENDPOINT : a STRING value that specifies the Vertex AI endpoint to use for the model. You can specify any generally available or preview Gemini model. If you specify the model name, BigQuery ML automatically identifies and uses the full endpoint of the model. If you don't specify an ENDPOINT value, BigQuery ML dynamically chooses a model based on your query to have the best cost to quality tradeoff for the task. You can also specify the global endpoint . For example, to use gemini-3-pro-preview , specify the following endpoint:

       https:// aiplatform.googleapis.com/v1/projects/ PROJECT_ID 
/locations/ global/publishers/google/models/gemini-3-pro-preview

    Output

    AI.SCORE returns a FLOAT64 indicating the score assigned to the input. There is no fixed default range for the score. For best results, provide a scoring range in your prompt.

    If the call to Vertex AI is unsuccessful for any reason, such as exceeding quota or model unavailability, then the function returns NULL .

    Examples

    The following examples show how to use the AI.SCORE function to assign ratings.

    Rate reviews

    The following query uses the AI.SCORE function to assign ratings based on movie reviews of 'The English Patient', alongside the ratings that the human reviewers gave. It returns the top 10 highest AI rated reviews.

      SELECT 
  
 AI 
 . 
 SCORE 
 (( 
  
 " 
 "" 
 On a scale from 1 to 10, rate how much the reviewer liked the movie. 
 Review: 
  
 "" 
 " 
 , 
  
 review 
 )) 
  
 AS 
  
 ai_rating 
 , 
  
 reviewer_rating 
  
 AS 
  
 human_rating 
 , 
  
 review 
 FROM 
  
 `bigquery-public-data.imdb.reviews` 
 WHERE 
  
 title 
  
 = 
  
 'The English Patient' 
 ORDER 
  
 BY 
  
 ai_rating 
  
 DESC 
 LIMIT 
  
 10 
 ;

    The result is similar to the following:

     +-----------+--------------+----------------------------------------------------+
| ai_rating | human_rating | review                                             |
+-----------+--------------+----------------------------------------------------+
| 10.0      | 10           | Even after all these years, this remain "a perfect |
|           |              | movie" for me. I still remember sitting for a ...  |
| ...       | ...          |                                                    |
+-----------+--------------+----------------------------------------------------+

    Rate and filter reviews

    The following query builds on the previous example by using the AI.IF function to filter the results to reviews that mention at least one of the film's main characters:

      SELECT 
  
 AI 
 . 
 SCORE 
 (( 
  
 " 
 "" 
 On a scale from 1 to 10, rate how much the reviewer liked the movie. 
 Review: 
  
 "" 
 " 
 , 
  
 review 
 )) 
  
 AS 
  
 ai_rating 
 , 
  
 reviewer_rating 
  
 AS 
  
 human_rating 
 , 
  
 review 
 FROM 
  
 `bigquery-public-data.imdb.reviews` 
 WHERE 
  
 title 
  
 = 
  
 'The English Patient' 
  
 AND 
  
 AI 
 . 
 IF 
 ( 
  
 ( 
 "This review mentions at least one of the film's main cast members: " 
 , 
  
 review 
 ), 
  
 endpoint 
  
 = 
>  
 'gemini-2.5-flash' 
 ) 
 ORDER 
  
 BY 
  
 ai_rating 
  
 DESC 
 LIMIT 
  
 10 
 ;

    Rate images

    The following query creates an external table from images of pet products stored in a publicly available Cloud Storage bucket. Then, it uses the AI.SCORE function to rate the images on a scale of 1 to 10 based on how fun they look for a pet, and returns the top 5 most fun looking items:

      -- Create a dataset 
 CREATE 
  
 SCHEMA 
  
 IF 
  
 NOT 
  
 EXISTS 
  
 cymbal_pets 
 ; 
 -- Create an object table 
 CREATE 
  
 OR 
  
 REPLACE 
  
 EXTERNAL 
  
 TABLE 
  
 cymbal_pets 
 . 
 product_images 
 WITH 
  
 CONNECTION 
  
 us 
 . 
 example_connection 
 OPTIONS 
  
 ( 
  
 object_metadata 
  
 = 
  
 'SIMPLE' 
 , 
  
 uris 
  
 = 
  
 [ 
 'gs://cloud-samples-data/bigquery/tutorials/cymbal-pets/images/*.png' 
 ] 
 ); 
 -- Find the top 5 most fun pet products 
 SELECT 
  
 STRING 
 ( 
 OBJ 
 . 
 GET_ACCESS_URL 
 ( 
 ref 
 , 
 'r' 
 ). 
 access_urls 
 . 
 read_url 
 ) 
  
 AS 
  
 signed_url 
 , 
  
 AI 
 . 
 SCORE 
 ( 
  
 ( 
 'Rate the product from 1-10 based on how fun it looks for a pet: ' 
 , 
  
 OBJ 
 . 
 GET_ACCESS_URL 
 ( 
 ref 
 , 
  
 'r' 
 ))) 
  
 AS 
  
 fun_score 
 FROM 
  
 `cymbal_pets.product_images` 
 ORDER 
  
 BY 
  
 fun_score 
  
 DESC 
 LIMIT 
  
 5 
 ;

    The AI.SCORE and AI.GENERATE_DOUBLE functions both use models to generate a number in response to a prompt. The following differences can help you choose which function to use:

    • Prompt optimization: AI.SCORE automatically rewrites your prompt to generate a scoring rubric, which is especially helpful if you don't provide clear instructions for how to score input in your prompt.
    • Input: AI.GENERATE_DOUBLE lets you specify a specific model parameters to use.
    • Output: AI.SCORE returns a FLOAT64 value, which makes it easier to work with in queries. AI.GENERATE_DOUBLE returns a STRUCT that contains a FLOAT64 , as well as additional information about the model call, which is useful if you need to view details such as the safety rating or API response status.
    • Error handling: If AI.SCORE produces an error for any input, then the function returns NULL . AI.GENERATE_DOUBLE records details about the errors in its output.

    Locations

    You can run AI.SCORE in all of the regions that support Gemini models, and also in the US and EU multi-regions.

    Quotas

    See Generative AI functions quotas and limits .

    What's next
    Design a Mobile Site
    View Site in Mobile | Classic
    Share by: