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


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:

Bringing intelligence to where data lives: Python & R embedded in T-SQL


Did you know that you can write R and Python code within your T-SQL statements? Machine Learning Services in SQL Server eliminates the need for data movement. Instead of transferring large and sensitive data over the network or losing accuracy with sample csv files, you can have your R/Python code execute within your database. 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.

You can install and run 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.

If you are excited to try out SQL Server Machine Learning Services, check out the hands on tutorial below. If you do not have Machine Learning Services installed in SQL Server,you will first want to follow the getting started tutorial I published here: 

How-To Tutorial

In this tutorial, I will cover the basics of how to Execute R and Python in T-SQL statements. If you prefer learning through videos, I also published the tutorial on YouTube.


Open up SQL Server Management Studio and make a connection to your server. Open a new query and paste this basic example: (While I use Python in these samples, you can do everything with R as well)

Sp_execute_external_script is a special system stored procedure that enables R and Python execution in SQL Server. There is a “language” parameter that allows us to choose between Python and R. There is a “script” parameter where we can paste R or Python code. If you do not see an output print 7, go back and review the setup steps in this article.

Parameter Introduction

Now that we discussed a basic example, let’s start adding more pieces:

Machine Learning Services provides more natural communications between SQL and R/Python with an input data parameter that accepts any SQL query. The input parameter name is called “input_data_1”.
You can see in the python code that there are default variables defined to pass data between Python and SQL. The default variable names are “OutputDataSet” and “InputDataSet” You can change these default names like this example:

As you executed these examples, you might have noticed that they each return a result with “(No column name)”? You can specify a name for the columns that are returned by adding the WITH RESULT SETS clause to the end of the statement which is a comma separated list of columns and their datatypes.

Input/Output Data Types

Alright, let’s discuss a little more about the input/output data types used between SQL and Python. Your input SQL SELECT statement passes a “Dataframe” to python relying on the Python Pandas package. Your output from Python back to SQL also needs to be in a Pandas Dataframe object. If you need to convert scalar values into a dataframe here is an example:

Variables c and d are both scalar values, which you can add to a pandas Series if you like, and then convert them to a pandas dataframe. This one shows a little bit more complicated example, go read up on the python pandas package documentation for more details and examples:

You now know the basics to execute Python in T-SQL!

Did you know you can also write your R and Python code in your favorite IDE like RStudio and Jupyter Notebooks and then remotely send the execution of that code to SQL Server? Check out these documentation links to learn more:

Check out the SQL Server Machine Learning Services documentation page for more documentation, samples, and solutions. Check out these E2E tutorials on github as well.

Would love to hear from you! Leave a comment below to ask a question, or start a discussion!

Data Science Knowledge Stack – Was ein Data Scientist können muss

Was muss ein Data Scientist können? Diese Frage wurde bereits häufig gestellt und auch häufig beantwortet. In der Tat ist man sich mittlerweile recht einig darüber, welche Aufgaben ein Data Scientist für Aufgaben übernehmen kann und welche Fähigkeiten dafür notwendig sind. Ich möchte versuchen, diesen Konsens in eine Grafik zu bringen: Ein Schichten-Modell, ähnlich des OSI-Layer-Modells (welches übrigens auch jeder Data Scientist kennen sollte).
Ich gebe Einführungs-Seminare in Data Science für Kaufleute und Ingenieure und bei der Erläuterung, was wir in den Seminaren gemeinsam theoretisch und mit praxisnahen Übungen erarbeiten müssen, bin ich auf die Idee für dieses Schichten-Modell gekommen. Denn bei meinen Seminaren fängt es mit der Problemstellung bereits an, ich gebe nämlich Seminare für Data Science für Business Analytics mit Python. Also nicht beispielsweise für medizinische Analysen und auch nicht mit R oder Julia. Ich vermittle also nicht irgendein Data Science, sondern eine ganz bestimmte Richtung.

