bigquery-fdw 1.4

BigQuery Foreign Data Wrapper for PostgreSQL

Pypi Build Status codecov MIT licensed

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

Dependencies

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-10 python3-setuptools python3-dev make gcc git

# Install pip3
easy_install3 pip

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

Major dependencies installed automatically during the installation process:

• Google Cloud BigQuery

• Multicorn

Installation

# Install `setuptools` if necessary
pip3 install --upgrade setuptools

# Install Multicorn
git clone git://github.com/Kozea/Multicorn.git && cd Multicorn
export PYTHON_OVERRIDE=python3
make && make install

# Install bigquery_fdw
pip3 install bigquery-fdw

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',
    fdw_key '/opt/bigquery_fdw/key.json'
);

Options

List of options implemented in CREATE FOREIGN TABLE syntax:

Option	Default	Description
fdw_dataset		BigQuery dataset name
fdw_table		BigQuery table name
fdw_key		Path to private Json key (See Key storage recommendations <do cs/key_storage.md> __)
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.

More documentation

See bigquery_fdw documentation.