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.
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":
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.
|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.|
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.
An optional component that allows you to specify a single column on the table to use for simple filtering. Used in conjunction with keyvalue. Value must be an integer.
There can only ever be a single keycolumn for any given path. More complex filtering can be accomplished by instead using a Named Query.
An optional component that works with keycolumn, allowing the query to only return rows if the keycolumn contains the value specified on this component.
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.
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:
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.
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: