Erstellen und benutzen einer Geodatenbank

In diesem Artikel soll es im Gegensatz zum vorherigen Artikel Alles über Geodaten weniger darum gehen, was man denn alles mit Geodaten machen kann, dafür aber mehr darum wie man dies anstellt. Es wird gezeigt, wie man aus dem öffentlich verfügbaren Datensatz des OpenStreetMap-Projekts eine Geodatenbank erstellt und einige Beispiele dafür gegeben, wie man diese abfragen und benutzen kann.

Wahl der Datenbank

Prinzipiell gibt es zwei große “geo-kompatible” OpenSource-Datenbanken bzw. “Datenbank-AddOn’s”: Spatialite, welches auf SQLite aufbaut, und PostGIS, das PostgreSQL verwendet.

PostGIS bietet zum Teil eine einfachere Syntax, welche manchmal weniger Tipparbeit verursacht. So kann man zum Beispiel um die Entfernung zwischen zwei Orten zu ermitteln einfach schreiben:

während dies in Spatialite “nur” mit einer normalen Funktion möglich ist:

Trotztdem wird in diesem Artikel Spatialite (also SQLite) verwendet, da dessen Einrichtung deutlich einfacher ist (schließlich sollen interessierte sich alle Ergebnisse des Artikels problemlos nachbauen können, ohne hierfür einen eigenen Datenbankserver aufsetzen zu müssen).

Der Hauptunterschied zwischen PostgreSQL und SQLite (eigentlich der Unterschied zwischen SQLite und den meissten anderen Datenbanken) ist, dass für PostgreSQL im Hintergrund ein Server laufen muss, an welchen die entsprechenden Queries gesendet werden, während SQLite ein “normales” Programm (also kein Client-Server-System) ist welches die Queries selber auswertet.

Hierdurch fällt beim Aufsetzen der Datenbank eine ganze Menge an Konfigurationsarbeit weg: Welche Benutzer gibt es bzw. akzeptiert der Server? Welcher Benutzer bekommt welche Rechte? Über welche Verbindung wird auf den Server zugegriffen? Wie wird die Sicherheit dieser Verbindung sichergestellt? …

Während all dies bei SQLite (und damit auch Spatialite) wegfällt und die Einrichtung der Datenbank eigentlich nur “installieren und fertig” ist, muss auf der anderen Seite aber auch gesagt werden dass SQLite nicht gut für Szenarien geeignet ist, in welchen viele Benutzer gleichzeitig (insbesondere schreibenden) Zugriff auf die Datenbank benötigen.

Benötigte Software und ein Beispieldatensatz

Was wird für diesen Artikel an Software benötigt?

SQLite3 als Datenbank

libspatialite als “Geoplugin” für SQLite

spatialite-tools zum erstellen der Datenbank aus dem OpenStreetMaps (*.osm.pbf) Format

python3, die beiden GeoModule spatialite, folium und cartopy, sowie die Module pandas und matplotlib (letztere gehören im Bereich der Datenauswertung mit Python sowieso zum Standart). Für pandas gibt es noch die Erweiterung geopandas sowie eine praktisch unüberschaubare Anzahl weiterer geographischer Module aber bereits mit den genannten lassen sich eine Menge interessanter Dinge herausfinden.

– und natürlich einen Geodatensatz: Zum Beispiel sind aus dem OpenStreetMap-Projekt extrahierte Datensätze hier zu finden.

Es ist ratsam, sich hier erst einmal einen kleinen Datensatz herunterzuladen (wie zum Beispiel einen der Stadtstaaten Bremen, Hamburg oder Berlin). Zum einen dauert die Konvertierung des .osm.pbf-Formats in eine Spatialite-Datenbank bei größeren Datensätzen unter Umständen sehr lange, zum anderen ist die fertige Datenbank um ein vielfaches größer als die stark gepackte Originaldatei (für “nur” Deutschland ist die fertige Datenbank bereits ca. 30 GB groß und man lässt die Konvertierung (zumindest am eigenen Laptop) am besten über Nacht laufen – willkommen im Bereich “BigData”).

Erstellen eine Geodatenbank aus OpenStreetMap-Daten

Nach dem Herunterladen eines Datensatzes der Wahl im *.osm.pbf-Format kann hieraus recht einfach mit folgendem Befehl aus dem Paket spatialite-tools die Datenbank erstellt werden:

Erkunden der erstellten Geodatenbank

Nach Ausführen des obigen Befehls sollte nun eine Datei mit dem gewählten Namen (im Beispiel bremen-latest.sqlite) im aktuellen Ordner vorhanden sein – dies ist bereits die fertige Datenbank. Zunächst sollte man mit dieser Datenbank erst einmal dasselbe machen, wie mit jeder anderen Datenbank auch: Sich erst einmal eine Weile hinsetzen und schauen was alles an Daten in der Datenbank vorhanden und vor allem wo diese Daten in der erstellten Tabellenstruktur zu finden sind. Auch wenn dieses Umschauen prinzipiell auch vollständig über die Shell oder in Python möglich ist, sind hier Programme mit graphischer Benutzeroberfläche (z. B. spatialite-gui oder QGIS) sehr hilfreich und sparen nicht nur eine Menge Zeit sondern vor allem auch Tipparbeit. Wer dies tut, wird feststellen, dass sich in der generierten Datenbank einige dutzend Tabellen mit Namen wie pt_addresses, ln_highway und pg_boundary befinden.

Die Benennung der Tabellen folgt dem Prinzip, dass pt_*-Tabellen Punkte im Geokoordinatensystem wie z. B. Adressen, Shops, Bäckereien und ähnliches enthalten. ln_*-Tabellen enthalten hingegen geographische Entitäten, welche sich als Linien darstellen lassen, wie beispielsweise Straßen, Hochspannungsleitungen, Schienen, ect. Zuletzt gibt es die pg_*-Tabellen welche Polygone – also Flächen einer bestimmten Form enthalten. Dazu zählen Landesgrenzen, Bundesländer, Inseln, Postleitzahlengebiete, Landnutzung, aber auch Gebäude, da auch diese jeweils eine Grundfläche besitzen. In dem genannten Datensatz sind die Grundflächen von Gebäuden – zumindest in Europa – nahezu vollständig. Aber auch der Rest der Welt ist für ein “Wikipedia der Kartographie” insbesondere in halbwegs besiedelten Gebieten bemerkenswert gut erfasst, auch wenn nicht unbedingt davon ausgegangen werden kann, dass abgelegenere Gegenden (z. B. irgendwo auf dem Land in Südamerika) jedes Gebäude eingezeichnet ist.

Verwenden der Erstellten Datenbank

Auf diese Datenbank kann nun entweder direkt aus der Shell über den Befehl

zugegriffen werden oder man nutzt das gleichnamige Python-Paket:

Nach Eingabe der obigen Befehle in eine Python-Konsole, ein Jupyter-Notebook oder ein anderes Programm, welches die Anbindung an den Python-Interpreter ermöglicht, können die von der Datenbank ausgegebenen Ergebnisse nun direkt in ein Pandas Data Frame hineingeladen und verwendet/ausgewertet/analysiert werden.

Im Grunde wird hierfür “normales SQL” verwendet, wie in anderen Datenbanken auch. Der folgende Beispiel gibt einfach die fünf ersten von der Datenbank gefundenen Adressen aus der Tabelle pt_addresses aus:

Link zur Ausgabe

Es wird dem Leser sicherlich aufgefallen sein, dass die Spalte “Geometry” (zumindest für das menschliche Auge) nicht besonders ansprechend sowie auch nicht informativ aussieht: Der Grund hierfür ist, dass diese Spalte die entsprechende Position im geographischen Koordinatensystem aus Gründen wie dem deutlich kleineren Speicherplatzbedarf sowie der damit einhergehenden Optimierung der Geschwindigkeit der Datenbank selber, in binärer Form gespeichert und ohne weitere Verarbeitung auch als solche ausgegeben wird.

Glücklicherweise stellt spatialite eine ganze Reihe von Funktionen zur Verarbeitung dieser geographischen Informationen bereit, von denen im folgenden einige beispielsweise vorgestellt werden:

Für einzelne Punkte im Koordinatensystem gibt es beispielsweise die Funktionen X(geometry) und Y(geometry), welche aus diesem “binären Wirrwarr” den Längen- bzw. Breitengrad des jeweiligen Punktes als lesbare Zahlen ausgibt.

Ändert man also das obige Query nun entsprechend ab, erhält man als Ausgabe folgendes Ergebnis in welchem die Geometry-Spalte der ausgegebenen Adressen in den zwei neuen Spalten Longitude und Latitude in lesbarer Form zu finden ist:

Link zur Tabelle

Eine weitere häufig verwendete Funktion von Spatialite ist die Distance-Funktion, welche die Distanz zwischen zwei Orten berechnet.

Das folgende Beispiel sucht in der Datenbank die 10 nächstgelegenen Bäckereien zu einer frei wählbaren Position aus der Datenbank und listet diese nach zunehmender Entfernung auf (Achtung – die frei wählbare Position im Beispiel liegt in München, wer die selbe Position z. B. mit dem Bremen-Datensatz verwendet, wird vermutlich etwas weiter laufen müssen…):

Link zur Ausgabe

Ein Anwendungsfall für eine solche Liste können zum Beispiel Programme/Apps wie maps.me oder Google-Maps sein, in denen User nach Bäckereien, Geldautomaten, Supermärkten oder Apotheken “in der Nähe” suchen können sollen.

Diese Liste enthält nun alle Informationen die grundsätzlich gebraucht werden, ist soweit auch informativ und wird in den meißten Fällen der Datenauswertung auch genau so gebraucht, jedoch ist diese für das Auge nicht besonders ansprechend.

Viel besser wäre es doch, die gefundenen Positionen auf einer interaktiven Karte einzuzeichnen:

Was kann man sonst interessantes mit der erstellten Datenbank und etwas Python machen? Wer in Deutschland ein wenig herumgekommen ist, dem ist eventuell aufgefallen, dass sich die Endungen von Ortsnamen stark unterscheiden: Um München gibt es Stadteile und Dörfer namens Garching, Freising, Aubing, ect., rund um Stuttgart enden alle möglichen Namen auf “ingen” (Plieningen, Vaihningen, Echterdingen …) und in Berlin gibt es Orte wie Pankow, Virchow sowie eine bunte Auswahl weiterer *ow’s.

