SQLite and dplyr

SQLite with R/dplyr Introduction

This is an example using the dplyr package to access SQLite. It draws on lessons from the UW-Madison Data Carpentry Workshop. Other resources include the Data Wrangling Cheat Sheet and the Databases using dplyr ⚠️. Note that the 0.7.0 release of dplyr, and introduction of dbplyr package, dramatically changed the connections to databases. See dplyr compatibility for notes on this.

For more information on SQL in general see the Lynda.com SQL Essential Training or the W3 SQL Tutorial.

Motivation

The Databases with SQL lessons give a good motivation in general for using SQL. Here we stress a few points that will be important for Big Data projects in omic studies:

  • SQL can make working with multiple related files manageable.
  • SQL scales nicely to large projects, where you only want to examine a small portion of the data.
  • Multiple people can work off the same SQL when properly set up.

Learning Goals

The goal is to use R tools with SQL to reproduce what was done with the SQL tool embedded in Firefox, we’ll need to do the same basic data operations:

  • select subsets of the data (rows and columns)
  • group subsets of data
  • do math and other calculations
  • combine data across spreadsheets

Setup

Make sure you have the package dplyr. You also need the back dbplyr to use SQL features.

install.packages("dplyr","dbplyr")

Attach the package dplyr. The suppressPackageStartupMessages() suppresses messages.

suppressPackageStartupMessages({
  library(dplyr)
})

Download the SQLite file portal_mammals.sqlite from the Portal Project Teaching Database.

It helps to save the name for later use.

sql_file <- "../data/portal_mammals.sqlite"

Basic queries:

Connecting to SQLite database tables

Connect to the SQLite database with the command src_sqlite(). There are similar commands for other types of SQL databases.

sql_connect <- src_sqlite(sql_file)

## Error:
## ! `src_sqlite()` was deprecated in dplyr 1.0.0 and is now defunct.
## ℹ Please use `tbl()` directly with a database connection

Connect to a table in the SQLite database.

species_sql <- tbl(sql_connect, "species")

## Error:
## ! object 'sql_connect' not found

species_sql

## Error:
## ! object 'species_sql' not found

The object species_sql is not actually a data table, even though it looks like it is. Notice for instance that the table begins and ends with

## Source:   query [?? x 4]
# ... with more rows

since the number of rows is not yet known (??). Typicall we refine this object to the rows and columns we want, and then collect it from SQL to have the data “in hand” that we want.

species_all <- species_sql %>% collect

## Error:
## ! object 'species_sql' not found

species_all

## Error:
## ! object 'species_all' not found

The species table is fairly small, but if it were huge, we might only want to work with a part of it. The SQL verbs SELECT and WHERE have comparable R/dplyr verbs select() and filter(). We use the dplyr “pipes” to make this nice and clean.

Select columns

Select year, month and day only from table surveys.

tbl(sql_connect, "surveys") %>%
  select(year,month,day)

## Error:
## ! object 'sql_connect' not found

Distinct (unique) entries in a column.

Just get distinct species_id.

tbl(sql_connect, "surveys") %>%
  distinct(species_id)

## Error:
## ! object 'sql_connect' not found

or species_id and year

tbl(sql_connect, "surveys") %>%
  distinct(year,species_id)

## Error:
## ! object 'sql_connect' not found

Calculated values

Calculations in dplyr are often done with the mutate() verb. The filter() verb (explained below) removes rows with missing weight_kg values.

tbl(sql_connect, "surveys") %>%
  mutate(weight_kg = weight / 1000) %>%
  filter(!is.na(weight_kg)) %>%
  select(plot_id,species_id,weight_kg)

## Error:
## ! object 'sql_connect' not found

You can use mutate() to change column values without creating new columns. Here we change species_id to lower case.

tbl(sql_connect, "surveys") %>%
  mutate(weight_kg = weight / 1000,
         species_id = tolower(species_id)) %>%
  filter(!is.na(weight_kg)) %>%
  select(plot_id,species_id,weight_kg)

## Error:
## ! object 'sql_connect' not found

Filter rows

Filter on species_id being DM

tbl(sql_connect, "surveys") %>%
  filter(species_id == "DM")

## Error:
## ! object 'sql_connect' not found

Filter on species_id being DM and year past 2000.

tbl(sql_connect, "surveys") %>%
  filter(species_id == "DM",
         year > 2000)

## Error:
## ! object 'sql_connect' not found

Filter on species_id being one of DM, DO or DS.

tbl(sql_connect, "surveys") %>%
  filter(species_id == "DM" |
           species_id == "DO" |
           species_id == "DS")

## Error:
## ! object 'sql_connect' not found

Same result done with the %in% operator.

tbl(sql_connect, "surveys") %>%
  filter(species_id %in% c("DM","DO","DS"))

## Error:
## ! object 'sql_connect' not found

We can also add comments with # character.

tbl(sql_connect, "surveys") %>%
  filter(species_id %in% c("DM","DO","DS"), # subset of species
         year > 2000) # recent years

## Error:
## ! object 'sql_connect' not found

Order rows

