0
I am designing an SQL database that will have tables that store collected data and a table that stores calculated indexes of this data to be later exposed on a Dashboard. I intend to calculate these indexes with the language R. I would like to know two things:
1 - How do I pull data from my SQL data tables to R to perform the calculations?
2 - How do I store the results of my calculations in R in my SQL table of indexes?
3
Dude you use the package RODBC, then just make the consultation:
#testa se vc tem o pacote, caso não instala
list.of.packages <- c("RODBC")
new.packages <- list.of.packages[!(list.of.packages %in% installed.packages()[,"Package"])]
if(length(new.packages)) install.packages(new.packages)
library(RODBC)
dbhandle <- odbcDriverConnect('driver={SQL Server};server=mysqlhost;database=mydbname;trusted_connection=true')
res <- sqlQuery(dbhandle, 'select * from information_schema.tables')
Just to complement, if you are using another DBMS other than the SQL Server example above, you will need to search the connection strings, because each DBMS has its particularities in the connections. The best site for this is the www.connectionstrings.com.
Browser other questions tagged mysql sql r
You are not signed in. Login or sign up in order to post.
Look here: https://github.com/rstats-db/RMySQL
– Daniel Falbel
Which database system are you using? you need to know if there is a driver for the system you are using. If the driver exists, just look at the documentation how to make updates with it, or rewrite tables with it. The dplyr people did a backend where they could do manipulations with dplyr itself, using a database from behind. It might be nice to take a look. But it depends on your system and the existence of a driver.
– Guilherme Marthe
In linux I am testing in Mysql and in Windows I am using SQL Server 2016.
– Bruno Rigueti