Sync BigQuery data to AlloyDB

This page shows you how to sync tables from BigQuery into your AlloyDB for PostgreSQL instance.

By syncing analytical data from BigQuery into AlloyDB, you can build operational systems that benefit from low-latency, transactional access to your data lake. Unlike a foreign data wrapper (FDW) which queries data in place, sync table moves the data into AlloyDB storage for maximum performance.

AlloyDB provides the following ways to move BigQuery data into your instance:

One-time sync: creates a writable, independent copy of your BigQuery table.
Periodic sync (mirroring): creates a read-only local table that automatically refreshes on a schedule—for example, every 6 hours or daily.

Performance and operational considerations

When you use BigQuery sync tables, consider the following:

Resource usage: data movement consumes CPU and memory. For very large tables, consider scheduling syncs during off-peak hours to avoid affecting your primary transactional workload.
Data visibility: during a replace operation, the existing target table is dropped and recreated upfront. Queries during the import see an empty table initially, followed by newly imported data appearing incrementally as batch transactions commit.

Before you begin

Familiarize yourself with how the bigquery_fdw handles BigQuery data types and column mappings, because the alloydb_sync extension uses bigquery_fdw to connect to BigQuery.

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.

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 the resourcemanager.projects.create permission. Learn how to grant roles.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

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 the resourcemanager.projects.create permission. Learn how to grant roles.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

Enable the Cloud APIs necessary to create and connect to AlloyDB.

Enable the APIs
To confirm the name of the project you are going to make changes to, in the Confirm project step, click Next.
In the Enable APIs step, click Enable to enable the following:
- AlloyDB API
- Compute Engine API
- Cloud Resource Manager API
- Service Networking API
- BigQuery Storage API
The Service Networking API is required if you plan to configure network connectivity to AlloyDB using a VPC network that resides in the same Google Cloud project as AlloyDB.

The Compute Engine API and Cloud Resource Manager API are required if you plan to configure network connectivity to AlloyDB using a VPC network that resides in a different Google Cloud project.
Ensure you have an existing BigQuery table to sync data from. For more information, see Create and use BigQuery tables.

Required roles

To grant the BigQuery dataset access to the AlloyDB cluster service account, you need the following permissions:

BigQuery Data Viewer (roles/bigquery.dataViewer) or any custom role with permissions bigquery.tables.get and bigquery.tables.getData. When granted on a service account, this role provides permissions to read data and metadata from the table or view.
BigQuery Read Session User (roles/bigquery.readSessionUser) or any custom role with permissions bigquery.readsessions.create and bigquery.readsessions.getData. Provides the ability to create and use read sessions.
BigQuery Job User (roles/bigquery.jobUser) or any custom role with permissions bigquery.jobs.create. Provides the ability to create and run jobs, including query jobs.

Configure the extension

Before you sync tables from BigQuery, enable the required extension and configure the connection to BigQuery.

Create the extension.
1. Connect to the AlloyDB instance using the psql client by following the instructions in Connect a psql client to an instance.
2. Run the following command:
```
CREATE EXTENSION IF NOT EXISTS alloydb_sync;
```
  Note: You must create these extensions in every database where you want to sync BigQuery data.
To let AlloyDB authenticate with BigQuery, create the user mapping.
```
CREATE EXTENSION IF NOT EXISTS bigquery_fdw;
CREATE SERVER IF NOT EXISTS BIGQUERY_SERVER_NAME FOREIGN DATA WRAPPER bigquery_fdw;
CREATE USER MAPPING IF NOT EXISTS FOR USER SERVER BIGQUERY_SERVER_NAME;
```
Replace the following:
- USER: a database username or an IAM user that accesses the BigQuery table.
- BIGQUERY_SERVER_NAME: unique identifier for the BigQuery server. Define this once in a given database. You can replace BIGQUERY_SERVER_NAME with your server name.

Sync a BigQuery table for one-time export

You can sync a BigQuery table for one-time export using psql.

Sync a BigQuery table one-time using psql

To create an editable copy of BigQuery data, use psql to run the alloydb_sync.import_bq_table function.

SELECT alloydb_sync.import_bq_table(
  'PROJECT_ID.DATASET_ID.TABLE_ID',
  'ALLOYDB_DESTINATION_TABLE_NAME',
  'ON_EXISTS',
  ARRAY['PRIMARY_KEY_COLUMN']
);

Replace the following:

PROJECT_ID: the ID of the project where the BigQuery dataset resides.
DATASET_ID: the name of the BigQuery dataset for the table. For Iceberg tables with a 4-part name, this is the Catalog.Namespace.
TABLE_ID: the name of the BigQuery table or view.
ALLOYDB_DESTINATION_TABLE_NAME: the name of the local table in the AlloyDB database to create and import data into. You can include the schema name—for example, public.local_sales.
ON_EXISTS: the strategy to use if the destination table already exists.
PRIMARY_KEY_COLUMN: an optional list of column names to use as the primary key.

Example

The following example shows how to sync a table named transactions from a BigQuery dataset into a new AlloyDB table named public.local_sales:

