eval_db <- FALSE if(Sys.getenv("GLOBAL_EVAL") != "") eval_db <- Sys.getenv("GLOBAL_EVAL")
library(connections) library(RSQLite) library(dplyr) library(dbplyr) library(config)
dplyr
connections
Use connections
to open open a database connection
Load the connections
package
r
library(connections)
library(config)
Use connection_open()
to open a Database connection
r
con <- connection_open(
RPostgres::Postgres(),
host = "localhost",
user = get("user", config = "dev"),
password = get("pwd", config = "dev"),
port = 5432,
dbname = "postgres",
bigint = "integer"
)
The RStudio Connections pane should show the tables in the database
Use the dplyr
's tbl()
command
Load the dplyr
package
r
library(dplyr)
Add in_schema()
as an argument to tbl()
to specify the schema
r
tbl(con, in_schema("retail", "customer"))
Load the results from the tbl()
command that points the table called orders to a variable called orders
```r
```
Use the class
function to determine the object type of orders
```r
```
Use show_query()
to preview the SQL statement that will be sent to the database
Use show_query()
to preview SQL statement that actually runs when we run orders
as a command
```r
```
When executed, orders
returns the first 1000 rows of the remote orders table
r
orders
Full results of a remote query can be brought into R with collect
r
local_orders <- collect(orders)
Easily view the resulting query by adding show_query()
in another piped command
```r
```
Insert head()
in between the two statements to see how the SQL changes
```r
```
Queries can be assigned to variables. Create a variable called orders_head
that contains the previous query
```r
```
Use sql_render()
and simulate_mssql()
to see how the SQL statement changes from vendor to vendor
```r
```
Use explain()
to explore the query plan
```r
```
Review of how dbplyr
handles R commands that have not been translated into a like-SQL command
Preview how mean
is translated
r
orders %>%
mutate(avg_id = mean(order_id, na.rm = TRUE)) %>%
show_query()
Preview how Sys.Date()
is translated
```r
```
Use PostgreSQL native commands, in this case date
r
orders %>%
mutate(today = date('now')) %>%
show_query()
Run the dplyr
code to confirm it works
```r
```
Intro on passing unevaluated code to a dplyr verb
Preview how Sys.Date()
is translated when prefixing !!
```r
```
View resulting table when Sys.Date()
is translated when prefixing !!
r
orders %>%
mutate(today = !!Sys.Date()) %>%
head()
Disconnect from the database using connection_close
r
connection_close(con)
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.