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

  1. 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.
  2. 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.

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

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

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

    Go to project selector

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

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

    Enable the APIs

  8. To confirm the name of the project you are going to make changes to, in the Confirm project step, click Next.

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

  10. 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. If you use the Google Cloud console, AlloyDB performs these steps automatically.

  1. 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;

  2. 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 the Google Cloud console or by using psql.

Use the Google Cloud console

To sync a BigQuery table to AlloyDB using the Google Cloud console, do the following:

  1. Open the BigQuery page in the Google Cloud console.

    Go to the BigQuery page