Archiv der Kategorie: PostgreSQL

Conference, Data Science, Pentaho, PostgreSQL, RapidMiner

Konferenz-Herbst 2017

Schreibe einen Kommentar

(English version)

Der Herbst dieses Jahres hält viele spannende Konferenzen bereit.

Gerade ist die Industrial Data Science 2017 in Dortmund zu Ende gegangen. Einen Tag lang hielten verschiedene Vertreter der Industrie hauptsächlich aus Deutschland interessante Vorträge über ihre Data-Science-Projekte. Es ging um komplexe Sachen wie die Vorhersage der Qualität von Stahl schon während des Verarbeitungsprozesses, von Engpässen in flexiblen Produktionsprozessen und um Produkt-Design mit automatischer Erstellung von Prozess- und Teilelisten. Die klassische Industrieanwendung „predictive maintenance“ haben diese führenden Unternehmen also bereits hinter sich. Allerdings wurden viele Projekte als noch nicht in Produktion befindlich angegeben – insofern liegt Österreich vielleicht noch gar nicht so weit zurück.

Das nächste Ereignis ist die Predictive-Analytics-Konferenz am 10. und 11. Oktober in Wien. Diese findet heuer schon zum 13. Mal statt, und ist jedes Jahr ein Pflichttermin für mich. Die Bandbreite der Vorträge in den letzten Jahren war sehr groß, und ich fand immer interessante Anregungen für meine Arbeit.

Weiter geht es dann zwischen 24. und 27. Oktober mit der Europäischen PostgreSQL-Konferenz, diesmal in Warschau. Hier werde ich über die erfolgreiche Migration der Mainframe-DB2-Datenbank nach PostgreSQL in München sprechen, und die Erfahrungen daraus.

Vom 10. bis 12. November findet dann das Pentaho-Community-Treffen in Mainz statt. Hier habe ich auch einen Vortrag eingereicht: Pentaho im Startup. Gemeint ist natürlich SCO2T. Auch wenn wir SCO2T mittlerweile nicht mehr als Startup, sondern als etabliertes Unternehmen und Marktführer bezeichnen, sind die Erfahrungen, die ich unter anderem mit Pentaho gemacht habe, hoffentlich fürs Publikum interessant. Da es schon zu viele technische Präsentationen gab, wurde ich gebeten, im Business-Track zu präsentieren, es werden also eher die Anwendungsfälle als die technischen Details zur Sprache kommen.

2017 Fall Conferences

In this autumn, many interesting conferences present themselves to the data scientist.

The Industrial Data Science 2017 conference in Dortmund, Germany is already over. For an entire day, people from leading (mostly German) industry companies gave interesting talks about their data science projects. The topics were complex: predicting steel quality during the milling process, bottlenecks in flexible production processes and product design with automatic creation of process and parts lists. These companies are already well behind the basic industrial use case of predictive maintenance. However, many of the projects are still not in production – so companies in other countries didn‘t yet lost the race.

The next conference is Predictive Analytics in Vienna, with mostly German talks. This is the 13th iteration of the conference, and I always consider it a mandatory event for me. In the last years, the range of the topics was enormous, and I was always finding new approaches and ideas for my own work.

Still in October, the European PostgreSQL Conference will take place in Warsaw this time. There I‘ll present the successfull migration of a mainframe DB2 database to Postgres in Munich and the experiences I gained from this project.

In November, this year‘s Pentaho Community Meeting is in Mainz. I also submitted a talk there about Pentaho in a startup, with the startup being SCO2T, of course. We don‘t call SCO2T a startup anymore, but an established service provider and market leader. Still, the audience is hopefully interested in learning about the use cases with Pentaho in this environment. There were already too many technical presentations submitted, so I‘ll present in the business track. This means that the talk will be more about use cases than technical details.

PostgreSQL, RapidMiner, Software

PostgreSQL-JDBC-Optionen für RapidMiner und andere Software

Schreibe einen Kommentar

(English version)

In verschiedenen Projekten habe ich mich genauer mit den Optionen des PostgreSQL-JDBC-Treibers beschäftigen müssen und damit konkrete Probleme von Java-Anwendungen, unter anderem RapidMiner gelöst. Ich möchte meine Erfahrungen hier teilen und einige der JDBC-Parameter genauer beschreiben.

defaultRowFetchSize (default: 0)

Dieser Parameter steuert die Anzahl der auf einmal angeforderten Zeilen in einem ResultSet. Der Vorgabewert von 0 ergibt eine gute Performance, solange das Abfrageergebnis nicht zu groß wird. Wenn aber sehr viele Daten zurückgegeben werden, die das Programm dann nicht mal braucht, kann der Speicher des Java-Programms (oder RapidMiner Studio) vollständig verbraucht werden.

Meine Empfehlung: zur Sicherheit eine hohe, aber noch sinnvoll in den Speicher passende Zeilenanzahl wählen, z. B. 10.000 oder 100.000. Dadurch wird nicht alles gleich aus der Datenbank geladen, sondern erst wenn die Anwendung die nachfolgende Zeile anfordert.

RapidMiner lädt die Abfrageergebnisse komplett in den Speicher, diese Einstellung bewirkt also nicht so viel wie bei einem Programm, das die Daten sequenziell liest und verarbeitet. Sie kann aber beim Einschränken des Speicherverbrauchs eines Prozesses helfen, und auch bei Verwendung von Stream Database.

stringtype (default: VARCHAR)

Prepared Statements sind bei der Entwicklung von Datenbankanwendungen sehr empfehlenswert. Auch RapidMiner verwendet sie z. B. in Write Database, Update Database und optional in Read Database.

Diese Statements haben folgende Form:


SELECT a, b, c FROM tabelle WHERE c1 = ? and c2 = ?;

INSERT INTO tabelle (a, b, c) VALUES (?, ?, ?);

Für jedes Fragezeichen wird ein Wert mit Datentyp eingegeben. Das vermeidet Typkonversionsfehler und SQL Injections, die sonst viel zu leicht auftreten (das ‚O’Connor‘-Problem).

stringtype = VARCHAR (die Standardeinstellung) bewirkt, daß PostgreSQL als String übergebene Parameter explizit als VARCHAR betrachtet. Das verursacht in einigen Situationen Probleme:


