IMD SolutionComputing LibraryShop main store

How-to

Identifying the source gateway in a shared historian database

Edge computing and IIoT Published 5 min read

Short answer

The history does not land in one table. Each gateway connected to the shared database registers at least two rows in sqlth_drv, and the number in the sqlt_data_ table prefix is one of those row IDs, so the source is already recorded. Join sqlth_te to sqlth_drv for the tag-to-gateway map and filter the visualization on a site-code tag-path prefix, not on scan class names.

On this page
  1. What the table names already tell you
  2. Map every tag to its gateway
  3. Worked example: three sites, one database
  4. Use the driver entry, not the scan class
  5. Fix identity collisions before more history lands
  6. Deploy the central system step by step
  7. Questions asked

All the history does not land in one table. Every gateway you connect to the central database registers at least two rows in sqlth_drv, the table the documented Ignition 8.1 reference describes as storing information about drivers, the source systems writing tag history, and the first number in each sqlt_data_<n> table name is one of those row IDs. The source identity you were about to invent is already in the schema. Join sqlth_te to sqlth_drv for the tag-to-gateway map, filter the visualization on a site-code prefix in the tag path, and keep scan class names out of the identity job entirely.

What the table names already tell you #

The database table reference for Ignition 8.1 documents sqlth_drv as the table that stores information about drivers: the source systems writing tag history data. Data tables follow the pattern sqlt_data_<n>_..., where <n> is a row ID from sqlth_drv. One connected gateway adds at least two rows, so the prefixes skip: three gateways produce sqlt_data_1_..., sqlt_data_3_... and sqlt_data_5_..., while IDs 2, 4 and 6 are sibling entries from the same registrations.

The driver ID in a partition name is the sqlth_drv entry that matches that gateway and carries a null provider; the sibling row never names a partition. You do not need the rest of the table name at all, because you query through the tag metadata rather than by parsing table names. That decision is the method.

This layout is the documented 8.1 one; the table reference for the release you run is the authority before anything is scripted against the schema.

Map every tag to its gateway #

sqlth_te holds one row per historized tag, the data tables reference that row, and the tag row carries the driver that produced it. The site map is one query:

SELECT d.id AS driver_id, COUNT(*) AS tag_count
FROM sqlth_te t
JOIN sqlth_drv d ON d.id = t.drvid
GROUP BY d.id;

drvid is the driver-reference column as it reads on the 8.1 databases we query. Read your own table with SELECT * FROM sqlth_te before automating around it: the column set moves between releases, and the join shape, not the column spelling, is the deliverable. Every driver ID that returns a non-zero tag count is one gateway registration; two IDs with overlapping tag sets means two gateways presented the same name, which is the collision case below.

Worked example: three sites, one database #

Three site gateways connect in order, each writing tag history directly to the central database. Registration arithmetic: at least 2 rows per gateway × 3 gateways = 6 rows in sqlth_drv, IDs 1 through 6, so the partition prefixes read 1, 3 and 5.

The sites historize 120, 75 and 40 tags, and the join returns:

driver_id tag_count site
1 120 Site A
3 75 Site B
5 40 Site C

The check: 120 + 75 + 40 = 235. If sqlth_te returns 241 rows, the difference, 241 − 235 = 6, is retired rows left by recreated tags, not a fourth site. Back the database up first, then follow the documented procedure for safely deleting retired tag rows from sqlt_te rather than deleting by eye.

Use the driver entry, not the scan class #

A scan class is a gateway-local timing object. Its name is whatever an engineer typed on that gateway, two sites can type the same name with nobody notified, and renaming or recreating it changes the key mid-history. The driver entry, by contrast, is assigned by the database at registration. The scan class carries no identity information the driver entry lacks, so it is out of the design.

Discriminator Survives a gateway rebuild Depends on naming discipline Role
sqlth_drv entry No, a rebuilt gateway registers new rows No Primary join key
Tag-path site prefix Yes Yes Site filter at the visualization
Scan-class name Only if recreated identically Yes None, timing only

Join on the driver entry, filter the UI on the tag-path prefix, and let the scan class do timing and nothing else.

Fix identity collisions before more history lands #

Two gateways presenting the same identifying name resolve to one driver entry, and their tags merge in the join. Rows already written under a shared entry cannot be split by the schema afterward; only the tag path separates them, and only if the site code was in the path before the collision. Give every gateway a unique system name before its first connection. If a merge tempts you into cleanup, remember the supported direction: deleting tag-history data rows without touching metadata is what cleanup deletes, while stripping sqlth_te or sqlth_drv rows to start clean orphans history you cannot reattach.

