Prepare and test your skills
Prepare and test your skills
Establishing a remote connection to BigQuery means creating a secure, stored link that lets BigQuery combine its own tables with data or services outside BigQuery storage. The connection is created and managed through the BigQuery Connection API, and a single SQL query can then use both BigQuery tables and the external system. For large language model (LLM) work, a special Cloud resource connection lets BigQuery (opens in a new tab) send requests to Google models such as Gemini.
A BigQuery connection provides authorized access to an external source without sending database credentials as cleartext. The connection definition is encrypted and securely stored in the BigQuery connection service, so BigQuery holds the trust boundary between the user on one side and the external system on the other side. A common example is storing customer orders in Cloud SQL and sales in BigQuery, then creating a Cloud SQL connection to join the two tables in one query. Access to the connection is controlled separately from access to tables, because owners share a connection by granting BigQuery connection Identity and Access Management (IAM) roles to other users.
A connection type defines which external system BigQuery can reach, and each type uses the same secure storage model while traffic flows from BigQuery to a different destination. The main types are:
No connection is required for Bigtable and Google Drive. Choose the Google Cloud resource connection type when the goal is AI integration rather than federated access to an external database or object store.
A Cloud resource connection (opens in a new tab) is a connection that authorizes BigQuery to call other Google Cloud resources, including Vertex AI remote models, remote functions, and BigLake. Once the connection exists, it can be referenced in a CREATE MODEL statement to create a remote model over an LLM, a Cloud AI service, or a custom model hosted on Vertex AI. The remote model then appears as a BigQuery ML model that can be used with SQL. Data flows from a BigQuery SQL query to the remote model, and inference is processed in Vertex AI rather than inside BigQuery storage, so analysts can use models like Gemini without moving BigQuery data or managing training infrastructure.
Authentication for a Cloud resource connection is based on a system service account created automatically by BigQuery. When the connection resource is created, BigQuery creates a unique service account and associates it with that connection. The administrator copies the service account ID from the Connection info pane or from the bq show --connection output. That service account is the identity that Vertex AI sees when BigQuery calls the remote model, so the administrator must grant the service account permission to use Vertex AI and must grant users IAM permission to use the connection.
Exam tip: Use a Cloud Resource connection type for Vertex AI remote models, remote functions, and BigLake; Bigtable and Google Drive do not require a connection.
The steps to create a Cloud resource connection establish the identity and location that later SQL statements will reuse. The connection and dataset should be in the same location, such as multi-region US, so BigQuery and Vertex AI can work together efficiently.
bq mk --connection --location=US --connection_type=CLOUD_RESOURCE with a connection ID.CREATE MODEL statement to create a remote model over an LLM, then call that model with SQL for prediction and text generation.Utilizing Google LLM functions in BigQuery means using SQL to run machine learning and text analysis without moving data or managing training infrastructure. BigQuery ML (opens in a new tab) is the capability in BigQuery that lets you train, evaluate, and run inference on machine learning models by using GoogleSQL queries. For LLMs, BigQuery ML uses a remote model over a Google built-in model such as Gemini, and all training and inference for that remote model is processed in Gemini Enterprise Agent Platform while you interact with it by using SQL in BigQuery.
A remote model in BigQuery ML is a model resource that points to an LLM hosted outside of BigQuery, such as a Gemini Enterprise Agent Platform built-in model like Gemini or a custom hosted model. The remote model is created with a CREATE MODEL statement and is then used like any other BigQuery ML model in GoogleSQL queries and batch predictions. Data flows from BigQuery tables into the query, from the query to the remote model endpoint in Agent Platform for inference, and the generated results flow back into BigQuery query results or tables for further analysis.
A Cloud resource connection for LLM access is the authorized path that lets a BigQuery query reach an Agent Platform remote model. The connection is created through the BigQuery Connection API, is encrypted and securely stored in the BigQuery connection service, and access to the connection is controlled with BigQuery connection IAM roles. The BigQuery query depends on the connection, and the remote model depends on the connection, because without the authorized connection BigQuery cannot send requests to the LLM endpoint.
Generative AI functions (opens in a new tab) are SQL functions in BigQuery that access Gemini and partner LLM models to work with text and unstructured data directly in queries. These functions support routine data processing tasks including:
There are two types of generative AI functions that differ in control and ease of use. General-purpose AI functions give full control over model, prompt, and parameters, while managed AI functions offer streamlined syntax for routine tasks where BigQuery can choose a model to optimize for cost and quality.
| Function type | Control | Choose when |
|---|---|---|
| General-purpose AI functions | You choose the model, prompt, and parameters | The task needs full control and transparency over generation |
| Managed AI functions | BigQuery chooses a model to optimize cost and quality | The task is routine filtering, rating, or classification |
Exam tip: BigQuery ML only supports batch predictions, so deploy a model trained in BigQuery ML to an Agent Platform endpoint through Model Registry when online predictions are needed.
Task-specific AI functions are SQL functions that use Cloud AI APIs to perform specialized analysis without building a custom model. These functions help with tasks including:
These functions let you enrich BigQuery data in place, because text flows from a BigQuery table row into the function call and the analyzed or translated result flows back as a new column in the same query result for review and interpretation.
Configuring a remote model definition means writing a Data Definition Language (DDL) statement that registers a Vertex AI foundation model as a BigQuery model resource. The definition does not train data in BigQuery; it creates a pointer that lets BigQuery send SQL requests through a connection to inference processing in Vertex AI. The remote model (opens in a new tab) is then called from SQL functions for prediction and text generation.
The parts of a remote model statement for pretrained models are CREATE MODEL with a REMOTE WITH CONNECTION clause and an ENDPOINT option. BigQuery stores the model name in a dataset, uses the connection to reach Vertex AI, and uses the endpoint to select which foundation model to call. The basic form is CREATE OR REPLACE MODEL PROJECT_ID.DATASET_ID.MODEL_NAME REMOTE WITH CONNECTION CONNECTION_PATH OPTIONS (ENDPOINT = 'ENDPOINT'). The placeholders in the statement each have a specific meaning:
PROJECT_ID: the Google Cloud project that will contain the modelDATASET_ID: the BigQuery dataset that will contain the modelMODEL_NAME: the name to give the remote model for use in later SQL functionsREGION: the region used by the connection, such as usCONNECTION_ID: the ID of the Cloud resource connection, such as myconnectionENDPOINT: the Vertex AI model to use, such as a Gemini model nameUse DEFAULT when a default connection is configured, or use the fully qualified path PROJECT_ID.REGION.CONNECTION_ID to name a specific connection. You can find the connection ID by viewing connection details in the console and copying the last section of the fully qualified ID, for example myconnection from projects/myproject/locations/us/connections/myconnection.
Location requirements for a remote model control where BigQuery allows the dataset, connection, and Vertex AI model to live. The dataset that contains the remote model must be in the same location as the connection used by the model. The Vertex AI model named in ENDPOINT must also be available in the location where the remote model is created. In a BigQuery multi-region US or EU dataset, a remote model can only connect to an endpoint deployed in a region inside that same multi-region, although specifying the global endpoint for supported Gemini models can improve availability across regions.
Exam tip: The dataset location must match the connection location, and the Vertex AI model must be available in that same location.
The ENDPOINT option in a remote model tells BigQuery which pretrained or deployed model should handle the request. Traffic flows from the BigQuery SQL function to the remote model, then through the connection to the Vertex AI endpoint named in ENDPOINT. Different model families use different endpoint formats, and custom models (opens in a new tab) also require explicit input and output definitions.
| Model family | ENDPOINT value to specify | Example |
|---|---|---|
| Gemini, embedding, Claude, and Mistral AI models | Pretrained model name, optionally with version | gemini-2.0-flash |
| Llama models | OpenAI API style path with publisher and model | openapi/meta/llama-3.1-405b-instruct-maas |
| Custom or manually deployed open model | Full Vertex AI endpoint URL with endpoint ID | https://us-central1-aiplatform.googleapis.com/v1/projects/PROJECT_ID/locations/us-central1/endpoints/ENDPOINT_ID |
A remote model over a custom model also requires explicit INPUT and OUTPUT clauses that exactly match the deployed model's field names and types. For example, INPUT (text STRING) with OUTPUT (scores ARRAY<FLOAT64>) defines a text-in, scores-out interface, and BigQuery rejects calls if the names or types differ. Remote models over Cloud AI services use a different option, REMOTE_SERVICE_TYPE, such as OPTIONS (REMOTE_SERVICE_TYPE = 'CLOUD_AI_TRANSLATE_V3'), instead of a foundation-model ENDPOINT name.
A BigQuery Cloud resource connection is an authorized and encrypted path that lets BigQuery send requests to Google Cloud resources including Vertex AI remote models, remote functions, and BigLake. Authentication uses a system service account created automatically when the connection is created, and the administrator must grant that service account permission to use Vertex AI and grant users permission to use the connection. Once created, the connection is referenced in a CREATE MODEL statement to create a remote model, so data flows from a BigQuery SQL query to Vertex AI for inference and results flow back into BigQuery.
General-purpose AI functions give you full control because you choose the model, prompt, and parameters, while managed AI functions let BigQuery choose a model to optimize cost and quality. Use general-purpose AI functions when the task needs full control and transparency over generation, and use managed AI functions for routine filtering, rating, or classification.
A BigQuery ML remote model over pretrained large language models (LLMs) is created with a CREATE MODEL statement that includes a REMOTE WITH CONNECTION clause and an ENDPOINT option naming the foundation model such as a Gemini model. The dataset that contains the remote model must be in the same location as the connection used by the model, and the Vertex AI model named in the endpoint must also be available in that same location.
A data team is expanding their data warehouse capabilities by integrating external machine learning capabilities into BigQuery. They have set up a BigQuery Cloud resource connection named my_connection in the us multi-region and granted the associated service account the required Vertex AI permissions.
Which Data Definition Language (DDL) statement should the team run in BigQuery to register a remote model that connects through this external connection?
CREATE OR REPLACE MODEL my_project.my_dataset.remote_llm TRANSFORM (REMOTE_CONNECTION = 'my_project.us.my_connection') AS SELECT * FROM my_project.my_dataset.training_data;
CREATE OR REPLACE MODEL my_project.my_dataset.remote_llm REMOTE WITH CONNECTION my_project.us.my_connection OPTIONS (ENDPOINT = 'https://us-central1-aiplatform.googleapis.com/v1/projects/my_project/locations/us-central1/publishers/google/models/text-bison');
CREATE OR REPLACE MODEL my_project.my_dataset.remote_llm IMPORT FROM CONNECTION my_project.us.my_connection OPTIONS (MODEL_TYPE = 'VERTEX_AI');
CREATE OR REPLACE EXTERNAL TABLE my_project.my_dataset.remote_llm WITH CONNECTION my_project.us.my_connection OPTIONS (ENDPOINT = 'https://us-central1-aiplatform.googleapis.com/v1/...');
Last updated