-
Notifications
You must be signed in to change notification settings - Fork 2
/
Copy path05_2_datenimport-exkurs-sql.Rmd
108 lines (84 loc) · 7.78 KB
/
05_2_datenimport-exkurs-sql.Rmd
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
## **Exkurs 3: Datenabfragen in SQL**
[](https://creativecommons.org/licenses/by/4.0/deed.de)
*"Datenabfragen in SQL" von Nina Hauser in "R Lernen - der Datenkurs von und für die Zivilgesellschaft" von CorrelAid e.V. Lizensiert unter [Creative Commons Attribution 4.0 International](https://creativecommons.org/licenses/by/4.0/deed.de).*
### **Interaktive Übung**
Daten, die uns in unserem Alltag als Datenanalyst:innen und Datenwissenschaftler:innen begegnen, sind oft in **Datenbanken** gespeichert. Datenbanken sind **logisch modellierte, strukturierte Datenspeicher**, mit denen mit Hilfe von **Datenbankmanagementsystemen (kurz DBMS)**, also Softwaretools, interagiert werden kann. Diese Interaktion funktioniert für jedes Datenbankmanagementsystem anders und ist **nicht trivial**. Während in einigen Fällen ein manueller Export als XLSX-, CSV- oder JSON-Datei möglich ist, kann es in einigen Fällen, auch auf Grund der Größe der Datensätze, nicht praktikabel sein manuell Daten zu exportieren. Daneben ist das natürlich auch IT im Schneckentempo statt in Lichtgeschwindigkeit. Idealerweise sind Eure Daten nämlich **live** mit Euren Analysetools verknüpft, sodass sie beständig aktuell sind. Die populärsten Datenbankmanagementsysteme findet Ihr in dieser Übersicht einer Umfrage unter Entwickler:innen von [Stack Overflow](https://insights.stackoverflow.com/survey/2020#technology-databases-all-respondents4){target="_blank}.
<center>
{#id .class width=50% height=50%}
</center>
An einer Technologie kommt man beim Thema Datenbanken also nicht vorbei: **SQL (Structured Query Language, zu dt. Strukturierte Abfragesprache)**. SQL erlaubtuns aus SQL-basierten Datenbanken Daten abzufragen. Wie bei R handelt es sich hier also um eine Programmiersprache mit eigenem Syntax. Um erste Abfragen zu generieren, reichen allerdings nur wenige Befehle aus. In diesem Code Chunk stellen wir die Verbindung zu einer SQLite-DBMS her (Platz 4 in der 2020 Developer Survey von Stack Overflow).
```{r exercise_sqlite, exercise = TRUE, message = FALSE}
# Laden der benötigten Packages für die Verknüpfung mit SQLite-Datenbanken
library(RSQLite)
library(DBI)
# Initialisierung eines temporären Ordners
tmpfile <- tempfile(fileext = "sqlite") # Identifizierung der SQLite-DB über das Suffix "sqlite"
download.file("https://correlaid.github.io/lernplattform/daten/plastics.sqlite", tmpfile) # Herunterladen der temporären Datei
con <- dbConnect(RSQLite::SQLite(), tmpfile) # Aufbau der Verbindung
```
Die Live-Verknüpfung selbst funktioniert wie erklärt **für jede Datenbank anders** - dieser Code kann also nicht einfach übertragen werden. Für einen Großteil der verschiedenen DBMS und zugehörige Importmöglichkeiten gibt es jedoch von RStudio eine praktische [Übersicht](https://db.rstudio.com/tooling/pro-drivers/){target="_blank"}. Ist Euer Analysetool mit der Datenbank verknüft, könnt Ihr in der zugehörigen Abfragesprache **Abfragen** generieren. Schauen wir uns dazu zunächst an, wie das ERM (Entity-Relationship-Modell, zu dt. Datenbankschema) der Datenbank aussieht:
{#id .class width=50% height=50%}
```{r quiz_dbdiagramm}
quiz(
caption = "Was fällt Euch auf?",
question_numeric(
"Wie viele Tabellen enthält das Datenbankschema?",
answer(3, correct = TRUE),
correct = "Richtig! Bei den Tabellen Audit Plastic und Community handelt es sich um Entitäten, also identifizierbare Objekte, während in der Tabelle Countries lediglich eine Beziehung zwischen den beiden Entitätstabellen hergestellt wird. Alle Tabellen haben Attribute (Eigenschaften).",
incorrect = "Leider falsch. Es gibt drei Tabellen. Bei den Tabellen Audit Plastic und Community handelt es sich um Entitäten, also identifizierbare Objekte, während in der Tabelle Countries lediglich eine Beziehung zwischen den beiden Entitätstabellen hergestellt wird. Alle Tabellen haben Attribute (Eigenschaften).",
allow_retry = TRUE,
try_again_button = "Nochmal versuchen"
),
question(
"Anhand welchen Attributs werden die Tabellen verknüpft?",
answer("year"),
answer("plastics"),
answer("countrycode", correct = TRUE),
correct = "Richtig! Das erkennt man daran, dass dieses Attribut in allen drei Tabellen auftaucht.",
incorrect = "Leider falsch. Das erkennt man an dem Attribut countrycode, das in allen drei Tabellen auftaucht." ,
allow_retry = TRUE,
try_again_button = "Nochmal versuchen"
)
)
```
In einer **idealen Welt** würde so auch unser Datensatz aussehen - aufgeteilt nach Entitäten (identifizierbare Objekte) und ihren jeweiligen Attributen (Eigenschaften) in drei klar benannten Tabellen, in der jede Zeile für eine Beobachtung steht. In der realen Welt passiert das allerdings selten. So ist es eben. Wichtig ist, dass Ihr auch mit solchen Daten arbeiten könnt, aber versteht, dass Eure Datenbanken idealerweise dieser Grundlogik folgen.
Für **SQL-basierte Datenbanken** werden Abfragen nun in der zugehörigen Abfragesprache SQL erstellt. Mit `dbListTables(Verbindung)` könnt Ihr Euch die Tabelle, die das DBMS enthält **anzeigen** lassen. Mit `dbReadTable(Verbindung, Tabelle)` könnt Ihr die gewünschte **Gesamttabelle laden** und als Objekt in R **speichern**. Mit `dbGetQuery(Verbindung, "SQL Befehl")` könnt Ihr die Tabelle filtern und so **Teilmengen des Datensatzes laden** (und ggf. in R als Objekt speichern).
```{r exercise_queries, exercise = TRUE}
# Abfrage der Tabellen
dbListTables(con)
# Speicherung der Tabelle "Audit Plastic" als Objekt
audit_plastic <- dbReadTable(con, "audits")
# Laden eines gefilterten Datensatzes (Land = Benin)
dbGetQuery(con, "SELECT *
FROM audits
WHERE countrycode = 'BEN'") # Achtung: Während wir R Ist-gleich-Vergleiche mit "==" initialisieren, benutzt man in SQL nur ein "="
# Laden eines gefilterten Datensatzes (Hersteller = Nice And Lovely)
dbGetQuery(con, "SELECT *
FROM audits
WHERE parent_company = 'Nice And Lovely'")
```
Die wichtigsten **Befehle zur Datenabfrage in SQL** sind:
- `SELECT`: **Auswahl** aller **Spalten** (mit *) oder definierter Spalten
- `FROM`: **Auswahl** eines **Datensatzes**
- `WHERE`: **Filtern des Datensatzes** auf Basis von Kriterien
Versucht hier die Datentabelle "events" für Australien zu filtern.
```{r exercise_sql, exercise = TRUE}
# Euer Code hier
```
```{r exercise_sql-solution}
# Laden eines gefilterten Datensatzes (Land = AUS)
dbGetQuery(con, "SELECT *
FROM events
WHERE countrycode = 'AUS'")
```
```{r exercise_sql-check}
grade_this_code()
```
Im letzten Schritt **schließen** wir **immer** die **Verbindung** zu der Datenbank mit dem Befehl `dbDisconnect()`.
```{r dbclose_exercise, exercise = TRUE}
# Schließen der DB-Verbindung
dbDisconnect(con)
```
SQLite-Datenbanken benötigen in der Regel **kein Passwort** (und somit auch keinen Code für die Authentifizierung). Falls das für Euch notwendig ist, findet Ihr in der Dokumentation von RStudio in den Kapiteln [Database](https://docs.rstudio.com/connect/1.7.8/admin/database-provider.html){target="_blank"} und [Authentication](https://docs.rstudio.com/connect/1.7.8/admin/authentication.html){target="_blank"} Hilfestellungen.
### **Zusätzliche Ressourcen**
Ihr wollt mehr SQL lernen? Dataquest bietet den Kurs [SQL Fundamentals](https://app.dataquest.io/course/sql-fundamentals){target="_blank"} und [Intermediate SQL in R](https://app.dataquest.io/course/sql-intermediate-r){target="_blank"} auf DataQuest (engl.)