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.

map(String key, String value): 
    //key: document name 
    //value: document contents 
    for each word w in value: 
        EmitIntermediate(w,"1"); 
     
reduce(String key, Iterator values): 
    //key: a word 
    //values: a list of counts 
    int result = 0; 
    for each v in values: 
        result += ParseInt(v); 
        Emit(AsString(result)); 

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:

library(dplyr)
set.seed(42)	

df <- data.frame(id = 1:20, 
                 a=sample(c("Hund","Katze","Maus","Tiger"),20,replace=T),
                 b=sample(1:10,20, replace = T))
df
   id     a  b
1   1  Maus  7
2   2  Hund  3
3   3 Katze  3
4   4  Maus  4
5   5 Tiger 10
6   6  Maus 10
7   7  Hund  8
8   8  Hund  8
9   9  Hund  6
10 10 Katze  1
11 11  Maus  7
12 12  Hund  9
13 13  Hund  8
14 14 Tiger  5
15 15 Tiger  6
16 16  Maus  6
17 17 Katze  1
18 18  Maus  4
19 19  Maus  7
20 20  Maus  9
df %>%
  group_by(a) %>%
  mutate(r = row_number(),        # aus dplyr 
         n_memb = n(),            # aus dplyr
         n_dist = n_distinct(b),  # aus dplyr
         ra=rank(desc(b)),        # aus base und dplyr
         last_b = lag(b),         # aus dplyr
         next_b = lead(b),        # aus dplyr
         mb = mean(b),            # aus base
         cs = cumsum(b)  )        # aus base
Source: local data frame [20 x 11]
Groups: a [4]

     id      a     b     r n_memb n_dist    ra last_b next_b       mb     cs
                    
1      1   Maus     7     1      8      5   4.0     NA      4 6.750000     7
2      2   Hund     3     1      6      4   6.0     NA      8 7.000000     3
3      3  Katze     3     1      3      2   1.0     NA      1 1.666667     3
4      4   Maus     4     2      8      5   7.5      7     10 6.750000    11
5      5  Tiger    10     1      3      3   1.0     NA      5 7.000000    10
6      6   Maus    10     3      8      5   1.0      4      7 6.750000    21
7      7   Hund     8     2      6      4   3.0      3      8 7.000000    11
8      8   Hund     8     3      6      4   3.0      8      6 7.000000    19
9      9   Hund     6     4      6      4   5.0      8      9 7.000000    25
10    10  Katze     1     2      3      2   2.5      3      1 1.666667     4
11    11   Maus     7     4      8      5   4.0     10      6 6.750000    28
12    12   Hund     9     5      6      4   1.0      6      8 7.000000    34
13    13   Hund     8     6      6      4   3.0      9     NA 7.000000    42
14    14  Tiger     5     2      3      3   3.0     10      6 7.000000    15
15    15  Tiger     6     3      3      3   2.0      5     NA 7.000000    21
16    16   Maus     6     5      8      5   6.0      7      4 6.750000    34
17    17  Katze     1     3      3      2   2.5      1     NA 1.666667     5
18    18   Maus     4     6      8      5   7.5      6      7 6.750000    38
19    19   Maus     7     7      8      5   4.0      4      9 6.750000    45
20    20   Maus     9     8      8      5   2.0      7     NA 6.750000    54

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:

df %>%
  mutate(r = row_number(),        # aus dplyr 
         n_memb = n(),            # aus dplyr
         n_dist = n_distinct(b),  # aus dplyr
         ra=rank(desc(b)),        # aus base und dplyr
         last_b = lag(b),         # aus dplyr
         next_b = lead(b),        # aus dplyr
         mb = mean(b),            # aus base
         cs = cumsum(b)  )        # aus base


   id     a  b  r n_memb n_dist   ra last_b next_b  mb  cs
