library(duckdb) # For working with DuckDB databases
library(DBI)
library(tidycensus) # For downloading Census microdata
# Download Census microdata
california_microdata <- get_pums(
state = "CA", year = 2024,
variables = c(
"SPORDER", "SERIALNO", "DIVISION", "REGION", "STATE", "NP", "SEX", "SCHL"
)
)
# Save the microdata to a local database file
db_conn <- dbConnect(duckdb("local-database.db"))
dbWriteTable(
conn = db_conn,
name = "california_microdata",
value = california_microdata
)
dbDisconnect(db_conn)
# Delete the Census microdata file
rm(california_microdata)While SQL is an almost universal tool for data scientists, many folks who analyze data get by without ever having to use it. Which is nice for them. SQL is a handy tool but it’s often less intuitive to use compared to data manipulation tools in a language like R. For many years now, SQL has been referred to as “the assembly language of databases.” The implication is that SQL as a language often feels closer to low-level computer instructions (like MOV CL, 2H;) than an expressive high-level language (like read_data('data.csv')).
Some recent dialects of SQL are much friendlier than the SQL dialects of even a decade ago. DuckDB in particular has so many features that make SQL easier to read and write. And Databricks introduced one of my favorite new SQL features, which is a beloved feature from R and Python called “pipe syntax.”
Even with all of these improvements, I still find it more pleasant to just write R code or sometimes Python code. Thanksfully, as I mentioned in another recent blog post, these days you can easily get R or Python to translate code into SQL and interact with a database for you. That works really nicely when I’m partnering up with statisticians or subject matter experts who are comfortable in R but aren’t really comfortable with SQL. These days you can do so much work with databases without ever having to write SQL.
So an obvious question you might have as a statistician is why would you ever write SQL instead of just using R or Python? One obvious reason is that writing SQL can avoid introducing additional software dependencies to your work. That might be a necessity in a cloud environment where running Python or R requires running a server or spinning up a Docker image.
The reason I want to highlight here is that writing SQL lets you modify your database tables in ways that you can’t in R or Python. The added flexibility of SQL can be very helpful for working with large datasets.
Suppose we have a large dataset that we don’t want to read into memory. As an example, let’s consider public use microdata from the American Community Survey (ACS). First, we’ll download a dataset of almost 2 million records and save it to a database.
To work with the database in R, we can create a DuckDB connection.
library(dplyr)
db_conn <- dbConnect(duckdb("local-database.db"))
microdata_tbl <- tbl(db_conn, "california_microdata")Let’s suppose we want to recode the educational attainment variable. Instead of detailed categories, we want to collapse it to “Less than college” or “College or more.” We’ll write usual R code, like the following.
# Define list of values to recode
education_categories <- list(
'Less than college' = c(
"bb", "01", "02", "03", "04", "05", "06",
"07", "08", "09", "10", "11", "12", "13",
"14", "15", "16", "17", "18", "19", "20"
),
'College or more' = c("21", "22", "23", "24")
)
# Derive the new variable
updated_microdata_tbl <- microdata_tbl |> mutate(
EDUCATION = case_when(
SCHL %in% !!education_categories[['Less than college']] ~ "Less than college",
SCHL %in% !!education_categories[['College or more']] ~ "College or more",
.default = NA
)
)The result looks like a standard data frame with the new EDUCATION variable.
print(updated_microdata_tbl)# Source: SQL [?? x 11]
# Database: DuckDB 1.5.2 [benja@Windows 10 x64:R 4.6.1/C:\Users\benja\OneDrive\Documents\bschneider_site\posts\sometimes-you-should-just-write-sql\local-database.db]
SERIALNO SPORDER WGTP PWGTP NP DIVISION REGION STATE SCHL SEX
<chr> <dbl> <dbl> <dbl> <dbl> <chr> <chr> <chr> <chr> <chr>
1 2020GQ0000016 1 0 3 1 9 4 06 09 2
2 2020GQ0000023 1 0 7 1 9 4 06 16 1
3 2020GQ0000039 1 0 16 1 9 4 06 16 1
4 2020GQ0000044 1 0 24 1 9 4 06 11 1
5 2020GQ0000070 1 0 5 1 9 4 06 15 1
6 2020GQ0000072 1 0 1 1 9 4 06 13 2
7 2020GQ0000083 1 0 8 1 9 4 06 01 1
8 2020GQ0000088 1 0 9 1 9 4 06 17 1
9 2020GQ0000105 1 0 6 1 9 4 06 19 1
10 2020GQ0000107 1 0 14 1 9 4 06 19 1
# ℹ more rows
# ℹ 1 more variable: EDUCATION <chr>It’s important to know though that updated_microdata_tbl is not a data frame, but a ‘lazy query’. Fundamentally it’s just a SQL query that only gets executed when needed. R translated our R code from earlier into this SQL query.
updated_microdata_tbl |> show_query()<SQL>
SELECT
california_microdata.*,
CASE
WHEN (SCHL IN ('bb', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20')) THEN 'Less than college'
WHEN (SCHL IN ('21', '22', '23', '24')) THEN 'College or more'
ELSE NULL
END AS EDUCATION
FROM california_microdataSuppose we want to save our updated data back to the database. With ‘dplyr’, the way to do this is to call the compute() function, which saves the entire query as a table. That’s unfortunately a very expensive operation. It has to execute the query and then copy and write all the variables and records in the data.
updated_microdata_tbl |> compute(name = "updated_microdata", temporary = FALSE)The code above is essentially a shortcut for the following SQL code.
CREATE TABLE updated_microdata AS (
SELECT
california_microdata.*,
CASE
WHEN (SCHL IN (
'bb', '01', '02', '03', '04', '05', '06',
'07', '08', '09', '10', '11', '12', '13',
'14', '15', '16', '17', '18', '19', '20')) THEN 'Less than college'
WHEN (SCHL IN ('21', '22', '23', '24')) THEN 'College or more'
ELSE NULL
END AS EDUCATION
FROM california_microdata
)A much cheaper operation is to simply add a new column to the existing data table. But to do that, we need to use SQL. Here’s what the SQL for that looks like.
ALTER TABLE california_microdata
ADD COLUMN IF NOT EXISTS EDUCATION VARCHAR;
UPDATE california_microdata
SET EDUCATION =
CASE
WHEN SCHL IN (
'bb', '01', '02', '03', '04', '05', '06',
'07', '08', '09', '10', '11', '12', '13',
'14', '15', '16', '17', '18', '19', '20'
) THEN 'Less than college'
WHEN SCHL IN ('21', '22', '23', '24') THEN 'College or more'
ELSE NULL
END;We can execute this SQL code from R.
db_conn |> dbExecute(
"ALTER TABLE california_microdata
ADD COLUMN IF NOT EXISTS EDUCATION VARCHAR;
UPDATE california_microdata
SET EDUCATION =
CASE
WHEN SCHL IN (
'bb', '01', '02', '03', '04', '05', '06',
'07', '08', '09', '10', '11', '12', '13',
'14', '15', '16', '17', '18', '19', '20'
) THEN 'Less than college'
WHEN SCHL IN ('21', '22', '23', '24') THEN 'College or more'
ELSE NULL
END;
"
)Now we can see that the EDUCATION variable actually exists in the database.
db_conn |> tbl("california_microdata") |> count(EDUCATION)# Source: SQL [?? x 2]
# Database: DuckDB 1.5.2 [benja@Windows 10 x64:R 4.6.1/C:\Users\benja\OneDrive\Documents\bschneider_site\posts\sometimes-you-should-just-write-sql\local-database.db]
EDUCATION n
<chr> <dbl>
1 Less than college 1317631
2 College or more 549432At the cost of writing some SQL, we were able to actually update the data in the database and avoid copying an entire table.