Das folgende Query spuckt gibt alle “village’s”, “town’s” und “city’s” aus der Tabelle pt_place, also Dörfer und Städte, aus:

Link zur Ausgabe

Graphisch mit matplotlib und cartopy in ein Koordinatensystem eingetragen sieht diese Verteilung folgendermassen aus:

Die Grafik zeigt, dass stark unterschiedliche Vorkommen der verschiedenen Ortsendungen in Deutschland (Clustering). Über das genaue Zustandekommen dieser Verteilung kann ich hier nur spekulieren, jedoch wird diese vermutlich ähnlichen Prozessen unterliegen wie beispielsweise die Entwicklung von Dialekten.

Wer sich die Karte etwas genauer anschaut wird merken, dass die eingezeichneten Landesgrenzen und Küstenlinien nicht besonders genau sind. Hieran wird ein interessanter Effekt von häufig verwendeten geographischen Entitäten, nämlich Linien und Polygonen deutlich. Im Beispiel werden durch die beiden Zeilen

die bereits im Modul cartopy hinterlegten Daten verwendet. Genaue Verläufe von Küstenlinien und Landesgrenzen benötigen mit wachsender Genauigkeit hingegen sehr viel Speicherplatz, da mehr und mehr zu speichernde Punkte benötigt werden (genaueres siehe hier).

Schlussfolgerung

Man kann also bereits mit einigen Grundmodulen und öffentlich verfügbaren Datensätzen eine ganze Menge im Bereich der Geodaten erkunden und entdecken. Gleichzeitig steht, insbesondere für spezielle Probleme, eine große Bandbreite weiterer Software zur Verfügung, für welche dieser Artikel zwar einen Grundsätzlichen Einstieg geben kann, die jedoch den Rahmen dieses Artikels sprengen würden.

Interview: Does Business Intelligence benefit from Cloud Data Warehousing?

Interview with Ross Perez, Senior Director, Marketing EMEA at Snowflake

Read this article in German:
“Profitiert Business Intelligence vom Data Warehouse in der Cloud?”

Does Business Intelligence benefit from Cloud Data Warehousing?

Ross Perez is the Senior Director, Marketing EMEA at Snowflake. He leads the Snowflake marketing team in EMEA and is charged with starting the discussion about analytics, data, and cloud data warehousing across EMEA. Before Snowflake, Ross was a product marketer at Tableau Software where he founded the Iron Viz Championship, the world’s largest and longest running data visualization competition.

Data Science Blog: Ross, Business Intelligence (BI) is not really a new trend. In 2019/2020, making data available for the whole company should not be a big thing anymore. Would you agree?

BI is definitely an old trend, reporting has been around for 50 years. People are accustomed to seeing statistics and data for the company at large, and even their business units. However, using BI to deliver analytics to everyone in the organization and encouraging them to make decisions based on data for their specific area is relatively new. In a lot of the companies Snowflake works with, there is a huge new group of people who have recently received access to self-service BI and visualization tools like Tableau, Looker and Sigma, and they are just starting to find answers to their questions.

Data Science Blog: Up until today, BI was just about delivering dashboards for reporting to the business. The data warehouse (DWH) was something like the backend. Today we have increased demand for data transparency. How should companies deal with this demand?

Because more people in more departments are wanting access to data more frequently, the demand on backend systems like the data warehouse is skyrocketing. In many cases, companies have data warehouses that weren’t built to cope with this concurrent demand and that means that the experience is slow. End users have to wait a long time for their reports. That is where Snowflake comes in: since we can use the power of the cloud to spin up resources on demand, we can serve any number of concurrent users. Snowflake can also house unlimited amounts of data, of both structured and semi-structured formats.

Data Science Blog: Would you say the DWH is the key driver for becoming a data-driven organization? What else should be considered here?

Absolutely. Without having all of your data in a single, highly elastic, and flexible data warehouse, it can be a huge challenge to actually deliver insight to people in the organization.

Data Science Blog: So much for the theory, now let’s talk about specific use cases. In general, it matters a lot whether you are storing and analyzing e.g. financial data or machine data. What do we have to consider for both purposes?

Financial data and machine data do look very different, and often come in different formats. For instance, financial data is often in a standard relational format. Data like this needs to be able to be easily queried with standard SQL, something that many Hadoop and noSQL tools were unable to provide. Luckily, Snowflake is an ansi-standard SQL data warehouse so it can be used with this type of data quite seamlessly.

On the other hand, machine data is often semi-structured or even completely unstructured. This type of data is becoming significantly more common with the rise of IoT, but traditional data warehouses were very bad at dealing with it since they were optimized for relational data. Semi-structured data like JSON, Avro, XML, Orc and Parquet can be loaded into Snowflake for analysis quite seamlessly in its native format. This is important, because you don’t want to have to flatten the data to get any use from it.

Both types of data are important, and Snowflake is really the first data warehouse that can work with them both seamlessly.

Data Science Blog: Back to the common business use case: Creating sales or purchase reports for the business managers, based on data from ERP-systems such as Microsoft or SAP. Which architecture for the DWH could be the right one? How many and which database layers do you see as necessary?

The type of report largely does not matter, because in all cases you want a data warehouse that can support all of your data and serve all of your users. Ideally, you also want to be able to turn it off and on depending on demand. That means that you need a cloud-based architecture… and specifically Snowflake’s innovative architecture that separates storage and compute, making it possible to pay for exactly what you use.

Data Science Blog: Where would you implement the main part of the business logic for the report? In the DWH or in the reporting tool? Does it matter which reporting tool we choose?

The great thing is that you can choose either. Snowflake, as an ansi-Standard SQL data warehouse, can support a high degree of data modeling and business logic. But you can also utilize partners like Looker and Sigma who specialize in data modeling for BI. We think it’s best that the customer chooses what is right for them.

Data Science Blog: Snowflake enables organizations to store and manage their data in the cloud. Does it mean companies lose control over their storage and data management?

Customers have complete control over their data, and in fact Snowflake cannot see, alter or change any aspect of their data. The benefit of a cloud solution is that customers don’t have to manage the infrastructure or the tuning – they decide how they want to store and analyze their data and Snowflake takes care of the rest.

Data Science Blog: How big is the effort for smaller and medium sized companies to set up a DWH in the cloud? Does this have to be an expensive long-term project in every case?

The nice thing about Snowflake is that you can get started with a free trial in a few minutes. Now, moving from a traditional data warehouse to Snowflake can take some time, depending on the legacy technology that you are using. But Snowflake itself is quite easy to set up and very much compatible with historical tools making it relatively easy to move over.

Allgemeines über Geodaten

Dieser Artikel ist der Auftakt in einer Artikelserie zum Thema “Geodatenanalyse”.

Von den vielen Arten an Datensätzen, die öffentlich im Internet verfügbar sind, bin ich in letzter Zeit vermehrt über eine besonders interessante Gruppe gestolpert, die sich gleich für mehrere Zwecke nutzen lassen: Geodaten.

Gerade in wirtschaftlicher Hinsicht bieten sich eine ganze Reihe von Anwendungsfällen, bei denen Geodaten helfen können, Einblicke in Tatsachen zu erlangen, die ohne nicht möglich wären. Der wohl bekannteste Fall hierfür ist vermutlich die einfache Navigation zwischen zwei Punkten, die jeder kennt, der bereits ein Navigationssystem genutzt oder sich eine Route von Google Maps berechnen lassen hat.
Hiermit können nicht nur Fragen nach dem schnellsten oder Energie einsparensten (und damit gleichermaßen auch witschaftlichsten) Weg z. B. von Berlin nach Hamburg beantwortet werden, sondern auch die bestmögliche Lösung für Ausnahmesituationen wie Stau oder Vollsperrungen berechnet werden (ja, Stau ist, zumindest in der Theorie immer noch eine “Ausnahmesituation” ;-)).
Neben dieser beliebten Art Geodaten zu nutzen, gibt es eine ganze Reihe weiterer Situationen in denen deren Nutzung hilfreich bis essentiell sein kann. Als Beispiel sei hier der Einzugsbereich von in Konkurrenz stehenden Einheiten, wie z. B. Supermärkten genannt. Ohne an dieser Stelle statistische Nachweise vorlegen zu können, kaufen (zumindest meiner persönlichen Beobachtung nach) die meisten Menschen fast immer bei dem Supermarkt ein, der am bequemsten zu erreichen ist und dies ist in der Regel der am nächsten gelegene. Besitzt man nun eine Datenbank mit der Information, wo welcher Supermarkt bzw. welche Supermarktkette liegt, kann man mit so genannten Voronidiagrammen recht einfach den jeweiligen Einzugsbereich der jeweiligen Supermärkte berechnen.
Entsprechende Karten können auch von beliebigen anderen Entitäten mit fester geographischer Position gezeichnet werden: Geldautomaten, Funkmasten, öffentlicher Nahverkehr, …

Ein anderes Beispiel, das für die Datenauswertung interessant ist, ist die kartographische Auswertung von Postleitzahlen. Diese sind in fast jedem Datensatz zu Kunden, Lieferanten, ect. vorhanden, bilden jedoch weder eine ordinale, noch eine sinnvolle kategorische Größe, da es viele tausend verschiedene gibt. Zudem ist auch eine einfache Gruppierung in gröbere Kategorien wie beispielsweise Postleitzahlen des Schemas 1xxxx oft kaum sinnvoll, da diese in aller Regel kein sinnvolles Mapping auf z. B. politische Gebiete – wie beispielsweise Bundesländer – zulassen. Ein Ausweg aus diesem Dilemma ist eine einfache kartographische Übersicht, welche die einzelnen Postleitzahlengebiete in einer Farbskala zeigt.

