foreign data wrappers and their utilization in real world
play

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

Apr 06, 2024 •47 likes •617 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 /

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

  2. Foreign Data Wrappers / PgConf.EU Milano, 18 October 2019 The Planet of Krikkit https://www. 2ndQuadrant.com

  3. Foreign Data Wrappers / PgConf.EU Milano, 18 October 2019 Planet PostgreSQL https://www. 2ndQuadrant.com

  4. Foreign Data Wrappers / PgConf.EU Milano, 18 October 2019 Planet PostgreSQL in the Real World ● You can’t always migrate to PostgreSQL https://www. 2ndQuadrant.com

  5. 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 https://www. 2ndQuadrant.com

  6. 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 https://www. 2ndQuadrant.com

  7. 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 https://www. 2ndQuadrant.com

  8. 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 https://www. 2ndQuadrant.com

  9. Foreign Data Wrappers / PgConf.EU Milano, 18 October 2019 https://www. 2ndQuadrant.com

  10. Foreign Data Wrappers / PgConf.EU Milano, 18 October 2019 https://www. 2ndQuadrant.com

  11. Foreign Data Wrappers / PgConf.EU Milano, 18 October 2019 https://www. 2ndQuadrant.com

  12. Foreign Data Wrappers / PgConf.EU Milano, 18 October 2019 https://www. 2ndQuadrant.com

  13. Foreign Data Wrappers / PgConf.EU Milano, 18 October 2019 https://www. 2ndQuadrant.com

  14. Foreign Data Wrappers / PgConf.EU Milano, 18 October 2019 https://www. 2ndQuadrant.com

  15. Foreign Data Wrappers / PgConf.EU Milano, 18 October 2019 https://www. 2ndQuadrant.com

  16. 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 https://www. 2ndQuadrant.com

  17. Foreign Data Wrappers / PgConf.EU Milano, 18 October 2019 https://www. 2ndQuadrant.com

  18. 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 ); https://www. 2ndQuadrant.com

  19. 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'); https://www. 2ndQuadrant.com

  20. 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'); https://www. 2ndQuadrant.com

  21. 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; https://www. 2ndQuadrant.com

  22. 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; https://www. 2ndQuadrant.com

  23. Foreign Data Wrappers / PgConf.EU Milano, 18 October 2019 https://www. 2ndQuadrant.com

  24. Foreign Data Wrappers / PgConf.EU Milano, 18 October 2019 https://www. 2ndQuadrant.com

  25. 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 ); https://www. 2ndQuadrant.com

  26. Foreign Data Wrappers / PgConf.EU Milano, 18 October 2019 Another PostgreSQL INSERT INTO hitchhikers (hitchhiker) VALUES ('Trillian'); INSERT INTO hitchhikers (hitchhiker) VALUES ('Marvin'); https://www. 2ndQuadrant.com

  27. 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'); https://www. 2ndQuadrant.com

  28. 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; https://www. 2ndQuadrant.com

  29. 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; https://www. 2ndQuadrant.com

  30. 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) https://www. 2ndQuadrant.com

  31. 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 ) https://www. 2ndQuadrant.com

  32. 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; https://www. 2ndQuadrant.com

  33. 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) ; https://www. 2ndQuadrant.com

  34. 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; https://www. 2ndQuadrant.com

  35. 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; https://www. 2ndQuadrant.com

  36. 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'; https://www. 2ndQuadrant.com

  37. 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); https://www. 2ndQuadrant.com

  38. 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; https://www. 2ndQuadrant.com

  39. 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'; https://www. 2ndQuadrant.com

  40. Foreign Data Wrappers / PgConf.EU Milano, 18 October 2019 https://www. 2ndQuadrant.com

  41. Foreign Data Wrappers / PgConf.EU Milano, 18 October 2019 https://www. 2ndQuadrant.com

Download Presentation
Download Policy: The content available on the website is offered to you 'AS IS' for your personal information and use only. It cannot be commercialized, licensed, or distributed on other websites without prior consent from the author. To download a presentation, simply click this link. If you encounter any difficulties during the download process, it's possible that the publisher has removed the file from their server.

Recommend

YOUR ONE-STOP DATA SHOP - AN INTRO TO FOREIGN DATA WRAPPERS IN POSTGRES Sam Bail @spbail

YOUR ONE-STOP DATA SHOP - AN INTRO TO FOREIGN DATA WRAPPERS IN POSTGRES Sam Bail @spbail

YOUR ONE-STOP DATA SHOP - AN INTRO TO FOREIGN DATA WRAPPERS IN POSTGRES Sam Bail @spbail PostgresOpen 2019 HI, IM SAM PhD in Semantic Web, knowledge representation, automated reasoning Spent 5 years at Flatiron Health in NYC analyzing

595 views • 48 slides
PostgreSQL Foreign Data Wrappers Ibrar Ahmed Senior Database Architect - Percona LLC May 2019

PostgreSQL Foreign Data Wrappers Ibrar Ahmed Senior Database Architect - Percona LLC May 2019

