Is there an efficient way to update MariaDB rows from a data frame in R?

Question

I am collecting live data from a website which populates a data frame in R. The rows could have the same unique id's, or new rows could be introduced. I want to send the dynamic data frame to a MariaDB database table, where the rows with existing unique id's update the columns that I have specified, rows without existing unique id's get inserted in the table as new rows. I can get this to work with the MariaDB INSERT ON DUPLICATE KEY UPDATE statement, and a function that generates the needed values from the dynamic data frame.

MWE:

install.packages("odbc")
insall.packages("RMariaDB")
library(odbc)
library(RMariaDB)

con <- dbConnect(RMariaDB::MariaDB(), host = Sys.getenv('MARIADB_DBHOST'), 
port = Sys.getenv('MARIADB_DBPORT'), user = Sys.getenv('MARIADB_DBUSER'), 
password = Sys.getenv('MARIADB_DBPW'), db = Sys.getenv('MARIADB_DBNAME'), 
timeout = 5)

# Database table for mwe to work.
db_live <- data.frame(id = c(12, 22, 32), car_name = c("rolls royce","nissan","mercedes benz"), km = c(123,100,150), temp = c(78,60,80))


# Get table from database, id column is unique index.
db_live <- dbReadTable(con, "db_live")
print(db_live)
  id      car_name  km temp
1 12    rols royce 123   78
2 22        nissan 100   60
3 32 mercedes benz 150   80

# Build dynamic dataframe
df_live <- data.frame(id = c(12, 22, 32, 42), 
car_name = c("rolls royce","nissan","mercedes benz", "aston martin"),
km = c(140,120,200,40), temp = c(81,65,85,50))

print(df_live)
  id      car_name  km temp
1 12    rols royce 140   81
2 22        nissan 120   65
3 32 mercedes benz 200   85
4 42  aston martin  40   50

# create function that generates a string with values for dbSendQuery.
gen_insert_values <- function(df) {
for(i in 1:nrow(df)) {
row_string <- paste(df[i,1], paste0("'",df[i,2],"'"), df[i,3], df[i,4], 
collapse = ", ")

if(exists("df_string")) {
  df_string <- paste0(df_string,", ",paste0("(",row_string,")"))
} else {
  df_string <- paste0("(",row_string,")")
}
}
df_string
}

values <- gen_insert_values(df_live)

print(values)
"(12 'rolls royce' 140 81), (22 'nissan' 120 65), (32 'mercedes benz' 200 85), (42 'aston martin' 40 50)"

# Send query.
res <- dbSendQuery(con, paste0("INSERT INTO db_live (id,car_name,km,temp) VALUES ", values," ON DUPLICATE KEY UPDATE km = VALUES(km), temp = VALUES(temp);"))
dbClearResult(res)

#Check db table after sent query.

new_db_live <- dbReadTable(con, "db_live")

print(new_db_live)
  id      car_name  km temp
1 12   rolls royce 140   81
2 22        nissan 120   65
3 32 mercedes benz 200   85
4 42  aston martin  40   50

This does not seem very efficient, as I have to change the query and the function in case I want to update more columns, and I include a for loop in my function which can cause the script to be slow.

Is there a more efficient way to solve this problem?

If you get errors, please include the literal error text. Sometimes they're not informative, sometimes they indicate a different problem (e.g., parsing error, typo, wrong table, etc). — r2evans, Jun 28 '21 at 16:29
@r2evans, I added a MWE which works, so there is no error text. I am looking for a more efficient solution to the problem. — Nowak, Jun 29 '21 at 17:48
In the first version of your question, you stated *"I get syntax errors and it didn't seem very efficient"*, which is why I was asking for the literal text of the errors. — r2evans, Jun 29 '21 at 17:52
(1) Correct me if I'm wrong, but when inserting literal values, aren't they comma-delimited? I'd expect `(12 'rolls royce' 140 81)` to instead be `(12, 'rolls royce', 140, 81)`. (2) Regardless, that's a really inefficient way to do it, why not insert the data into a (temporary) table and work from there? — r2evans, Jun 29 '21 at 17:54

r2evans · Accepted Answer · 2021-06-29T19:13:22.303

Here's a method that is likely more efficient: the use of a temporary table instead of manually encoding the data as a string of (a,b,c),(a,b,c) data sets.

For the sake of complete-demonstration, I've modified the df_live data slightly so that we have one row with no change, one row with updated data, and one row that is new. This process also works unaltered with your original df_live, I just wanted to highlight the three modes.

Technically, though, the "no change" row does update the database, but it is not obvious. If the table has a "lastmodified" field that updates with the current timestamp when something in the row is updated, then you can see a little more of what is happening.

In fact, I'll add (just for demonstration) two fields: created and modified, which show when the row was first created and when the last update occurred. These are not required for normal UPSERTs.

Setup

This section should not be necessary, unless you don't have a primary key(s) on the table (in which case, add one).

I'll name the main table "mydata", and upload the db_live dataset into it. I believe (without extensive testing) that MariaDB requires UPSERTs to find the duplicate or conflict rows based on pre-existing keys. This means we'll need to set a (primary) key; I'll assume your table already has this (and show how I do it with manually-uploaded data).

