Archiv der Kategorie: Software

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

(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.

Linuxwochen-Vortrag: Open Source im Startup

Ich halte auch heuer einen Vortrag bei den Linuxwochen Wien, diesmal mit dem Titel „Open Source im Startup”. Es ist eine große Ehre, gleich den ersten Vortrag nach der Eröffnung halten zu dürfen.

Diesmal geht es weniger um mein Kernthema Data Science, sondern um allgemeine IT und den Einsatz verschiedener Open-Source-Lösungen in einem Startup. Ich bin ja „nebenbei“ der CIO (und gleichzeitig die IT-Abteilung) von SCO2T, dem Roller-Sharing in Wien.

Im Vortrag geht es um Technologien, die ich teilweise auch schon hier im Blog vorgestellt habe: PostgreSQL und PostGIS, Foreign Data Wrappers, den Pentaho-Stack, aber auch um neue Themen wie Web-APIs mit PostgREST, Traccar und so weiter.  Ich zeige einige Beispiele, wie man als Nicht-Programmierer mit eher dem Bereich Data Science zugehörigen Werkzeugen auch komplexe-IT-Systeme aufbauen kann.

Der Vortrag beginnt am Donnerstag, 4. 5. 2017 um 10 Uhr im Raum F0.01 am FH Technikum Wien, Höchstädtplatz 6.

Auf der Vortrags-Seite im Programm werden auch meine Folien hinterlegt werden, außerdem gibt es dort wie jedes Jahr die Möglichkeit, eine Bewertung des Vortrags abzugeben.

Es gibt wie jedes Jahr spannende Vorträge an allen drei Tagen, ich werde wohl öfters in einem der Hörsäle anzutreffen sein.

Window Functions in RapidMiner

(English version)

Window-Funktionen in RapidMiner

Richtige Datenbankserver wie PostgreSQL haben seit geraumer Zeit den SQL-99-Standard umgesetzt, der unter anderem Window Functions beschreibt.

Mit Window Functions lassen sich gruppenbezogene Statistiken und Kennzahlen berechnen, ohne die Ergebnismenge zu gruppieren. (Dies ist der Unterschied zur klassischen Aggregierung mit GROUP BY.) Es gibt eine Menge Anwendungsbeispiele: es lassen sich laufende Summen, gleitende Mittelwerte, Anteile innerhalb der Gruppe, gruppenweise Minima oder Maxima und noch viele andere Dinge berechnen. Manche Leute meinen, daß mit Window Functions eine neue Ära für SQL begonnen hat, und ich neige dazu, ihnen zuzustimmen.

RapidMiner hat bislang keine Window Functions eingebaut. Erfahrene Data Scientists, die Bedarf an dieser Funktionalität hatten, haben diese mit verschiedenen Operatoren (Loops, gruppenweise Aggregation und Join usw.) selbst nachbauen können, aber das ist alles andere als trivial.

Um die Funktionalität für einen größeren Benutzerkreis zu öffnen und gleichzeitig eine wiederverwendbare Implementierung zu schaffen, habe ich das Projekt rapidminer-windowfunctions ins Leben gerufen und eine erste funktionierende Implementierung hochgeladen.

Alle im RapidMiner-Operator „Aggregate“ eingebauten Aggregationsfunktionen lassen sich verwenden: sum, average, minimum, maximum usw. Zusätzlich sind die Funktionen row_number, rank und dense_rank verfügbar.

Im Projektordner sind der eigentliche Prozess und aktuell drei Beispielprozesse enthalten, um gleich verschiedene Anwendungsmöglichkeiten testen zu können.

Golf-Datensatz: Gruppenmittelwert der Temperatur und Rang innerhalb der Gruppe für Luftfeuchtigkeit
  • Golf: Der berühmte Golf-Dataset wird aus dem mitgelieferten Beispiel-Repository geladen. Für jede Gruppe basierend auf dem „Outlook“-Attribut wird die Durchschnittstemperatur berechnet und als Attribut hinzugefügt. Ein zweites neues Attribut enthält den Rang des Datensatzes bei Sortierung nach dem Wert der Luftfeuchtigkeit. Gleiche Werte erhalten den gleichen Rang.
  • Iris: Ein weiterer Standard-Dataset, Iris, wird geladen, und es wird pro Spezies der Durchschnittswert fürs Attribut „a3“ berechnet. Dieser Mittelwert wird dann genutzt, um Exemplare herauszufiltern, die mehr als 10 % vom Gruppenmittelwert entfernt sind.
  • Deals: Der mitgelieferte Dataset „Deals“ wird geladen. In jeder Kundengruppe aus Geschlecht und Zahlungsmethode werden die drei ältesten Kunden gesucht, die anderen werden herausgefiltert.

Das zeigt schon die Bandbreite der Möglichkeiten, die Window Functions bieten. Die Möglichkeit, sie leicht einzusetzen und Indikatoren sowie Kennzahlen wie „Transaktionszähler des Kunden im Monat“, „Anteil des Produkts an der Monatssumme“ und ähnliche erzeugen zu können, wird viele Prozesse vereinfachen, oder neue Möglichkeiten eröffnen.

Implementierung

Nach einigen Prüfungen, z. B. ob die benötigten Makros gesetzt sind, werden neue Attribute zur Gruppierung und Identifizierung von Examples angelegt. Das zusammengesetzte Gruppierungsattribut wird dabei aus den Werten der als groupFields angegebenen Attribute generiert. Das Identifizerungs-Attribut muß mit einem Groovy-Skript generiert werden, da Generate Id mit vielen Datensätzen nicht funktionieren würde. (Generate Id funktioniert nicht, wenn der Datensatz bereits ein Attribut mit der Rolle id enthält.)

Die drei Spezialfunktionen, die in Aggregate nicht enthalten sind, werden gesondert behandelt. Die Untergruppen werden nach dem ausgewählten Attribut sortiert und der Datensatzzähler bzw. die Rangfolge berechnet.

Die Standard-Aggregierungsfunktionen von RapidMiner werden einfach auf jede Subgruppe angewendet, dabei entsteht jeweils eine gruppierte Zeile. Diese wird wieder mit den Originaldaten gejoint.

Einschränkungen

Gegenüber dem SQL-Standard und der in Datenbanken implementierten Funktionalität fehlt noch einiges. Z. B. erlauben Datenbanken bei Funktionen wie SUM die Angabe eines Sortierkriteriums und berechnen dann eine kumulierte Summe für jede Zeile, statt die gleiche Summe wiederholt auszugeben.

Der Prozess ist auch recht langsam, wenn viele Gruppen existieren. Dies ergibt eine große Anzahl von Filterungen in der Schleife. Bei den speziellen Rang- und Zählerfunktionen ist das Problem besonders ausgeprägt, da in jedem Durchlauf ein Groovy-Interpreter gestartet wird, was erhebliche Zeit kostet.

Das Konzept von Window-Definitionen geht in SQL-Datenbanken über Selektion von Gruppen hinaus. Z. B. kann man in SQL auf den vorherigen oder nächsten Datensatz zugreifen (LAG bzw. LEAD), ein fixes Fenster von X Zeilen definieren, und in den Daten „nach vorne“ schauen. Diese Dinge sind gegenwärtig nicht im Prozess eingebaut.

Mögliche Erweiterungen

Es spricht nichts dagegen, einige von den in SQL zur Verfügung stehenden Funktionen wie percent_rank, ntile oder first/last_value einzubauen. Dies wird bei Bedarf sicher passieren.

Die Funktionalität, über ORDER BY das Verhalten der Aggregierungsfunktionen zu ändern (z. B. für kumulierte Summen), erscheint mit RapidMiner-Mitteln nicht einfach. Allerdings ließe sich die kumulierte Summe leicht mit einem weiteren Groovy-Skript implementieren.

Bezugsquelle

Das Projekt kann auf Github heruntergeladen oder ausgecheckt werden. Es enthält Dokumentation und Beispielprozesse. Die Lizenz ist Apache 2.0, somit sind Einsatz und Weiterentwicklung uneingeschränkt möglich. Es ist also ein klassisches Open-Source-Projekt, das von der Beteiligung und Weiterentwicklung durch die Community leben soll.

Window functions in RapidMiner

For powerful database software like PostgreSQL the SQL-99 standard has been implemented for a long time. Window functions are an important part of SQL-99.

Window functions are used for calculating groupwise statistics and measures without actually grouping the result. (This is the difference between Window functions and the well-known aggregation with GROUP BY.) There are many applications: running sums, moving averages, ratios inside a group, groupwise minimum and maximum and so on. Some people consider the introduction of Window functions the beginning of a new era for SQL. I agree with this quite strongly.

RapidMiner doesn’t offer Window functions, yet. Experienced data scientists were able to build this functionality when they had to, using loops, aggregation and join, but these processes are not easy to create and debug.

My goal is to open this functionality for a larger group of potential users and to create a reusable implementation. So I started the rapidminer-windowfunctions project and uploaded the first working version.

All functions built into RapidMiner’s Aggregate operator can be used: sum, average, minimum, maximum etc. Additional functions are row_number, rank and dense_rank.

The project ships with the actual process and a number of example processes. These demonstrate different applications of Window functions on public datasets available in RapidMiner Studio.

Golf dataset with groupwise temperature average and rank by humidity
  • Golf: The process loads the well-known Golf dataset from the Samples repository. For each group defined by the attribute “Outlook”, the average temperature gets calculated. A second attribute ranks the examples by the attribute “Humidity”. Identical values get the same rank.

  • Iris: This is another standard dataset, available from RapidMiner’s Samples repository. The process calculates the average value for the attribute “a3”. This group average is used to filter out examples which differ from the average by more than 10 %.

  • Deals: Another data set built into RapidMiner. This process considers all combinations of Gender and Payment Method as groups and ranks the customers based on their age. Only the 3 oldest customers per group are retained.

This is just a small sample of all the functionality available with Window Functions. Being able to use them easily for building indicators and measures like “transaction counter per customer and month” or “the product’s contribution percentage” will open new possibilities for modelling or reporting.

Implementation

The process checks a few preconditions, for example required parameter macros. It creates new attributes for grouping and identifying examples. The grouping attribute is built from all values given in the groupFields parameter. The identifying attribute needs to be generated in a Groovy script, as Generate Id would fail on too many real-world datasets. (It doesn’t work when the dataset already has an attribute with the role id.)

The three special functions not available in Aggregate are implemented with Groovy scripts. The process sorts the subgroups by the selected attributes and calculates the record counter or the rank.

The implementation of the standard aggregations is simpler: each subgroup is aggregated and the result is joined with the original data.

Limitations

Some functionality is missing compared to the SQL standard and to database systems. For example, in SQL databases, you can use ORDER BY in the window function to specify an ordering and calculate cumulative sums for each row instead of a groupwise sum.

The process is quite slow if many groups exist in the dataset. Having many groups results in a large number of filters in the loop. The performance is especially bad with the special ranking and counter functions because they start a Groovy interpreter in each iteration, which takes a lot of time compared to other operators.

The concept of window definition in SQL databases covers more than just the selection of groups. For example, you can access the previous and next records (LAG and LEAD), define a fixed window of N rows, and look forward in the data. These things are not available in the current version of the process.

Possible extensions

It’s entirely possible to implement more SQL standard functions like percent_rank, ntile and first/last_value. This will happen for sure when there’s a need.

It seems to be hard to change the behaviour of aggregation functions using ORDER BY (e. g. for cumulative sums) with RapidMiner means. However, special cases like the cumulative sum could be implemented with another Groovy script.

Getting the process

You can download the process or check it out on Github. The project also contains documentation and example processes. The license is Apache 2.0, so there are no restrictions on usage or further development. It is intended to become an Open Source project with the participation of and further development by the community.

Owncloud als Laufwerk einbinden

(English version)

Owncloud ist eine sehr nützliche Software, um Dateien auf dem eigenen Server abzulegen, für andere freizugeben und von mehreren Rechnern aus zu nutzen. Technisch findet der Dateizugriff entweder über den Browser oder übers WebDAV-Protokoll statt.

Als Berater bin ich oft in fremden Netzwerken unterwegs, die alle möglichen Einschränkungen haben, z. B. auch häufig die großen Cloud-Speicher-Anbieter blockieren. Ein eigener Server, auf den man über HTTPS zugreifen kann, ist da sehr nützlich.

Unter Linux ist der Aufwand, die Inhalte ins Dateisystem einzubinden, dank davfs2 nicht sehr groß. Die Konfiguration ist gut beschrieben und schnell erledigt.

Windows ist ein anderes Thema. Die Anleitung von Owncloud selbst funktioniert nicht unter allen Windows-Versionen; man müßte in der Registry Einstellungen setzen, damit es geht. Ohne Admin-Rechte ist das zum Scheitern verurteilt.

Vor einiger Zeit habe ich CarotDAV entdeckt und bin schnell ein Fan davon geworden. Neben der Installationsversion existiert auch eine „Portable“-Version, die keine Installation und somit keine Administratorrechte braucht – sie läuft auch von einem USB-Stick.

Neben WebDAV für Owncloud beherrscht CarotDAV auch weitere Anbieter wie Dropbox, Google Drive, OneDrive usw.

Der Clou ist, daß man unter Tool/Setting/DAV Server einen Laufwerksbuchstaben einrichten kann, unter dem alle konfigurierten Zugänge (Owncloud und andere) als Verzeichnisse zur Verfügung stehen. Somit kann man sie ohne Zusatzsoftware und Installation einfach in allen Windows-Programmen verwenden.

CarotDAV-Laufwerk im Explorer

Es ist eine gute Idee, ein „Master Password“ zu setzen. Damit wird die Konfiguration verschlüsselt, die Paßwörter der einzelnen Dienste und Server sind also geschützt. Beim Starten von CarotDAV muß das Paßwort eingegeben werden; wenn man den Computer verläßt, kann man den Desktop sperren und/oder CarotDAV beenden.

Mounting Owncloud as a drive

Owncloud is a great software for storing files on your own server, sharing with others and using them from many locations. Data access is done in the browser or using the WebDAV protocol.

As a consultant I’m frequently working in foreign corporate networks with a variety of limitations. The big cloud storage providers are frequently blocked there. Having my own server with HTTPS access is very useful in these situations.

On Linux, mounting the remote service is easy using davfs2. The configuration is easy and well documented.

Windows is another topic, however. The Owncloud tutorial doesn’t work on many Windows versions or requires changes in the registry that can be only performed with admin rights.

Fortunately I found CarotDAV and quickly became a fan. In addition to the installable version there’s also a „Portable“ one that doesn’t require administrator rights and also runs from a USB stick.

CarotDAV supports Owncloud access over WebDAV, but also other providers like Dropbox, Google Drive, OneDriver and more.

A great feature is the ability to specify a drive letter in Tool/Setting/DAV Server. Each configured account (Owncloud and others) is displayed as a subdirectory of this drive. This means that all Windows programs are able to work with the files stored on all your cloud storage providers.

CarotDAV drive in Windows Explorer

It’s a good idea to set a Master Password. This encrypts the configuration and protects the passwords of the cloud services. When starting CarotDAV, the password must be entered; if you leave the computer, you can lock the desktop or end CarotDAV.