One nice way to order is with the arrange() verb. I added a filter to eliminate any rows with missing species_id or sex.

tbl(sql_connect, "surveys") %>%
  filter(!is.na(species_id),
         !is.na(sex)) %>%
  arrange(species_id)

## Error:
## ! object 'sql_connect' not found

We can arrange by more than one column:

tbl(sql_connect, "surveys") %>%
  filter(!is.na(species_id),
         !is.na(sex)) %>%
  arrange(species_id,hindfoot_length,sex)

## Error:
## ! object 'sql_connect' not found

Order of Execution

SQL clauses are written in a fixed order, but R/dplyr sorts that out for us. We can put them in any order, provided the columns and rows we want to act on are available at that step.

Suppose we want to select genus and species but filter to only include the taxa labeled "Bird".

species_genus_Bird <- tbl(sql_connect, "species") %>%
  filter(taxa == "Bird") %>%
  select(genus,species)

## Error:
## ! object 'sql_connect' not found

Notice that we filter first, as once we select genus and species, we no longer have the taxa column. This behavior is somewhat different from the SQLite commands highlighted in Data Carpentry, so watch out. The following will not work:

## This does not work -- why?
tbl(sql_connect, "species") %>%
  select(genus,species) %>%
  filter(taxa == "Bird")

SQL Aggregation

Count and Group By

The SQL COUNT and GROUP BY have corresponding verbs in dplyr. The group_by() verb enables us to group by variables and do various things. What is often done is to summarize() some aspect of variables by group.

The count verb is n(), and there are other useful verbs such as row_number(). However, these will not work properly on SQL objects, but rather on tables after we collect them.

species_all %>%
  group_by(genus) %>%
  summarize(count = n()) %>%
  arrange(desc(count))

## Error:
## ! object 'species_all' not found

Having

The SQL HAVING does not have an analogous verb. However, you can get the same thing using group_by() and summarize() and filter(). Note that here we get the count of the number rows with missing species_id.

tbl(sql_connect, "surveys") %>%
  group_by(species_id) %>%
  summarize(count = n()) %>%
  filter(count > 10)

## Error:
## ! object 'sql_connect' not found

Joins and Merges

One great power of databases is to merge or join data from tables of different type. SQLite uses the JOIN verb (with possible prefix), while dplyr uses a variety of *_join. The dplyr package has a variety of ways to join data, depending on the purpose. We can only introduce the ideas here. See the dplyr cheatsheet and manual page for join for more information.

The dplyr join routines will join by column name and value.

Note: There is a merge() verb in base R. This is not what we are examining.

Let’s collect the subset of surveys examined above:

surveys_2001 <- tbl(sql_connect, "surveys") %>%
  filter(species_id %in% c("DM","DO","DS"), # subset of species
         year > 2000) %>% # recent years
  collect

## Error:
## ! object 'sql_connect' not found

dim(surveys_2001)

## Error:
## ! object 'surveys_2001' not found

To recall, we recreate species_all.

species_all <- tbl(sql_connect, "species") %>%
  collect

## Error:
## ! object 'sql_connect' not found

dim(species_all)

## Error:
## ! object 'species_all' not found

species_surveys <- full_join(species_all, surveys_2001)

## Error:
## ! object 'species_all' not found

dim(species_surveys)

## Error:
## ! object 'species_surveys' not found

Notice how the left_join and right_join are different sizes. Why is this?

dim(left_join(species_all, surveys_2001))

## Error:
## ! object 'species_all' not found

dim(right_join(species_all, surveys_2001))

## Error:
## ! object 'species_all' not found

The anti_join will return rows of species_all that have no species_id in surveys_2001. Why are these not found in surveys_2001?

anti_join(species_all, surveys_2001)

## Error:
## ! object 'species_all' not found

Advanced Topics

Other dplyr verbs for SQL Databases

There are a host of other dplyr verbs. See the references at the top of this document for help. The databases document is part of the dplyr package: type databases in the Help tab of Rstudio, or to go to the Packages tab, click on dplyr and then click on User guides, package vignettes and other documentation.

You can also use arbitrary SQL directly:

