Perform semantic analysis with managed AI functions

This tutorial shows you how to use BigQuery ML managed AI functions toperform semantic analysis on customer feedback.

Objectives

In this tutorial, you:

  • Create a dataset and load sentiment data into a table
  • Use the following AI functions to perform semantic analysis:
    • AI.IF:to filter your data with natural language conditions
    • AI.SCORE:to rate input by sentiment
    • AI.CLASSIFY:to classify input into user-defined categories

Costs

This tutorial uses billable components of Google Cloud,including the following:

  • BigQuery
  • BigQuery ML

For more information on BigQuery costs, see theBigQuery pricing page.

For more information on BigQuery ML costs, seeBigQuery ML pricing.

Before you begin

  1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

  2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Roles required to select or create a project

    • Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
    • Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains theresourcemanager.projects.create permission.Learn how to grant roles.
    Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

    Go to project selector

  3. If you're using an existing project for this guide,verify that you have the permissions required to complete this guide. If you created a new project, then you already have the required permissions.

  4. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Roles required to select or create a project

    • Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
    • Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains theresourcemanager.projects.create permission.Learn how to grant roles.
    Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

    Go to project selector

  5. If you're using an existing project for this guide,verify that you have the permissions required to complete this guide. If you created a new project, then you already have the required permissions.

  6. Enable the BigQuery API.

    Roles required to enable APIs

    To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains theserviceusage.services.enable permission.Learn how to grant roles.

    Enable the API

    For new projects, the BigQuery API is automatically enabled.

  7. Optional:Enable billing for the project. If you don't want to enable billing or provide a credit card, the steps in this document still work. BigQuery provides you a sandbox to perform the steps. For more information, seeEnable the BigQuery sandbox.Note: If your project has a billing account and you want to use the BigQuery sandbox, thendisable billing for your project.

Required roles

To get the permissions that you need to use AI functions, ask your administrator to grant you the following IAM roles on the project:

  • Run query jobs and load jobs:BigQuery Job User (roles/bigquery.jobUser)
  • Create a dataset, create a table, load data into a table, and query a table:BigQuery Data Editor (roles/bigquery.dataEditor)

For more information about granting roles, seeManage access to projects, folders, and organizations.

You might also be able to get the required permissions throughcustom roles or otherpredefined roles.

Create sample data

To create a dataset calledmy_dataset for this tutorial, run the followingquery.

CREATESCHEMAmy_datasetOPTIONS(location='LOCATION');

Next, create a table calledcustomer_feedback that contains sample customerreviews for a device:

CREATETABLEmy_dataset.customer_feedbackAS(SELECT*FROMUNNEST([STRUCT<review_idINT64,review_textSTRING>(1,"The battery life is incredible, and the screen is gorgeous! Best phone I've ever had. Totally worth the price."),(2,"Customer support was a nightmare. It took three weeks for my order to arrive, and when it did, the box was damaged. Very frustrating!"),(3,"The product does exactly what it says on the box. No complaints, but not exciting either."),(4,"I'm so happy with this purchase! It arrived early and exceeded all my expectations. The quality is top-notch, although the setup was a bit tricky."),(5,"The price is a bit too high for what you get. The material feels cheap and I'm worried it won't last. Service was okay."),(6,"Absolutely furious! The item arrived broken, and getting a refund is proving impossible. I will never buy from them again."),(7,"This new feature for account access is confusing. I can't find where to update my profile. Please fix this bug!"),(8,"The shipping was delayed, but the support team was very helpful and kept me informed. The product itself is great, especially for the price.")]));

Categorize overall sentiment

It can be helpful to extract the overall sentiment expressed in textto support use cases such as the following:

  • Gauge customer satisfaction from reviews.
  • Monitor brand perception on social media.
  • Prioritize support tickets based on how upset users are.

The following query shows how to use theAI.CLASSIFY function to classifyreviews from thecustomer_feedback table aspositive,negative, orneutral:

SELECTreview_id,review_text,AI.CLASSIFY(review_text,categories=>['positive','negative','neutral'])ASsentimentFROMmy_dataset.customer_feedback;

The result looks similar to the following:

+-----------+------------------------------------------+-----------+| review_id | review_text                              | sentiment |+-----------+------------------------------------------+-----------+| 7         | This new feature for account access is   | negative  ||           | confusing. I can't find where to update  |           ||           | my profile. Please fix this bug!         |           |+-----------+------------------------------------------+-----------+| 4         | "I'm so happy with this purchase! It     | positive  ||           | arrived early and exceeded all my        |           ||           | expectations. The quality is top-notch,  |           ||           | although the setup was a bit tricky."    |           |+-----------+------------------------------------------+-----------+| 2         | "Customer support was a nightmare. It    | negative  ||           | took three weeks for my order to         |           ||           | arrive, and when it did, the box was     |           ||           | damaged. Very frustrating!"              |           |+-----------+------------------------------------------+-----------+| 1         | "The battery life is incredible, and     | positive  ||           | the screen is gorgeous! Best phone I've  |           ||           | ever had. Totally worth the price."      |           |+-----------+------------------------------------------+-----------+| 8         | "The shipping was delayed, but the       | positive  ||           | support team was very helpful and kept   |           ||           | me informed. The product itself is       |           ||           | great, especially for the price."        |           |+-----------+------------------------------------------+-----------+| 5         | The price is a bit too high for what     | negative  ||           | you get. The material feels cheap and    |           ||           | I'm worried it won't last. Service was   |           ||           | okay.                                    |           |+-----------+------------------------------------------+-----------+| 3         | "The product does exactly what it says   | neutral   ||           | on the box. No complaints, but not       |           ||           | exciting either."                        |           |+-----------+------------------------------------------+-----------+| 6         | "Absolutely furious! The item arrived    | negative  ||           | broken, and getting a refund is proving  |           ||           | impossible. I will never buy from them   |           ||           | again."                                  |           |+-----------+------------------------------------------+-----------+