SELECT alloydb_sync.import_bq_table(
    'my-gcp-project.sales_data.transactions',
    'public.local_sales',
    'replace'
);

`on_exists` parameter

The on_exists parameter determines how the function handles the sync if the destination table already exists in AlloyDB:

error: the default option. Stops the sync if the destination table already exists.
skip: skips the sync if the destination table already exists.
replace: replaces the existing local table with fresh data from BigQuery.

Primary key support

If you provide the optional primary_key parameter as a text array, AlloyDB creates the table with the specified columns as the primary key.

SELECT alloydb_sync.import_bq_table(
    'my-gcp-project.sales_data.transactions',
    'public.local_sales',
    ARRAY['transaction_id']
);

Sync a BigQuery table for periodic export

You can sync a BigQuery table for periodic export by using psql.

Create a periodic sync

To maintain a read-only table that stays synced with BigQuery data, use psql to run the alloydb_sync.create_bq_sync_table function.

SELECT alloydb_sync.create_bq_sync_table(
    'PROJECT_ID.DATASET_ID.TABLE_ID',
    'ALLOYDB_DESTINATION_TABLE_NAME',
    'REFRESH_INTERVAL',
    'ON_EXISTS',
    ARRAY['PRIMARY_KEY_COLUMN']
);

Replace the following:

PROJECT_ID.DATASET_ID.TABLE_ID: The fully qualified name of the BigQuery table or view, including the project ID, dataset ID, and table ID, separated by periods. For Iceberg tables with a 4-part name, the DATASET_ID is represented as Catalog.Namespace. For example, my-gcp-project.sales_data.transactions.
ALLOYDB_DESTINATION_TABLE_NAME: the name of the local table in the AlloyDB database to create and sync data into.
REFRESH_INTERVAL: the interval at which AlloyDB periodically refreshes data from BigQuery—for example, 12 hours.
ON_EXISTS: the strategy to use if the destination table already exists.
PRIMARY_KEY_COLUMN: an optional list of column names to use as the primary key.

Example

The following example shows how to create a customer profile mirror that refreshes every 12 hours:

SELECT alloydb_sync.create_bq_sync_table(
    'my-gcp-project.crm_data.profiles',
    'public.customer_mirror',
    '12 hours',
    'replace'
);

Monitor and manage jobs

After you initiate a sync, you can monitor its progress and manage the jobs.

Check job status

Large syncs can take time. You can monitor progress, including records processed and estimated completion time, by querying the job_status view:

SELECT
    import_id,
    status,
    records_processed,
    total_records,
    error
FROM alloydb_sync.job_status;

For example, to cancel the job, run the following command:

SELECT alloydb_sync.cancel_import_job('85bb5dfa-dfb9-4017-9153-738f55abe4b1');

Stop and delete a sync job

To stop mirroring a BigQuery table and delete the local table, use the alloydb_sync.delete_bq_sync_table function:

SELECT alloydb_sync.delete_bq_sync_table('public.customer_mirror');

Limitations

The following limitations apply when syncing tables from BigQuery:

This feature is supported for PostgreSQL version 18 only.
If you DROP the alloydb_sync extension, you must restart the instance before creating the extension again.
Syncs run within a transaction. If the import job is interrupted or fails, the system rolls back the imported data.
If two users start sync jobs at the same time with the same target tables, the tables might overwrite each other.
If any interruption occurs during the initial background import for a newly registered sync table, the table remains incomplete until its next scheduled refresh interval. To resolve this, you can delete the sync table using the alloydb_sync.delete_bq_sync_table() function and recreate it.
Complex BigQuery types such as ARRAY, BYTES, VECTOR, and GEOGRAPHY aren't supported for synchronization. For a full list, see Supported BigQuery data types and column mappings.
Don't manually drop a replicated table. Use the alloydb_sync.delete_bq_sync_table() API function to safely drop the table and refreshes.
To drop a database that uses the alloydb_sync extension, you must use DROP DATABASE ... WITH (FORCE).
If the Postgres database crashes while an import is running, the metadata might become stuck in the RUNNING state, blocking future imports. You must manually run UPDATE alloydb_sync.import_job_status SET status = 'FAILED' WHERE status = 'RUNNING'; to unblock it.

Pricing

When you sync data from BigQuery to AlloyDB, you are billed using BigQuery capacity compute pricing.

After the data is exported, you're charged for storing the data in AlloyDB. For more information, see AlloyDB for PostgreSQL pricing.

What's next

Learn more about Export data to AlloyDB (reverse ETL).
Learn more about querying BigQuery data in-place using FDW.

Sync BigQuery data to AlloyDB Stay organized with collections Save and categorize content based on your preferences.

Performance and operational considerations

Before you begin

Required roles

Configure the extension

Sync a BigQuery table for one-time export

Sync a BigQuery table one-time using psql

Example

on_exists parameter

Primary key support

Sync a BigQuery table for periodic export

Create a periodic sync

Example

Monitor and manage jobs

Check job status

Stop and delete a sync job

Limitations

Pricing

What's next

Sync BigQuery data to AlloyDB

`on_exists` parameter