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.

C74bdcd3fa7c3d3f23290f46430b1463?s=128

Mehdi Lahmam B.

June 15, 2022
Tweet

More Decks by Mehdi Lahmam B.

Other Decks in Technology

Transcript

  1. PG FDW FTW Mehdi Lahmam June 2022

  2. Hi 👋 I 'm @mehlah Works at

  3. RUBY DAD!

  4. PostgreSQL Foreign Data Wrappers 1

  5. 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 .
  6. None
  7. 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
  8. 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
  9. None
  10. Write your own ¯\_(ツ)_/¯ ! Using C , Python or

    even Ruby ! 👋 franckverrot /holycorn
  11. postgres _fdw 2

  12. None
  13. Enable the extension CREATE EXTENSION postgres_fdw;

  14. Create the remote /foreign server CREATE SERVER app_database_server FOREIGN DATA

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

    MAPPING FOR CURRENT_USER SERVER app_database_server OPTIONS (user 'username', password 'secret123');
  16. Create foreign tables CREATE SCHEMA app; IMPORT FOREIGN SCHEMA public

    FROM SERVER app_database_server INTO app;
  17. Query anything 🎉 SELECT COUNT(*) FROM app.users;

  18. Datawarehousing using Postgres FDW 3

  19. The problem db1 SQL queries Reporting tool

  20. The problem db1 db2 Reporting tool SQL queries

  21. The problem db1 db2 Reporting tool SQL queries ETL pipeline

    ?
  22. A solution db1 db2 Reporting database with a schema for

    each foreign db
  23. IRL EXAMPLE

  24. I'M HIRING!