SQL-Datenbanken abfragen und Versionierung mit git

Unit 6

Ziele für heute

  1. eine Verbindung zu einer Datenbank herstellen und SQL-Abfragen mit {DBI} ausführen
  2. den grundlegenden Workflow von Git in RStudio erklären
  3. die wichtigsten Konzepte des Kurses zusammenfassen

Praktikum 06a

CO2-Emissionen

30:00

SQL Database

SQL Database

  • DBI + RPostgres \(\rightarrow\) Verbindung mit SQL Datenbank
  • dbplyr \(\rightarrow\) dplyr-Verben mit Datenbank
library(DBI) # dbConnect()
library(RPostgres) # Postgres()

SQL Database

  • DBI + RPostgres \(\rightarrow\) Verbindung mit SQL Datenbank
  • dbplyr \(\rightarrow\) dplyr-Verben mit Datenbank
library(DBI) # dbConnect()
library(RPostgres) # Postgres()

con <- dbConnect(
  Postgres(),
  sslmode = "allow",
  host = "host-name",
  port = port-number,
  dbname = "db-name",
  user = "username",
  password = "my_password"
)

⛔ Niemals Passwort im Skript speichern!

SQL Database

  • DBI + RPostgres \(\rightarrow\) Verbindung mit SQL Datenbank
  • dbplyr \(\rightarrow\) dplyr-Verben mit Datenbank
library(DBI) # dbConnect()
library(RPostgres) # Postgres()

con <- dbConnect(
  Postgres(),
  sslmode = "allow",
  host = "host-name",
  port = port-number,
  dbname = "db-name",
  user = "username",
  password = rstudioapi::askForPassword("Datenbank Passwort")
)

config.yml

default:
  DWH_var:
    host: "host-name"
    user: "username"
    password: "DWH_pass" 
    port: port-number
    dbname: "db-name"
library(DBI) # dbConnect()
library(RPostgres) # Postgres()

con <- dbConnect(
  Postgres(),
  sslmode = "allow",
  host = config::get("DWH_var")$host,
  dbname = config::get("DWH_var")$dbname,
  user = config::get("DWH_var")$user,
  password = config::get("DWH_var")$password,
  port = config::get("DWH_var")$port
)

config.yml

default:
  DWH_var:
    host: "host-name1"
    user: "username"
    password: "DWH_pass" 
    port: port-number1
    dbname: "db-name1"
  DWH_hrm:
    host: "host-name"
    user: "username"
    password: "DWH_pass" 
    port: port-number2
    dbname: "db-name2"
    
andere_DB:
  DB_fin:
    host: "host-name3"
    user: "username"
    password: "DB_pass" 
    port: port-number3
    dbname: "db-name3"

SQL Daten Bearbeiten

con


dbListObjects(con)

SQL Daten Bearbeiten

dbListObjects(con, Id(schema = "schema_name"))

SQL Daten Bearbeiten

library(tidyverse)
df_db <- tbl(con, Id(schema = "schema_name", table = "table_name"))
class(df_db)
glimpse(df_db, width = 20)

SQL Daten Bearbeiten

df_db_small <- df_db |>
  select(var1, district_k, var2) |>
  mutate(
    var1 = as.character(var1), # dbplyr cannot translate factor() to sql
    district_k = as.character(district_k),
    district_k = case_when(
      district_k == "1301" ~ "Arlesheim",
      district_k == "1302" ~ "Laufen",
      district_k == "1303" ~ "Liestal",
      district_k == "1304" ~ "Sissach",
      .default = "Waldenburg"
    )
  ) |>
  summarise(var3 = sum(var2), .by = c(var1, district_k))

SQL Daten Bearbeiten

df_db_small |>
  ggplot(
    aes(
      x = var1,
      y = var3,
      fill = fct_reorder(
        district_k,
        var3
      )
    )
  ) +
  geom_col() +
  labs(
    x = "",
    y = "Total",
    fill = "Bezirk"
  ) +
  scale_y_continuous(
    label = scales::label_number(
      prefix = "CHF ",
      big.mark = "'"
    )
  ) +
  theme_minimal() +
  theme(
    legend.position = c(0.2, 0.8)
  )

SQL Query: show_query()

df_db_small |>
  show_query()

SQL Queries in Quarto Dateien

```{sql}
#| connection: con
#| output.var: "db_sql_small"
#| code-line-numbers: "1-4|"

SELECT "var1", "district_k", SUM("var2") AS "var3"
FROM (
  SELECT
    "var1",
    CASE
WHEN ("district_k" = '1301') THEN 'Arlesheim'
WHEN ("district_k" = '1302') THEN 'Laufen'
WHEN ("district_k" = '1303') THEN 'Liestal'
WHEN ("district_k" = '1304') THEN 'Sissach'
ELSE 'Waldenburg'
END AS "district_k",
    ""
  FROM (
    SELECT
      CAST("var1" AS TEXT) AS "var1",
      CAST("district_k" AS TEXT) AS "district_k",
      ""
    FROM "schema_name"."table_name"
  ) "q01"
) "q02"
GROUP BY "var1", "district_k"
```

Mit Resultierenden Daten Weiter Arbeiten

Code 
db_sql_small |>
  ggplot(
    aes(
      x = var1,
      y = var3,
      fill = fct_reorder(district_k, var3)
    )
  ) +
  geom_col() +
  labs(
    x = "",
    y = "Total",
    fill = "Bezirk"
  ) +
  scale_y_continuous(
    label = scales::label_number(prefix = "CHF ", big.mark = "'")
  ) +
  theme_minimal()

Resultierende Daten aus Datenbank Holen

class(df_db_small)

👉 Daten in DB (lazy loading)


df <- df_db_small |>
  collect() 

class(df)

👉 Daten im Dataframe!

Versionierung und Kollaboration: git

Versionierung

Versionierung

Warum Versionierung

Kollaboration mit sich Selbst


add, commit

github.com

RStudio

Kollaboration mit anderen

Warum Git und GitLab?

Git und GitLab

Neues Projekt

Existierendes Projekt

git in RStudio

Step-by-Step 1, step-by-step 2

Praktikum 06b: git

Quarto-Projekt mit git

20:00

Fragen?

Danke! 🌕

Slides created via revealjs and Quarto.

Access slides as PDF.

All material is licensed under Creative Commons Attribution Share Alike 4.0 International.

Git-Begriffe

  • repository dein Projektordner
  • commit ein Schnappschuss deines Repos
  • push Commits an den remote senden
  • pull Commits vom remote erhalten
  • clone das Repository zum ersten Mal aus dem remote abrufen
  • remote einen Computer, auf dem sich das Repository befindet