Foreign Data Wrappers and their utilization in real world scenarios - - PowerPoint PPT Presentation

▶
foreign data wrappers and their utilization in real world
SMART_READER_LITE
LIVE PREVIEW

Foreign Data Wrappers and their utilization in real world scenarios - - PowerPoint PPT Presentation

Apr 06, 2024 47 likes •620 views

Foreign Data Wrappers / PgConf.EU Milano, 18 October 2019 Foreign Data Wrappers and their utilization in real world scenarios Boriss Mejas Consultant - 2ndQuadrant Air Guitar Player https://www. 2ndQuadrant.com Foreign Data Wrappers /

slide-1
SLIDE 1

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

Boriss Mejías Consultant - 2ndQuadrant Air Guitar Player

Foreign Data Wrappers and their utilization in real world scenarios

slide-2
SLIDE 2

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

The Planet of Krikkit

slide-3
SLIDE 3

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

Planet PostgreSQL

slide-4
SLIDE 4

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

Planet PostgreSQL in the Real World

  • You can’t always migrate to PostgreSQL
slide-5
SLIDE 5

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

Planet PostgreSQL in the Real World

  • You can’t always migrate to PostgreSQL
  • Sometimes you don’t want to migrate
slide-6
SLIDE 6

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

Planet PostgreSQL in the Real World

  • You can’t always migrate to PostgreSQL
  • Sometimes you don’t want to migrate
  • The other system might be the right tool
slide-7
SLIDE 7

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

Planet PostgreSQL in the Real World

  • You can’t always migrate to PostgreSQL
  • Sometimes you don’t want to migrate
  • The other system might be the right tool
  • Data Integration from different

departments/companies/software

slide-8
SLIDE 8

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

Planet PostgreSQL in the Real World

  • You can’t always migrate to PostgreSQL
  • Sometimes you don’t want to migrate
  • The other system might be the right tool
  • Data Integration from different

departments/companies/software

  • Avant Garde
slide-9
SLIDE 9

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

slide-10
SLIDE 10

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

slide-11
SLIDE 11

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

slide-12
SLIDE 12

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

slide-13
SLIDE 13

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

slide-14
SLIDE 14

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

slide-15
SLIDE 15

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

slide-16
SLIDE 16

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

Postgres Setup

shared_preload_libraries = 'mongo_fdw, mysql_fdw'

  • And install software

sudo apt install postgresql-plpython-11 sudo apt install postgresql-11-mysql-fdw compile mongo_fwd

slide-17
SLIDE 17

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

slide-18
SLIDE 18

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

MySQL/MariaDB

CREATE DATABASE mypgconfeu; CREATE USER 'milanese'@'%'; GRANT ALL ON mypgconfeu.* TO 'milanese'@'%'; CREATE TABLE hitchhikers ( id INTEGER PRIMARY KEY AUTO_INCREMENT, hitchhiker TEXT, last_seen TIMESTAMP );

slide-19
SLIDE 19

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

MySQL/MariaDB

INSERT INTO hitchhikers (hitchhiker) VALUES ('Ford Prefect'); INSERT INTO hitchhikers (hitchhiker) VALUES ('Zaphod Beeblebrox');

slide-20
SLIDE 20

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

MySQL FDW - Setup

CREATE EXTENSION mysql_fdw; CREATE SERVER mysql_pgconfeu FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'localhost'); CREATE USER MAPPING FOR douglas SERVER mysql_pgconfeu OPTIONS (username 'milanese' , password 'cappuccino');

slide-21
SLIDE 21

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

MySQL FDW – Import Schema

CREATE SCHEMA mysql; IMPORT FOREIGN SCHEMA mypgconfeu LIMIT TO (hitchhikers) FROM SERVER mysql_pgconfeu INTO mysql;

slide-22
SLIDE 22

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

MySQL FDW – Read and Writes

SELECT * FROM mysql.hitchhikers; INSERT INTO mysql.hitchhikers (hitchhiker) VALUES ('Arthur Dent') SELECT * FROM mysql.hitchhikers;

slide-23
SLIDE 23

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

slide-24
SLIDE 24

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

slide-25
SLIDE 25

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

Another PostgreSQL

CREATE USER milanese; CREATE DATABASE theguide OWNER milanese; CREATE TABLE hitchhikers ( id SERIAL PRIMARY KEY, hitchhiker TEXT, last_seen TIMESTAMP DEFAULT current_timestamp );