Im gezeigten Beispiel ist die Bevölkerungsdichte Deutschlands als Karte zu sehen. Hiermit wird schnell und übersichtlich deutlich, wo in Deutschland die Bevölkerung lokalisiert ist. Ähnliche Karten können beispielsweise erstellt werden, um Fragen wie “Wie ist meine Kundschaft verteilt?” oder “Wo hat die Werbekampange XYZ besonders gut funktioniert?” zu beantworten. Bezieht man weitere Daten wie die absolute Bevölkerung oder die Bevölkerungsdichte mit ein, können auch Antworten auf Fragen wie “Welchen Anteil der Bevölkerung habe ich bereits erreicht und wo ist noch nicht genutztes Potential?” oder “Ist mein Produkt eher in städtischen oder ländlichen Gebieten gefragt?” einfach und schnell gefunden werden.
Ohne die entsprechende geographische Zusatzinformation bleiben insbesondere Postleitzahlen leider oft als “nicht sinnvoll auswertbar” bei der Datenauswertung links liegen.
Eine ganz andere Art von Vorteil der Geodaten ist der educational point of view:
  • Wer erst anfängt, sich mit Datenbanken zu beschäftigen, findet mit Straßen, Postleitzahlen und Ländern einen deutlich einfacheren und vor allem besser verständlichen Zugang zu SQL als mit abstrakten Größen und Nummern wie ProductID, CustomerID und AdressID. Zudem lassen sich Geodaten nebenbei bemerkt mittels so genannter GeoInformationSystems (*gis-Programme), erstaunlich einfach und ansprechend plotten.
  • Wer sich mit SQL bereits ein wenig auskennt, kann mit den (beispielsweise von Spatialite oder PostGIS) bereitgestellten SQL-Funktionen eine ganze Menge über Datenbanken sowie deren Möglichkeiten – aber auch über deren Grenzen – erfahren.
  • Für wen relationale Datenbanken sowie deren Funktionen schon lange nichts Neues mehr darstellen, kann sich hier (selbst mit dem eigenen Notebook) erstaunlich einfach in das Thema “Bug Data” einarbeiten, da die Menge an öffentlich vorhandenen Geodaten z.B. des OpenStreetMaps-Projektes selbst in optimal gepackten Format vielen Dutzend GB entsprechen. Gerade die Möglichkeit, die viele *gis-Programme wie beispielsweise QGIS bieten, nämlich Straßen-, Schienen- und Stromnetze “on-the-fly” zu plotten, macht die Bedeutung von richtig oder falsch gesetzten Indices in verschiedenen Datenbanken allein anhand der Geschwindigkeit mit der sich die Plots aufbauen sehr eindrucksvoll deutlich.
Um an Datensätze zu kommen, reicht es in der Regel Google mit den entsprechenden Schlagworten zu versorgen.
Neben – um einen Vergleich zu nutzen – dem Brockhaus der Karten GoogleMaps gibt es beispielsweise mit dem OpenStreetMaps-Projekt einen freien Geodatensatz, welcher in diesem Kontext etwa als das Wikipedia der Karten zu verstehen ist.
Hier findet man zum Beispiel Daten wie Straßen-, Schienen- oder dem Stromnetz, aber auch die im obigen Voronidiagramm eingezeichneten Gebäude und Supermärkte stammen aus diesem Datensatz. Hiermit lassen sich recht einfach just for fun interessante Dinge herausfinden, wie z. B., dass es in Deutschland ca. 28 Mio Gebäude gibt (ein SQL-Einzeiler), dass der Berliner Osten auch ca. 30 Jahre nach der Wende noch immer vorwiegend von der Tram versorgt wird, während im Westen hauptsächlich die U-Bahn fährt. Oder über welche Trassen der in der Nordsee von Windkraftanlagen erzeugte Strom auf das Festland kommt und von da aus weiter verteilt wird.
Eher grundlegende aber deswegen nicht weniger nützliche Datensätze lassen sich unter dem Stichwort “natural earth” finden. Hier sind Daten wie globale Küstenlinien, mittels Echolot ausgemessene Meerestiefen, aber auch von Menschen geschaffene Dinge wie Landesgrenzen und Städte sehr übersichtlich zu finden.
Im Grunde sind der Vorstellung aber keinerlei Grenzen gesetzt und fast alle denkbaren geographischen Fakten können, manchmal sogar live via Sattelit, mitverfolgt werden. So kann man sich beispielsweise neben aktueller Wolkenbedekung, Regenradar und globaler Oberflächentemperatur des Planeten auch das Abschmelzen der Polkappen seit 1970 ansehen (NSIDC) oder sich live die Blitzeinschläge auf dem gesamten Planeten anschauen – mit Vorhersage darüber, wann und wo der Donner zu hören ist (das funktioniert wirklich! Beispielsweise auf lightningmaps).
Kurzum Geodaten sind neben ihrer wirtschaftlichen Relevanz – vor allem für die Logistik – auch für angehende Data Scientists sehr aufschlussreich und ein wunderbares Spielzeug, mit dem man sich lange beschäftigen und eine Menge interessanter Dinge herausfinden kann.

How To Remotely Send R and Python Execution to SQL Server from Jupyter Notebooks

Introduction

Did you know that you can execute R and Python code remotely in SQL Server from Jupyter Notebooks or any IDE? Machine Learning Services in SQL Server eliminates the need to move data around. Instead of transferring large and sensitive data over the network or losing accuracy on ML training with sample csv files, you can have your R/Python code execute within your database. You can work in Jupyter Notebooks, RStudio, PyCharm, VSCode, Visual Studio, wherever you want, and then send function execution to SQL Server bringing intelligence to where your data lives.

This tutorial will show you an example of how you can send your python code from Juptyter notebooks to execute within SQL Server. The same principles apply to R and any other IDE as well. If you prefer to learn through videos, this tutorial is also published on YouTube here:


 

Environment Setup Prerequisites

  1. Install ML Services on SQL Server

In order for R or Python to execute within SQL, you first need the Machine Learning Services feature installed and configured. See this how-to guide.

  1. Install RevoscalePy via Microsoft’s Python Client

In order to send Python execution to SQL from Jupyter Notebooks, you need to use Microsoft’s RevoscalePy package. To get RevoscalePy, download and install Microsoft’s ML Services Python Client. Documentation Page or Direct Download Link (for Windows).

After downloading, open powershell as an administrator and navigate to the download folder. Start the installation with this command (feel free to customize the install folder): .\Install-PyForMLS.ps1 -InstallFolder “C:\Program Files\MicrosoftPythonClient”

Be patient while the installation can take a little while. Once installed navigate to the new path you installed in. Let’s make an empty folder and open Jupyter Notebooks: mkdir JupyterNotebooks; cd JupyterNotebooks; ..\Scripts\jupyter-notebook

Create a new notebook with the Python 3 interpreter:

 

To test if everything is setup, import revoscalepy in the first cell and execute. If there are no error messages you are ready to move forward.

Database Setup (Required for this tutorial only)

For the rest of the tutorial you can clone this Jupyter Notebook from Github if you don’t want to copy paste all of the code. This database setup is a one time step to ensure you have the same data as this tutorial. You don’t need to perform any of these setup steps to use your own data.

  1. Create a database

Modify the connection string for your server and use pyodbc to create a new database.

  1. Import Iris sample from SkLearn

Iris is a popular dataset for beginner data science tutorials. It is included by default in sklearn package.

  1. Use RecoscalePy APIs to create a table and load the Iris data

(You can also do this with pyodbc, sqlalchemy or other packages)

Define a Function to Send to SQL Server

Write any python code you want to execute in SQL. In this example we are creating a scatter matrix on the iris dataset and only returning the bytestream of the .png back to Jupyter Notebooks to render on our client.

Send execution to SQL

Now that we are finally set up, check out how easy sending remote execution really is! First, import revoscalepy. Create a sql_compute_context, and then send the execution of any function seamlessly to SQL Server with RxExec. No raw data had to be transferred from SQL to the Jupyter Notebook. All computation happened within the database and only the image file was returned to be displayed.

While this example is trivial with the Iris dataset, imagine the additional scale, performance, and security capabilities that you now unlocked. You can use any of the latest open source R/Python packages to build Deep Learning and AI applications on large amounts of data in SQL Server. We also offer leading edge, high-performance algorithms in Microsoft’s RevoScaleR and RevoScalePy APIs. Using these with the latest innovations in the open source world allows you to bring unparalleled selection, performance, and scale to your applications.

Learn More

Check out SQL Machine Learning Services Documentation to learn how you can easily deploy your R/Python code with SQL stored procedures making them accessible in your ETL processes or to any application. Train and store machine learning models in your database bringing intelligence to where your data lives.

Other YouTube Tutorials:

DS-GVO: Wie das moderne Data-Warehouse Unternehmen entlastet

Artikel des Blog-Sponsors: Snowflake

Viele Aktivitäten, die zur Einhaltung der DS-GVO-Anforderungen beitragen, liegen in den Händen der Unternehmen selbst. Deren IT-Anbieter sollten dazu beitragen, die Compliance-Anforderungen dieser Unternehmen zu erfüllen. Die SaaS-Anbieter eines Unternehmens sollten zumindest die IT-Sicherheitsanforderungen erfüllen, die sich vollständig in ihrem Bereich befinden und sich auf die Geschäfts- und Datensicherheit ihrer Kunden auswirken.

Snowflake wurde von Grund auf so gestaltet, dass die Einhaltung der DS-GVO erleichtert wird – und von Beginn darauf ausgelegt, enorme Mengen strukturierter und semistrukturierter Daten mit der Leichtigkeit von Standard-SQL zu verarbeiten. Die Zugänglichkeit und Einfachheit von SQL gibt Organisationen die Flexibilität, alle unter der DS-GVO erforderlichen Aktualisierungen, Änderungen oder Löschungen nahtlos vorzunehmen. Snowflakes Unterstützung für semistrukturierte Daten kann die Anpassung an neue Felder und andere Änderungen der Datensätze erleichtern. Darüber hinaus war die Sicherheit von Anfang an von grundlegender Bedeutung für Architektur, Implementierung und Betrieb von Snowflakes Data-Warehouse-as-a-Service.

Ein Grundprinzip der DS-GVO

Ein wichtiger Faktor für die Einhaltung der DS-GVO ist, zu verstehen, welche Daten eine Organisation besitzt und auf wen sie sich beziehen. Diese Anforderung macht es nötig, dass Daten strukturiert, organisiert und einfach zu suchen sind.

Die relationale SQL-Datenbankarchitektur von Snowflake bietet eine erheblich vereinfachte Struktur und Organisation, was sicherstellt, dass jeder Datensatz einen eindeutigen und leicht identifizierbaren Speicherort innerhalb der Datenbank besitzt. Snowflake-Kunden können auch relationalen Speicher mit dem Variant-Spaltentyp von Snowflake für semistrukturierte Daten kombinieren. Dieser Ansatz erweitert die Einfachheit des relationalen Formats auf die Schema-Flexibilität semistrukturierter Daten.