SELECT ... WHERE datumsfeld = ?; -- Datum als String übergeben

INSERT INTO booltabelle (boolfeld) VALUES (?); -- 'true' oder 'false' als String übergeben

Meine Empfehlung: stringtype = unspecified. Das erlaubt z. B. in RapidMiner die Verwendung von korrekt formatierten Datumsangaben in Nominal-Feldern, oder auch von Booleans (true/false) mit Write Database. Es hilft auch bei Legacy-Anwendungen, die mit einer anderen Datenbank entwickelt wurden, und z. B. Zeitstempel als Text an die Datenbank übergeben.

Es wären Situationen denkbar, in denen die Einstellung „unspecified“ Probleme verursachen kann, z. B. wenn die Eingabe nicht auf korrekt formatierte Daten eingeschränkt ist. Dann würde nicht schon das erste Einfügen fehlschlagen, sondern erst der Datensatz mit den falschen Daten. Die Ergebnisse in der Datenbank wären also unvollständig.

ApplicationName

Dieser Parameter kann mit einem beliebigen String belegt werden. Für Datenbank-Admins hilft es manchmal zu wissen, welche Anwendung und welcher User z. B. eine aufwändige Abfrage ausführt.

Meine Empfehlung: Den Namen der eigenen Anwendung eintragen.

currentSchema

Mit diesem Parameter kann der Suchpfad (search_path) festgelegt werden. PostgreSQL sucht standardmäßig in den Schemata pg_catalog, $user und public. Wenn die eigenen Tabellen z. B. im Schema „datamining“ liegen, können sie mit currentSchema=datamining direkt angesprochen werden.

Diese Einstellung kann in speziellen Situationen helfen, wenn z. B. eine migrierte Anwendung ein eigenes Schema verwendet und nicht darauf vorbereitet ist, Schema-Namen anzugeben.

Meine Empfehlung: Nur in begründeten Sonderfällen ändern. Die Änderung von search_path bewirkt, daß die JDBC-Verbindung sich anders verhält als z. B. ein SQL-Client (solange man diesen nicht auch umstellt). Das kann zu subtilen, erst später bemerkten Fehlern führen.

Angabe der JDBC-Parameter

Im Allgemeinen werden die Parameter an die URL der JDBC-Verbindung angehängt, z. B. so:

jdbc:postgresql://server/database?stringtype=unspecified&defaultRowFetchSize=10000&ApplicationName=user@RapidMiner

RapidMiner bietet in Manage Database Connections unter Advanced eine Liste der einstellbaren Parameter, die aber defaultRowFetchSize nicht anbietet. Solche Parameter kann man einfach an den Inhalt des Feldes „Database scheme“ anhängen und die URL überprüfen.

PostgreSQL JDBC parameters for RapidMiner and other software

Some of my projects required working with parameters of the PostgreSQL JDBC driver and using them to solve problems in Java applications like RapidMiner. I’d like to share what I learned and describe some of the parameters in detail.

defaultRowFetchSize (default: 0)

This parameter sets the number of rows that are fetched together in a ResultSet. The default value of 0 results in a good performance as long as the query result doesn’t become too large. But if the query returns a huge amount of data, the memory usage of the Java application (e. g. RapidMiner Studio) grows by a large amount.

I recommend setting a large number that limits the memory requirements, for example 10,000 or 100,000 according to the capabilities of the server and the application. This limits the number of rows fetched from the database initially. The subsequent rows will be fetched when requested by the application.

RapidMiner loads query results into the memory anyway, so this setting doesn’t change its behaviour much. Still, it can help limiting the memory usage of a process, and when using Stream Database.

stringtype (default: VARCHAR)

The recommended way to write SQL statements in application development is to use prepared statements. RapidMiner uses these in Write Database, Update Database and optionally in Read Database.

Prepared statements look like this:


SELECT a, b, c FROM tablename WHERE c1 = ? and c2 = ?;

INSERT INTO tablename (a, b, c) VALUES (?, ?, ?);

Each question mark gets a typed value assigned. This avoids type conversion errors and SQL injections that are all too easy to make otherwise (the ‚O’Connor‘ problem).

The default setting stringtype=VARCHAR forces PostgreSQL to always interpret string parameters with the VARCHAR type. This causes problems in some situations:


SELECT ... WHERE datecolumn = ?; -- date specified as string

INSERT INTO booltable (boolcolumn) VALUES (?); -- 'true' or 'false' strings

I recommend setting stringtype=unspecified. This adds flexibility e. g. in RapidMiner, for example nominal fields with dates or booleans (true/false) with the Write Database operator. It also helps with legacy applications developed with a different database in mind, that use strings to represent timestamps in the database.

There might be situations where „unspecified“ is not the right setting. If the input is not always correct, the process would only fail when encountering the wrong data, so it might end up with an incomplete data set in the database.

ApplicationName

This parameter takes an arbitrary string. It is helpful for database admins so they can see the application and the user behind long-running queries.

I recommend setting the name of the application in this parameter.

currentSchema

This parameter changes the search_path of the database. PostgreSQL searches for non-schema qualified objects in the following schemas: pg_catalog, $user, public. If your tables are in the schema „datamining“, you can set currentSchema=datamining so you can refer to them directly.

This setting can be useful in migration settings, e. g. when the legacy application uses tables in a schema but isn’t prepared to specify the schema name in queries.

I recommend leaving this setting empty and only changing it when required. When you change search_path, your JDBC connection in the application will work differently from e. g. the JDBC client (if that is left with the default settings). This might cause subtle problems later.

Setting JDBC parameters

Usually you can simply append the parameters to the JDBC connection URL like this:

jdbc:postgresql://server/database?stringtype=unspecified&defaultRowFetchSize=10000&ApplicationName=user@RapidMiner

RapidMiner offers a list of parameters in Manage Database Connections with the Advanced button. This list doesn’t offer defaultRowFetchSize, however. You can still specify this parameter by appending it to the „Database scheme“ input field and checking the URL below.

PostgreSQL, RapidMiner

Absicherung für Cron in RapidMiner Server

Schreibe einen Kommentar

(English version)

Seit der Version 7.2 ist der RapidMiner Server in einer eingeschränkten Version frei erhältlich, und es lassen sich sehr nützliche Dinge damit machen.

