You are currently browsing the monthly archive for June 2017.
I’m pleased to announce the release of the dbplyr package, which now contains all dplyr code related to connecting to databases. This shouldn’t affect you-as-a-user much, but it makes dplyr simpler, and makes it easier to release improvements just for database related code.
You can install the latest version of dbplyr with:
install.packages("dbplyr")DBI and dplyr alignment
The biggest change in this release is that dplyr/dbplyr works much more directly with DBI database connections. This makes it much easier to switch between low-level queries written in SQL, and high-level data manipulation functions written with dplyr verbs.
To connect to a database, first use DBI::dbConnect() to create a database connection. For example, the following code connects to a temporary, in-memory, SQLite database, then uses DBI to copy over some data.
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
DBI::dbWriteTable(con, "iris", iris)
#> [1] TRUE
DBI::dbWriteTable(con, "mtcars", mtcars)
#> [1] TRUEWith this connection in hand, you can execute hand-written SQL queries:
DBI::dbGetQuery(con, "SELECT count() FROM iris")
#> count()
#> 1 150Or you can let dplyr generate the SQL for you:
iris2 <- tbl(con, "iris")
species_mean <- iris2 %>%
group_by(Species) %>%
summarise_all(mean)
species_mean %>% show_query()
#> <SQL>
#> SELECT `Species`, AVG(`Sepal.Length`) AS `Sepal.Length`, AVG(`Sepal.Width`) AS `Sepal.Width`, AVG(`Petal.Length`) AS `Petal.Length`, AVG(`Petal.Width`) AS `Petal.Width`
#> FROM `iris`
#> GROUP BY `Species`
species_mean
#> # Source: lazy query [?? x 5]
#> # Database: sqlite 3.11.1 [:memory:]
#> Species Sepal.Length Sepal.Width Petal.Length Petal.Width
#> <chr> <dbl> <dbl> <dbl> <dbl>
#> 1 setosa 5.006 3.428 1.462 0.246
#> 2 versicolor 5.936 2.770 4.260 1.326
#> 3 virginica 6.588 2.974 5.552 2.026This alignment is made possible thanks to the hard work of Kirill Muller who has been working to make DBI backends more consistent, comprehensive, and easier to use. This work has been funded by the R Consortium and will continue this year with improvements to backends for the two major open source databases MySQL/MariaDB and PostgreSQL.
(You can continue to the old style src_mysql(), src_postgres(), and src_sqlite() functions, which still live in dplyr, but I recommend that you switch to the new style for new code)
SQL translation
We’ve also worked to improve the translation of R code to SQL. Thanks to @hhoeflin, dbplyr now has a basic SQL optimiser that considerably reduces the number of subqueries needed in many expressions. For example, the following code used to generate three subqueries, but now generates idiomatic SQL:
con %>%
tbl("mtcars") %>%
filter(cyl > 2) %>%
select(mpg:hp) %>%
head(10) %>%
show_query()
#> <SQL>
#> SELECT `mpg` AS `mpg`, `cyl` AS `cyl`, `disp` AS `disp`, `hp` AS `hp`
#> FROM `mtcars`
#> WHERE (`cyl` > 2.0)
#> LIMIT 10At a lower-level, dplyr now:
- Can translate
case_when():library(dbplyr) translate_sql(case_when(x > 1 ~ "big", y < 2 ~ "small"), con = con) #> <SQL> CASE #> WHEN (`x` > 1.0) THEN ('big') #> WHEN (`y` < 2.0) THEN ('small') #> END - Has better support for type coercions:
translate_sql(as.character(cyl), con = con) #> <SQL> CAST(`cyl` AS TEXT) translate_sql(as.integer(cyl), con = con) #> <SQL> CAST(`cyl` AS INTEGER) translate_sql(as.double(cyl), con = con) #> <SQL> CAST(`cyl` AS NUMERIC) - Can more reliably translate
%IN%:translate_sql(x %in% 1:5, con = con) #> <SQL> `x` IN (1, 2, 3, 4, 5) translate_sql(x %in% 1L, con = con) #> <SQL> `x` IN (1) translate_sql(x %in% c(1L), con = con) #> <SQL> `x` IN (1)
You can now use in_schema() to refer to tables in schema: in_schema("my_schema_name", "my_table_name"). You can use the result of this function anywhere you could previously use a table name.
We’ve also included better translations for Oracle, MS SQL Server, Hive and Impala. We’re working to add support for more databases over time, but adding support on your own is surprisingly easy. Submit an issue to dplyr and we’ll help you get started.
These are just the highlights: you can see the full set of improvements and bug fixes in the release notes
Contributors
As with all R packages, this is truly a community effort. A big thanks goes to all those who contributed code or documentation to this release: Austen Head, Edgar Ruiz, Greg Freedman Ellis, Hannes Mühleisen, Ian Cook, Karl Dunkle Werner, Michael Sumner, Mine Cetinkaya-Rundel, @shabbybanks and Sergio Oller
Vision
Since you’ve read this far, I also wanted to touch on RStudio’s vision for databases. Many analysts have most of their data in databases, and making it as easy as possible to get data out of the database and into R makes a huge difference. Thanks to the community, R already has strong tools for talking to the popular open source databases. But support for connecting to enterprise databases and solving enterprise challenges has lagged somewhat. At RStudio we are actively working to solve these problems.
As well as dbplyr and DBI, we are working on many other pain points in the database ecosystem. You’ll hear much more about these packages in the future, but I wanted to touch on the highlights so you can see where we are heading. These pieces are not yet as integrated as they should be, but they are valuable by themselves, and we will continue to work to make a seamless database experience, that is as good as (or better than!) any other environment.
- The odbc package provides a DBI compliant backend for any database with an ODBC driver. Compared to the existing RODBC package, odbc is faster (~3x for reading, ~2x for writing), translates date/time data types, and is under active development.RStudio is also planning on providing best-of-breed ODBC drivers for the most important enterprise databases to our Pro customers. If you’ve felt the pain of connecting to your enterprise database and would like to learn more, please schedule a meeting with our sales team.
- You should never record database credentials in your R scripts, so we are working on safer ways to store them that don’t add a lot of extra hassle. One piece of the puzzle is the keyring package, which allows you to securely store information in your system keychain, and only decrypt it when needed.Another piece of the puzzle is the config package, which makes it easy to parameterise your database connection credentials so that you can connect to your testing database when experimenting locally, and your production database when you deploy your code.
- Connecting to databases from Shiny can be challenging because you don’t want a fresh connection every for every user action (because that’s slow), and you don’t want one connection per app (because that’s unreliable). The pool package allows you to manage a shared pool of connections for your app, giving you both speed and reliability.
- We’re also working to make sure all of these pieces are easily used from the IDE and inside R Markdown. One neat feature that you might not have heard about is support for SQL chunks in R Markdown.
If any of these pieces sound interesting, please stay tuned to the blog for more upcoming announcements. Please also check out out new database website: https://db.rstudio.com. Over time, this website will expand to document all database best practices, so you can find everything you need in one place.
I’m pleased to announce that bigrquery 0.4.0 is now on CRAN. bigrquery makes it possible to talk to Google’s BigQuery cloud database. It provides both DBI and dplyr backends so you can interact with BigQuery using either low-level SQL or high-level dplyr verbs.
Install the latest version of bigrquery with:
install.packages("bigrquery")Basic usage
Connect to a bigquery database using DBI:
library(dplyr)
con <- DBI::dbConnect(dbi_driver(),
project = "publicdata",
dataset = "samples",
billing = "887175176791"
)
DBI::dbListTables(con)
#> [1] "github_nested" "github_timeline" "gsod" "natality"
#> [5] "shakespeare" "trigrams" "wikipedia"(You’ll be prompted to authenticate interactively, or you can use a service token with set_service_token().)
Then you can either submit your own SQL queries or use dplyr to write them for you:
shakespeare <- con %>% tbl("shakespeare")
shakespeare %>%
group_by(word) %>%
summarise(n = sum(word_count))
#> 0 bytes processed
#> # Source: lazy query [?? x 2]
#> # Database: BigQueryConnection
#> word n
#> <chr> <int>
#> 1 profession 20
#> 2 augury 2
#> 3 undertakings 3
#> 4 surmise 8
#> 5 religion 14
#> 6 advanced 16
#> 7 Wormwood 1
#> 8 parchment 8
#> 9 villany 49
#> 10 digs 3
#> # ... with more rowsNew features
- dplyr support has been updated to require dplyr 0.7.0 and use dbplyr. This means that you can now more naturally work directly with DBI connections. dplyr now translates to modern BigQuery SQL which supports a broader set of translations. Along the way I also fixed a vareity of SQL generation bugs.
- New functions
insert_extract_job()makes it possible to extract data and save in google storage, andinsert_table()allows you to insert empty tables into a dataset. - All POST requests (inserts, updates, copies and
query_exec) now take.... This allows you to add arbitrary additional data to the request body making it possible to use parts of the BigQuery API that are otherwise not exposed.snake_caseargument names are automatically converted tocamelCaseso you can stick consistently to snake case in your R code. - Full support for DATE, TIME, and DATETIME types (#128).
There were a variety of bug fixes and other minor improvements: see the release notes for full details.
Contributors
bigrquery a community effort: a big thanks go to Christofer Bäcklin, Jarod G.R. Meng and Akhmed Umyarov for their pull requests. Thank you all for your contributions!
I’m pleased to announce that dplyr 0.7.0 is now on CRAN! (This was dplyr 0.6.0 previously; more on that below.) dplyr provides a “grammar” of data transformation, making it easy and elegant to solve the most common data manipulation challenges. dplyr supports multiple backends: as well as in-memory data frames, you can also use it with remote SQL databases. If you haven’t heard of dplyr before, the best place to start is the Data transformation chapter in R for Data Science.
You can install the latest version of dplyr with:
install.packages("dplyr")Features
dplyr 0.7.0 is a major release including over 100 improvements and bug fixes, as described in the release notes. In this blog post, I want to discuss one big change and a handful of smaller updates. This version of dplyr also saw a major revamp of database connections. That’s a big topic, so it’ll get its own blog post next week.
Tidy evaluation
The biggest change is a new system for programming with dplyr, called tidy evaluation, or tidy eval for short. Tidy eval is a system for capturing expressions and later evaluating them in the correct context. It is is important because it allows you to interpolate values in contexts where dplyr usually works with expressions:
my_var <- quo(homeworld)
starwars %>%
group_by(!!my_var) %>%
summarise_at(vars(height:mass), mean, na.rm = TRUE)
#> # A tibble: 49 x 3
#> homeworld height mass
#>
#> 1 Alderaan 176.3333 64.0
#> 2 Aleen Minor 79.0000 15.0
#> 3 Bespin 175.0000 79.0
#> 4 Bestine IV 180.0000 110.0
#> 5 Cato Neimoidia 191.0000 90.0
#> 6 Cerea 198.0000 82.0
#> 7 Champala 196.0000 NaN
#> 8 Chandrila 150.0000 NaN
#> 9 Concord Dawn 183.0000 79.0
#> 10 Corellia 175.0000 78.5
#> # ... with 39 more rowsThis makes it possible to write your functions that work like dplyr functions, reducing the amount of copy-and-paste in your code:
starwars_mean <- function(my_var) {
my_var <- enquo(my_var)
starwars %>%
group_by(!!my_var) %>%
summarise_at(vars(height:mass), mean, na.rm = TRUE)
}
starwars_mean(homeworld)You can also use the new .data pronoun to refer to variables with strings:
my_var <- "homeworld"
starwars %>%
group_by(.data[[my_var]]) %>%
summarise_at(vars(height:mass), mean, na.rm = TRUE)This is useful when you’re writing packages that use dplyr code because it avoids an annoying note from R CMD check.
To learn more about how tidy eval helps solve data analysis challenge, please read the new programming with dplyr vignette. Tidy evaluation is implemented in the rlang package, which also provides a vignette on the theoretical underpinnings. Tidy eval is a rich system and takes a while to get your head around it, but we are confident that learning tidy eval will pay off, especially as it roles out to other packages in the tidyverse (tidyr and ggplot2 are next on the todo list).
The introduction of tidy evaluation means that the standard evaluation (underscored) version of each main verb (filter_(), select_() etc) is no longer needed, and so these functions have been deprecated (but remain around for backward compatibility).
Character encoding
We have done a lot of work to ensure that dplyr works with encodings other than Latin1 on Windows. This is most likely to affect you if you work with data that contains Chinese, Japanese, or Korean (CJK) characters. dplyr should now just work with such data. Please let us know if you have problems!
New datasets
dplyr has some new datasets that will help write more interesting examples:
starwars, shown above, contains information about characters from the Star Wars movies, sourced from the Star Wars API. It contains a number of list-columns.starwars #> # A tibble: 87 x 13 #> name height mass hair_color skin_color eye_color #> #> 1 Luke Skywalker 172 77 blond fair blue #> 2 C-3PO 167 75 gold yellow #> 3 R2-D2 96 32 white, blue red #> 4 Darth Vader 202 136 none white yellow #> 5 Leia Organa 150 49 brown light brown #> 6 Owen Lars 178 120 brown, grey light blue #> 7 Beru Whitesun lars 165 75 brown light blue #> 8 R5-D4 97 32 white, red red #> 9 Biggs Darklighter 183 84 black light brown #> 10 Obi-Wan Kenobi 182 77 auburn, white fair blue-gray #> # ... with 77 more rows, and 7 more variables: birth_year , #> # gender , homeworld , species , films , #> # vehicles , starshipsstormshas the trajectories of ~200 tropical storms. It contains a strong grouping structure.storms #> # A tibble: 10,010 x 13 #> name year month day hour lat long status category #> #> 1 Amy 1975 6 27 0 27.5 -79.0 tropical depression -1 #> 2 Amy 1975 6 27 6 28.5 -79.0 tropical depression -1 #> 3 Amy 1975 6 27 12 29.5 -79.0 tropical depression -1 #> 4 Amy 1975 6 27 18 30.5 -79.0 tropical depression -1 #> 5 Amy 1975 6 28 0 31.5 -78.8 tropical depression -1 #> 6 Amy 1975 6 28 6 32.4 -78.7 tropical depression -1 #> 7 Amy 1975 6 28 12 33.3 -78.0 tropical depression -1 #> 8 Amy 1975 6 28 18 34.0 -77.0 tropical depression -1 #> 9 Amy 1975 6 29 0 34.4 -75.8 tropical storm 0 #> 10 Amy 1975 6 29 6 34.0 -74.8 tropical storm 0 #> # ... with 10,000 more rows, and 4 more variables: wind , #> # pressure , ts_diameter , hu_diameterband_members,band_instrumentsandband_instruments2has a tiny amount of data about bands. It’s designed to be very simple so you can illustrate how joins work without getting distracted by the details of the data.band_members #> # A tibble: 3 x 2 #> name band #> #> 1 Mick Stones #> 2 John Beatles #> 3 Paul Beatles band_instruments #> # A tibble: 3 x 2 #> name plays #> #> 1 John guitar #> 2 Paul bass #> 3 Keith guitar
New and improved verbs
- The
pull()generic allows you to extract a single column either by name or position. It’s similar toselect()but returns a vector, rather than a smaller tibble.mtcars %>% pull(-1) %>% str() #> num [1:32] 4 4 1 1 2 1 4 2 2 4 ... mtcars %>% pull(cyl) %>% str() #> num [1:32] 6 6 4 6 8 6 8 4 4 6 ...Thanks to Paul Poncet for the idea!
arrange()for grouped data frames gains a.by_groupargument so you can choose to sort by groups if you want to (defaults toFALSE).- All single table verbs now have scoped variants suffixed with
_if(),_at()and_all(). Use these if you want to do something to every variable (_all), variables selected by their names (_at), or variables that satisfy some predicate (_if).iris %>% summarise_if(is.numeric, mean) starwars %>% select_if(Negate(is.list)) storms %>% group_by_at(vars(month:hour))
Other important changes
- Local join functions can now control how missing values are matched. The default value is
na_matches = "na", which treats two missing values as equal. To prevent missing values from matching, usena_matches = "never".
You can change the default behaviour by calling pkgconfig::set_config("dplyr::na_matches", "never").
bind_rows()andcombine()are more strict when coercing. Logical values are no longer coerced to integer and numeric. Date, POSIXct and other integer or double-based classes are no longer coerced to integer or double to avoid dropping important metadata. We plan to continue improving this interface in the future.
Breaking changes
From time-to-time I discover that I made a mistake in an older version of dplyr and developed what is now a clearly suboptimal API. If the problem isn’t too big, I try to just leave it – the cost of making small improvements is not worth it when compared to the cost of breaking existing code. However, there are bigger improvements where I believe the short-term pain of breaking code is worth the long-term payoff of a better API.
Regardless, it’s still frustrating when an update to dplyr breaks your code. To minimise this pain, I plan to do two things going forward:
- Adopt an odd-even release cycle so that API breaking changes only occur in odd numbered releases. Even numbered releases will only contain bug fixes and new features. This is why I’ve skipped dplyr 0.6.0 and gone directly to dplyr 0.7.0.
- Invest time in developing better tools isolating packages across projects so that you can choose when to upgrade a package on a project-by-project basis, and if something goes wrong, easily roll back to a version that worked. Look for news about this later in the year.
Contributors
dplyr is truly a community effort. Apart from the dplyr team (myself, Kirill Müller, and Lionel Henry), this release wouldn’t have been possible without patches from Christophe Dervieux, Dean Attali, Ian Cook, Ian Lyttle, Jake Russ, Jay Hesselberth, Jennifer (Jenny) Bryan, @lindbrook, Mauro Lepore, Nicolas Coutin, Daniel, Tony Fischetti, Hiroaki Yutani and Sergio Oller. Thank you all for your contributions!
RStudio Blog 