Snowflake ist noch leistungsfähiger durch seine Fähigkeit, massive Nebenläufigkeit zu unterstützen. Bei größeren Organisationen können Dutzende oder sogar Hunderte nebenläufiger Datenänderungen, -abfragen und -suchvorgänge zu einem bestimmten Zeitpunkt auftreten. Herkömmliche Data-Warehouses können nicht zu einem bestimmten Zeitpunkt über einen einzelnen Rechen-Cluster hinaus skaliert werden, was zu langen Warteschlangen und verzögerter Compliance führt. Snowflakes Multi-Cluster-Architektur für gemeinsam genutzte Daten löst dieses Problem, indem sie so viele einzigartige Rechen-Cluster bereitstellen kann, wie für einen beliebigen Zweck nötig sind, was zu einer effizienteren Workload-Isolierung und höherem Abfragedurchsatz führt. Jeder Mitarbeiter kann sehr große Datenmengen mit so vielen nebenläufigen Benutzern oder Operationen wie nötig speichern, organisieren, ändern, suchen und abfragen.

Rechte von Personen, deren Daten verarbeitet werden („Datensubjekte“)

Organisationen, die von der DS-GVO betroffen sind, müssen sicherstellen, dass sie Anfragen betroffener Personen nachkommen können. Einzelpersonen haben jetzt erheblich erweiterte Rechte, um zu erfahren, welche Art von Daten eine Organisation über sie besitzt, und das Recht, den Zugriff und/oder die Korrektur ihrer Daten anzufordern, die Daten zu löschen und/oder die Daten an einen neuen Provider zu übertragen. Bei der Bereitstellung dieser Dienste müssen Organisationen ziemlich schnell reagieren, in der Regel innerhalb von 30 Tagen. Daher müssen sie ihre Geschäftssysteme und ihr Data-Warehouse schnell durchsuchen können, um alle personenbezogenen Daten zu finden, die mit einer Person in Verbindung stehen, und entsprechende Maßnahmen ergreifen.

Organisationen können in großem Umfang von der Speicherung aller Daten in einem Data-Warehouse-as-a-Service mit vollen DML- und SQL-Fähigkeiten profitieren. Dies erleichtert das (mühevolle) Durchsuchen getrennter Geschäftssysteme und Datenspeicher, um die relevanten Daten zu finden. Und das wiederum hilft sicherzustellen, dass einzelne Datensätze durchsucht, gelöscht, eingeschränkt, aktualisiert, aufgeteilt und auf andere Weise manipuliert werden können, um sie an entsprechende Anfragen betroffener Personen anzupassen. Außerdem können Daten so verschoben werden, dass sie der Anforderung einer Anfrage zum „Recht auf Datenübertragbarkeit“ entsprechen. Von Anfang an wurde Snowflake mit ANSI-Standard-SQL und vollständiger DML-Unterstützung entwickelt, um sicherzustellen, dass diese Arten von Operationen möglich sind.

Sicherheit

Leider erfordern es viele herkömmliche Data-Warehouses, dass sich Unternehmen selbst um die IT-Sicherheit kümmern und diese mit anderen Services außerhalb des Kernangebots kombiniert wird. Außerdem bieten sie manchmal noch nicht einmal standardmäßige Verschlüsselung.

Als Data-Warehouse, das speziell für die Cloud entwickelt wurde und das Sicherheit als zentrales Element bietet, umfasst Snowflake unter anderem folgende integrierte Schutzfunktionen:

  • Minimaler Betriebsaufwand: Weniger Komplexität durch automatische Performance, Sicherheit und Hochverfügbarkeit, sodass die Infrastruktur nicht optimiert werden muss und kein Tuning nötig ist.
  • Durchgängige Verschlüsselung: Automatische Verschlüsselung aller Daten jederzeit (in ruhendem und bewegtem Zustand).
  • Umfassender Schutz: Zu den Sicherheitsfunktionen zählen Multi-Faktor-Authentifizierung, rollenbasierte Zugriffskontrolle, IP-Adressen-Whitelisting, zentralisierte Authentifizierung und jährliche Neuverschlüsselung verschlüsselter Daten.
  • Tri-Secret Secure: Kundenkontrolle und Datenschutz durch die Kombination aus einem vom Kunden, einem von Snowflake bereitgestellten Verschlüsselungsschlüssel und Benutzerzugangsdaten.
  • Unterstützung für AWS Private Link: Kunden können Daten zwischen ihrem virtuellen privaten Netzwerk und Snowflake übertragen, ohne über das Internet gehen zu müssen. Dadurch ist die Konnektivität zwischen den Netzwerken sicher und einfacher zu verwalten.
  • Stärkere unternehmensinterne Datenabgrenzung dank Snowflake Data Sharing: Organisationen können die Datenfreigabefunktionen von Snowflake nutzen, um nicht personenbezogene Daten mit anderen Abteilungen zu teilen, die keinen Zugriff benötigen – indem sie strengere Sicherheits- und DS-GVO-Kontrollen durchsetzen.
  • Private Umgebung: Unternehmen können eine dedizierte, verwaltete Snowflake- Instanz in einer separaten AWS Virtual Private Cloud (VPC) abrufen.

Rechenschaftspflicht

Was die Komplexität weiter erhöht: Organisationen müssen auch sicherstellen, dass sie und die Organisationen und Tools, mit denen sie arbeiten, Compliance nachweisen können. Snowflake prüft und verfeinert seine IT-Sicherheitspraxis regelmäßig mit peniblen Penetrationstests. Snowflakes Data-Warehouse-as-a-Service ist zertifiziert nach SOC 2 Type II, ist PCI-DSS-konform und unterstützt HIPAA-Compliance. Um Anfragen von Personen, deren Daten verarbeitet werden („Datensubjekte“), zu entsprechen, können Kunden genutzte Daten überprüfen.

Zusätzlich zu diesen Standardfunktionen und -validierungen schützt Snowflake seine Kunden auch durch den Datenschutznachtrag („Data Protection Addendum“), der genau auf die Anforderungen der DS-GVO abgestimmt ist. Snowflake hält sich außerdem an penibel vertraglich festgelegte Sicherheitsverpflichtungen („contractual security commitments“), um effizientere Transaktionen und eine vereinfachte Sorgfaltspflicht zu ermöglichen.

Fazit

Im Rahmen der Europäischen Datenschutz-Grundverordnung müssen Unternehmen technische Maßnahmen ergreifen, mit deren Hilfe sie den Anforderungen ihrer Kunden in Bezug auf Datenschutz und Schutz der Privatsphäre gerecht werden können. Snowflake bietet hier nicht nur den Vorteil, alle wichtigen Kundendaten an einem einzigen Ort zu speichern, sondern ermöglicht auch das schnelle Auffinden und Abrufen dieser Daten, sodass Unternehmen im Bedarfsfall schnell aktiv werden können.

Big Data Essentials – Intro

1. Big Data Definition

Data umfasst Nummern, Text, Bilder, Audio, Video und jede Art von Informationen die in Ihrem Computer gespeichert werden können. Big Data umfasst Datenmengen, die eine oder mehrere der folgenden Eigenschaften aufweisen: Hohes Volumen (High Volume), hohe Vielfalt (High Variety) und / oder eine notwendige hohe Geschwindigkeit (High Velocity) zur Auswertung. Diese drei Eigenschaften werden oft auch als die 3V’s von Big Data bezeichnet.

1.1. Volumen: Menge der erzeugten Daten

Volumen bezieht sich auf die Menge der generierten Daten. Traditionelle Datenanalysemodelle erfordern typischerweise Server mit großen Speicherkapazitäten, bei massiver Rechenleistung sind diese Modelle nicht gut skalierbar. Um die Rechenleistung zu erhöhen, müssen Sie weiter investieren, möglicherweise auch in teurere proprietäre Hardware. Die NASA ist eines von vielen Unternehmen, die enorme Mengen an Daten sammeln. Ende 2014 sammelte die NASA alle paar Sekunden etwa 1,73 GB an Daten. Und auch dieser Betrag der Datenansammlung steigt an, so dass die Datenerfassung entsprechend exponentiell mitwachsen muss. Es resultieren sehr hohe Datenvolumen und es kann schwierig sein, diese zu speichern.

1.2. Vielfalt: Unterschiedliche Arten von Daten

Das  traditionelle  Datenmodell (ERM)  erfordert  die  Entwicklung  eines  Schemas,  das  die  Daten in ein Korsett zwingt. Um das Schema zu erstellen, muss man das Format der Daten kennen, die gesammelt werden. Daten  können  wie  XML-Dateien  strukturiert  sein,  halb  strukturiert  wie  E-Mails oder unstrukturiert wie Videodateien.

Wikipedia – als Beispiel – enthält mehr als nur Textdaten, es enthält Hyperlinks, Bilder, Sound-Dateien und viele andere Datentypen mit mehreren verschiedenen Arten von Daten. Insbesondere unstrukturierte   Daten haben   eine   große   Vielfalt.  Es   kann   sehr   schwierig   sein, diese Vielfalt in einem Datenmodell zu beschreiben.

1.3. Geschwindigkeit: Geschwindigkeit, mit der Daten genutzt werden

Traditionelle Datenanalysemodelle wurden für die Stapelverarbeitung (batch processing) entwickelt. Sie sammeln die gesamte Datenmenge und verarbeiten sie, um sie in die Datenbank zu speichern. Erst mit einer Echtzeitanalyse der Daten kann schnell auf Informationen reagiert werden. Beispielsweise können Netzwerksensoren, die mit dem Internet der Dinge (IoT) verbunden sind, tausende von Datenpunkten pro Sekunde erzeugen. Im Gegensatz zu Wikipedia, deren Daten später verarbeitet werden können, müssen Daten von Smartphones und anderen Netzwerkteilnehmern mit entsprechender Sensorik in  Echtzeit  verarbeitet  werden.

2. Geschichte von Big Data

2.1. Google Solution

  • Google File System speichert die Daten, Bigtable organisiert die Daten und MapReduce verarbeitet es.
  • Diese Komponenten arbeiten zusammen auf einer Sammlung von Computern, die als Cluster bezeichnet werden.
  • Jeder einzelne Computer in einem Cluster wird als Knoten bezeichnet.

2.2 Google File System

Das Google File System (GFS) teilt Daten in Stücke ‚Chunks’ auf. Diese ‚Chunks’ werden verteilt und auf verschiedene Knoten in einem Cluster nachgebildet. Der Vorteil ist nicht nur die mögliche parallele Verarbeitung bei der späteren Analysen, sondern auch die Datensicherheit. Denn die Verteilung und die Nachbildung schützen vor Datenverlust.

