Skip to end of metadata
Go to start of metadata

This feature is new in Ignition version 8.1.0
Click here to check out the other new features

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 that a Datasource History Provider. Meaning, 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. 

On this page ...

IULocgo


DB Table Historian


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.



  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. 



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, which each represent 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.

ComponentDescription
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.
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. 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

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. 

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. 

histprov:DB_Table_Historian:/table:group_table:/column:Sine1:/timestamp:my_timestamp_column

Example

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:

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



  • No labels