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. 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