bigquery_fdw: BigQuery Foreign Data Wrapper for PostgreSQL

bigquery_fdw is a BigQuery foreign data wrapper for PostgreSQL using Multicorn.

It allows to write queries in PostgreSQL SQL syntax using a foreign table. It supports most of BigQuery's data types and operators.

Features and limitations

• Table partitioning is supported. You can use partitions in your SQL queries.
• Queries are parameterized when sent to BigQuery
• BigQuery's standard SQL support (legacy SQL is not supported)
• Authentication works with a "Service Account" Json private key

Read more.

Requirements

• PostgreSQL >= 9.5
• Python >= 3.4

⚠️ Migrating to version 1.8 from versions 1.7 and below

Starting with version 1.8, the fdw_key option is deprecated and replaced with a default environment variable. See Authentication.

Get started

Using docker

See getting started with Docker

Installation on Debian/Ubuntu

Dependencies required to install bigquery_fdw:

You need to install the following dependencies:

# Install required packages
apt-get update
apt-get install --yes postgresql-server-dev-12 python3-setuptools python3-dev make gcc git

For PostgresSQL 9.X, install postgresql-server-dev-9.X instead of postgresql-server-dev-12.

All PostgreSQL versions from 9.2 to 12 should be supported. Building Multicorn against PostgreSQL 13 is currently not working properly (as of 1/21/2013).

Installation

# Install Multicorn
# gabfl/Multicorn is a fork of Segfault-Inc/Multicorn that adds better support for Python3.
# You may chose to build against the original project instead.
git clone git://github.com/gabfl/Multicorn.git && cd Multicorn
make && make install

# Install bigquery_fdw
pip3 install bigquery-fdw

Major dependencies installed automatically during the installation process:

• Google Cloud BigQuery
• Multicorn

Authentication

bigquery_fdw relies on Google Cloud API's default authentication.

Your need to have an environment variable GOOGLE_APPLICATION_CREDENTIALS that has to be accessible by bigquery_fdw. Setting environment variables varies depending on OS but for Ubuntu or Debian, the preferred way is to edit /etc/postgresql/[version]/main/environment and add:

GOOGLE_APPLICATION_CREDENTIALS = '/path/to/key.json'

Restarting PostgreSQL is required for the environment variable to be loaded.

Usage

We recommend testing the BigQuery client connectivity before trying to use the FDW.

With psql:

CREATE EXTENSION multicorn;

CREATE SERVER bigquery_srv FOREIGN DATA WRAPPER multicorn
OPTIONS (
    wrapper 'bigquery_fdw.fdw.ConstantForeignDataWrapper'
);

CREATE FOREIGN TABLE my_bigquery_table (
    column1 text,
    column2 bigint
) SERVER bigquery_srv
OPTIONS (
    fdw_dataset  'my_dataset',
    fdw_table 'my_table'
);

Or with the new IMPORT FOREIGN SCHEMA support

CREATE EXTENSION multicorn;

CREATE SERVER bigquery_srv FOREIGN DATA WRAPPER multicorn
OPTIONS (
    wrapper 'bigquery_fdw.fdw.ConstantForeignDataWrapper',
    fdw_colcount 'skip'
);

IMPORT FOREIGN SCHEMA public FROM SERVER bigquery_srv INTO public;

Options

List of options implemented in CREATE FOREIGN TABLE syntax:

Option	Default	Description
fdw_dataset	-	BigQuery dataset name
fdw_table	-	BigQuery table name
fdw_convert_tz	-	Convert BigQuery time zone for dates and timestamps to selected time zone. Example: 'US/Eastern'.
fdw_group	'false'	See Remote grouping and counting.
fdw_casting	-	See Casting.
fdw_verbose	'false'	Set to 'true' to output debug information in PostrgeSQL's logs
fdw_sql_dialect	'standard'	BigQuery SQL dialect. Currently only standard is supported.
fdw_colnames	'error'	What to do if more than one column in the same table has the same 63 character prefix; error, trim, and skip are supported.
fdw_colcount	'error'	What to do if your BigQuery table has >1600 columns; error, trim, and skip are supported.

Read more about fdw_colnames and fdw_colcount ordering.

More documentation

See bigquery_fdw documentation.