2.3. Bigtable

Bigtable ist ein Datenbanksystem, das GFS zum Speichern und Abrufen von Daten verwendet. Trotz seines Namens ist Bigtable nicht nur eine sehr große Tabelle. Bigtable ordnet die Datenspeicher mit einem Zeilenschlüssel, einem Spaltenschlüssel und einem Zeitstempel zu. Auf diese Weise können dieselben Informationen über einen längeren Zeitraum hinweg erfasst werden, ohne dass bereits vorhandene Einträge überschrieben werden. Die Zeilen sind dann in den Untertabellen partitioniert, die über einem Cluster verteilt sind. Bigtable wurde entwickelt, um riesige Datenmengen zu bewältigen, mit der Möglichkeit, neue Einträge zum Cluster hinzuzufügen, ohne dass eine der vorhandenen Dateien neu konfiguriert werden muss.

2.4. MapReduce

Als dritter Teil des Puzzles wurde ein Parallelverarbeitungsparadigma namens MapReduce genutzt, um die bei GFS gespeicherten Daten zu verarbeiten. Der Name MapReduce wird aus den Namen von zwei Schritten im Prozess übernommen. Obwohl der Mapreduce-Prozess durch Apache Hadoop berühmt geworden ist, ist das kaum eine neue Idee. In der Tat können viele gängige Aufgaben wie Sortieren und Falten von Wäsche als Beispiele für den MapReduce- Prozess betrachtet werden.

