First we run the two course setup chunks, which check versions and connect to DuckDB:
Course setup (R): check versions and load packages
# Checks that R and each package are installed and new enough for this course.# Stops with a list of everything that needs fixing, and how to fix it.check_course_versions <-function(required, area) { problems <-character(0) to_install <-character(0) found <-character(0)for (name innames(required)) { need <- required[[name]]if (name =="R") { have <-getRversion()if (have < need) { problems <-c(problems, sprintf("R is version %s; this course needs %s or newer. Install the current R from https://cran.r-project.org/ (see https://howisonlab.github.io/datawrangling/current/rtidyverse/rstudio_installation.html), then reinstall your packages.", have, need)) } found <-c(found, paste("R", have)) } elseif (!nzchar(system.file(package = name))) { problems <-c(problems, sprintf("%s is not installed.", name)) to_install <-c(to_install, name) } else { have <-packageVersion(name)if (have < need) { problems <-c(problems, sprintf("%s is version %s; this course needs %s or newer.", name, have, need)) to_install <-c(to_install, name) } elseif (isNamespaceLoaded(name) &&getNamespaceVersion(name) != have) { problems <-c(problems, sprintf("%s %s is installed, but this R session is still using %s. Restart R.", name, have, getNamespaceVersion(name))) } found <-c(found, paste(name, have)) } }if (length(problems) >0) { fix <-"In RStudio choose Session > Restart R"if (length(to_install) >0) { fix <-paste0(fix, ", then run this in the Console:\n install.packages(c(",paste0('"', to_install, '"', collapse =", "), "))\n") }stop("Course setup (", area, ") found problems:\n - ",paste(problems, collapse ="\n - "), "\n\n", fix, "\nThen run the setup chunk(s) again.", call. =FALSE) }cat("Course setup OK (", area, "): ", paste(found, collapse =", "), "\n", sep ="")}check_course_versions(c(R ="4.5.0", # CRAN builds Mac/Windows packages for the current and previous R onlytidyverse ="2.0.0",dplyr ="1.2.0", # recode_values() and replace_values()tidyr ="1.3.0", # separate_wider_delim()gt ="1.0.0", # needed by gtExtrasgtExtras ="0.6.1"# works with ggplot2 4), "R")library(tidyverse)library(gt)library(gtExtras)
Course setup OK (R): R 4.6.1, tidyverse 2.0.0, dplyr 1.2.1, tidyr 1.3.2, gt 1.3.0, gtExtras 0.6.2
Course setup (SQL): check versions and connect to DuckDB
# needs check_course_versions() from the Course setup (R) chunkcheck_course_versions(c(DBI ="1.0.0",duckdb ="1.5.5"# duckdb(shared_home = FALSE)), "SQL")library(DBI)library(duckdb)options(duckdb.enable_rstudio_connection_pane =TRUE)# a temporary in-memory database, nothing saved to a filedrv <-duckdb(shared_home =FALSE)con <-dbConnect(drv)
Course setup OK (SQL): DBI 1.3.0, duckdb 1.5.6
Then, up until now, we’ve been creating data frames in R (typing them in with tribble, or using datasets from packages like gt), then using the dbWriteTable function to write the data frame from R into a table in DuckDB.
We can, however, directly read data files into duckdb, without loading them using R. Here we use a file about Olympic medalists, from a broader project described at https://edjnet.github.io/OlympicsGoNUTS/
First we need the file. This line of R downloads it from the course website and saves it as 2024_medalists_all.csv in the same folder as your notebook. You only need to run it once.
DuckDB has an impressive CSV sniffer that tries to figure out the correct datatypes for columns and other aspects of the CSV files, but you can obtain more control as well. Full details are here: https://duckdb.org/docs/data/csv/overview.html
Caution
While much of SQL querying is standardized, loading data into SQL databases is not, so recognize that these commands are DuckDB specific.
DuckDB even allows us to query directly from a CSV file, without actually loading this into a permanent table. DuckDB parses the CSV file and loads it into a temporary table, just for this one query.
For example, we can find all the 2024 Medalists from South Korea directly using:
In Arranging SQL transforms into a pipeline we built pipelines inside a single query, using sub-queries and CTEs. Those are thrown away once the query finishes. Sometimes we want to keep the result of a step, so that later queries (or other people) can use it.
We have already seen this briefly, on the SQL Transforms page:
CREATEORREPLACETABLE products_split ASSELECT STRING_SPLIT(product_num, '-')[1] AS product_code, STRING_SPLIT(product_num, '-')[2] AS product_num_no_code, product_nameFROM products
And then go on to query from the new products_split table.
Directed Acyclical Graphs (DAGs) like DBT
SQL transforms are used in large scale data work, usually called Data Engineering. In these situations there is a team that is transforming data within a shared store (sometimes called a Data Warehouse or Data Lake). The Data Engineers then create Data Products for analysts to reference directly. You can think of the Data Products as the end points of a transformation pipeline.
At scale, some of the transformations can be slow and even costly (if additional servers have to be spun up to execute large queries). While a WITH block is local and convenient, you definitely want to hold on to the results of a large transformation, so that others can query it. While directly creating tables using CREATE TABLE AS SELECT can store transformations, this can quickly lead to out of date transforms or version issues, or just lots of scattered tables with unclear origins.
Into this space step DAGs. DAG stands for Directed Acyclical Graph and it is a way of describing data pipelines by specifying dependencies between tables. A DAG runs outside the SQL database, and is able to optimize the steps needed to update tables.
Examples of DAGs include the tools DBT (Data Build Tool), Apache Airflow, or Dagster. They are sometimes called “Data Orchestration” tools.
While these tools are outside the scope of this course, we can get a quick feel for DBT in this example. DBT works by having a set of separate text files. Inside each file is a SELECT query. DBT runs that query and creates a table with the same name as the file. E.g.,
filename: products_split.sql
contents:
SELECT STRING_SPLIT(product_num, '-')[1] AS product_code,
STRING_SPLIT(product_num, '-')[2] AS product_num_no_code,
product_name
FROM products
When run, DBT would use the SELECT query results and create a table called products_split.
Where DBT becomes more powerful than CTEs or manual temporary tables is because DBT can have references from one file to another, signalling that there is a dependency between them.
For example,
filename: product_counts.sql
contents:
SELECT product_code, COUNT(*) AS product_count
FROM {{ ref('products_split') }}
GROUP BY product_code
The DBT special code here is {{ ref('products_split') }}. DBT is able to look at this and know that it has to figure out how to create products_split, which it does by finding the file named that then executing the code inside it to create the table needed before product_counts.sql can be executed.
In this way DBT and other DAGs allow the creation of complex pipelines … but the ordering is specified through dependencies, rather than procedurally through the order of code as with R tidyverse or Python Pandas.