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

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
Day 3 Lab2: IoT Introduction In this example, we process real-world vehicle IoT data. Our

Day 3 Lab2: IoT Introduction In this example, we process real-world vehicle IoT data. Our

Day 3 Lab2: IoT Introduction In this example, we process real-world vehicle IoT data. Our data is in file vehicle_1_1000.tsv . This file contains first 1000 rows of the vehicle sensor data representing car movements. The data stored is in a

470 views • 6 slides
Protecting Data in Untrusted Locations An exercise in Real World threat modeling. Jan

Protecting Data in Untrusted Locations An exercise in Real World threat modeling. Jan

Protecting Data in Untrusted Locations An exercise in Real World threat modeling. Jan Schaumann 99CE 1DC7 770A C5A8 09A6 @jschauma 0DCD 66CE 4FE9 6F6B D3D7 Me. Errday. Obligatory James Mickens This World of Ours reference.

714 views • 40 slides
10: Biological Applications for HMMs Machine Learning and Real-world Data Ann Copestake and

10: Biological Applications for HMMs Machine Learning and Real-world Data Ann Copestake and

10: Biological Applications for HMMs Machine Learning and Real-world Data Ann Copestake and Simone Teufel Computer Laboratory University of Cambridge Lent 2017 Last session: dice world and HMM decoding You now have written a decoder, i.e., an

401 views • 14 slides
Sparse tensors are a natural way of representing real-world data 1 Sparse tensors are a natural

Sparse tensors are a natural way of representing real-world data 1 Sparse tensors are a natural

Sparse tensors are a natural way of representing real-world data 1 Sparse tensors are a natural way of representing real-world data 1 Sparse tensors are a natural way of representing real-world data Durable Poor Quality Peter 2 Lilly

1.29k views • 91 slides
Study Design & Quality Measures Real World Evidence Demonstrates Real Results Valeritas has

Study Design & Quality Measures Real World Evidence Demonstrates Real Results Valeritas has

Study Design & Quality Measures Real World Evidence Demonstrates Real Results Valeritas has focused on providing data that demonstrates clinical and economic benefits in a real-world setting Based on standard clinical practice

453 views • 5 slides
Tuning Epidemiological Study Design Methods for Exploratory Data Analysis in Real World Data

Tuning Epidemiological Study Design Methods for Exploratory Data Analysis in Real World Data

Tuning Epidemiological Study Design Methods for Exploratory Data Analysis in Real World Data Andrew Bate Senior Director, Epidemiology Group Lead, Analytics 15th Annual Meeting of the International Society of Pharmacovigilance, Prague, 30

472 views • 21 slides
Improving Query Performance THE REAL WORLD OF THE DATABASE in Data

Improving Query Performance THE REAL WORLD OF THE DATABASE in Data

Improving Query Performance THE REAL WORLD OF THE DATABASE in Data Warehouses ADMINISTRATOR Business Intelligence Journal 06 Unisphere Research 15 Perception Action Model Planning

404 views • 29 slides
Structuring Real Estate Loans With Foreign Borrowers, Trusts and Tenants in Common Documenting

Structuring Real Estate Loans With Foreign Borrowers, Trusts and Tenants in Common Documenting

Presenting a live 90-minute webinar with interactive Q&A Structuring Real Estate Loans With Foreign Borrowers, Trusts and Tenants in Common Documenting U.S. Assets of Foreign Guarantors, Reviewing TIC Agreements, Revocable vs. Irrevocable

660 views • 39 slides

More recommend