Data Science, Machine Learning und KI
Kontakt

Wer mit großen Datenmengen in seinem beruflichen Alltag zu tun hat, der weiß, wie nützlich Datenbanken sein können. Als elektronisches Verwaltungssystem sind Datenbanken darauf ausgelegt, effizient und widerspruchsfrei mit großen Datenmengen umzugehen. Zudem sorgt eine Datenbank im Unternehmen dafür, dass jeder Mitarbeiter auf einen einheitlichen und aktuellen Datenstand zurückgreifen kann. Änderungen in der Datenbasis werden somit allen Beteiligten direkt zuteil. Das ist insbesondere dann von Vorteil, wenn Daten computergestützt automatisch verarbeitet werden.

Mit diesen Paketen meistern Sie den Umgang mit Datenbanken

R bietet verschiedene Pakete zum Umgang mit Datenbanken und ermöglicht es somit auf eine komfortable Art, sich aus R heraus mit Datenbank zu verbinden und diese in den Data Science Prozess zu integrieren. So können wir uns zum Beispiel mit den Paketen DBI und RMySQL bequem und einfach mit unserer Testdatenbank test_db verbinden und uns die darin liegende Testtabelle flights anschauen, welche Informationen zu Abflügen am New Yorker Flughafen des Jahres 2013 enthält.

# Pakete laden 
library(DBI)    # Funktionen zum Umgang mit Datenbanken 
library(RMySQL) # MySQL Treiber 
library(dplyr)  # Für %>%  

