Skip to main content

BigQuery Foreign Data Wrapper for PostgreSQL

Project description

bigquery_fdw: BigQuery Foreign Data Wrapper for PostgreSQL
==========================================================

`Pypi <https://pypi.org/project/bigquery-fdw/>`__ `Build
Status <https://travis-ci.org/gabfl/bigquery_fdw>`__
`codecov <https://codecov.io/gh/gabfl/bigquery_fdw>`__ `MIT
licensed <https://raw.githubusercontent.com/gabfl/bigquery_fdw/master/LICENSE>`__

bigquery_fdw is a BigQuery foreign data wrapper for PostgreSQL using
`Multicorn <https://github.com/Kozea/Multicorn>`__.

It allows to write queries in PostgreSQL SQL syntax using a foreign
table. It supports most of BigQuery’s `data
types <docs/data_types.md>`__ and `operators <docs/operators.md>`__.

Features and limitations
------------------------

- Table partitioning is supported. `You can use partitions in your SQL
queries <docs/table_partitioning.md>`__.
- Queries are parameterized when sent to BigQuery
- BigQuery’s standard SQL support (legacy SQL is not supported)
- Authentication works with a “`Service
Account <docs/service_account.md>`__” Json private key

`Read more <docs/README.md>`__.

Requirements
------------

- PostgreSQL >= 9.5
- Python 3

Get started
-----------

Using docker
~~~~~~~~~~~~

See `getting started with Docker <docs/docker.md>`__

Installation on Debian/Ubuntu
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Dependencies required to install bigquery_fdw:
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

You need to install the following dependencies:

.. code:: bash

# 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``.

Installation
^^^^^^^^^^^^

.. code:: bash

# 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

Major dependencies installed automatically during the installation
process:

- `Google Cloud
BigQuery <https://pypi.org/project/google-cloud-bigquery/>`__
- `Multicorn <https://github.com/Kozea/Multicorn>`__

Usage
-----

We recommend testing the `BigQuery client
connectivity <docs/test_client.md>`__ before trying to use the FDW.

With ``psql``:

.. code:: sql

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 <docs/remo |
| | | te_grouping.md>`__. |
+--------------------------+---------------------+---------------------+
| ``fdw_casting`` | - | See |
| | | `Casting <docs/cast |
| | | ing.md>`__. |
+--------------------------+---------------------+---------------------+
| ``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 <docs/README.md>`__.


Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Files for bigquery-fdw, version 1.5
Filename, size File type Python version Upload date Hashes
Filename, size bigquery_fdw-1.5-py2.py3-none-any.whl (9.8 kB) File type Wheel Python version py2.py3 Upload date Hashes View hashes
Filename, size bigquery-fdw-1.5.tar.gz (9.3 kB) File type Source Python version None Upload date Hashes View hashes

Supported by

Elastic Elastic Search Pingdom Pingdom Monitoring Google Google BigQuery Sentry Sentry Error logging AWS AWS Cloud computing DataDog DataDog Monitoring Fastly Fastly CDN DigiCert DigiCert EV certificate StatusPage StatusPage Status page