DB Table Historian Provider
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.
Configuring a DB Table Historian Provider​
On the Gateway Webpage, navigate to the Config section.
Under the TAGS heading in the sidebar, click on History.
On the History Provider's listing, click Create new Historical Tag Provider.
From the listing of available types, select DB Table Historian, and click Next.
Enter a unique name for the Provider in the Provider Name field.
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.
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:
Then, we could use the Perspective Power Chart built-in Tag Browser panel to detect the table, which exposes the columns as "tags":
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, where each one represents some identification of the data source. The DB Table Historian is molded after the following:
histprov:[historyProvider]:/table:[tableName]:/column:[columnName]:/timestamp:[timestampColumnName]:/keycolumn:[keyColumnName]:/keyvalue:[keyColumnValue]
Each of the components are described below.
| Component | Description |
|---|---|
| histprov | The name of the History Provider that should be queried. |
| table | The name of the database table in the the History Provider. |
| column | The name of the column on the table, in the History Provider. |
| timestamp | A 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. |
| keycolumn | An optional component that allows you to specify a single column on the table to use for simple filtering. This component can be used in conjunction with keyvalue. There can only ever be a single keycolumn for any given path. Note: More complex filtering can be accomplished with a Named Query instead of a historical path. For instance, you could point a Query binding to a Named Query that takes in many key value pairs for the WHERE clause. |
| keyvalue | An 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, created tags may look like the example below, with dedicated table and column components in the path.
histprov:DB_Table_Historian:/table:group_table:/column:Sine1:/timestamp:my_timestamp_column
Example​
Say we have a database table like the following.
SELECT machine_id, process_value, time FROM machine_values
| machine_id | process_value | time |
|---|---|---|
| 1 | 111 | 2020-09-10 21:44:35 |
| 1 | 100 | 2020-09-10 21:44:41 |
| 2 | 22 | 2020-09-10 21:45:01 |
| 2 | 222 | 2020-09-10 21:45:15 |
We could use the DB Table Historian Provider to expose our machine_values table to a Vision Tag History binding.
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