Generative AI overview

This document describes the generative artificial intelligence (AI) functionsthat BigQuery supports. These functions accept natural languageinputs and use pre-trainedVertex AI models andbuilt-in BigQuery models.

Overview

BigQuery offers a variety of AI functions to help with taskssuch as the following:

• Generate creative content.
• Analyze, detect sentiment, and answer questions about text or unstructureddata, such as images.
• Summarize the key ideas or impressions conveyed by the content.
• Extract structured data from text.
• Classify text or unstructured data into user defined categories.
• Generate embeddings to search for similar text, images, and video.
• Rate inputs in order to rank them by quality, similarity, or other criteria.

There are two main categories of AI functions to help youaccomplish these tasks:

• General-purpose AI functions: These functionsgive you full control and transparency on the choice of model, prompt, andparameters to use.

  • Perform inference,such as to answer questions about your data

    • AI.GENERATE is the most flexible inference function, which letsyou analyze any combination of text and unstructured data, andlets you output structured data that matches your custom schema.

    • If you require output of a specific type, select one of thefollowing more specialized inference functions:

      • AI.GENERATE_BOOL
      • AI.GENERATE_DOUBLE
      • AI.GENERATE_INT

  • Generate structured output, such asextracting names and addresses from unstructured text

    • AI.GENERATE, when you specify anoutput_schema
    • AI.GENERATE_TABLE

  • Generate text with a table-valued function versionofAI.GENERATE

    • AI.GENERATE_TEXT

  • Generate embeddings for semantic search andclustering

    • AI.EMBED
    • AI.GENERATE_EMBEDDING
    • AI.SIMILARITY

• Managed AI functions: These functions have astreamlined syntax and are optimized for cost and quality.BigQuery chooses a model for you.

  • Filter your data with natural language conditions

    • AI.IF

  • Rate input, such as by quality or sentiment

    • AI.SCORE

  • Classify input into user-defined categories

    • AI.CLASSIFY

General-purpose AI functions

General-purpose AI functions give you full control and transparency on thechoice of model, prompt, and parameters to use. Their output includes detailedinformation about call the to the model, including the status and full modelresponse, which might include information about the safety rating or citations.

Generate structured data

Structured data generation is very similar to text generation, except thatyou can format the response from the model by specifying a SQL schema. Forexample, you might generate a table that contains a customer's name, phonenumber, address, request, and pricing quote from a transcript of a phone call.

There are two ways to generate data structured data:

• TheAI.GENERATE functioncalls a Vertex AI endpoint and can generate aSTRUCT valuewith your custom schema.

  To try it out, see how touse structured outputwhen you call theAI.GENERATE function.

• TheAI.GENERATE_TABLE functioncalls a remote model and is a table-valuedfunction that generates a table with your custom schema.

  To try creating structured data, seeGenerate structured data by using theAI.GENERATE_TABLE function.

Generate values of a specific type by row

Preview

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

Note: To provide feedback or request support for this feature during thepreview, contactbqml-feedback@google.com.

You can use scalar generative AI functions with Gemini models toanalyze data in BigQuery standard tables. Data includes bothtext data and unstructured data fromcolumns that containObjectRef values.For each row in the table, these functions generate output containing aspecific type. Forexample, you might analyze images of home furnishings to generate text for adesign_type column, so that the furnishings SKU has an associateddescription, such asmid-century modern orfarmhouse.

The following AI functions are available:

• AI.GENERATE
• AI.GENERATE_BOOL
• AI.GENERATE_DOUBLE
• AI.GENERATE_INT

When you use theAI.GENERATE function withsupported Gemini models,you can useVertex AI Provisioned Throughputto provide consistent high throughput for requests. For more information, seeUse Vertex AI Provisioned Throughput.

Generate text with a TVF

You can perform generative AI tasks by using the table-valued functionGENERATE_TEXT, which uses remote models inBigQuery ML to reference models deployedto or hosted in Vertex AI. You can create the following typesofremote models:

• Remote models over any of thegenerally availableorpreviewGemini models.

• Remote models over the following partner models:

  • Anthropic Claude models
  • Mistral AI models
  • Llama models

• Remote modelsoversupported open models.

After you create a remote model, you can use theAI.GENERATE_TEXT functionto interact with that model:

• For remote models based on Gemini models, you can do the following:

  • Use theAI.GENERATE_TEXT function to generatetext from a prompt that you specify in a query or pull from acolumn in a standard table. When you specify the prompt in a query,you can reference the following types of table columns in the prompt:

    • STRING columns to provide text data.
    • STRUCTcolumns that use theObjectRef formatto provide unstructured data. You must use theOBJ.GET_ACCESS_URL functionwithin the prompt to convert theObjectRef values toObjectRefRuntimevalues.

  • Use theAI.GENERATE_TEXT function to analyze text, image, audio,video, or PDF content from an object table with a prompt that youprovide as a function argument.

• For all other types of remote models, you can use theAI.GENERATE_TEXTfunction with a prompt that you provide in a query or from a column in astandard table.

Use the following topics to try text generation in BigQuery ML:

