Process documents with the ML.PROCESS_DOCUMENT function
This document describes how to use the
ML.PROCESS_DOCUMENT
function
with a
remote model
to extract useful insights from documents in an
object table.
Supported locations
You must create the remote model used in this procedure in either the US
or EU
multi-region. You must run
the ML.PROCESS_DOCUMENT
function in the same region as the remote model.
Required permissions
To create a Document AI processor, you need the following role:
roles/documentai.editor
To create a connection, you need membership in the following role:
roles/bigquery.connectionAdmin
To create the model using BigQuery ML, you need the following permissions:
bigquery.jobs.create
bigquery.models.create
bigquery.models.getData
bigquery.models.updateData
bigquery.models.updateMetadata
To run inference, you need the following permissions:
bigquery.tables.getData
on the object tablebigquery.models.getData
on the modelbigquery.jobs.create
Before you begin
- 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.
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
-
Make sure that billing is enabled for your Google Cloud project.
-
Enable the BigQuery, BigQuery Connection API, and Document AI APIs.
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
-
Make sure that billing is enabled for your Google Cloud project.
-
Enable the BigQuery, BigQuery Connection API, and Document AI APIs.
Create a processor
Create a processor in Document AI to process the documents. The processor must be of a supported type.
Create a connection
Create a cloud resource connection and get the connection's service account.
Select one of the following options:
Console
Go to the BigQuery page.
To create a connection, click
Add, and then click Connections to external data sources.In the Connection type list, select Vertex AI remote models, remote functions and BigLake (Cloud Resource).
In the Connection ID field, enter a name for your connection.
Click Create connection.
Click Go to connection.
In the Connection info pane, copy the service account ID for use in a later step.
bq
In a command-line environment, create a connection:
bq mk --connection --location=REGION --project_id=PROJECT_ID \ --connection_type=CLOUD_RESOURCE CONNECTION_ID
The
--project_id
parameter overrides the default project.Replace the following:
REGION
: your connection regionPROJECT_ID
: your Google Cloud project IDCONNECTION_ID
: an ID for your connection
When you create a connection resource, BigQuery creates a unique 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...
Retrieve and copy the service account ID for use in a later step:
bq show --connection PROJECT_ID.REGION.CONNECTION_ID
The output is similar to the following:
name properties 1234.REGION.CONNECTION_ID {"serviceAccountId": "connection-1234-9u56h9@gcp-sa-bigquery-condel.iam.gserviceaccount.com"}
Terraform
Append the following section into your main.tf
file.
## This creates a cloud resource connection. ## Note: The cloud resource nested object has only one output only field - serviceAccountId. resource "google_bigquery_connection" "connection" { connection_id = "CONNECTION_ID" project = "PROJECT_ID" location = "REGION" cloud_resource {} }
CONNECTION_ID
: an ID for your connectionPROJECT_ID
: your Google Cloud project IDREGION
: your connection region
Grant access to the service account
Select one of the following options:
Console
Go to the IAM & Admin page.
Click
Grant Access.The Add principals dialog opens.
In the New principals field, enter the service account ID that you copied earlier.
In the Select a role field, select Document AI, and then select Document AI Viewer.
Click Add another role.
In the Select a role field, select Cloud Storage, and then select Storage Object Viewer.
Click Save.
gcloud
Use the
gcloud projects add-iam-policy-binding
command:
gcloud projects add-iam-policy-binding 'PROJECT_NUMBER' --member='serviceAccount:MEMBER' --role='roles/documentai.viewer' --condition=None gcloud projects add-iam-policy-binding 'PROJECT_NUMBER' --member='serviceAccount:MEMBER' --role='roles/storage.objectViewer' --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 a Permission denied
error.
Create a dataset
Create a dataset to contain the model and the object table. You must create the dataset, the connection and the document processor in the same region.
Create a model
Create a remote model with a
REMOTE_SERVICE_TYPE
of
CLOUD_AI_DOCUMENT_V1
:
CREATE OR REPLACE MODEL `PROJECT_ID.DATASET_ID.MODEL_NAME` REMOTE WITH CONNECTION `PROJECT_ID.REGION.CONNECTION_ID` OPTIONS ( REMOTE_SERVICE_TYPE = 'CLOUD_AI_DOCUMENT_V1', DOCUMENT_PROCESSOR = 'PROCESSOR_ID' );
Replace the following:
PROJECT_ID
: your project ID.DATASET_ID
: the ID of the dataset to contain the model.MODEL_NAME
: the name of the model.REGION
: the region used by the connection.CONNECTION_ID
: the connection ID—for example,myconnection
.When you view 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 in Connection ID—for example
projects/myproject/locations/connection_location/connections/myconnection
.PROCESSOR_ID
: the document processor ID. To find this value, view the processor details, and then look at the ID row in the Basic Information section.
To see the model output columns, click Go to model in the query result after the model is created. The output columns are shown in the Labels section of the Schema tab.
Create an object table
Create an object table over a set of documents in Cloud Storage. The documents in the object table must be of a supported type.
Process documents
Process all the documents with the ML.PROCESS_DOCUMENT
:
SELECT * FROM ML.PROCESS_DOCUMENT( MODEL `PROJECT_ID.DATASET_ID.MODEL_NAME`, TABLE `PROJECT_ID.DATASET_ID.OBJECT_TABLE_NAME` );
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.OBJECT_TABLE_NAME
: the name of the object table that contains the URIs of the documents to process.
Alternatively, process some of the documents with the ML.PROCESS_DOCUMENT
:
SELECT * FROM ML.PROCESS_DOCUMENT( MODEL `PROJECT_ID.DATASET_ID.MODEL_NAME`, (SELECT * FROM `PROJECT_ID.DATASET_ID.OBJECT_TABLE_NAME` WHERE FILTERS LIMIT NUM_DOCUMENTS ) );
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.OBJECT_TABLE_NAME
: the name of the object table that contains the URIs of the documents to process.FILTERS
: conditions to filter out the documents you want to process on the object table columns.NUM_DOCUMENTS
: the max number of documents you want to process.
Examples
Example 1
The following example uses the
expense parser
to process the documents represented by the documents
table:
SELECT * FROM ML.PROCESS_DOCUMENT( MODEL `myproject.mydataset.expense_parser`, TABLE `myproject.mydataset.documents` );
This query returns the parsed expense reports, including the currency,
total amount, receipt date, and line items on the expense reports. The
ml_process_document_result
column contains the raw output of the expense
parser, and the ml_process_document_status
column contains any errors returned
by the document processing.
Example 2
The following example shows how to filter the object table to choose which documents to process, and then write the results to a new table:
CREATE TABLE `myproject.mydataset.expense_details` AS SELECT uri, content_type, receipt_date, purchase_time, total_amount, currency FROM ML.PROCESS_DOCUMENT( MODEL `myproject.mydataset.expense_parser`, (SELECT * FROM `myproject.mydataset.expense_reports` WHERE uri LIKE '%restaurant%'));
What's next
- For information about model inference in BigQuery ML, see Model inference overview.
- For information about the supported SQL statements and functions for each model type, see End-to-end user journey for each model.