/
README.Rmd
executable file
·81 lines (57 loc) · 2.81 KB
/
README.Rmd
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
---
output: github_document
---
<!-- README.md is generated from README.Rmd. Please edit that file -->
```{r, echo = FALSE}
knitr::opts_chunk$set(
collapse = TRUE,
comment = "#>",
fig.path = "README-"
)
```
# dbplyr <a href='https://dbplyr.tidyverse.org'><img src='man/figures/logo.png' align="right" height="139" /></a>
<!-- badges: start -->
[](https://cran.r-project.org/package=dbplyr)
[](https://github.com/tidyverse/dbplyr/actions/workflows/R-CMD-check.yaml)
[](https://app.codecov.io/gh/tidyverse/dbplyr?branch=main)
<!-- badges: end -->
## Overview
dbplyr is the database backend for [dplyr](https://dplyr.tidyverse.org). 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
```{r, eval = FALSE}
# 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:
```{r setup}
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:
```{r}
mtcars2 <- tbl(con, "mtcars")
mtcars2
```
All dplyr calls are evaluated lazily, generating SQL that is only sent to the database when you request the data.
```{r}
# lazily generates query
summary <- mtcars2 %>%
group_by(cyl) %>%
summarise(mpg = mean(mpg, na.rm = TRUE)) %>%
arrange(desc(mpg))
# see query
summary %>% show_query()
# execute query and retrieve results
summary %>% collect()
```
## Code of Conduct
Please note that the dbplyr project is released with a [Contributor Code of Conduct](https://dbplyr.tidyverse.org/CODE_OF_CONDUCT.html). By contributing to this project, you agree to abide by its terms.