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.
ItemStocks Join - Find the min time. Date_0 <- sqlQuery(channel, paste("SELECT Min(Timestamp) FROM Valve.App440ItemAuditMasked")) 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,
Example of code that worked
None issues:
Drop a Database Table - When replacing a new data, it seems you need to delete the existing table, and then upload the replacement.
|