db <- DBI::dbConnect(RMariaDB::MariaDB(), ...)
db_live <- data.frame(id = c(12, 22, 32),
                      car_name = c("rolls royce","nissan","mercedes benz"),
                      km = c(123,100,150), temp = c(78,60,80))
df_live <- data.frame(id = c(12, 22, 42), 
                      car_name = c("rolls royce","nissan","aston martin"),
                      km = c(140,120,40), temp = c(81,65,50))
df_live
#   id     car_name  km temp
# 1 12  rolls royce 140   81   # updated
# 2 22       nissan 100   60   # no change
# 3 42 aston martin  40   50   # new data

DBI::dbWriteTable(db, "mydata", db_live)
DBI::dbExecute(db, "alter table mydata add primary key (id)")
# [1] 0
DBI::dbExecute(db, "
  alter table mydata
    add column created timestamp not null default CURRENT_TIMESTAMP,
    add column modified timestamp null default null")
# [1] 0
DBI::dbExecute(db, "
  create trigger updatemodified_mydata
  before update on mydata
  for each row set NEW.modified = CURRENT_TIMESTAMP")
# [1] 0

DBI::dbGetQuery(db, "select * from mydata")
#   id      car_name  km temp             created modified
# 1 12   rolls royce 123   78 2021-06-29 19:11:00     <NA>
# 2 22        nissan 100   60 2021-06-29 19:11:00     <NA>
# 3 32 mercedes benz 150   80 2021-06-29 19:11:00     <NA>

If you don't have a primary key on the main table mydata, then the "UPSERT" operation will simply insert (add) all rows without updating. I don't know if there's a way to trick mariadb into faking keys for the purpose of correctly triggering your intended "update if exists" logic.

UPSERT

We'll use a temporary table so that the data to be upserted will not persist; this is good for several reasons, and if you do it correctly then your DBAs will thank you :-)

(If you aren't familiar with temp tables ... they are not visible to other users on the db, often not visible to different connections for the same user, and will be dropped when the connection is closed.)

DBI::dbCreateTable(db, "mytemp", df_live, temporary = TRUE)
DBI::dbWriteTable(db, "mytemp", df_live, append = TRUE, create = FALSE)
DBI::dbGetQuery(db, "select * from mytemp")
#   id     car_name  km temp
# 1 12  rolls royce 140   81
# 2 22       nissan 100   60
# 3 42 aston martin  40   50

DBI::dbExecute(db, "
  insert into mydata (id,car_name,km,temp)
  select id,car_name,km,temp from mytemp
  on duplicate key update km=VALUES(km), temp=VALUES(temp);")
# [1] 5

DBI::dbGetQuery(db, "select * from mydata")
#   id      car_name  km temp             created            modified
# 1 12   rolls royce 140   81 2021-06-29 19:11:00 2021-06-29 19:11:27
# 2 22        nissan 100   60 2021-06-29 19:11:00 2021-06-29 19:11:27
# 3 32 mercedes benz 150   80 2021-06-29 19:11:00                <NA>
# 4 42  aston martin  40   50 2021-06-29 19:11:27                <NA>

If you note, even though the values for "nissan" were not different, the row was still allegedly updated as evidenced by the modified timestamp. The "change" row we had, "rolls royce", shows the appropriate modified time. The mercedes benz was uploaded the first time and not updated, and the aston martin was updated the second time, so its created time is different than the others.

Reproduction

I did this with the mariadb:latest docker image. These steps below are purely for demonstration, and are not provided as the canonical way to manage a database (for security or performance). Yes I'm connecting to the "mysql" database, which is not where user data should be going, I believe ... it was hasty, please forgive me.

$ docker pull mariadb:latest
Using default tag: latest
latest: Pulling from library/mariadb
c549ccf8d472: Pull complete
26ea6552a462: Pull complete
329b1f41043f: Pull complete
9f8d09317d80: Pull complete
2bc055a5511d: Pull complete
e989e430508e: Pull complete
cdba2af19f87: Pull complete
04fe4f90eab8: Pull complete
389c6b423e31: Pull complete
bef640655d86: Pull complete
Digest: sha256:0c72b63198ac53df4e84db821876c73794b00509b2d8a77100d186a13e49ac31
Status: Downloaded newer image for mariadb:latest
docker.io/library/mariadb:latest

$ docker run -p 127.0.0.1:3306:3306  --name some-mariadb \
    -e MARIADB_ROOT_PASSWORD=mysecretpw -e MARIADB_DATABASE=mydb -d mariadb:latest

In R, connecting is straight-forward:

db <- DBI::dbConnect(RMariaDB::MariaDB(), host="127.0.0.1", port=3306, 
                     username="root", password="mysecretpw", dbname="mydb")
DBI::dbGetQuery(db, "select version() as dbver")
#                                   dbver
# 1 10.5.11-MariaDB-1:10.5.11+maria~focal

Is there an efficient way to update MariaDB rows from a data frame in R?

1 Answers1

Setup

UPSERT

Reproduction