Join Heterogeneous Databases Using PostgreSQL Foreign Data Wrappers Ibrar Ahmed Senior Database Architect - Percona LLC May 2019 Why? Accessing Data From Multiple Sources SELECT * from multiple Database Engines and generate results?

446 views • 21 slides
Foreign Investment in U.S. Real Property: Foreign Investment in U.S. Real Property: Tax

Foreign Investment in U.S. Real Property: Foreign Investment in U.S. Real Property: Tax

Presenting a live 90 minute webinar with interactive Q&A Foreign Investment in U.S. Real Property: Foreign Investment in U.S. Real Property: Tax Considerations Navigating the Legal Challenges of Acquiring, Owning, and Disposing of U.S. Real

628 views • 58 slides
Wrap-up Part 1 Web IE, Wrappers and Information Integration using Karma Extracting Data from

Wrap-up Part 1 Web IE, Wrappers and Information Integration using Karma Extracting Data from

Wrap-up Part 1 Web IE, Wrappers and Information Integration using Karma Extracting Data from Semi-structured Sources NAME Casablanca Restaurant STREET 220 Lincoln Boulevard CITY Venice PHONE (310) 392-5751 Approaches to Wrapper

229 views • 21 slides
INVESTING IN A COORDINATED DATA NETWORK INFORMING THE VISION OF NEST THROUGH PARTNERSHIPS ACROSS

INVESTING IN A COORDINATED DATA NETWORK INFORMING THE VISION OF NEST THROUGH PARTNERSHIPS ACROSS

INVESTING IN A COORDINATED DATA NETWORK INFORMING THE VISION OF NEST THROUGH PARTNERSHIPS ACROSS THE INDUSTRY A PARTNERSHIP TO ACCESS REAL-WORLD DATA TO GENERATE RELIABLE REAL-WORLD EVIDENCE 1 KEY CHALLENGES FACING REAL-WORLD DATA BUILDING ON

424 views • 7 slides
XML in the real world XML in the real world XML agentzh ( )

XML in the real world XML in the real world XML agentzh ( )

XML in the real world XML in the real world XML agentzh ( ) 2006.10 RSS is cool ! RSS RSS Really Simple Syndication Tired of checking your favorite news and blogs sites everyday?

786 views • 75 slides
Enums CSCI 112: Programming in C Fitting C data types to the real world So far, weve seen

Enums CSCI 112: Programming in C Fitting C data types to the real world So far, weve seen

Enums CSCI 112: Programming in C Fitting C data types to the real world So far, weve seen int , double , float and char For a lot of real world data, these fit well! What sorts of things would be difficult to represent with these

587 views • 14 slides
Practical advice Real survey data is messy Distance sampling in the Real World We've talked a

Practical advice Real survey data is messy Distance sampling in the Real World We've talked a

Practical advice Real survey data is messy Distance sampling in the Real World We've talked a lot about models We've also talked about assumptions Our example is relatively well-behaved What can we do about all the nasty real world stuff?

