Analyze anything with AI-powered SQL in BigQuery | by Alicia Williams | Google Cloud – Community | Oct, 2025

These three new functions are managed AI functions, which differ slightly from the general-purpose AI functions you may already be using (like AI.GENERATE, AI.GENERATE_BOOL, etc.). The key difference is this: the new managed functions are optimized for ease of use and an appropriate model and parameters are chosen for you, while general-purpose AI functions give power-users full control over the prompt and model. Both types are incredibly useful!

In this post, I’ll walk you through how to use each of AI.SCORE, AI.CLASSIFY, and AI.IF and point you to a notebook where you can try them yourself alongside the general-purpose functions.

Want to go straight to the notebook? You can get it here: Semantic Analysis in BigQuery with AI Functions.

Using AI.SCORE for ranking

First up is AI.SCORE, which lets you rank items based on criteria that don’t currently exist as columns in your table. Imagine you have a table of pet supply products and want to find the best ones for a “Holiday Gift Guide” campaign. How “giftable” is a product? That’s not a column in my table, but it’s a perfect job for AI.SCORE to quantify the qualitative.

I can just ask the model to rank the giftability of products based on their description, and it will return a score.

Here’s the query from our example notebook:

SELECT
product_name,
description,
AI.SCORE((
'How "giftable" is this product for a pet owner? ', description,
'Use a scale from 1-10.'),
connection_id => 'us.test_connection') AS giftability_score
FROM
`cymbal_pets.products`
ORDER BY
giftability_score DESC;

Just like that, I can rank all my products on a completely subjective metric.

Press enter or click to view image in full size

BigQuery enhances your prompts behind the scenes, so you don’t need to be a prompt engineering expert to get great results. For example, when using AI.SCORE, BigQuery will automatically rewrite your prompt to create a scoring rubric, even if your original prompt doesn’t clearly explain how the input should be scored.

Using AI.CLASSIFY for categorizing data

Next, let’s look at AI.CLASSIFY. This function can help you enrich your data by classifying text or images into a set of categories you provide.

Let’s stick with our pet products table. Say we want to categorize each item by animal type. We can use AI.CLASSIFY to analyze the product name and description, then label it with one of our defined categories like “Dog,” “Cat,” “Bird,” or “Fish.”

The query is just as straightforward. You provide the text to analyze and the list of categories:

SELECT
product_name,
AI.CLASSIFY(
('What animal is this product for?',product_name,' ',description),
categories => ["Dog", "Cat", "Bird", "Fish", "Small Animal", "All Pets"],
connection_id => 'us.test_connection') AS animal_type
FROM
`cymbal_pets.products`

It’s a super quick way to add new, structured data to a table.

Here are a few of the results that you’ll see after running the query:

Using AI.IF for semantic filtering

Finally, there’s AI.IF, which might be my favorite. It evaluates a natural language condition and returns true or false, making it perfect for powerful filtering in a WHERE clause. What’s really cool is that it works on multimodal data, like images, audio, or PDFs.

Let’s say I have a table pointing to product images in Cloud Storage. If I want to find all the images that contain a ball, I can’t do that with normal SQL. With AI.IF, I just ask the question.

Here’s how to do it:

SELECT
STRING(OBJ.GET_ACCESS_URL(ref, 'r').access_urls.read_url) AS signed_url,
uri,
metadata
FROM
`cymbal_pets.product_images`
WHERE
AI.IF((
'Does this product image contain a ball? ',ref),
connection_id => 'us.test_connection');

Here are a few of the results visualized in the notebook:

Press enter or click to view image in full size

The AI.IF function can also be used for semantic joins, like matching a product description to the correct product image, which is another example you’ll find in the notebook.

Try it Yourself!

The best way to learn is by doing, and I’ve put all these examples and more into a hands-on notebook. It has all the setup instructions and sample data you need to get started in just a few minutes.

Get the notebook here: Semantic Analysis in BigQuery with AI Functions

Then open the notebook in BigQuery by clicking “Open in BigQuery Studio”:

Press enter or click to view image in full size

Let me know what you think of these new functions in the comments.