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:

  • Content must be in one of the supported formats that are described in the Gemini API model mimeType parameter.
  • For more information about accepted multimodal input, see the technical specifications for Gemini.

This function passes your input to a Gemini model and incurs charges in Vertex AI each time it's called.

Syntax

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

Arguments

AI.SCORE takes the following arguments:

  • PROMPT: The input to the model that consists of at least one STRING literal and at least one reference to either a STRING column or ObjectRef column. The prompt must be the first argument that you specify. You can provide the prompt value in the following ways:

    • Specify a concatenation of one or more STRING columns and STRING literals, such as CONCAT("Rate the review from 1-10: ", review_col).
    • Specify a STRUCT value that contains two or more fields. The struct must contain at least one STRING literal and at least one reference to either a STRING column or an ObjectRef column. You can use the following types of fields within the STRUCT value:
    Field type Description Examples
    STRING A string literal, or the name of a STRING column. String literal:
    'Rate how positive this review is:'

    String column name:
    my_string_column
    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 specifying the name of a column that contains ObjectRef values.

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

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

    The function combines STRUCT fields similarly to a CONCAT operation and concatenates the fields in their specified order. For example:

    Input:

    ('Rate this review: ', review_col, ' Use a scale from 1-10.')

    Transformed function input:

    'Rate this review: review_col Use a scale from 1-10.'

  • CONNECTION: a STRING value specifying the Cloud resource connection to use. The following forms are accepted:

    • Connection name: [PROJECT_ID].LOCATION.CONNECTION_ID

      For example, myproject.us.myconnection.

    • Fully qualified connection ID: projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID

      For example, projects/myproject/locations/us/connections/myconnection.

    Replace the following:

    • PROJECT_ID: the project ID of the project that contains the connection.
    • LOCATION: the location used by the connection.
    • CONNECTION_ID: the connection ID—for example, myconnection.

      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.

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),
    connection_id => 'us.example_connection') 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;

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),
    connection_id => 'us.example_connection') 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),
    connection_id => 'us.example_connection')
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')),
    connection_id => 'us.example_connection') 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.SCORE automatically selects a Gemini model. AI.GENERATE_DOUBLE lets you specify a specific model endpoint and 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