Ein Data Scientist muss bei jedem Data Science Vorhaben Probleme auf unterschiedlichsten Ebenen bewältigen, beispielsweise klappt der Datenzugriff nicht wie geplant oder die Daten haben eine andere Struktur als erwartet. Ein Data Scientist kann Stunden damit verbringen, seinen eigenen Quellcode zu debuggen oder sich in neue Data Science Pakete für seine ausgewählte Programmiersprache einzuarbeiten. Auch müssen die richtigen Algorithmen zur Datenauswertung ausgewählt, richtig parametrisiert und getestet werden, manchmal stellt sich dabei heraus, dass die ausgewählten Methoden nicht die optimalen waren. Letztendlich soll ein Mehrwert für den Fachbereich generiert werden und auch auf dieser Ebene wird ein Data Scientist vor besondere Herausforderungen gestellt.

english-flagRead this article in English:
“Data Science Knowledge Stack – Abstraction of the Data Scientist Skillset”

Data Science Knowledge Stack

Mit dem Data Science Knowledge Stack möchte ich einen strukturierten Einblick in die Aufgaben und Herausforderungen eines Data Scientists geben. Die Schichten des Stapels stellen zudem einen bidirektionalen Fluss dar, der von oben nach unten und von unten nach oben verläuft, denn Data Science als Disziplin ist ebenfalls bidirektional: Wir versuchen gestellte Fragen mit Daten zu beantworten oder wir schauen, welche Potenziale in den Daten liegen, um bisher nicht gestellte Fragen zu beantworten.

Der Data Science Knowledge Stack besteht aus sechs Schichten:

Database Technology Knowledge

Ein Data Scientist arbeitet im Schwerpunkt mit Daten und die liegen selten direkt in einer CSV-Datei strukturiert vor, sondern in der Regel in einer oder in mehreren Datenbanken, die ihren eigenen Regeln unterliegen. Insbesondere Geschäftsdaten, beispielsweise aus dem ERP- oder CRM-System, liegen in relationalen Datenbanken vor, oftmals von Microsoft, Oracle, SAP oder eine Open-Source-Alternative. Ein guter Data Scientist beherrscht nicht nur die Structured Query Language (SQL), sondern ist sich auch der Bedeutung relationaler Beziehungen bewusst, kennt also auch das Prinzip der Normalisierung.

Andere Arten von Datenbanken, sogenannte NoSQL-Datenbanken (Not only SQL)  beruhen auf Dateiformaten, einer Spalten- oder einer Graphenorientiertheit, wie beispielsweise MongoDB, Cassandra oder GraphDB. Einige dieser Datenbanken verwenden zum Datenzugriff eigene Programmiersprachen (z. B. JavaScript bei MongoDB oder die graphenorientierte Datenbank Neo4J hat eine eigene Sprache namens Cypher). Manche dieser Datenbanken bieten einen alternativen Zugriff über SQL (z. B. Hive für Hadoop).

Ein Data Scientist muss mit unterschiedlichen Datenbanksystemen zurechtkommen und mindestens SQL – den Quasi-Standard für Datenverarbeitung – sehr gut beherrschen.

Data Access & Transformation Knowledge

Liegen Daten in einer Datenbank vor, können Data Scientists einfache (und auch nicht so einfache) Analysen bereits direkt auf der Datenbank ausführen. Doch wie bekommen wir die Daten in unsere speziellen Analyse-Tools? Hierfür muss ein Data Scientist wissen, wie Daten aus der Datenbank exportiert werden können. Für einmalige Aktionen kann ein Export als CSV-Datei reichen, doch welche Trennzeichen und Textqualifier können verwendet werden? Eventuell ist der Export zu groß, so dass die Datei gesplittet werden muss.
Soll eine direkte und synchrone Datenanbindung zwischen dem Analyse-Tool und der Datenbank bestehen, kommen Schnittstellen wie REST, ODBC oder JDBC ins Spiel. Manchmal muss auch eine Socket-Verbindung hergestellt werden und das Prinzip einer Client-Server-Architektur sollte bekannt sein. Auch mit synchronen und asynchronen Verschlüsselungsverfahren sollte ein Data Scientist vertraut sein, denn nicht selten wird mit vertraulichen Daten gearbeitet und ein Mindeststandard an Sicherheit ist zumindest bei geschäftlichen Anwendungen stets einzuhalten.

