Generative AI and Machine Learning in BigQuery: What is New

The gap between data analysts and powerful Artificial Intelligence is closing rapidly. Gone are the days when running Large Language Models (LLMs) on your data required complex Python pipelines, data movement, or specialized infrastructure. With the latest updates to Google Cloud, you can now access state-of-the-art generative models, including Gemini 3.1, Claude, Mistral, and Llama, directly within BigQuery using standard SQL.

This post explores the new suite of AI. and ML. functions, their capabilities, and how they handle security to transform your data warehouse into an AI engine.

The Core Capabilities: What Are These Functions?

BigQuery has introduced a set of native SQL functions that handle the heavy lifting of calling Vertex AI models. These functions allow you to generate text, categorize unstructured data, create vector embeddings, and perform semantic searches without managing external infrastructure.

1. AI.GENERATE and ML.GENERATE_TEXT

The Swiss Army Knife for Text and Structure

AI.GENERATE is the primary generative AI inside BigQuery. It allows you to invoke foundation models directly from SQL to process text, images, audio, and documents, without exporting data or building external pipelines.

Key Capabilities

• Multimodal Processing enables AI.GENERATE to analyze mixed input types within a single SQL query. It can process text, images, audio, and documents together, allowing users to ask questions about an image, summarize a video transcript, extract insights from PDFs, or combine structured and unstructured data in one prompt.
• Flexible Output Control allows the function to generate both free-form text and structured responses. While it excels at producing summaries, explanations, translations, or generated content, it also supports the output_schema parameter. This ensures the output conforms to a defined structure, such as JSON or typed columns, making the results directly consumable in downstream SQL workflows.
• Broad Task Coverage means a single function can handle multiple AI-driven operations, including summarization, translation, entity extraction, sentiment analysis, classification, and structured data extraction. This eliminates the need for multiple specialized models and simplifies AI integration directly within BigQuery.

Use Case: Automated Article Enrichment with Generative AI

This query demonstrates how a media organization can automatically enrich unstructured news articles directly within BigQuery. It selects technology articles and uses AI.GENERATE to extract key entities, identify main topics, analyze sentiment, translate the content into Chinese, and produce a one-sentence summary, all in a single SQL statement. The result is structured, analytics-ready data generated from raw text without exporting data or building external AI pipelines.

SELECT
  title,
  body,
  AI.GENERATE(
    body,
    output_schema =>
      "key_entities ARRAY<STRING>, main_topics ARRAY<STRING>, sentiment STRING, translate_to_chinese STRING, summary_one_sentence STRING").*
    EXCEPT (full_response, status)
FROM bigquery-public-data.bbc_news.fulltext
WHERE category = 'tech'
LIMIT 3;

While AI.GENERATE offers a simplified experience, ML.GENERATE_TEXT is the robust foundation for calling remote models, particularly when you need to use a fine-tuned Gemini model with fine-grained control over parameters like temperature, top_k, and safety_settings.

2. AI.GENERATE_TABLE

The Structured Data Converter

AI.GENERATE_TABLE is a specialized variation designed specifically to return structured data tables based on unstructured inputs, ideal for converting messy text logs or reviews into clean rows and columns. This makes the output immediately queryable.

Key Capabilities

• Strict Schema Enforcement: You explicitly define the output structure (for example, age INT64, is_married BOOL, diagnosis STRING). The function validates that the model’s response conforms to these data types, ensuring clean, strongly typed, and immediately queryable results inside BigQuery.
• Messy Data Cleanup: Designed for transforming unstructured or inconsistent inputs, such as customer review logs, call center transcripts, or medical notes, into standardized, analytics-ready tables. This eliminates manual parsing logic and reduces downstream data cleaning efforts.

Use Case: Structured Medical Data Extraction from Clinical Notes

