PostgreSQL to BigQuery

-- Connect to PeerDB
psql "port=9900 host=localhost password=peerdb"

-- Add PostgreSQL and BigQuery peers
CREATE PEER postgres_peer FROM postgres (...);
CREATE PEER bigquery_peer FROM bigquery (...);

-- Customizable ETL from PostgreSQL to BigQuery
CREATE MIRROR streaming_query_pg_to_bq FROM
	postgres_peer TO bigquery_peer FOR
$$
  SELECT * FROM events WHERE
  updated_at BETWEEN {{.start}} AND {{.end}}
$$
WITH (
	destination_table_name = 'events',
	watermark_column = 'updated_at',
	watermark_table_name = 'public.events',
	mode = 'upsert',
	unique_key_columns = 'id',
	parallelism = 64,
	refresh_interval = 30,
	num_rows_per_partition = 50000
);

-- Validate the mirror
SELECT COUNT(*) FROM postgres_peer.events;
SELECT COUNT(*) FROM bigquery_peer.events;

-- drop the mirror
DROP MIRROR <mirror_name>;