Historian

How _historian data is stored and can be queried

Our database for the umh.v1 _historian datamodel currently consists of three tables. These are used for the _historian schema. We choose this layout to enable easy lookups based on the asset features, while maintaining separation between data and names. The split into tag & tag_string prevents accidental lookups of the wrong datatype, which might break queries such as aggregations, averages, …

erDiagram asset { int id PK "SERIAL PRIMARY KEY" text enterprise "NOT NULL" text site "DEFAULT '' NOT NULL" text area "DEFAULT '' NOT NULL" text line "DEFAULT '' NOT NULL" text workcell "DEFAULT '' NOT NULL" text origin_id "DEFAULT '' NOT NULL" } tag { timestamptz timestamp "NOT NULL" text name "NOT NULL" text origin "NOT NULL" int asset_id FK "REFERENCES asset(id) NOT NULL" real value } tag_string { timestamptz timestamp "NOT NULL" text name "NOT NULL" text origin "NOT NULL" int asset_id FK "REFERENCES asset(id) NOT NULL" text value } asset ||--o{ tag : "id" asset ||--o{ tag_string : "id"
erDiagram asset { int id PK "SERIAL PRIMARY KEY" text enterprise "NOT NULL" text site "DEFAULT '' NOT NULL" text area "DEFAULT '' NOT NULL" text line "DEFAULT '' NOT NULL" text workcell "DEFAULT '' NOT NULL" text origin_id "DEFAULT '' NOT NULL" } tag { timestamptz timestamp "NOT NULL" text name "NOT NULL" text origin "NOT NULL" int asset_id FK "REFERENCES asset(id) NOT NULL" real value } tag_string { timestamptz timestamp "NOT NULL" text name "NOT NULL" text origin "NOT NULL" int asset_id FK "REFERENCES asset(id) NOT NULL" text value } asset ||--o{ tag : "id" asset ||--o{ tag_string : "id"

asset

This table holds all assets. An asset for us is the unique combination of enterprise, site, area, line, workcell & origin_id.

All keys except for id and enterprise are optional. In our example we have just started our CNC cutter, so it’s unique asset will get inserted into the database. It already contains some data we inserted before so the new asset will be inserted at id: 8

identerprisesitearealineworkcellorigin_id
1acme-corporation
2acme-corporationnew-york
3acme-corporationlondonnorthassembly
4stark-industriesberlinsouthfabricationcell-a13002
5stark-industriestokyoeasttestingcell-b33005
6stark-industriespariswestpackagingcell-c23009
7umhcologneofficedevserver1sensor0
8cuttingincoperatedcolognecnc-cutter

tag

This table is a timescale hypertable. These tables are optimized to contain a large amount of data which is roughly sorted by time.

In our example we send data to umh/v1/cuttingincorperated/cologne/cnc-cutter/_historian/head using the following JSON:

{
 "timestamp_ms": 1670001234567,
  "pos":{ 
    "x": 12.5,
    "y": 7.3,
    "z": 3.2
  },  
  "temperature": 50.0,
  "collision": false
}

This will result in the following table entries:

timestampnameoriginasset_idvalue
1670001234567head_pos_xunknown812.5
1670001234567head_pos_yunknown87.3
1670001234567head_pos_zunknown83.2
1670001234567head_temperatureunknown850.0
1670001234567head_collisionunknown80

The origin is a placeholder for a later feature, and currently defaults to unknown.

tag_string

This table is the same as tag, but for string data. Our CNC cutter also emits the G-Code currently processed. umh/v1/cuttingincorperated/cologne/cnc-cutter/_historian

{
 "timestamp_ms": 1670001247568,
  "g-code": "G01 X10 Y10 Z0"
}

Resulting in this entry:

timestampnameoriginasset_idvalue
1670001247568g-codeunknown8G01 X10 Y10 Z0

Data retrieval

SQL

  1. SSH into your instance.
  2. Open a PSQL session
  3. Select the umh_v2 database using \c umh_v2
  4. Execute any query against our tables.

Example Queries

  • Get the number of rows in your tag table:
    SELECT COUNT(1) FROM tag;
    
  • Get the newest tag row for “umh/v1/umh/cologne”:
    SELECT * FROM tag WHERE asset_id=get_asset_id('umh', 'cologne') LIMIT 1;
    
    The equivalent function, without using our helper is:
    SELECT t.* FROM tag t, asset a WHERE t.asset_id=a.id AND a.enterprise='umh' AND a.site='cologne' LIMIT 1;
    

get_asset_id(<enterprise>, <site>, <area>, <line>, <workcell>, <origin_id>) is a helper function to ease retrieval of the asset id.

All fields except <enterprise> are optional, and it will always return the first asset id matching the search.

Grafana

Follow our Data Visualization tutorial to get started.

External access (Datagrip, PGAdmin, …)

  1. SSH into your instance.

  2. Get the password of the kafkatopostgresqlv2 user

    sudo kubectl get secret timescale-post-init-pw --kubeconfig /etc/rancher/k3s/k3s.yaml -n united-manufacturing-hub -o json | jq -r '.data["1_set_passwords.sh"]' | base64 -d | grep "kafkatopostgresqlv2 WITH PASSWORD" | awk -F "'" '{print $2}'
    
  3. Use your preferred tool to connect to our umh_v2 database using the kafkatopostgresqlv2 user and the password from above command.

    datagrip
    datagrip

Last modified April 16, 2024: Update favicons (#285) (acbfbbf)