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
  • Create a Cloud Resource Connection
  • 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 and BigQuery Connection API APIs.

    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 APIs

    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:

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.")]));

Create a connection

Create aCloud resource connectionand get the connection's service account.

Select one of the following options:

Console

  1. Go to theBigQuery page.

    Go to BigQuery

  2. In the left pane, clickExplorer:

    Highlighted button for the Explorer pane.

    If you don't see the left pane, clickExpand left pane to open the pane.

  3. In theExplorer pane, expand your project name, and then clickConnections.

  4. On theConnections page, clickCreate connection.

  5. ForConnection type, chooseVertex AI remote models, remotefunctions, BigLake and Spanner (Cloud Resource).

  6. In theConnection ID field, enter a name for your connection.

  7. ForLocation type, select a location for your connection. Theconnection should be colocated with your other resources such asdatasets.

  8. ClickCreate connection.

  9. ClickGo to connection.

  10. In theConnection info pane, copy the service account ID for use ina later step.

bq

  1. In a command-line environment, create a connection:

    bqmk--connection--location=REGION--project_id=PROJECT_ID\--connection_type=CLOUD_RESOURCECONNECTION_ID

    The--project_id parameter overrides the default project.

    Replace the following:

    • REGION: yourconnection region
    • PROJECT_ID: your Google Cloud project ID
    • CONNECTION_ID: an ID for yourconnection

    When you create a connection resource, BigQuery creates aunique system service account and associates it with the connection.

    Troubleshooting: If you get the following connection error,update the Google Cloud SDK:

    Flags parsing error: flag --connection_type=CLOUD_RESOURCE: value should be one of...

  2. Retrieve and copy the service account ID for use in a laterstep:

    bqshow--connectionPROJECT_ID.REGION.CONNECTION_ID

    The output is similar to the following:

    name                          properties1234.REGION.CONNECTION_ID     {"serviceAccountId": "connection-1234-9u56h9@gcp-sa-bigquery-condel.iam.gserviceaccount.com"}

Python

Before trying this sample, follow thePython setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQueryPython API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

importgoogle.api_core.exceptionsfromgoogle.cloudimportbigquery_connection_v1client=bigquery_connection_v1.ConnectionServiceClient()defcreate_connection(project_id:str,location:str,connection_id:str,):"""Creates a BigQuery connection to a Cloud Resource.    Cloud Resource connection creates a service account which can then be    granted access to other Google Cloud resources for federated queries.    Args:        project_id: The Google Cloud project ID.        location: The location of the connection (for example, "us-central1").        connection_id: The ID of the connection to create.    """parent=client.common_location_path(project_id,location)connection=bigquery_connection_v1.Connection(friendly_name="Example Connection",description="A sample connection for a Cloud Resource.",cloud_resource=bigquery_connection_v1.CloudResourceProperties(),)try:created_connection=client.create_connection(parent=parent,connection_id=connection_id,connection=connection)print(f"Successfully created connection:{created_connection.name}")print(f"Friendly name:{created_connection.friendly_name}")print(f"Service Account:{created_connection.cloud_resource.service_account_id}")exceptgoogle.api_core.exceptions.AlreadyExists:print(f"Connection with ID '{connection_id}' already exists.")print("Please use a different connection ID.")exceptExceptionase:print(f"An unexpected error occurred while creating the connection:{e}")

Node.js

Before trying this sample, follow theNode.js setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQueryNode.js API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

