Data Partitioning and Pruning
To partition and prune data
- Go to the Configure section of the Gateway.
- Select Tag > 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.
- Click on edit at the far right of the database you want make changes to.
Historical Tag Provider Properties
Main
| Property | Description |
|---|---|
| Provider Name | Name of the Data Connection for the Tag History Provider. |
| Enabled | By default, the check box is selected (enabled) meaning the provider is turned on and accepts tag history data. |
| Description | An optional user created description of the provider. |
Data Partitioning
| Property | Description |
|---|---|
| Enable Partitioning | 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. |
| Partition Length and Units | 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. |
| Enable Pre-processed Partitions | This option is not selected by default. Pre-processed partitions will use more space in the database, but can improve query speed by summarizing data, reducing the amount that must be loaded. |
| Pre-processed Window Size (seconds) | When pre-processing is turned on, the data will be summarized into blocks of this size. |
Data Pruning
| Property | Description |
|---|---|
| Enable Data Pruning | The check box is not selected/enabled by default. Partitions with data older than a specific age are deleted and if the data is not archived, the data is then lost. Important: 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 and Units | 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. |
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. This section will quickly cover 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 spit out 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