Eine Kernfunktion ist das geplante Ausführen von Prozessen. Das passiert mit Hilfe von Cron-Ausdrücken, wobei die Implementierung in RM Server die Cron-Syntax vorne um ein Sekunden-Feld erweitert. Das erhöht natürlich die Flexibilität, aber birgt eine Gefahr in sich: wenn man nicht aufpaßt, legt man ganz schnell einen Cron-Job an, der jede Sekunde einen Prozess startet. Das kann auch den besten Server schnell in die Knie zwingen.

Leider ist die Benutzeroberfläche so gestaltet, daß die Standardeinstellungen genau dieses Problem verursachen:

Cron Editor in RapidMiner Studio with default settings
Cron Editor in RapidMiner Studio mit den Standardeinstellungen

Wer intensiv an einem Server arbeitet, wird vielleicht früher oder später die Standardeinstellungen übernehmen. Oder es passiert jemandem in einem Training dieser Fehler. Glücklicherweise gibt es einen Weg, die Datenbank gegen diese Cron-Jobs abzusichern.

Mein RapidMiner Server verwendet eine PostgreSQL-Datenbank im Hintergrund. Die Tabelle qrtz_cron_triggers enthält die definierten Cron-Jobs. Es ist möglich, einen Trigger auf diese Tabelle zu legen, der unerwünschte Eingaben ablehnt oder, noch eleganter, korrigiert.

In PostgreSQL bestehen Trigger aus zwei Teilen: einer Triggerfunktion und dem eigentlichen Trigger. (Dadurch läßt sich eine Funktion für mehrere ähnliche Trigger verwenden, man muß sie nicht jedes Mal neu schreiben.)

Meine Triggerfunktion schaut so aus:


create or replace function check_second_cron() returns trigger
as $func$
begin
  new.cron_expression = regexp_replace(new.cron_expression, 
                                       '^\s*(\*|\d+/\d)\s', 
                                       '0 ');
  return new;
end;
$func$
language plpgsql;

Die Funktion ersetzt im hereinkommenden Cron-Ausdruck unerwünschte Sekundenangaben gegen 0. Eine unerwünschte Angabe ist * (jede Sekunde), die andere hat die Form X/Y (beginnend bei der Sekunde X, alle Y Sekunden). Hier vermeiden wir einstellige Sekundenangaben. Damit kann man einen Prozess alle 10 Sekunden oder seltener laufen lassen, aber nicht häufiger; das reduziert die Gefahr schon wesentlich.

Der reguläre Ausdruck schaut komplex aus, ist aber nicht so schlimm:

^\s* – Am Anfang darf Whitespace (Space oder Tabulator) vorkommen

(\*|\d+/\d) – Entweder ein Stern ODER Ziffer(n) gefolgt von einer Ziffer

\s – Wieder Whitespace

Der Trigger wird dann auf die Tabelle angewendet, und zwar sowohl bei Insert als auch bei Update, und auch nur, wenn der Cron-Ausdruck den unerwünschten Inhalt hat:


drop trigger if exists prevent_second_cron on qrtz_cron_triggers;
create trigger prevent_second_cron
before insert or update 
on qrtz_cron_triggers
for each row
when (new.cron_expression ~ '^\s*(\*|\d+/\d)\s')
execute procedure check_second_cron();

Damit wurde der Server gegen die unabsichtliche oder bösartige Eingabe von zu häufig ausgeführten Cron-Prozessen abgesichert. (Natürlich könnte ein Angreifer auch anders viele Prozesse gleichzeitig starten; dagegen helfen nur Queues.)

 

 

Securing Cron in RapidMiner Server

A slightly limited version of RapidMiner Server is freely available since release 7.2. It is a very useful piece of software.

Scheduled process execution is a core functionality. The execution time and frequency are specified using Cron expressions; however, the implementation in RM Server extends the usual Cron syntax by a leading Seconds field. This gives us more flexibility but is also dangerous: If we aren’t careful, we can easily schedule a process to run every second. This can cause a huge usage even on the fastest server.

Unfortunately, the default settings in the user interface expose exactly this problem:

Cron Editor in RapidMiner Studio with default settings
Cron Editor in RapidMiner Studio with default settings

People working a lot on a server will probably accept the default values, or someone in a training makes a mistake. Fortunately, there’s a way to secure the database against this kind of Cron schedule.

My RapidMiner Server uses a PostgreSQL backend database. The table qrtz_cron_triggers contains the defined Cron jobs. It is easy to create a trigger on this table that rejects undesired input, or fixes it, which I consider more elegant.

Triggers consist of two parts in PostgreSQL: a trigger function and the actual trigger. (This allows us to use the same function in many triggers instead of writing it many times.)

This is my trigger function:


create or replace function check_second_cron() returns trigger
as $func$
begin
  new.cron_expression = regexp_replace(new.cron_expression, 
                                       '^\s*(\*|\d+/\d)\s', 
                                       '0 ');
  return new;
end;
$func$
language plpgsql;

The function replaces unwanted specification of seconds by zero. One kind of unwanted entry is * (every second), the other variant has the form X/Y (start at X seconds, repeat every Y seconds). Here we deny one-digit second entries. This allows us to schedule a process every 10 or more seconds, but not more frequently. This reduces the problem by a large margin.

The regular expression seems complex but it’s not too bad:

^\s* – The beginning of the expression can contain whitespace

(\*|\d+/\d) – A star OR digit(s) followed by / and one digit

\s – Whitespace again

The trigger is placed on the table both for Insert and Update and is executed when the expression has unwanted contents:


drop trigger if exists prevent_second_cron on qrtz_cron_triggers;
create trigger prevent_second_cron
before insert or update 
on qrtz_cron_triggers
for each row
when (new.cron_expression ~ '^\s*(\*|\d+/\d)\s')
execute procedure check_second_cron();

Thus the server was secured against unintentional or malicious creation of Cron processes that run too often. (An attacker could start many processes at a time with other means, of course. This can be avoided with Queues.)

Conference, Data Science, Open Data, PostgreSQL, R, RapidMiner

Linuxwochen 2016 Wien: Citizen Data Science

Schreibe einen Kommentar