This query demonstrates how a healthcare analytics team can convert unstructured medical transcription text into structured, queryable clinical data directly within BigQuery. It selects raw medical notes and uses AI.GENERATE_TABLE with a defined schema to extract patient age, blood pressure (systolic and diastolic), weight, medical conditions, diagnoses, and prescribed medications. By enforcing strict data types (e.g., INT64, FLOAT64, ARRAY ), the function ensures the output is clean, standardized, and immediately ready for downstream analytics, reporting, or population health insights, without building custom NLP pipelines.

SELECT
  age,
  blood_pressure,
  weight,
  conditions,
  diagnosis,
  medications,
  prompt
FROM
  AI.GENERATE_TABLE(MODEL bqml_tutorial.gemini25flash,
    (
    SELECT
      input_text AS prompt
    FROM
      bqml_tutorial.kaggle_medical_transcriptions
    LIMIT
      3),
    STRUCT(
      "age INT64, blood_pressure STRUCT<high INT64, low INT64>, weight FLOAT64, conditions ARRAY<STRING>, diagnosis ARRAY<STRING>, medications ARRAY<STRING>" AS output_schema,
      1024 AS max_output_tokens))

3. AI.CLASSIFY

Automated Categorization

While you could ask a generic LLM to “categorize this,” AI.CLASSIFY is optimized specifically for mapping inputs to a defined set of labels.

Key Capabilities

You provide a list of categories (e.g., [‘urgent’, ‘routine’, ‘spam’]) or a JSON map with descriptions, and the function returns the best fit. It automatically structures the input to improve classification quality.

Use Case: Automated News Categorization at Scale

This query shows how a media organization can automatically classify large volumes of news articles directly within BigQuery. Using AI.CLASSIFY, each article body is assigned to one of several predefined categories, such as tech, sport, business, politics, or entertainment. The results are then aggregated to count how many articles fall into each category. This enables real-time content tagging, editorial analytics, and trend monitoring without building separate machine learning models or exporting data outside the data warehouse.

SELECT
  AI.CLASSIFY(
    body, 
    categories => ['tech', 'sport', 'business', 'politics', 'entertainment'],
    connection_id => 'us.test_connection') AS category,
  COUNT(*) num_articles
FROM bigquery-public-data.bbc_news.fulltext
GROUP BY category;

4. AI.EMBED and ML.GENERATE_EMBEDDING

The Foundation of Semantic Search

These functions convert text, images, or videos into “embeddings”, numerical vectors that represent meaning.

Key Capabilities

Once data is embedded, you can mathematically compare items to find the “nearest matches” rather than just matching keywords. AI.EMBED is the newer, simplified syntax for generating embeddings, while ML.GENERATE_EMBEDDING offers granular control for tasks like Principal Component Analysis (PCA) and Matrix Factorization.

Use Case: Generating Semantic Embeddings for Intelligent Search and Retrieval

This query demonstrates how a media organization can generate vector embeddings for news articles directly within BigQuery using AI.EMBED. For each article, the body text is converted into a high-dimensional numerical representation using the specified embedding model. These embeddings can then power semantic search, similarity matching, recommendation systems, and clustering, enabling more intelligent content discovery and personalization without exporting data to an external vector processing system.

SELECT
 title,
 body,
 AI.EMBED(
   body,
   endpoint => "text-embedding-005"
 ).result
FROM
 `bigquery-public-data.bbc_news.fulltext`
LIMIT 3;

5. AI.SIMILARITY

Semantic Search Made Simple

This scalar function calculates the semantic similarity score between two inputs (text vs. text, or text vs. image).

Key Capabilities

It handles the embedding generation and the cosine similarity calculation in a single step. This removes the need to pre-compute vectors for ad-hoc analysis.

Use Case: Semantic Search for Relevant News Articles