# Konnektor-objekt erzeugen 
con % 
  dbGetQuery("SELECT month, day, carrier, origin, dest, air_time  
              FROM flights LIMIT 3") 

#   month day carrier origin dest air_time 
# 1     1   1      UA    EWR  IAH      227 
# 2     1   1      UA    LGA  IAH      227 
# 3     1   1      AA    JFK  MIA      160 

# Verbindung schließen 
dbDisconnect(con) 

# [1] TRUE 

Wie wir sehen, genügen wenige Zeilen Code, um den Inhalt der Datenbanktabelle einzusehen. Der Umgang mit Datenbanken, auch über die R API, hat jedoch einen kleinen Knackpunkt: Für den Umgang mit Datenbanken muss man SQL beherrschen. Das ist an sich kein großes Problem, da SQL als deklarative Sprache recht intuitiv ist. So ist die im Beispiel aufgeführte Query auch einfach zu verstehen. Das Ganze ändert sich jedoch, wenn die Daten auf der Datenbank zu groß sind, um diese über ein einfaches SELECT * FROM abzufragen. In diesem Fall ist es notwendig, erste Aggregationen auf der Datenbank durchzuführen. Sind diese komplex, kann SQL zur echten Hürde eines Data Scientisten werden.

Bei STATWORX greifen wir ständig auf Datenbanken zurück, um unsere prädiktiven Systeme nahtlos in die Datenprozesse unserer Kunden integrieren zu können. Man muss jedoch kein SQL-Experte sein, um dies zu tun. Einzelne Packages helfen, mit Datenbanken sicher und souverän umzugehen.

Im Folgenden werden drei Pakete vorgestellt, die die Arbeit mit Datenbanken sicherer, stabiler und einfacher machen.

Verbindungen clever verwalten mit pool

Beim Umgang mit Datenbanken sind oft auch etwas technischere Themen von Relevanz.

So kann das Verwalten der Verbindungen sehr mühsam sein, wenn diese dynamisch, zum Beispiel in einer Shiny App, erzeugt werden. Dies kann zum Absturz einer App führen, da manche Datenbanken per Default nur 16 gleichzeitige Verbindungen erlauben. Konnektoren müssen daher immer auch geschlossen werden, sollten diese nicht mehr gebraucht werden. Das Schließen wird im Beispielcode oben zum Schluss durchgeführt.

Um Konnektoren stabiler zu verwalten, kann das Paket pool verwendet werden.

Das pool-Paket erzeugt eine Art intelligenten Konnektor, einen sogenannten Objekt-Pool. Das Praktische daran ist, dass sich der Pool einmal zu Beginn des Aufrufs um das Erzeugen von Konnektoren kümmert und diese das möglichst effizient verwaltet, d.h. Verbindungen zur Datenbank möglichst optimal auslastet. Ein Vorteil im Umgang mit dem pool-Paket ist, dass sich die Funktionalitäten zum DBI-Paket kaum unterscheiden. Dies wollen wir einmal genauer anhand eines Beispiels betrachten.

# Paket laden  
library(pool) 

# Pool-Objekt erzeugen 
pool % 
  dbGetQuery("SELECT month, day, carrier, origin, dest, air_time  
             FROM flights LIMIT 3") 
 
#   month day carrier origin dest air_time 
# 1     1   1      UA    EWR  IAH      227 
# 2     1   1      UA    LGA  IAH      227 
# 3     1   1      AA    JFK  MIA      160 

# Verbindung schließen 
poolClose(pool)  

Wie wir sehen, hat sich die Syntax kaum verändert. Der einzige Unterschied liegt darin, dass wir den Pool mit den eigens dafür vorgesehenen Funktionen dbPool() und poolClose() verwalten. Um die Konnektoren, die für Datenbankabfragen benötigt werden, kümmert sich der Pool selbst. Dies zeigt die untere Grafik schematisch. Der User sendet – stark vereinfacht gesagt – die Query an den Pool, woraufhin der Pool bestimmt, über welchen Konnektor er die Query an die Datenbank schickt und das Ergebnis zurückgibt.

Zugangsdaten verbergen mit config

Beim Erstellen einer Verbindung ist die Eingabe von Zugangsdaten notwendig, die besser an einem geschützten Ort liegen sollten. Dies stellte im obigen Beispiel kein Problem dar, da wir test_db bei uns lokal auf dem PC laufen lassen und die Zugangsdaten somit nicht sensibel sind. Soll der Code zur Erstellung des Konnektors jedoch mit anderen Mitarbeitern geteilt werden, empfiehlt es sich, die Zugangsdaten aus einem R-Objekt auszulesen.

Mit config können YAML Konfigurationsdateien aus R heraus eingelesen werden, in denen sich zum Beispiel Zugangsdaten für Datenbanken abspeichern lassen (YAML ist ein für den Menschen lesefreundliches Format, um Daten zu speichern). So müssen wir im ersten Schritt lediglich eine Konfigurationsdatei erstellen, die wir config.yml nennen.

# Konfigurationsdatei erstellen  
default: 
  database_settings: 
    host: 127.0.0.1 
    dbname: test_db 
    user: root 
    pwd: root 
    port: 3306 
  other_setting: 
    filepath: /path/to/file 
    username: gauss

Zu beachten ist hierbei, dass die erste Zeile der Datei obligatorisch ist. Das YAML Format hat den Vorteil, dass Einstellungen thematisch durch das Einführen von Unterlisten voneinander getrennt werden können. So haben wir im Beispiel eine Unterliste mit den Zugangsdaten zur Datenbank (database_settings) und eine Unterliste mit sonstigen beispielhaften Einstellungen (other_settings) erstellt. Nun können im zweiten Schritt mit der Funktion get() gezielt die Datenbankeinstellungen eingelesen werden.

# Paket laden 
library(config) 

# Datenbankeinstellungen laden 
config <- get(value = "database_settings", 
              file  = "~/Desktop/r-spotlight/config.yml") 

str(config) 

# List of 5 
# $ host  : chr "127.0.0.1" 
# $ dbname: chr "test_db" 
# $ user  : chr "root" 
# $ pwd   : chr "root" 
# $ port  : int 3306 

Beim Erstellen eines Pools müssen wir nun nicht mehr unsere sensiblen Daten offenbaren.

# Pool-Objekt erzeugen 
pool <- dbPool(drv      = RMySQL::MySQL(), 
               user     = config$user, 
               password = config$pwd, 
               host     = config$host, 
               port     = config$port, 
               dbname   = config$dbname) 

SQL ohne SQL dank dbplyr

dbplyr ist das Datenbank-Back-End von dplyr und kümmert sich schlicht und einfach darum, dass dplyr’s elegante Syntax auch bei der Nutzung von Konnektor-Objekten Anwendung findet. dbplyr wurde in dplyr integriert und muss daher nicht separat geladen werden.

# Paket laden 
library(dplyr) 

# Eine kleine Query mit dplyr 
pool %>%  
  tbl("flights") %>%  
  select(month, day, carrier, origin, dest, air_time) %>% 
  head(n = 3) 

# Source:   lazy query [?? x 6] 
# Database: mysql 5.6.35 [root@127.0.0.1:/test_db] 
#   month   day carrier origin  dest air_time 
#               
# 1     1     1      UA    EWR   IAH      227 
# 2     1     1      UA    LGA   IAH      227 
# 3     1     1      AA    JFK   MIA      160

Dabei fällt auf, dass es sich bei dem Ergebnis nicht um einen R Data Frame handelt (wie an „Source: lazy query …“ zu sehen). Vielmehr wird die eingegebene R-Syntax in SQL übersetzt und als Query an die Datenbank geschickt. Es wird somit alles auf der Datenbank berechnet. Den dahinterliegenden SQL-Befehlt kann man sich mit show_query() anzeigen lassen.

# SQL anzeigen lassen 

pool %>%  
  tbl("flights") %>%  
  select(month, day, carrier, origin, dest, air_time) %>% 
  head(n = 3) %>% 
  show_query() 

# :SQL: 
# SELECT `month` AS `month`, `day` AS `day`, `carrier` AS `carrier`,  
#        `origin` AS `origin`, `dest` AS `dest`, `air_time` AS `air_time` 
# FROM `flights` 
# LIMIT 3 

Zugegeben: das war noch nicht die Killer-Query, aber das Prinzip sollte klar sein. Mit diesem Tool lassen sich schnell auch deutlich komplexere Querys schreiben. Entsprechend könnten wir nun die mittlere geflogene Distanz pro Fluggesellschaft auf der Datenbank berechnen lassen.

# Eine etwas komplexere Query 
qry % 
  tbl("flights") %>%  
  group_by(carrier) %>% 
  summarise(avg_dist = mean(distance)) %>% 
  arrange(desc(avg_dist)) %>% 
  head(n = 3) 

qry 

# Source:     lazy query [?? x 2] 
# Database:   mysql 5.6.35 [root@127.0.0.1:/test_db] 
# Ordered by: desc(avg_dist) 
#   carrier avg_dist 
#          
# 1      HA 4983.000 
# 2      VX 2499.482 
# 3      AS 2402.000 

Mit collect() können wir uns das Ergebnis des SQL-Statements als R-Objekt abspeichern lassen.

# SQL Resultat in R abspeichern 
qry %>% 
  collect() 

# A tibble: 3 x 2 
#   carrier avg_dist 
#          
# 1      HA 4983.000 
# 2      VX 2499.482 
# 3      AS 2402.000 

Fazit

Das Arbeiten mit Datenbanken kann durch das Verwenden der richtigen Pakete um einiges einfacher und sicherer gemacht werden. Für das Schreiben komplexer SQL Querys müssen wir dank dplyr nicht länger online SQL-Tutorials pauken, sondern können die freigewordene Zeit für wichtigere Dinge nutzen; zum Beispiel für das Vernaschen der köstlichen Süßigkeiten bei STATWORX, wie Jessica in ihrem Blogbeitrag festgestellt hat.

Referenzen

  1. Boergs, Barbara (2017). Pool: Object Pooling. R Package Version 0.1.3. URL: https://CRAN.R-project.org/package=pool
  2. Datenbanken verstehen. Was ist eine Datenbank? URL: http://www.datenbanken-verstehen.de/datenbank-grundlagen/datenbank/
  3. Wickham, Hadley (2017a). Flights that Departed NYC in 2013. R Package. URL: https://CRAN.R-project.org/package=nycflights13
  4. Wickham, Hadley (2017b). dbplyr: A ‚dplyr‘ Back End for Databases. R Package Version 1.1.0. URL: https://CRAN.R-project.org/package=dbplyr

Tobias Krabel

Seit einiger Zeit ist mit Shiny ein Paket für die Statistiksoftware R verfügbar, mit dessen Hilfe man ansprechende, interaktive Webapplikationen erstellen und hierbei auf den kompletten Funktionsumfang von R zugreifen kann.

Mit Hilfe des R Paketes Shiny interaktive Webapplikationen erstellen

Mit Hilfe des R Paketes Shiny lassen sich schnell HTML/JavaScript-basierte, interaktive Webapplikationen erstellen. Die möglichen Anwendungsszenarien sind vielfältig: Reporting, Deployment von statistischen Analysen, interaktive Visualisierungen von Datenbeständen. Bei der Erstellung einer Shiny Applikation sind grundsätzlich keine HTML- oder JavaScript-Kenntnisse notwendig, da die komplette Programmierung der App direkt in R stattfindet. Lediglich beim Customizen der Applikation, sprich, bei der Anpassung von Farben, Logos, Fonts und Layouts sind Grundkenntnisse in HTML/CSS/JavaScript nötig.

Die Vorteile von Shiny

Besonders vorteilhaft an Shiny ist, dass die interaktive Web-App auf den kompletten Funktionsumfang von R zurückgreifen kann. R bietet zur Zeit über 10000 Packages für statistische Auswertungen, Data Mining und Predictive Analytics an und zählt somit zu der wichtigsten Statistiksoftware auf dem Markt. Zudem ist R eine Open-Source-Software und somit, ebenso wie das Zusatzpaket Shiny, kostenlos erhältlich.

Shiny ermöglicht es dem Anwender, in einer optisch ansprechenden und übersichtlichen Web-Oberfläche, verschiedene Input-Parameter auszuwählen bzw. zu variieren, auf Basis derer eine Berechnung und Analyse in R gestartet wird. Hierfür werden zwei Shiny-Programme geschrieben. Eines zur Definition der Benutzeroberfläche (ui.r) und ein zweites in dem die Analyse mit R stattfindet (server.r). Als Eingabemöglichkeiten auf Benutzerseite stehen z. B. Slider, Drop-Downs, Text- und Zahleneingabe-, aber auch Optionsfelder zur Verfügung. Auch der Import von lokalen Excel-Files und eine entsprechende Verarbeitung der Daten in der Shiny Applikation ist möglich. Auf der Output-Seite stehen dem Benutzer Tabellen, Summaries, Grafiken, Landkarten und Texte zur Verfügung, die reaktiv, je nach Selektion der Inputparameter, on-the-fly angepasst werden. Grundsätzlich lassen sich alle R Packages in eine Shiny-App implementieren, was nahezu unendliche Anwendungsmöglichkeiten offenbart.

Die fertige Applikation kann bspw. via GitHub gist, als R-Paket oder als zip-Ordner versendet oder auf allen Rechnern, die R und Shiny installiert haben, aufgerufen werden. Außerdem kann die Web-App auf einem Linux-basierten Shiny Server online zur Verfügung gestellt werden. Hier existieren eine kostenfreie Open-Source-Edition sowie eine kostenpflichtige Enterprise-Variante, die weitere, für Unternehmen wichtige, Features enthält.

Fazit

Dank Shiny ist es möglich, auf eine einfache und schnelle Art ansprechende, interaktive Applikationen zu erstellen, die auf den kompletten statistischen Funktionsumfang von R zurückgreifen können. Tobias Krabel

Seit einiger Zeit ist mit Shiny ein Paket für die Statistiksoftware R verfügbar, mit dessen Hilfe man ansprechende, interaktive Webapplikationen erstellen und hierbei auf den kompletten Funktionsumfang von R zugreifen kann.

Mit Hilfe des R Paketes Shiny interaktive Webapplikationen erstellen

Mit Hilfe des R Paketes Shiny lassen sich schnell HTML/JavaScript-basierte, interaktive Webapplikationen erstellen. Die möglichen Anwendungsszenarien sind vielfältig: Reporting, Deployment von statistischen Analysen, interaktive Visualisierungen von Datenbeständen. Bei der Erstellung einer Shiny Applikation sind grundsätzlich keine HTML- oder JavaScript-Kenntnisse notwendig, da die komplette Programmierung der App direkt in R stattfindet. Lediglich beim Customizen der Applikation, sprich, bei der Anpassung von Farben, Logos, Fonts und Layouts sind Grundkenntnisse in HTML/CSS/JavaScript nötig.

Die Vorteile von Shiny

Besonders vorteilhaft an Shiny ist, dass die interaktive Web-App auf den kompletten Funktionsumfang von R zurückgreifen kann. R bietet zur Zeit über 10000 Packages für statistische Auswertungen, Data Mining und Predictive Analytics an und zählt somit zu der wichtigsten Statistiksoftware auf dem Markt. Zudem ist R eine Open-Source-Software und somit, ebenso wie das Zusatzpaket Shiny, kostenlos erhältlich.

Shiny ermöglicht es dem Anwender, in einer optisch ansprechenden und übersichtlichen Web-Oberfläche, verschiedene Input-Parameter auszuwählen bzw. zu variieren, auf Basis derer eine Berechnung und Analyse in R gestartet wird. Hierfür werden zwei Shiny-Programme geschrieben. Eines zur Definition der Benutzeroberfläche (ui.r) und ein zweites in dem die Analyse mit R stattfindet (server.r). Als Eingabemöglichkeiten auf Benutzerseite stehen z. B. Slider, Drop-Downs, Text- und Zahleneingabe-, aber auch Optionsfelder zur Verfügung. Auch der Import von lokalen Excel-Files und eine entsprechende Verarbeitung der Daten in der Shiny Applikation ist möglich. Auf der Output-Seite stehen dem Benutzer Tabellen, Summaries, Grafiken, Landkarten und Texte zur Verfügung, die reaktiv, je nach Selektion der Inputparameter, on-the-fly angepasst werden. Grundsätzlich lassen sich alle R Packages in eine Shiny-App implementieren, was nahezu unendliche Anwendungsmöglichkeiten offenbart.

Die fertige Applikation kann bspw. via GitHub gist, als R-Paket oder als zip-Ordner versendet oder auf allen Rechnern, die R und Shiny installiert haben, aufgerufen werden. Außerdem kann die Web-App auf einem Linux-basierten Shiny Server online zur Verfügung gestellt werden. Hier existieren eine kostenfreie Open-Source-Edition sowie eine kostenpflichtige Enterprise-Variante, die weitere, für Unternehmen wichtige, Features enthält.

Fazit

Dank Shiny ist es möglich, auf eine einfache und schnelle Art ansprechende, interaktive Applikationen zu erstellen, die auf den kompletten statistischen Funktionsumfang von R zurückgreifen können. Tobias Krabel