Pivot-Tabellen mit Elasticsearch und Kibana anstatt Excel geht das?
In diesem Blog-Post möchte ich aufzeigen, wie man eine Pivot-Tabelle mit Kibana und Elasticsearch umsetzen kann. Als Basis benutzte ich eine Pivot-Tabelle aus dem Buch »Excel 2010 im Controlling«, das im Verlag Rheinwerk (früher Galileo Computing) erschienen ist. Der nachfolgende Screenshot zeigt das Beispiel:
Für den Import in Elasticsearch verwendete ich Logstash [2] mit dem untenstehenden Skript. Damit Logstash das CSV-File immer frisch einspielt, muss das sincedb File bspw. unter Windows mit del %userprofile%\.sincedb* gelöscht werden.
input {
file {
path => "[absolute path]\pivot_daten.csv"
start_position => beginning
}
}
filter {
# skip 1st line of csv file
if ([message] =~ "\bKunde\b") {
drop { }
} else {
csv {
separator => ";"
columns => ["Kunde","Januar","Februar","März","Produktgruppe","Artikel","Vertriebsgebiet"]
}
}
mutate {convert => ["Januar", "float"]}
mutate {convert => ["Februar", "float"]}
mutate {convert => ["März", "float"]}
}
output {
elasticsearch {
action => "index"
hosts => "localhost"
workers => 1
}
stdout {}
}
Jetzt kann man Kibana [3] für die Analyse aufsetzen. Folgender Screenshot zeigt das Resultat.
Vergleicht man das Ergebnis mit Excel so stellt man fest, dass jeweils die Gesamtsummen fehlen. Die Gesamtsumme auf Zeilenebene könnte man mit einem Skript auf Elasticsearch Seite [4] berechnen. Groovy Skripte können seit Version 1.4.3 aus Sicherheitsgründen jedoch nicht mehr direkt in eine Search Query eingebettet werden. Die Gesamtsumme pro Spalte kann hingegen nicht direkt berechnet werden - außer man benutzt eine zweite Search Query für die Addition der Spalten. Aus diesem Grund schrieb ich ein Python-Skript, das sich unten findet. Ich habe mich für Python entschieden, da der Source Code kompakt und dementsprechend das Wesentliche besser ersichtlich ist.
from terminaltables import AsciiTable
from elasticsearch import Elasticsearch
def addRowTable(table, col1, col2, col3, col4, col5):
table.append([str(col1), "%10.2f" % col2, "%10.2f" % col3, "%10.2f" % col4, "%14.2f" % col5])
es = Elasticsearch()
results = es.search(index='logstash*', body={
"query": {"match_all": {}},
"size": 0,
"aggs": {
"revenue": {
"terms": {
"field": "Kunde.raw",
"size": 0,
"order": {
"_term": "asc"
}
},
"aggs": {
"Januar": {
"sum": {
"field": "Januar"
}
},
"Februar": {
"sum": {
"field": "Februar"
}
},
"März": {
"sum": {
"field": "März"
}
}
}
}
}
})
table_data = [['Kunde', '%10s' % 'Januar', '%10s' % 'Februar', '%10s' % 'März', 'Gesamtergebnis']]
summe_jan = summe_feb = summe_mar = summe_ges = 0.0
for result in results['aggregations']['revenue']['buckets']:
jan = result['Januar']['value']
summe_jan += jan
feb = result['Februar']['value']
summe_feb += feb
mar = result['März']['value']
summe_mar += mar
summe = jan + feb + mar
summe_ges += summe
addRowTable(table_data, result["key"],jan, feb, mar, summe)
addRowTable(table_data, "Gesamtergebnis", summe_jan, summe_feb, summe_mar, summe_ges)
table = AsciiTable(table_data)
table.inner_footing_row_border = True
print(table.table)
Das Ergebnis als ASCII Tabelle sieht wie folgt aus und ist analog zu Excel:
+-----------------------+------------+------------+------------+----------------+
| Kunde | Januar | Februar | März | Gesamtergebnis |
+-----------------------+------------+------------+------------+----------------+
| Beispiel GmbH | 12710.00 | 10428.80 | 15203.10 | 38341.90 |
| Dummy AG | 106392.00 | 109992.50 | 114392.00 | 330776.50 |
| Felix Test AG | 13250.00 | 11550.00 | 15450.00 | 40250.00 |
| Muster & Söhne | 79.90 | 95.88 | 79.90 | 255.68 |
| Muster AG | 23406.50 | 14112.50 | 17476.70 | 54995.70 |
| No Name GbR | 125.00 | 262.50 | 362.50 | 750.00 |
| P. Robe GbR | 1117.50 | 1188.00 | 1240.00 | 3545.50 |
| Probe GmbH | 9879.65 | 16000.00 | 14495.88 | 40375.53 |
| Test & Partner | 719.10 | 998.75 | 958.80 | 2676.65 |
| Test GmbH | 8000.00 | 9600.00 | 9600.00 | 27200.00 |
| Übung AG | 29730.00 | 36645.00 | 35172.00 | 101547.00 |
| Übungsgesellschft mbH | 2625.00 | 3750.00 | 4250.00 | 10625.00 |
+-----------------------+------------+------------+------------+----------------+
| Gesamtergebnis | 208034.65 | 214623.93 | 228680.88 | 651339.46 |
+-----------------------+------------+------------+------------+----------------+
Fazit
Mit Kibana kann man eine Pivot-Tabelle aufbauen, jedoch nicht vollumfänglich, wie mit Excel. Kibana dient hier mehr der Exploration von großen Datenmengen (Big Data). Will man das gleiche Ergebnis, wie bei Excel erzielen, so muss man noch etwas programmieren. Die Filter auf Zeilen- und Spaltenebene sind beispielsweise über eine facettierte Navigation (siehe Bild unten) realisierbar. In Kibana filtert man auf Zeilenebene (Kunden) mit Hilfe der Suche von Elasticsearch. Dies hat vor allem bei mehreren tausend Einträgen (etwa Big Data) den Vorteil, dass die Filterung rascher als bei Excel vonstattengeht.