This query illustrates how a media or financial analytics team can perform semantic search directly within BigQuery. Using AI.SIMILARITY, it compares a natural language query“housing market downward trends” against the body of each news article by leveraging text embeddings. The function computes a similarity score based on semantic meaning rather than keyword matching, ranks the articles accordingly, and returns the top five most relevant results. This enables context-aware content retrieval, trend monitoring, and intelligent research workflows without deploying an external vector database.

SELECT
 "housing market downward trends" AS query,
 title AS bbc_news_title,
 body AS bbc_news_body,
 AI.SIMILARITY(
   "housing market downward trends", body, endpoint => "text-embedding-005")
   AS similarity_score
FROM `bigquery-public-data.bbc_news.fulltext`
ORDER BY similarity_score DESC
LIMIT 5;

A Summary Table of BigQuery Generative AI Functions and Features

Supported Models: From Gemini to Open Source

BigQuery now supports a massive variety of models, giving you the flexibility to choose the right balance of cost and performance.

1. Google Models: Full support for the Gemini family (including Gemini 3.1 Pro/Flash) and Vertex AI text/multimodal embedding models.
2. Partner Models: You can run inference using Google-managed partner models such as Anthropic’s Claude, Mistral, and Meta’s Llama directly via SQL.
3. Open Models: Through Hugging Face and Vertex AI Model Garden, you can deploy open models such as gemma-3-1b-it with a simple CREATE MODEL statement, as shown below. BigQuery automatically provisions the required compute resources and handles lifecycle management, including cleanup.

CREATE OR REPLACE MODEL my_dataset.managed_text_model
REMOTE WITH CONNECTION DEFAULT
OPTIONS (
  model_garden_model_name = 'publishers/google/models/gemma3@gemma-3-1b-it'
);

Real-World Use Cases for BigQuery Generative AI

The ability to mix structured business data with unstructured AI processing opens doors across industries:

• Retail & E-Commerce: Transform product images into text descriptions for SEO, classify customer reviews by sentiment, or build visual search tools using embeddings.
• Healthcare: Extract structured patient data (e.g., age, symptoms, medication) from unstructured doctor notes or intake forms using AI.GENERATE with a defined output schema.
• Finance: Use AI.SIMILARITY to detect anomalies or find conceptually similar transaction descriptions that might indicate fraud.
• Media & Publishing: Automate content tagging, translate articles into different languages, and generate headlines or summaries for millions of archived documents.

Security and Identity

Integrating AI into the database raises valid security questions. Google Cloud has streamlined this with a focus on governance and ease of use.

• End User Credentials (EUC): Previously, you had to manage complex service account keys. Now, BigQuery can authenticate Vertex AI requests using your personal IAM identity (End User Credentials). If your account has the “Vertex AI User” role, you can run these queries immediately without setting up intermediary connections.
• Unified Governance: Because the workflow happens within BigQuery, your existing data governance policies apply. Data does not need to be exported to insecure environments to be processed.
• Safety Filters: Functions like ML.GENERATE_TEXT and AI.GENERATE_TABLE supports, allowing safety_settings you to set thresholds for hate speech, harassment, and dangerous content. The model will filter out responses that violate these safety standards.
• Resource Management: For open models, BigQuery automatically manages the backend compute, releasing resources and stopping costs when the endpoint sits idle for a configured duration.

⭐⭐⭐

The latest AI and ML functions in BigQuery mark a significant evolution toward truly SQL-native AI. By bringing models directly to the data, organizations can accelerate experimentation, reduce architectural overhead, and unlock insights from unstructured data, without leaving the familiar SQL environment their teams already rely on.

This approach not only simplifies implementation; it fundamentally shortens the path from data to value. Whether you’re enriching datasets, automating workflows, or building intelligent analytics, BigQuery is rapidly becoming a unified platform for both data and AI.

Ready to take the next step? Start exploring AI functions today using BigQuery’s public datasets, and see how quickly you can turn raw data into actionable intelligence.

Ready to scale your impact? Contact us today to design, deploy, and scale your AI-powered data solutions on Google Cloud.