bigquery-fdw 2.1.2
BigQuery Foreign Data Wrapper for PostgreSQL
License: MIT

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

|Pypi| |Build Status| |codecov| |MIT licensed|

bigquery_fdw is a BigQuery foreign data wrapper for PostgreSQL using
`Multicorn <https://github.com/Segfault-Inc/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 up to 14
-  Python >= 3.4

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 update
   apt install -y postgresql-server-dev-14 python3-setuptools python3-dev make gcc git

All PostgreSQL versions from 9.2 to 14 should be supported.

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

.. code:: bash

   # Install Multicorn
   # pgsql-io/multicorn2 is a fork of Segfault-Inc/Multicorn that adds support for PostgreSQL 13/14.
   # Alternatively, up to PostgreSQL 12, you can use gabfl/Multicorn that adds better support for Python3.
   # You may also choose to build against the original project instead.
   git clone https://github.com/pgsql-io/multicorn2.git Multicorn && cd Multicorn
   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/pgsql-io/multicorn2>`__

Authentication
--------------

bigquery_fdw relies on `Google Cloud API’s default
authentication <https://cloud.google.com/docs/authentication/getting-started#linux-or-macos>`__.

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 <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'
   );

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 <docs/remo |
|                          |                     | te_grouping.md>`__. |
+--------------------------+---------------------+---------------------+
| ``fdw_casting``          | -                   | See                 |
|                          |                     | `Casting <d         |
|                          |                     | ocs/casting.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>`__.

.. |Pypi| image:: https://img.shields.io/pypi/v/bigquery-fdw.svg
   :target: https://pypi.org/project/bigquery-fdw/
.. |Build Status| image:: https://github.com/gabfl/bigquery_fdw/actions/workflows/ci.yml/badge.svg?branch=main
   :target: https://github.com/gabfl/bigquery_fdw/actions
.. |codecov| image:: https://codecov.io/gh/gabfl/bigquery_fdw/branch/main/graph/badge.svg
   :target: https://codecov.io/gh/gabfl/bigquery_fdw
.. |MIT licensed| image:: https://img.shields.io/badge/license-MIT-green.svg
   :target: https://raw.githubusercontent.com/gabfl/bigquery_fdw/main/LICENSE