• Generate text by using a Gemini model and theAI.GENERATE_TEXT function.
• Generate text by using a Gemma model and theAI.GENERATE_TEXT function.
• Analyze images with a Gemini model.
• Generate text by using theAI.GENERATE_TEXT function with your data.
• Tune a model using your data.

You can usegroundingandsafety attributeswhen you use Gemini models with theAI.GENERATE_TEXT function,provided that you are using a standard table for input. Grounding lets theGemini model use additional information from the internet togenerate more specific and factual responses. Safety attributes let theGemini model filter the responses it returns based on theattributes you specify.

For some models, you can optionally choose to configuresupervised tuning,which lets you train the model on your own data to make it better suited foryour use case. All inference occurs in Vertex AI.The results are stored in BigQuery.

Forsupported Gemini models,you can useVertex AI Provisioned Throughputto provide consistent high throughput forrequests. For more information, seeUse Vertex AI Provisioned Throughput.

Generate embeddings

An embedding is a high-dimensional numerical vector that represents a givenentity, like a piece of text or an audio file. Generating embeddings lets youcapture the semantics of your data in a way that makes it easier to reason aboutand compare the data.

Some common use cases for embedding generation are as follows:

• Using retrieval-augmented generation (RAG) to augment model responses to userqueries by referencing additional data from a trusted source. RAG providesbetter factual accuracy and response consistency, and also provides accessto data that is newer than the model's training data.
• Performing multimodal search. For example, using text input to search images.
• Performing semantic search to find similar items for recommendations,substitution, and record deduplication.
• Creating embeddings to use with a k-means model for clustering.

For more information about how to generate embeddings and use them to performthese tasks, see theIntroduction to embeddings and vector search.

Managed AI functions

Preview

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

Managed AI functions are purpose-built to automate routine tasks, such asclassification, ordering, or filtering. These functions useGemini and don't require customization. BigQueryuses prompt engineering and selects the appropriate model and parameters to usefor the specific task to optimize the quality and consistency of your results.Each function returns a scalar value, such as aBOOL,FLOAT64 orSTRING,and doesn't include additional status information from the model.The following managed AI functions are available:

• AI.IF:Filter text or multi-modal data, such as in aWHERE orJOIN clause,based on a prompt. For example, you could filter product descriptions bythose that describe an item that would make a good gift.
• AI.SCORE:Rate inputs based on a prompt in order to rank rows by quality, similarity, orother criteria. You can use this function in anORDER BY clause to extractthe top K items according to score. For example, you could find the top10 most positive or negative user reviews for a product.
• AI.CLASSIFY:Classify text into user-defined categories. You can usethis function in aGROUP BY clause to group inputs according to thecategories that you define. For example, you could classify support ticketsby whether they relate to billing, shipping, product quality, or somethingelse.

For a tutorial that shows examples of how to use these functions, seePerform semantic analysis with managed AI functions.

For a notebook tutorial that shows how to use managed and general-purpose AIfunctions, seeSemantic analysis with AI functions.

Locations

Supported locations for text generation and embedding models vary based on themodel type and version that you use. For more information, seeLocations.

Pricing

You are charged for the compute resources that you use to run queries againstmodels. Remote models make calls to Vertex AImodels, so queries against remote models also incur charges fromVertex AI.

For more information, seeBigQuery ML pricing.

Track costs

The generative AI functions in BigQuery work by sending requests toVertex AI, which can incur costs. To track the Vertex AIcosts incurred by a job that you run in BigQuery, follow thesesteps:

1. View your billing reports inCloud Billing.

2. Use filters to refine your results.

   For services, selectVertex AI.

3. To see the charges for a specific job,filter by label.

   Set the key tobigquery_job_id_prefix and the value to thejob ID of your job. If your job IDis longer than 63 characters, only use the first 63 characters. If yourjob ID contains any uppercase characters, change them to lowercase.Alternatively, you canassociate jobs with a custom labelto help you look them up later.

It can take up to 24 hours for some charges to appear in Cloud Billing.

Monitoring

To better understand the behavior of AI functions that you call inBigQuery, you can enable request and response logging. Tolog the entire request and response sent to and received fromVertex AI, follow these steps:

1. Enable request-response logsin Vertex AI. The logs are stored in BigQuery.You must separately enable logging for each different foundation model andregion. To log queries that run in theus region, specify theus-central1region in your request. To log queries that run in theeu region, specifytheeurope-west4 region in your request.

2. Run a query using an AI function that makes a call toVertex AI using the model that you enabled logging forin the previous step.

3. To view the full Vertex AI request and response, queryyour logging table for rows where thelabels.bigquery_job_id_prefix field of thefull_request columnmatches the first 63 characters of yourjob ID. Optionally, you canuse a custom query labelto help you look up the query in the logs.

   For example, you can use a query similar to the following:

   SELECT*FROM`my_project.my_dataset.request_response_logging`WHEREJSON_VALUE(full_request,'$.labels.bigquery_job_id_prefix')='bquxjob_123456...';

What's next

• For an introduction to AI and ML in BigQuery, seeIntroduction to AI and ML in BigQuery.
• For more information about performing inference over machine learningmodels, seeModel inference overview.
• For more information about supported SQL statements and functions forgenerative AI models, seeEnd-to-end user journeys for generative AI models.