Postgres-Compatible Query Layer for BigQuery and Snowflake
You can use PeerDB’s Postgres-compatible SQL layer to query tables in BigQuery or Snowflake. You can leverage Postgres’ hundreds of integrations, including client tools like pgadmin and psql, BI tools like Grafana and Power BI, and languages such as Python and Ruby to query data in BigQuery or Snowflake.
PeerDB parses and translates the incoming SQL query to make it compatible with the appropriate Peer. It pushes down most SQL constructs, including filters, JOINs, aggregates, sorts, limits etc., to the attached Peer. This enables blazing fast performance.
You can also query tables in BigQuery or Snowflake from your existing Postgres database by connecting PeerDB as a
postgres_fdw FOREIGN SERVER.
You can utilize postgres_fdw’s advance push-down capabilities to maximize the performance of your queries involving BigQuery and Snowflake tables. For queries where push-down is important, you can expect 100x performance gains compared to other foreign data wrappers.
Let’s go through steps to query BigQuery and Snowflake using PeerDB.
Step 1: Add BigQuery and Snowflake Peers
Run the following commands to let PeerDB know about the existing Postgres and Snowflake Peers:
-- Connect to PeerDB psql "port=9900 host=localhost password=peerdb" -- Add Postgres and BigQuery peers CREATE PEER bigquery_peer FROM bigquery (...); CREATE PEER snowflake_peer FROM snowflake (...);
Make sure to replace
(…) with the appropriate connection details for both the Postgres and Snowflake instances. More details on adding PEERs are available here.
Step 2: Query Tables in BigQuery and Snowflake
-- Querying table(s) in BigQuery PEER SELECT country, count(*), sum(id), count(distinct id), avg(id), max(id), min(id) FROM bigquery_peer.users WHERE id = 1 AND country = 'India' GROUP BY country HAVING count(*) > 0 ORDER BY country DESC LIMIT 1; -- Querying table(s) in Snowflake PEER SELECT count(*) FROM snowflake_peer.PUBLIC.EVENTS e RIGHT JOIN snowflake_peer.PUBLIC.USERS u ON u.id = e.user_id JOIN sf_test.PUBLIC.USERS u1 ON u1.id = u.id WHERE u1.country = 'Indonesia';
You can find different variations of queries that should work out of the box for BigQuery and Snowflake in the provided links. Currently, only SELECT commands are supported, while DML and DDL commands are not yet.
Query BigQuery and Snowflake using
PeerDB enables you to query BigQuery or Snowflake from your existing Postgres database using
postgres_fdw. As PeerDB is Postgres wire-compatible, you can connect to PeerDB as a postgres_fdw FOREIGN SERVER from your Postgres database and start querying tables in Peers that have been created on PeerDB.
Let’s walk through the steps to query tables in BigQuery from your existing Postgres database using PeerDB.
Step 1: Create PeerDB as a FOREIGN SERVER to your Postgres database
-- Creating PeerDB as a Foreign Server -- if your postgres database is not running on the same machine make sure to open up the inbound port on the vm peerdb is running. CREATE SERVER bigquery FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '<peerdb_hostname>', port '9900'); -- Creating a user mapping CREATE USER MAPPING FOR postgres SERVER bigquery OPTIONS (user 'peerdb', password 'peerdb');
Step 2: Create FOREIGN TABLE on Postgres pointing to tables in BigQuery
-- Creating a FOREIGN TABLE pointing to BigQuery via PeerDB CREATE FOREIGN TABLE events_bigquery ( id int, user_id int, event_type text, os text, device text, ip text, time_spent int, domain_name text, payload text ) SERVER bigquery OPTIONS (schema_name 'bigquery_peer', table_name 'events');
Step 3: Start querying BigQuery FOREIGN TABLE from Postgres
SELECT count(*) FROM events_bigquery; SELECT count(*) FROM events_bigquery WHERE country = 'USA'; SELECT * FROM events_bigquery LIMIT 5;
Step 4: Join local Postgres tables with the BigQuery FOREIGN TABLE
SELECT count(*) FROM events_bigquery e JOIN users u ON e.user_id = u.id;