Wie schon einige Male halte ich wieder einen Vortrag bei den Linuxwochen Wien. Dieses Jahr heißt mein Thema „Citizen Data Science“.

Der Begriff „Citizen Data Scientist“ wurde von großen Beratungsfirmen geprägt. Sie verstehen darunter Mitarbeiter in Unternehmen, die keine Data-Scientist-Ausbildung haben, aber trotzdem analytisch arbeiten.

Ich möchte mich allerdings auf mein Verständnis von „Citizen“– wir alle, nicht unbedingt in einem Unternehmenskontext – konzentrieren.

Im Vortrag geht es darum, was man sich unter Data Science vorstellen kann, welche Werkzeuge und Methoden es gibt, und wie man mit frei verfügbarer Software Daten holen, zusammenführen, verarbeiten und analysieren kann.

Einige Themen: Open Data und Web-APIs; Datenbanken; Software für Analytik.

Hier sind die Vortragsfolien.

GIS, PostgreSQL, RapidMiner

GIS in RapidMiner (4) – Geo-Filter und Joins

Schreibe einen Kommentar

(English version)

Update 2020-02: GIS-Funktionalität ist jetzt als Erweiterung verfügbar.

In diesem vierten Teil geht es um die Filterung von Datensätzen und die Verbindung mehrerer Datensätze anhand geographischer Kriterien. (Um die Beispiele nachzuvollziehen, muß RapidMiner wie in der Einführung beschrieben um die GeoScript-Libraries ergänzt werden.)

Da kein eingebauter Join-Operator für geographische Kriterien existiert, bauen wir diese Operation nach, indem wir jedes Element der beiden Datensätze miteinander vergleichen und das Ergebnis dann filtern. Der Vergleich wird mit geographischen Operationen durchgeführt.

In den bisherigen Beispielen wurde mit Hilfe des Cartesian-Product-Operators jede Kombination der Datensätze gebildet. Die andere Möglichkeit ist, in einer Schleife alle Elemente eines Datensatzes mit denen des anderen zu vergleichen.

(Dies ist wieder ein Bereich, in dem PostGIS mit geographischen Indizes in der Datenbank eine wesentliche Beschleunigung bietet, die bei wirklich großen Datenmengen auch noch gut funktioniert.)

Einige Funktionen, die uns für die Verbindung von Datensätzen zur Verfügung stehen:

distance: Diese Funktion haben wir bereits kennengelernt. Wenn wir die Distanzen aller Kombinationen bestimmt haben, können wir das Ergebnis filtern, um z. B. jene herauszufiltern, deren Distanz einen bestimmten Wert nicht überschreitet.

intersects: Liefert true, wenn die Objekte sich an mindestens einem Punkt überschneiden.

intersection: Erzeugt die Überschneidung der Objekte als neues geometrisches Objekt. Der Typ des überschneidenden Bereichs orientiert sich an den verglichenen Objekten: Z. B. ist die Überschneidung einer Fläche mit einer Linie wieder eine Linie. Wir können mit dem Ergebnis natürlich weiterarbeiten und z. B. die Fläche oder andere Kennzahlen bestimmen und darauf filtern.

contains: A.contains(B) liefert true, wenn das Objekt A das Objekt B vollständig enthält, also kein Teil von B außerhalb von A liegt.

Bei intersects und intersection ist die Richtung des Aufrufs egal (A.intersects(B) ergibt das gleiche wie B.intersects(A)). Bei contains jedoch nicht: Eine Fläche A kann einen Punkt B enthalten, was im umgekehrten Fall nicht gilt.

Eine häufig verwendete geographische Operation ist das Buffering, das mit der buffer-Funktion realisiert wird. Hierbei wird um das ursprüngliche Objekt (Punkt, Linie, Fläche) eine Fläche erzeugt, deren Grenze die im Funktionsaufruf angegebene Distanz zum Objekt hat. Das Ergebnis ist somit immer eine Fläche. Damit können wir verschiedene Dinge wie Einzugsgebiete von Geschäften, die Reichweite von Funkantennen oder die tatsächliche Fläche einer als Linie mit Breitenangabe angegebenen Straße berechnen. Mit der Berechnung des Buffers werden auch häufig Distanz-Vergleiche mit Hilfe von contains oder intersects durchgeführt.

Die Vorgehensweise in RapidMiner ist diese: zuerst werden in einem Execute-Script-Operator mit Groovy/GeoScript die benötigten Ergebnisse ermittelt (z. B. contains: true/false) und danach die Ergebnismenge mit Filter Examples gefiltert, sodaß nur die Objekte übrigbleiben, auf die das gewünschte Kriterium zutrifft (z. B. contains = true oder distance < 10).

Der Beispielprozess existiert in zwei Varianten: einmal mit Cartesian Product und einmal mit Loop Examples. Die erste Variante ist deutlich schneller, braucht jedoch sehr viel Speicher, weil sie riesige Tabellen anlegen muß. Die zweite Variante braucht viel länger, aber der Speicherverbrauch ist geringer, da keine „multiplizierten“ Datensätze erzeugt werden.

Für manche Operationen wie intersects oder contains ist die Projektion unerheblich (solange beide Geometrien im gleichen Koordinatensystem angegeben sind). Für buffer müssen wir aber eine Ausdehnung angeben, somit ist es wieder zweckmäßig, mit einer Meter-basierten Projektion zu arbeiten. Deswegen transformieren die Beispielprozesse alle Datensätze in die für Österreich geeignete Projektion EPSG:3416.

Drei Datensätze werden vom Wiener Open-Data-Server geholt: Wasserflüsse (Linien), Brücken (Flächen) und Spielplätze (Punkte). Dann sucht der Prozess mit intersects und intersection die Bereiche, in denen die Brücke über Wasser führt. Mit buffer wird der Bereich um die Wasserflüsse markiert, in dem mit contains nach Spielplätzen gesucht wird. Das Ergebnis ist dann eine Liste von Spielplätzen, die nahe an einem Bach oder Fluß liegen.

Beispielprocess mit Cartesian Product

Beispielprozess mit Schleifen

