Default connection overview

To simplify your workflow, you can configure default Cloud resource connections in BigQuery for creating tables and models.

BigQuery supports default connections in the following tables and models:

Administrators configure the default Cloud resource connection for a project.

If you create tables and models in a project, you can choose the default connection that administrators have configured by specifying DEFAULT in the following SQL clauses:

If you're not an administrator, you cannot modify the default connection's permissions or other configurations. Instead, the default connection automatically resolves to an existing connection referenced in the project's configuration, giving the connection the required permissions and resources.

Before you begin

Enable the BigQuery Connection API.

Enable the API

Required roles and permissions

For the required roles and permissions to configure and use default connections in BigQuery, see Manage connections.

Permissions provisioning when using CONNECTION DEFAULT

Depending on the type of table or model you create with the default connection, Google Cloud automatically updates the roles assigned to the service account of the connection. The following roles are automatically given to the service account for the connection for these tables and models:

Type of table or model Remote resource Roles assigned to the connection's service account
Cloud Storage BigLake table Cloud Storage roles/storage.legacyBucketReader
roles/storage.legacyObjectReader
Object Table Cloud Storage roles/storage.legacyBucketReader
roles/storage.legacyObjectReader
BigQuery tables for Iceberg Cloud Storage roles/storage.legacyBucketWriter
roles/storage.legacyObjectOwner
BigQuery ML remote models over Vertex AI models Google owned models roles/aiplatform.user
Deployable to an endpoint from Model Garden
User models
Fine tuned models roles/aiplatform.serviceAgent
BigQuery ML remote models over Cloud AI services Document processor roles/documentai.apiUser
Speech recognizer roles/speech.serviceAgent
Cloud NLP roles/serviceusage.serviceUsageConsumer
Cloud Vision roles/serviceusage.serviceUsageConsumer
Cloud Translation roles/cloudtranslate.user

Configure the default connection for a project

As an administrator, configure the default Cloud resource connection for the project:

  • To create and configure the default Cloud resource connection, use the ALTER PROJECT SET OPTIONS DDL statement.

    The following example configures the default connection for the project:

    ALTER PROJECT PROJECT_ID
SET OPTIONS (
`region-REGION.default_cloud_resource_connection_id` = CONNECTION_ID);

    Replace the following:

    • PROJECT_ID: the ID of the project where you're setting the default connection.
    • REGION: the region of the connection.
    • CONNECTION_ID: the ID or name of the connection to use as the default for tables and models. Only specify the connection ID or name, and exclude the project ID and region prefixes attached to the name or ID.

For more information about configuring a default connection for a project, see Manage default configurations.

Create tables using CONNECTION DEFAULT

The following examples show how to create tables specifying WITH CONNECTION DEFAULT in BigQuery.

Example: Create a Cloud Storage BigLake table

The following SQL expression creates a Cloud Storage BigLake table with a default connection:

CREATE EXTERNAL TABLE PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME
WITH CONNECTION DEFAULT
OPTIONS (
  format = 'TABLE_FORMAT',
  uris = ['BUCKET_PATH']);

Example: Create an object table with a default connection

The following SQL expression creates an object table with a default connection:

CREATE EXTERNAL TABLE PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME
WITH CONNECTION DEFAULT
OPTIONS (
  object_metadata = 'SIMPLE'
  uris = ['BUCKET_PATH']);

Example: Create a BigQuery table for Iceberg with a default connection

The following SQL expression creates a BigQuery table for Iceberg with a default connection:

CREATE TABLE `myproject.tpch_clustered.nation` (
  n_nationkey integer,
  n_name string,
  n_regionkey integer,
  n_comment string)
CLUSTER BY n_nationkey
WITH CONNECTION DEFAULT
OPTIONS (
  file_format = 'PARQUET',
  table_format = 'ICEBERG',
  storage_uri = 'gs://mybucket/warehouse/nation');

Create models using REMOTE WITH CONNECTION DEFAULT

The following examples show how to create models specifying REMOTE WITH CONNECTION DEFAULT in BigQuery.

Example: Create a remote model over a Vertex AI model

The following SQL expression creates a remote model with a default connection:

CREATE OR REPLACE MODEL `mydataset.flash_model`
  REMOTE WITH CONNECTION DEFAULT
  OPTIONS(ENDPOINT = 'gemini-1.5-flash-001');

Example: Create a remote model over a Cloud AI service

The following SQL expression creates a remote model SERVICE with a default connection:

CREATE MODEL `project_id.mydataset.mymodel`
REMOTE WITH CONNECTION DEFAULT
 OPTIONS(REMOTE_SERVICE_TYPE = 'CLOUD_AI_VISION_V1')

Example: Create a remote model with an HTTPS endpoint

The following SQL expression creates a remote model with an HTTPS endpoint and a default connection:

CREATE MODEL `project_id.mydataset.mymodel`
 INPUT(f1 INT64, f2 FLOAT64, f3 STRING, f4 ARRAY)
 OUTPUT(out1 INT64, out2 INT64)
 REMOTE WITH CONNECTION DEFAULT
 OPTIONS(ENDPOINT = 'https://us-central1-aiplatform.googleapis.com/v1/projects/myproject/locations/us-central1/endpoints/1234')

What's next