We are finishing up a conversion from iFIX to IGNITION. The last task is figuring out how to import 18 years of iHistorian data into Ignition SQL data. We have done a little research and need some direction on best methods.
An acceptable option may be 5 years of data.
30 second data is 1M records per year for 1900 tags. 10 min avg is 52,560 records per year.
We may need to recover history on 500 critical tags.
There are some forum threads from 2012-2013, but no significant recent discussion.
I am a decent SQL query writer and I believe I understand the sqlt and sqlth files. I am able to write JOINs on them.
History was configured in 1 week partitions. Do I have to maintain that table interval? Can I dump 10 minute average data into a year partition? I am trying to reduce the amount of manual conversion tasks - 1 year is better than 52 weeks.
Yes, iFIX Historian has the ability to export raw and filtered (averaged) data to CSV, but excel or the iFIX plugin breaks somewhere past 50,000 records. 1 year at 10 min average is 52,560 records.
Should be able to do SQL BULK INSERT from CSV. Is there a better way?
The excel CSV intermediate step seems to have limitations.
History was configured in 1 week partitions. Do I have to maintain that table interval? Can I dump 10 minute average data into a year partition? I am trying to reduce the amount of manual conversion tasks - 1 year is better than 52 weeks.
No, Ignition would be able to use whatever table size you specify. The range of data (as well as the table name) is defined in sqlth_partitions.
Should be able to do SQL BULK INSERT from CSV. Is there a better way?
Importing the data directly into the database may not be the best option - there will be almost certainly be manipulation required to get the table structure to line up with what Ignition expects.
What advice on automating this conversion?
There's three basic steps behind adding old data into the historian.
First is matching the actual data table structure - so a tagid, timestamp, separate columns for float, int, string, and a quality code. If you have raw data already, then the CSV import @mazeyrat linked above would be a good starting point.
The second step is creating the additional rows in sqlth_partitions necessary to define the range of data your new tables hold. You'll need to match up drv_ids, enter correct start and end times, and enter the table names.
The final step, probably the most complex, is matching the tagids from sqlth_te with however the tags were identified in the old data. There's no certain way to do this - you'll have to query sqlth_te for each active tag instance (WHERE retired IS NOT NULL) and match the Ignition tag names and paths with the tags from your old system. Then the data tables you're dropping in will need the same tagids.
The Blog reference from mazeyrat looks like a great starting point, it greatly streamlines the SQL import process once you get CSV files. I will download Travis’ CSVtoTagConvertor and work through the process.
Yes the old tagnames require translation to make any sense in a named tagpath but we will work through that.
I realize this is a couple years old, but I have a similar need (migrate several years of iHistorian data), and the blog post is currently MIA. Does anyone have this info archived anywhere?
@jharrell I realize your response on this post that’s a couple years old is a couple years old. Did you find a way to convert iFix historian files into Ignition historian?
I haven’t done it yet, but its still on the list somewhere.
I was able to find an archived copy of that post though, which might be useful for you.
The basics of what I was planning was (similar to the blog post):
Build a lookup table of iFix tag to Ignition Tag Path (our structure in iFix makes it pretty easy)
Let Ignition query the iHistorian SQL interface directly for a block of time for a specific tag (either at some useful interval or every raw sample).
2a. If that proved problematic, then one alternative is to build an intermediate csv with the Excel Add-In (slow though) or SQL scripting against iHistorian running on one of the iFix nodes building CSVs that way.
Use the system.tag.storeTagHistory function to get it into the historian (formatting issues in the blog post to be aware of)
Keep looping through the tags for that time block, and then loop forward in time, etc etc.
The process data I was looking to import at the time is also now rolled up into a separate rollup table, and going back past the year+ we already have collected natively is making this effort slowly drop down the list in priority, but I do still need to get it done just so I can turn iHistorian off finally. The iFix system has about 20,000 tags archived at sub 5 minutes for the last 9-10 years, so getting everything transferred will definitely be very time consuming and resource intensive (lots of processor time)