1   1  Maus  7  1     20      9  9.0     NA      3 6.1   7
2   2  Hund  3  2     20      9 17.5      7      3 6.1  10
3   3 Katze  3  3     20      9 17.5      3      4 6.1  13
4   4  Maus  4  4     20      9 15.5      3     10 6.1  17
5   5 Tiger 10  5     20      9  1.5      4     10 6.1  27
6   6  Maus 10  6     20      9  1.5     10      8 6.1  37
7   7  Hund  8  7     20      9  6.0     10      8 6.1  45
8   8  Hund  8  8     20      9  6.0      8      6 6.1  53
9   9  Hund  6  9     20      9 12.0      8      1 6.1  59
10 10 Katze  1 10     20      9 19.5      6      7 6.1  60
11 11  Maus  7 11     20      9  9.0      1      9 6.1  67
12 12  Hund  9 12     20      9  3.5      7      8 6.1  76
13 13  Hund  8 13     20      9  6.0      9      5 6.1  84
14 14 Tiger  5 14     20      9 14.0      8      6 6.1  89
15 15 Tiger  6 15     20      9 12.0      5      6 6.1  95
16 16  Maus  6 16     20      9 12.0      6      1 6.1 101
17 17 Katze  1 17     20      9 19.5      6      4 6.1 102
18 18  Maus  4 18     20      9 15.5      1      7 6.1 106
19 19  Maus  7 19     20      9  9.0      4      9 6.1 113
20 20  Maus  9 20     20      9  3.5      7     NA 6.1 122

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:

products <- data.frame(
  id = 1:5, 
  name = c("Desktop", "Laptop", "Maus", "Tablet", "Smartphone"),
  preis = c(500, 700, 10, 300, 500)  
)

set.seed(1)

(salesfacts <- data.frame(
  prod_id = sample(1:5,size = 8,replace = T),
  date = as.Date('2017-01-01') + sample(1:5,size = 8,replace = T)
)  )  

 prod_id       date
1      2 2017-01-05
2      2 2017-01-02
3      3 2017-01-03
4      5 2017-01-02
5      2 2017-01-05
6      5 2017-01-03
7      5 2017-01-05
8      4 2017-01-04

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:

salesfacts %>% 
  inner_join(products, by = c("prod_id" = "id"))

  prod_id       date       name preis
1       2 2017-01-05     Laptop   700
2       2 2017-01-02     Laptop   700
3       3 2017-01-03       Maus    10
4       5 2017-01-02 Smartphone   500
5       2 2017-01-05     Laptop   700
6       5 2017-01-03 Smartphone   500
7       5 2017-01-05 Smartphone   500
8       4 2017-01-04     Tablet   300

products %>% 
  inner_join(salesfacts, by = c("id" = "prod_id")) 

  id       name preis       date
1  2     Laptop   700 2017-01-05
2  2     Laptop   700 2017-01-02
3  2     Laptop   700 2017-01-05
4  3       Maus    10 2017-01-03
5  4     Tablet   300 2017-01-04
6  5 Smartphone   500 2017-01-02
7  5 Smartphone   500 2017-01-03
8  5 Smartphone   500 2017-01-05

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:

salesfacts %>% 
  inner_join(products, by = c("prod_id" = "id")) %>% 
  group_by(prod_id) %>% 
  summarise(n_verk = n(), sum_preis = sum(preis), letzt_dat = max(date))

# A tibble: 4 × 4
  prod_id n_verk sum_preis  letzt_dat
                
1       2      3      2100 2017-01-05
2       3      1        10 2017-01-03
3       4      1       300 2017-01-04
4       5      3      1500 2017-01-05

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:

SELECT ...
FROM a
WHERE EXISTS (SELECT * FROM b WHERE b.a_id = a.id)

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:

products %>% anti_join(salesfacts,c("id" = "prod_id"))

  id    name preis
1  1 Desktop   500

… 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:

mysql_db <- src_mysql(host = "localhost", user = "testuser",
                   password = "********", dbname = "test")

library(ggplot2)

str(diamonds)