slide-26
SLIDE 26

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

Another PostgreSQL

INSERT INTO hitchhikers (hitchhiker) VALUES ('Trillian'); INSERT INTO hitchhikers (hitchhiker) VALUES ('Marvin');

slide-27
SLIDE 27

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

PostgreSQL FDW - Setup

CREATE EXTENSION postgres_fdw; CREATE SERVER planet_postgresql FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'theguide' , host 'localhost' , port '5666'); CREATE USER MAPPING FOR douglas SERVER planet_postgresql OPTIONS (USER 'milanese');

slide-28
SLIDE 28

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

PostgreSQL FDW – Import Schema

CREATE SCHEMA pgsql; IMPORT FOREIGN SCHEMA PUBLIC LIMIT TO (hitchhikers) FROM SERVER planet_postgresql INTO pgsql;

slide-29
SLIDE 29

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

PostgreSQL FDW – Read and Write

SELECT * FROM pgsql.hitchhikers; INSERT INTO pgsql.hitchhikers VALUES (3, 'Slartibartfast', now()); SELECT * FROM pgsql.hitchhikers;

slide-30
SLIDE 30

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

Statistical Anomaly

EXPLAIN SELECT * FROM pgsql.hitchhikers; QUERY PLAN

  • Foreign Scan on hitchhikers (cost=100.00..146.12 rows=1204 width=44)

ANALYZE pgsql.hitchhikers; EXPLAIN SELECT * FROM pgsql.hitchhikers; QUERY PLAN

  • Foreign Scan on hitchhikers (cost=100.00..101.09 rows=3 width=20)
slide-31
SLIDE 31

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

Statistical Anomaly

EXPLAIN SELECT * FROM pgsql.hitchhikers; QUERY PLAN

  • Foreign Scan on hitchhikers (cost=100.00..146.12 rows=1204 width=44)

ANALYZE pgsql.hitchhikers; EXPLAIN SELECT * FROM pgsql.hitchhikers; QUERY PLAN

  • Foreign Scan on hitchhikers (cost=100.00..101.09 rows=3 width=20)
slide-32
SLIDE 32

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

Let’s add more tables - location

CREATE TABLE location ( id INT PRIMARY KEY, location_name VARCHAR NOT NULL ); INSERT INTO location (id, location_name) SELECT s.id, 'Location ' || s.id::TEXT FROM generate_series(1, 1000) s(id); ANALYZE location;

slide-33
SLIDE 33

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

Let’s add more tables – sensor log

CREATE TABLE sensor_log ( id INT PRIMARY KEY, location_id INT NOT NULL, reading BIGINT NOT NULL, reading_date TIMESTAMP NOT NULL );

INSERT INTO sensor_log (id, location_id, reading, reading_date) SELECT s.id, s.id % 1000, s.id % 100, CURRENT_DATE - ((s.id * 10) || 's')::INTERVAL FROM generate_series(1, 50000) s(id);

slide-34
SLIDE 34

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

Let’s add more tables – and indexes

CREATE INDEX idx_sensor_log_location ON sensor_log (location_id); CREATE INDEX idx_sensor_log_date ON sensor_log (reading_date); ANALYZE sensor_log;

slide-35
SLIDE 35

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

PostgreSQL FDW – Import new tables

IMPORT FOREIGN SCHEMA PUBLIC LIMIT TO (location, sensor_log) FROM SERVER planet_postgresql INTO pgsql; ANALYZE pgsql.location; ANALYZE pgsql.sensor_log;

slide-36
SLIDE 36

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

Let’s do a JOIN

EXPLAIN SELECT l.location_name, s.reading FROM pgsql.sensor_log s JOIN pgsql.location l ON (l.id = s.location_id) WHERE s.reading_date >= '2019-10-2';

slide-37
SLIDE 37

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

Let’s do a JOIN on the source

CREATE VIEW v_sensor_details AS SELECT s.*, l.location_name FROM sensor_log s JOIN location l ON (l.id = s.location_id);

slide-38
SLIDE 38

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

PostgreSQL FDW – Import the View

IMPORT FOREIGN SCHEMA PUBLIC LIMIT TO (v_sensor_details) FROM SERVER planet_postgresql INTO pgsql; ANALYZE pgsql.v_sensor_details;

