Databases with R

by Kirill Müller

---

🗓️ October 24, 2024 ⏰ 09:00 - 17:00

---

Description

As a data professional, you likely have to deal with databases that are larger than your available RAM. Downloading the data requires patience, applying traditional workflows is frustrating. This workshop will teach you to work with your (large) data:

• if it resides in a traditional database, effortlessly

• from local storage, using DuckDB, a modern database engine tailored to data analysis

The workshop will introduce basic database concepts and move on with practical work with traditional databases and DuckDB. You are encouraged to bring your own data(base) to immediately apply what you have learned during the workshop. Among others, the workshop showcases the DBI, dbplyr, duckdb, duckplyr, and dm packages.

Audience

This course is for you if you:

• have worked with the dplyr package.

• have just read or heard about databases and are ready to get your hands dirty.

• performed basic operations on a database, and you would like to deepen your knowledge.

• have heard about DuckDB and want to know what makes it unique and how to leverage it in your daily workflow.

Prework

Laptop

• We strongly recommend you to bring a laptop where you have permission to install software
• If this is not possible, a cloud environment will be made available with material and software already installed

R and RStudio IDE

• Follow installation instructions here
• If you have R installed, make sure you have at least R version 4.1.0

R Packages Installation

Open RStudio and install the required R Packages:

# Alternative: use pak::pak(...), see https://pak.r-lib.org/
install.packages(c(
  "tidyverse",
  "devtools",
  "duckplyr",
  "RMariaDB",
  "adbi",
  "dm",
  "pixarfilms",
  "nycflights13",
  "config",
  "rstudioapi",
  "progress",
  "DiagrammeR",
  "DiagrammeRsvg",
  "arrow",
  "odbc",
  "adbi",
  "parquetize"
))

Database setup

We will be using DuckDB for demonstration purposes, with selected exercises targeting a publicly accessible database server.

Test your setup

Run the following lines of code:

library(DBI)
library(duckdb)
duckdb_con <- dbConnect(duckdb())

dbExecute(duckdb_con, "INSTALL httpfs")
dbExecute(duckdb_con, "LOAD httpfs")
dbExecute(duckdb_con, "INSTALL json")
dbExecute(duckdb_con, "LOAD json")

dbDisconnect(duckdb_con)

Data

We invite you to bring your own data and/or databases to experiment with techniques during the last session on your own data.

We will provide a backup for you in case you don't have any.

Schedule

Time	Activity
09:00 - 10:30	Talking to the database
10:30 - 11:00	Coffee break
11:00 - 12:30	Working with files
12:30 - 13:30	Lunch break
13:30 - 15:00	Digging in deeper
15:00 - 15:30	Coffee break
15:30 - 17:00	Exercises - Bring your own data

Instructor

Kirill Müller has been working on the boundary between data and computer science for more than 25 years. He has been awarded several R consortium projects to improve database connectivity and performance in R; the DBI project is currently a top-level project with the R Consortium. Kirill is author and core contributor to several tidyverse packages, including duckplyr, tibble, and dplyr. He holds a Ph.D. in Civil Engineering from ETH Zurich and is a founder and partner at cynkra.

---

This work is licensed under a Creative Commons Attribution 4.0 International License. Add materials for your workshop in this folder. You can then remove this README, and rename this folder if you prefer.