Hi,
I have to move a local tag provider to a remote tag provider. But I cant figure out how to keep the tag history intact.
All help is gratefully received☺️
Stian
The only way I know is to edit the records so that records tied to the old TagId point to the new TagId.
In editing the tag ids, I'd be concerned about not retrieving history from these old records since the date range in your new tags will start after the old history was created. I would rather rename the tag path in the table for the old tags. You'll also have to relink them to the new tag provider as well. Definitely something to test in a dev gw first
Umm, isn't that a given? It's not a new tag, its the same tag moved to a new location. the old Id will have already been retired (at least it should have been), so telling the system that the old data belongs to the new tag seems like the better approach. IMO
But the datetimes of the old tag's data will have timestamps before the start date of the new tag
Edit:
Nevermind, changing tagids in the sqlt_data tables should be ok. I just wonder if you need to change the created date of the new tag as well or if that's just not looked at when querying for data
When moving a tag to different folder in the same provider, the old location tag path will have a retired timesamp in _TE table and the new tag path will have a new id. And for keeping the tag history I only need to edit the old tag path matching the new.
But when moving to a new provider, the new tag path in _TE table haveing a new id, also have a new scid.
Do you mean changing all the table names, and scid?
He means just updating the tagid fields in the sqlt_data_... tables to the id of the new tag path in the sqlth_te table.
The data tables are not tag-provider-aware (or rather, they're made aware of them with the link back to the tag) and hence moving the tagid to a new one isn't going to matter if the new tag is in a different tag provider
I will do a test tomorrow.
Are the queries that Ignition uses for history documented somewhere?
Not that I know of
One word of caution, if you change the tagid values in the sqlt_data_... tables, you will lose any related information associated with that tag in regards to scan class info/execution as that is all stored in sqlth_scinfo and sqlth_sce tables. You may also now have records in your sqlt_data_... tables that are older than the execution records in sqlth_sce since it is now associated with a new scan class id.
sqlth_sce
A record of executions of scan classes, defined insqlth_scinfo. This table is primarily used to determine when the system was running correctly. The end_time of an entry is updated, as long as it falls within 2*rate of the last execution. If not, a new entry is made, and data queried will be "stale" during the gap.
I think it's likely that this also impacts tag history calculations, but I'm not certain about that.
Personally, instead of changing the tagid values in the sqlt_data_... tables, I would change the tagpath values in the sqlth_te table to match the new tagpath and then adjust the sqlth_scinfo values (and sqlth_sce table if necessary). Depending on whether or not the scan class info still needs to be related to the old provider, it may necessitate duplicating scinfo and sce records and associate them with the new provider. I know this is a little convoluted. It might not be worth the effort for you, but I wanted to at least call out the pitfalls of changing tagid values in the sqlt_data_... tables.
Edit: I forgot to add, if you are changing the provider, then your drivid will change, so if you change tagid values in the sqlt_data tables, you will likely end up with partitions holding data that do not match the partitions for the new provider (records in the sqlth_partitions table). You have this same problem if changing the tagpath values. It's a headache to properly adjust when moving data between providers...
3 Likes
Hi @lrose
I need to move a tag too (same provider though).
By "edit the records so that records tied to the old TagId point to the new TagId." do you mean do an update query on the sqlth tables?
Which tables would you go with editing?
Thanks
Cameron
Nope, you need to update all sqlt_data_x tables and update the tagid field.
For example, if you move a tag and it changes from id 1400 to id 7300, then you would run this:
UPDATE
sqlt_data_1_2022_12 -- do this for all sqlt_data_x tables that you have
SET
tagid = 7300
WHERE
tagid = 1400
Via script, you could use something like this:
tagid_old = 15000000
tagid_new = 16000000
query = '''
SELECT
TABLE_NAME
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_NAME LIKE 'sqlt_data%'
'''
tables = system.db.runPrepQuery(query)
tables = [row['TABLE_NAME'] for row in tables]
tx = system.db.beginTransaction()
for table in tables:
query = '''
UPDATE
{}
SET
tagid = ?
WHERE
tagid = ?
'''.format(table)
ret = system.db.runPrepUpdate(query, [tagid_new, tagid_old], tx=tx)
print table, 'rows changed:', ret
#system.db.commitTransaction(tx)
system.db.rollbackTransaction(tx) # comment this out and uncomment the commit above to actually apply the changes
system.db.closeTransaction(tx)
This will print the number of rows changed for each table (I'm using daily partitions for this test gateway):
sqlt_data_1_20221217 rows changed: 287
sqlt_data_1_20221218 rows changed: 288
sqlt_data_1_20221219 rows changed: 287
sqlt_data_1_20221220 rows changed: 216
EDIT:
As discussed below, you also need to update the created date of the affected tags as well in the sqth_te table
1 Like
Keep in mind that if you go about changing tagid's in the sqlt_data_ tables, you need to make sure you change the created value for the new record in sqlth_te. In the example above, you would want to change the created value for tagid = 7300 to match the created value for tagid = 1400.
Note that I would still advise against changing tagid values in the sqlt_data_ tables. If your tag provider has not changed, it's much simpler to change the tagpath value in sqlth_te for the old tagid to match the tagpath in the new tagid. It also preserves the scan class information for that tag in case the new copy of the tag is using a different scan class.
I would run the following query based on tagpath rather than tagid because there is always a high probability that you have multiple rows in sqlth_te with the same tagpath value (different created/retired values) and would want to update all of them accordingly (Disclaimer: For others reading this post in the future, please see my post further above regarding the pitfalls with changing providers if you are moving tags between providers.) As always, make sure you have DB backups before trying to do any changes to these tables as you can very quickly get yourself into a state that is not easy to recover from without a backup:
UPDATE
sqlth_te old_tag
INNER JOIN sqlth_te new_tag ON (new_tag.tagpath = '<new_tagpath_here>' AND old_tag.tagpath = '<old_tagpath_here>')
SET
old_tag.tagpath = new_tag.tagpath,
old_tag.retired = COALESCE(old_tag.retired, new_tag.created - 1);
Not the most elegant query, but it works.
Edit 1: Changed the query to be tagpath based instead of tagid and added a note about backing up the DB before trying to make any of these changes.
Edit 2: Fixed the query. I was rushing when I converted the query to be tagpath based and didn't take into account that some records will already have a retired value.
1 Like
I actually didn't find any issues with just changing the tag ids in the data and not changing the created date of the corresponding tag. This was actually my original concern when @lrose suggested it, but when I tested it, it just worked. It's also happens to be the simplest method to move the history data across
These posts:
Edit: Actually I just read one of your later posts RE scan classes which I didn't consider. This is certainly a valid consideration
If created values are not updated, it can break the Easy Chart in Vision. My best guess is that Ignition uses those created/retired values as a way to cheat and determine which sqlt_data_ partitions they should be querying for the relevant tag data. If your Easy Chart date selection bridges over the created time for the tag, it will show everything up to the start of the relevant partition, but it will not show anything from prior partitions because it assumes, based on the created value that there is no data in the older partitions.
Here is an example. I have data for this particular tag going all the way back to 2020. All I did was change the created value to March 22nd, 2022 - nothing else changed. You can see that this drives the red highlighting in the bottom range, and the data stops at March 1st, 2022 because I have partitions set to monthly and it doesn't try to look at older partitions.
The only thing different in the screenshot below is that I changed the end date of the range to a time prior to March 22nd, 2022. It shows nothing on the screen even though you can see in the screenshot above there was data going back to at least the 1st of March (in reality, all the way back to 2020):
2 Likes
I just ran a test and confirmed that the created value appears to impact the system.tag.queryTag* functions too...
1 Like
Maybe i'm remembering wrong then... :S
@WillMT10 and @nminchin Thanks humans 
That is a lot clearer now for which things to change and why in terms of impact.
I am happy!
Testing will occur.
Cameron
Hello again humans...
I'm getting the following error running the query adapted from @WillMT10 ;
Blockquote
GatewayException: SQL error for "UPDATE sqlth_te old_tag
INNER JOIN sqlth_te new_tag ON
(new_tag.tagpath = '<new_tag_path>' AND
(old_tag.tagpath = '<old_tag_path_1>'
OR old_tag.tagpath = '<old_tag_path_2>'
OR old_tag.tagpath = '<old_tag_path_3>')
)
SET old_tag.tagpath = new_tag.tagpath, old_tag.retired = COALESCE(old_tag.retired, new_tag.created -1)
": Incorrect syntax near 'old_tag'.
caused by SQLServerException: Incorrect syntax near 'old_tag'.
Phrased as a SELECT query (without the "SET..." part), it returns what I'd expect, so I think it must be an issue with the SET section.
But I don't know what.
Halp?
Thanks