const{ConnectionServiceClient}=require('@google-cloud/bigquery-connection').v1;const{status}=require('@grpc/grpc-js');constclient=newConnectionServiceClient();/** * Creates a new BigQuery connection to a Cloud Resource. * * A Cloud Resource connection creates a service account that can be granted access * to other Google Cloud resources. * * @param {string} projectId The Google Cloud project ID. for example, 'example-project-id' * @param {string} location The location of the project to create the connection in. for example, 'us-central1' * @param {string} connectionId The ID of the connection to create. for example, 'example-connection-id' */asyncfunctioncreateConnection(projectId,location,connectionId){constparent=client.locationPath(projectId,location);constconnection={friendlyName:'Example Connection',description:'A sample connection for a Cloud Resource',// The service account for this cloudResource will be created by the API.// Its ID will be available in the response.cloudResource:{},};constrequest={parent,connectionId,connection,};try{const[response]=awaitclient.createConnection(request);console.log(`Successfully created connection:${response.name}`);console.log(`Friendly name:${response.friendlyName}`);console.log(`Service Account:${response.cloudResource.serviceAccountId}`);}catch(err){if(err.code===status.ALREADY_EXISTS){console.log(`Connection '${connectionId}' already exists.`);}else{console.error(`Error creating connection:${err.message}`);}}}

Terraform

Use thegoogle_bigquery_connectionresource.

Note: To create BigQuery objects using Terraform, you mustenable theCloud Resource Manager API.

To authenticate to BigQuery, set up Application DefaultCredentials. For more information, seeSet up authentication for client libraries.

The following example creates a Cloud resource connection namedmy_cloud_resource_connection in theUS region:

# This queries the provider for project information.data "google_project" "default" {}# This creates a cloud resource connection in the US region named my_cloud_resource_connection.# Note: The cloud resource nested object has only one output field - serviceAccountId.resource "google_bigquery_connection" "default" {  connection_id = "my_cloud_resource_connection"  project       = data.google_project.default.project_id  location      = "US"  cloud_resource {}}

To apply your Terraform configuration in a Google Cloud project, complete the steps in the following sections.

Prepare Cloud Shell

  1. LaunchCloud Shell.

  2. Set the default Google Cloud project where you want to apply your Terraform configurations.

    You only need to run this command once per project, and you can run it in any directory.

    export GOOGLE_CLOUD_PROJECT=PROJECT_ID

    Environment variables are overridden if you set explicit values in the Terraform configuration file.

Prepare the directory

Each Terraform configuration file must have its own directory (alsocalled aroot module).

  1. InCloud Shell, create a directory and a new file within that directory. The filename must have the.tf extension—for examplemain.tf. In this tutorial, the file is referred to asmain.tf.
    mkdirDIRECTORY && cdDIRECTORY && touch main.tf

  2. If you are following a tutorial, you can copy the sample code in each section or step.

    Copy the sample code into the newly createdmain.tf.

    Optionally, copy the code from GitHub. This is recommended when the Terraform snippet is part of an end-to-end solution.

  3. Review and modify the sample parameters to apply to your environment.
  4. Save your changes.
  5. Initialize Terraform. You only need to do this once per directory.
    terraform init

    Optionally, to use the latest Google provider version, include the-upgrade option:

    terraform init -upgrade

Apply the changes

  1. Review the configuration and verify that the resources that Terraform is going to create or update match your expectations:
    terraform plan

    Make corrections to the configuration as necessary.

  2. Apply the Terraform configuration by running the following command and enteringyes at the prompt:
    terraform apply

    Wait until Terraform displays the "Apply complete!" message.

  3. Open your Google Cloud project to view the results. In the Google Cloud console, navigate to your resources in the UI to make sure that Terraform has created or updated them.
Note: Terraform samples typically assume that the required APIs are enabled in your Google Cloud project.

Grant permissions to the connection's service account

Grant the connection's service account the Vertex AI User role. You must grant this role in the same project you created or selected in theBefore you begin section. Granting the role in a different project results in the errorbqcx-1234567890-xxxx@gcp-sa-bigquery-condel.iam.gserviceaccount.com does not have the permission to access resource.

To grant the role, follow these steps:

  1. Go to theIAM & Admin page.

    Go to IAM & Admin

  2. ClickGrant Access.

  3. In theNew principals field, enter the service account ID that youcopied earlier.

  4. In theSelect a role field, chooseVertex AI, and thenselectVertex AI User role.

  5. ClickSave.

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'],connection_id=>"CONNECTION_ID")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"),connection_id=>"CONNECTION_ID")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"),connection_id=>"CONNECTION_ID")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"),connection_id=>"CONNECTION_ID")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'],connection_id=>"CONNECTION_ID")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'],connection_id=>"CONNECTION_ID")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),connection_id=>"CONNECTION_ID")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),connection_id=>"CONNECTION_ID");

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'],connection_id=>"CONNECTION_ID")ASnegative_topicFROMmy_dataset.customer_feedbackWHEREAI.IF(("Does this review express a negative sentiment? Review: ",review_text),connection_id=>"CONNECTION_ID");

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),connection_id=>"CONNECTION_ID"));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 2025-12-15 UTC.