bigquery-fdw 1.1

BigQuery Foreign Data Wrapper for PostgreSQL

bigquery\_fdw: BigQuery Foreign Data Wrapper for PostgreSQL
===========================================================

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

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

- PostgreSQL >= 9.5
- Python 3

Dependencies
------------

Dependencies required to install bigquery\_fdw:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

- ``postgresql-server-dev-X.Y``
- ``python3-pip``
- ``python3-dev``
- ``make``
- ``gcc``

Major dependencies installed automatically during the installation process:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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

Installation
------------

::

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

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

# Install bigquery_fdw
pip3 install bigquery-fdw

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/user.json'
);

Options
-------

List of options implemented in ``CREATE FOREIGN TABLE`` syntax:

+--------+-------+-------+
| Option | Defau | Descr |
| | lt | iptio |
| | | n |
+========+=======+=======+
| ``fdw_ | - | BigQu |
| datase | | ery |
| t`` | | datas |
| | | et |
| | | name |
+--------+-------+-------+
| ``fdw_ | - | BigQu |
| table` | | ery |
| ` | | table |
| | | name |
+--------+-------+-------+
| ``fdw_ | - | Path |
| key`` | | to |
| | | priva |
| | | te |
| | | Json |
| | | key |
| | | (See |
| | | `Key |
| | | stora |
| | | ge |
| | | recom |
| | | menda |
| | | tions |
| | | <doc |
| | | s/key |
| | | _stor |
| | | age.m |
| | | d>`__ |
| | | ) |
+--------+-------+-------+
| ``fdw_ | - | Conve |
| conver | | rt |
| t_tz`` | | BigQu |
| | | ery |
| | | time |
| | | zone |
| | | for |
| | | dates |
| | | and |
| | | times |
| | | tamps |
| | | to |
| | | selec |
| | | ted |
| | | time |
| | | zone. |
| | | Examp |
| | | le: |
| | | ``'US |
| | | /East |
| | | ern'` |
| | | `. |
+--------+-------+-------+
| ``fdw_ | ``'fa | See |
| group` | lse'` | `Remo |
| ` | ` | te |
| | | group |
| | | ing |
| | | and |
| | | count |
| | | ing < |
| | | remot |
| | | e_gro |
| | | uping |
| | | .md>` |
| | | __. |
+--------+-------+-------+
| ``fdw_ | ``'fa | Set |
| verbos | lse'` | to |
| e`` | ` | ``'tr |
| | | ue'`` |
| | | to |
| | | outpu |
| | | t |
| | | debug |
| | | infor |
| | | matio |
| | | n |
| | | in |
| | | Postr |
| | | geSQL |
| | | 's |
| | | logs |
+--------+-------+-------+
| ``fdw_ | ``'st | BigQu |
| sql_di | andar | ery |
| alect` | d'`` | SQL |
| ` | | diale |
| | | ct. |
| | | Curre |
| | | ntly |
| | | only |
| | | ``sta |
| | | ndard |
| | | `` |
| | | is |
| | | suppo |
| | | rted. |
+--------+-------+-------+

Table partitioning
------------------

BigQuery **table partitioning is supported**. When partitioning a table,
BigQuery creates a pseudo column called ``_PARTITIONTIME``.

To use partitions, you need to add a column ``partition_date`` with the
type ``date`` in the foreign table definition, for example:

.. code:: sql

CREATE FOREIGN TABLE my_bigquery_table (
column1 text,
column2 bigint,
partition_date date -- <-- partition!
) SERVER bigquery_srv
OPTIONS (
fdw_dataset 'my_dataset',
fdw_table 'my_table',
fdw_key '/opt/bigquery_fdw/user.json'
);

You can then use the partition in the ``WHERE`` clause:

.. code:: sql

SELECT column1, column2
FROM my_bigquery_table
WHERE column1 = 'abc' AND partition_date = '2017-12-01'

More documentation
------------------

See `/docs </docs>`__.