Damit ist diese Serie über GIS in RapidMiner vorerst abgeschlossen. Die besprochenen Methoden decken schon eine große Anzahl von Aufgaben ab, und mit etwas Kreativität ist noch viel mehr möglich. Ich werde sicherlich noch Anwendungen und Lösungen finden und darüber auch hier berichten. Wenn etwas unklar sein sollte, beantworte ich gerne Fragen: hier in den Kommentaren, im RapidMiner-Forum, oder auch direkt. Ich wünsche viel Erfolg!

GIS in RapidMiner (4) – Geographic Filter and Joins

Update 2020-02: GIS functionality is now available in an extension.

The fourth part of this series is about filtering and joining example sets on geographic criteria. (RapidMiner needs to be extended with the GeoScript libraries as described in the Introduction for the examples to work.)

There is no built-in Join operator with support for geographic functions. So we reproduce this functionality by comparing each element of both example sets and filter the result. Geographic functions are used for the comparison.

In the examples until now, we used the Cartesian Product operator for building an example set with each combination of examples. The other way is a Loop over each element of example set 1 that compares the one example of the loop with all elements of example set 2.

(This is also an area where PostGIS shines with geographic indexes in the database that improve processing times by a huge factor, even in the case of huge tables.)

Some geographic functions usable for joining or connecting example sets:

distance: We already saw this. After calculating the distance of all combinations, we can filter the result set to only contain those within a certain distance.

intersects: Returns true if the objects have at least one point in common.

intersection: Creates a new geometry with the common parts of both objects. The type of the result depends on the compared objects: e. g. the intersection of an area and a line is again a line. The resulting geometry can be processed further, for example by calculating its area or other measures and filtering on those.

contains: A.contains(B) returns true if object A fully contains B, in other words, no part of B is outside of A.

The order of the objects is not relevant when using intersects and intersection: A.intersects(B) has the same result as B.intersects(A). This is not true for contains: An area A can contain point B but this is not true for the opposite.

Buffering is a popular geographic operation, available in the buffer function. Buffering creates an area around the original object (point, line, area) with a border in a distance specified in the function call. The result is always an area. We can calculate different things with buffering: the service area of shops, the coverage of wireless antennas or the actual area of a street that is specified as a line but has a width attribute. After creating the buffer, less-or-equal distance calculations can be done with contains or intersects.

The following work flow is available in RapidMiner: first, in an Execute Script operator we process the data using geographic functions with Groovy and GeoScript (e. g. contains: true/false); then we filter the result set with Filter Examples to only keep examples with the selected criteria (e. g. contains = true or distance < 10).

There are two variants of the example process: one with Cartesian Product and one with Loop Examples. The first version is much faster but needs a huge amount of memory as it has to create very large tables. The second version takes much longer but uses less memory, as it doesn’t need to process „multiplied“ data sets.

For some operations like intersects oder contains, the projection is not relevant (as long as both geometries use the same coordinate system). But we need to specify the border size in buffer, so it is again better to work with a meter based projection. Therefore, the example processes transform the original geometries to EPSG:3416, a projection suitable for Austria.

The process fetches three data sets from the Vienna Open Data Server: Water flows (lines), Bridges (areas) und playgrounds (points). It then uses intersects and intersection to find areas where the bridge is over water. Using buffer, it marks an area around water flows and uses contains to find playgrounds in that area. The result is a list of playgrounds in the vicinity of streams or rivers.

Example process with Cartesian Product

Example process with loops

This concludes the series about GIS in RapidMiner for now. The described methods solve a range of problems, and many more can be solved with some creativity. I will surely find use cases and solutions, and describe them here. If something is not clear, please ask: here in the comments, in the RapidMiner Forum or even directly. I wish you a lot of success!

Data Mining, Data Science, PostgreSQL, R

Data Science with PostgreSQL

Schreibe einen Kommentar

… ist der Titel des Vortrags, den ich bei der Europäischen PostgreSQL-Konferenz eingereicht habe. Die Konferenz findet vom 27. bis 30. Oktober in Wien statt. Der Vortrag wurde vom Programmkomitee (alles große Namen in der PostgreSQL-Welt) angenommen.

Im Vortrag geht es um die verschiedenen Aufgaben im Bereich Data Science und wie PostgreSQL sie abdeckt:

  • Datenintegration und ETL: Foreign Data Wrappers für den Zugriff auf andere Datenbanken, Dateien, NoSQL- und Hadoop-Datenquellen; prozedurale Sprachen für den Zugriff auf Web-APIs und andere Systeme
  • Preprocessing: Standard-SQL und fortgeschrittene Methoden
  • Data understanding: Deskriptive Statistiken mit SQL, Visualisierung mit PL/R
  • Predictive analytics: Data Mining mit PL/R und PL/Python, Modellanwendung in der Datenbank

Ich freue mich sehr, vor dem hochkarätigen Publikum einer renommierten Konferenz über diese Themen sprechen zu können.

PostgreSQL

Erfahrungen mit PostgreSQL und dem MySQL-Foreign-Data-Wrapper

Schreibe einen Kommentar

(English version)

Foreign Data Wrapper in PostgreSQL sind eine sehr elegante Möglichkeit, andere Datenbanken und andere Datenquellen wie normale Tabellen anzusprechen.

Ich habe den MySQL-FDW eingehend getestet und möchte die Erfahrungen teilen.

Generell lassen sich auch Tabellen anlegen, die aus verschiedenen Gründen danach nicht vollständig in PostgreSQL nutzbar sind. Z. B. verursachen die berüchtigten ungültigen Timestamps von MySQL (0000-00-00 00:00) Lesefehler beim Abfragen der betreffenden Zeilen. Einige spezielle Datentypen, die in MySQL und PostgreSQL gleich heißen, sind trotzdem nicht lesbar, z. B. der Datentyp POINT.

Abgesehen von diesen Problemen geht das Lesen üblicherweise problemlos. In einigen Konstruktionen (z. B. wenn Werte verglichen werden sollen) versucht das Foreign-Wrapper-Modul, PostgreSQL-Sprachelemente wie ::TYP (statt CAST(… AS TYP)) an die fremde Datenbank zu senden, was einen Fehler ergibt.

Die Lösung ist, die betreffenden Werte selbst mit der standardkonformen CAST-Syntax umzuwandeln.