606 views • 42 slides
12: Social Networks Machine Learning and Real-world Data (MLRD) Ann Copestake (based on slides

12: Social Networks Machine Learning and Real-world Data (MLRD) Ann Copestake (based on slides

12: Social Networks Machine Learning and Real-world Data (MLRD) Ann Copestake (based on slides created by Simone Teufel) Lent 2018 Where have we got to? You have now encountered two applications of ML and real-world data: Sentiment Detection

486 views • 12 slides
12: Social Networks Machine Learning and Real-world Data (MLRD) Ann Copestake (based on slides

12: Social Networks Machine Learning and Real-world Data (MLRD) Ann Copestake (based on slides

12: Social Networks Machine Learning and Real-world Data (MLRD) Ann Copestake (based on slides created by Simone Teufel) Lent 2019 Where have we got to? You have now encountered two applications of ML and real-world data: Sentiment Detection

269 views • 16 slides
Challenges of real-world data We face an explosion in data from e.g.: Internet

Challenges of real-world data We face an explosion in data from e.g.: Internet

Challenges of real-world data We face an explosion in data from e.g.: Internet transactions Satellite measurements Advances in Environmental sensors Privacy-Preserving Machine Learning

373 views • 6 slides
Foreign Investment in U.S. Real Property: Tax and Reporting Challenges Anticipating Tax Issues

Foreign Investment in U.S. Real Property: Tax and Reporting Challenges Anticipating Tax Issues

Foreign Investment in U.S. Real Property: Tax and Reporting Challenges Anticipating Tax Issues When a Foreign Investor or Entity Acquires or Disposes of Interests TUESDAY , JULY 21, 2015, 1:00-2:50 pm Eastern IMPORTANT INFORMATION This program

1.15k views • 99 slides
12: Social Networks Machine Learning and Real-world Data (MLRD) Ryan Cotterell (based on slides

12: Social Networks Machine Learning and Real-world Data (MLRD) Ryan Cotterell (based on slides

12: Social Networks Machine Learning and Real-world Data (MLRD) Ryan Cotterell (based on slides created by Simone Teufel) Lent 2020 Where have we got to? You have now encountered two applications of ML and real-world data: Sentiment

546 views • 12 slides
Measurement and Data Data describes the real world Data maps entities in the domain of

Measurement and Data Data describes the real world Data maps entities in the domain of

Measurement and Data Data describes the real world Data maps entities in the domain of interest to symbolic representation by means of a measurement procedure Numerical relationships between variables capture relationships between

502 views • 28 slides
with DLP Real World Scenarios My Journey into Patient Data Protection 10+ years in Data Loss

with DLP Real World Scenarios My Journey into Patient Data Protection 10+ years in Data Loss

Stopping PHI Theft with DLP Real World Scenarios My Journey into Patient Data Protection 10+ years in Data Loss Prevention Dozens of customer installations across the finance and healthcare industries Chris Leffel My story in

603 views • 32 slides
DScope: Detecting Real-World Data Corruption Hang Bugs in Cloud Server Systems Ting Dai 1 ,

DScope: Detecting Real-World Data Corruption Hang Bugs in Cloud Server Systems Ting Dai 1 ,

DScope: Detecting Real-World Data Corruption Hang Bugs in Cloud Server Systems Ting Dai 1 , Jingzhu He 1 , Xiaohui (Helen) Gu 1 , Shan Lu 2 , Peipei Wang 1 1 NC State University 2 University of Chicago 1 DScope, SoCC18 Real-World Data

333 views • 21 slides
Ubiquitous and Mobile Computing CS 528: Automatically Characterizing Places with Opportunistic

Ubiquitous and Mobile Computing CS 528: Automatically Characterizing Places with Opportunistic

Ubiquitous and Mobile Computing CS 528: Automatically Characterizing Places with Opportunistic CrowdSensing using Smartphones Gauri Pulekar Computer Science Dept. Worcester Polytechnic Institute (WPI) Automatically Characterizing Places with

311 views • 30 slides
University of Dallas application to join DUNE Will Flanagan August 16, 2017 About UD 50

University of Dallas application to join DUNE Will Flanagan August 16, 2017 About UD 50

University of Dallas application to join DUNE Will Flanagan August 16, 2017 About UD 50 undergraduate physics majors Braniff Memorial UD excels at connecting students with research Cappuccino Physics Tower and preparing

874 views • 6 slides
DSLs in JavaScript Nathaniel T. Schutta 1 Who am I? Nathaniel T. Schutta

DSLs in JavaScript Nathaniel T. Schutta 1 Who am I? Nathaniel T. Schutta

DSLs in JavaScript Nathaniel T. Schutta 1 Who am I? Nathaniel T. Schutta http://www.ntschutta.com/jat/ Foundations of Ajax & Pro Ajax and Java Frameworks UI guy Author, speaker, teacher More than a couple of web apps 2

2.37k views • 187 slides
Selector-based View Composition William Zeller and Edward Felten Princeton University

Selector-based View Composition William Zeller and Edward Felten Princeton University

Selector-based View Composition William Zeller and Edward Felten Princeton University http://svc.from.bz What is SVC? Modification to Model-View-Controller (MVC) architecture Provides automatic progressive enhancement (Ajaxification)

1.33k views • 107 slides
O O P w i t h J a v a Y u a n b i n Wu c s @e c n u O O P w i t h J

O O P w i t h J a v a Y u a n b i n Wu c s @e c n u O O P w i t h J

O O P w i t h J a v a Y u a n b i n Wu c s @e c n u O O P w i t h J a v a P 2 9 r o j e c t 7 : 6 7 6 1 4 6 2 1 6

937 views • 60 slides
A cute presentation of Debtags The last, ultimate step towards Total World Domination! Enrico

A cute presentation of Debtags The last, ultimate step towards Total World Domination! Enrico

A cute presentation of Debtags The last, ultimate step towards Total World Domination! Enrico Zini enrico@debian.org Jul 10, 2005 18 slides Enrico Zini (enrico@debian.org) 1/18 A cute presentation of Debtags Debconf 5, Helsinki, July 10,

434 views • 18 slides
torsdag den 13. oktober 11 torsdag den 13. oktober 11 torsdag den 13. oktober 11 torsdag den 13.

torsdag den 13. oktober 11 torsdag den 13. oktober 11 torsdag den 13. oktober 11 torsdag den 13.

torsdag den 13. oktober 11 torsdag den 13. oktober 11 torsdag den 13. oktober 11 torsdag den 13. oktober 11 torsdag den 13. oktober 11 http://www.doxdesk.com/updates/2009.html torsdag den 13. oktober 11 Design, techniques and tools for

1.12k views • 96 slides
brighterkind Allan Brazier Director of Hospitality Services b righterkind signature element.

brighterkind Allan Brazier Director of Hospitality Services b righterkind signature element.

brighterkind Allan Brazier Director of Hospitality Services b righterkind signature element. To be the best care home company in the UK 70 care homes Over 3,000 resident's The BK journey. Introduction of back of house work with

303 views • 16 slides

More recommend