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
   

   View Slide

2. Hi
   👋
   I
   'm
   @mehlah
   Works at
   

   View Slide

3. RUBY DAD!
   

   View Slide

4. PostgreSQL
   
   Foreign Data Wrappers
   1
   

   View Slide

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
   .
   

   View Slide

6. View Slide

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
   

   View Slide

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
   

   View Slide

9. View Slide

10. Write your own
    ¯\_(ツ)_/¯ !
    Using C
    , Python or even Ruby
    !
    
    👋
    franckverrot
    /holycorn
    

    View Slide

11. postgres
    _fdw
    2
    

    View Slide

12. View Slide

13. Enable the extension
    CREATE EXTENSION postgres_fdw;
    

    View Slide

14. Create the remote
    /foreign server
    CREATE SERVER app_database_server
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'foo.example.com', dbname 'foo');
    

    View Slide

15. Create a user mapping for the remote server
    CREATE USER MAPPING FOR CURRENT_USER
    SERVER app_database_server
    OPTIONS (user 'username', password 'secret123');
    

    View Slide

16. Create foreign tables
    CREATE SCHEMA app;
    IMPORT FOREIGN SCHEMA public
    FROM SERVER app_database_server
    INTO app;
    

    View Slide

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

    View Slide

18. Datawarehousing
    using Postgres FDW
    3
    

    View Slide

19. The problem
    db1 SQL queries Reporting tool
    

    View Slide

20. The problem
    db1
    db2
    Reporting tool
    SQL queries
    

    View Slide

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

    View Slide

22. A solution
    db1
    db2
    Reporting database
    with a schema
    for each foreign db
    

    View Slide

23. IRL EXAMPLE
    

    View Slide

24. I'M HIRING!
    

    View Slide