Querying a PostgreSQL database from Snowflake

Question

PostgreSQL offers a way to query a remote database through dblink.

Similarly (sort-of), Exasol provides a way to connect to a remote Postgres database via the following syntax:

CREATE CONNECTION JDBC_PG
  TO 'jdbc:postgresql://...'
  IDENTIFIED BY '...';

SELECT * FROM (
  IMPORT FROM JDBC AT JDBC_PG
  STATEMENT 'SELECT * FROM MY_POSTGRES_TABLE;'
)

-- one can even write direct joins such as
SELECT
  t.COLUMN,
  r.other_column
FROM MY_EXASOL_TABLE t
LEFT JOIN (
  IMPORT FROM JDBC AT JDBC_PG
  STATEMENT 'SELECT key, other_column FROM MY_POSTGRES_TABLE'
) r ON r.key = t.KEY

This is very convenient to import data from PostgreSQL directly into Exasol without having to use a temporary file (csv, pg_dump...).

Is it possible to achieve the same thing from Snowflake (querying a remote PostgreSQL database from Snowflake with a direct live connection)? I couldn't find any mention of it in the documentation.

user_10130596 · Answer

Have you looked into using external functions? It's not exactly what you're looking for (Snowflake doesn't have that capability yet) but it can be used as a workaround in some use cases. For instance, you could create a Python function on AWS Lambda that queries PostgreSQL for small amounts of data (due to Lambda limits) or have it trigger a PostgreSQL process that dumps to S3 to trigger Snowpipe for the bulk import use case.

Querying a PostgreSQL database from Snowflake

1 Answers