MySQL Notes




The Set-up
library(RODBC)
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';", 
                 sep="")


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 >= ",
DATE,
" AND Valve.App440ItemAuditMasked.Timestamp < (",
DATE,
" + (60*60*24));", 
sep=""))
})


Upload R DataFrame to mySQL database - 
sqlSave(channel,
        Prices.MovingAvg,
        tablename = "ValveDataExplorer.ItemTimeSeriesData",
        append = FALSE,
        colnames=F,
        verbose=F)
  • 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: http://cran.r-project.org/web/packages/RODBC/RODBC.pdf

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")



Comments