Two Database in same project

Question

What approach should I follow to use two different type of databases in the same project for eg. MySql for transaction related queries and MonetDB for analysis purpose ?

Answer 1

You could keep your transactions in MySQL and periodically (e.g. every hour) move data over to MonetDB, e.g. using CSV export. For example, given a table sometable you could do the following in MySQL:

SELECT * FROM sometable
INTO OUTFILE '/tmp/export.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

And then in MonetDB:

DELETE FROM sometable;
COPY INTO sometable FROM '/tmp/export.csv' USING DELIMITERS ',','\n','"';

More elaborate setups could also just export the data added during the last day, and then just append on the MonetDB side.