MQTT and Ignition data questions

justin3 · November 6, 2025, 4:12pm

I will preface this by saying that I am just the data/infrastructure person working on trying to get a real-time feed of tag data for this start-up. The goal is to use some ML for predictive controls inputs and increase regulatory compliance among other things. I did not install or configure the Ignition system initially and I only provided the AWS infrastructure as requested by the folks that did.

Phase 1 is 1 site, Phase 2 will have at least two different sites and thus we need a way to split the data in the lake by site.

Goals -
Near real-time (every 60 seconds batched for all tags) data delivery
ML Model retrieves data and outputs a 2-minute prediction for specific points
Dynamic and automatic site data split and ingestion into the s3 Lake and the ML Models

Implementation -
Lambda pulls from Aurora Postgres Historian cross-account to the lake in s3 using a 1-minute schedule and a SQL query.

Issues -
Costs: IO from Aurora is not inexpensive
Multiple sqlt_data_#_yyyy_mm tables: For some reason there are 2 different tables getting data fed into them and each one has a different tagid in it. This is not insurmountable but the tagid being different for the same tagpath is problematic on the analytics side.
Real-time data in the future: While technically feasible the extra table feed (which seems to happen randomly) and latency additions will make this less desirable.

So, with those issues, even as minor as they are, I went looking for another option that looked like it would provide a less expensive per site option that would also provide that real-time feed eventually. Enter the MQTT feed. There is an Edge with the Transmission module (Cirrus I believe v 4.0.27) and then the IOT Core in AWS along with an EC2 instance with Ignition Gateway and the MQTT Engine.

I created a lambda to use an IOT Rule to grab the data from the engine and then I blew through $2500 in like 2 days because I apparently built the IOT Rule without enough filtering so that initially was bad. I did my best to create a better rule, and I think I have a good filter now.

The issue I am seeing is tagid: None for all tag paths. I could create a mapping table in s3 and pick them up in the process of writing to the lake but it adds some non-trivial latency to the process when reading from s3.

Questions:
Is tagid supposed to be in the message? If Yes, where should I go to figure out why they aren’t? If No, is there a way to send the tagid → tagpath mapping via the feed so I can be sure to have the latest mappings?

Is there something I should do to stop the multiple historian tables? Having two different tagids in the historian is not ideal for referential integrity.

Is there a better choice than an AWS lambda for the MQTT → s3 lake for this data? I looked at Kinesis but it seems like that will be more expensive. I looked at IOT bridge → SiteWise and that is about $825 a month just for those. (Which may be cheaper than any other options)

Is there a potential to use something like a sqllite on the EC2 instance to provide the necessary historian which can be a more limited set of historical data because we have historical in s3? (I am thinking 90 days on the instance and everything else on s3)

I did manage to setup the automatic failover on the edge gateway for a loss of connection so that we hopefully don’t lose data, but I am not sure it will work that way. Happy to read any documents or other articles. I have done a lot of internet/forum searches, but any help is appreciated. In my research I suspect I am not doing a good job with search terms.

kvane · November 11, 2025, 11:38pm

Welcome to the forums. There are good questions here but I'd recommend breaking this up into multiple posts. For example, I am mainly interested in this line above. You are describing Ignition tag History Tables. Are you sure you should be storing data this way, and not something like a time-series database or canary historian type resource? I would focus on ensuring, if you are using Tag History, that the historian providers are setup as expected.

There are table references here: Ignition Database Table Reference | Ignition User Manual
The sqlth_data_ tables are tag history tables of raw data. The number before the partition date info is the drvid (seen in the sqlth_drv table), the specific Ignition History Provider that exists on a gateway. A database could have multiple Gateways storing Tag History, or a Gateway name change which could cause drvids to update.

For example I have many tag history tables across many providers because I have had many Gateways with different names/historians connected:

When looking at a direct sqlt_data table, it will have tagids associated with each row. Those tagids correlate to the sqlth_te table and provide the human readable tag path.

When working with Ignition Tag History data, it is not expected that you would query from the SQL tables directly. Data is usually retrieved via scripting, bindings, and similar UI within Ignition. The system stores data in a tall format with compression that should generate ids automatically appropriately. The sqlth_te table will retire tags as changes occur to them, so a single tag could have many tagids associated with it over time. Hopefully this helps answer some questions.

justin3 · November 12, 2025, 2:31pm

I appreciate the answer to some of my question and the time to answer it.

I need to reiterate - I didn’t design the system or set it up. I am merely the data person trying to get data out of the system and put it into a lake. A time series database could be something I do with the MQTT feed but that is a future state project. I am not even sure we need a historian if we are feeding the data into the lake except for something like a local store and forward sqllite that clears itself after a certain time. However, I have to convince both the company I work for and the 3rd party that is still under contract for the Ignition setup that I am right (I may not be when we think about scale and see answer below).

Historian Context:
The system is extremely simple - HMI PC has the Edge Gateway and MQTT Transmission module, EC2 instance has the Main Gateway and the MQTT Engine, there is an Aurora RDS for Historian. There is a dev server as well, but it is generally offline.

I know why there are multiple tables, I am aware of what the 1 and 3 represent (the different drivers), what I can’t figure out is why data is still going there when the team that set this up said they disabled the second driver. It is still there but was supposedly disabled. The driver wasn’t for a different gateway or different tag provider; it is literally the exact same everything. That is a waste of money when it comes to IO to an Aurora Database. I was trying to figure out what I should be looking at to make sure it is disabled, so we don’t keep spending money on IO to the database for the same tags. Apologies about not making the ask very clear about the goal.

