Unleash Row-Level AI in BigQuery: Scalar LLM Functions for Smarter SQL | by Rodrigo Souza | Google Cloud – Community | May, 2025

10 min read

·

14 hours ago

TL;DR: BigQuery’s scalar AI functions (AI.GENERATE, _BOOL, _INT, _DOUBLE) bring LLM power to each row of your data. This post demonstrates how to use them in SQL for tasks like extracting text, getting real-time data with Google Search grounding, boolean classification for filtering, and extracting numbers — all setting the stage for the upcoming AI Query Engine.

The world of data analytics is constantly evolving, and BigQuery continues to be at the forefront, empowering users to not just query data, but to truly understand and interact with it. For years, BigQuery ML has been bringing machine learning closer to your data. Now, it’s taking another giant leap forward with the introduction of powerful, row-wise (scalar) LLM functions: AI.GENERATE, AI.GENERATE_BOOL, AI.GENERATE_DOUBLE, and AI.GENERATE_INT.

These functions are set to revolutionize how we approach data manipulation and analysis using AI directly within SQL. Because they return scalar values, they can be seamlessly integrated almost anywhere a value is needed in your queries — in SELECT lists to generate new insights per row, in WHERE clauses for AI-driven filtering, within JOIN conditions for intelligent data linking, and even in GROUP BY clauses for dynamic, AI-assisted categorization.

These foundational scalar functions are doing more than just enhancing today’s workflows; they are paving the way for even more advanced capabilities. Think of them as the building blocks for the upcoming AI Query Engine operators that created such a buzz at Google Cloud Next ’25, promising a future where AI is an even more integral and intuitive part of your data conversations.

In this post, we’ll dive into practical examples of each of these new scalar AI functions. We’ll use a common dataset to illustrate how you can start leveraging them today to enrich your data, derive deeper insights, and simplify complex text processing tasks — all directly within BigQuery.

Setting the Stage: Data and Setup

For these examples, we’ll use a simple table containing text snippets related to various financial transactions, travel plans, and currency interests. This will give our AI functions some rich, unstructured text to work with. Please feel free to create them in your environment, so you may follow along.

First, create our source table, transaction_texts, in your preferred dataset:

CREATE OR REPLACE TABLE your_dataset.transaction_texts (
text_id STRING NOT NULL,
description STRING
);

Now, populate it with some sample data:

INSERT INTO your_dataset.transaction_texts (text_id, description)
VALUES
('tt_001', 'Planning my trip from California to the UK next month, then a quick hop over to Switzerland. Need to check conversion for US Dollar to British Pound and then USD to Swiss Franc.'),
('tt_002', 'Our company just closed a deal with a Japanese client for JPY 1,500,000. We also need to pay a supplier in Canada 5,000 CAD. Our books are in USD.'),
('tt_003', 'I saw an interesting item online priced at 75 EUR. Wondering what that would be in my local AUD. Also curious about the general trend of EUR against AUD.'),
('tt_004', 'For our European expansion, we are tracking key rates like Euro to US Dollar and also Euro to British Pound closely this quarter.'),
('tt_005', 'Received a refund of 300 GBP. Need to convert this back to USD. Also, I have a small balance in INR from my last trip.');

Common Parameters

For all the AI function calls in these examples, there are two parameters that are always required: connection_id and endpoint. The connection_id can be created following these steps. In this case, we are creating a connection to Vertex AI, which hosts our model.

For the endpoint, we will inform which model we want to use. It is worth mentioning that only Gemini models are supported on these new functions. For this particular post, we will be using Gemini 2.0 Flash.

You may also notice that, as of now, we need to keep passing these parameters over and over again, in every function call. I really hope that, when AI Engine becomes available, some sort of abstraction will be created, so we can reuse the same configuration over multiple functions, which would make our lives even easier!

AI.GENERATE: Extracting and Enriching Textual Data

The AI.GENERATE() function is your go-to for generating or transforming text, returning a STRING value. We can use it for summarization, extraction, rephrasing and basically anything that would return a STRING answer. It is like talking to an LLM, but in a row based fashion.

Let’s begin by asking the model to extract all currency pairs that it finds in our text. This will give us an understanding of what kind of currency conversion our customers are interested in.

