JSON-Verarbeitung in RapidMiner

(English version below)

Da man immer wieder auf Datenquellen im JSON-Format stößt, möchte ich auch zeigen, wie man sie mit RapidMiner verarbeitet.

Früher gab es nur den Operator „Json to XML“. Dieser versucht, die JSON-Datenstruktur in XML abzubilden, sodaß man sie dann mit dem vorhandenen Read XML-Operator verarbeiten kann. Leider schlägt das in vielen Fällen fehl, da JSON wesentlich weniger strukturiert sein kann als XML.

Mit der Version 6.2 wurde aber ein genialer Operator eingeführt, der die Verarbeitung extrem erleichtert und elegant macht: Json to Data. Im Prinzip bildet er die Struktur des JSON-Dokuments mit der kompletten Hierarchie inkl. Arrays in Spalten ab, die nach dem „JSON-Pfad“ zum Element benannt sind.

Natürlich können da sehr breite Datenstrukturen rauskommen, aber damit hat RapidMiner kein Problem. Mit einem simplen Trick kann man diese „unbequeme“ Datenstruktur bearbeiten: Der Transpose-Operator transponiert die Spalten in Zeilen, und erzeugt damit eine viel simplere Tabelle, die leicht weiterzuverarbeiten ist.

So sieht der Prozess zur Verarbeitung des JSON-Dokuments aus dem letzten Betrag aus:

RapidMiner-Prozess zur JSON-Verarbeitung
RapidMiner-Prozess zur JSON-Verarbeitung

(Prozess herunterladen)

Open File öffnet die URL und gibt ein File-Objekt aus. Read Document liest den Inhalt des File-Objekts und gibt ihn als Document (unstrukturierter Text) aus. JSON To Data konvertiert das Dokument in eine RapidMiner-Tabelle (in diesem Fall mit 24.000 Spalten und einer Zeile). Transpose klappt die Zeilen und Spalten dann um.

Es ist sinnvoll, einen Haltepunkt auf Transpose zu setzen (F7, Breakpoint after) und die Daten anzuschauen. Es gibt eine ID-Spalte, die den Pfad zu den Daten (in diesem Fall in Array-Syntax: [x][y]) enthält, und eine Attribut-Spalte, die die Daten selbst enthält. Alles wurde in Strings konvertiert.

Beispiel-Ausgabe:

id att_1
[0][0] 194.0
[0][10] Conglomerates
[0][11] Minnesota

Da wir die zwei Dimensionen des Arrays in zwei separaten Spalten brauchen, kopieren wir die ID-Spalte, und transformieren sie und ihre Kopie mit regulären Ausdrücken. Das Ergebnis sieht so aus:

id id2 att_1
0 0 194.0
0 10 Conglomerates
0 11 Minnesota

Das id-Feld ist die Gruppe und das id2-Feld der Spaltenindex. Diese Struktur wird mit dem Pivot-Operator so konvertiert, daß id die Zeile definiert und id2 die Spalten. Diese brauchen dann nur mehr umbenannt zu werden. Danach werden noch die String-Spalten in Zahlen konvertiert und das Ergebnis nach dem Sort-Feld sortiert.

Fertig! Elegant und intuitiv, wie von RapidMiner gewohnt.

Processing JSON documents in RapidMiner

Data scientists frequently have the requirement to process JSON documents. I’d like to show how one can process them in RapidMiner.

Until recently, the only way was using the operator „Json to XML“. This operator tries to convert the original structure of the JSON document to XML so it can be processed using Read XML. Unfortunately, JSON is usually less structured than XML so this fails quite often.

A great operator was introduced in  version 6.2 that makes processing JSON much easier and more elegant: Json to Data. It converts the structure of the JSON document to columns named by the „JSON path“ of the element.

This can lead to very broad data structures with hundreds or thousands of columns, but this is not a problem for RapidMiner. Using a simple trick, this unusual data representation can be changed: the Transpose operator flips columns to rows, so the table becomes much simpler.

This is a process for loading an example JSON array from the web and converting it into a tabular structure with metadata:

RapidMiner process for processing JSON
RapidMiner process for processing JSON

(Download process)

Open File loads the contents from the URL and returns a File object. Read Document reads the contents of the file and returns an unstructured Document object. JSON to Data takes the document and converts it into a RapidMiner example set. (In this case, the table has 24,000 columns and one row.) Transpose changes the columns into rows.

You could set a breakpoint on Transpose (F7, Breakpoint after) and look at the data. There’s an ID column which contains the JSON path of the attribute value (in this case, in array syntax: [x][y]), and an attribute value column with the data. Everything was converted to strings.

Example:

id att_1
[0][0] 194.0
[0][10] Conglomerates
[0][11] Minnesota

We need both dimensions of the array as separate columns, so we copy the ID column and transform both copies using regular expressions. This is the result:

id id2 att_1
0 0 194.0
0 10 Conglomerates
0 11 Minnesota

The id field is the group and id2 the column index. This structure is processed with the Pivot operator by using id as the row identifier and id2 as the column. Then we rename the columns, convert string columns to numbers and sort the results by the value of the Sort field.

That’s it! Elegant and intuitive, thanks to RapidMiner.

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.

JSON-Array in PostgreSQL verarbeiten

(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

Pentaho: Data integration in Dashboard ausgeben

Vor kurzem wurde ich gefragt, wie aufwändig es ist, das Ergebnis eines ETL-Jobs (also aus Pentaho Data Integration) im BA Server auf einem Dashboard auszugeben. Es ist gar nicht kompliziert und geht sehr schnell.

Als erstes erstellen wir eine Transformation und merken uns den Namen des Schrittes, dessen Ergebnis im BA Server ausgegeben werden soll. In diesem Beispiel lese ich den RSS-Feed von pentaho.com aus und selektiere daraus die Spalten Titel, Text und Datum.

Beispiel-Transformation
Beispiel-Transformation

Diese Transformation muß dann auf den Server geladen werden. Wenn man in Kettle ohne Repository oder mit einem Datei-Repository arbeitet, ist die Datei schon vorhanden; aus einem Datenbank-Repository müßte man sie exportieren (File/Export/To XML).

Die .ktr-Datei wird dann im BA Server unter Browse Files ins gewünschte Verzeichnis raufgeladen (Upload…).

Danach geht’s ans Erstellen des Dashboards. Im Hauptmenü Create New, dann CDE Dashboard. (Wenn dieser Menüpunkt nicht erscheint, muß man im Marketplace „Community Dashboard Editor“ installieren und den Server neu starten.)

Der erste Weg führt zu den Einstellungen des Dashboards:

Dashboard-Einstellungsdialog
Dashboard-Einstellungen

Interessant hier ist die Auswahl des Dashboard-Typs: blueprint oder bootstrap. Blueprint ist (noch?) Standard, aber viele neue Dashboards entstehen mit dem moderneren Bootstrap-Framework. Hier arbeiten wir mit dem Standard, blueprint.

In diesem Beispiel brauchen wir keine komplexe Dashboard-Struktur (die auch möglich wäre). Deswegen fügen wir nur eine Zeile und innerhalb der Zeile eine Spalte ein. Wir geben der Zeile einen Namen (RSSTableHTML) und stellen Span size auf 24, weil bei Blueprint die ganze Breite auf 24 Spalten aufgeteilt ist.

Dashboard-Struktur mit Spalteneinstellungen
Dashboard-Struktur

Als nächstes kommt die Datenquelle. Dazu wählen wir unter Datenquellen  aus den „KETTLE Queries“ den Eintrag „kettle over kettleTransFromFile“ und stellen die Eigenschaften ein.

Name: RSSFeedResult
Kettle Transformation File: (die hochgeladene Datei wählen)
Kettle Step name: Der Schritt, dessen Ergebnis ausgegeben werden soll

Wir können Cache=True belassen, um das Ergebnis für eine Stunde (3600 Sekunden) zu cachen.

Eigenschaften der Kettle-Datenquelle
Eigenschaften der Kettle-Datenquelle

Jetzt bleibt nur mehr die Tabelle. Unter Komponenten wählen wir Others und Table Component. Wir benennen die Komponente (RSSTable), wählen die Datenquelle (RSSFeedResult) und das HTML-Objekt, in dem die Tabelle angezeigt wird (RSSTableHTML). Fertig!

Eigenschaften der Tabellenkomponente
Tabellenkomponente

Wer mit dem Aussehen der Tabelle nicht zufrieden ist, kann unter Advanced Properties noch einiges umstellen und z. B. Style auf Classic umstellen.

Das Ergebnis der Bemühungen ist eine schöne Tabelle mit den Inhalten des RSS-Feeds.

Dashboard-Vorschau
Dashboard-Vorschau

Natürlich können die Kettle-Transformationen und die Dashboards beliebig komplex werden. Es ist nur zu beachten, daß der Datenerfassungsprozess nicht zu lang dauert, sonst warten die Benutzer des Dashboards zu lange.

Microsoft kauft Revolution Analytics

Microsoft-Blogeintrag

Das ist eine ziemliche Überraschung.

Revolution Analytics pflegt eine für Unternehmen optimierte kommerzielle Version der Open-Source-Analytik-Sprache R. Die Sprache selbst basiert auf einer früheren namens S.

Da R ein waschechtes Open-Source-Projekt mit sehr aktiver Community ist, sind die Berührungspunkte mit Microsoft auf den ersten Blick nicht klar.

Jedenfalls zeigt Microsoft damit, es mit Analytik sehr ernst zu meinen. Data science ist ja ein heißes Thema.

Was könnte aus dieser Kooperation entstehen?

Vielleicht zusätzliche Ressourcen auch für die Open-Source-R-Entwicklung, eventuell eine grafische Entwicklungsumgebung? Integration von R in MS SQL Server (andere Datenbanken haben das ja längst) und Excel? Ein neuer R-Kern in .net, zur Integration in andere Programme und zur Entwicklung nativer Windows-Applikationen? Es wird jedenfalls spannend.

5 Minutes with Ingo

Ingo Mierswa ist Gründer und Geschäftsführer von RapidMiner. In diesen Videos erklärt er Aspekte von Data science, Predictive analytics und Data mining. Ohne Formeln, aber mit viel Einhorn-Einsatz.

1. Folge: das Einhorn wird vorgestellt

2. Folge: die vier Aufgaben von Data mining

3. Folge: Der K-Nächste-Nachbarn-Algorithmus (Hinzugefügt: 2015-01-31)

4. Folge: Overfitting erklärt, und ein Gewinnspiel (Hinzugefügt: 2015-02-09)

5. Folge: Berechnung der zu erwartenden Ungenauigkeit prediktiver Modelle (Hinzugefügt: 2015-02-12)

Es kommen wöchentlich neue Folgen hinzu, hier ist die volle Playlist:

Viel Spaß!