INSERTs und UPDATEs sind ein anderes Thema. Hat man sich mühsam eine Abfragesyntax erarbeitet, die von beiden Datenbanken akzeptiert wird, stürzen PostgreSQL-Backends sporadisch und nicht reproduzierbar ab, sodaß das Datenbanksystem sich automatisch neu startet und dabei alle bestehenden Verbindungen abreißt. Leider wird dabei nichts geloggt, sodaß ich den Grund noch nicht herausgefunden habe. Derzeit kann ich diese Funktionalität jedenfalls nicht verwenden.

Die Anwendung wäre, Änderungen an Datensätzen mit Hilfe von Triggern direkt in der anderen Datenbank nachzuziehen. So läuft weiterhin ein periodischer Pentaho-Data-Integration-Prozesses.

Update 2016-03-06: Mit aktuellen Versionen des Foreign Data Wrappers treten keine Abstürze mehr auf. INSERTs und UPDATEs sind jetzt stabil. Somit können datenbankübergreifende Trigger-Funktionen geschrieben werden, um Werte in der MySQL-Datenbank zu aktualisieren, wenn sich etwas in PostgreSQL ändert.

Practical experience with PostgreSQL and the MySQL Foreign Data Wrapper

Foreign Data Wrappers in PostgreSQL are a very elegant way to access other datasources and use them like normal database tables.

I’d like to share the results of my tests of the MySQL FDW.

It is possible to create tables that are not fully usable in PostgreSQL afterwards, for different reasons. For example, the infamous invalid MySQL timestamp (0000-00-00 00:00) makes the record unreadable. There are data types like POINT that are called the same in MySQL and PostgreSQL but they are different enough not to be readable, as well.

Aside from these problems, reading data usually works without issues. In some queries (e. g. when comparing values not having the same data type), the foreign data wrapper module tries to use PostgreSQL-specific syntax like ::TYPE instead of the standard CAST(… AS TYPE) in the foreign database, returning errors.

The solution for this problem is to explicitly cast the values with the standard CAST syntax.

INSERT and UPDATE don’t work quite as well. After finding a query syntax that is accepted by both database systems, PostgreSQL backends start crashing sporadically and in a non-reproducible way. The database server restarts automatically but kills all existing connections. Nothing is logged, so I couldn’t find the reason yet. Therefore, I can’t use this functionality yet.

I wanted to use write access to the MySQL tables to quickly enter changes in the foreign database from a PostgreSQL trigger function. As this doesn’t work, a Pentaho Data Integration process is started periodically for the same purpose.

Update 2016-03-06: Current versions of the Foreign Data Wrapper don’t crash the PostgreSQL backend anymore. INSERT and UPDATE on foreign tables is stable now. Therefore it is possible to write trigger functions that update values in the MySQL database automatically when a value changes in PostgreSQL.

 

Data Integration, PostgreSQL

PostgreSQL: Zugriff auf fremde Datenbanken

Ein Kommentar

(English version)
Komplexe Lösungen bestehen häufig aus mehr als einer Datenbank, manchmal sogar unterschiedlichen Typs. Im Fall von Sco2t haben wir eine „geerbte“ MySQL-Datenbank, während neue Daten wegen der erweiterten GIS-Funktionalität in PostgreSQL liegen. Natürlich müssen gewisse Daten zwischen beiden Systemen synchronisiert werden.

Pentaho Data Integration ist dafür eine sehr gute Lösung und wird auch intensiv verwendet. Allerdings möchte ich in einigen Fällen sofort auf Änderungen reagieren und nicht erst eine Minute später. (Und auch nicht ständig Java-Prozesse starten, die die meiste Zeit keine Änderungen finden.) Dafür bieten sich PostgreSQL-seitig Trigger an, die Änderungen an einer Tabelle in der MySQL-Datenbank nachvollziehen.

PostgreSQL 9.1 hat die erste Version der Foreign Data Wrappers genannten Funktionalität bekommen, seit 9.3 können diese sogar in fremde Datenbanken schreiben. Ein FDW-Modul für den Zugriff auf andere PostgreSQL-Server ist in 9.4 im PostgreSQL-Contrib-Bereich dabei, Module für viele andere Datenbanken (auch NoSQL und Hadoop!) sind verfügbar.

(Linked Server sind schon seit Längerem in vielen Datenbanksystemen verfügbar, PostgreSQL hatte dafür „dblink“. Diese Funktionalität hat sich aber großteils auf Verbindungen zum eigenen Datenbanktyp beschränkt.)

EnterpriseDB hat ein FDW-Modul für MySQL realisiert. Derzeit muß man es selbst kompilieren, da in den üblichen Repositories keine fertigen Pakete enthalten sind. Das ist aber nicht schwierig.

Installation auf einem Debian-System:


git clone https://github.com/EnterpriseDB/mysql_fdw.git

apt-get install make gcc libmysqlclient-dev postgresql-server-dev-9.4

cd mysql_fdw

make USE_PGXS=1

make USE_PGXS=1 install

Damit ist das Modul übersetzt und als PostgreSQL-Extension installiert. Der Rest passiert als PostgreSQL-User mit Superuser-Rechten in SQL:


CREATE EXTENSION mysql_fdw;

CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw

OPTIONS (host '127.0.0.1', port '3306');

CREATE USER MAPPING FOR username SERVER mysql_server

OPTIONS (username 'myuser', password 'mypassword');

CREATE FOREIGN TABLE mysqltabelle (

-- Felder aus der MySQL-Tabelle mit ihrem Datentyp

)

SERVER mysql_server

OPTIONS (dbname='datenbankname', table_name='OriginalTabelleName');

Damit ist die Fremdtabelle „mysqltabelle“ in der PostgreSQL-Datenbank angelegt und kann wie eine normale Tabelle angesprochen, gelesen und beschrieben werden.

CREATE EXTENSION registriert das Modul in der aktuellen Datenbank. (Verwendet man mehrere Datenbanken auf einem Server, muß CREATE EXTENSION in jeder, die die Funktionalität braucht, ausgeführt werden.)

CREATE SERVER erzeugt die Verbindung zum fremden Datenbankserver und gibt die Verbindungsparameter an.

CREATE USER MAPPING ist notwendig, um PostgreSQL-seitige User mit einem User in der fremden Datenbank zu verknüpfen. Es können mehrere Mappings für unterschiedliche User angelegt werden.