SELECT
tt.text_id,
tt.description,
AI.GENERATE(
('Analyze the following text and identify up to two currency pairs that are explicitly mentioned or strongly implied for conversion or comparison. ',
'Express each pair using their standard three-letter codes in the format "CODE1/CODE2" (e.g., "USD/EUR"). ',
'If multiple pairs are found, list them comma-separated (e.g., "USD/EUR, USD/GBP"). ',
'If no clear pairs, output "N/A". Text: "',
tt.description,'"'),
connection_id => 'us.your-connection',
endpoint => 'gemini-2.0-flash'  
).result AS identified_pairs_string
FROM
your_dataset.transaction_texts AS tt
order by text_id; 

You can already see how powerful a simple example can be! For each row in our table, we are asking Gemini to identify what are the currencies (and the relationship between them) in an open text format. This is a true conversation and reasoning using your data and getting important value out of it.

Another very useful feature that we can use on this function is Google search grounding. Now that you have identified the currencies, how about giving the current exchange rate between them?

WITH ExtractedCurrencyPairs AS (
SELECT
tt.text_id,
tt.description,
AI.GENERATE(
('Analyze the following text and identify up to two currency pairs that are explicitly mentioned or strongly implied for conversion or comparison. ',
'Express each pair using their standard three-letter codes in the format "CODE1/CODE2" (e.g., "USD/EUR"). ',
'If multiple pairs are found, list them comma-separated (e.g., "USD/EUR, USD/GBP"). ',
'If no clear pairs, output "N/A". Text: "',
tt.description,'"'),
connection_id => 'us.your-connection',
endpoint => 'gemini-2.0-flash'  
).result AS identified_pairs_string
FROM
your_dataset.transaction_texts AS tt
)
SELECT
ecp.text_id,
ecp.description AS original_text,
ecp.identified_pairs_string,
TRIM(individual_pair) AS currency_pair_to_search, -- Cleaned individual pair from the UNNEST
AI.GENERATE(
('What is the current exchange rate for the currency pair ',
TRIM(individual_pair)),
connection_id => 'us.your-connection',
endpoint => 'gemini-2.0-flash',   
model_params => JSON '{"tools": [{"googleSearch": {}}]}' -- Enabling Google Search
).result AS current_exchange_rate
FROM
ExtractedCurrencyPairs AS ecp,
UNNEST(SPLIT(ecp.identified_pairs_string, ',')) AS individual_pair -- Unnest the comma-separated string of pairs
order by text_id;

Explanation:

• In the ExtractedCurrencyPairs CTE, the first AI.GENERATE call processes each description to identify currency pairs (like “USD/GBP”). The prompt guides the LLM to format this as a comma-separated string.
• The main SELECT statement then takes this string of pairs, uses SPLIT and UNNEST to handle each pair individually.
• The second AI.GENERATE call is where the grounding happens. Its prompt asks for the current exchange rate for the specific pair.
• The magic for grounding is in model_params => JSON ‘{“tools”: [{“googleSearch”: {}}]}’. This tells the LLM to use Google Search to find the most up-to-date information to answer the prompt.

This example beautifully shows how AI.GENERATE can perform initial text processing and then leverage grounding for real-time data enrichment, all within one SQL query. The output for current_exchange_rate_info will be a string, even listing multiple providers and market commentary.

AI.GENERATE_BOOL: Classifying Data with True/False Logic

Next up is AI.GENERATE_BOOL. This function is perfect when you need a clear TRUE or FALSE output based on your text data, ideal for classification tasks and filtering.

Let’s say that we want to filter only the rows that are primarily business-related. We can easily use AI.GENERATE_BOOL in a where clause:

SELECT
tt.text_id,
tt.description
FROM
your_dataset.transaction_texts AS tt
where AI.GENERATE_BOOL(
('Is the primary subject of the following text related to a commercial business activity, a companys financial operations, or corporate travel, as opposed to personal finances, personal travel, or general individual currency interest?',
tt.description),
connection_id => 'us.your-connection',
endpoint => 'gemini-2.0-flash'   
).result = true
order by text_id;

Running this, you’d expect tt_002 (“Our company just closed a deal…”) and tt_004 (“For our European expansion…”) to be TRUE, while others like tt_001 (personal trip) would ideally be FALSE. This is a powerful feature. We just asked a direct question that can be answered with a yes/no (true/false) and we use that for filtering in a where clause. This kind of classification can be incredibly useful for segmenting data, routing workflows, filtering based on perceived urgency and so on.

