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.
- 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
- PostgreSQL >= 9.5
- Python 3
postgresql-server-dev-X.Ypython3-pippython3-devmakegcc
# 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-fdwWe 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/user.json'
);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) |
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. |