CODEWITHSUNDEEP
phpdatabasemariadb
rocket_doge_
rocket_doge_

Reputation: 185

MariaDB Update Table Contents From Changing File

I have the following problem:
I have got a dataset inside a text file (not xml or csv encoded or something, just field values separated by \t and \n) which is updated every 2 minutes. I need to put the data from the file into a MariaDB Database, which itself is not very difficult to do.
What I am unsure about however, is how I would go about updating the table if the file's contents change. I thought about truncating the table and then filling it again, but doing that every 2 minutes with about 1000 datasets would mean some nasty problems with the database being incomplete during those updates, which makes it not a usable solution (which it wouldn't have been with fewer datasets either :D)
Another solution I thought about was to append the new data to the existing table, and use a delimter on the unique column (e.g. use cols 1-1000 before update, append data, then use values 1001-2000 after the update and remove 1-1000, after 2 or so updates start at id 1 again).
Updating the changing fields is not an option, because the raw data format would make that really difficult to keep track of the column that has changed (or hasn't)
I am, however unsure about best practices, as I am relatively new to SQL and stuff, and would like to hear your opinion, maybe I am just overlooking something obvious...

Upvotes: 0

Views: 230

Answers (2)

Rick James
Rick James

Reputation: 142356

Even better...

CREATE TABLE new LIKE real;   -- permanent, not TEMPORARY
load `new` from the incoming data
RENAME TABLE real TO old, new TO real;
DROP TABLE old.

Advantages:

  • The table real is never invisible, nor empty, to the application.
  • The RENAME is "instantaneous" and "atomic".

Upvotes: 1

rocket_doge_
rocket_doge_

Reputation: 185

As suggested by Alex, I will create a temporary table, insert my data into the temporary table, truncate the production table and then insert from the temporary table. Works like a charm!

Upvotes: 1

Related Questions