I have connected to a database using pool package. I need to transmute a column and I get an error.
library (pool)
library (dplyr)
library (RMySQL)
my_db <- dbPool(
MySQL(),
donate = "aaa",
host =" localhost",
username = "root"
)
my_db %>% tbl("bbb") %>%
mutate(Date = paste(Years, Q)) %>%
transmute(Date = factor(Date),
Product)
The error message I get:
#Error in . local(connect, statement, ...)
# could not run statement: FUNCTION aaa. factor does not exist
EDIT ONE
I have found multiple answer that requiere that I create a function to apply the require SQL querry. But I have not knowledge of SQL(that's a problem I have been avoiding). After to much looking and trying I came up with this:
library (pool)
library (dplyr)
library (RMySQL)
my_db <- dbPool(
MySQL(),
donate = "aaa",
host =" localhost",
username = "root"
)
my_db %>% tbl("bbb") %>% as.data.frame() %>%
mutate(Date = paste(Years, Q)) %>%
transmute(Date = factor(Date),
Product)
I'm not sure if this is a solution. The goal of using the pool package is to do not need to load the table in my memory and I think that is exactly what as.data.frame() do.
Thanks in advance for helping