How to Query BigQuery Using bigquery_fdw in PostgreSQL

Galaxy Glossary

How do I query BigQuery data from PostgreSQL using open-source tools?

bigquery_fdw lets PostgreSQL run SQL against Google BigQuery through an open-source Foreign Data Wrapper.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why use bigquery_fdw to query BigQuery?

bigquery_fdw removes ETL overhead. You can join BigQuery datasets with local PostgreSQL tables in one statement, keeping your BI stack simple and lowering data-latency.

How do I install bigquery_fdw?

Install the extension package, then run CREATE EXTENSION bigquery_fdw; in the target database. The server must have libpq, Google Cloud SDK, and valid service-account JSON credentials.

What is the basic setup syntax?

Create a foreign server that points at your GCP project and dataset, add a user mapping with a credential file path, then import or create foreign tables that mirror the BigQuery schema.

How do I query BigQuery tables from PostgreSQL?

Once the foreign tables exist, use regular SELECT, JOIN, and WHERE clauses. PostgreSQL pushes down filters and projections so only necessary rows come back.

Which ecommerce use cases are common?

Blend BigQuery click-stream data with local Orders to run funnel analyses, enrich customer profiles, or validate nightly ETL jobs by comparing row counts.

What are best practices?

Limit columns, add WHERE clauses, and set costs on foreign tables to reflect BigQuery pricing. Cache cold data locally with CREATE MATERIALIZED VIEW for faster repeated reads.

Common mistakes and fixes

Missing credentials: Verify the service account has BigQuery Data Viewer. Slow queries: Add predicates or aggregate in PostgreSQL instead of pulling entire tables.

Need more help?

Review the bigquery_fdw GitHub README, or run \help CREATE SERVER inside psql for parameter details.

Why How to Query BigQuery Using bigquery_fdw in PostgreSQL is important

How to Query BigQuery Using bigquery_fdw in PostgreSQL Example Usage


-- Join local Orders with BigQuery Products to compute revenue per product
SELECT p.name,
       SUM(oi.quantity * p.price) AS revenue
FROM   Orders      o
JOIN   OrderItems  oi ON oi.order_id   = o.id
JOIN   Products    p  ON p.id          = oi.product_id   -- remote BigQuery table
WHERE  o.order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP  BY p.name
ORDER  BY revenue DESC;

How to Query BigQuery Using bigquery_fdw in PostgreSQL Syntax


-- 1. Enable extension
CREATE EXTENSION IF NOT EXISTS bigquery_fdw;

-- 2. Create server
CREATE SERVER bigquery_svr
  FOREIGN DATA WRAPPER bigquery_fdw
  OPTIONS (
    project       'my-gcp-project',
    dataset       'ecommerce',
    location      'US',             -- Optional, BigQuery location
    suppress_pull 'true'            -- Do not auto-import on connect
  );

-- 3. Map user to a service-account key
CREATE USER MAPPING FOR current_user
  SERVER bigquery_svr
  OPTIONS (
    keyfile '/var/keys/bq-sa.json'
  );

-- 4. Import remote schemas or define tables manually
IMPORT FOREIGN SCHEMA ecommerce
  LIMIT TO (Customers, Orders, Products, OrderItems)
  FROM SERVER bigquery_svr INTO public;

Common Mistakes

Omitting <code>CREATE EXTENSION bigquery_fdw;</code>. PostgreSQL cannot reference the FDW library until the extension is loaded. Run the command once per database.
Providing a service-account key without BigQuery viewer permissions. Grant roles/bigquery.dataViewer (or higher) to avoid “access denied” errors.