MySQL Notes

The Set-up
channel <- odbcConnect("Valve", uid="rstudio",pwd="****")
tables <- sqlQuery(channel, paste("show tables from Valve"))

Show Process List
sqlQuery(channel, paste("SHOW PROCESSLIST"))

Kill Process - Where "1" is the number, found in SHOW PROCESSLIST of the process you want to kill
sqlQuery(channel, paste("kill 1"))

Lots of Helpful Examples: ODBC (note that requires a special driver be installed)

Simple Query to Get Item Subset of Price Observations - probably wise to ensure that the ItemID column is an index. 

temp <- sqlQuery(channel,
                 paste("SELECT * FROM ValveDataExplorer.PriceObs ",
                 "WHERE DefID_AppID_Q = '0_440_0';", 

ItemStocks Join - Find the min time. 
Date_0 <- sqlQuery(channel, paste("SELECT Min(Timestamp) FROM Valve.App440ItemAuditMasked"))
DATE <- as.numeric(Date_0[1])
temp <- sqlQuery(channel,
paste("SELECT * FROM Valve.App440ItemAuditMasked LEFT JOIN Valve.App440ItemMasked ",
        "ON Valve.App440ItemAuditMasked.ItemID=Valve.App440ItemMasked.ID ",
"WHERE Valve.App440ItemAuditMasked.Timestamp >= ",
" AND Valve.App440ItemAuditMasked.Timestamp < (",
" + (60*60*24));", 

Upload R DataFrame to mySQL database
        tablename = "ValveDataExplorer.ItemTimeSeriesData",
        append = FALSE,
  • Where "channel" is the odbc channel connection us to the server
  • ItemTimeSeriesData is the name of the R object data frame we're uploading
  • ValveDataExplorer.ItemTimeSeriesData is a character string, with the database and table name we'd like to update or upload our data frame into. 
  • append refers to whether or not we'd like to rbind, or replace any existing table. 
  • colnames=T, with a normal R data frame, if the columns are named, then "colnames=T" will put those columns names as the first line in the mySQL database table. So, probably make that false. 
  • verbose=T
  • More:

Example of code that worked
sqlSave(channel, Price.Obs.temp, tablename = "ValveDataExplorer.PriceObs", append = TRUE,colnames = FALSE, rownames=F)

None issues: 
  • Inf values messed up with importing into a DOUBLE field in mySQL. 

Drop a Database Table - When replacing a new data, it seems you need to delete the existing table, and then upload the replacement. 
> sqlDrop(channel, "DataBaseName.TableName")