OLAP Cube vs. Elasticsearch

von Peter Soth

In diesem Blog-Post möchte ich die Möglichkeiten aufzeigen, die Elasticsearch im Bereich Business Analytics bietet. Als Basis benutzte ich wiederum Testdaten aus der Publikation »Excel 2010 im Controlling« [1], das im Verlag Rheinwerk (früher Galileo Computing) erschienen ist. Diesmal ist es eine CSV-Datei mit 1,5 Millionen Zeilen. Da diese für Excel zu umfangreich ist (~1 Million Zeilen Limit in Excel 2007), wird im Buch PowerPivot und OLAP Cubes (auch als Datenwürfel bezeichnet) in Kombination mit dem Microsoft SQL Server benutzt.

OLAP ist die Abkürzung für »Online Analytical Processing« und dient der Abfrage von mehrdimensionalen Informationen, analog zu einer Pivot-Tabelle. In unserem Beispiel ist die erste Dimension das Land, die zweite das Produkt und die dritte das Jahr (zeitliche). Die Dimensionen können in einem OLAP-Cube je nach Anforderung flexibel gewählt werden, jedoch sind spätere Änderungen äußerst aufwändig. Des Weiteren gibt es noch die Faktentabellen, die mathematische Auswertungen, wie berechnete Umsätze enthalten. Solch ein Datenbankschema wird auch als Star-Schema bezeichnet. Vorteil des Star-Schemas besteht darin, dass nur eine geringe Anzahl von Joins benötigt werden (bessere Performance bei großen Datenmengen einer RDB). Die Abfrage von OLAP-Cubes erfolgt mit einer speziellen Sprache, genannt MDX (Multidimensional Expressions).

In Elasticsearch wird dies alles nicht benötigt. Die Daten müssen demzufolge nicht voraggregiert in ein Star-Schema geladen werden, sie werden einfach flach (denormalisiert) in einem JSON-Dokument gespeichert. Will man Relationen in Elasticsearch verwenden, so gibt es die Möglichkeit »Nested Objects« bzw. »Parent/Child Relationships« zu benutzen. In meinem Beispiel habe ich zu Testzwecken »Nested Objects« für die Kunden und die Produkte eingefügt. Man sollte jedoch darauf verzichten, sofern es machbar ist, da dies komplexere Abfragen gegen Elasticsearch bewirkt.  

Folgendes Python-Skript erzeugt das Elasticsearch-Schema:

from elasticsearch import Elasticsearch # http://elasticsearch-py.readthedocs.org/en/master/api.html?highlight=mapping

es = Elasticsearch()

mapping = '''
{
   "mappings":{
      "orders":{
         "_timestamp":{
            "enabled":"true"
         },
         "properties":{
            "order_id":{
               "type":"string"
            },
            "customer_id":{
               "type":"string"
            },
            "VG":{
               "type":"string"
            },
            "date1":{
               "type":"date",
               "format":"dd.MM.yyyy HH:mm:ss"
            },
            "date2":{
               "type":"date",
               "format":"dd.MM.yyyy HH:mm:ss"
            },
            "date3":{
               "type":"date",
               "format":"dd.MM.yyyy HH:mm:ss"
            },
            "product_code":{
               "type":"string"
            },
            "amount":{
               "type":"double"
            },
            "customer_name":{
               "type":"string",
               "fields": {
                    "raw" : {
                    "type": "string",
                    "index": "not_analyzed"
                    }
                }
            },
            "street":{
               "type":"string"
            },
            "city":{
               "type":"string"
            },
            "region":{
               "type":"string"
            },
            "zip":{
               "type":"string"
            },
            "country":{
               "type":"string",
               "fields": {
                    "raw" : {
                    "type": "string",
                    "index": "not_analyzed"
                    }
                }
            },
            "customer":{
               "type":"nested",
               "properties":{
                  "id":{
                     "type":"string"
                  },
                  "company_name":{
                     "type":"string",
                     "fields": {
                        "raw" : {
                        "type": "string",
                        "index": "not_analyzed"
                        }
                     }
                  },
                  "contact_name":{
                     "type":"string"
                  },
                  "contact_title":{
                     "type":"string"
                  },
                  "address":{
                     "type":"string"
                  },
                  "city":{
                     "type":"string"
                  },
                  "region":{
                     "type":"string"
                  },
                  "postal_code":{
                     "type":"string"
                  },
                  "country":{
                     "type":"string",
                     "fields": {
                        "raw" : {
                        "type": "string",
                        "index": "not_analyzed"
                        }
                     }
                  },
                  "phone":{
                     "type":"string"
                  },
                  "fax":{
                     "type":"string"
                  }
               }
            },
            "product":{
               "type":"nested",
               "properties":{
                  "id":{
                     "type":"string"
                  },
                  "product_name":{
                     "type":"string"
                  },
                  "quantityperunit":{
                     "type":"string"
                  }
               }
            }
         }
      }
   }
}
'''