Viele Daten liegen nicht strukturiert in einer Datenbank vor, sondern sind sogenannte unstrukturierte oder semi-strukturierte Daten aus Dokumenten oder aus Internetquellen. Auch hier haben wir es mit Schnittstellen zutun, ein häufiger Einstieg für Data Scientists stellt beispielsweise die Twitter-API dar. Manchmal wollen wir Daten in nahezu Echtzeit streamen, beispielsweise Maschinendaten. Dies kann recht anspruchsvoll sein, so das Data Streaming beinahe eine eigene Disziplin darstellt, mit der ein Data Scientist schnell in Berührung kommen kann.

Programming Language Knowledge

Programmiersprachen sind für Data Scientists Werkzeuge, um Daten zu verarbeiten und die Verarbeitung zu automatisieren. Data Scientists sind in der Regel keine richtigen Software-Entwickler, sie müssen sich nicht um Software-Sicherheit oder -Ergonomie kümmern. Ein gewisses Basiswissen über Software-Architekturen hilft jedoch oftmals, denn immerhin sollen manche Data Science Programme in eine IT-Landschaft integriert werden. Unverzichtbar ist hingegen das Verständnis für objektorientierte Programmierung und die gute Kenntnis der Syntax der ausgewählten Programmiersprachen, zumal nicht jede Programmiersprache für alle Vorhaben die sinnvollste ist.

Auf dem Level der Programmiersprache gibt es beim Arbeitsalltag eines Data Scientists bereits viele Fallstricke, die in der Programmiersprache selbst begründet sind, denn jede hat ihre eigenen Tücken und Details entscheiden darüber, ob eine Analyse richtig oder falsch abläuft: Beispielsweise ob Datenobjekte als Kopie oder als Referenz übergeben oder wie NULL-Werte behandelt werden.

Data Science Tool & Library Knowledge

Hat ein Data Scientist seine Daten erstmal in sein favorisiertes Tool geladen, beispielsweise in eines von IBM, SAS oder in eine Open-Source-Alternative wie Octave, fängt seine Kernarbeit gerade erst an. Diese Tools sind allerdings eher nicht selbsterklärend und auch deshalb gibt es ein vielfältiges Zertifizierungsangebot für diverse Data Science Tools. Viele (wenn nicht die meisten) Data Scientists arbeiten überwiegend direkt mit einer Programmiersprache, doch reicht diese alleine nicht aus, um effektiv statistische Datenanalysen oder Machine Learning zu betreiben: Wir verwenden Data Science Bibliotheken, also Pakete (Packages), die uns Datenstrukturen und Methoden als Vorgabe bereitstellen und die Programmiersprache somit erweitern, damit allerdings oftmals auch neue Tücken erzeugen. Eine solche Bibliothek, beispielsweise Scikit-Learn für Python, ist eine in der Programmiersprache umgesetzte Methodensammlung und somit ein Data Science Tool. Die Verwendung derartiger Bibliotheken will jedoch gelernt sein und erfordert für die zuverlässige Anwendung daher Einarbeitung und Praxiserfahrung.

Geht es um Big Data Analytics, also die Analyse von besonders großen Daten, betreten wir das Feld von Distributed Computing (Verteiltes Rechnen). Tools (bzw. Frameworks) wie Apache Hadoop, Apache Spark oder Apache Flink ermöglichen es, Daten zeitlich parallel auf mehren Servern zu verarbeiten und auszuwerten. Auch stellen diese Tools wiederum eigene Bibliotheken bereit, für Machine Learning z. B. Mahout, MLlib und FlinkML.

Data Science Method Knowledge

