MySQL push to NoSQL?
I am seeking some advice from any one with more database experience than I (which would be almost anyone). ;)
At the moment I have Mango (ver 2.7.2) using its NoSQL database for data storage. Our data loggers feed into a proprietary MySQl database and I have Mango getting the data from those tables on a time interval, i,.e. every 5 minutes.
There are occasions when the data from the data loggers will update faster than every 5 minutes. So I am trying to set a trigger from our initial MySQL database tables that would insert the data into Mango.
You probably see my problem, with Mango using NoSQL. Can you tell me how to write the MySQL statement to go to the proper data location in the NoSQL database or even if that is possible.
For example: I use this trigger to move the latest historical readings in another table:
AFTER INSERT ON 'historical' FOR EACH ROW
insert into historical_copy
select * from historical order by historial_id desc limit 1;
Any advice would be greatly appreciated. Please keep in mind I am an SQL novice.
JoelHaggar last edited by
There isn't a way to push data into the Mango NoSQL database directly. Your only option would be to use the REST API for this.
Would it also be possible to simply speed up your polling of the Data Source. You could have your logging setting to log when timestamp changes perhaps.
You could also have a second SQL Data Source polling faster just for the data points you need quicker.
Thank you for your reply Joel,
I think I will try to get Mango to read on the time stamp change. Another reason is because the data that is coming in faster is actually back logged data from periodic communications drop outs. Getting Mango to record the actual time stamp of the incoming data would solve the problem of Mango reading old data but giving it a new server timestamp.
Thank you for your advice.
I have been trying to set the data source / point to update when the timestamp changes in the MySQL table. I need to use the timestamps from the reports table.
I can see that the timestamps in the watchlist matches the timestamp from the MySQL table.
However the time stamps that are listed in the spread sheet downloaded from the watchlist does not match what is displayed on the watchlist web page.
Each data point in the spread sheet has a difference of about 30 seconds.
I also then checked the history recorded in the data points details page and found that they don't match the watchlist either.
The data is first brought into Mango via an SQL data source where I have the data points using a "Time override column" that points to the data column in the originating MySQL table. I also have the data points set so that the "Logging type" is set to "When point timestamp changes"
Then we use a Meta Data Source so we can scale the SQL data points into meaningful data. I have tried simply keeping the logging interval to a set 15 minutes because that is the time the data will normally come into the database. I also had the "Update Even set to "Start of Minute". But I found that the data points had data for every minute in the history even thought the logging was set to 15 Minute Interval.
So I changed the Update Event to "Context Update" and added a new context above the script box for the timestamp.
Please let me know if I have the is set up incorrectly or if there are any other changes I need to make to get the original data timings from the MySQL table.