---

title : Reading mySQL

subtitle :

author : Jeffrey Leek

job : Johns Hopkins Bloomberg School of Public Health

logo : bloomberg_shield.png

framework : io2012 # {io2012, html5slides, shower, dzslides, ...}

highlighter : highlight.js # {highlight.js, prettify, highlight}

hitheme : tomorrow #

url:

lib: ../../libraries

assets: ../../assets

widgets : [mathjax] # {mathjax, quiz, bootstrap}

mode : selfcontained # {standalone, draft}

---

```{r setup, cache = F, echo = F, message = F, warning = F, tidy = F}

# make this an external chunk that can be included in any file

options(width = 100)

opts_chunk$set(message = F, error = F, warning = F, comment = NA, fig.align = 'center', dpi = 100, tidy = F, cache.path = '.cache/', fig.path = 'fig/', cache=TRUE)

options(xtable.type = 'html')

knit_hooks$set(inline = function(x) {

if(is.numeric(x)) {

round(x, getOption('digits'))

} else {

paste(as.character(x), collapse = ', ')

}

})

knit_hooks$set(plot = knitr:::hook_plot_html)

```

## mySQL

* Free and widely used open source database software

* Widely used in internet based applications

* Data are structured in

* Databases

* Tables within databases

* Fields within tables

* Each row is called a record

[http://en.wikipedia.org/wiki/MySQL](http://en.wikipedia.org/wiki/MySQL)

[http://www.mysql.com/](http://www.mysql.com/)

---

## Example structure

<img class=center src=../../assets/img/03_ObtainingData/database-schema.png height=450>

[http://dev.mysql.com/doc/employee/en/sakila-structure.html](http://dev.mysql.com/doc/employee/en/sakila-structure.html)

---

## Step 1 - Install MySQL

<img class=center src=../../assets/img/03_ObtainingData/installmysql.png height=450>

[http://dev.mysql.com/doc/refman/5.7/en/installing.html](http://dev.mysql.com/doc/refman/5.7/en/installing.html)

---

## Step 2 - Install RMySQL

* On a Mac: ```install.packages("RMySQL")```

* On Windows:

* Official instructions - [http://biostat.mc.vanderbilt.edu/wiki/Main/RMySQL](http://biostat.mc.vanderbilt.edu/wiki/Main/RMySQL) (may be useful for Mac/UNIX users as well)

* Potentially useful guide - [http://www.ahschulz.de/2013/07/23/installing-rmysql-under-windows/](http://www.ahschulz.de/2013/07/23/installing-rmysql-under-windows/)

---

## Example - UCSC database

<img class=center src=../../assets/img/03_ObtainingData/ucsc.png height=450>

[http://genome.ucsc.edu/](http://genome.ucsc.edu/)

---

## UCSC MySQL

<img class=center src=../../assets/img/03_ObtainingData/ucscmysql.png height=450>

[http://genome.ucsc.edu/goldenPath/help/mysql.html](http://genome.ucsc.edu/goldenPath/help/mysql.html)

---

## Connecting and listing databases

```{r databases}

ucscDb <- dbConnect(MySQL(),user="genome",

host="genome-mysql.cse.ucsc.edu")

result <- dbGetQuery(ucscDb,"show databases;"); dbDisconnect(ucscDb);

result

```

---

## Connecting to hg19 and listing tables

```{r tables}

hg19 <- dbConnect(MySQL(),user="genome", db="hg19",

host="genome-mysql.cse.ucsc.edu")

allTables <- dbListTables(hg19)

length(allTables)

allTables[1:5]

```

---

## Get dimensions of a specific table

```{r dimensions,dependson="tables"}

dbListFields(hg19,"affyU133Plus2")

dbGetQuery(hg19, "select count(*) from affyU133Plus2")

```

---

## Read from the table

```{r readdata ,dependson="tables"}

affyData <- dbReadTable(hg19, "affyU133Plus2")

head(affyData)

```

---

## Select a specific subset

```{r, dependson="tables"}

query <- dbSendQuery(hg19, "select * from affyU133Plus2 where misMatches between 1 and 3")

affyMis <- fetch(query); quantile(affyMis$misMatches)

affyMisSmall <- fetch(query,n=10); dbClearResult(query);

dim(affyMisSmall)

```

---

## Don't forget to close the connection!

```{r, dependson="tables"}

dbDisconnect(hg19)

```

---

## Further resources

* RMySQL vignette [http://cran.r-project.org/web/packages/RMySQL/RMySQL.pdf](http://cran.r-project.org/web/packages/RMySQL/RMySQL.pdf)

* List of commands [http://www.pantz.org/software/mysql/mysqlcommands.html](http://www.pantz.org/software/mysql/mysqlcommands.html)

* __Do not, do not, delete, add or join things from ensembl. Only select.__

* In general be careful with mysql commands

* A nice blog post summarizing some other commands [http://www.r-bloggers.com/mysql-and-r/](http://www.r-bloggers.com/mysql-and-r/)