Ein Data Scientist ist nicht einfach nur ein Bediener von Tools, sondern er nutzt die Tools, um seine Analyse-Methoden auf Daten anzuwenden, die er für die festgelegten Ziele ausgewählt hat. Diese Analyse-Methoden sind beispielweise Auswertungen der beschreibenden Statistik, Schätzverfahren oder Hypothesen-Tests. Etwas mathematischer sind Verfahren des maschinellen Lernens zum Data Mining, beispielsweise Clusterung oder Dimensionsreduktion oder mehr in Richtung automatisierter Entscheidungsfindung durch Klassifikation oder Regression.

Maschinelle Lernverfahren funktionieren in der Regel nicht auf Anhieb, sie müssen unter Einsatz von Optimierungsverfahren, wie der Gradientenmethode, verbessert werden. Ein Data Scientist muss Unter- und Überanpassung erkennen können und er muss beweisen, dass die Vorhersageergebnisse für den geplanten Einsatz akkurat genug sind.

Spezielle Anwendungen bedingen spezielles Wissen, was beispielsweise für die Themengebiete der Bilderkennung (Visual Computing) oder der Verarbeitung von menschlicher Sprache (Natural Language Processiong) zutrifft. Spätestens an dieser Stelle öffnen wir die Tür zum Deep Learning.


Data Science ist kein Selbstzweck, sondern eine Disziplin, die Fragen aus anderen Fachgebieten mit Daten beantworten möchte. Aus diesem Grund ist Data Science so vielfältig. Betriebswirtschaftler brauchen Data Scientists, um Finanztransaktionen zu analysieren, beispielsweise um Betrugsszenarien zu erkennen oder um die Kundenbedürfnisse besser zu verstehen oder aber, um Lieferketten zu optimieren. Naturwissenschaftler wie Geologen, Biologen oder Experimental-Physiker nutzen ebenfalls Data Science, um ihre Beobachtungen mit dem Ziel der Erkenntnisgewinnung zu machen. Ingenieure möchten die Situation und Zusammenhänge von Maschinenanlagen oder Fahrzeugen besser verstehen und Mediziner interessieren sich für die bessere Diagnostik und Medikation bei ihren Patienten.

Damit ein Data Scientist einen bestimmten Fachbereich mit seinem Wissen über Daten, Tools und Analyse-Methoden ergebnisorientiert unterstützen kann, benötigt er selbst ein Mindestmaß an der entsprechenden Fachexpertise. Wer Analysen für Kaufleute, Ingenieure, Naturwissenschaftler, Mediziner, Juristen oder andere Interessenten machen möchte, muss eben jene Leute auch fachlich verstehen können.

Engere Data Science Definition

Während die Data Science Pioniere längst hochgradig spezialisierte Teams aufgebaut haben, suchen beispielsweise kleinere Unternehmen eher den Data Science Allrounder, der vom Zugriff auf die Datenbank bis hin zur Implementierung der analytischen Anwendung das volle Aufgabenspektrum unter Abstrichen beim Spezialwissen übernehmen kann. Unternehmen mit spezialisierten Daten-Experten unterscheiden jedoch längst in Data Scientists, Data Engineers und Business Analysts. Die Definition für Data Science und die Abgrenzung der Fähigkeiten, die ein Data Scientist haben sollte, schwankt daher zwischen der breiteren und einer engeren Abgrenzung.

Die engere Betrachtung sieht vor, dass ein Data Engineer die Datenbereitstellung übernimmt, der Data Scientist diese in seine Tools lädt und gemeinsam mit den Kollegen aus dem Fachbereich die Datenanalyse betreibt. Demnach bräuchte ein Data Scientist kein Wissen über Datenbanken oder APIs und auch die Fachexpertise wäre nicht notwendig…

