In rstudio-conf-2020/big-data: Content and setup files for the Big Data with R class

if(Sys.getenv("GLOBAL_EVAL") != "") eval_connections <- Sys.getenv("GLOBAL_EVAL")
eval_connections <- FALSE

library(DBI)
library(odbc)
library(config)
library(keyring)

Introduction to database connections

Connect with the Connections pane

Connect using the features of the RStudio IDE

1. The connections pane (top right hand corner of the RStudio IDE) can guide through establishing database connections.

Connecting via DSN

Connect using defined Data Source Name (DSN). This requires an ODBC driver.

1. Load the DBI and odbc packages r library(DBI) library(odbc)

2. Use odbcListDatasources to list available DSNs r odbcListDataSources()

3. Use dbConnect to connect to a database using the odbc function and a DSN r con <- dbConnect(odbc(), "Postgres Dev")

4. Disconnect using dbDisconnect r dbDisconnect(con)

Connect with a connection string

Connect by specifying all connection details in dbConnect

1. Use dbConnect and odbc to connect to a database, but this time all connection details are provided r con <- dbConnect(odbc(), driver = "postgresql", host = "localhost", user = "rstudio_dev", pwd = "dev_user", port = 5432, database = "postgres", bigint = "integer")

2. Disconnect using dbDisconnect r dbDisconnect(con)

Secure connection details

Use config, keyring, or environment variables to secure connection credentials

1. Load the config package r library(config)

2. Get the current config using the get function and store the results in an object called config r config <- get()

3. Use str to investigate the contents of config r str(config)

4. Connect using details provided in config r con <- dbConnect(odbc(), driver = config$driver, host = config$host, user = config$user, pwd = config$pwd, port = config$port, database = config$dbname, bigint = config$bigint)

5. Disconnect using dbDisconnect r dbDisconnect(con)

6. Load the keyring package r library(keyring)

7. Store the database username and password using keyring. The username is rstudio_dev and the password is dev_user r key_set("postgres", "username") key_set("postgres", "password")

8. Use the stored credentials along with dbConnect to connect to the database r con <- dbConnect(odbc(), driver = "postgresql", host = "localhost", user = key_get("postgres", "username"), pwd = key_get("postgres", "password"), port = 5432, database = "postgres", bigint = "integer")

9. Discnonnect using dbDisconnect r dbDisconnect(con)

10. The .Renviron file contains entries to create environment variables for PG_USER and PG_PWD. These variables can be read using Sys.getenv(). r Sys.getenv("PG_USER")

11. Connect to the database using the credentials stored in .Renviron and Sys.getenv() r con <- dbConnect(odbc(), driver = "postgresql", host = "localhost", user = Sys.getenv("PG_USER"), pwd = Sys.getenv("PG_PWD"), port = 5432, database = "postgres", bigint = "integer")

12. Disconnect using dbDisconnect r dbDisconnect(con)

13. Store connection details using options() r options(pg_user = "rstudio_dev", pg_pwd = "dev_user")

14. Connect using the credentials accessed via getOption r con <- dbConnect(odbc(), driver = "postgresql", host = "localhost", user = getOption("pg_user"), pwd = getOption("pg_pwd"), port = 5432, database = "postgres", bigint = "integer")

15. Disconnect using dbDisconnect r dbDisconnect(con)

16. Interactively prompt users for input using rstudioapi::askForPassword() r con <- dbConnect(odbc(), driver = "postgresql", host = "localhost", user = rstudioapi::askForPassword("DB User"), pwd = rstudioapi::askForPassword("DB Password"), port = 5432, database = "postgres", bigint = "integer")

17. Disconnect using dbDisconnect r dbDisconnect(con)

rstudio-conf-2020/big-data documentation built on Feb. 4, 2020, 5:24 p.m.