Datenbankabfragen mit R und SQL

Abbildung 1: Struktur der Datenbank Artigo in phpMyAdmin.

Das vorliegende, kommentierte Skript dient dazu, über das DHVLab mit R und dem Paket RMySQL eine Verbindung zu einer MySQL-Datenbank herzustellen und auf Basis dieser Datenbank weiterführende Analysen durchzuführen. In den Abschnitten Abfragen mit SQL und Abfragen mit R werden jeweils dieselben Fragestellungen abgehandelt, um Unterschiede in der Syntax der beiden Sprachen aufzudecken. Der Part mit Abfragen in SQL setzt Grundlagen in SQL voraus, der Part mit Abfragen in R entsprechend Grundlagen in R.

Für die Ausführung der Befehle ist ein freigeschalteter Account des DHVLab notwendig. Konkret wird der Bestand des Onlinespiels ARTigo betrachtet, in dem Kunstwerke verschlagwortet werden. Die Struktur der Datenbank Artigo können Sie zuvor im Datenbankmanagementsystem phpMyAdmin einsehen, indem Sie über die Navigation auf der linken Seite all -> art -> all_art_artigo anwählen (Abbildung 1). Für dieses Skript sind die Tabellen artworks, tag und tagging relevant.

In dieser Einheit verwendete Funktionen: library, dbConnect, dbGetQuery, dbListTables, colnames, nrow, merge, unique, aggregate, order.

Verbindung zur Datenbank herstellen

# Notwendige Pakete laden
library(RMySQL)

# Mit Datenbank verbinden (Statt "ihrname" und "ihrpasswort" tragen Sie Ihre bei der Registrierung am DHVLab erhaltenen Benutzerdaten ein)
connect <- dbConnect(MySQL(), host = "db.dmz.dhvlab.fo", user = "ihrname", password = "ihrpasswort", dbname = "[Name der Datenbank]")

# Wenn Sie vermeiden möchten, dass Ihr Passwort als plaintext in der Konsole wiedergegeben wird (z.B. im Unterrichtskontext), können Sie alternativ 
# eine cnf-Datei (abgelegt in Ihrem persönlichen Verzeichnis auf dem Virtuellen Desktop) mit Ihren Login-Daten für die DB-Verbindung mit R verwenden:
rmysql.settingsfile <- "/home/users/[Benutzername]/.my.cnf"
connect <- dbConnect(MySQL(), default.file = rmysql.settingsfile, dbname = "[Name der Datenbank]")

In der .my.cnf-Datei werden folgende Informationen hinterlegt:
[client]
host = db.dmz.dhvlab.fo
user = [Ihre DHVLab-Nutzerkennung]
password = [Passwort in Plaintext]

# Zu UTF-8 kodieren
dbGetQuery(connect, "set names utf8")

# Tabellen anzeigen
dbListTables(connect)

Abfragen mit SQL

