Generative AI overview

This document describes the generative artificial intelligence (AI) functions that BigQuery supports. These functions accept natural language inputs and use pre-trained Vertex AI models and built-in BigQuery models.

Overview

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

  • Generate creative content.
  • Analyze, detect sentiment, and answer questions about text or unstructured data, 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 you accomplish these tasks:

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

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

      • AI.GENERATE is the most flexible inference function, which lets you analyze any combination of text and unstructured data.

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

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

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

      • AI.GENERATE_TABLE

    • Generate text with a table-valued function version of AI.GENERATE

      • ML.GENERATE_TEXT

    • Generate embeddings for semantic search and clustering

      • ML.GENERATE_EMBEDDING

  • Managed AI functions: These functions have a streamlined 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 the choice of model, prompt, and parameters to use. Their output includes detailed information about call the to the model, including the status and full model response, which might include information about the safety rating or citations.

Generate structured data

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

To generate structured data, create a remote model over any of the generally available or preview Gemini models. You can then use the AI.GENERATE_TABLE function to interact with that model. To try creating structured data, see Generate structured data by using the AI.GENERATE_TABLE function.

You can specify safety attributes when you use Gemini models with the AI.GENERATE_TABLE function in order to filter the model's responses.

For supported Gemini models, you can use Vertex AI Provisioned Throughput to provide consistent high throughput for requests.

Generate values of a specific type by row

You can use scalar generative AI functions with Gemini models to analyze data in BigQuery standard tables. Data includes both text data and unstructured data from columns that contain ObjectRef values. For each row in the table, these functions generate output containing a specific type. For example, you might analyze images of home furnishings to generate text for a design_type column, so that the furnishings SKU has an associated description, such as mid-century modern or farmhouse.

The following AI functions are available:

When you use the AI.GENERATE function with supported Gemini models, you can use Vertex AI Provisioned Throughput to provide consistent high throughput for requests. For more information, see Use Vertex AI Provisioned Throughput.

Generate text with a TVF

You can perform generative AI tasks by using the table-valued function GENERATE_TEXT, which uses remote models in BigQuery ML to reference models deployed to or hosted in Vertex AI. You can create the following types of remote models:

After you create a remote model, you can use the ML.GENERATE_TEXT function to interact with that model:

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

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

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

  • For all other types of remote models, you can use the ML.GENERATE_TEXT function with a prompt that you provide in a query or from a column in a standard table.

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

You can use grounding and safety attributes when you use Gemini models with the ML.GENERATE_TEXT function, provided that you are using a standard table for input. Grounding lets the Gemini model use additional information from the internet to generate more specific and factual responses. Safety attributes let the Gemini model filter the responses it returns based on the attributes you specify.

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

For supported Gemini models, you can use Vertex AI Provisioned Throughput to provide consistent high throughput for requests. For more information, see Use Vertex AI Provisioned Throughput.

Generate embeddings

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

Some common use cases for embedding generation are as follows:

  • Using retrieval-augmented generation (RAG) to augment model responses to user queries by referencing additional data from a trusted source. RAG provides better factual accuracy and response consistency, and also provides access to 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.

Supported models

The following models are supported:

For a smaller, lightweight text embedding, try using a pretrained TensorFlow model, such as NNLM, SWIVEL, or BERT.

Using embedding generation models

After you create the model, you can use the ML.GENERATE_EMBEDDING function to interact with it. For all types of supported models, ML.GENERATE_EMBEDDING works with structured data in standard tables. For multimodal embedding models, ML.GENERATE_EMBEDDING also works with visual content from either standard table columns that contain ObjectRef values, or from object tables.

For remote models, all inference occurs in Vertex AI. For other model types, all inference occurs in BigQuery. The results are stored in BigQuery.

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

Managed AI functions

Managed AI functions are purpose-built to automate routine tasks, such as classification, ordering, or filtering. These functions use Gemini and don't require customization. BigQuery uses prompt engineering and selects the appropriate model and parameters to use for the specific task to optimize the quality and consistency of your results. Each function returns a scalar value, such as a BOOL, FLOAT64 or STRING, 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 a WHERE or JOIN clause, based on a prompt. For example, you could filter product descriptions by those 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, or other criteria. You can use this function in an ORDER BY clause to extract the top K items according to score. For example, you could find the top 10 most positive or negative user reviews for a product.
  • AI.CLASSIFY: Classify text into user-defined categories. You can use this function in a GROUP BY clause to group inputs according to the categories that you define. For example, you could classify support tickets by whether they relate to billing, shipping, product quality, or something else.

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

Locations

Supported locations for text generation and embedding models vary based on the model type and version that you use. For more information, see Locations.

Pricing

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

For more information, see BigQuery ML pricing.

What's next