01-database-access.Rmd

---
title: "Access a database"
output: html_notebook
---

## 1.1 - Connect to a database

1. Click on the `Connections` tab

2. Click on the `New Connection` button

3. Select `Postgres Dev`

4. Click OK

## 1.2 - Explore the database using the RStudio IDE

1. Expand the `datawarehouse` schema

2. Expand the `airport` table

3. Click on the table icon to the right of the `airport` table 

4. *(Optional)* Expand and explore the other tables

5. Click on the *disconnect* icon to close the connection


## 1.3 - List drivers and DSNs 

1. To get a list of drivers available in the server
```{r, access}
library(odbc)

odbcListDrivers()[1:2]
```

2. Click on the *ellipsis* button located in the **Files** tab

3. Type: `/etc`

4. Locate and open the `odbcinst.ini` file

5. To see a list of DSNs available in the server
```{r}
odbcListDataSources()
```

6. Using the *ellipsis* button again, navigate to `/etc/odbc.ini`

## 1.4 - Connect to a database using code

1. Run the following code to connect
```{r}
library(DBI)
con <- dbConnect(odbc::odbc(), "Postgres Dev")
```

2. Use `dbListTables()` to retrieve a list of tables
```{r}
dbListTables(con)
```

3. Use `dbGetQuery()` to run a quick query
```{r}
odbc::dbGetQuery(con, "SELECT * FROM datawarehouse.airport LIMIT 10")
```

4. Use the SQL chunk
```{sql, connection = con}
SELECT * FROM datawarehouse.airport LIMIT 10
```

5. Use the `output.var` option to load results to a variable
```{sql, connection = con, output.var = "sql_top10"}
SELECT * FROM datawarehouse.airport LIMIT 10
```

6. Test the variable
```{r}
sql_top10
```

7. Disconnect from the database using `dbDisconnect()`
```{r}
dbDisconnect(con)
```

## 1.5 - RStudio SQL Script
*Try out the new SQL Script support in RStudio 1.2*

1. Open the *query-example.sql* file

2. Click the *Preview* button. It is located in the top-right area of the script

3. In the script, change *airport* to *carrier*

4. Click on *Preview* again

## 1.6 - Connect to a database without a DSN
*A more complex way of connecting to a database, using best practices: http://db.rstudio.com/best-practices/managing-credentials/#prompt-for-credentials *

1. Use the following code to start a new connection that does not use the pre-defined DSN
```{r, eval = FALSE}
con <- dbConnect(
  odbc::odbc(),
  Driver = "PostgreSQL",
  Server = "localhost",
  UID    = rstudioapi::askForPassword("Database user"),
  PWD    = rstudioapi::askForPassword("Database password"),
  Port = 5432,
  Database = "postgres"
)
```

2. When prompted, type in **rstudio_dev** for the user, and **dev_user** as the password

3. Disconnect from the database using `dbDisconnect()`
```{r}
dbDisconnect(con)
```

## 1.7 - Secure credentials in a file

1. Open and explore the `config.yml` file available in your working directory

2. Load the `datawarehouse-dev` values to a variable
```{r}
dw <- config::get("datawarehouse-dev")
```

3. Check that the variable loaded propery, by checking the `driver` value
```{r}
dw$driver
```
4. Use info in the config.yml file to connect to the database
```{r}
con <- dbConnect(odbc::odbc(),
   Driver = dw$driver,
   Server = dw$server,
   UID    = dw$uid,
   PWD    = dw$pwd,
   Port   = dw$port,
   Database = dw$database
)
```

5. Disconnect from the database using `dbDisconnect()`
```{r}
dbDisconnect(con)
```

## 1.8 - Environment variables

1. Open and explore the `.Renviron` file available in your working directory

2. Confirm that the environment variables are loaded by using `Sys.getenv()`
```{r}
Sys.getenv("uid")
```

3. Pass the credentials using the environment variables
```{r}
con <- dbConnect(
  odbc::odbc(),
  Driver = "PostgreSQL",
  Server = "localhost",
  UID    = Sys.getenv("uid"),
  PWD    = Sys.getenv("pwd"),
  Port = 5432,
  Database = "postgres"
)
```

4. Disconnect from the database using `dbDisconnect()`
```{r}
dbDisconnect(con)
```

## 1.9 - Use options()

1. Open and explore the `options.R` script available in your working directory

2. Source the `options.R` script
```{r}
source("options.R")
```

3. Confirm that the environment variables are loaded by using `Sys.getenv()`
```{r}
getOption("database_userid")
```

4. Pass the credentials using the environment variables
```{r, eval = FALSE}
con <- dbConnect(
  odbc::odbc(),
  Driver = "PostgreSQL",
  Server = "localhost",
  UID    = getOption("database_userid"),
  PWD    = getOption("database_password"),
  Port = 5432,
  Database = "postgres"
)
```

5. Disconnect from the database using `dbDisconnect()`
```{r}
dbDisconnect(con)
```