if (dir.exists("~/pins")) unlink("~/pins", recursive = TRUE) knitr::opts_chunk$set( collapse = TRUE, comment = "#>", fig.path = "man/figures/README-", out.width = "100%", eval = TRUE ) add_figure <- function(path, width = 400) { char_html <- paste0("<img src='", path, "' width ='", width, "px'/><br/>") htmltools::HTML(char_html) } toc <- function() { re <- readLines("README.Rmd") has_title <- as.logical(lapply(re, function(x) substr(x, 1, 2) == "##")) only_titles <- re[has_title] titles <- trimws(gsub("#", "", only_titles)) links <- trimws(gsub("`", "", titles)) links <- tolower(links) links <- trimws(gsub(" ", "-", links)) links <- trimws(gsub(",", "", links)) toc_list <- lapply( seq_along(titles), function(x) { pad <- ifelse(substr(only_titles[x], 1, 3) == "###", " - ", " - ") paste0(pad, "[", titles[x], "](#", links[x], ")") } ) toc_full <- paste(toc_list, collapse = "\n") cat(toc_full) }
dplyr
pins
DBI
connectionsThe main goal of connections
is to integrate DBI
-compliant packages with the
RStudio IDE's Connection
Pane. Packages such as
RPostgres, RSQLite,
RMariaDB and
bigrquery connect R to those databases, but
do not provide a direct integration with the Connections Pane. connections
reads the configuration of the connection and creates the integration with RStudio.
A second goal is to provide integration with the pins
package. The connections
package allows you to pin database connections and
dplyr table objects.
Install the development version from GitHub with:
# install.packages("remotes") remotes::install_github("rstudio/connections")
library(connections) library(RSQLite) library(dplyr) library(pins) if (file.exists("local.sqlite")) unlink("local.sqlite")
The two main functions added by connections
are:
connection_open()
- Opens the database connection. Use instead of
dbConnect()
, but use the exact same arguments. It also automatically starts
the Connections pane. connection_close()
- Closes the database connection.library(connections) library(RSQLite) con <- connection_open(SQLite(), "local.sqlite")
r add_figure("man/figures/connection-1.png")
The connection can now be closed by using the appropriate button in the
Connections pane, or by using connection_close()
connection_close(con)
r add_figure("man/figures/connection-2.png")
The connection code is parsed when connecting to the database, and it is visible once the connection is closed.
dplyr
connections
integrates with dplyr
by supporting the following two functions:
tbl()
- To create a pointer to a table or view within the database.copy_to()
- To copy data from the R session to the database.The version of copy_to()
inside connections
automatically updates the
Connections pane, so the new table automatically shows up.
con <- connection_open(SQLite(), "local.sqlite") copy_to(con, mtcars, temporary = FALSE, overwrite = TRUE)
To use an existing table inside the database use tbl()
.
db_mtcars <- tbl(con, "mtcars")
r add_figure("man/figures/pane-1.png")
The tbl()
function opens the rest of the already available dplyr
database integration.
db_mtcars %>% group_by(am) %>% summarise(avg_mpg = mean(mpg, na.rm = TRUE))
pins
The connections
package integrates with pins
. It adds the ability to "pin"
database connections and queries. It follows the same approach as the
vetiver
package. connections
now has two new functions:
connection_pin_write()
connection_pin_read()
The connection_pin_write()
function does not save the R object. It records
the code necessary to recreate the connection.
library(pins) board <- board_folder("~/pins") connection_pin_write(board, con, name = "my_conn")
r add_figure("man/figures/pins-1.png")
If you wish to see the code that connections
will use when recreating the
conneciton from the pin, you can use connection_code()
:
connection_code(con)
connection_pin_read()
will replay the exact same code used to initially
connect to the database. Assign the output to a variable, such as
con1
. The variable will work just like any connection variable.
con1 <- connection_pin_read(board, "my_conn")
The con1
variable is now a regular database connection variable.
db_mtcars <- tbl(con1, "mtcars") %>% group_by(am) %>% summarise(avg_mpg = mean(mpg, na.rm = TRUE)) db_mtcars
dplyr
database queryWhen dplyr
works with database data, the resulting query is not executed until
the data is explicitly collected into R, or when printing the top results to the
R Console. The pin
records two things:
The dplyr
R object that contains all of the transformations. It does not
save the actual results.
The necessary information to recreate the database connection. This is to make sure that the data is being retrieved from the original database connection.
connection_pin_write(board, db_mtcars, name = "avg_mpg")
r add_figure("man/figures/pins-2.png")
connection_pin_read()
will connect to the database, and return the dplyr
object.
Without assigning it to a variable, the pin will immediately print the results of the
database. Those results are being processed at the time connection_pin_read()
runs.
connection_pin_read(board, "avg_mpg")
pins
exampleThe way pins
integrates with databases, via the connections
package, allows
to open the connection from a pin, and pipe all of the subsequent code into a
new pin. Afterwards, that pin can be used to collect or to continue using the
dplyr
object.
board <- board_folder("~/pins") con <- connection_pin_read(board, "my_conn") tbl_summary <- con %>% tbl("mtcars") %>% group_by(cyl) %>% summarise(avg_mpg = mean(mpg, na.rm = TRUE)) connection_pin_write(board, tbl_summary, name = "cyl_mpg") connection_close(con) connection_pin_read(board, "cyl_mpg")
r add_figure("man/figures/pins-3.png")
There are a couple of examples of how the Connections pane will look when
opening the connection via connections
.
bigrquery
library(connections) library(bigrquery) con <- connection_open( bigquery(), project = "bigquery-public-data", dataset = "austin_311", billing = "my_project_billing", use_legacy_sql = FALSE )
r add_figure("man/figures/bigquery-1.png")
connection_close(con)
r add_figure("man/figures/bigquery-2.png")
RPostgres
library(connections) library(RPostgres) con <- connection_open( Postgres(), host = "localhost", dbname = "datawarehouse", user = "[user id]", password = "[password]", bigint = "integer", port = "5432" )
r add_figure("man/figures/postgres-1.png")
DBI
connectionsIt is possible to integrate DBI
connections not opened via connection_open()
.
To do that, use connection_view()
and pass it the variable containing the
existing database connection.
library(DBI) con <- dbConnect(RSQLite::SQLite(), ":memory:") connection_view(con)
r add_figure("man/figures/dbi-1.png", 200)
Changes to the database will not automatically load in the Connections pane.
The connection_update()
function will refresh the pane with the latest.
dbWriteTable(con, "mtcars", mtcars) connection_update(con)
r add_figure("man/figures/dbi-2.png", 300)
connection_close(con)
r add_figure("man/figures/dbi-3.png", 300)
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.