---
title: "Predict Billboard Top 10 Hits using RStudio,H2O and Amazon Athena"
output:
  html_notebook: default
  pdf_document: default
  word_document: default
---

Copyright 2017 Amazon.com, Inc. or its affiliates. All Rights Reserved. Licensed under the Apache License, Version 2.0 (the "License"). You may not use this file except in compliance with the License. A copy of the License is located at http://aws.amazon.com/apache2.0/
or in the "license" file accompanying this file. This file is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.

RStudio is a popular IDE, licensed either commercially or under AGPLv3, for working with R. This is ideal if you don’t want to connect to a server via SSH and use code editors such as vi to do analytics. RStudio is available in a desktop version, or a server version that allows you to access R via a web browser.

```{r}
if("pacman" %in% rownames(installed.packages()) == FALSE)
{install.packages("pacman")}  
```

```{r}
library(pacman)
p_load(h2o,rJava,RJDBC,awsjavasdk)
h2o.init(nthreads = -1)
```

```{r}
#install aws sdk if not present (pre-requisite for using Athena with an IAM role)
if (!aws_sdk_present()) {
  install_aws_sdk()
}

load_sdk()
```



```{r}
URL <- 'https://s3.amazonaws.com/athena-downloads/drivers/AthenaJDBC41-1.0.1.jar'
fil <- basename(URL)
#download the file into current working directory
if (!file.exists(fil)) download.file(URL, fil)
#verify that the file has been downloaded successfully
list.files()
```


```{r}
drv <- JDBC(driverClass="com.amazonaws.athena.jdbc.AthenaDriver", fil, identifier.quote="'")
```

```{r}
con <- jdbcConnection <- dbConnect(drv, 'jdbc:awsathena://athena.us-east-1.amazonaws.com:443/',
                            s3_staging_dir=Sys.getenv("ATHENABUCKET"),
                                   aws_credentials_provider_class="com.amazonaws.auth.DefaultAWSCredentialsProviderChain")
```


```{r}
con
dbListTables(con)
```


```{r}
dbGetQuery(con, "show create table sampledb.billboard")
```


```{r}
dbGetQuery(con, " SELECT songtitle,artistname,top10 FROM sampledb.billboard where lower(artistname) = 'janet jackson' and top10 = 1")
```


```{r}
dbGetQuery(con, " SELECT count(*) FROM sampledb.billboard where year = 2010")
```


```{r}
#t<-dbGetQuery(con, " SELECT timesignature FROM sampledb.billboard")
#Note:Running the query as above will result in the error below - 
#Error in .jcall(rp, "I", "fetch", stride, block) : java.sql.SQLException: The requested #fetchSize is more than the allowed value in Athena. Please reduce the fetchSize and try #again. Refer to the Athena documentation for valid fetchSize values.
```



```{r}
r <- dbSendQuery(con, " SELECT timesignature FROM sampledb.billboard")
dftimesignature<- fetch(r, n=-1, block=100)
dbClearResult(r)
table(dftimesignature)
nrow(dftimesignature)
```

```{r}
dbGetQuery(con, " SELECT songtitle,artistname,tempo FROM sampledb.billboard where tempo = (select max(tempo) FROM sampledb.billboard) ")
```



```{r}
#BillboardTrain <- dbGetQuery(con, "SELECT * FROM sampledb.billboard where year <= 2009")
#Note: running the query as above will result in the error below -
#Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set for ", : Unable to retrieve #JDBC result set for SELECT * FROM sampledb.billboard where year <= 2009 (Internal error)
#We will follow the same approach as before to address this issue.
```


```{r}
r <- dbSendQuery(con, "SELECT * FROM sampledb.billboard where year <= 2009")
BillboardTrain <- fetch(r, n=-1, block=100)
dbClearResult(r)
```

```{r}
BillboardTrain[1:2,c(1:3,6:10)]
```
```{r}
nrow(BillboardTrain)
```

```{r}
BillboardTest <- dbGetQuery(con, "SELECT * FROM sampledb.billboard where year = 2010")
```

```{r}
BillboardTest[1:2,c(1:3,11:15)]
```

```{r}
nrow(BillboardTest)
```

```{r}
train.h2o <- as.h2o(BillboardTrain)
test.h2o <- as.h2o(BillboardTest)
```

```{r}
colnames(train.h2o)
```



```{r}
y.dep <- 39
x.indep <- c(6:38)
x.indep
```


```{r}
modelh1 <- h2o.glm( y = y.dep, x = x.indep, training_frame = train.h2o, family = "binomial")
```

```{r}
h2o.performance(modelh1,test.h2o)
```
```{r}
h2o.auc(h2o.performance(modelh1,test.h2o))
```



```{r}
dfmodelh1 <- as.data.frame(h2o.varimp(modelh1))
dfmodelh1
```



```{r}
cor(train.h2o$loudness,train.h2o$energy)
```


```{r}
colnames(train.h2o)
```

```{r}
y.dep <- 39
x.indep <- c(6:7,9:38)
x.indep
```

```{r}
modelh2 <- h2o.glm( y = y.dep, x = x.indep, training_frame = train.h2o, family = "binomial")
```

```{r}
h2o.performance(modelh2,test.h2o)
```


```{r}
dfmodelh2 <- as.data.frame(h2o.varimp(modelh2))
dfmodelh2
```
```{r}
h2o.auc(h2o.performance(modelh2,test.h2o))
```


```{r}
colnames(train.h2o)
```

```{r}
y.dep <- 39
x.indep <- c(6:12,14:38)
x.indep
```

```{r}
modelh3 <- h2o.glm( y = y.dep, x = x.indep, training_frame = train.h2o, family = "binomial")
```


```{r}
perfh3<-h2o.performance(modelh3,test.h2o)
perfh3
```

```{r}

dfmodelh3 <- as.data.frame(h2o.varimp(modelh3))
dfmodelh3
```
```{r}
h2o.sensitivity(perfh3,0.5)
```

```{r}
h2o.auc(perfh3)
```


```{r}
predict.regh <- h2o.predict(modelh3, test.h2o)
```

```{r}
print(predict.regh)
```


```{r}
predict.regh$predict
```
```{r}
dpr<-as.data.frame(predict.regh)
#let's rename the predicted column 
colnames(dpr)[colnames(dpr) == 'predict'] <- 'predict_top10'
table(dpr$predict_top10)

```


```{r}
table(BillboardTest$top10)
```



```{r}
train.h2o$top10=as.factor(train.h2o$top10)
```

```{r}
gbm.modelh <- h2o.gbm(y=y.dep, x=x.indep, training_frame = train.h2o, ntrees = 500, max_depth = 4, learn_rate = 0.01, seed = 1122,distribution="multinomial")

```


```{r}
perf.gbmh<-h2o.performance(gbm.modelh,test.h2o)
perf.gbmh
```

```{r}
h2o.sensitivity(perf.gbmh,0.5)
```

```{r}
h2o.auc(perf.gbmh)
```


```{r}
system.time(
  dlearning.modelh <- h2o.deeplearning(y = y.dep,
                                      x = x.indep,
                                      training_frame = train.h2o,
                                      epoch = 250,
                                      hidden = c(250,250),
                                      activation = "Rectifier",
                                      seed = 1122,
                                      distribution="multinomial"
  )
)
```
```{r}
perf.dl<-h2o.performance(dlearning.modelh,test.h2o)
perf.dl
```
```{r}
h2o.sensitivity(perf.dl,0.5)
```

```{r}
h2o.auc(perf.dl)
```


