Understand text with the ML.UNDERSTAND_TEXT function

This document describes how to use theML.UNDERSTAND_TEXT functionwith aremote modelto perform a natural language text analysis function on text from aBigQuery standard table.

Required roles

To create a remote model and analyze text, you need thefollowing Identity and Access Management (IAM) roles at the project level:

  • Create and use BigQuery datasets, tables, and models:BigQuery Data Editor (roles/bigquery.dataEditor)

  • Create, delegate, and use BigQuery connections:BigQuery Connections Admin (roles/bigquery.connectionsAdmin)

    If you don't have adefault connectionconfigured, you can create and set one as part of running theCREATE MODEL statement. To do so, you must have BigQuery Admin(roles/bigquery.admin) on your project. For more information, seeConfigure the default connection.

  • Grant permissions to the connection's service account: Project IAM Admin(roles/resourcemanager.projectIamAdmin)

  • Create BigQuery jobs: BigQuery Job User(roles/bigquery.jobUser)

These predefined roles contain the permissions required to perform the tasks inthis document. To see the exact permissions that are required, expand theRequired permissions section:

Required permissions

  • Create a dataset:bigquery.datasets.create
  • Create, delegate, and use a connection:bigquery.connections.*
  • Set service account permissions:resourcemanager.projects.getIamPolicy andresourcemanager.projects.setIamPolicy
  • Create a model and run inference:
    • bigquery.jobs.create
    • bigquery.models.create
    • bigquery.models.getData
    • bigquery.models.updateData
    • bigquery.models.updateMetadata

You might also be able to get these permissions withcustom roles or otherpredefined roles.

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. Verify that billing is enabled for your Google Cloud project.

  4. Enable the BigQuery, BigQuery Connection API, and Cloud Natural Language 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

  5. 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

  6. Verify that billing is enabled for your Google Cloud project.

  7. Enable the BigQuery, BigQuery Connection API, and Cloud Natural Language 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

Create a dataset

Create a BigQuery dataset to contain your resources:

Console

  1. In the Google Cloud console, 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, click your project name.

  4. ClickView actions > Create dataset.

  5. On theCreate dataset page, do the following:

    1. ForDataset ID, type a name for the dataset.

    2. ForLocation type, selectRegion orMulti-region.

      • If you selectedRegion, then select a location from theRegion list.
      • If you selectedMulti-region, then selectUS orEuropefrom theMulti-region list.

    3. ClickCreate dataset.

bq

  1. To create a new dataset, use thebq mk commandwith the--location flag:

    bq --location=LOCATION mk -dDATASET_ID

    Replace the following:

    • LOCATION: the dataset'slocation.
    • DATASET_ID is the ID of the dataset that you'recreating.

  2. Confirm that the dataset was created:

    bqls

Create a connection

Create aCloud resource connectionand get the connection's service account. Create the connection inthe samelocation as the dataset you created in theprevious step.

You can skip this step if you either have a default connection configured, oryou have the BigQuery Admin role.

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 access to the service account

Select one of the following options:

Console

  1. Go to theIAM & Admin page.

    Go to IAM & Admin

  2. ClickAdd.

    TheAdd principals dialog opens.

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

  4. In theSelect a role field, selectService Usage, and thenselectService Usage Consumer.

  5. ClickAdd another role.

  6. In theSelect a role field, selectBigQuery, and thenselectBigQuery Connection User.

  7. ClickSave.

gcloud

Use thegcloud projects add-iam-policy-binding command:

gcloud projects add-iam-policy-binding 'PROJECT_NUMBER' --member='serviceAccount:MEMBER' --role='roles/serviceusage.serviceUsageConsumer' --condition=Nonegcloud projects add-iam-policy-binding 'PROJECT_NUMBER' --member='serviceAccount:MEMBER' --role='roles/bigquery.connectionUser' --condition=None

Replace the following:

  • PROJECT_NUMBER: your project number.
  • MEMBER: the service account ID that you copied earlier.

Failure to grant the permission results in an error.

Create a model

Create a remote model with aREMOTE_SERVICE_TYPE ofCLOUD_AI_NATURAL_LANGUAGE_V1:

CREATEORREPLACEMODEL`PROJECT_ID.DATASET_ID.MODEL_NAME`REMOTEWITHCONNECTION{DEFAULT|`PROJECT_ID.REGION.CONNECTION_ID`}OPTIONS(REMOTE_SERVICE_TYPE='CLOUD_AI_NATURAL_LANGUAGE_V1');

Replace the following:

  • PROJECT_ID: your project ID.
  • DATASET_ID: the ID of the dataset to contain the model. This dataset must be in the samelocation as the connection that you are using.
  • MODEL_NAME: the name of the model.
  • REGION: the region used by the connection.
  • CONNECTION_ID: the connection ID—for example,myconnection.

    When youview the connection details in the Google Cloud console, the connection ID is the value in the last section of the fully qualified connection ID that is shown inConnection ID—for exampleprojects/myproject/locations/connection_location/connections/myconnection.

Understand text

Understand text with theML.UNDERSTAND_TEXT function:

SELECT*FROMML.UNDERSTAND_TEXT(MODEL`PROJECT_ID.DATASET_ID.MODEL_NAME`,{TABLEPROJECT_ID.DATASET_ID.TABLE_NAME|(QUERY)},STRUCT('FEATURE_NAME'ASnlu_option[,'FLATTEN_JSON_OUTPUT'ASflatten_json_output][,ENCODING_TYPEASencoding_type]));

Replace the following:

  • PROJECT_ID: your project ID.
  • DATASET_ID: the ID of the dataset that contains the model.
  • MODEL_NAME: the name of the model.
  • TABLE_NAME: the name of the table that contains the text to analyze in a column namedtext_content. If the text is in a column with a different name, specifytext_content as an alias for that column.
  • QUERY: a query that contains the text to describe in a column namedtext_content. If the text is in a column with a different name, specifytext_content as an alias for that column.
  • FEATURE_NAME: the name of a supportedNatural Language API feature.
  • FLATTEN_JSON_OUTPUT: aBOOL value that determines whether the JSON content returned by the function is parsed into separate columns.
  • ENCODING_TYPE: aSTRING value that specifies the encoding that the Cloud Natural Language API uses to determine encoding-dependent information such as thebeginOffset value. For more information, seeEncodingType.

Example 1

The following example inspects the text in the table'stext_content columnand identifies the prevailing emotional opinion within the text:

SELECT*FROMML.UNDERSTAND_TEXT(MODEL`mydataset.mynlpmodel`,TABLEmydataset.mytable,STRUCT('analyze_sentiment'ASnlu_option));

Example 2

The following example inspects the text in the table'scomment columnand provides syntactic information about the text:

SELECT*FROMML.UNDERSTAND_TEXT(MODEL`mydataset.mynlpmodel`,(SELECTcommentAStext_contentfrommydataset.mytable),STRUCT('analyze_syntax'ASnlu_option));

What's next

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.