I am querying the most recent partition table ([sqlt_data_1_2023_07]) from Tag Historian database. I plan to run this query programmatically but not sure how to keep track of the most recent partition. If data starts getting saved in _08 partition in the future then this code will break at that time. Any suggestions on how to find the latest partition?
The architecture of Ignition's historian tables is documented here:
https://docs.inductiveautomation.com/display/DOC81/Ignition+Database+Table+Reference
But, you should not be accessing these tables yourself. The Right Answer™ is to use the provided system.tag.query* scripting functions and let Ignition manage its partitions however it wants. This protects you from breaking changes in the future.
1 Like
Thank you. I actually have a backend worker project in C# that primarily runs multiple background tasks (non-ignition related) but also wanted to add some ignition related task. So I wanted to access the data from ignition database for this ignition related task.
Any successor in your position will hate you. You should strongly consider doing your Ignition-related tasks in Ignition in a gateway event script.
1 Like
Thank you for the feedback. So if we have non ignition systems that need only few bits of information from ignition then is there any recommendation? It is not ignition task but an integration task that talks to multiple systems and want to also access specific ignition data, if possible.
The supported way for outside systems to get information from Ignition is to use the WebDev module to implement a web service. The jython scripts backing the web service can call Ignition's scripting API for any desired purpose (including writing into Ignition) without concern over Ignition's implementation details.
1 Like
That is very good option. Thank you
Scripting is so slow though.
Is there not a way to use a named query and still get the data from the partitions?
Or at least the most recent partition consistently?
I would prefer an alternative so I could leverage SQL speed without building my own tables.
How would you do this? Named queries are not the answer for everything, and you need some type of interface between ignition and the SQL server.
This isn't a trivial problem to solve.
2 Likes
SELECT TOP (1) [pname] FROM [sqlth_partitions] ORDER BY end_time DESC
Gets you the latest partition, assuming you don't have multiple tag sources feeding into the same database.
Then just use the result of that to pass the table name to any queries you want to execute.
Of course then you get into tag names, converting start_time and end_time to usable formats, managing retired tags, writing your own calculations for tags, etc etc.
My best guess, a tableID column instead of a time based table name.
It is probably a terrible idea though with mandatory pruning implications.
I will use the scripting way.
There's no assurance that a custom solution will receive support or remain unaffected by unforeseen runtime intricacies. Future updates of Ignition may break it as well.
As long as execution speed of provided solutions are tolerable for your application the robustness they provide should be a priority.
I am not worried about that. The historian breaks on me in the reports.
Might not now that I am on 8.1.30 though.