slide-39
SLIDE 39

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

PostgreSQL FDW – Verify improvement

EXPLAIN SELECT location_name, reading FROM pgsql.v_sensor_details WHERE reading_date >= '2019-10-2';

slide-40
SLIDE 40

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

slide-41
SLIDE 41

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

slide-42
SLIDE 42

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

Import Data from Files with Python

CREATE SCHEMA python; CREATE LANGUAGE plpythonu;

slide-43
SLIDE 43

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

With a Stored Procedure

CREATE OR REPLACE FUNCTION python.yield_dictionary() RETURNS TABLE (id INT, word TEXT) AS $$ for i, word in enumerate(open('/usr/share/dict/words', 'r')): yield (i, word.strip()) $$ LANGUAGE plpythonu;

slide-44
SLIDE 44

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

Search for Words

SELECT * FROM python.yield_dictionary() WHERE word LIKE 'fun%' LIMIT 5;

slide-45
SLIDE 45

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

Let’s check performance

\timing on SELECT * FROM python.yield_dictionary() WHERE word LIKE 'fun%' LIMIT 5;

slide-46
SLIDE 46

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

Good Old Cache to the Rescue

CREATE MATERIALIZED VIEW python.word_cache AS SELECT * FROM python.yield_dictionary(); ANALYZE python.word_cache; CREATE INDEX idx_sensor_word_cache_word ON python.word_cache (word TEXT_PATTERN_OPS);

slide-47
SLIDE 47

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

Verify improvement

EXPLAIN SELECT * FROM python.word_cache WHERE word LIKE 'fun%';

QUERY PLAN

  • Index Scan using idx_sensor_word_cache_word on word_cache

Index Cond: ((word ~>=~ 'fun'::text) AND (word ~<~ 'fuo'::text)) Filter: (word ~~ 'fun%'::text)

slide-48
SLIDE 48

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

slide-49
SLIDE 49

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

slide-50
SLIDE 50

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

A bit of MongoDB

use pgconfeu db.createCollection('sensorLog') db.sensorLog.ensureIndex( { readingDate: 1 } ) db.sensorLog.ensureIndex( { location: 1 } ) db.sensorLog.count()

slide-51
SLIDE 51

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

Mongo FDW – Setup

CREATE EXTENSION mongo_fdw; CREATE SERVER mongo_pgconfeu FOREIGN DATA WRAPPER mongo_fdw OPTIONS (address '127.0.0.1', port '27017'); CREATE USER MAPPING FOR douglas SERVER mongo_pgconfeu;

slide-52
SLIDE 52

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

Mongo FDW – A table in PostgreSQL

CREATE SCHEMA mongo; CREATE FOREIGN TABLE mongo.sensor_log ( _id NAME, log_id INT NOT NULL, location_id INT NOT NULL, reading BIGINT NOT NULL, reading_date TIMESTAMP NOT NULL ) SERVER mongo_pgconfeu OPTIONS (database 'pgconfeu', collection 'sensorLog');

slide-53
SLIDE 53

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

Mongo FDW – Read and Write

SELECT * FROM mongo.sensor_log; INSERT INTO mongo.sensor_log (log_id, location_id, reading, reading_date) SELECT * FROM pgsql.sensor_log LIMIT 10; SELECT * FROM mongo.sensor_log;

slide-54
SLIDE 54

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

Sources of this talk

  • Shaun M. Thomas's 2ndQuadrant Webinar

https://resources.2ndquadrant.com/webinar-data- integration-with-postgresql

  • Foreign Data Wrappers

https://wiki.postgresql.org/wiki/Foreign_data_wrappers

  • mongo_fdw

https://github.com/EnterpriseDB/mongo_fdw

slide-55
SLIDE 55

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

slide-56
SLIDE 56

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

Thoughts

  • None of these tables exist in the central database
  • We can read from different sources
  • We can write to all of these sources
  • We can construct extensions/FDWs to fill any gaps
  • PostgreSQL works very well for data integration
slide-57
SLIDE 57

https://www.2ndQuadrant.com

Foreign Data Wrappers / PgConf.EU

Milano, 18 October 2019

Thanks and Remember Benjamin Zander’s Rule #6

Boriss Mejias boriss.mejias@2ndquadrant.com @tchorix