# Alle Kunstwerke von Albrecht Dürer
sqlDürer <- dbGetQuery(connect, 
                               "select * 
                               from artworks 
                               where fullname = 'Albrecht Dürer'")

# Anzahl aller Kunstwerke von Albrecht Dürer
sqlDürerAnzahl <- dbGetQuery(connect, 
                               "select count(id) as Anzahl 
                               from artworks 
                               where fullname = 'Albrecht Dürer'")

# Alle Tags, mit denen Werke von Dürer ausgezeichnet worden sind
sqlDürerTag <- dbGetQuery(connect, 
                               "select a.id, a.title, t.tag_id 
                               from artworks a
                               join tagging t on t.resource_id = a.id
                               where a.fullname = 'Albrecht Dürer'")

# Alle verschiedenen Tags, mit denen Werke von Dürer ausgezeichnet worden sind
sqlDürerTagDistinct <- dbGetQuery(connect, 
                               "select distinct a.id, a.title, t.tag_id 
                               from artworks a
                               join tagging t on t.resource_id = a.id
                               where a.fullname = 'Albrecht Dürer'")

# Alle verschiedenen Tags als Klarname, mit denen Werke von Dürer ausgezeichnet worden sind
sqlDürerTagDistinctName <- dbGetQuery(connect, 
                               "select distinct a.id, a.title, b.name 
                               from artworks a
                               join tagging t on t.resource_id = a.id
                               join tag b on b.id = t.tag_id
                               where a.fullname = 'Albrecht Dürer'")

# Anzahl Tags, die pro Werk vergeben wurden, in absteigender Reihenfolge
sqlDürerTagAnzahl <- dbGetQuery(connect, 
                               "select a.id, a.title, count(*) as Anzahl 
                               from artworks a
                               join tagging t on t.resource_id = a.id
                               where a.fullname = 'Albrecht Dürer'
                               group by a.id
                               order by Anzahl desc")

# Wie vorhergehende Abfrage, jedoch nur die ersten 10 Resultate zurückgeben
sqlDürerTagAnzahl10 <- dbGetQuery(connect, 
                               "select a.id, a.title, count(*) as Anzahl 
                               from artworks a
                               join tagging t on t.resource_id = a.id
                               where a.fullname = 'Albrecht Dürer'
                               group by a.id
                               order by Anzahl desc
                               limit 10")

# Anzahl der verschiedenen Tags pro Werk
sqlDürerTagAnzahlDistinct <- dbGetQuery(connect, 
                               "select a.id, a.title, count(distinct t.tag_id) as Anzahl 
                               from artworks a
                               join tagging t on t.resource_id = a.id
                               where a.fullname = 'Albrecht Dürer'
                               group by a.id
                               order by Anzahl desc")

# Anzahl der verschiedenen Tags für alle Selbstbildnisse Dürers
sqlDürerTagAnzahlDistinctSelbst <- dbGetQuery(connect, 
                               "select a.id, a.title, count(distinct t.tag_id) as Anzahl 
                               from artworks a
                               join tagging t on t.resource_id = a.id
                               where a.fullname = 'Albrecht Dürer' and a.title like 'Selbst%'
                               group by a.id
                               order by Anzahl desc")

Abfragen mit R

# Alle Daten von artworks, tagging und tag einlesen
artworks <- dbGetQuery(connect, "select * from artworks")
tagging <- dbGetQuery(connect, "select * from tagging")
tag <- dbGetQuery(connect, "select * from tag")

# Abfrage aller Spaltennamen
colnames(artworks)
colnames(tagging)
colnames(tag)

# Alle Kunstwerke von Albrecht Dürer
rDürer <- artworks[which(artworks$fullname == "Albrecht Dürer"),]

# Anzahl aller Kunstwerke von Albrecht Dürer
nrow(rDürer)

# Alle Tags, mit denen Werke von Dürer ausgezeichnet worden sind
rDürerTag <- merge(rDürer, tagging, by.x = "id", by.y = "resource_id") # Tabellen verknüpfen
rDürerTag <- rDürerTag[,c("id","title","tag_id")] # Spalten auswählen

# Alle verschiedenen Tags, mit denen Werke von Dürer ausgezeichnet worden sind
rDürerTagDistinct <- unique(rDürerTag)

# Alle verschiedenen Tags als Klarname, mit denen Werke von Dürer ausgezeichnet worden sind
rDürerTagDistinctName <- merge(rDürerTagDistinct, tag[,c("id","name")], by.x = "tag_id", by.y = "id")

# Anzahl Tags, die pro Werk vergeben wurden, in absteigender Reihenfolge
rDürerTagAnzahl <- aggregate(tag_id ~ id + title, rDürerTag, length)
rDürerTagAnzahl <- rDürerTagAnzahl[order(rDürerTagAnzahl$tag_id, decreasing = TRUE),]

# Wie vorhergehende Abfrage, jedoch nur die ersten 10 Resultate zurückgeben
rDürerTagAnzahl10 <- rDürerTagAnzahl[1:10,]

# Anzahl der verschiedenen Tags pro Werk
rDürerTagAnzahlDistinct <- aggregate(tag_id ~ id + title, rDürerTag, function(x){length(unique(x))})
rDürerTagAnzahlDistinct <- rDürerTagAnzahlDistinct[order(rDürerTagAnzahlDistinct$tag_id, decreasing = TRUE),]

# Anzahl der verschiedenen Tags pro Selbstbildnis
rDürerTagAnzahlDistinctSelbst <- rDürerTagAnzahlDistinct[grepl("Selbst", rDürerTagAnzahlDistinct$title),]

Verbindung zur Datenbank schließen

on.exit(dbDisconnect(connect))