Analyze aspect-based sentiment

If an overall sentiment such aspositive ornegative isn't sufficient foryour use case, you can analyze a specific aspect of the meaning of text. Forexample, you might want to understand a user's attitude towards the qualityof the product, without regard for their thoughts on its price. You can evenask for a custom value to indicate that a particular aspect doesn't apply.

The following example shows how to use theAI.SCORE function to rate usersentiment from 1 to 10 based on how favorable each review in thecustomer_feedback table is toward price, customer service, and quality. Thefunction returns the custom value -1 in cases where an aspect isn't mentioned inthe review so that you can filter these out later.

SELECTreview_id,review_text,AI.SCORE(("Score 0.0 to 10 on positive sentiment about PRICE for review: ",review_text,"If price is not mentioned, return -1.0"))ASprice_score,AI.SCORE(("Score 0.0 to 10 on positive sentiment about CUSTOMER SERVICE for review: ",review_text,"If customer service is not mentioned, return -1.0"))ASservice_score,AI.SCORE(("Score 0.0 to 10 on positive sentiment about QUALITY for review: ",review_text,"If quality is not mentioned, return -1.0"))ASquality_scoreFROMmy_dataset.customer_feedbackLIMIT3;

The result looks similar to the following:

+-----------+------------------------------------------+--------------+---------------+---------------+| review_id | review_text                              |  price_score | service_score | quality_score |+-----------+------------------------------------------+--------------+---------------+---------------+| 4         | "I'm so happy with this purchase! It     | -1.0         | -1.0          | 9.5           ||           | arrived early and exceeded all my        |              |               |               ||           | expectations. The quality is top-notch,  |              |               |               ||           | although the setup was a bit tricky."    |              |               |               |+-----------+------------------------------------------+--------------+---------------+---------------+| 8         | "The shipping was delayed, but the       |  9.0         |  8.5          | 9.0           ||           | support team was very helpful and kept   |              |               |               ||           | me informed. The product itself is       |              |               |               ||           | great, especially for the price."        |              |               |               |+-----------+------------------------------------------+--------------+---------------+---------------+| 6         | "Absolutely furious! The item arrived    | -1.0         |  1.0          | 0.0           ||           | broken, and getting a refund is proving  |              |               |               ||           | impossible. I will never buy from them   |              |               |               ||           | again."                                  |              |               |               |+-----------+------------------------------------------+--------------+---------------+---------------+

Detect emotions

In addition to positive or negative sentiment, you can classify text based onspecific emotions that you select. This is useful when you want to gain abetter understanding of user responses, or to flag highly emotional feedbackfor review.

SELECTreview_id,review_text,AI.CLASSIFY(review_text,categories=>['joy','anger','sadness','surprise','fear','disgust','neutral','other'])ASemotionFROMmy_dataset.customer_feedback;

The result looks similar to the following:

+-----------+------------------------------------------+---------+| review_id | review_text                              | emotion |+-----------+------------------------------------------+---------+| 2         | "Customer support was a nightmare. It    | anger   ||           | took three weeks for my order to         |         ||           | arrive, and when it did, the box was     |         ||           | damaged. Very frustrating!"              |         |+-----------+------------------------------------------+---------+| 7         | This new feature for account access is   | anger   ||           | confusing. I can't find where to update  |         ||           | my profile. Please fix this bug!         |         |+-----------+------------------------------------------+---------+| 4         | "I'm so happy with this purchase! It     | joy     ||           | arrived early and exceeded all my        |         ||           | expectations. The quality is top-notch,  |         ||           | although the setup was a bit tricky."    |         |+-----------+------------------------------------------+---------+| 1         | "The battery life is incredible, and     | joy     ||           | the screen is gorgeous! Best phone I've  |         ||           | ever had. Totally worth the price."      |         |+-----------+------------------------------------------+---------+| 8         | "The shipping was delayed, but the       | joy     ||           | support team was very helpful and kept   |         ||           | me informed. The product itself is       |         ||           | great, especially for the price."        |         |+-----------+------------------------------------------+---------+| 5         | The price is a bit too high for what     | sadness ||           | you get. The material feels cheap and    |         ||           | I'm worried it won't last. Service was   |         ||           | okay.                                    |         |+-----------+------------------------------------------+---------+| 3         | "The product does exactly what it says   | neutral ||           | on the box. No complaints, but not       |         ||           | exciting either."                        |         |+-----------+------------------------------------------+---------+| 6         | "Absolutely furious! The item arrived    | anger   ||           | broken, and getting a refund is proving  |         ||           | impossible. I will never buy from them   |         ||           | again."                                  |         |+-----------+------------------------------------------+---------+