In der beruflichen Praxis sieht Data Science meiner Erfahrung nach so nicht aus, das Aufgabenspektrum umfasst mehr als nur den Kernbereich. Dieser Irrtum entsteht in Data Science Kursen und auch in Seminaren – würde ich nicht oft genug auf das Gesamtbild hinweisen. In Kursen und Seminaren, die Data Science als Disziplin vermitteln wollen, wird sich selbstverständlich auf den Kernbereich fokussiert: Programmierung, Tools und Methoden aus der Mathematik & Statistik.

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.


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.


Data Science mit Neo4j und R

Traurig, aber wahr: Data Scientists verbringen 50-80% ihrer Zeit damit, Daten zu bereinigen, zu ordnen und zu bearbeiten. So bleibt nur noch wenig Zeit, um tatsächlich vorausschauende Vorhersagemodelle zu entwickeln. Vor allem bei klassischen Stacks, besteht die Datenanalyse zum Großteil darin, Zeile für Zeile in SQL zu überführen. Zeit zum Schreiben von Modell-Codes in einer statistischen Sprache wie R bleibt da kaum noch. Die langen, kryptischen SQL-Abfragen verlangsamen aber nicht nur die Entwicklungszeit. Sie stehen auch einer sinnvollen Zusammenarbeit bei Analyse-Projekten im Weg, da alle Beteiligten zunächst damit beschäftigt sind, die SQL-Abfragen der jeweils anderen zu verstehen.

Komplexität der Daten steigt

Der Grund für diese Schwierigkeiten: Die Datenstrukturen werden immer komplexer, die Vernetzung der Daten untereinander nimmt immer stärker zu. Zwängt man diese hochgradig verbundenen Datensätze in eine SQL-Datenbank, in der Beziehungen naturgemäß abstrakt über Fremdschlüssel dargestellt werden, erhält man als Ergebnis übermäßig komplizierte Schematas und Abfragen. Als Alternative gibt es jedoch einige NoSQL-Lösungen – allen voran Graphdatenbanken – die solche hochkomplexen und heterogenen Daten ohne Informationsverlust speichern können – und zwar nicht nur die Entitäten an sich, sondern auch besonders die Beziehungen der Daten untereinander.

Datenanalysen zielen immer stärker darauf ab, das Verhalten und die Wünsche von Kunden besser verstehen zu können. Die Fragen lauten z. B.:

  • Wie hoch ist die Wahrscheinlichkeit, dass ein Besucher auf eine bestimmte Anzeige klickt?
  • Welcher Kunde sollte in welchem Kontext welche Produktempfehlungen erhalten?
  • Wie kann man aus der bisherigen Interaktionshistorie des Kunden sein Ziel vorhersagen, bevor er selbst dort ankommt?
  • In welchen Beziehungen steht Nutzer A zu Nutzer B?

Menschen sind bekanntermaßen von Natur aus sozial. Einige dieser Fragen lassen sich daher beantworten, wenn man weiß, wie Personen miteinander in Verbindung stehen: Unsere Zielperson, Nutzer A ähnelt in seinem Kontext und Verhalten Benutzer B. Und da Benutzer B ein bestimmtes Produkt (z. B. ein Spielfilm) gefällt, empfehlen wir diesen Film auch Nutzer A. In diese Auswertung fließen natürlich auch noch weitere Faktoren mit ein, z. B. die Demographie und der soziale Status des Nutzers, seine Zuordnung zu Peer Groups, vorher gesehene Promotions oder seine bisherigen Interaktionen.

Visualisierung eines Graphen mit RNeo4j

Mit R und Neo4j lassen sich Graphen und Teilgraphen ganz einfach mit RNeo4j, igraph und visNetwork libraries visualisieren.


Das folgende Beispiel zeigt wie in einem Graphen Schauspieler und Filme sowie ihre Beziehungen zueinander anschaulich dargestellt werden können, z. B. um Empfehlungen innerhalb eines Filmportals zu generieren. Dabei sind zwei Schauspieler über eine Kante miteinander verbunden, wenn sie beide im gleichen Film mitspielen.

Im ersten Schritt werden dazu in Neo4j die Film-Datensätze importiert (Achtung: Dieser Vorgang löscht die aktuelle Datenbank).

