Fun with Foreign Data Wrappers (FDW) and PostgreSQL

Video in TIB AV-Portal: Fun with Foreign Data Wrappers (FDW) and PostgreSQL

Formal Metadata

Fun with Foreign Data Wrappers (FDW) and PostgreSQL
Title of Series
CC Attribution 3.0 Germany:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor.
Release Date

Content Metadata

Subject Area
With Foreign Data Wrappers (FDW) you can access remote objects from SQL databases. This feature is part of the SQL/MED (SQL Management of External Data) specification that was added to the SQL standard. With PostgreSQL you can make use of this great functionality. You can access different remote datasources via FDW for example an ORACLE or MySQL database, JSON- or CSV-file, sources via ODBC and even twitter. The list of FDW is very long: data wrappers There are also Geospatial Wrappers for GDAL/OGR, GeoJSON and OpenStreetMap. You can access your geospatial data for example geopackage or OGC WFS via FDW. That is easy and great fun. Some of the wrappers support only read others support read/write access. The presentation will give an overview on the possibilities of Foreign Data Wrappers and show how to setup a connection to remote data using some examples with geospatial data.
Keywords General

Related Material

Video is cited by the following resource
Mapping Information Multiplication sign Projective plane Repetition Mereology Perspective (visual) Family
Musical ensemble Source code Database
Musical ensemble Musical ensemble Casting (performing arts) Wiki Wrapper (data mining) Repetition Cartesian coordinate system Local ring
Presentation of a group Slide rule Presentation of a group Wiki Wrapper (data mining) Gastropod shell Mereology
Presentation of a group Functional (mathematics) Wrapper (data mining) Multiplication sign Convex hull Database Extension (kinesiology)
Point (geometry) Type theory Geometry Building Wrapper (data mining) Personal digital assistant Database Bit Extension (kinesiology)
Standard deviation Functional (mathematics) Link (knot theory) Wrapper (data mining) Multiplication sign Source code Menu (computing) Database Database Connected space Revision control Wiki Customer relationship management Different (Kate Ryan album) Right angle Sinc function Physical system Salem, Illinois
Functional (mathematics) Wrapper (data mining) Arithmetic mean Projective plane Maxima and minima Mass Database Generic programming Emulation
Source code Wrapper (data mining) Computer file Source code Projective plane Electronic mailing list Database Computer network Database Twitter Connected space Wiki Wiki Quicksort Extension (kinesiology)
Table (information) Wrapper (data mining) Structural load Server (computing) Structural load Computer network Database Connected space Revision control Process (computing) Different (Kate Ryan album) Extension (kinesiology) Local ring Extension (kinesiology)
Table (information) Wrapper (data mining) Structural load Personal digital assistant Server (computing) Self-organization Database Table (information) Connected space Extension (kinesiology)
Server (computing) Service (economics) Wrapper (data mining) Mapping Structural load Repetition Server (computing) Texture mapping Database Repetition Grass (card game) Computer configuration Computer configuration Personal digital assistant Cuboid Extension (kinesiology) Local ring Form (programming) Extension (kinesiology)
Polygon Functional (mathematics) Wrapper (data mining) Table (information) Server (computing) Source code State of matter Database Database Limit (category theory) Line (geometry) Distance Personal digital assistant Limit set Table (information) Local ring
Group action Table (information) Wrapper (data mining) User interface Mapping Lemma (mathematics) Login Menu (computing) Database Numbering scheme Binary file Macro (computer science) Event horizon Computer configuration Network topology Query language Cuboid Maize Electronic visual display Table (information) Summierbarkeit Domain name
Table (information) Computer file Different (Kate Ryan album) Mountain pass Password Utility software Planning Video game Database Extension (kinesiology) 1 (number) Computer programming
Wrapper (data mining) Computer configuration Computer file Server (computing) Computer file Password Electric current Extension (kinesiology)
Root Computer configuration Table (information) Wrapper (data mining) Computer file Server (computing) Computer file Password Extension (kinesiology) Mereology Form (programming)
Table (information) Wrapper (data mining) Software Structural load Server (computing) Order (biology) Database Extension (kinesiology) Oracle Oracle Connected space Extension (kinesiology)
Table (information) Wrapper (data mining) Mapping Server (computing) Texture mapping State observer Sheaf (mathematics) Password Bit Connected space Computer configuration Computer configuration Personal digital assistant Different (Kate Ryan album) Table (information) Integer Oracle Extension (kinesiology)
Table (information) Wrapper (data mining) Texture mapping File format Combinational logic Password Database Mass Mereology Ramsey theory Bounded variation Metropolitan area network Dot product Server (computing) State observer Database Stack (abstract data type) Shape (magazine) Googol Computer configuration Quicksort Table (information) Integer Oracle Extension (kinesiology)
Revision control Installation art Wrapper (data mining) Physical law Video game Physical system
Wrapper (data mining) Table (information) Structural load File format Server (computing) File format Electronic mailing list Shape (magazine) Connectionism Pauli exclusion principle Set (mathematics) Information Extension (kinesiology)
Fibonacci number Wrapper (data mining) Computer file Point (geometry) State of matter Shape (magazine) Directory service Connected space Inflection point
Source code Scale (map) Greatest element Wrapper (data mining) Table (information) Computer file Server (computing) File format Sheaf (mathematics) Shape (magazine) Geometry Computer configuration Computer configuration Statement (computer science) Ranking Lie group Table (information) Integer
Source code Polygon Wrapper (data mining) Server (computing) Codierung <Programmierung> Line (geometry) Point (geometry) File format Client (computing) Parameter (computer programming) Shape (magazine) Unicode Shape (magazine) Computer configuration Computer configuration Finitary relation Analytic continuation Information Multiplication
Point (geometry) Digital filter Server (computing) Table (information) Wrapper (data mining) Source code File format Counting Planning Geometry Information Oracle Source code Scaling (geometry) Mapping File format Server (computing) Point (geometry) Database Bit Line (geometry) Computer configuration Personal digital assistant Vapor barrier Address space
Source code Reading (process) Table (information) Wrapper (data mining) Server (computing) Source code File format Drop (liquid) Line (geometry) Connected space Computer configuration Personal digital assistant Information Table (information)
Revision control Information Block (periodic table) Computer configuration Asynchronous Transfer Mode
Message passing Wrapper (data mining) Server (computing) Set (mathematics) Moving average Client (computing) Message passing Flow separation Sanitary sewer
Wrapper (data mining) Interface (computing) Source code Electronic mailing list Numbering scheme Bit Database transaction Repetition Line (geometry) Mereology Connected space Mathematics Natural number Mixed reality output
Type theory Wrapper (data mining) Code Line (geometry) Window Connected space
Authentication Mathematics Service (economics) Wrapper (data mining) Mapping Personal digital assistant Solitary confinement Mehrplatzsystem Software developer Numbering scheme Office suite Identity management
Boss Corporation Implementation Service (economics) Personal digital assistant Physical law Analytic set Database Endliche Modelltheorie Mass Table (information) Address space Number
mccain you have to i'm hearing information from the project itself my them talk is more from the user perspective and using posters since a long time and protracted and i discovered fund africa top the rep by some time ago and so on that would be a great thing to. talk about it. and the young maybe a have to tell you i have no maps no animations in the torque soul you will see some ask well and i wanted to extend the family part of it but maybe for that to repower fund themselves so i'm you will see.
so they go abroad for another up to his to get access to other saucers year for that abrupt not in when i do courses people complain and they ask how can i get that from one database to another and i would like to join them and i'm combined the data and yet we will see how this will look so i'm came from germany.
and from work group from someone for what i mean by because they have the local team t. shirt but i'm not i'm from from born and die or get where group and we do a lot of have wept years application where most of our cast the must use pasco schools that her base years and if you want to try out for not a rep up.
will be a cold wind a community sprint tomorrow so we could have a look closer look at the presentation is made up that you can easily able to the steps on your own you can use it was you lived as all the data that i use in the presentation and from from that are about their a stop to.
so are being my slides arm you can use the japanese for three to do it you can use it on the command line am but i'm here is on his the steps how you can install preach happen for so why shell years use found that our part.
why is it from to work with them because we have already the billing may be to link from one database to another and so who knows doubling in the audience some a few decades so this is from this functionality is around quite a long time of think it's an extension hour days.
and it enables you to go and we have to go to another database from your database which were in this case i go from natural lost or used his treatment was and local i take the point is that the ship was always to live and look for the fees their book or a start. so i want to find out how many coffees because i like coffee are in bucharest and this is how i can do it was the building so all i have to define the database of want to connect and it was a bit i'm a lot of work i always have to define which started type the columns are that i want to connect.
and then i can go on.
an hoops ok so i thought i would that their seventy care for all that's compete but there's some coffees in i'm focused so now we have an extended way because with the billing ali khan can connect to other posters world database of but i want to connect to about data sources in general.
and this was implemented in an s.p.l. stand out at sea ask well and needy stem not and of this is where it is defined how this connection will work. the new online it's at this time of his from two thousand aids in front arctic imitation it says two thousand once i'm not sure about it i will find out and. and when you implemented this time that you can connect to two different external data sources. and so i'm who are in. the who implemented this time now but says that i'm married to be implemented it but they did not following system not so strict sold they implemented to connect functionality and the beach to with a connected and post quest well. it was invented in two thousand al eleven to compost risk well this read all the support in version nine one and now was to sell in two thousand and thirteen we have right support since version ninety three and there's a long vicky pay a nice to keep age or you can get.
an overview on foreign daughter upon and which projects which external projects and support these functionality soul as you can hear see here that different projects listed here lie or a would you be he can connect you have to ask a large left with you have.
i'm all sort of connections you can build and for every year from external data it may be a different way of installing it so you have to check the installation and see how you can get from it.
so you have one for that our repertoire in and now we are so we so many sources are supported it was not the whole of lists on the wiki pages are some more project and for example you can connect to other databases to other possible out of places you can connect. to flap files and maybe is most interesting thing for us as you can connect to about two spatial dr sources because in a minute i will talk about the old g r four dr pepper and there's a project to tap your to four for example connected to twitter.
moon yet there are different ways of installation i mention this already here and now we will have a locale as compared with a the billing version you can do a connection from one to another about a base was found out or so the process as always is always the same and you load and extend.
in which enables you to connect to foreign doubt a sauce then you create a foreign serve our you create different user because the users use in pa stressed well it's not same that you may use to connect to an organ that a base or to some as a sauce it's not always the case that you need this for news about.
some half the connections and then you can start and connect to tables and pulled up foreign tables and have farm and yet easily work on external data as if the doctor would be in your diet pace and so let's see the steps how they look like.
create extension to a pos grass f t v f t w so this is a form that of rep of his that is already shipped on installation from prosperous well so you can use it out of the box without instead of installing it and here you see how you can play of all new cars.
c two probably but i hope you can imagine it fast trade server are foreign and you give a name to the soul of our and then you say which for dr repartee want to use in my case it's prosperous california for on that a rapper and then you as well and have to define the options how. or you can go to these external and that's a sauce and build up the connection and for the user mapping it looks like this you create hughes and create use a mapping for use them and then define which service user should be for which survived and you define the options so you say. ok take this user name and this pos what and then i go to the other database and to get a ticket for.
the next step you want to create a foreign table so the first line is only that i want to separate maybe the data from my daughter asked my created shame are a different name and then that's a nice functionality you can import foreign she marched from the other database and in my case i say ok import from. and shame a public sell all of the tables that are in the other about a base in public shame our will be important or on the the president talks of the tables is important to my daughter base when i do this i could and i'm not interested in all the tables i could a limited set of the important sale. i only want these and that table or i could say ok some tables i don't want to import and i'm like this you can only get the table sets you are interested.
and then that you have access to this and hottest holes from your database and can easily run. clear where you combine both data sources and here we can see hundred seventy forty seven and fees and focused so maybe at the beginning you want if you use p.d. up when three is still a you may not find the formed out of upon them.
actions because they are not activated by the ford you have to go to your options in the japanese and activate some of the check boxes so far so of our own daughter of a pow use a mapping and foreign table and then you will see them in the tree so you see foreign tables here in the middle.
you see you get them more about half the. from survive and the difference of eye and also they use them.
so i am use a small example of where you can connect to a plane files so on always your life we have a passport file for the programs where you hit you need a password so i would like to have access to this fired from a database so there's an extension it.
called file farmed out to repower and you see this the passport files on my desktop and now i want to create to serve our.
and it's a form that everybody. many as two to access to this file for that are part and i don't need to use a mapping and first i had to created it creates the extension five for dr up and maybe a misstep in the sights and then i can import this.
cable s.p. soared in the example before and have easily access to the new data from this fight.
ok and i talked about proprietary software already in my school no or it calls for oracle i was talking about so and or m. is a skill or of our.
says you can connect for example i'm oracle i have an example here you can create extension order for and out of a palm and its if you have to concept it's always very similar are so for every year connection a foreign data repower maybe you have that.
and. yet the definitive this may be a bit and difference on this case we define in option section of the db serve our to hang him billups connection and we have to create to use a mapping and then we can trade or foreign tables.
new. when.
so like this as well you can combine tables as if they're in a database and when you connect to the tables on the remote database where clauses push down to the database so not all the dots was going to be loaded. the push down is already done for the cry of columns and a half explains support. but now the fun part starts of you want to get access to or them to dart and there's a forgotten up father was implemented by poor ramsey its own gr found out about and as we all laugh and know which are the deal google isn't it supports lot of. sector for markets and john you can access the data from a database so like to pack your w. mass of mistreatment daughter as who shaped like a man and all sorts of dart or even have enough us read you can connect with these m four months and you can download and.
compile would your for dr upon yourself are hughes was your life thirteen version thirteen it's already shipped with most you live and you can start from at once and i'm their packages available so you can cut and install it on his system and while it's the law.
long list if you want to have a look at all but four months i have only have the top of their lives so if you're i want to know about with four months are supported you can go to orgy are farmed out to be in for with smiles half and it will give you a list of all the support of formats. and you'll see whether they are only read redouble our we tried to access to success. and um yeah maybe you're fifty stick aswell and let's have a look at history shave long it's still quite use so there was.
it's very easy to get connection to shape files so we have this natural have started out on which to live with this c'mon to can ask orgy are funded up in food to list all the data or the shape of the layers we say that it's correlated to shape five that are.
and this directory and then i can define which of these layers of would like to import so i or whether i want to import them all like this i can put of the layout and then i can create this know i can first i can create a connection to these directory and then.
i can't lie i'm create a foreign table and at the bottom you see you in their options section which way i would like to import in this and connect and with this table statement in and then i can work with his table as is it as with the shape file us i'm right. directly from my diet is if i ever in coding problems their solution as well i could.
an option parameter shape and courting the islands for example and then the according to fight.
or move you have the possibility to them connects to mistreatment start are so was the same concept the concept was might have asked you can list of the layoffs and then you can the time and decide which of the last you would like to connect so you create a foreign serve our.
the server gets a name already and he uses data source bits from the street map file format yours defined for much of the doctor that you would like to connect and then create your foreign table. the night thing is that with this come around them or jr from dr up in four will write you already their desk well on the commodity line so you can easily copy this different mission go to your database and then maybe more he fired a bit and then. and now runs his girls there.
an ok so you also an africa sastre explain and an arsonist at it for in case of oracle and he can see it's a small scale were connected to all the points were highway i wanted to know in the column highway which attract. signals so when i run a us and six some of them and when iran and you can see how. yeah how the database get the data and he uses a four inch came in the middle of.
ok then. king this skip it was an example and to fdic to how you can connect with them and jeff charter then we have a very nice feature you can connect to double your fast during its supported it's rude all he knew and.
in the first line you see how you want to lead a foreign service so i'm there some connections to the user to the table so you have to use his skates to lead it and let's see what happens when you connect with wi fi us so in this case africa your ass you have to find you have to defend dr source.
and then the former it is doubly of fast and and i being made me your doctor. and remote that has asked us not so you would like to know what happens why does not answer and there's this conflict option to pale d. block that you can see it to on and like this you can and get the but mode of information may be destined for all of the request was version and like this.
it will be a year from this that in the deeper. ok so you can see here you can't separate show klein messages and set it in two d. back to and like this you will get the deep messages and then you can in this lecture can see you can a community kate was a double of us.
and that's it that's a short introduction into the old year.
i'm probably have s. and w.r.v.s. and jam and own know i'm mixed foreign data rep angelo i hope you like it and maybe a one of you would like to try the future and if you have questions come around the country.
how you. i think we don't have a microphones for crest some soul and natural. all maybe if that's the question i can repeated or. parton. or. when i think so yes but i'm not sure i didn't try all of them and maybe i'm not so connected to every a former part to a fuel. look at the list. we work can find. it. oh. and no and i said before the question was asked at w f s i know. in general it it's it's the provides some are transactions i'm not sure i mutter. maybe i'm you know. hi. so i happen to know the year the interface itself provides the way the tool do transactions but if they're under learned line but did the source doesn't have a way to implement transactions he just keeps them. and in other questions. are sometimes it's a little bit of flair pain if the scheme of this was changes so you have to drop and create year dave if the top your connection foist their bit of a likely for a shot at think you would have to be created but maybe terrifying knows something more. but yeah so there's not a permanent connection to the southwest think other mazzola are so when your had tried to connected it's a build up so if you change she my in your remote that a base maybe it will get an hour but hadn't tried so since the motion of input for in ski market.
it's much easier because now you can just important new one you can just white and report it and that's the two lines of code.
when you're any other questions. yeah. every issue is there any year to see magic a huge amending type to to channel the the active territory to logging on a windows she declined through to making a connection to m m m m mrs girls over to know that.
what was the last possible to connect to an end i had ever out i was visiting inclined and they were talking about the using the if t.w. to connect with that the local it mr welsh over which is used as there's a lot in active the richer east stuff i'm not a window. person so i am not really into it a but as far as i could see you needed to indicate the post office over instead of the local klein using it so. it was if there was a way to channel the the authentication through the if the wu shu mapping me or maybe yes there's a way you could create multiple users and their s. crete sell their to import held up for sure to into the posters. yes. and hello ever do know of any practical use cases to dislike i have been using them and i cannot say i have seen any the proper he used to be honest like you've seen the scheme much changes and that can buy. but you understand why does so as a developer i would usually use a p ice tool is a poor data together with the micro services.
my identity rubble at but they say should use a piazzas one in there so why would you even have a fourteen day to operate to fight those or when i found myself and use case and the customer they have had your board or and they have lots of such must use but they have data from a big weekend.
and and a half the pasta laws and the house numbers and they don't want to update them regularly but want to access them yet apnea fast and we thought about ok let's connected doubly a fast but the such model that a half dozen support that we have asked so in that case we created a four under. the rapper and now we talk to this table that we created and that will go to the tally a mass and give give specs the address all the parcel number apostle and sold like that it was a very good use chris boss and stopped us from m yeah updating the dart. and importing them to the database regularly it was a really good solution for them. any other questions. for for another it will take a second or just for another use case if you've got a bunch of microsoft mrs and once you want to implement analytics on top of that and you didn't quite desired it's right. a quick way is to connect all the databases of all the data services of of your microsurgery ces into one single database and by the grace and after that you've got a powerful stuff to join them.
the other questions. so thank you very much faster it.