tbl(sql_connect, dbplyr::build_sql(
  "SELECT species_id, COUNT(surveys.species_id)
   FROM surveys
   GROUP BY species_id
   HAVING COUNT(surveys.species_id) > 10")) %>%
  collect

## Error:
## ! object 'sql_connect' not found

Big Data

With very large tables, say many thousands or millions of rows, it makes sense to select and filter before the collect stage, so that the collected object is manageable size and is focused on material you want. For instance, for genomic data, one typically filters to a chromosome and a megabyte region, possibly selecting a subset of columns. With SQLite, this is very fast, and ensures that you only have in hand what you want.

That is, the basic philosophy is to clean up your data, store it somewhere, place it in a SQL database, and then collect only the parts you need when you need them. The SQLite database could be in another part of your computer, in a folder in Box, or somewhere on the internet. [There may be access issues, and time delays with some configurations.]

Let’s compare the complete species table with the reduced table for Bird.

species_all <- tbl(sql_connect, "species") %>%
  collect

## Error:
## ! object 'sql_connect' not found

dim(species_all)

## Error:
## ! object 'species_all' not found

species_genus_Bird <- tbl(sql_connect, "species") %>%
  filter(taxa == "Bird") %>%
  select(genus,species) %>%
  collect

## Error:
## ! object 'sql_connect' not found

dim(species_genus_Bird)

## Error:
## ! object 'species_genus_Bird' not found

Remember, it is only at the collect step that data are collected from the SQL database. Fewer columns and rows means less space and faster subsequent computation.

Challenge: Why do you not need to save large data files locally? Think about several reasons why and why not to save large data files on your computer.

Creating SQLite Database with R/dplyr

SQL has a CREATE verb to create a database and tables within it. Here are the basic steps to create an SQLite database in R with dplyr. We illustrate with the species_all and surveys_2001 objects. Here are the commands to create the database and copy species_all and surveys_2001 in with names species and surveys, respectively.

my_sql_db <- src_sqlite("my_db.sqlite", create = TRUE)
copy_to(my_sql_db, species_all, "species", temporary = FALSE)
copy_to(my_sql_db, surveys_2001, "surveys", temporary = FALSE)

The above was not actually run. The code below is run, but has some checks to see if things exist already. Give a path to create an SQLite database. Here we check if the database exists, and if so, define my_tbls as the set of tables already in the database.

if(!file.exists("my_db.sqlite")) {
  my_sql_db <- src_sqlite("my_db.sqlite", create = TRUE)
  my_tbls <- NULL
} else {
  my_sql_db <- src_sqlite("my_db.sqlite", create = FALSE)
  my_tbls <- collect(tbl(my_sql_db, "sqlite_stat1"))$tbl
}

## Error:
## ! `src_sqlite()` was deprecated in dplyr 1.0.0 and is now defunct.
## ℹ Please use `tbl()` directly with a database connection

Now copy_to this database the species table

if(!("species" %in% my_tbls)) {
  copy_to(my_sql_db, species_all, "species", temporary = FALSE)
}

## Error:
## ! object 'my_tbls' not found

and the survey table.

if(!("surveys" %in% my_tbls)) {
  copy_to(my_sql_db, surveys_2001, "surveys", temporary = FALSE)
}

## Error:
## ! object 'my_tbls' not found

The SQLite database now has three tables.

Using the option temporary = FALSE forces the tables to be written into the SQLite database.

my_sql_db

## Error:
## ! object 'my_sql_db' not found

The sqlite_stat1 is a table to manage information about the database. You can look at it, but this one is not very interesting.

tbl(my_sql_db, "sqlite_stat1")

## Error:
## ! object 'my_sql_db' not found

We can check the size of the saved file within R, or by looking at the directory outside of R. This file can be saved somewhere for later use.

file.size("my_db.sqlite")

## [1] NA

Indexing

Indexing of SQL is a way to speed up access for large database tables. The SQL verb is INDEX. This is done in dplyr with copy_to() at the time the table is created.

if(!("surveys_index" %in% my_tbls)) {
  copy_to(my_sql_db, surveys_2001, "surveys_index", temporary = FALSE,
          indexes = list(sp="species_id",yearsex=c("year","sex")))
}

## Error:
## ! object 'my_tbls' not found

Here we see that the indexes were actually created.

tbl(my_sql_db,"sqlite_stat1")

## Error:
## ! object 'my_sql_db' not found

Dropping SQL Tables

Generally, one should take great care in deleting, removing or dropping electronic objects. Once gone, it may be gone forever. Having said that, there is an SQL DROP verb to drop a table from a database, or a whole database. One can also simply remove the *.sqlite file from a folder.

In dplyr, the drop verb is not revealed to users quite so directly, for the reasons stated above. There is a command db_drop_table() but it is pretty arcane. Here we show how to drop the surveys table from the database, but we don’t actually run it in this markdown document.

db_drop_table(my_sql_db$con,"surveys")

Structure of SQL Connection

The R/dplyr SQL verbs use “lazy evaluation”. That is, operations are only done when absolutely necessary. The SQL connect structure created by a call to src_sqlite() is basically a set of pointers into the SQL database. It does not actually contain any of the data, but instead points to where the data are and what to do to get the requested parts at the moment of collection.

sql_connect <- src_sqlite(sql_file)

## Error:
## ! `src_sqlite()` was deprecated in dplyr 1.0.0 and is now defunct.
## ℹ Please use `tbl()` directly with a database connection

species_sql <- tbl(sql_connect, "species")

## Error:
## ! object 'sql_connect' not found

str(species_sql)

## Error:
## ! object 'species_sql' not found

The object species_genus_Bird before collecting a set of instructions in machine-readable form to get the data from the SQL database. Look carefully to see reference to taxa, genus and species.

species_genus_Bird <- tbl(sql_connect, "species") %>%
  filter(taxa == "Bird") %>%
  select(genus,species)

## Error:
## ! object 'sql_connect' not found

str(species_genus_Bird)

## Error:
## ! object 'species_genus_Bird' not found
This site uses Just the Docs, a documentation theme for Jekyll.