es.indices.delete(index='olap-test')
es.indices.create(index='olap-test',body=mapping)

Mit folgendem Python-Skript erzeuge ich das JSON-File für den Bulk-Import in Elasticsearch. Es werden auch gleich noch die 1:n und m:n Relationen aufgelöst und als »Nested Objects« gespeichert. Die Datei ist zum Schluss ca. 1 GByte groß, mit dem Linux Befehl split (split -l 700000 olap_test.json ./bulk) habe ich dieses dann in kleinere Happen aufgeteilt und mit mehreren Imports (curl -XPOST localhost:9200/_bulk --data-binary @bulkaa) in Elasticsearch importiert.

import csv
import json

order_infos = []
customer_infos = []
order_details = []
product_infos = []

ORDERS_FILE = "D:\\temp\\_blogpost\\file1.txt"
CUSTOMERS_FILE = "D:\\temp\\_blogpost\\file2.txt"
ORDERDETAILS_FILE = "D:\\temp\\_blogpost\\file3.txt"
PRODUCTS_FILE = "D:\\temp\\_blogpost\\file4.txt"
JSON_FILE = "D:\\temp\\_blogpost\\file5.txt"

with open(ORDERS_FILE, encoding='utf-8') as f:
    reader = csv.reader(f, delimiter=';', quotechar='"')
    order_infos = list(reader)

with open(CUSTOMERS_FILE, encoding='utf-8') as f:
    reader = csv.reader(f, delimiter=';', quotechar='"')
    customer_infos = list(reader)

with open(ORDERDETAILS_FILE, encoding='utf-8') as f:
    reader = csv.reader(f, delimiter=';', quotechar='"')
    order_details = list(reader)

with open(PRODUCTS_FILE, encoding='utf-8') as f:
    reader = csv.reader(f, delimiter=';', quotechar='"')
    product_infos = list(reader)

row = 1
with open(JSON_FILE, 'w', encoding='utf-8') as jsonfile:
    for order_info in order_infos:
        #
        # find matching infos from customer csv file
        for customer_info in customer_infos:
            if order_info[1] in customer_info[0]:
                break
        #
        # orderdetails is a m:n csv mapping file
        # OrderID;ProductID;UnitPrice;Quantity;Discount
        # 10248;11;14;12;0
        # 10248;42;9,8;10;0
        # 10248;72;34,8;5;0
        product_info_list = []
        for order_detail in order_details:
            if order_info[0] in order_detail[0]:
                for product_info in product_infos:
                    if order_detail[1] in product_info[0]:
                        product_info_list.append({'id': product_info[0], 'product_name': product_info[1],
                                                  'quantityperunit': product_info[2]})
                        break

        index_line = {
            'index': {
                '_index': 'olap-test',
                '_type': 'orders',
                '_id': row
            }
        }
        order_line = {
            'order_id': order_info[0],
            'customer_id': order_info[1],
            'VG': order_info[2],
            'date1': order_info[3] if not order_info[3] == "" else None,
            'date2': order_info[4] if not order_info[4] == "" else None,
            'date3': order_info[5] if not order_info[5] == "" else None,
            'product': order_info[6],
            'amount': float(order_info[7].replace(' €', '').replace(',', '.')),
            'customer_name': order_info[8],
            'street': order_info[9],
            'city': order_info[10],
            'region': order_info[11],
            'zip': order_info[12],
            'country': order_info[13],
            'customer': {
                'id': customer_info[0],
                'company_name': customer_info[1],
                'contact_name': customer_info[2],
                'contact_title': customer_info[3],
                'address': customer_info[4],
                'city': customer_info[5],
                'region': customer_info[6],
                'postal_code': customer_info[7],
                'country': customer_info[8],
                'phone': customer_info[9],
                'fax': customer_info[10]
            },
            'product': product_info_list
        }
        row = row + 1
        json.dump(index_line, jsonfile)
        jsonfile.write('\n')
        json.dump(order_line, jsonfile)
        jsonfile.write('\n')
    jsonfile.close()

