Skip to main content
Version: 8.1

DB Table Historian Provider

New in 8.1.0

Inductive University

DB Table Historian

Watch the video

The DB Table Historian Provider acts as a bridge between tables in a database connection and the Tag Historian Module, mapping columns in the tables as "available Tags", thus allowing Tag History Queries to access the content of the table. Usually Tag History Queries can interact only with tables created by the Tag Historian. Thus, a table created with a Transaction Group, or by some other means (e.g., manually creating the database tables, tables generated by third party systems, etc) couldn't be accessed via things like a Tag History Binding. The DB Table Historian Provider solves this problem.

Utilizing this provider requires a Datasource History Provider, which is the type of historical provider that is automatically created whenever a Database connection is configured. You can check the type of any History Provider under the Config section of the Gateway, on the Tags > History page.

DB Table Historian Provider

Configuring a DB Table Historian Provider

  1. On the Gateway Webpage, navigate to the Config section.

  2. Under the TAGS heading in the sidebar, click on History.

  3. On the History Provider's listing, click Create new Historical Tag Provider.

  4. From the listing of available types, select DB Table Historian, and click Next.

    Configuring a DB Table Historian Provider Step 4

  5. Enter a unique name for the provider in the Provider Name field.

  6. In the Data source field, select the backing data source. You would select which ever Datasource History Provider contains the tables you want exposed to the historian system. Once ready, click the Create New Historical Tag Provider button, which completes the configuration process.

    Configuring a DB Table Historian Provider Step 6

Retrieving Records

Once configured, the DB Table Historian will expose any tables found in the associated data source (specifically, the Data Source listed on the configuration page of the DB Table Historian). From this point on, any tables found in the data source will be available for browsing via the various Tag History interfaces found throughout Ignition.

For example, we could store some records in a database connection with a Transaction Group, which would create a table like the following:

Retrieving Records DB Query Browser

Then, we could use the Perspective Power Chart built-in Tag browser panel to detect the table, which exposes the columns as "tags":

Retrieving Records Power Chart

Path Components

The DB Table Historian Provider attempts to map each column in a database table to a Tag. When querying the results, the historical Tag path used is composed of multiple components, which each represent some identification of the data source. The DB Table Historian is molded after the following:


Each of the components are described below.

histprovThe name of the history provider that should be queried.
tableThe name of the database table in the the history provider.
columnThe name of the column on the table, in the history provider.
timestampA column on the table that will be used as the source of the timestamp for the query. By default, the query will look for a column named t_stamp to use for the timestamp component.
It's highly recommended to include this component if the table doesn't contain a timestamp column named "t_stamp", otherwise the query will fail.
keycolumnAn optional component that allows you to specify a single column on the table to use for simple filtering. Used in conjunction with keyvalue.
There can only ever be a single keycolumn for any given path. More complex filtering can be accomplished by instead using a Named Query.
keyvalueAn optional component that works with keycolumn, allowing the query to only return rows if the keycolumn contains the value specified on this component. Value must be an integer.

As far as historical pathing goes, Tags created by this provider may look something like below, with dedicated table and column components in the path.



Say we have a database table like the following.

SELECT machine_id, process_value, time FROM machine_values
11112020-09-10 21:44:35
11002020-09-10 21:44:41
2222020-09-10 21:45:01
22222020-09-10 21:45:15

DB Table Historian Provider Example DB Query Browser

We could use the DB Table Historian provider to expose our machine_values table to a Vision Tag History Binding.

Property Binding Power Table Example

The resulting historical Tag path for process_value (after dragging it over to the Selected Historical Tags table) would look like the following:

histprov:DB Table Historian:/table:machine_values:/column:process_value

We would need to explicitly state that the "time" column should be used by the binding, so we could double click on the cell under the "Tag Path" header, and change the Tag Path to the following, which would allow the query to feature the records accurately.

histprov:DB Table Historian:/table:machine_values:/column:process_value:/timestamp:time

We could further modify this Tag path, so that only entries with a "machine_id" of 1 are returned, but changing the path to the following:

histprov:DB Table_Historian:/table:machine_values:/column:process_value:/timestamp:time:/keycolumn:machine_id:/keyvalue:1