CREATE FOREIGN TABLE definiert dann die Fremdtabelle und gibt ihren Namen in der fremden Datenbank an. Die Datentypen der Felder müssen in PostgreSQL existieren. Es ist möglich, Constraints wie NOT NULL oder Default-Werte schon PostgreSQL-seitig anzugeben.

Foreign Tables eröffnen ganz neue Möglichkeiten der Datenintegration: Daten müssen nicht mehr hin und her kopiert werden, stattdessen sind sie in der Master-Datenbank und die anderen Datenbanken arbeiten immer mit der aktuellen Kopie. Datenübernahmeprozesse können direkt in der Datenbank, ohne Zwischenschaltung einer ETL-Software ausgeführt werden. Ich bin gespannt, welche Anwendungsmöglichkeiten mir noch einfallen.

Accessing foreign databases in PostgreSQL

Many complex solutions use more than one database, sometimes even different database systems. At Sco2t we „inherited“ a MySQL database, but new data are stored in PostgreSQL because of the better GIS functionality. So some data need to be kept in sync between the two systems.

Pentaho Data Integration is a great solution for this and it’s already in heavy use. In some situations, however, I’d like to react to changes instantly instead of waiting a minute. (Also, I dont’t want to start Java processes frequently that don’t have to process any changes most of the time.) In PostgreSQL, we can use triggers for updating data in other tables, now even in the MySQL database.

PostgreSQL 9.1 was the first release with Foreign Data Wrappers functionality. With 9.3, even writing into foreign databases is supported. In 9.4 there is a FDW module for accessing other PostgreSQL servers in the Contrib area. Modules for accessing other database types (even NoSQL and Hadoop!) available.

(Linked server functionality has been available in many database systems for a long time. PostgreSQL had „dblink“. However, linked servers usually had to be of the same type.)

There is a FDW module for MySQL from EnterpriseDB. Currently we need to compile it ourselves, as there are no binary packages in the usual repositories. But it’s not hard.

This is the installation on a Debian system:


git clone https://github.com/EnterpriseDB/mysql_fdw.git

apt-get install make gcc libmysqlclient-dev postgresql-server-dev-9.4

cd mysql_fdw

make USE_PGXS=1

make USE_PGXS=1 install

After this, the module is compiled and registered as a PostgreSQL extension. The rest of the procedure has to be executed (with database superuser rights) in SQL:


CREATE EXTENSION mysql_fdw;

CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw

OPTIONS (host '127.0.0.1', port '3306');

CREATE USER MAPPING FOR username SERVER mysql_server

OPTIONS (username 'myuser', password 'mypassword');

CREATE FOREIGN TABLE mysqltable (

-- Fields of the MySQL table, including their type

)

SERVER mysql_server

OPTIONS (dbname='databasename', table_name='OriginalTableName');

The result of this script is a foreign table „mysqltable“ in the PostgreSQL database. This table can be used like any normal table, you can read and write it.

CREATE EXTENSION registers the compiled module in the current database. (When using multiple databases on a server, CREATE EXTENSION must be executed in each database that will contain foreign tables.)

CREATE SERVER creates the link to the foreign database server and defines connection parameters.

CREATE USER MAPPING is necessary to link a PostgreSQL user to a user in the foreign database. Multiple mappings can be created for different users.

CREATE FOREIGN TABLE defines the foreign table and its name in the foreign database. The field types must exist in PostgreSQL. It is possible to define constraints like NOT NULL or give default values on the PostgreSQL side.

Foreign tables open entire new worlds of possibilities for data integration. Master data don’t need to be copied to multiple databases: other databases can work directly on the original which is always current. Data integration processes run in the database, without a separate ETL software. It’ll be interesting to explore further ideas and usage scenarios.

PostgreSQL, R

JSON-Array in PostgreSQL verarbeiten

Schreibe einen Kommentar

(English version)

Neulich mußte ich eine JSON-Datenstruktur aus einer Web-Quelle holen und in die Datenbank einfügen. Das Besondere an diesen Daten ist, daß sie einfach in einem zweidimensionalen Array liegen, die Webseite interpretiert die Daten selbst. Es sind also keine Feldnamen im JSON.

In R ging es recht einfach:

require(rjson)

# JSON-Array laden, List daraus machen
global2k_lst <- fromJSON(file="http://www.forbes.com/ajax/load_list/?type=organization&uri=global2000&year=2014");
# Die Liste in einen eindimensionalen Vektor konvertieren
global2k <- unlist(global2k_lst)
# Durch die Angabe der Dimensionen wird eine Matrix daraus
dim(global2k) <- c(12,2000)
# Aus der Matrix dann ein Data Frame machen und die Felder benennen
global2k <- as.data.frame(t(global2k))
names(global2k) <- c("rank", "urlname", "name", "logoname", "sort",
                     "sales", "profits", "assets", "marketvalue",
                     "country", "industry", "us_state")

write.csv2(global2k, "/tmp/forbes2000.csv", row.names=FALSE)

Andererseits hat PostgreSQL seit Version 9.3 viele JSON-Funktionen. Wie funktioniert es also direkt in der Datenbank?

Erst mal habe ich den ganzen JSON-String in eine Tabelle ins Feld „forbes2k_json“ eingelesen. (PostgreSQL hat ja auch mit 244 kByte Daten in einem Feld kein Problem.) Der Inhalt des Arrays läßt sich dann mit PostgreSQL-Mitteln mit etwas Aufwand als Tabelle ausgeben:

-- JSON-Array in 2000 Zeilen aufspalten
with lines as (
    select json_array_elements(forbes2k_json::json) as company
    from forbes2k_json
),
-- Die Elemente der Zeilen in jeweils 12 Zeilen aufspalten, die Original-Zeilen nummerieren
entries as (
    select json_array_elements(company)::text as entry,
        row_number() over () as entrynr
    from lines
),
-- Die Felder nummerieren
fields as (
    select *,
        row_number() over (partition by entrynr) as fieldnr
    from entries
)
-- Mit 12 self joins die Tabellenstruktur aufbauen und die Felder konvertieren
select f1.entry::integer as rank,
    regexp_replace(f2.entry::text, '^"(.*)"$', '\1') as urlname,
    regexp_replace(f3.entry::text, '^"(.*)"$', '\1') as company,
    regexp_replace(f4.entry::text, '^"(.*)"$', '\1') as logoname,
    f5.entry::integer as sort,
    regexp_replace(regexp_replace(f6.entry::text, ',', ''), '^"(.*)"$', '\1')::numeric(10,3) as sales,
    regexp_replace(regexp_replace(f7.entry::text, ',', ''), '^"(.*)"$', '\1')::numeric(10,3) as profits,
    regexp_replace(regexp_replace(f8.entry::text, ',', ''), '^"(.*)"$', '\1')::numeric(10,3) as assets,
    regexp_replace(regexp_replace(f9.entry::text, ',', ''), '^"(.*)"$', '\1')::numeric(10,3) as marketvalue,
    regexp_replace(f10.entry::text, '^"(.*)"$', '\1') as country,
    regexp_replace(f11.entry::text, '^"(.*)"$', '\1') as industry,
    regexp_replace(f12.entry::text, '^"(.*)"$', '\1') as us_state
from fields f1
inner join fields f2 on f2.entrynr = f1.entrynr and f2.fieldnr = 2
inner join fields f3 on f3.entrynr = f1.entrynr and f3.fieldnr = 3
inner join fields f4 on f4.entrynr = f1.entrynr and f4.fieldnr = 4
inner join fields f5 on f5.entrynr = f1.entrynr and f5.fieldnr = 5
inner join fields f6 on f6.entrynr = f1.entrynr and f6.fieldnr = 6
inner join fields f7 on f7.entrynr = f1.entrynr and f7.fieldnr = 7
inner join fields f8 on f8.entrynr = f1.entrynr and f8.fieldnr = 8
inner join fields f9 on f9.entrynr = f1.entrynr and f9.fieldnr = 9
inner join fields f10 on f10.entrynr = f1.entrynr and f10.fieldnr = 10
inner join fields f11 on f11.entrynr = f1.entrynr and f11.fieldnr = 11
inner join fields f12 on f12.entrynr = f1.entrynr and f12.fieldnr = 12
where f1.fieldnr = 1
order by rank, sort

Kompliziert ist es nicht, nur viel zu tippen (und copy/paste-en). Da die Daten selbst keine Feld-Metadaten wie Name und Typ enthalten, muß man sie selbst festlegen.

Processing a JSON array in PostgreSQL

I needed to get a JSON data structure from the web and put its contents into a database. These data were a bit special in the sense that the JSON only contained only a two dimensional array interpreted by the website, without any field names.

In R it was quite easy:

require(rjson)

# Load JSON array and convert to a list
global2k_lst <- fromJSON(file="http://www.forbes.com/ajax/load_list/?type=organization&uri=global2000&year=2014");
# Convert to a one-dimensional vector
global2k <- unlist(global2k_lst)
# Change to a matrix by specifying the dimensions
dim(global2k) <- c(12,2000)
# Convert the matrix to a data frame and specify the field names
global2k <- as.data.frame(t(global2k))
names(global2k) <- c("rank", "urlname", "name", "logoname", "sort",
                     "sales", "profits", "assets", "marketvalue",
                     "country", "industry", "us_state")

write.csv2(global2k, "/tmp/forbes2000.csv", row.names=FALSE)

On the other hand, PostgreSQL has lots of JSON functions since release 9.3. So how to do it directly in the database?

The first step is reading the entire JSON string into a table in a field called „forbes2k_json“. (PostgreSQL can easily store 244 kBytes of data in a single field.) A not too complex query converts the array contents into a normal table:

-- JSON array into 2000 lines
with lines as (
    select json_array_elements(forbes2k_json::json) as company
    from forbes2k_json
),
-- Existing lines converted into 12 lines each, with an additional row number for 
-- the original lines
entries as (
    select json_array_elements(company)::text as entry,
        row_number() over () as entrynr
    from lines
),
-- Row numbers of the entry lines
fields as (
    select *,
        row_number() over (partition by entrynr) as fieldnr
    from entries
)
-- 12 self joins for building the table structure; convert field types
select f1.entry::integer as rank,
    regexp_replace(f2.entry::text, '^"(.*)"$', '\1') as urlname,
    regexp_replace(f3.entry::text, '^"(.*)"$', '\1') as company,
    regexp_replace(f4.entry::text, '^"(.*)"$', '\1') as logoname,
    f5.entry::integer as sort,
    regexp_replace(regexp_replace(f6.entry::text, ',', ''), '^"(.*)"$', '\1')::numeric(10,3) as sales,
    regexp_replace(regexp_replace(f7.entry::text, ',', ''), '^"(.*)"$', '\1')::numeric(10,3) as profits,
    regexp_replace(regexp_replace(f8.entry::text, ',', ''), '^"(.*)"$', '\1')::numeric(10,3) as assets,
    regexp_replace(regexp_replace(f9.entry::text, ',', ''), '^"(.*)"$', '\1')::numeric(10,3) as marketvalue,
    regexp_replace(f10.entry::text, '^"(.*)"$', '\1') as country,
    regexp_replace(f11.entry::text, '^"(.*)"$', '\1') as industry,
    regexp_replace(f12.entry::text, '^"(.*)"$', '\1') as us_state
from fields f1
inner join fields f2 on f2.entrynr = f1.entrynr and f2.fieldnr = 2
inner join fields f3 on f3.entrynr = f1.entrynr and f3.fieldnr = 3
inner join fields f4 on f4.entrynr = f1.entrynr and f4.fieldnr = 4
inner join fields f5 on f5.entrynr = f1.entrynr and f5.fieldnr = 5
inner join fields f6 on f6.entrynr = f1.entrynr and f6.fieldnr = 6
inner join fields f7 on f7.entrynr = f1.entrynr and f7.fieldnr = 7
inner join fields f8 on f8.entrynr = f1.entrynr and f8.fieldnr = 8
inner join fields f9 on f9.entrynr = f1.entrynr and f9.fieldnr = 9
inner join fields f10 on f10.entrynr = f1.entrynr and f10.fieldnr = 10
inner join fields f11 on f11.entrynr = f1.entrynr and f11.fieldnr = 11
inner join fields f12 on f12.entrynr = f1.entrynr and f12.fieldnr = 12
where f1.fieldnr = 1
order by rank, sort