Folgendes Python-Skript zeigt die Abfrage gegen Elasticsearch, die benötigt wird, um die Pivot-Tabelle aufzubauen. Das Skript enthält auch die Query für die Nested Objects. Hierbei sieht man sehr schön, dass diese um einiges komplexer ist. Ich habe auch die Zeiten ausgegeben, die Elasticsearch benötigt. Diese sind beim initialen Aufruf fast gleich. Bei mehrmaligem Aufruf greift der Cache, dann ist die Nested Query um ca. 30% langsamer. Ich habe meinen Elasticsearch-Server in keiner Weise optimiert. Am Ende des Python-Skripts findet sich noch die ausgegebene Tabelle.

from terminaltables import AsciiTable
from elasticsearch import Elasticsearch

def addRowTable(table, col1, col2, col3, col4, col5):
    table.append([str(col1), str(col2), str(col3), str(col4), str(col5)])

def printExecutionTime(result):
    print("Ausführungszeit " + str(result['took']) + " ms")

es = Elasticsearch()

query = '''
{
  "size":0,
  "query":{
    "bool":{
      "must":[
        {"range":{"amount":{"gte":0.00}}},
        {
          "bool":{
            "should":[
              {"match":{"country":"Argentina"}},
              {"match":{"country":"Brazil"}},
              {"match":{"country":"Mexico"}},
              {"match":{"country":"Venezuela"}}
            ]
          }
        }
      ]
    }
  },
  "aggs":{
    "country":{
      "terms":{
        "field":"country.raw",
        "size":0
      },
      "aggs":{
        "customerid":{
          "terms":{
            "field":"customer_name.raw",
            "size":0
          },
          "aggs":{
            "revenue_by_year":{
              "date_histogram":{
                "field":"date1",
                "interval":"year",
                "min_doc_count":0
              },
              "aggs":{
                "revenue_total":{
                  "sum":{
                    "field":"amount"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}
'''

query_nested = '''
{
  "size":0,
  "query":{
    "bool":{
      "must":[
        {"range":{"amount":{"gte":0.00}}},
        {
          "nested":{
            "path":"customer",
            "query":{
              "bool":{
                "should":[
                  {"match":{"customer.country":"Argentina"}},
                  {"match":{"customer.country":"Brazil"}},
                  {"match":{"customer.country":"Mexico"}},
                  {"match":{"customer.country":"Venezuela"}}
                ]
              }
            }
          }
        }
      ]
    }
  },
"aggs":{
    "revenue":{
      "nested":{
        "path":"customer"
      },
      "aggs":{
        "country":{
          "terms":{
            "field":"customer.country.raw",
            "size":0
          },
          "aggs":{
            "customerid":{
              "terms":{
                "field":"customer.company_name.raw",
                "size":0
              },
              "aggs":{
                "revenue_over_time":{
                  "reverse_nested":{
                  },
                  "aggs":{
                    "revenue_by_year":{
                      "date_histogram":{
                        "field":"date1",
                        "interval":"year"
                      },
                      "aggs":{
                        "revenue":{
                          "reverse_nested":{
                          },
                          "aggs":{
                            "revenue_total":{
                              "sum":{
                                "field":"amount"
                              }
                            }
                          }
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}
'''

def executeQuery(query):
  results = es.search(index='olap-test', body=query)

  table_data = [['Land', 'Kunde', '%10s' % 'Umsatz 1994', '%10s' % 'Umsatz 1995', '%10s' % 'Umsatz 1996']]
  for country in results['aggregations']['country']['buckets']:
      addRowTable(table_data, country['key'], '', '', '', '')
      for customer in country['customerid']['buckets']:
          revenue_per_year = [0.0, 0.0, 0.0]
          for revenue in customer['revenue_by_year']['buckets']:
            if '1994' in revenue['key_as_string']:
              revenue_per_year[0] = revenue['revenue_total']['value']
            if '1995' in revenue['key_as_string']:
              revenue_per_year[1] = revenue['revenue_total']['value']
            if '1996' in revenue['key_as_string']:
              revenue_per_year[2] = revenue['revenue_total']['value']
          addRowTable(table_data, '', customer['key'], "%10.2f" % revenue_per_year[0], "%10.2f" % revenue_per_year[1], "%10.2f" % revenue_per_year[2])
  table = AsciiTable(table_data)
  print(table.table)
  printExecutionTime(results)

