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 full-size slide

  2. Hi
    👋
    I
    'm
    @mehlah
    Works at

    View full-size slide

  3. PostgreSQL

    Foreign Data Wrappers
    1

    View full-size slide

  4. 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 full-size slide

  5. 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 full-size slide

  6. 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 full-size slide

  7. Write your own
    ¯\_(ツ)_/¯ !
    Using C
    , Python or even Ruby
    !

    👋
    franckverrot
    /holycorn

    View full-size slide

  8. postgres
    _fdw
    2

    View full-size slide

  9. Enable the extension
    CREATE EXTENSION postgres_fdw;

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  14. Datawarehousing
    using Postgres FDW
    3

    View full-size slide

  15. The problem
    db1 SQL queries Reporting tool

    View full-size slide

  16. The problem
    db1
    db2
    Reporting tool
    SQL queries

    View full-size slide

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

    View full-size slide

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

    View full-size slide