dbplyr v1.4.0

0

Monthly downloads

0th

Percentile

A 'dplyr' Back End for Databases

A 'dplyr' back end for databases that allows you to work with remote database tables as if they are in-memory data frames. Basic features works with any database that has a 'DBI' back end; more advanced features require 'SQL' translation to be provided by the package author.

Readme

dbplyr

Travis build
status CRAN
status 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 the development version from GitHub:
# install.packages("devtools")
devtools::install_github("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 http://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.25.3 [: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
#> # … with 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 x 2
#>     cyl   mpg
#>   <dbl> <dbl>
#> 1     4  26.7
#> 2     6  19.7
#> 3     8  15.1

Functions in dbplyr

Name Description
lahman Cache and retrieve an src_sqlite of the Lahman baseball database.
tbl_sql Create an SQL tbl (abstract)
testing Infrastructure for testing dplyr
translate_sql Translate an expression to sql.
win_over Generate SQL expression for window functions
db_compute More db generics
dbplyr-package dbplyr: A 'dplyr' Back End for Databases
ident Flag a character vector as SQL identifiers
named_commas Provides comma-separated string out ot the parameters
in_schema Refer to a table in a schema
simulate_dbi Simulate database connections
sql SQL escaping.
src_sql Create a "sql src" object
nycflights13 Database versions of the nycflights13 data
tbl_lazy Create a local lazy tibble
window_order Override window order and frame
arrange.tbl_lazy Arrange rows by variables in a remote database table
collapse.tbl_sql Force computation of query
copy_to.src_sql Copy a local data frame to a DBI backend.
memdb_frame Create a database table in temporary in-memory database.
lazy_ops Lazy operations
sql_substr Create an sql translator
src_dbi dplyr backend for any DBI-compatible database
build_sql Build a SQL string.
sql_expr Generate SQL from R expressions
sql_quote Helper function for quoting sql elements.
do.tbl_sql Perform arbitrary computation on remote backend
escape Escape/quote a string.
partial_eval Partially evaluate an expression.
remote_name Metadata about a remote table
join_query Build and render SQL from a sequence of lazy operations
sql_escape_logical More SQL generics
join.tbl_sql Join sql tbls.
No Results!

Vignettes of dbplyr

Name
notes/_mysql-setup.Rmd
notes/_postgres-setup.Rmd
dbplyr.Rmd
new-backend.Rmd
reprex.Rmd
sql.Rmd
translation-function.Rmd
translation-verb.Rmd
windows.graffle
windows.png
No Results!

Last month downloads

Details

Type Package
License MIT + file LICENSE
URL https://github.com/tidyverse/dbplyr
BugReports https://github.com/tidyverse/dbplyr/issues
VignetteBuilder knitr
Encoding UTF-8
LazyData yes
RoxygenNote 6.1.1
Collate 'utils.R' 'sql.R' 'escape.R' 'translate-sql-quantile.R' 'translate-sql-string.R' 'translate-sql-paste.R' 'translate-sql-helpers.R' 'translate-sql-window.R' 'translate-sql-conditional.R' 'backend-.R' 'backend-access.R' 'backend-hive.R' 'backend-impala.R' 'backend-mssql.R' 'backend-mysql.R' 'backend-odbc.R' 'backend-oracle.R' 'backend-postgres.R' 'backend-sqlite.R' 'backend-teradata.R' 'build-sql.R' 'data-cache.R' 'data-lahman.R' 'data-nycflights13.R' 'dbplyr.R' 'explain.R' 'ident.R' 'lazy-ops.R' 'memdb.R' 'partial-eval.R' 'query-join.R' 'query-select.R' 'query-semi-join.R' 'query-set-op.R' 'query.R' 'remote.R' 'schema.R' 'simulate.R' 'sql-build.R' 'sql-expr.R' 'src-sql.R' 'src_dbi.R' 'tbl-lazy.R' 'tbl-sql.R' 'test-frame.R' 'testthat.R' 'translate-sql-clause.R' 'translate-sql.R' 'utils-format.R' 'verb-arrange.R' 'verb-compute.R' 'verb-copy-to.R' 'verb-distinct.R' 'verb-do-query.R' 'verb-do.R' 'verb-filter.R' 'verb-group_by.R' 'verb-head.R' 'verb-joins.R' 'verb-mutate.R' 'verb-pull.R' 'verb-select.R' 'verb-set-ops.R' 'verb-summarise.R' 'verb-window.R' 'zzz.R'
NeedsCompilation no
Packaged 2019-04-23 11:59:54 UTC; hadley
Repository CRAN
Date/Publication 2019-04-23 22:40:11 UTC

Include our badge in your README

[![Rdoc](http://www.rdocumentation.org/badges/version/dbplyr)](http://www.rdocumentation.org/packages/dbplyr)