BigQuery Foreign Data Wrapper for PostgreSQL
Project description
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
Requirements
- PostgreSQL >= 9.5
- Python 3
Get started
Using docker
Installation on Debian/Ubuntu
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-13 python3-setuptools python3-dev make gcc git
For PostgresSQL 9.X, install postgresql-server-dev-9.X instead of postgresql-server-dev-13.
Installation
# Install Multicorn git clone git://github.com/Segfault-Inc/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:
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 rec ommendations) | |
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
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Filename, size | File type | Python version | Upload date | Hashes |
---|---|---|---|---|
Filename, size bigquery_fdw-1.7-py2.py3-none-any.whl (10.7 kB) | File type Wheel | Python version py2.py3 | Upload date | Hashes View |
Filename, size bigquery-fdw-1.7.tar.gz (9.7 kB) | File type Source | Python version None | Upload date | Hashes View |
Close
Hashes for bigquery_fdw-1.7-py2.py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | f2df13faf610b2cc3e55f8694fdb52879963b5c5d8742a2218e80e5d3f43a9ed |
|
MD5 | 0f1decb2041e6d1877f79a8ed696c994 |
|
BLAKE2-256 | 5656ee6e08cde8f5d04ff40b1830ee83a5d1fa3e7aac5afb4bbdbd5b3457cec0 |