Quadratische Funktion:

  • wendet die gleiche Logik auf jeden Wert an, jeweils einen Wert
  • gibt das Ergebnis für jeden Wert aus
    (map square'(1 2 3 4)) = (1 4 9 16)

Additionsfunktion

  • wendet die gleiche Logik auf alle Werte an, die zusammen genommen werden.
    (reduce + ‘(1 4 9 16)) = 30

Die Namen Map und Reduce können bei der Programmierung mindestens bis in die 70er-Jahre zurückverfolgt werden. In diesem Beispiel sieht man, wie die Liste das MapReduce-Modell verwendet. Zuerst benutzt man Map der Quadratfunktion auf einer Eingangsliste für die Quadratfunktion, da sie abgebildet ist, alle angelegten Eingaben und erzeugt eine einzige Ausgabe pro Eingabe, in diesem Fall (1, 4, 9 und 16). Additionsfunktion reduziert die Liste und erzeugt eine einzelne Ausgabe von 30, der die Summe aller Eingänge ist.

Google nutzte die Leistung von MapReduce, um einen Suchmaschinen-Markt zu dominieren. Das Paradigma kam in der 19. Websearch-Engine zum Einsatz und etablierte sich innerhalb weniger Jahre und ist bis heute noch relevant. Google verwendete MapReduce auf verschiedene Weise, um die Websuche zu verbessern. Es wurde verwendet, um den Seiteninhalt zu indexieren und ein Ranking über die Relevant einer Webseite zu berechnen.

Dieses  Beispiel  zeigt  uns  den MapReduce-Algorithmus, mit dem Google Wordcount auf Webseiten ausführte. Die Map-Methode verwendet als Eingabe einen Schlüssel (key) und einen Wert, wobei der Schlüssel den Namen des Dokuments darstellt  und  der  Wert  der  Kontext  dieses Dokuments ist. Die Map-Methode durchläuft jedes Wort im Dokument und gibt es als Tuple zurück, die aus dem Wort und dem Zähler 1 besteht.

Die   Reduce-Methode   nimmt   als   Eingabe auch  einen  Schlüssel  und  eine  Liste  von  Werten an, in der der Schlüssel ein Wort darstellt. Die  Liste  von  Werten  ist  die  Liste  von  Zählungen dieses Worts. In diesem Beispiel ist der Wert 1. Die Methode “Reduce” durchläuft alle Zählungen. Wenn die Schleife beendet ist, um die Methode zu reduzieren, wird sie als Tuple zurückgegeben, die aus dem Wort und seiner Gesamtanzahl besteht.

 

R Data Frames meistern mit dplyr – Teil 2

Dieser Artikel ist Teil 2 von 2 aus der Artikelserie R Data Frames meistern mit dplyr.

Noch mehr Datenbank-Features

Im ersten Teil dieser Artikel-Serie habe ich die Parallelen zwischen Data Frames in R und Relationen in SQL herausgearbeitet und gezeigt, wie das Paket dplyr eine Reihe von SQL-analogen Operationen auf Data Frames standardisiert und optimiert. In diesem Teil möchte ich nun drei weitere Analogien aufzeigen. Es handelt sich um die

  • Window Functions in dplyr als Entsprechung zu analytischen Funktionen in SQL,
  • Joins zwischen Data Frames als Pendant zu Tabellen-Joins
  • Delegation von Data Frame-Operationen zu einer bestehenden SQL-Datenbank

Window Functions

Im letzten Teil habe ich gezeigt, wie durch die Kombination von group_by() und summarise() im Handumdrehen Aggregate entstehen. Das Verb group_by() schafft dabei, wie der Name schon sagt, eine Gruppierung der Zeilen des Data Frame anhand benannter Schlüssel, die oft ordinaler oder kategorialer Natur sind (z.B. Datum, Produkt oder Mitarbeiter).

Ersetzt man die Aggregation mit summarise() durch die Funktion mutate(), um neue Spalten zu bilden, so ist der Effekt des group_by() weiterhin nutzbar, erzeugt aber „Windows“, also Gruppen von Datensätzen des Data Frames mit gleichen Werten der Gruppierungskriterien. Auf diesen Gruppen können nun mittels mutate() beliebige R-Funktionen angewendet werden. Das Ergebnis ist im Gegensatz zu summarise() keine Verdichtung auf einen Datensatz pro Gruppe, sondern eine Erweiterung jeder einzelnen Zeile um neue Werte. Das soll folgendes Beispiel verdeutlichen:

Das group_by() unterteilt den Data Frame nach den 4 gleichen Werten von a. Innerhalb dieser Gruppen berechnen die beispielsweise eingesetzten Funktionen

  • row_number(): Die laufende Nummer in dieser Gruppe
  • n(): Die Gesamtgröße dieser Gruppe
  • n_distinct(b): Die Anzahl verschiedener Werte von b innerhalb der Gruppe
  • rank(desc(b)): Den Rang innerhalb der selben Gruppe, absteigend nach b geordnet
  • lag(b): Den Wert von b der vorherigen Zeile innerhalb derselben Gruppe
  • lead(b): Analog den Wert von b der folgenden Zeile innerhalb derselben Gruppe
  • mean(b): Den Mittelwert von b innerhalb der Gruppe
  • cumsum(b): Die kumulierte Summe der b-Werte innerhalb der Gruppe.

Wichtig ist hierbei, dass die Anwendung dieser Funktionen nicht dazu führt, dass die ursprüngliche Reihenfolge der Datensätze im Data Frame geändert wird. Hier erweist sich ein wesentlicher Unterschied zwischen Data Frames und Datenbank-Relationen von Vorteil: Die Reihenfolge von Datensätzen in Data Frames ist stabil und definiert. Sie resultiert aus der Abfolge der Elemente auf den Vektoren, die die Data Frames bilden. Im Gegensatz dazu haben Tabellen und Views keine Reihenfolge, auf die man sich beim SELECT verlassen kann. Nur mit der ORDER BY-Klausel über eindeutige Schlüsselwerte erreicht man eine definierte, stabile Reihenfolge der resultierenden Datensätze.

Die Wirkungsweise von Window Functions wird noch besser verständlich, wenn in obiger Abfrage das group_by(a) entfernt wird. Dann wirken alle genannten Funktionen auf der einzigen Gruppe, die existiert, nämlich dem gesamten Data Frame:

Anwendbar sind hierbei sämtliche Funktionen, die auf Vektoren wirken. Diese müssen also wie in unserem Beispiel nicht unbedingt aus dplyr stammen. Allerdings komplettiert das Package die Menge der sinnvoll anwendbaren Funktionen um einige wichtige Elemente wie cumany() oder n_distinct().

Data Frames Hand in Hand…

In relationalen Datenbanken wird häufig angestrebt, das Datenmodell zu normalisieren. Dadurch bekommt man die negativen Folgen von Datenredundanz, wie Inkonsistenzen bei Datenmanipulationen und unnötig große Datenvolumina, in den Griff. Dies geschieht unter anderem dadurch, dass tabellarische Datenbestände aufgetrennt werden Stammdaten- und Faktentabellen. Letztere beziehen sich über Fremdschlüsselspalten auf die Primärschlüssel der Stammdatentabellen. Durch Joins, also Abfragen über mehrere Tabellen und Ausnutzen der Fremdschlüsselbeziehungen, werden die normalisierten Tabellen wieder zu einem fachlich kompletten Resultat denormalisiert.

In den Data Frames von R trifft man dieses Modellierungsmuster aus verschiedenen Gründen weit seltener an als in RDBMS. Dennoch gibt es neben der Normalisierung/Denormalisierung andere Fragestellungen, die sich gut durch Joins beantworten lassen. Neben der Zusammenführung von Beobachtungen unterschiedlicher Quellen anhand charakteristischer Schlüssel sind dies bestimmte Mengenoperationen wie Schnitt- und Differenzmengenbildung.

Die traditionelle R-Funktion für den Join zweier Data Frames lautet merge(). dplyr erweitert den Funktionsumfang dieser Funktion und sorgt für sprechendere Funktionsnamen und Konsistenz mit den anderen Operationen.

Hier ein synthetisches Beispiel:

Nun gilt es, die Verkäufe aus dem Data Frame sales mit den Produkten in products zusammenzuführen und auf Basis von Produkten Bilanzen zu erstellen. Diese Denormalisierung geschieht durch das Verb inner_join() auf zweierlei Art und Weise:

Die Ergebnisse sind bis auf die Reihenfolge der Spalten und der Zeilen identisch. Außerdem ist im einen Fall der gemeinsame Schlüssel der Produkt-Id als prod_id, im anderen Fall als id enthalten. dplyr entfernt also die Spalten-Duplikate der Join-Bedingungen. Letzere wird bei Bedarf im by-Argument der Join-Funktion angegeben. R-Experten erkennen hier einen „Named Vector“, also einen Vektor, bei dem jedes Element einen Namen hat. Diese Syntax verwendet dplyr, um elegant die äquivalenten Spalten zu kennzeichnen. Wird das Argument by weggelassen, so verwendet dplyr im Sinne eines „Natural Join“ automatisch alle Spalten, deren Namen in beiden Data Frames vorkommen.

Natürlich können wir dieses Beispiel mit den anderen Verben erweitern, um z.B. eine Umsatzbilanz pro Produkt zu erreichen:

dplyr bringt insgesamt 6 verschiedene Join-Funktionen mit: Neben dem bereits verwendeten Inner Join gibt es die linksseitigen und rechtsseitigen Outer Joins und den Full Join. Diese entsprechen genau der Funktionalität von SQL-Datenbanken. Daneben gibt es die Funktion semi_join(), die in SQL etwa folgendermaßen ausgedrückt würde:

Das Gegenteil, also ein NOT EXISTS, realisiert die sechste Join-Funktion: anti_join(). Im folgenden Beispiel sollen alle Produkte ausgegeben werden, die noch nie verkauft wurden:

… und in der Datenbank

Wir schon mehrfach betont, hat dplyr eine Reihe von Analogien zu SQL-Operationen auf relationalen Datenbanken. R Data Frames entsprechen Tabellen und Views und die dplyr-Operationen den Bausteinen von SELECT-Statements. Daraus ergibt sich die Möglichkeit, dplyr-Funktionen ohne viel Zutun auf eine bestehende Datenbank und deren Relationen zu deligieren.

Mir fallen folgende Szenarien ein, wo dies sinnvoll erscheint:

  • Die zu verarbeitende Datenmenge ist zu groß für das Memory des Rechners, auf dem R läuft.
  • Die interessierenden Daten liegen bereits als Tabellen und Views auf einer Datenbank vor.
  • Die Datenbank hat Features, wie z.B. Parallelverarbeitung oder Bitmap Indexe, die R nicht hat.

In der aktuellen Version 0.5.0 kann dplyr nativ vier Datenbank-Backends ansprechen: SQLite, MySQL, PostgreSQL und Google BigQuery. Ich vermute, unter der Leserschaft des Data Science Blogs dürfte MySQL (oder der Fork MariaDB) die weiteste Verbreitung haben, weshalb ich die folgenden Beispiele darauf zeige. Allerdings muss man beachten, dass MySQL keine Window Funktionen kennt, was sich 1:1 auf die Funktionalität von dplyr auswirkt.

Im folgenden möchte ich zeigen, wie dplyr sich gegen eine bestehende MySQL-Datenbank verbindet und danach einen bestehenden R Data Frame in eine neue Datenbanktabelle wegspeichert:

Die erste Anweisung verbindet R mit einer bestehenden MySQL-Datenbank. Danach lade ich den Data Frame diamonds aus dem Paket ggplot2. Mit str() wird deutlich, dass drei darin enthaltene Variablen vom Typ Factor sind. Damit dplyr damit arbeiten kann, werden sie mit mutate() in Character-Vektoren gewandelt. Dann erzeugt die Funktion copy_to() auf der MySQL-Datenbank eine leere Tabelle namens diamonds, in die die Datensätze kopiert werden. Danach erhält die Tabelle noch drei Indexe (von dem der erste aus drei Segmenten besteht), und zum Schluß führt dplyr noch ein ANALYSE der Tabelle durch, um die Werteverteilungen auf den Spalten für kostenbasierte Optimierung zu bestimmen.

Meistens aber wird bereits eine bestehende Datenbanktabelle die interessierenden Daten enthalten. In diesem Fall lautet die Funktion zum Erstellen des Delegats tbl():

Die Rückgabewerte von copy_to() und von tbl() sind natürlich keine reinrassigen Data Frames, sondern Objekte, auf die die Operationen von dplyr wirken können, indem sie auf die Datenbank deligiert werden. Im folgenden Beispiel sollen alle Diamanten, die ein Gewicht von mindestens 1 Karat haben, pro Cut, Color und Clarity nach Anzahl und mittlerem Preis bilanziert werden:

Die Definition der Variablen bilanz geschieht dabei komplett ohne Interaktion mit der Datenbank. Erst beim Anzeigen von Daten wird das notwendige SQL ermittelt und auf der DB ausgeführt. Die ersten 10 resultierenden Datensätze werden angezeigt. Mittels der mächtigen Funktion explain() erhalten wir das erzeugte SQL-Kommando und sogar den Ausführungsplan auf der Datenbank. SQL-Kundige werden erkennen, dass die verketteten dplyr-Operationen in verschachtelte SELECT-Statements umgesetzt werden.

Zu guter Letzt sollen aber meistens die Ergebnisse der dplyr-Operationen irgendwie gesichert werden. Hier hat der Benutzer die Wahl, ob die Daten auf der Datenbank in einer neuen Tabelle gespeichert werden sollen oder ob sie komplett nach R transferiert werden sollen. Dies erfolgt mit den Funktionen compute() bzw. collect():

Durch diese beiden Operationen wurde eine neue Datenbanktabelle „t_bilanz“ erzeugt und danach der Inhalt der Bilanz als Data Frame zurück in den R-Interpreter geholt. Damit schließt sich der Kreis.

Fazit

Mit dem Paket dplyr von Hadley Wickham wird die Arbeit mit R Data Frames auf eine neue Ebene gehoben. Die Operationen sind konsistent, vollständig und performant. Durch den Verkettungs-Operator %>% erhalten sie auch bei hoher Komplexität eine intuitive Syntax. Viele Aspekte der Funktionalität lehnen sich an Relationale Datenbanken an, sodass Analysten mit SQL-Kenntnissen rasch viele Operationen auf R Data Frames übertragen können.

Zurück zu R Data Frames meistern mit dplyr – Teil 1.

 

Was ist eigentlich Apache Spark?

Viele Technologieanbieter versprechen schlüsselfertige Lösungen für Big Data Analytics, dabei kann keine proprietäre Software-Lösung an den Umfang und die Mächtigkeit einiger Open Source Projekten heranreichen.

Seit etwa 2010 steht das Open Source Projekt Hadoop, ein Top-Level-Produkt der Apache Foundation, als einzige durch Hardware skalierbare Lösung zur Analyse von strukturierten und auch unstrukturierten Daten. Traditionell im Geschäftsbereich eingesetzte Datenbanken speichern Daten in einem festen Schema ab, das bereits vor dem Laden der Daten definiert sein muss. Dieses Schema-on-Write-Prinzip stellt zwar sicher, dass Datenformate bekannt und –konflikte vermieden werden. Es bedeutet jedoch auch, dass bereits vor dem Abspeichern bekannt sein muss, um welche Daten es sich handelt und ob diese relevant sind. Im Hadoop File System (HDFS) wird ein Schema für erst bei lesenden Zugriff erstellt.

Apache Spark ist, ähnlich wie Hadoop, dank Parallelisierung sehr leistungsfähig und umfangreich mit Bibliotheken (z. B. für Machine Learning) und Schnittstellen (z. B. HDFS) ausgestattet. Allerdings ist Apache Spark nicht für jede Big Data Analytics Aufgabe die beste Lösung, Als Einstiegslektüre empfiehlt sich das kostenlose Ebook Getting Started with Spark: From Inception to Production. Wer jedoch erstmal wissen möchte, erfährt nachfolgend die wichtigsten Infos, die es über Apache Spark zu wissen gilt.

Was ist Apache Spark?

Apache Spark ist eine Allzweck-Tool zur Datenverarbeitung, eine sogenannte Data Processing Engine. Data Engineers und Data Scientists setzen Spark ein, um äußerst schnelle Datenabfragen (Queries) auf große Datenmengen im Terabyte-Bereich ausführen zu können.

Spark wurde 2013 zum Incubator-Projekt der Apache Software Foundation, eine der weltweit wichtigsten Organisationen für Open Source. Bereits 2014 es wie Hadoop zum Top-Level-Produkt. Aktuell ist Spark eines der bedeutensten Produkte der Apache Software Foundation mit viel Unterstützung von Unternehmen wie etwa Databricks, IBM und Huawei.

Was ist das Besondere an Spark?

Mit Spark können Daten transformiert, zu fusioniert und auch sehr mathematische Analysen unterzogen werden.
Typische Anwendungsszenarien sind interactive Datenabfragen aus verteilten Datenbeständen und Verarbeitung von fließenden Daten (Streaming) von Sensoren oder aus dem Finanzbereich. Die besondere Stärke von Spark ist jedoch das maschinelle Lernen (Machine Learning) mit den Zusätzen MLib (Machine Learning Bibliothek) oder SparkR (R-Bibliotheken direkt unter Spark verwenden), denn im Gegensatz zum MapReduce-Algorithmus von Hadoop, der einen Batch-Prozess darstellt, kann Spark sehr gut iterative Schleifen verarbeiten, die für Machine Learning Algorithmen, z. B. der K-Nearest Neighbor Algorithmus, so wichtig sind.spark-stack

Spark war von Beginn an darauf ausgelegt, Daten dynamisch im RAM (Arbeitsspeicher) des Server-Clusters zu halten und dort zu verarbeiten. Diese sogenannte In-Memory-Technologie ermöglicht die besonders schnelle Auswertung von Daten. Auch andere Datenbanken, beispielsweise SAP Hana, arbeiten In-Memory, doch Apache Spark kombiniert diese Technik sehr gut mit der Parallelisierung von Arbeitsschritten über ein Cluster und setzt sich somit deutlich von anderen Datenbanken ab. Hadoop ermöglicht über MapReduce zwar ebenfalls eine Prallelisierung, allerdings werden bei jedem Arbeitsschrit Daten von einer Festplatte zu einer anderen Festplatte geschrieben. Im Big Data Umfeld kommen aus Kostengründen überwiegend noch mechanisch arbeitende Magnet-Festplatten zum Einsatz, aber selbst mit zunehmender Verbreitung von sehr viel schnelleren SSD-Festplatten, ist der Arbeitsspeicher hinsichtlich der Zeiten für Zugriff auf und Schreiben von Daten unschlagbar. So berichten Unternehmen, die Spark bereits intensiv einsetzen, von einem 100fachen Geschwindigkeitsvorteil gegenüber Hadoop MapReduce.

Spark kann nicht nur Daten im Terabyte, sondern auch im Petabyte-Bereich analysieren, ein entsprechend großes Cluster, bestehend aus tausenden physikalischer oder virtueller Server, vorausgesetzt. Ähnlich wie auch bei Hadoop, skaliert ein Spark-Cluster mit seiner Größe linear in seiner Leistungsfähigkeit. Spark ist neben Hadoop ein echtes Big Data Framework.
Spark bringt sehr viele Bibliotheken und APIs mit, ist ferner über die Programmiersprachen Java, Python, R und Scala ansprechbar – das sind ohne Zweifel die im Data Science verbreitetsten Sprachen. Diese Flexibilität und geringe Rüstzeit rechtfertigt den Einsatz von Spark in vielen Projekten. Es kann sehr herausfordernd sein, ein Data Science Team mit den gleichen Programmiersprachen-Skills aufzubauen. In Spark kann mit mehreren Programmiersprachen gearbeitet werden, so dass dieses Problem teilweise umgangen werden kann.spark-runs-everywhere

In der Szene wird Spark oftmals als Erweiterung für Apache Hadoop betrachtet, denn es greift nahtlos an HDFS an, das Hadoop Distributed File System. Dank der APIs von Spark, können jedoch auch Daten anderer Systeme abgegriffen werden, z. B. von HBase, Cassandra oder MongoDB.

Was sind gängige Anwendungsbeispiele für Spark?

  • ETL / Datenintegration: Spark und Hadoop eignen sich sehr gut, um Daten aus unterschiedlichen Systemen zu filtern, zu bereinigen und zusammenzuführen.
  • Interaktive Analyse: Spark eignet sich mit seinen Abfragesystemen fantastisch zur interaktiven Analyse von großen Datenmengen. Typische Fragestellungen kommen aus dem Business Analytics und lauten beispielsweise, welche Quartalszahlen für bestimmte Vertriebsregionen vorliegen, wie hoch die Produktionskapazitäten sind oder welche Lagerreichweite vorhanden ist. Hier muss der Data Scientist nur die richtigen Fragen stellen und Spark liefert die passenden Antworten.
  • Echtzeit-Analyse von Datenströmen: Anfangs vor allem zur Analyse von Server-Logs eingesetzt, werden mit Spark heute auch Massen von Maschinen- und Finanzdaten im Sekundentakt ausgewertet. Während Data Stream Processing für Hadoop noch kaum möglich war, ist dies für Spark ein gängiges Einsatzgebiet. Daten, die simultan von mehreren Systemen generiert werden, können mit Spark problemlos in hoher Geschwindigkeit zusammengeführt und analysiert werden.
    In der Finanzwelt setzen beispielsweise Kreditkarten-Unternehmen Spark ein, um Finanztransaktionen in (nahezu) Echtzeit zu analysieren und als potenziellen Kreditkartenmissbrauch zu erkennen.
  • Maschinelles Lernen: Maschinelles Lernen (ML – Machine Learning) funktioniert desto besser, je mehr Daten in die ML-Algorithmen einbezogen werden. ML-Algorithmen haben in der Regel jedoch eine intensive, vom Data Scientist betreute, Trainingsphase, die dem Cluster viele Iterationen an Arbeitsschritten auf die großen Datenmengen abverlangen. Die Fähigkeit, Iterationen auf Daten im Arbeitsspeicher, parallelisiert in einem Cluster, durchführen zu können, macht Spark zurzeit zu dem wichtigsten Machine Learning Framework überhaupt.
    Konkret laufen die meisten Empfehlungssysteme (beispielsweise von Amazon) auf Apache Spark.

 

Eine Hadoop Architektur mit Enterprise Sicherheitsniveau

Dies ist Teil 3 von 3 der Artikelserie zum Thema Eine Hadoop-Architektur mit Enterprise Sicherheitsniveau.

Die ideale Lösung

Man denkt, dass die Integration einer sehr alten Technologie, wie ActiveDirectory oder LDAP zusammen mit einem etablierten und ausgereiften Framework wie Hadoop reibungslos funktionieren würde. Leider sind solche Annahmen in der IT Welt zu gut um wahr zu sein. Zum Glück gibt es bereits erste Erfahrungsberichte  von  Unternehmen, die ihre Hadoop Infrastruktur an ein zentrales IMS gekoppelt haben.

Da die meisten Unternehmen  Active Directory als IMS benutzen, werden die im Folgenden  dargestellte Bilder und Architekturen dies ebenfalls tun.  Die vorgeschlagene Architektur ist jedoch derartig flexibel und technologieunabhängig, dass man das Active Directory auf den Bildern problemlos gegen LDAP austauschen könnte. Vielmehr ist die Integration eines Hadoop Clusters mit LDAP einfacher, da beide Technologien nativ zu Linux sind.

Schritt Eins – Integration von Hadoop mit Active Directory

Der erste Schritt, um Hadoop in dasActive Directory zu integrieren, ist ein sogenannter One-Way Trust von der Linux Welt hin zur Windows Welt . Dabei ist das Vertrauen des Authentisierungsmechanismuses von Hadoop zum Active Directory gemeint. Alle Identity Management Systeme bieten diese Funktionalität an, um sich gegenseitig vertrauen zu können und User aus anderen Domänen (Realms) zu akzeptieren. Das ermöglicht z.B. globalen Firmen mit vielen Standorten und unterschiedlichen IT Infrastrukturen und Identity Management Systemen diese zu verwalten und miteinander kommunizieren zu lassen.

Das Key Distribution Center (KDC) von Kerberos ist das Herz des Kerberos Systems im Hadoop. Hier  werden die User und ihre Passwörter oder Keytabs geschützt und verwaltet. Dabei brauchen wir lediglich den One Way Trust von KDC zu Active Directory. Allerdings gibt es eine vielversprechendere Technologie, die FreeIPA. Diese hat laut Wikipedia das Ziel, ein einfach zu verwaltendes Identity,-Policy-and-Audit-System (IPA) zur Verfügung zu stellen. Seit der Version 3.0.0 kann sich FreeIPA in das Active Directory integrieren. Die aussagekräftigen Vorteile von FreeIPA sind folgende:

  1. Reibungslose Integration mit Active Directory
  2. Es wird zusammen mit der Technologie SSSD geliefert, die das temporäre Speichern von Rechten und Passwörtern erlaubt. Das erlaubt auch offline den Zugriff auf  Fähigkeiten und Unabhängigkeit vom zentralen IPA, dem unterliegenden System.
  3. Integrierte Kerberos und Single Sign On (SSO) Funktionalitäten.

Wir lassen dann FreeIPA die Verwaltung von Kerberos und die primäre Authentisierung unseres Clusters übernehmen. Sowohl das Active Directory, als auch FreeIPA erlauben eine kinderleichte Umsetzung des One Way Trusts mithilfe von Web Tools. Im Prinzip muss man beim One Way Trust lediglich die öffentlichen Zertifikate jedes Tools mit denen der anderen bekannt machen.

Schritt Zwei – Synchronisation der Rechte & Rollen von Active Directory

Jetzt sind alle User, die sich im Active Directory befinden, unserem Hadoop Cluster bekannt. Ein User kann sich mithilfe des kinit Kommandos und nach Eingabe seines Usernames und Passwortes einloggen. Aber man braucht auch die im Active Directory definierten Rollen und Gruppen, um eine Autorisierung mithilfe von Ranger oder Sentry zu ermöglichen. Ohne die Provisionierung der Rollen haben wir bei der Autorisierung ein ähnliches Problem, wie es bei der Authentisierung aufgetreten ist.  Man müsste die Rollen selber verwalten, was nicht ideal ist.

Zum Glück gibt es verschiedene Ansätze um eine regelforme Synchronisierung der Gruppen von Active Directory in Ranger oder Sentry zu implementieren. Ranger kommt mit einem LDAP Plugin namens uxugsync, das sowohl mit LDAP als auch mit dem Active Directory kommunizieren kann. Leider hat die aktuelle Version dieses Plugins einige Nachteile:

  1. Leistungsprobleme, weil es defaultsmäßig versucht, den ganzen Hierarchiebaum von Active Directory zu synchronisieren. Das kann zu einem großen Problem für große Firmen werden, die mehrere tausend User haben. Außerdem müssen nicht alle User Zugriff auf Hadoop haben.
  2. Man kann bestimmte User syncen lassen, indem man ihren Gruppename im Gruppenfeld vom Plugin einträgt. Nachteil dabei ist, dass diese Abfrage nicht rekursiv funktioniert und alle Gruppe die im Ranger sein sollen einzeln abgefragt werden müssen, Das wiederum skaliert nicht sonderlich gut.
  3. Massive und regelmäßige Abfragen des Plugins können sogar zu einem DDoS Angriff auf den zentralen Active Directory führen.

Eine bessere Lösung wäre es, wenn wir die schönen Features des SSSD Deamons (der wie oben beschrieben zusammen mit FreeIPA kommt) ausnutzen könnten. Mithilfe von SSSD werden alle User und ihre entsprechenden Gruppen dem unterliegenden Linux Betriebssystem bekannt gemacht. Das bedeutet, dass man ein einfaches Script schreiben könnte, das die User und ihre Gruppen vom System direkt abfragt und zu Ranger oder Sentry über ihre entsprechende REST APIs überträgt. Dabei schont man sowohl das Active Directory vor regelmäßigen und aufwändigen Abfragen und schafft sogar ein schnelleres Mapping der Rollen zwischen Hadoop und Betriebssystem, auch wenn Active Directory nicht erreichbar ist. Es gibt derzeit Pläne, ein solches Plugin in den nächsten Versionen von Ranger mitzuliefern.

Schritt Drei – Anlegen und Verwaltung von technischen Usern

Unser System hat jedoch neben personalisierten Usern, die echten Personen in einem Unternehmen entsprechen, auch  technische User. Die technischen Users (Nicht Personalisierte Accounts – NPA), sind die Linux User mit denen die Hadoop Dienste gestartet werden. Dabei hat HDFS, Ambari usw. jeweils seinen eigenen User mit demselben Namen. Rein theoretisch könnten diese User auch im Active Directory einen Platz finden.

Meiner Meinung nach gehören diese User aber nicht dorthin. Erstens, weil sie keine echten User sind und zweitens, weil die Verwaltung solcher User nach Upgrades oder Neuinstallation des Clusters schwierig sein kann. Außerdem müssen solche User nicht den gleichen Sicherheitspolicies unterliegen, wie die normalen User. Am besten sollten sie kein Passwort besetzen, sondern lediglich ein Kerberos Keytab, das sich nach jedem Upgrade oder Neuinstallierung des Clusters neu generiert und in FreeIPA angelegt ist. Deswegen neige ich eher dazu, die NPAs in IPA anzulegen und zu verwalten.

High Level Architektur

Das folgende Bild fasst die Architektur zusammen. Hadoop Dienste, die üblicherweise in einer explorativen Umgebung benutzt werden, wie Hive und HBase, werden mit dargestellt. Es ist wichtig zu beachten, dass jegliche Technologie, die ein Ausführungsengine für YARN anbietet, wie Spark oder Storm, von dieser Architektur ebenfalls profitiert. Da solche Technologien nicht direkt mit den unterliegenden Daten interagieren, sondern diese immer über YARN und die entsprechenden Datanodes erhalten, benötigen sie auch keine besondere Darstellung oder Behandlung. Der Datenzugriff aus diesen 3rd Party Technologien respektiert die im Ranger definierten ACLs und Rollen des jeweiligen Users, der sie angestoßen hat.

hadoop-integration-active-directory-ipa-domain

Architektur in einer Mehrclusterumgebung

Wir haben schon das Argument untermauert, warum  unsere technischen User direkt im IPA liegen sollten. Das kann jedoch insofern Probleme verursachen, wenn man mit mehreren Clustern arbeitet, die alle die gleichen Namen für ihre technischen User haben. Man merkt sofort, dass es sich hier um eine Namenskollision handelt. Es gibt zwei Lösungsansätze hierfür:

  1. Man fügt den Namen Präfixen, die als kurze Beschreibungen der jeweiligen Umgebung dienen, wie z.B. ada, proj1, proj2 hinzu. Dadurch haben die User unterschiedliche Namen, wie proj1_hdfs für die proj1 Umgebung und ada_hdfs für die ada Umgebung. Man kann diese Lösung auch bei Kerberos KDCs benutzen, die in jeder Umgebung dediziert sind und die technischen User der jeweiligen Umgebung beibehalten.
  2. Man benutzt einen separaten Realm für jede Umgebung und damit auch eine separate IPA Instanz. Hier gibt es wiederum zwei verschiedene Ansätze. Ich muss jedoch zugeben, dass ich die Zweite nie ausprobiert habe und daher für ihre Durchführbarkeit nicht garantieren kann:
    1. Man bindet jede Umgebung einzeln über ihre FreeIPA per One Way Trust an das zentrale Active Directory. Das hat natürlich den Nachteil einer uneinheitlichen User Management Infrastruktur für alle Umgebungen, da Jede ihre eigene IPA Infrastruktur verwaltet und wartet.
    2. Man baut einen Hierarchiebaum von unterschiedlichen IPA Instanzen, so wie man es bei Forests von Active Directory Instanzen macht.

Das folgende Bild stellt den letzten Ansatz dar. Im Prinzip haben wir hier einen hierarchischen IPA Cluster mit mehreren One Way Trusts von den lokalen IPA Instanzen zu der zentralen IPA.

hadoop-local-identity-management-domain-ipa-netzwerk

Zusammenfassung

Wie Sie vielleicht von der gesamten Diskussion her abgeleitet haben, ist die Umsetzung einer unternehmerisch-konformen und personenbasierten Sicherheitsarchitektur innerhalb von Hadoop  keine einfache Sache. Man muss mit unterschiedlichen Architekturen und Ansätzen spielen, bevor man einen relativ vernünftigen oder sogar idealen Zustand erreicht hat. Die Berücksichtigung der jeweiligen IT Architektur spielt dabei eine sehr große Rolle. Ich hoffe, ich konnte die wichtigsten Merkmalen einer solchen Architektur und die Punkte, die ein Architekt besonders beachten muss, klar darstellen.

Als Zusammenfassung habe ich Ihnen am Ende eine Art Shoppingliste aller Komponenten zusammengestellt, die wichtig für den personalisierten Zugriff im Hadoop sind:

  1. Kerberos – Authentisierung
  2. FreeIPA – Authentisierung, Integration mit Active Directory
  3. Active Directory oder LDAP
  4. Ranger oder Sentry
    1. Plugin für Rollen/Gruppen Mapping zwischen AD und dem Betriebssystem
  5. Optional SSSD für schnellere Abfrage der Gruppen und Rollen des Betriebssystems

Zurück zu Teil 2 von 3 – Sicherheitstechnologie in Hadoop

Eine Hadoop Architektur mit Enterprise Sicherheitsniveau

Dies ist Teil 2 von 3 der Artikelserie zum Thema Eine Hadoop-Architektur mit Enterprise Sicherheitsniveau.

Der aktuelle Stand der Technologie

Zum Glück ist Hadoop heutzutage ein bisschen reifer, als es noch vor zehn Jahren war. Es gibt viele Tools, einige davon OpenSource und einige lizenziert, die den Sicherheitsmangel im Hadoop zu lösen versuchen. Die Tabelle unten zeigt eine Auswahl der am meisten genutzten Sicherheitstools. Da jedes Tool von einer anderen Hadoop Distribution bevorzugt wird, habe ich diese Parameter mit berücksichtigt.

Es ist zu beachten, dass die zwei populärsten Hadoop Distributions (Hortonworks und Cloudera) kaum Unterschiede aufweisen, wenn man sie auf funktionaler Ebene vergleicht. Der größte Unterschied  besteht darin, dass Hortonworks ein Open Source und Cloudera ein kommerzielles Produkt ist. Abgesehen davon hat jeder Vendor den einen oder anderen Vorteil, ein ausführlicher Vergleich würde jedoch den Rahmen dieses Artikels sprengen.

sicherheitsmerkmale-hadoop-hortenworks-cloudera-other

Hadoop kommt von der Stange ohne aktivierte Authentisierung. Die Hadoop Dienste vertrauen jedem User, egal als was er oder sie sich ausgibt. Das sieht  folgendermaßen aus:

Angenommen Mike arbeitet an einer Maschine, die ihm Zugriff auf den Hadoop Cluster erlaubt und Sudo-Rechte gibt. Aber Mike hat das Passwort für den hdfs Superuser nicht. Er kann sich jetzt einfach als der hdfs User ausgeben, indem er die folgenden Kommandos ausführt. Dabei bekommt er fatalerweise alle Rechten des hdfs Superusers und ist in der Lage das gesamte HDFS Filesystem zu löschen. Es würde sogar bereits der Environment variabel USER ausreichen, um einen anderen User umzuwandeln.

hadoop-linux-useradd-hdfs

Kerberos ist im Moment der einzige Weg um Authentisierung im Hadoop zu gewährleisten. Kein Weg führt daran vorbei, es sei denn, man ist verrückt genug, um ein hochkompliziertes System auf Linux basierter ACLs auf jeder Maschine zu installieren und zu verwalten, um User daran zu hindern sich falsch zu authentifizieren. Es ist zudem wichtig zu beachten, dass Kerberos als einziges Sicherheitsmerkmal zur Authentifizierung dient, aber ohne richtige Authentisierung gibt es auch keine richtige Autorisierung. Wenn User jetzt selbst in der Lage sind, sich beliebig als jemand anderes auszugeben, können sie so selbst zu den sensibelsten Daten unbefugten Zugriff erlangen.

Apache Ranger oder Sentry erlauben die Definition und Verwaltung von Access Control Lists (ACLs). Diese Listen legen fest, welche User Zugriff auf welchen Bereich des HDFS Filesystems haben Der gleiche Effekt kann auch ohne diese Tools, durch einfache  Hadoop ACLs erreicht werden, die den normalen Linux ACLs ähneln. Es empfiehlt sich jedoch die neuesten Tools zu benutzen, wegen a) ihrer Benutzerfreundlichkeit, b) ihrer ausgearbeiteten APIs, die einem Administrator erlauben die Listen ohne GUI zu verwalten und beim Programmieren sogar zu automatisieren, und c) wegen ihrer Auditingfähigkeiten, die das Nachverfolgen von Zugriffen und Aktionen ermöglichen.

