• Stars
    star
    473
  • Rank 92,472 (Top 2 %)
  • Language
    R
  • License
    Other
  • Created over 7 years ago
  • Updated 27 days ago

Reviews

There are no reviews yet. Be the first to send feedback to the community and the maintainers!

Repository Details

Database (DBI) backend for dplyr

dbplyr

CRAN status R-CMD-check Codecov test coverage

Overview

dbplyr is the database backend for dplyr. It allows you to use remote database tables as if they are in-memory data frames by automatically converting dplyr code into SQL.

To learn more about why you might use dbplyr instead of writing SQL, see vignette("sql"). To learn more about the details of the SQL translation, see vignette("translation-verb") and vignette("translation-function").

Installation

# The easiest way to get dbplyr is to install the whole tidyverse:
install.packages("tidyverse")

# Alternatively, install just dbplyr:
install.packages("dbplyr")

# Or the development version from GitHub:
# install.packages("pak")
pak::pak("tidyverse/dbplyr")

Usage

dbplyr is designed to work with database tables as if they were local data frames. To demonstrate this I’ll first create an in-memory SQLite database and copy over a dataset:

library(dplyr, warn.conflicts = FALSE)

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
copy_to(con, mtcars)

Note that you don’t actually need to load dbplyr with library(dbplyr); dplyr automatically loads it for you when it sees you working with a database. Database connections are coordinated by the DBI package. Learn more at https://dbi.r-dbi.org/

Now you can retrieve a table using tbl() (see ?tbl_dbi for more details). Printing it just retrieves the first few rows:

mtcars2 <- tbl(con, "mtcars")
mtcars2
#> # Source:   table<`mtcars`> [?? x 11]
#> # Database: sqlite 3.45.0 [:memory:]
#>      mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
#>    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#>  1  21       6  160    110  3.9   2.62  16.5     0     1     4     4
#>  2  21       6  160    110  3.9   2.88  17.0     0     1     4     4
#>  3  22.8     4  108     93  3.85  2.32  18.6     1     1     4     1
#>  4  21.4     6  258    110  3.08  3.22  19.4     1     0     3     1
#>  5  18.7     8  360    175  3.15  3.44  17.0     0     0     3     2
#>  6  18.1     6  225    105  2.76  3.46  20.2     1     0     3     1
#>  7  14.3     8  360    245  3.21  3.57  15.8     0     0     3     4
#>  8  24.4     4  147.    62  3.69  3.19  20       1     0     4     2
#>  9  22.8     4  141.    95  3.92  3.15  22.9     1     0     4     2
#> 10  19.2     6  168.   123  3.92  3.44  18.3     1     0     4     4
#> # β„Ή more rows

All dplyr calls are evaluated lazily, generating SQL that is only sent to the database when you request the data.

# lazily generates query
summary <- mtcars2 %>% 
  group_by(cyl) %>% 
  summarise(mpg = mean(mpg, na.rm = TRUE)) %>% 
  arrange(desc(mpg))

# see query
summary %>% show_query()
#> <SQL>
#> SELECT `cyl`, AVG(`mpg`) AS `mpg`
#> FROM `mtcars`
#> GROUP BY `cyl`
#> ORDER BY `mpg` DESC

# execute query and retrieve results
summary %>% collect()
#> # A tibble: 3 Γ— 2
#>     cyl   mpg
#>   <dbl> <dbl>
#> 1     4  26.7
#> 2     6  19.7
#> 3     8  15.1

Code of Conduct

Please note that the dbplyr project is released with a Contributor Code of Conduct. By contributing to this project, you agree to abide by its terms.

More Repositories

1

ggplot2

An implementation of the Grammar of Graphics in R
R
6,480
star
2

dplyr

dplyr: A grammar of data manipulation
R
4,725
star
3

tidyverse

Easily install and load packages from the tidyverse
R
1,633
star
4

rvest

Simple web scraping for R
R
1,488
star
5

tidyr

Tidy Messy Data
R
1,369
star
6

purrr

A functional programming toolkit for R
R
1,254
star
7

readr

Read flat files (csv, tsv, fwf) into R
R
1,001
star
8

magrittr

Improve the readability of R code with the pipe
R
957
star
9

datascience-box

Data Science Course in a Box
JavaScript
937
star
10

reprex

Render bits of R code for sharing, e.g., on GitHub or StackOverflow.
R
735
star
11

lubridate

Make working with dates in R just that little bit easier
R
727
star
12

readxl

Read excel files (.xls and .xlsx) into R πŸ–‡
C++
726
star
13

glue

Glue strings to data in R. Small, fast, dependency free interpreted string literals.
R
705
star
14

dtplyr

Data table backend for dplyr
R
661
star
15

tibble

A modern re-imagining of the data frame
R
659
star
16

multidplyr

A dplyr backend that partitions a data frame over multiple processes
R
640
star
17

vroom

Fast reading of delimited files
C++
618
star
18

stringr

A fresh approach to string manipulation in R
R
594
star
19

forcats

🐈🐈🐈🐈: tools for working with categorical variables (factors)
R
551
star
20

haven

Read SPSS, Stata and SAS files from R
C
423
star
21

modelr

Helper functions for modelling
R
401
star
22

googlesheets4

Google Spreadsheets R API (reboot of the googlesheets package)
R
354
star
23

googledrive

Google Drive R API
R
321
star
24

style

The tidyverse style guide for R code
HTML
291
star
25

duckplyr

A drop-in replacement for dplyr, powered by DuckDB for performance.
R
236
star
26

design

Tidyverse design principles
R
217
star
27

tidyverse.org

Source of tidyverse.org
HTML
191
star
28

hms

A simple class for storing time-of-day values
R
137
star
29

nycflights13

An R data package containing all out-bound flights from NYC in 2013 + useful metdata
R
127
star
30

tidyversedashboard

Tidyverse activity dashboard
R
71
star
31

tidy-dev-day

Tidyverse developer day
R
69
star
32

tidyeval

A guide to tidy evaluation
CSS
55
star
33

dsbox

Companion R package to Data Science Course in a Box
R
49
star
34

tidytemplate

A pkgdown template for core tidyverse packages
SCSS
45
star
35

blob

A simple S3 class for representing BLOBs
R
44
star
36

funs

Collection of low-level functions for working with vctrs
R
34
star
37

code-review

33
star
38

website-analytics

Web analytics for tidyverse + r-lib sites
R
28
star
39

tidyups

21
star
40

ggplot2-docs

ggplot2 documentation. Auto-generated from ggplot2 sources by pkgdown
HTML
10
star