R bietet mehrere Möglichkeiten, mit Datenbanken zu kommunizieren. Dieser Artikel richtet sich sowohl an R-Anwender, die noch nicht von R aus mit Datenbanken gearbeitet haben, als auch an solche, die dies bereits tun, aber bisher nur eine Möglichkeit genutzt haben. So kann jedeR den Ansatz auswählen, der für den jeweiligen Anwendungsfall am besten passt.

SQL und R: Strategien zur Kommunikation mit Datenbanken

Die folgenden Strategien werden im Video und im Blogartikel vorgestellt:

• Das DBI-Paket in Verbindung mit einem Backend-Paket
• tidyverse-Stil: dplyr und dbplyr
• SQL-Code in Markdown-Dokumente einbetten

Dazu gibt es noch ein paar Tipps, u. a. zur Absicherung des Datenbank-Zugangs (Passwörter etc.) und weitere Paket-Empfehlungen.

DBI und Backend-Paket

Die erste Möglichkeit besteht darin, mit dem DBI-Paket („R Database Interface“) sowie einem Backend-Paket eine Datenbankverbindung aufzubauen. Übliche Backends sind:

• MySQL und MariaDB: R-Paket RMySQL
• Postgres und Redshift: R-Paket RPostgreSQL
• SQLite: R-Paket RSQLite
• Googles Bigquery: R-Paket bigrquery
• Viele kommerzielle Datenbanken halten sich an das open database connectivity protocol: R-Paket odbc

Das DBI-Paket (Autoren: eine Gruppe um Hadley Wickham und Kirill Müller) ist gut dokumentiert und enthält mehrere Vignetten, siehe help(package = „DBI“).

Hier ein Codebeispiel:

library(DBI)
library(RSQLite)

