I have an established connection with MySQL hosted on AWS. Currently for bulk uploads I am using the DBI dbWriteTable function. The downside is, it takes 5 minutes to insert a data.frame that has 10k observations. I have decided to try and use LOAD DATA INFILE query to reduce the time it takes to upload a bulk data frame. However, I am getting an error telling me my current user id is denied from doing such commands.
Below is the code that I have tried followed by the error message.
library(RMariaDB)
library(DBI)
con = dbConnect(MariaDB(),
dbname = "testing123",
host = "********",
user = "*****",
password = "******",
port = 3306
)
dbWriteTable(con, "mt", mtcars)
##create a data frame that has about 6000 rows
mtcars.extended = cbind(mtcars, rep(unname(unlist(mtcars)), 20))
#use the standard dbwritetable to append the rows
dbWriteTable(con, "mt", mtcars, append = T)
###This took 5 minutes but worked
##So I decided to save mtcars.extended as a csv and try the bulk
upload
write.table(mtcars.extended, "mtext.csv")
query = "LOAD DATA INFILE 'mtext.csv' INTO TABLE mt"
dbGetQuery(con, query)
Error in result_create([email protected], statement, is_statement) :
Error executing query: Access denied for user 'testing123'@'%'
(using password: YES)
dbSendStatement(con, query)
Error in result_create([email protected], statement, is_statement) :
Error executing query: Access denied for user 'testing123'@'%'
(using password: YES)
What I would have liked to see happen is a relatively quick insert into the data table "mt" without any overwriting. But it seems AWS is not allowing me to do bulk uploads.
Thanks to Matus, I solved the issue. The only problem is, I am getting NA 's when bulk uploading.
query = "LOAD DATA LOCAL INFILE 'mt.csv' INTO TABLE mtcars"
