05_2_datenimport-exkurs-sql.Rmd

---
title: "Datenabfragen in SQL (Exkurs)"
author: CorrelAid e.V.
date: "`r Sys.Date()`"
authors:
  - Nina Hauser
  - Zoé Wolter
  - Jonas Lorenz
output: 
  html_document:
    toc: true
    toc_depth: 2
    toc_float: true
    theme: flatly
    css: www/style.css
    includes:
      after_body: ./www/favicon.html
    language: de
runtime: shiny_prerendered
---

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = FALSE, message = FALSE, warning = FALSE)
library(learnr)
library(gradethis)
library(RSQLite)
 
source("R/setup/gradethis-setup.R")
source("R/setup/tutorial-setup.R")
# Read app parameters
params <- yaml::yaml.load_file("www/app_parameters.yml")

# Benötigte Daten laden
source("R/setup/functions.R")
con <- get_connection()
```

```{r results='asis'}
cat(get_license_note(rmarkdown::metadata$title, rmarkdown::metadata$authors))
```

# **Was ist SQL?**

**SQL (Structured Query Language)** ist eine Programmiersprache für die **Bearbeitung von Daten und relationalen Datenbanksystemen**. Diese Sprache wird hauptsächlich für die Kommunikation mit Datenbanken verwendet, um die darin enthaltenen Daten zu verwalten. Es ermöglicht insbesondere die Speicherung, Bearbeitung und Abfrage dieser Daten. Wir können damit allerdings auch Abfragen durchführen, Daten aktualisieren und/oder reorganisieren, das Schema und die Struktur eines Datenbanksystems erstellen und ändern, sowie den Zugriff auf die Daten kontrollieren. Daten, die uns in unserem Alltag als Datenanalyst:innen begegnen, sind nämlich oft in **Datenbanken** gespeichert. Das sind **logisch modellierte, strukturierte Datenspeicher**, mit denen wir durch **Datenbankmanagementsysteme (kurz DBMS)**, also Softwaretools, interagieren können. 

# **Datenbanken mit R**
Allerdings funktioniert diese Interaktion 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 aufgrund der Größe der Datensätze, nicht praktikabel sein, Daten manuell zu exportieren. Idealerweise sind Eure Daten nämlich **live** mit unseren 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>

![*2020 Developer Survey, Stack Overflow, n = 65,000*](https://github.com/CorrelAid/rlernen_umwelt/blob/main/abbildungen/05_datenimport/popular_dbms.png?raw=true){#id .class width=50% height=50%}
</center>

## **Schritt 1: Datenabfrage und Verbindungsaufbau**

Wie wir sehen, kommt man an einer bestimmten Technologie beim Thema Datenbanken also nicht vorbei: **SQL (Structured Query Language, zu dt. Strukturierte Abfragesprache)**. Wie bereits erwähnt, können wir mithilfe dieser Sprache Daten aus SQL-basierten Datenbanken abfragen. 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). Hierfür benötigen wir die Packages **`RSGLite`** und **`DBI`**, mit deren Hilfe wir die temporäre Datei zunächst herunterladen können, um dann eine Verbindug herzustellen:

```{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
```

## **Schritt 2: Verknüpfung mit RStudio**

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 unser Analysetool mit der Datenbank verknüpft, können wir 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:

![*ERM der Plastics-SQLite-Datenbank*](https://github.com/CorrelAid/rlernen_umwelt/blob/main/abbildungen/05_datenimport/erm.png?raw=true){#id .class width=50% height=50%}

<br>

Was fällt Euch auf?

```{r quiz_dbdiagramm}
quiz(
  caption = " ",
  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 verschiedene 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 verschiedene 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, werfe noch einmal einen Blick auf die verschiedene Tabellen: Welches Attribut kommt in allen drei Tabellen vor?" ,
    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. Wichtig ist, dass wir auch mit solchen Daten arbeiten können und verstehen, dass unsere Datenbanken idealerweise dieser Grundlogik folgen.

## **Schritt 3: Erste Abfragen mit SQL**

Für **SQL-basierte Datenbanken** werden Abfragen nun in der zugehörigen Abfragesprache SQL erstellt. Mit `dbListTables(Verbindung)` können wir uns die Tabelle, die das DBMS enthält **anzeigen** lassen. Mit `dbReadTable(Verbindung, Tabelle)` können wir 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).

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

```{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 in 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'")
```

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

## **Schritt 4: Verbindung schließen**

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**
- [Database Queries With R](https://solutions.posit.co/connections/db/getting-started/database-queries/){target="_blank"}
- 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.)

<br>
<a class="btn btn-primary btn-back-to-main" href=`r params$links$end_session`>Session beenden</a>