Categorize reviews by topic

You can use theAI.CLASSIFY function to group reviews into predefined topics.For example, you can do the following:

  • Discover common themes in customer feedback.
  • Organize documents by subject matter.
  • Route support tickets by topic.

The following example shows how to classify customer feedback into varioustypes such asbilling issue oraccount access and then count how manyreviews belong to each category:

SELECTAI.CLASSIFY(review_text,categories=>['Billing Issue','Account Access','Product Bug','Feature Request','Shipping Delay','Other'])AStopic,COUNT(*)ASnumber_of_reviews,FROMmy_dataset.customer_feedbackGROUPBYtopicORDERBYnumber_of_reviewsDESC;

The result looks similar to the following:

+----------------+-------------------+| topic          | number_of_reviews |+----------------+-------------------+| Other          | 5                 || Shipping Delay | 2                 || Product Bug    | 1                 |+----------------+-------------------+

Identify semantically similar reviews

You can use theAI.SCORE function to assess how semantically similar twopieces of text are by asking it to rate similarity of meaning. This can help youwith tasks such as the following:

  • Find duplicate or near-duplicate entries.
  • Group similar pieces of feedback.
  • Power semantic search applications.

The following query finds reviews that discuss difficulty setting up theproduct:

SELECTreview_id,review_text,AI.SCORE(("""How similar is the review to the concept of 'difficulty in setting up the product'?         A higher score indicates more similarity. Review:""",review_text))ASsetup_difficultyFROMmy_dataset.customer_feedbackORDERBYsetup_difficultyDESCLIMIT2;

The result looks similar to the following:

+-----------+------------------------------------------+------------------+| review_id | review_text                              | setup_difficulty |+-----------+------------------------------------------+------------------+| 4         | "I'm so happy with this purchase! It     | 3                ||           | arrived early and exceeded all my        |                  ||           | expectations. The quality is top-notch,  |                  ||           | although the setup was a bit tricky."    |                  |+-----------+------------------------------------------+------------------+| 7         | This new feature for account access is   | 1                ||           | confusing. I can't find where to update  |                  ||           | my profile. Please fix this bug!         |                  |+-----------+------------------------------------------+------------------+

You can also use theAI.IF function to find reviews that relate to text:

SELECTreview_id,review_textFROMmy_dataset.customer_feedbackWHEREAI.IF(("Does this review discuss difficulty setting up the product? Review: ",review_text));

Combine functions

It can be helpful to combine these functions in a single query. For example,the following query first filters reviews for negative sentiment, and thenclassifies them by the type of frustration:

SELECTreview_id,review_text,AI.CLASSIFY(review_text,categories=>['Poor Quality','Bad Customer Service','High Price','Other Negative'])ASnegative_topicFROMmy_dataset.customer_feedbackWHEREAI.IF(("Does this review express a negative sentiment? Review: ",review_text));

Create reusable prompt UDFs

To keep your queries readable, you can reuse your prompt logic by creatinguser-defined functions. The followingquery creates a function to detect negative sentiment by callingAI.IF witha custom prompt. Then, it calls that function to filter by negative review.

CREATEORREPLACEFUNCTIONmy_dataset.is_negative_sentiment(review_textSTRING)RETURNSBOOLAS(AI.IF(("Does this review express a negative sentiment? Review: ",review_text)));SELECTreview_id,review_textFROMmy_dataset.customer_feedbackWHEREmy_dataset.is_negative_sentiment(review_text);

Clean up

To avoid incurring charges, you can either delete the project that contains theresources that you created, or keep the project and delete the individualresources.

Delete your project

To delete the project:

    Caution: Deleting a project has the following effects:
    • Everything in the project is deleted. If you used an existing project for the tasks in this document, when you delete it, you also delete any other work you've done in the project.
    • Custom project IDs are lost. When you created this project, you might have created a custom project ID that you want to use in the future. To preserve the URLs that use the project ID, such as anappspot.com URL, delete selected resources inside the project instead of deleting the whole project.

    If you plan to explore multiple architectures, tutorials, or quickstarts, reusing projects can help you avoid exceeding project quota limits.

  1. In the Google Cloud console, go to theManage resources page.

    Go to Manage resources

  2. In the project list, select the project that you want to delete, and then clickDelete.
  3. In the dialog, type the project ID, and then clickShut down to delete the project.

Delete your dataset

To delete the dataset and all resources that it contains, including all tablesand functions, run the following query:

DROPSCHEMAmy_datasetCASCADE;

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2026-02-05 UTC.