Hello,
I’m currently using history provider to store my data on a mysql database.
My issue is the following : The table is reaching a size that is creating issues(15 Gb).
I saw that there is a way to partition the data so that the table doesn’t grow too much.
This option haven’t been used before because there is calculation that are specific and cannot be performed using ignition functions that forces me to read directly into that table.
To get an usable table i’ve created a second table that is partitionned by an integer that correspond to a site where the data is collected. To fill that table I used a trigger that would calculate that integer and insert it into that second table. That first table being generated by tag historian is that possible to make it so that trigger is created automatically?
Do you need to do external calculations on every tag that the historian stores? If not, consider using the SQL Bridge module’s transaction groups for tag data storage that you need to be compatible with external DB access. (If necessary, you can script the data storage instead of using transaction groups.)
Then you can turn on normal partitioning and pruning in Ignition, since the special data is then separate. Any route where Ignition is partitioning and you are attempting external access is likely to be buggy over time. IA’s historian’s structure, while not secret or rocket science, is an implementation detail. Future improvements might break your setup. If you design your own tables with transaction groups or scripts, that won’t happen.
I presume you mean to read directly out of that table.
Partitioning is designed to avoid the problem you are having. The tag history queries then only have to query and aggregate results from tables in the query date range.
Can you share what the calculation is and we can have a look to see if there is another way around your problem. Script transform on a named query comes to mind.
I’ve done some changes during time so the most complex ones are gone because before i had to integrate power level to get a hourly energy value. The next things i had was calculation of a maximum of a value per hour/days considering that if no value the value from previous hour was to be taken, the default value being 0.I’d have to check more the application to see if there is any others left because that was made a while ago.
All of that would probably be easier to code and read in Python. Get the basic dataset from your tag history using Ignition’s built-in functions and then transform it using a script.
Both system.tag.queryTagHistory and system.tag.queryTagCalculations allow you to provide custom python aggregates. That should allow you to perform your calculations, while still using the provided functions. This would make it so that there is no need to worry about crossing partitions.