Skip to end of metadata
Go to start of metadata


Tag Historian will partition data into separate tables according to the time setting so that one table doesn't grow indefinitely, and then will delete old data to ensure the system is maintained for query performance. By default, partitioning is enabled to improve query performance. Tag Historian partitions and breaks up the data into separate tables based on time. Partitions will only be queried if the query time range includes their data, thereby avoiding partitions that aren't applicable and reducing database processing. On the other hand, the system must execute a query per partition. It is therefore best to avoid both very large partitions, and partitions that are too small and fragment the data too much. When choosing a partition size, it is also useful to examine the most common time span of queries.The data prune feature will delete partitions with data older than a specific age/time.

On this page ...


IULocgo


Data Partitioning and Pruning



Partition and Prune Data

  1. Go to the Config tab of the Gateway.
  2. Select Tags > History from the menu on the left.
    The Historical Tag Providers page is displayed. You can see the Databases that have Enabled tag history on and their Status shows as Running.



  3. Click on edit at the far right of the provider you want to update.
  4. Once you've made changes, click Save Changes at the bottom of the screen.


The following table describes all the settings available for Tag History:

Main

Provider NameName of the Tag History Provider, for example, MySQL.
Enabled

By default, the check box is selected (enabled) meaning the provider is turned on and accepts tag history data.

DescriptionDescription of the Tag History Provider (optional).

Data Partitioning

Enable Partitioning The built-in partitioning system breaks up data into separate tables of a specified time frame. This can improve performance and make certain maintenance tasks easier. Default is true.

Partition Length

The size of each partition, the default is one table per month. Many systems whose primary goal is to show only recent data might use smaller values, such as a week, or even a day. Default is 1.

Partition UnitsUnit of time for the partition length. Options are: Milliseconds, Seconds, Minutes, Hours, Days, Weeks, Months, and Years. Default is Months.
Enable Pre-processed PartitionsPre-processed partitions will use more space in the database, but can improve query speed by summarizing data, reducing the amount that must be loaded. Default is false.
Pre-processed Window Size (seconds)When pre-processing is turned on, the data will be summarized into blocks of this size. Default is 60.

Data Pruning

Enable Data Pruning

Partitions with data older than a specific age are deleted and if the data is not archived, the data is then lost. Default is false.

Note: Data pruning works by deleting old partitions. Therefore, data will only be removed when a partition has no data younger than the prune age.

Prune Age

The maximum age of data. As mentioned, the data is deleted by the partition, and could therefore surpass this threshold by quite a bit before all of the data in the partition is old enough to be dropped. Default is 1.

Prune Age UnitsUnit of time for the prune age. Options are: Milliseconds, Seconds, Minutes, Hours, Days, Weeks, Months, and Years. Default is Years.
Show Advanced PropertiesSelect this option to display the Advanced properties below:
Advanced
Enable Stale Data Detection

If enabled, tracks tag group executions to determine the difference between unchanging values, and values that are flat due to the system not running. Default is true.

Stale Detection Multiplier

The multiplier for tag group rate used to determine when values are stale. If tag group execution is not recorded within this amount of time, values will be considered bad on query. Default is 2.


History Table Timestamps

If you've looked behind the scenes of SQLTags Historian, you've probably noticed the timestamps are not stored as standard SQL timestamps. They are stored in a variant of Unix time, or the number of milliseconds since January 1, 1970 00:00:00. The time may come when you need to convert that timestamp to a more human-readable format. The following describes how to do it in MySQL and MSSQL.

Both examples below assume the partition table is named 'sqlt_data_1_2016_08'.


MySQL
It's pretty easy to deal with Unix timestamp in MySQL because they have a built-in function for doing so. The FROM_UNIXTIME() function will take in a Unix timestamp and return the current timestamp.
Usage:

SELECT FROM_UNIXTIME(t_stamp/1000) FROM sqlt_data_1_2016_08



MSSQL
In Microsoft SQL Server, it's a little more verbose. We use the DATEADD() function to figure out the timestamp.
Usage: 

SELECT DATEADD(s,t_stamp/1000,'1970-01-01 00:00:00') FROM sqlt_data_1_2016_08



  • No labels