Upgrade to Pro — share decks privately, control downloads, hide ads and more …

PG FDW FTW

PG FDW FTW

Aviez-vous déjà eu envie de croiser des données de plusieurs bases de données PG différentes, voire une base de données et une feuille Excel de l’équipe marketing ou finance ? Découvrez les Foreign Data Wrapper de Postgres et comment vous pourriez fabriquer un data warehouse assez facilement sans sortir l’artillerie lourde.

Mehdi Lahmam B.

June 15, 2022
Tweet

More Decks by Mehdi Lahmam B.

Other Decks in Technology

Transcript

  1. Why and what ? Foreign data wrappers , or FDW

    , allow you to connect from within Postgres to a remote system . From there you can query them with SQL , join across disparate data sets , or join across different systems .
  2. SQL Databases PostgreSQL Oracle MySQL SQLite MS SQL Server NoSQL

    Databases BigTable or HBase Cassandra CouchDB DynamoDB InfluxDB Kafka MongoDB Neo4J Redis Others CSV LDAP IMAP Git RSS Google Sheets S3 Twitter Openstreetmap PBF … In the wild
  3. Google Sheets CREATE EXTENSION multicorn; CREATE SERVER lyonrb FOREIGN DATA

    WRAPPER multicorn OPTIONS ( wrapper 'gspreadsheet_fdw.GspreadsheetFdw' ); CREATE FOREIGN TABLE attendees ( first_name STRING, last_name STRING) SERVER lyonrb OPTIONS ( gskey '1j3jhy2EWaHbdJ0STKpPc668lhOVW4iwEqYas93TYtBY', keyfile '/Users/mehlah/code/fdw_test_auth.json' ); SELECT * from attendees; first_name | last_name ----------------+-------------------------- Jane | Doe BML | Moenne-Loccoz Pierre-Alban | Toth Elodie | Oudot Mehdi | Lahmam Arthur | Delorme
  4. Write your own ¯\_(ツ)_/¯ ! Using C , Python or

    even Ruby ! 👋 franckverrot /holycorn
  5. Create the remote /foreign server CREATE SERVER app_database_server FOREIGN DATA

    WRAPPER postgres_fdw OPTIONS (host 'foo.example.com', dbname 'foo');
  6. Create a user mapping for the remote server CREATE USER

    MAPPING FOR CURRENT_USER SERVER app_database_server OPTIONS (user 'username', password 'secret123');