I am using the below code after creating my connection, "mydb" with my MySQL server to import the data into R and it's working fine.
my_data <- dbReadTable(mydb, "ar_data")
But I don't want to import or read the whole table, I just don't want to read first 5 columns. How can I do that ?
Maybe try dbSendQuery:
library(DBI)
library(RMySQL)
drv <- dbDriver("MySQL")
con <- dbConnect (drv, dbname="mydb", user="username")
dbWriteTable(con, "mtcars", mtcars)
dbReadTable(con, "mtcars") # full table
sql <- paste0("SELECT ", paste(dbListFields(con, "mtcars")[-(1:5)], collapse=","), " FROM mtcars LIMIT 5")
res <- dbSendQuery(con, sql)
dbFetch(res)
# drat wt qsec vs am gear carb
# 1 3.90 2.620 16.46 0 1 4 4
# 2 3.90 2.875 17.02 0 1 4 4
# 3 3.85 2.320 18.61 1 1 4 1
# 4 3.08 3.215 19.44 1 0 3 1
# 5 3.15 3.440 17.02 0 0 3 2
dbClearResult(res)
res <- dbSendQuery(con, 'DROP TABLE mtcars')
dbDisconnect(con)