def executeNestedQuery(query):
  results = es.search(index='olap-test', body=query)

  table_data = [['Land', 'Kunde', '%10s' % 'Umsatz 1994', '%10s' % 'Umsatz 1995', '%10s' % 'Umsatz 1996']]
  for country in results['aggregations']['revenue']['country']['buckets']:
      addRowTable(table_data, country['key'], '', '', '', '')
      for customer in country['customerid']['buckets']:
          revenue_per_year = [0.0, 0.0, 0.0]
          for revenue in customer['revenue_over_time']['revenue_by_year']['buckets']:
            if '1994' in revenue['key_as_string']:
              revenue_per_year[0] = revenue['revenue']['revenue_total']['value']
            if '1995' in revenue['key_as_string']:
              revenue_per_year[1] = revenue['revenue']['revenue_total']['value']
            if '1996' in revenue['key_as_string']:
              revenue_per_year[2] = revenue['revenue']['revenue_total']['value']
          addRowTable(table_data, '', customer['key'], "%10.2f" % revenue_per_year[0], "%10.2f" % revenue_per_year[1], "%10.2f" % revenue_per_year[2])
  table = AsciiTable(table_data)
  print(table.table)
  printExecutionTime(results)


executeNestedQuery(query_nested)
executeQuery(query)
executeNestedQuery(query_nested)
executeQuery(query)

Das Ergebnis sieht folgendermaßen aus:

+-----------+------------------------------------+-------------+-------------+-------------+
| Land      | Kunde                              | Umsatz 1994 | Umsatz 1995 | Umsatz 1996 |
+-----------+------------------------------------+-------------+-------------+-------------+
| Brazil    |                                    |             |             |             |
|           | Hanari Carnes                      |  232872.00  |  181903.08  |  944863.26  |
|           | Queen Cozinha                      |       0.00  | 2704507.80  | 1017204.30  |
|           | Ricardo Adocicados                 |   79852.56  |  485725.92  |  621214.02  |
|           | Que Delícia                        |   17747.10  |  491754.30  |  105637.50  |
|           | Wellington Importadora             |   26235.66  |  108304.26  |  231125.46  |
|           | Gourmet Lanchonetes                |       0.00  |  521745.96  |   83500.20  |
|           | Familia Arquibaldo                 |    5821.80  |  431282.70  |       0.00  |
|           | Tradição Hipermercados             |    2535.30  |  236947.26  |  276141.12  |
|           | Comércio Mineiro                   |  149676.60  |  146333.76  |   56055.82  |
| Venezuela |                                    |             |             |             |
|           | HILARIÓN-Abastos                   |  153826.98  | 1487469.90  |  720745.36  |
|           | LILA-Supermercado                  |  248947.68  |  576151.62  |  553818.20  |
|           | LINO-Delicateses                   |       0.00  |  834376.62  |  431038.56  |
|           | GROSELLA-Restaurante               |  124492.62  |       0.00  |    2835.78  |
| Mexico    |                                    |             |             |             |
|           | Tortuga Restaurante                |  306696.18  |  448316.16  |  137876.06  |
|           | Antonio Moreno Taquería            |   41316.00  |  353233.02  |  109731.54  |
|           | Pericles Comidas clásicas          |  101787.60  |  286977.18  |  132695.20  |
|           | Ana Trujillo Emparedados y helados |    3023.58  |  104961.42  |   74969.76  |
|           | Centro comercial Moctezuma         |    6103.50  |       0.00  |       0.00  |
| Argentina |                                    |             |             |             |
|           | Cactus Comidas para llevar         |       0.00  |   32339.16  |  104296.86  |
|           | Océano Atlántico Ltda.             |       0.00  |   71270.10  |  504599.82  |
|           | Rancho grande                      |       0.00  |  115290.42  |  296329.62  |
+-----------+------------------------------------+-------------+-------------+-------------+

Fazit

Anhand dieses Beispiels kann man m.E. überaus schön sehen, dass sich mit Elasticsearch auch im Bereich Business Analytics einiges bewerkstelligen lässt. Man muss kein Star-Schema aufsetzen und dieses über nächtliche Aggregationen laden. Ebenso ist das Erlernen von MDX oder eine Einarbeitung in andere BI-Tools nicht notwendig. Besonders interessant wird solch eine Lösung, wenn man noch die Volltextsuche in dieses Szenario integriert. So könnte man beispielsweise nach dem Kundennamen suchen. Bei einem riesigen Kundenkreis ist dies bestimmt schneller als das Filtern in einer Tree-View in einem klassischen BI-Tool. Einige BI-Tools verfügen auch über eine Suche, aber eine Suchmaschine kann hier beispielsweise mit einer Fuzzy-Search (Kundennamen werden dann auch noch mit Schreibfehlern gefunden) noch mehr punkten.

Kategorien: ElasticsearchPython

Zurück