How to stage bigquery_fdw on a test instance

If you would like to stage bigquery_fdw on a test instance before installing it on your dev/production servers, here is a quick how-to:

Stage a Debian/Ubuntu instance

Stage a new Debian/Ubuntu instance on your preferred cloud provider.

Install PostgreSQL 10

apt-get update
apt-get -y install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list

apt-get update
apt-get -y install postgresql-10

Install dependencies

apt-get update
apt-get -y install git make gcc python3-dev python3-setuptools postgresql-server-dev-10

easy_install3 pip

Install Multicorn

pip3 install --upgrade setuptools

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

Install bigquery_fdw

From Pypi

pip3 install bigquery-fdw

Or install the latest version from Github

cd
git clone git://github.com/gabfl/bigquery_fdw.git && cd bigquery_fdw
python3 setup.py install

Create BigQuery credential file

Create a BigQuery "Service Account" (see https://github.com/gabfl/bigquery_fdw/blob/master/docs/service_account.md) and save it to /opt/bigquery_fdw/user.json:

mkdir /opt/bigquery_fdw
nano /opt/bigquery_fdw/user.json # <-- fill with BigQuery Json

Create a foreign table

su postgres
psql

-- Create a database called `fdw`
CREATE DATABASE fdw;

-- Connect to the database
\c fdw

-- Create extention
CREATE EXTENSION multicorn;

-- Create connector to bigquery_fdw
CREATE SERVER bigquery_srv FOREIGN DATA WRAPPER multicorn
OPTIONS (
    wrapper 'bigquery_fdw.fdw.ConstantForeignDataWrapper'
);

-- Create a table to match your bigquery table
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'
);

-- Query your table
SELECT *
FROM my_bigquery_table
WHERE [...]
LIMIT 10;