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;
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