I don’t love the way Ignition does tag data in the Historian but it was manageable with only one site. IA, it seems, never considered the idea someone might try and use the database backing ignition as a data source for tags and expected python scripts running on the application host to fill the data export needs. This has never been clearer than when the new site’s tags were showing up in the same schema and tables as the existing site. Not even via a different driver so they were isolated tables, just new tagids with nearly the same tagpaths as the existing site and tagpath to tagid mapping that just found the next higher number index and appended. It’s possible that this is just a company doing this not understanding how to actually make the data segregation work, it could be IA’s design when it comes to data storage, or it could be a combination of both.

In the world I work in using a script on an application host to be relied upon as the primary data provider is the last step before manual exports in spreadsheets and eventual madness. The MQTT modules are as much burdening the compute the application runs on as I was willing to go to offload the tag data collection to serverless compute in AWS and eventual non-reliance on the very database IA, according to your statements, never intended to support data warehousing or lake hydration from. Even still, it is the store of last resort for retrieving historical data should the MQTT feed go down for a period of time that exceeds the store and forward cache.

MQTT future state from Historian data dive:
Based on what I am seeing in the historian from their testing we’re going to have a mess of an MQTT feed. The way the tag paths are setup currently the new site looks like it will come through as though it is a tag path within the first site. In the historian the tag paths for the original site are ec2//<value_keys> and the new site is ec2//<value_keys> which as far as I can tell will come through as if those tags belong to the original site and not the new one on the MQTT feed based on how the current site comes in. The current site in the MQTT feed is spBv1.0//DDATA//EC2 and based on what I believe I understand that is going to simply be spBv1.0//DDATA//EC2 when the new site comes online. That is a recipe for data collision and a nightmare to put data into the correct Iceberg table in s3 due to having a parsing problem. Same thing if I wanted to use a time series database - I will have to do a lot of pre-storage compute work to get the data into a segregated state in order to store it effectively for each site and ensure a clean data stream.

Answers to my own questions (maybe):

I don’t think TagID is relevant anywhere but historian. It seems like an index added somewhere during the process of Ignition storing the data in the historian. I am not even sure it is relevant to the data in a meaningful way except to trace lineage across gateways or providers when you have more than one. In my case, I do not believe it will matter for the purposes of the Lake and ML.
I am still not clear on why the supposedly disabled extra feed of the same provider is not disabled though my look through says it should be. I think I am missing a setting.
I think the answer to this is going to be IOT Bridge, especially once we have multiple sites, because that cost is going to be less than 2 sites through kinesis data pipeline.
I did my best to do further research, and it seems like my idea to not have an Aurora Postgres as the Historian and instead using a smaller SQLLite on the application compute is an option but will not be ideal once we have a lot of sites. So, I need to shift my focus on how to store the data in the historian effectively.

pturmel · November 12, 2025, 2:57pm

Yes, tagID is specific to the historian, and not meaningful elsewhere. It is important to know that a single tag will gain new tagIDs as its configuration changes (old IDs will be retired), and Ignition's internal query methods know to gather all relevant tagIDs for a given query time span.
Unclear. Be aware that a driverID represents a source gateway name and origin tag provider. Duplicate data is often caused by poorly isolated development or testing gateways running in parallel. If those systems were carelessly loaded from a production gateway backup, they might even be running with the same gateway name. Take a close look at your database to see where traffic/connections are originating.
I don't know enough about that to comment.
Under no circumstances should you ever use SQLite as a production historian with Ignition. SQLite is fundamentally singlethreaded and will bottleneck quickly--it is designed to store read-mostly configuration data, not to handle regular INSERT workloads.

Further note: Jython does not share the performance problems of CPython (though it does have some issues), as it has no global interpreter lock, it JITs heavily used code to java bytecode, which is then often Hotspot JIT'd to native machine code. As "glue" in the Ignition ecosystem to express business logic, it is not the madness you make it out to be.

justin3 · November 12, 2025, 3:31pm

Ok, this confirms what I suspected and it is very helpful knowing that.
I know for a fact they are originating from a duplicate driver that was sending the same data and I am about 90% sure that the reason is exactly as you stated - they didn’t even have a secondary/isolated dev environment until I championed the move from Azure to AWS. They were using the same gateway for both dev and production but the driver got pointed at prod in both cases.
I am pretty good with AWS costs and really just looking for experiences with different options if anyone has them. Appreciate anything anyone else has seen or done.
This tracks what I was beginning to think about in terms of that idea. I think this is a case where I need to really work hard with the 3rd party vendor to get a tag strategy fixed so that the aurora database is more useable and we segregate sites better so that the data can be retrieved in a last resort type of situation without extremely complex queries.

The note about Jython makes sense, I’ll have to get more familiar with it as all my work is CPython.

I appreciate everyone taking the time here. I think we have a situation where we’re about to build on top of a potentially disastrous data setup for tags in the system and it needs to be taken care of now before we end up in a data nightmare.

kvane · November 12, 2025, 4:28pm

The way the tag paths are setup currently the new site looks like it will come through as though it is a tag path within the first site.

I think you are right about the data situation, I know it's an overused buzzword but this is getting to the heart of what Unified Namespace is meant to fix. If the tag paths were setup in a structured manner across the enterprise -which you obviously had no control over- it would be less of a problem. Easier said than done.

For the duplicate questionable history provider, I would contact IA Support, it could be something simple being overlooked or an actual problem. If it is causing you headaches they would want to help.

8.3 included major changes to the historian system and a new local QuestDB historian Core Historian Video at Inductive University , but I am assuming this is 8.1 and won't change for some time.

justin3 · November 12, 2025, 4:34pm

Yes, it is 8.1 currently. I know 8.3 has some very nice updates to this. It was nice to see. I am not sure if moving to 8.3 is an option as part of the effort to clean up before we get worse.

I am also suggesting we use github for version control so we can get a handle on this as well.