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ß!