AI.GENERATE_DOUBLE: Extracting Numerical Values from Text

When you need to pull out numerical figures like prices, scores, or measurements from text and use them as actual numbers, AI.GENERATE_DOUBLE is the function you need.

Let’s try to extract the first clear monetary amount mentioned in our descriptions.

SELECT
tt.text_id,
tt.description,
AI.GENERATE_DOUBLE(
('From the following text, extract the first clear monetary value mentioned.',
'If no clear monetary value is found, respond with 0.0. '
'Text: "',tt.description,'"'),
connection_id => 'us.your-connection',
endpoint => 'gemini-2.0-flash'  
).result AS extracted_monetary_amount
FROM
your_dataset.transaction_texts AS tt
order by text_id;

This example seems simple, but it is actually pretty interesting. In the past, if you were working with LLM responses and you wanted a specific numeric value out of it, you would need to do some proper prompt engineering and parse the result to a numeric value. Now it is very straightforward: you only worry about the prompt and let AI.GENERATE_DOUBLE do the parsing for you.

AI.GENERATE_INT: Counting and Quantifying from Text

Finally, AI.GENERATE_INT is used when you need to extract or derive whole numbers from your text. This could be counts, IDs (if simple numerics), or other integer quantities.

Let’s use it to count the number of distinct financial “events” described in each text.

SELECT
tt.text_id,
tt.description,
AI.GENERATE_INT(
('Analyze the following text. Count how many distinct financial events such as "deals made", "payments to be made", "payments received", "refunds issued", "refunds received", or "items explicitly priced for purchase" are described. ',
'For example, "closed a deal and need to pay a supplier" would be 2 events. "An item priced at X" is 1 event. Mere currency conversion planning or rate tracking are not events. ',
'Respond with only the total numerical count of these distinct financial events. If no such events are described, respond with "0". Text: "',
tt.description,'"'),
connection_id => 'us.your-connection',
endpoint => 'gemini-2.0-flash'
).result AS number_of_financial_events
FROM
your_dataset.transaction_texts AS tt
order by text_id; 

For our data, we’d expect tt_002 to result in 2 events, while tt_001 and tt_004 would likely be 0. Another simple example, but showcases how you can use AI to retrieve insights out of your data, in an automated fashion, using only SQL and natural language.

Beyond SELECT: The True Flexibility of Scalar AI Functions

The examples above primarily use these AI functions in the SELECT list to generate new columns. However, their true power lies in their scalar nature. Because they return a single value per row, you can integrate them into other parts of your SQL queries (like we saw in our boolean example). Here are some more ideas of how they could be used:

• In a WHERE clause: Imagine filtering customer feedback to only process highly negative reviews: WHERE AI.GENERATE_BOOL(‘Does this review express strong negative sentiment?…’, feedback_column, …).result = TRUE
• In a GROUP BY clause: You could dynamically categorize transaction descriptions: GROUP BY AI.GENERATE(‘Categorize this transaction type: …’, description_column, …).result
• In a JOIN condition: For more complex scenarios, you might even use an AI function to help define join logic based on semantic similarity or extracted attributes (though this requires careful planning).

This flexibility opens up a new world of possibilities for building intelligent data processing pipelines directly within BigQuery.

Conclusion: A New Era of AI-Infused SQL

The introduction of row-wise scalar LLM functions — AI.GENERATE, AI.GENERATE_BOOL, AI.GENERATE_DOUBLE, and AI.GENERATE_INT — marks a significant step in democratizing access to powerful generative AI capabilities directly within BigQuery. They offer unparalleled flexibility for enriching, classifying, and transforming your data using familiar SQL.

These functions are more than just new tools; they represent a foundational shift, empowering data practitioners to embed AI intelligence into every level of their data interaction. And as we saw at Google Cloud Next ’25, they are the building blocks for the even more intuitive and powerful AI Query Engine operators on the horizon.

The ability to perform sophisticated, row-level AI processing without moving data out of BigQuery and even combining these functions with capabilities like Google Search grounding for real-time context, is a game-changer. I encourage you to start experimenting with these functions on your own datasets. The potential to unlock new insights and automate complex tasks is immense!