Appendix

Internal Data Structure

TimescaleDB

The Historian module uses the following table structure to store data in TimescaleDB.

tag_ids

The tag_ids table stores each tag name and its internal ID. The tag_history table uses this ID in the tag_id column to associate historical records with a tag.

CREATE TABLE IF NOT EXISTS tag_ids (
    id  BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    tag TEXT UNIQUE
);

tag_history

The tag_history table is a TimescaleDB hypertable used to store the historical data of tags

CREATE TABLE tag_history (
    ts              TIMESTAMPTZ NOT NULL,
    tag_id          BIGINT      NOT NULL,
    quality         SMALLINT    NOT NULL,
    boolean_value   BOOLEAN,
    number_value    DOUBLE PRECISION,
    string_value    TEXT,
    sequence_number BIGINT GENERATED ALWAYS AS IDENTITY
)
WITH (
    tsdb.hypertable,
    tsdb.chunk_interval = '1 hour',
    tsdb.partition_column = 'ts',
    tsdb.segmentby = 'tag_id',
    tsdb.orderby = 'ts ASC',
    tsdb.create_default_indexes = false
);
CREATE INDEX tag_history__query ON tag_history(tag_id, ts ASC);
ALTER TABLE tag_history SET (timescaledb.compress_chunk_time_interval = '1 day');
CALL add_columnstore_policy('tag_history', created_before => INTERVAL '12 hours');

alarm_history

The alarm_history table is a TimescaleDB hypertable used to store alarm events.

CREATE TABLE IF NOT EXISTS alarm_history (
    ts              TIMESTAMPTZ NOT NULL,
    path            TEXT        NOT NULL,
    description     TEXT        NOT NULL,
    priority        SMALLINT    NOT NULL,
    status          SMALLINT    NOT NULL,
    boolean_value   BOOLEAN,
    number_value    DOUBLE PRECISION,
    string_value    TEXT,
    quality         SMALLINT    NOT NULL,
    ack_msg         TEXT,
    ack_user        TEXT,
    ack_node        TEXT,
    ack_module      TEXT,
    sequence_number BIGINT GENERATED ALWAYS AS IDENTITY
)
WITH (
    tsdb.hypertable,
    tsdb.chunk_interval = '2 hours',
    tsdb.partition_column = 'ts',
    tsdb.segmentby = 'path',
    tsdb.orderby = 'ts ASC',
    tsdb.create_default_indexes = false
);

MongoDB

The Historian module uses a custom structure and compact binary format to efficiently store time-series data using a MongoDB database.

The structure is as follows:

• A collection named tags stores the tag name and its internal ID.

• A collection is created each day with the format --> yyyy-mm-dd.samples

• In each collection, a document is created per tag and hour, the ID of each document is composed of the numeric ID of the tag stored in the tags collection and the hour in UTC. For example, all events for a tag with ID 23 between 7:00 and 8:00 UTC are stored in the document with ID 2307.

• Events are stored as entries inside the document, the key represents the millisecond within the hour and the value is an encoded binary object that contains the event data.

• The binary object is encoded using the following serialization:

  • The first byte contains the quality according to the OPC standard (0 to 192).

  • The second byte contains the opcode which indicates the data type.

    • if opcode = 0: A boolean with the value false.

    • if opcode = 1: A boolean with the value true.

    • if opcode = 2: The next 8 bytes contain a double value using big-endian ordering.

    • if opcode = 3: A null value.

    • if opcode = 4: The rest of the bytes contain a UTF8 string.

    • if opcode > 4: The value is an integer.

      • if bit 5 = 0: The value is smaller than 5 bits and is stored in the first 5 bits to the right.

      • if bit 5 = 1: The next bytes contain an integer with little-endian order.

      • if bit 6 = 0: The sign of the integer value is positive.

      • if bit 6 = 1: The sign of the integer value is negative.

Examples:

Renaming Tags

TimescaleDB

When using TimescaleDB as the Historian database, you can rename a tag while preserving its historical data by executing a simple SQL statement:

UPDATE public.tag_ids
SET tag = :new_tag_name
WHERE tag = :current_tag_name;

Example:

UPDATE public.tag_ids
SET tag = '/PVSIM/BLUELAKE/PVG001/PST_10/INV001/ACTIVE_POWER_2'
WHERE tag = '/PVSIM/BLUELAKE/PVG001/PST_10/INV001/ACTIVE_POWER';

Output:

UPDATE 1

Query returned successfully in 57 msec.

We can verify the change with:

SELECT * FROM public.tag_ids WHERE tag = '/PVSIM/BLUELAKE/PVG001/PST_10/INV001/ACTIVE_POWER_2';

Output:

MongoDB

Note

Before proceeding with the following procedure, it is imperative to validate the CSV file. Ensuring the accuracy and integrity of the CSV file is crucial for the proper execution of the process.

Failure to validate the file may result in errors or unexpected outcomes. Take the necessary steps to validate the CSV file before proceeding further.

Note

This feature is new in N3uron version 1.21.5.

Changing tag names while keeping the historical data of the time series is possible. To do this, it is necessary to follow these steps:

• Step 1: Prepare a CSV file named tags_rename.csv with the following format:

/oldName1,/newName1
/oldName2,/newName2
/oldName3,/newName3

• Step 2: Stop the Historian module. (If data is received through links and Historian is configured with 'Pause links while unavailable'), remote nodes will pause sending data and will store it via S&F).

• Step 3: Don't forget to make a backup of the module's configuration and the database.

• Step 4: Make the corresponding changes in the data model and save them.

• Step 5: Save the CSV file (tags_rename.csv) to the Historian module folder inside data (On Windows: C:/Program Files/N3uron/data/Historian and on Linux: /opt/n3uron/data/Historian).

  Note

  If the Historian instance to be renamed is different, then it should be stored in C:/Program Files/N3uron/data/<Historian module name> or /opt/n3uron/data/<Historian module name>.

• Step 6: Restart the Historian module.

Enabling Zstd Compression in MongoDB

By default, MongoDB compresses data on disk using the Snappy compression algorithm. If you are using MongoDB 4.2 or later, you can enable Zstd compression and achieve up to a 40% reduction in disk space while maintaining a similar performance.

Note

Existing data will not be recompressed using Zstd. The compression change will only apply to new collections created after the configuration is updated.

There are two methods to enable Zstd as the default block compressor in MongoDB:

1. Update the mongod.conf file:

...
storage:
  engine: wiredTiger
  wiredTiger:
    collectionConfig:
      blockCompressor: zstd
...

2. Use the command line:

mongod --wiredTigerCollectionBlockCompressor zstd

Historian Full Product Details