Anbei ist das Bild einer Ranger Policy, die der Gruppe der User rekursiv Lese- und Ausführungsrechte auf das Verzeichnis /projects/autonomous_driving gibt.

Alle einzelne Stücke des Puzzles kommen zusammen

Nachdem wir ermittelt haben, welche Technologien es gibt, die uns zu einem sicheren Cluster verhelfen, müssen diese im nächsten Schritt zusammengesetzt werden. Zum Glück hat jeder Vendor seine eigene Technologie, um Tools aus dem  Hadoop Ecosystem zu integrieren und zu verwalten. Cloudera beispielsweise bietet den sehr wirksamen Cloudera Manager und Hortonworks das Apache Ambari an. Die beiden Tools kümmern sich um das Anlegung der technischen Hadoop User (hdfs, hadoop, hive, ranger, e.t.c.) und der entsprechenden Kerberos Keytabs, die den technischen Usern erlauben, sich gegenüber Hadoop zu authentisieren. Am Ende der Installation hat man sämtliche Konfigurationen zentral platziert und kann neue personalisierte Accounts anlegen. Man kann sich dann im Ranger oder Sentry Web UI anmelden und ACLs für die User und Gruppen definieren.

Das ist allerdings nicht der Idealzustand. Jedes Unternehmen verwaltet ihre User bereits in bestimmten Verwaltungssystemen, die sich innerhalb der IT Infrastruktur befinden. Diese Systeme (oder auch Identity Management Systems) sind ein wichtiges vertikales, abteilungsübergreifendes Element der unternehmerischen IT Architektur. Jedes EDS Tool im Unternehmen ist an ein Identity Management System, wie Active Directory oder LDAP, gekoppelt und muss damit die User nicht selbst verwalten.

Der Stellenwert solcher Tools wird sofort erkennbar, wenn man die strengen Sicherheitsregeln eines modernen Unternehmens betrachtet: Passwörter müssen bestimmte Kriterien erfüllen und alle 30 Tagen gewechselt werden. Außerdem darf niemand eins seiner letzten zehn Passwörter benutzen.

Eine IT Architektur, die die Implementierung solcher unternehmensbreiten  Anforderungen in jeder einzelne Applikation fördert ist der Alptraum jedes Applikationsentwicklers und zeigt das Versagen des IT-Architekten.

Aber lassen Sie uns zurück zu unserem Hauptthema kommen. Wie können wir ein System wie Active Directory oder LDAP in Hadoop integrieren?  Der nächste Abschnitt gibt die Antwort auf diese Frage.


Weiter zu  Teil 3 von 3 – Eine Einterprise Hadoop Architektur für beste Sicherheit

Zurück zu Teil 1 von 3 – Motivation und Anforderungen einer Data Science Plattform