# ":memory:" ist ein Platzhalter für eine ad-hoc-Datenbank im Arbeitsspeicher
con <- dbConnect(RSQLite::SQLite(), ":memory:"

# In realen Projekten bräuchte man Zugangsdaten wie user, password, host, port, dbname etc. 
# Beispiel (aus der dbplyr-Dokumentation):
# con <- DBI::dbConnect(RMariaDB::MariaDB(), 
# host = "database.rstudio.com",
# user = "hadley",
# password = rstudioapi::askForPassword("Database password")
# )

class(con)
# [1] "SQLiteConnection"
# attr(, "package")
# [1] "RSQLite"

# Nehmen wir an, die Datenbank enthält Daten
# Eine leere DB kann mit dplyr::copy_to() mit Daten aus R befüllt werden

(table_names <- dbListTables(con))
# [1] "comments" "tweats" "users"

users <- dbReadTable(con, "users")
(latest <- dbGetQuery(con, "SELECT post FROM tweats WHERE date > "2015-09-21"))
# [1] open and crush avocado. add shrimps. perferct starter.
# [2] just eat an apple. simply and healthy.

# Datenbankverbindung beenden
dbDisconnect(con)
# [1] TRUE
rm(con)

In dem fiktiven Beispiel oben geht es um Rezepte, die in einer Twitter-Alternative kursieren und hier „tweats“ genannt werden. Die Funktionen zur Kommunikation lauten:

• dbConnect() zur Aufnahme der Datenbankverbindung – im Folgenden wird sie mit dem Objekt con angesprochen
• dbListTables(), um die Tabellen der Datenbank abzurufen
• dbReadTable(), um eine Tabelle in R zu laden
• dbGetQuery(), um SQL-Befehle an die Datenbank zu schicken
• dbDisconnect(), um die Datenbankverbindung zu beenden.

Dieser Ansatz ist praktisch, wenn man über SQL-Erfahrung verfügt oder bereits SQL-Code von anderer Stelle zur Verfügung hat, den man in R anwenden möchte.

Der tidyverse-Ansatz: dplyr und dbplyr

Gute Nachricht für diejenigen, die mit dem Syntax-Stil des tidyverse und insbesondere dem dplyr-Paket vertraut sind: Der gewohnte R-Code kann auch verwendet werden, um mit Datenbanken zu kommunizieren! D. h. hier sind keinerlei SQL-Kenntnisse erforderlich. Das dbplyr-Paket übersetzt im Hintergrund den dplyr-Code in SQL. Unterschied zum gewohnten dplyr-Stil: Anstatt eines Datensatzes, der im Arbeitsspeicher liegt, verwenden wir die Datenbankverbindung, die wie oben das con-Objekt mittels DBI definiert wird. Den Rest übernimmt dbplyr für uns.

Es gibt bei dieser Strategie einige Besonderheiten:

• dbplyr ist faul (lazy): Es zieht nicht automatisch bei jedem dplyr-Befehl Daten aus der Datenbank, sondern verzögert die Ausführung so lange wie möglich. Dadurch kann eine längere Befehlskette effizient auf einmal ausgeführt werden.
• Selbst nach Aufforderung werden nicht gleich alle relevanten Daten aus der Datenbank geholt, sondern nur einige Zeilen. So kann man mit dem Code experimentieren, ohne viel Zeit an aufwändige Datenbank-Transaktionen zu verlieren.
• Wenn der Code die gewünschten Resultate liefert, kann man die Daten explizit mit collect() anfordern.
• Die Funktion show_query() zeigt den SQL-Übersetzung des R-Codes.
• nrow() liefert NA, da vor collect() die Anzahl der Ergebniszeilen nicht bekannt ist.
• tail() funktioniert nicht direkt aus der Datenbank – erst nach collect().

Codebeispiel:

library(DBI)
library(RMariaDB)

library(dplyr)
con <- DBI::dbConnect(RSQLite::SQLite(), dbname = ":memory:")

# Datenbank mit Daten füllen aus dem nycflights13-Paket
# install.packages("nycflights13")
copy_to(con, nycflights13::flights, "flights",
  temporary = FALSE, 
  indexes = list(
    c("year", "month", "day"), 
    "carrier", 
    "tailnum",
    "dest"
  )
)

flights_db %>% 
  group_by(dest) %>%
  summarise(delay = mean(dep_time))
#> Warning: Missing values are always removed in SQL.
#> Use `mean(x, na.rm = TRUE)` to silence this warning
#> This warning is displayed only once per session.
#> # Source: lazy query [?? x 2]
#> # Database: sqlite 3.34.1 [:memory:]
#> dest delay
#> <chr> <dbl>
#> 1 ABQ 2006.
#> 2 ACK 1033.
#> 3 ALB 1627.
#> 4 ANC 1635.
#> 5 ATL 1293.
#> 6 AUS 1521.
#> # … with more rows

dbplyr unterstützt mindestens folgende Datenbanken: Oracle, Microsoft SQL Server, PostgreSQL, Amazon Redshift, Apache Hive, Apache Impala.

Für mehr siehe die ausführlichen Vignetten unter help(package = „dbplyr“).

SQL-Code direkt in RMarkdown-Dokumente einbetten

R Markdown bietet vielfältige Möglichkeiten, ansprechende Berichte aus R heraus zu erstellen und voll zu automatisieren. Dabei werden verschiedene Ausgabeformate unterstützt: Word, Powerpoint, PDF und diverse HTML-Formate. Letztere schätze ich besonders wegen der Option, interaktive Elemente einzubinden, etwa filter- und sortierbare Datensätze oder Grafiken mit Mouse-Over-Effekten.

In Beispielen sieht man häufig lediglich R-Codeblöcke in Markdown-Berichten, die mit den typischen Zeichenfolgen gekennzeichnet sind:

```{r}
# R Code
```

Das „{r}“ lässt schon erahnen, dass auch andere Sprachen unterstützt werden. Das ist tatsächlich so – SQL-Code ist ebenfalls möglich, wie dieses Beispiel zeigt (zuvor muss wieder, wie oben beschrieben, eine Datenbankverbindung als con definiert sein):

'''{sql, connection=con, output.var = "mydataframe"}
SELECT "month_idx", "year", "month",
SUM(CASE WHEN ("term_deposit" = 'yes’)
THEN (1.0) ELSE (0.0) END) AS "subscribe",
COUNT(*) AS "total" FROM ("bank")
GROUP BY "month_idx", "year", "month“
'''

Vorteile dieses Ansatzes:

• • Im Gegensatz zum ersten Beispiel mit DBI und einem Backend-Paket profitiert man hier von Syntax Highlighting.
  • Der SQL-Code muss nicht in R-Ausdrücke eingebettet werden (wie oben dbGetQuery())
  • Neben R und SQL werden viele weitere Sprachen unterstützt, u. a. scala, stata, css, python, sass
  • Man kann Chunk-Optionen für Einstellungen nutzen, z. B. max.print für die Anzahl der höchstens dargestellten Ergebniszeilen, tab.cap für eine Beschriftung der Anzahl

SQL und R: Paket-Empfehlungen und Tricks

• RStudio bietet als Ergänzung zu kostenpflichtigen Produkten (wie RStudio Server Pro, RStudio Connect, Shiny Server Pro) spezielle Treiber für kommerzielle Datenbanken, z. B. Microsoft SQL Server, MongoDB, IBM Netezza, Salesforce etc., siehe hier.
• RStudio bietet neben Environment und History einen extra Bereich Connections, in dem Datenbankverbindungen hinterlegt werden können und der z. B. R-Code-Schnipsel bereithalten kann.
• DBI::dbGetQuery() sollte vermieden werden, wenn Anwender selbst SQL-Code eingeben können: Gefahr von sog. SQL Injection Attacks.

  

• Datenbankzugänge nie direkt im R-Code platzieren! Alternativen:
• keyring-Paket zur Verschlüsselung
• config-Paket für eine separate YAML-Datei; praktisch, wenn man häufig zwischen einer Test-Datenbank und der „echten“ Produktions-Datenbank wechseln muss; der Datenbankzugang kann so parametrisiert werden
• Environment-Variablen verwenden
• Datenbankzugang abfragen mit rstudioapi::askForPassword()
• Shiny Apps: pool-Paket zur Verwaltung von Datenbank-Verbindungen
• Diagramme aus Datenbanken erstellen: dbplot-Paket

Zusammenfassung: SQL und R – Strategien zur Datenbankanbindung im Vergleich

Was sind die Vor- und Nachteile der drei Strategien, Datenbanken aus R heraus anzusprechen?

Methode	Vorteile
DBI und Backend-Paket	Weniger (Paket-)Abhängigkeiten
dplyr	Gleiche Syntax für Datensätze in R und in einer Datenbank Keine SQL-Kenntnisse erforderlich Code wird über SQL-Varianten hinweg standardisiert Lazy Evaluation: Experimentieren mit Datenbankabfragen, ohne viel Zeit für aufwändige Transaktionen zu verlieren
SQL-Code direkt in Markdown-Dokumente einbetten	SQL direkt per Copy & Paste einfügen, ohne Code-Formatierung (in dbGetQuery() kann es erforderlich sein, Anführungszeichen speziell zu behandeln, „escape“) Syntax Highlighting – SQL-Code besser lesbar als im ersten Ansatz

Wie sind Eure Erfahrungen? Freue mich über Kommentare! Und nun viel Erfolg mit R und Datenbanken!