Historian

Our database for the umh.v1 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, …

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

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,
  "pos_y": 7.3,
  "pos_z": 3.2,
  "temperature": 50.0,
  "collision": false
}

This will result in the following table entries:

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:

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.