Fun with Foreign Data Wrappers (FDW) and PostgreSQL
This is a modal window.
The media could not be loaded, either because the server or network failed or because the format is not supported.
Formal Metadata
| Title |
| |
| Title of Series | ||
| Number of Parts | 295 | |
| Author | ||
| Contributors | ||
| License | 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. | |
| Identifiers | 10.5446/43423 (DOI) | |
| Publisher | ||
| Release Date | ||
| Language |
Content Metadata
| Subject Area | ||
| Genre | ||
| Abstract |
| |
| Keywords |
FOSS4G Bucharest 2019161 / 295
15
20
28
32
37
38
39
40
41
42
43
44
46
48
52
54
57
69
72
75
83
85
87
88
101
103
105
106
108
111
114
119
122
123
126
129
130
131
132
137
139
140
141
142
143
144
147
148
149
155
157
159
163
166
170
171
179
189
191
192
193
194
195
196
197
202
207
212
213
214
215
216
231
235
251
252
263
287
00:00
MappingMultiplication signMereologyRepetitionPerspective (visual)Projective planeInformationFamilyWrapper (data mining)Lecture/Conference
00:46
Musical ensembleSource codeDatabaseSource codeWrapper (data mining)Computer animationLecture/Conference
01:06
Wrapper (data mining)Musical ensembleWikiPresentation of a groupLocal ringMusical ensembleCasting (performing arts)RepetitionCartesian coordinate systemWeb 2.0DatabasePresentation of a groupWrapper (data mining)CodeGastropod shellSlide ruleMereologyComputer animation
01:49
Convex hullPresentation of a groupWrapper (data mining)DatabaseMultiplication signFunctional (mathematics)Extension (kinesiology)Computer animationXML
02:20
GeometryWrapper (data mining)Extension (kinesiology)DatabaseFunctional (mathematics)Extension (kinesiology)CASE <Informatik>Point (geometry)Multiplication signBitBuildingType theoryJSONXML
02:48
Wrapper (data mining)Link (knot theory)Menu (computing)Data managementStandard deviationDatabaseSalem, IllinoisData typeExtension (kinesiology)BitDatabaseSource code
03:26
Data managementStandard deviationDatabaseWikiWrapper (data mining)MassGeneric programmingRevision controlSource codeSinc functionWritingWikiStandard deviationRead-only memoryConnected spaceDifferent (Kate Ryan album)Right angleFunctional (mathematics)Physical systemMultiplication signXMLComputer animation
04:41
Arithmetic meanWrapper (data mining)DatabaseGeneric programmingMassEmulationMaxima and minimaProjective planeFunctional (mathematics)Connected spaceQuicksortLecture/Conference
05:17
Wrapper (data mining)Source codeDatabaseTwitterWikiComputer networkExtension (kinesiology)Projective planeComputer fileSource codeElectronic mailing listWikiDatabaseWrapper (data mining)XMLComputer animation
05:47
Computer networkExtension (kinesiology)Structural loadServer (computing)Table (information)Wrapper (data mining)DatabaseDifferent (Kate Ryan album)TwitterProjective planeRevision controlLocal ringStructural loadConnected spaceExtension (kinesiology)Process (computing)DatabaseComputer animation
06:13
Table (information)Extension (kinesiology)Structural loadServer (computing)Wrapper (data mining)DatabaseRepetitionComputer configurationLocal ringTexture mappingLimit (category theory)PolygonDistanceState of matterDomain nameNumbering schemeMacro (computer science)LoginMenu (computing)MaizeBinary fileEvent horizonQuery languageSummierbarkeitElectronic visual displayLemma (mathematics)User interfaceExtension (kinesiology)CASE <Informatik>Self-organizationTable (information)Computer configurationWrapper (data mining)Query languageSource codeConnected spaceDatabaseServer (computing)MappingLine (geometry)CuboidService (economics)RepetitionForm (programming)Grass (card game)Group actionFunctional (mathematics)Network topologyLimit setComputer animationXML
09:41
Table (information)1 (number)Utility softwareMountain passBitMappingTable (information)Computer fileDatabasePasswordComputer programmingVideo gamePlanningDifferent (Kate Ryan album)Extension (kinesiology)
10:01
Server (computing)Computer filePasswordWrapper (data mining)Electric currentExtension (kinesiology)Computer configurationComputer filePasswordDatabaseComputer programmingXMLUML
10:22
RootComputer fileForm (programming)Extension (kinesiology)Mereology
10:41
Server (computing)RootPasswordComputer configurationComputer fileTable (information)Wrapper (data mining)Extension (kinesiology)Computer fileTable (information)SoftwareOracleXMLLecture/Conference
11:00
Server (computing)Table (information)Extension (kinesiology)Structural loadWrapper (data mining)OracleDatabaseTexture mappingPasswordComputer configurationState observerIntegerDatabaseConnected spaceExtension (kinesiology)OracleOrder (biology)Computer animationJSON
11:30
Texture mappingServer (computing)Table (information)Computer configurationPasswordState observerOracleWrapper (data mining)Bounded variationIntegerExtension (kinesiology)Table (information)Computer configurationCASE <Informatik>BitMappingConnected spaceSheaf (mathematics)Different (Kate Ryan album)JSON
11:51
Wrapper (data mining)Table (information)Stack (abstract data type)DatabaseFile formatShape (magazine)Installation artInformationConnectionismSet (mathematics)Pauli exclusion principleServer (computing)Extension (kinesiology)Structural loadTable (information)Metropolitan area networkFile formatGoogolElectronic mailing listRamsey theoryDot productMassCombinational logicDatabaseMereologyPhysical systemVideo gameQuicksortRevision controlCompilation albumComputer fileWrapper (data mining)Level (video gaming)Open setGeometryShape (magazine)Vector spaceInformationComputer animation
13:53
Inflection pointPoint (geometry)State of matterFibonacci numberWrapper (data mining)Server (computing)Computer configurationFile formatSource codeTable (information)GeometryIntegerScale (map)RankingAnalytic continuationShape (magazine)UnicodeCodierung <Programmierung>Client (computing)InformationLine (geometry)MultiplicationPolygonFinitary relationVapor barrierAddress spaceCountingDigital filterPlanningReading (process)Drop (liquid)InformationWrapper (data mining)Connected spaceNatural numberComputer fileShape (magazine)Table (information)File formatServer (computing)Line (geometry)Codierung <Programmierung>CASE <Informatik>Computer configurationDatabaseStatement (computer science)Sheaf (mathematics)Greatest elementOracleSource codeBitLevel (video gaming)Scaling (geometry)Lie groupDirectory servicePoint (geometry)Parameter (computer programming)Computer animationSource code
17:18
CASE <Informatik>Connected spaceSource codeRemote procedure callFile formatComputer configurationRevision controlInformationAsynchronous Transfer ModeBlock (periodic table)
17:47
Sanitary sewerMessage passingMoving averageServer (computing)Configuration spaceClient (computing)Set (mathematics)Asynchronous Transfer ModeError messageMessage passingFlow separationLecture/Conference
18:09
Wrapper (data mining)Server (computing)Client (computing)Message passingSet (mathematics)Message passingRepetitionMixed realityWrapper (data mining)JSONXMLUMLLecture/Conference
18:37
Interface (computing)Line (geometry)Source codeElectronic mailing listMereologyDatabase transactionNatural numberComputer animationLecture/Conference
20:03
Source codeBitConnected spaceMathematicsNumbering schemeLecture/Conference
20:24
Numbering schemeConnected spaceSource codeoutputLecture/Conference
20:57
Wrapper (data mining)CodeLine (geometry)Connected spaceWindowType theoryComputer animationXML
21:27
Server (computing)Directory serviceLocal ringType theoryConnected spaceClient (computing)MappingScripting languageAuthenticationWindowLoginSolitary confinementOffice suiteMehrplatzsystemLecture/Conference
22:33
MathematicsCASE <Informatik>Computer virusService (economics)Software developerNumbering schemeLecture/Conference
23:07
Wrapper (data mining)Wrapper (data mining)CASE <Informatik>Identity managementGoodness of fitModule (mathematics)DatabaseAnalytic setImplementationAddress spaceTable (information)Regular graphSource codeNumberEndliche ModelltheorieMassPhysical lawService (economics)XMLComputer animationLecture/Conference
24:45
Staff (military)Single-precision floating-point formatDatabaseService (economics)Computer animation
Transcript: English(auto-generated)
00:10
Okay, yeah, after hearing information from the project itself, my talk is more from the user perspective, I'm using PostGIS since a long time and project, and I discovered
00:23
for a data wrapper some time ago and thought that would be a great thing to talk about. And yeah, maybe I have to tell you, I have no maps, no animations in the talk, so we will see some SQL, and I wanted to extend the fun part of it, but maybe
00:40
foreign data wrapper are fun themselves, so you will see. So the goal about foreign data wrapper is to get access to other sources via foreign data wrapper, and 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 combine the data, and yeah, we will see how this will work.
01:04
So I came from Germany, I'm from Workgroup, someone thought I'm Romanian because I had the local team t-shirt, but I'm not, I'm from Bonn and I work at Workgroup, and we do a lot of web.js application where most of our customers use PostgreSQL as database.
01:24
Yes, and if you want to try out foreign data wrapper, I will be at the Code Sprint, a community sprint tomorrow, so we could have a look, a closer look. The presentation is made up that you can easily do the steps on your own, you can use OSU Live, that's all the data that I use in the presentation,
01:43
and foreign data wrapper are there as well. So my slides, you can use pgAdmin 4 or 3 to do it, you can use it on the command line, but here's only the steps how you can install pgAdmin 4.
02:01
So, why shall we use foreign data wrapper? Why is it fun to work with them? Because we have already dblinked maybe to link from one database to another, so who knows dblink in the audience? Some of you, okay.
02:20
So this functionality is around quite a long time I think, it's an extension nowadays, and it enables you to go to another database, from your database which you are in, in this case I go from natural to OpenStreetMap, OSM local,
02:40
I take the points that we ship with OSU Live and look for cafes, they are Bucharest data, so I want to find out how many cafes, because I like coffee, are in Bucharest, and this is how I can do it with dblink, so I have to define the database I want to connect, and it was a bit of work,
03:03
I always have to define which data type the columns are that I want to connect, and then I can go on. Okay, so I found out that there are 70 cafes, no that's not, can't be, but there are some coffees in Bucharest.
03:20
So now we have an extended way, because with dblink I only can connect to other PostgreSQL databases, but I want to connect to other data sources in general, and this was implemented in an SQL standard, it's the SQL MED standard, and this is where it is defined how this connection will work.
03:45
Online it said this standard is from 2008, in the front data documentation it says 2001, so I'm not sure about it, I will find out, and when you implement this standard you can connect to two different external data sources.
04:02
So who implemented this standard? It says that MariaDB implemented it, but they did it not following the standard so strict, so they implemented a connect functionality, and db2 they connected it,
04:21
and PostgreSQL, it was invented in 2011 to PostgreSQL with read-only support in version 9.1, and now in 2013 we have write support since version 9.3. There's a long nice wiki page where you can get an overview on foreign data wrapper,
04:45
and which projects, which external projects support these functionality. So as you can see here, there are different projects listed here, like Oracle, ODBC, you can connect, you have SQLite, you have Twitter,
05:03
you have all sort of connections you can build up. And for every external data it may be a different way of installing it, so you have to check the installation and see how you can get it running. So you have one foreign data wrapper,
05:24
so we saw many sources are supported, it was not the whole list, on the wiki page there are some more projects, and for example you can connect to other databases, to other PostgreSQL databases, you can connect to flat files, and maybe the most interesting thing for us is you can connect to other geospatial data sources,
05:46
because in a minute I will talk about the OGR foreign data wrapper. And there's a project that helps you to, for example, connect to Twitter. There are different ways of installation, I mentioned this already,
06:01
and now we will have a look how, as compared with a DB-Link version, you can do a connection from one to another database with foreign data wrapper. So the process is always the same, you load an extension which enables you to connect to a foreign data source, then you create a foreign server, you create a foreign user,
06:25
because the user you use in PostgreSQL is not the same that you may use to connect to an Oracle database or to some other source, it's not always the case that you need this foreign user, but with some of the connections, and then you can start and connect to tables and build up foreign tables,
06:42
and have fun and easily work on external data as if the data would be in your database. So let's see the steps, how they look like. Create extension, a PostgreSQL FDW, so this is a foreign data wrapper that is already shipped
07:03
on installation from PostgreSQL, so you can use it out of the box without installing it. And here you see how you can create, oh, no, you can't see it so properly, but I hope you can imagine, it says create server,
07:21
and you give a name to the server, and then you say which foreign data wrapper you want to use, in my case it's PostgreSQL foreign data wrapper, and then you as well have to define the options, how you can go to this external data source and build up the connection.
07:40
For the user mapping it looks like this, you create a user, create user mapping for user, and then define which server this user should be, for which server, and you define the options, so you say, okay, take this user name and this password, and then I go to the other database and you get authenticated.
08:02
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 data, so I created Shema with a different name, and then there's a nice functionality, you can import foreign Shema from the other database,
08:20
and in my case I say, okay, import foreign Shema public, so all of the tables that are in the other database in public Shema will be imported, or only the syntax of the tables is imported to my database. When I do this, and I'm not interested in all the tables,
08:41
I could limit the import and say, oh, I only want this and that table, or I could say, okay, some tables I don't want to import, and like this you can only get the tables that you are interested in. And then you have access to this data source from your database,
09:02
and can easily run a query where you combine both data sources, and here we can see 147 fees in Bucharest. So maybe at the beginning you won't, if you use pgAdmin 3 still,
09:22
you may not find the foreign data wrapper sections, because they are not activated by default, you have to go to your options in pgAdmin and activate some of the check boxes, so foreign server, foreign data wrapper, user mapping, and foreign table.
09:40
And then you will see them in the tree, so you see foreign tables here in the middle, you see a bit more above the foreign server, and the different server, and also the user mapping. So here's a small example where you can connect to plain files.
10:02
So on OSU Live we have a password file for the programs where you need a password, so I would like to have access to this file from my database. So there's an extension, it's called file foreign data wrapper, and you see the password file is on my desktop,
10:23
and now I want to create a server, and it's a foreign data wrapper, to access to this file foreign data wrapper, and I don't need a user mapping,
10:41
and first I had to create the extension file foreign data wrapper, maybe I missed that in the slides, and then I can import this table as we saw in the example before, and have easily access to the data from this file. Okay, and I talked about proprietary software already,
11:06
Oracle I was talking about, or MS SQL, or other databases you can connect, for example Oracle I have an example here, you can create extension Oracle foreign data wrapper,
11:21
and if you have the concept it's always very similar, so for every connection, a foreign data wrapper, maybe you have, yeah, the definition is maybe a bit different, so in this case we define an option section, the DB server,
11:43
to build up the connection, and we have to create the user mapping, and then we can create our foreign tables. So like this, as well you can combine tables as if they are in your database,
12:01
when you connect to the tables on the remote database, the where clause is pushed down to the database, so not all the data is going to be loaded, the push down is already done for the required columns, and you have explained support. But now the fun part starts, we want to get access to geo data,
12:26
and there's a foreign data wrapper that was implemented by Paul Ramsey, it's an OGR foreign data wrapper, and it's as we all love and know OGR, Google, it supports a lot of vector formats,
12:42
and yeah, you can access the data from your database, so like geo package, WMS, open street map data, as we shape file KMN, and all sorts of data, even WFS3, you can connect to these formats. You can download and compile OGR foreign data wrapper on yourself,
13:04
or if you use OGR Live 13, version 13, it's already shipped with OGR Live, and you can start from at once. And there are packages available, so you could install it on your system as well. It's a long list, if you want to have a look at all the formats,
13:23
I only have the top of the list, so if you want to know about which formats are supported, you can go to OGR foreign data wrapper info with minus F, and it will give you a list of all the supported formats, and you also see whether they are only readable,
13:43
or read write access does exist. And yeah, maybe you have the sticker as well, let's have a look at S3 shape file, as long as it's still quite in use, so it's very easy to get connection to shape files,
14:04
so we have this Natural Earth data on OGR Live, and with this command you can ask OGR foreign data wrapper info to list all the data, all the shape, the layers, we say that it's correlated to the shape files that are in this directory,
14:21
and then I can define which of these layers I would like to import, so I, or whether I want to import them all like this, I can import all the layer, and then I can create this, no I can, first I can create a connection to this directory, and then I can create a foreign table,
14:43
and at the bottom you see in the options section which layer I would like to import in this, connect with this table statement. And then I can work with this table as, with the shape file as, yeah, directly from my database.
15:02
If I have encoding problems, there's a solution as well, I could add an option parameter, shape encoding, for example, and then the encoding will be fine. You have the possibility to connect to OpenStreetMap data,
15:23
so with the same concept with minus S, you can list all the layers, and then you can decide which of the layers you would like to connect. So you create a foreign server, the server gets a name already, and here you see data source, it's the OpenStreetMap file format,
15:45
you always define the format of the data that you would like to connect, and then create your foreign table. The nice thing is that with this command, OGR foreign data wrapper info will write you already the SQL on the command line,
16:03
so you can easily copy this definition, go to your database, and then maybe modify it a bit, and then run the SQL there. Okay, so you also have access to explain analyze,
16:21
I said it before in the case of Oracle, and here you can see it's a small SQL where I connect to all the points, in the column highway, which are traffic signals, so there are 600 of them,
16:42
and when I run it, you can see how the database gets the data, and here you see the foreign scan in the middle. Okay, this was an example for NTF data, how you can connect to NTF data,
17:05
then we have a very nice feature, you can connect to WFS, it's supported, it's read only, and here in the first line you see how you would delete a foreign server, so there are some connections to the user, to the table,
17:24
so you have to use this case to delete it, and let's see what happens when you connect to a WFS. So in this case, for WFS, you have to define the data source, then the format is WFS, and I think maybe your data, remote data source does not answer,
17:45
so you would like to know what happens, why it doesn't answer, and there's this config option, dpl-debug that you can set to on, and like this, you can get in the debug mode the information, maybe there's still an error with the request or with the version,
18:01
and like this it will be listed in the debug mode. Okay, so you can see here, you can set show client messages, and set it to debug 2, and like this you will get the debug messages. And then you can, in the select, you can see,
18:21
you can communicate with the WFS. And that's it, that's a short introduction into OGR, WFS, and WFS and Gen, oh no, I'm mixed, foreign data, wrapper in general, I hope you like it,
18:40
and maybe one of you would like to try the feature, and if you have questions, come around and we can talk. I think we don't have a microphone for questions, so I'm not sure.
19:01
Maybe if there's a question I can repeat it. Pardon? 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 format, but if you have a look at the list,
19:23
we can find out. No, I said before, the question was, does WFS, no.
19:43
In general, it provides, ah, transactions, I'm not sure, I'm not sure. Maybe, you know? Hi. So I happen to know, the interface itself provides the way to do transactions,
20:04
but if the underlying data source doesn't have a way to implement transactions, it just skips them. Any other questions?
20:23
Sometimes it's a little bit of a pain if the scheme of the source changes, so you have to drop and create the FTW connection, or is there a better way, like refresh, or? I think you would have to recreate it,
20:40
but maybe the RFI knows something more, but yeah. So there's not a permanent connection to the source, I think, on the remote server, so when you try to connect it, it's built up, so if you change the schema in your remote database, maybe it will get an error, but I didn't try it. So since the invention of import foreign schema,
21:03
it's much easier, because now you can just import a new one. You can just wipe it and reimport it, and that's two lines of code. Yeah. Any other questions?
21:22
Yeah. Is there any magic user mapping type to channel the active directory login on a Windows client through to making a connection to an MS SQL server?
21:44
Do you know that? What was the last part? Sorry. To connect to an... I was visiting a client, and they were talking about using the FDW to connect to the local MS SQL server, which uses their logged-in active directory stuff.
22:04
I'm not a Windows person, so I'm not really into it, but as far as I could see, you needed to authenticate the Postgres server instead of the local client using it, so if there was a way to channel the authentication
22:21
through the FDW user mapping. I don't know about that. Maybe. Yes, there is a way. You can create multiple users, and there are scripts out there to import LDAP for sure into the Postgres.
22:42
Yes. Hello. Hello. Do you know of any practical use cases to dislike? I've been using them, and I cannot say I've seen any proper real use, to be honest.
23:01
You've seen the schema changes that can bite you and stuff like that. As a developer, I would usually use APIs to, let's say, put data together with the microservices. They're not a silver bullet, but they say you should use APIs as well in there. Why would you even have a foreign data wrapper to files?
23:25
I have a nice use case. I have a customer. They have a GeoPortal, and they have lots of source modules, but they have data from a big region, and they have the parcels and the house numbers, and they don't want to update them regularly,
23:42
but want to access them via WFS. We thought about, okay, let's connect the WFS, but the search module that we have doesn't support WFS. In that case, we created a foreign data wrapper, and now we talk to this table that we created,
24:01
and that will go to the WMS and gives back the address or the parcel number, a parcel. Like that, it was a very good use case for us and stopped us from updating the data and importing them to the database regularly. It was a really good solution for them.
24:25
Any other questions? I'll take a second just for another use case. If you've got a bunch of microservices, and once you want to implement analytics on top of that, and you didn't quite design it right,
24:43
a quick way is to connect all the databases of all the data services of your microservices into one single database on Postgres, and after that, you've got powerful staff to join them. Other questions?
25:04
Thank you very much. That's it.