A rebuilt or factory-reset gateway registers fresh rows, so one site can legitimately own several driver IDs over its life. Keep the site-to-ID list as a lookup you append to; one ID per site forever is the wrong invariant.

Separately, on Ignition 8.1.19 a gateway log entry repeating every 60 seconds to state that the sqlth_drv table already exists is a registration fault, not the normal two-row registration. Fix the repeating sqlth_drv table-already-exists warning before trusting any mapping built on that database.

Deploy the central system step by step #

  1. Give each site gateway a unique system name before it ever connects to the central database.
  2. Make the first segment of every historized tag path the site code, and enforce it in the tag structure you deploy to the sites.
  3. Point each gateway's tag history provider at the shared database connection. A site box that keeps history in its own local store instead writes it where the Ignition Edge tag historian writes its data, not into your central database.
  4. Run the join query and record, per site, every driver ID it returns.
  5. Build the central visualization to filter on the tag-path prefix; the provider resolves paths to tag IDs and reaches whichever data table holds the rows, so the physical split never reaches the UI.
  6. Set retention once, centrally. Several connections writing into one database do not prune independently, so read why two Ignition historian connections share one pruning boundary before giving each site its own retention numbers.
  7. Prove the mapping: stop one site gateway for five minutes, then restart it. New rows must continue under the same driver ID with a five-minute gap in timestamps, and no new sqlt_data_ prefix may appear. A new prefix means the gateway re-registered, so check its name before adding the ID to the lookup.

Questions asked

Why do the data table numbers skip values?

Each connected gateway registers at least two rows in sqlth_drv, but a partition name uses only the entry that matches the gateway and carries a null provider. The unused sibling rows take IDs 2, 4 and 6 while the prefixes read 1, 3 and 5. The gaps are normal registration, so leave the sibling rows alone: the join depends on sqlth_drv staying exactly as registered.

Does this table layout change on Ignition 8.3?

Ignition 8.3 integrates QuestDB in the new Core Historian module, replacing the legacy SQLite-based historian on the gateway, which changes the gateway's local store. The sqlth_ tables are what your central database creates for the history provider you connect to it. The table reference that documents them is the 8.1 one, so read the reference for your release before automating against the schema.

Two sites already share one driver entry. Can the old rows be split?

Not through the schema: those rows carry a single driver ID and no update re-attributes them. Split going forward by renaming one gateway so its next registration creates a separate entry, and separate the existing rows by the tag-path site code, which is why the prefix belongs in the path from day one.

What is the repeating sqlth_drv warning in the gateway logs?

On Ignition 8.1.19, a warning repeating every 60 seconds to say the sqlth_drv table already exists is a historian registration fault, distinct from the normal two-row registration. Treat any mapping built while that warning repeats as untrusted, because registration is exactly what the mapping reads.

How do I add a fourth site later?

Name the gateway uniquely, put the site code first in its tag paths, connect it, and run the join once. After IDs 1 through 6 it registers rows 7 and 8, with 7 as its partition prefix, and appears as a new driver_id row with its own tag count. Add it to the site lookup; the visualization needs no change because the prefix filter already covers the new site.

Keep reading

  • Troubleshooting

    Why Ignition MQTT shows tagid None and what to key on

    tagid is None because the MQTT payload never carries it: tag IDs are indexes inside the Ignition tag history provider, assigned and retired as tag configuration changes. Key the lake on a site-scoped tag path with the site identity in the MQTT namespace, and trace duplicate sqlth_data_ table sets through their driver IDs in sqlth_drv before stopping the extra writer.

    Edge computing and IIoT

  • How-to

    Seed a large Ignition historian database for replication

    Stage the backup database by closed partition, not in one 88 GB dump. The measured 4.5 GB in 40 minutes gives 6.75 GB/h, so the whole history is about 13 hours of dumping, but a weekly 2 GB partition is an 18-minute unit you can restore and verify before starting MySQL replication from that aligned state.

    Edge computing and IIoT

  • Explainer

    Why two Ignition historian connections share one pruning boundary

    No. Two database connections pointed at the same SQL Server database give the tag historian two logical routes into one shared table set, and pruning deletes whole partition tables, so the 3-month policy can remove data marked never prune. Put each retention policy in its own database on the same instance.

    Edge computing and IIoT