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.

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 .
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
10. Write your own ¯\_(ツ)_/¯ ! Using C , Python or even Ruby ! 👋 franckverrot /holycorn
11. postgres _fdw 2
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!