Classes ‘tbl_df’, ‘tbl’ and 'data.frame':       53940 obs. of  10 variables:
 $ carat  : num  0.23 0.21 0.23 0.29 0.31 0.24 0.24 0.26 0.22 0.23 ...
 $ cut    : chr  "Ideal" "Premium" "Good" "Premium" ...
 $ color  : chr  "E" "E" "E" "I" ...
 $ clarity: chr  "SI2" "SI1" "VS1" "VS2" ...
 $ depth  : num  61.5 59.8 56.9 62.4 63.3 62.8 62.3 61.9 65.1 59.4 ...
 $ table  : num  55 61 65 58 58 57 57 55 61 61 ...
 $ price  : int  326 326 327 334 335 336 336 337 337 338 ...
 $ x      : num  3.95 3.89 4.05 4.2 4.34 3.94 3.95 4.07 3.87 4 ...
 $ y      : num  3.98 3.84 4.07 4.23 4.35 3.96 3.98 4.11 3.78 4.05 ...
 $ z      : num  2.43 2.31 2.31 2.63 2.75 2.48 2.47 2.53 2.49 2.39 ...

diamonds %>% mutate(cut = as.character(cut), 
                    color = as.character(color),
                    clarity = as.character(clarity)) -> diamonds

diamonds_mysql <- copy_to(mysql_db, diamonds, name="diamonds",
                         temporary = FALSE, indexes = list(
                       c("cut", "color", "clarity"), "carat", "price"))

diamonds_mysql %>% summarise(count = n())

Source:   query [?? x 1]
Database: mysql 5.5.54-0ubuntu0.14.04.1 [testuser@localhost:/test]

  count
  <dbl>
1 53940

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():

diamonds_mysql2 <- tbl(mysql_db,"diamonds")

identical(diamonds_mysql,diamonds_mysql2)

[1] TRUE

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:

bilanz <- diamonds_mysql2 %>% 
  filter(carat >= 1) %>% 
  group_by(cut,color,clarity) %>% 
  summarise(count = n(), mean_price = mean(price))

bilanz

Source:   query [?? x 5]
Database: mysql 5.5.54-0ubuntu0.14.04.1 [testuser@localhost:/test]
Groups: cut, color

     cut color clarity count mean_price
   <chr> <chr>   <chr> <dbl>      <dbl>
1   Fair     D      I1     3   9013.667
2   Fair     D     SI1    26   6398.192
3   Fair     D     SI2    29   6138.552
4   Fair     D     VS1     1   7083.000
5   Fair     D     VS2     7   8553.429
6   Fair     D    VVS1     1  10752.000
7   Fair     D    VVS2     2   9639.000
8   Fair     E      I1     5   2469.800
9   Fair     E     SI1    28   6407.464
10  Fair     E     SI2    45   5627.489
# ... with more rows

explain(bilanz)

<SQL>
SELECT `cut`, `color`, `clarity`, count(*) AS `count`, AVG(`price`) AS `mean_price`
FROM (SELECT *
FROM `diamonds`
WHERE (`carat` >= 1.0)) `cttxnwlelz`
GROUP BY `cut`, `color`, `clarity`


<PLAN>
  id select_type      table type  possible_keys  key key_len  ref  rows
1  1     PRIMARY <derived2>  ALL           <NA> <NA>    <NA> <NA> 19060
2  2     DERIVED   diamonds  ALL diamonds_carat <NA>    <NA> <NA> 50681
                            Extra
1 Using temporary; Using filesort
2                     Using where

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():

compute(bilanz, name = "t_bilanz", temporary = F)

df <- collect(bilanz)

str(df)

Classes ‘grouped_df’, ‘tbl_df’, ‘tbl’ and 'data.frame': 265 obs. of  5 variables:
 $ cut       : chr  "Fair" "Fair" "Fair" "Fair" ...
 $ color     : chr  "D" "D" "D" "D" ...
 $ clarity   : chr  "I1" "SI1" "SI2" "VS1" ...
 $ count     : num  3 26 29 1 7 1 2 5 28 45 ...
 $ mean_price: num  9014 6398 6139 7083 8553 ...
...

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.