So a customer is inquiring about importing in historical data from their old system into ignition. Im not talking about a few thousand lines of data, I would guess millions of rows of data that would need to be added to the Ignition taghistory tables.
I guess it would be possible to get this data into a database(its csv right now), then thin it out by pulling 1 sample per datapoint per day, then work from there.
Would it be possible to write a script of something that can look at this data and write it into the taghistory tables?
Yes, it would be possible. If you already have a CSV file it would be pretty easy. The only problem is that it might take a while to complete if you have millions of rows. If you have a small sample we can help you write the script.
If the data is in CSV, you could definitely use some sort of scripting to bring it in. I can work with you to explain the table structures and how I would go about it, or perhaps if you send me a sample of the CSV, I can help with the script.
Before that though, just a quick question: if it were possible to interface with the current system, would it still make sense/be preferable to import it? That is, imagine that you could access that history through the same mechanism that you accessed tag history currently: you could browse for the tags from a “historical provider”, and use them on charts, tables, scripting, etc. mixed in with SQLTags History. I’m just curious, because we’ll shortly be able to write these types of modules.
Yeah I am worried about doing the import thru ignition. I have done some with a few thousand rows and it takes quite a while(20-30 minutes) using the csv parsing script.
I guess my first question if we use the import option thru scripting, what kind of performance hit would we take by writing this data into the tag history tables while the system is live? would we run into any deadlocks or anything?
Colby, we dont really have that option in this situation, since a 3rd party was hosting the data. I dont think it is a bad idea though. I may be looking at something similar pretty soon to access wonderware tag data. kepware makes a wonderware to opc driver that i may use to access tag data from a wonderware system without actually pulling the plug on it.
I’m sure we can get it to go quite a bit quicker than that. I would guess at least 100 values per second, up to 1000. However, I personally would probably do it outside of Ignition, directly with python… but with the transaction functions available now, it could probably also be done in the client.
As for table locking, I don’t think there will be an issue. I would recommend importing all of the data into it’s own “partition” (table) anyhow, so it won’t touch the tables that are currently in use. Do the tags in this system correspond directly to tags in the new system? That is, is it expected that you’ll be able to query “tag a” and get some data stored by sqltags history and some data imported from this system? I don’t really think it’s a problem either way, but it would affect how I suggest laying out the tables.
yes, the data will correspond to tags currently in the system, although their may need to be some massaging of the data to do the correlation between tagnames in our system and tagnames in the previous system. Yes, we would like to be able to have the data show up in taghistory.
We would like to migrate FTView SE historian (MSSQL2016) data to Ignition historian.
We are following below steps.
Identified the tags and list out the Tagid vs TagIndex (FTVIEW History)
Sample data for June 2017 converted (MSSQL datatime to unix ) and replaced the tag index by Tag id.
Created monthly partition in the Ignition DB and added partition information in the partition table.
However we are not able to see the Jun 2017 data in the Ignition. We are getting only one row with bad overlay.
Please advise.
FYI : Ignition system database is MSSQL 2019.
There are multiple things that need to line up in order for Ignition to query the data successfully:
Each data table must exist in the sqlth_partitions table with the correct start time and end time and drvid. Should look like the ones Ignition already created but with different pname and times.
The data table should contain values that map to proper tag ids in the sqlth_te table
The tag in the sqlth_te table needs to match up to the proper scid from sqlth_scinfo table. It also should have a createdtime that is before all of the actual data. If the scid is exempt, you don’t need to worry about the sce table. If not, look at the next item.
If the tag is not excempt from scan class, you need to add a row to the sqlth_sce table with the start and end time for the month (or time period) and the correct rate.
The tag should belong to the right driver. Tag points to scid from sqlth_scinfo which points to drvid (same as partitions).
You can use our system.tag.storeTagHistory function for an old time period and we will create everything for you. You can verify against that if you want. Let me know if you have further questions.
Ohh, regarding this, I have some questions. I noticed that the mentioned function works probably in a none desired way when having remote history provider. Let me explain.
Say that Gateway A has a DB connection with a tag getting recording data from a PLC. A is connected via Gateway Network with Gateway B, this has a remote history provider configured from A that allows writing.
A rainy day, de PLC goes down and data was not recorded for a time window.
Users in Gateway B wants to fill this empty span and push data to A using system.tag.storeTagHistory().
In 8.1.11, additional tables gets created as shown
This is causing that, for the same tag, history gets splitted into separate tables.
Can you replicate this? Is this a desired/expected behavior?
Unfortunately, that is intended behavior. The 2 in the table name denotes the driver or originating system. Since you are calling the storeTagHistory from Gateway B, you will have a different driver id than Gateway A. We currently don’t have a way to control the system id in the storeTagHistory function. I will add a feature request. You could send the data to Gateway A and have A call the storeTagHistory function to get around this issue for now.
What is the best way to go about moving a Wonderware Historian into an Ignition Historian? I can get this data into a CSV. I also have it in the dbo tables in MSSQL. I only have about 135 tags, but about 15 years worth of data. Is there a script that got written to assist with this?
None that anyone seems willing to admit. But the structures aren't rocket science. A good DBA should be able to figure out a SQL ETL operation that does what you need. You'll want to start history on the tags in Ignition so you have tag names to correlate against.
There is a scripting call to do this now. I havent used it but it seems simple enough.
system.tag.storeTagHistory
Id probably massage the data to get it to more closely match what Ignition is expecting. Maybe put an extra column in the data to mark as processed and only do a batch of 1000 at a time to not overload the system. You may also want to think out the data and only do a sample an hour or per day for each tag or something along those lines.
