con <- dbConnect(RSQLite::SQLite(), ":memory:")2 dbplyr
2.1 Introduction
In this chapter, we will learn to query data from a database using dplyr.
We will use the following R packages:
All the data sets used in this chapter can be found here and code can be downloaded from here or run from code/ch2-dbplyr.R in this repo.
2.2 Connect to Database
Let us connect to an in memory SQLite database using dbConnect().
We will copy the ecom data to the database so that we can use it for running dplyr statements. We read the same ecom data set used across chapters (1000 rows, 8 columns including country and purchase) from the local data/web.csv snapshot, falling back to the online copy if needed.
web_path <- if (file.exists("data/web.csv")) "data/web.csv" else "https://raw.githubusercontent.com/rsquaredacademy/datasets/master/web.csv"
ecom <- readr::read_csv(web_path, show_col_types = FALSE)
ecom <- dplyr::select(ecom, referrer, device, bouncers, n_visit, n_pages, duration, country, purchase)
dplyr::copy_to(con, ecom)2.3 Reference Data
In order to use dplyr functions, we need to reference the table in the database using tbl().
ecom2 <- dplyr::tbl(con, "ecom")
ecom2# A query: ?? x 8
# Database: sqlite 3.53.3 [:memory:]
referrer device bouncers n_visit n_pages duration country purchase
<chr> <chr> <int> <dbl> <dbl> <dbl> <chr> <int>
1 google laptop 1 10 1 693 Czech Republic 0
2 yahoo tablet 1 9 1 459 Yemen 0
3 direct laptop 1 0 1 996 Brazil 0
4 bing tablet 0 3 18 468 China 1
5 yahoo mobile 1 9 1 955 Poland 0
6 yahoo laptop 0 5 5 135 South Africa 0
7 yahoo mobile 1 10 1 75 Bangladesh 0
8 direct mobile 1 10 1 908 Indonesia 0
9 bing mobile 0 3 19 209 Netherlands 0
10 google mobile 1 6 1 208 Czech Republic 0
# ℹ more rows2.4 Query Data
We will look at some simple examples. Let us start by selecting referrer, device and duration columns from ecom2.
select(ecom2, referrer, device, duration)# A query: ?? x 3
# Database: sqlite 3.53.3 [:memory:]
referrer device duration
<chr> <chr> <dbl>
1 google laptop 693
2 yahoo tablet 459
3 direct laptop 996
4 bing tablet 468
5 yahoo mobile 955
6 yahoo laptop 135
7 yahoo mobile 75
8 direct mobile 908
9 bing mobile 209
10 google mobile 208
# ℹ more rowsWe can filter data as well. Filter all the rows from ecom2 where duration is greater than 300.
filter(ecom2, duration > 300)# A query: ?? x 8
# Database: sqlite 3.53.3 [:memory:]
referrer device bouncers n_visit n_pages duration country purchase
<chr> <chr> <int> <dbl> <dbl> <dbl> <chr> <int>
1 google laptop 1 10 1 693 Czech Republic 0
2 yahoo tablet 1 9 1 459 Yemen 0
3 direct laptop 1 0 1 996 Brazil 0
4 bing tablet 0 3 18 468 China 1
5 yahoo mobile 1 9 1 955 Poland 0
6 direct mobile 1 10 1 908 Indonesia 0
7 direct laptop 1 9 1 738 Jamaica 0
8 direct mobile 0 9 14 406 Ireland 1
9 bing laptop 1 1 1 995 United States 0
10 bing tablet 0 5 16 368 Peru 1
# ℹ more rowsTime to do some grouping and summarizing. Let us compute the average time spent on the site for different types of devices.
ecom2 %>%
group_by(device) %>%
summarise(avg_duration = mean(duration))# A query: ?? x 2
# Database: sqlite 3.53.3 [:memory:]
device avg_duration
<chr> <dbl>
1 laptop 376.
2 mobile 337.
3 tablet 354.2.5 Show Query
If you want to view the SQL query generated in the above step, use show_query() or explain().
avg_time <-
ecom2 %>%
group_by(device) %>%
summarise(avg_duration = mean(duration))
dplyr::show_query(avg_time)
## <SQL>
## SELECT `device`, AVG(`duration`) AS `avg_duration`
## FROM `ecom`
## GROUP BY `device`
dplyr::explain(avg_time)
## <SQL>
## SELECT `device`, AVG(`duration`) AS `avg_duration`
## FROM `ecom`
## GROUP BY `device`
##
## <PLAN>
## id parent notused detail
## 1 6 0 115 SCAN ecom
## 2 8 0 0 USE TEMP B-TREE FOR GROUP BY2.6 Collect Data
Now, some interesting facts. When working with databases, dplyr never pulls data into R unless you explicitly ask for it. In the previous example, dplyr will not do anything until you ask for the avg_time data. It generates the SQL and only pulls down a few rows when you try to print avg_time. So how do we pull all the data and store it for further analysis? collect() will pull all the data and store it in a tibble and you can use it for any further analysis.
dplyr::collect(avg_time)# A tibble: 3 × 2
device avg_duration
<chr> <dbl>
1 laptop 376.
2 mobile 337.
3 tablet 354.It is good practice to close the connection when you are done:
dbDisconnect(con)