Connect to amazon web services using the DBI package.

cn <- dbConnect(drv = RMySQL::MySQL(), 
                username = "user1", 
                password = "testpassword", 
                host = "davisdbinstance.crarljboc8to.us-west-2.rds.amazonaws.com", 
                port = 3306, 
                dbname = "firstdb")

Download stock data using tidyquant.

aapl <- tq_get("AAPL")

Write the tibble to the database using DBI.

#dbWriteTable(cn, "apple", aapl)

Pull it back down using SQL in RMarkdown!

SELECT * 
FROM apple;
Displaying records 1 - 10
row_names date open high low close volume adjusted
1 2007-01-03 86.29 86.58 81.90 83.80 309579900 10.90416
2 2007-01-04 84.05 85.95 83.82 85.66 211815100 11.14619
3 2007-01-05 85.77 86.20 84.40 85.05 208685400 11.06681
4 2007-01-08 85.96 86.53 85.28 85.47 199276700 11.12147
5 2007-01-09 86.45 92.98 85.15 92.57 837324600 12.04533
6 2007-01-10 94.75 97.80 93.45 97.00 738220000 12.62176
7 2007-01-11 95.94 96.78 95.10 95.80 360063200 12.46562
8 2007-01-12 94.59 95.06 93.23 94.62 328172600 12.31207
9 2007-01-16 95.68 97.25 95.45 97.10 311019100 12.63477
10 2007-01-17 97.56 97.60 94.82 94.95 411565000 12.35501

Pull it back down using the DBI package in R!

x <- dbGetQuery(cn, "SELECT * FROM apple;")

head(x)

Make sure and disconnect so we can try other things.

dbDisconnect(cn)
## [1] TRUE

Now, redo the connection but use dplyr!

cn <- src_mysql(user = "user1", 
                password = "testpassword", 
                host = "davisdbinstance.crarljboc8to.us-west-2.rds.amazonaws.com", 
                port = 3306, 
                dbname = "firstdb")

Select the apple table from that database. This does not actually pull in the data. Just makes the connection!

apple_table <- tbl(cn, "apple")

We can use almost any dplyr command with this! It queries the database, and does not do the manipulation in R. This is insane.

apple_table %>% select(date, adjusted)

A dplyr filter().

apple_table %>% filter(date < "2008-01-01")

IBM added to the database using copy_to().

#ibm <- tq_get("IBM")

#copy_to(cn, ibm, temporary = FALSE)

Pull down IBM, then mutate it to add daily returns using tidyquant.

ibm_table <- tbl(cn, "ibm")
real_tibble <- collect(ibm_table)
real_tibble$date <- as.Date(real_tibble$date)

real_tibble <- real_tibble %>% 
  tq_mutate(ohlc_fun = Ad, 
            mutate_fun = periodReturn, 
            period = "daily")
## Warning: Argument ohlc_fun is deprecated; please use select instead.
real_tibble