Als nächstes wird mit Cypher eine entsprechende Liste von Beziehungen aus Neo4j gezogen. Wie man sehen kann, ist die Darstellung des gewünschten Graph-Musters innerhalb der Abfrage sehr anschaulich.

Die visNetwork Funktion erwartet sowohl Kanten-Dataframes als auch Knoten-Dataframes. Ein Knoten-Dataframe lässt sich daher über die eindeutigen Werte des Kanten-Dataframes generieren.

Im Anschluss können die Knoten- und Kanten-Dataframes in das visNetwork übertragen werden.
visNetwork(nodes, edges)

Nun kommt igraph mit ins Spiel, eine Bibliothek von Graph-Algorithmen. Durch Einbindung der Kantenliste lässt sich einfach ein igraph Graph-Objekt erstellen, das den Teilgraphen miteinschließt.

Die Größe der Knoten kann als Funktion der Edge-Betweeness-Centrality definiert werden. In visNetwork entspricht dabei jede “value”-Spalte im Knoten-Dataframe der Größe des Knoten.
nodes$value = betweenness(ig)

Mit Einführung der “Value”-Spalte werden die Knoten nun alle unterschiedlich groß dargestellt.
visNetwork(nodes, edges)

Mit Hilfe eines Community-Detection-Algorithmus lassen sich im Graphen nun Cluster finden. In diesem Beispiel wird der „Girvan-Newman”-Algorithmus verwendet, der in igraph als cluster_edge_betweenness bezeichnet wird.

In der Liste oben sind alle Schauspieler der ersten zwei Cluster zu sehen. Insgesamt konnten sechs Cluster identifiziert werden.

Durch Hinzufügen einer “Group”-Spalte im Knoten-Dataframe, werden alle Knoten in visNetwork entsprechend ihrer Gruppenzugehörigkeit farblich markiert. Diese Cluster-Zuordnung erfolgt über clusters$membership. Durch Entfernen der “Value”-Spalte lassen sich die Knoten wieder auf eine einheitliche Größe bringen.

Werden die Knoten- und Kanten-Datenframes erneut in visNetwork übertragen, sind nun alle Knoten eines Clusters in derselben Farbe dargestellt.
visNetwork(nodes, edges)

Mit diesem Workflow lassen sich Teilgraphen in Neo4j einfach abfragen und Cluster-Algorithmen einfach darstellen.

Generell eignen sich Graphdatenbanken wie Neo4j besonders gut, um stark vernetzte und beliebig strukturierte Informationen zu handhaben – egal ob es sich um Schauspieler, Filme, Kunden, Produkte, Kreditkarten oder Bankkonten handelt. Zudem können sowohl den Knoten als auch den Kanten beliebige qualitative und quantitative Eigenschaften zugeordnet werden. Beziehungen zwischen Daten sind also nicht mehr bloße Strukturinformationen, sondern stehen vielmehr im Zentrum des Modells.

Cypher: intuitiv nutzbare Programmiersprache

Die Zeiten, in denen Data Science zum Großteil aus Datenbereinigung und -mapping besteht, sind damit vorbei. Mit dem entsprechenden Ansatz laufen Entwicklungsprozesse deutlich schneller und einfacher ab. Data Scientists kommen mit weniger Code schneller ans Ziel und können mehr Zeit in das tatsächliche Entwickeln von relevanten Modellen investieren. Dabei nutzen sie die Flexibilität einer quelloffenen NoSQL-Graphdatenbank wie Neo4j kombiniert mit der Reife und weiten Verbreitung der Statistiksprache R für statistisches Rechnen und Visualisierung. Programmierer müssen nicht mehr stundenlang komplexe SQL-Anweisungen schreiben oder den ganzen Tag damit verbringen, eine Baumstruktur in SQL zu überführen. Sie benutzen einfach Cypher, eine musterbasierte, für Datenbeziehungen und Lesbarkeit optimierte Abfragesprache und legen los.