Visuale temporal API data with Grafana

[IngMiad]

I have a question regarding STH / temporal API and visualization with Grafana. When I use STH instead of QL+Subscriptions there is another table structure in Time-Series-Database that is not very self-descriptive.

QL+Subscriptions
For QL + subscriptions table structure and queries for time series data are documented very well (see here): We will have to use table 'et', time column 'time_index' and metric column 'entity_type' for example. I tested the scenario successfully and had no issues with that.

STH
When I want to visualize data with Grafana using STH it is more complicated as the data from temporal API is spreaded across different tables (I guess due to performance reasons). I also attached a picture of table design for Scorpio, but the issue with complex table design is quite similar when I try to use Orion-LD context broker.

I can successfully query data using temporal API e.g. with Scorpio:

curl --location --request GET 'http://localhost:9090/ngsi-ld/v1/temporal/entities/urn:ngsi-ld:Animal:cow001?lastN=3' \
--header 'Link: <http://context/ngsi-context.jsonld>; rel="http://www.w3.org/ns/json-ld#context"; type="application/ld+json"' \
--header 'NGSILD-Tenant: openiot'

Response:

{
    "id": "urn:ngsi-ld:Animal:cow001",
    "type": "Animal",
    "heartRate": [
        {
            "type": "Property",
            "value": 51,
            "instanceId": "urn:ngsi-ld:326baa39-0c97-481b-b540-52943c78a566",
            "observedAt": "2022-10-18T08:33:11.091Z",
            "unitCode": "5K",
            "providedBy": {
                "type": "Relationship",
                "object": "urn:ngsi-ld:Device:cow001"
            }
        },
        {
            "type": "Property",
            "value": 51,
            "instanceId": "urn:ngsi-ld:9dd57d23-131f-454a-88fc-6a926fcd5bc8",
            "observedAt": "2022-10-18T08:33:06.117Z",
            "unitCode": "5K",
            "providedBy": {
                "type": "Relationship",
                "object": "urn:ngsi-ld:Device:cow001"
            }
        },
        {
            "type": "Property",
            "value": 52,
            "instanceId": "urn:ngsi-ld:176464c8-213b-4bac-aaca-8a421ae33376",
            "observedAt": "2022-10-18T08:32:46.057Z",
            "unitCode": "5K",
            "providedBy": {
                "type": "Relationship",
                "object": "urn:ngsi-ld:Device:cow001"
            }
        }
    ],
    "phenologicalCondition": {
        "type": "Property",
        "value": "maleAdult",
        "instanceId": "urn:ngsi-ld:a0d7d0fd-5e58-473b-9a14-3a5288dcacf3"
    },
    "reproductiveCondition": {
        "type": "Property",
        "value": "active",
        "instanceId": "urn:ngsi-ld:20a26af4-ad72-4519-9f91-9c28dcd4859c"
    },
    "location": [
        {
            "type": "GeoProperty",
            "value": {
                "type": "Point",
                "coordinates": [
                    13.395,
                    52.472
                ]
            },
            "instanceId": "urn:ngsi-ld:ee8f75e5-5cfb-4a2f-97a1-3d7056948950",
            "observedAt": "2022-10-18T08:33:11.091Z",
            "providedBy": {
                "type": "Relationship",
                "object": "urn:ngsi-ld:Device:cow001"
            }
        },
        {
            "type": "GeoProperty",
            "value": {
                "type": "Point",
                "coordinates": [
                    13.398,
                    52.472
                ]
            },
            "instanceId": "urn:ngsi-ld:42295ecb-cd15-427f-9c81-978122fa6e7b",
            "observedAt": "2022-10-18T08:33:06.117Z",
            "providedBy": {
                "type": "Relationship",
                "object": "urn:ngsi-ld:Device:cow001"
            }
        },
        {
            "type": "GeoProperty",
            "value": {
                "type": "Point",
                "coordinates": [
                    13.398,
                    52.472
                ]
            },
            "instanceId": "urn:ngsi-ld:4f1ba3ba-0d3c-4559-b3cf-a751b2504672",
            "observedAt": "2022-10-18T08:32:46.057Z",
            "providedBy": {
                "type": "Relationship",
                "object": "urn:ngsi-ld:Device:cow001"
            }
        }
    ],
    "name": {
        "type": "Property",
        "value": "Beany",
        "instanceId": "urn:ngsi-ld:bd086d79-3ef0-4c92-99a2-79fba997b485"
    },
    "legalID": {
        "type": "Property",
        "value": "M-bull001-Beany",
        "instanceId": "urn:ngsi-ld:1d6d9550-e291-4993-b608-86fbea0b5be7"
    },
    "sex": {
        "type": "Property",
        "value": "male",
        "instanceId": "urn:ngsi-ld:8ae2f16d-fc5b-4f98-9338-1d4839791dea"
    },
    "species": {
        "type": "Property",
        "value": "dairy cattle",
        "instanceId": "urn:ngsi-ld:0924ad31-13c6-4cff-9f8f-058a8bbef80e"
    }
}

But due to the table design I can't find the entity 'Animal' or attribute e.g. 'heartrate' anywhere in the postgres table design. I also attached table content as csv from most promising table names (entitiy, temporalentity and temporalentityattrinstance), but it just includes buildings and farms.

• temporalentityattrinstance_202210181206.csv
• temporalentity_202210181205.csv
• entity_202210181220.csv

Question
Could you assist how data can be queried from time-series-database when I use Grafana instead of temporal API? Where are the entities and their temporal data stored in case of STH?

[IngMiad]

Okay I think I got it - There will be used different databases for different tenants in postgres. So relevant data from farm tutorial will be in database ngb<tenant-name> instead of database ngb. So I have been able to find all relevant data in database names 'ngbopeniot'.

There you can find all temporal data in the two tables temporalentity and temporalentityattrinstance in case of Scorpio.

In order to visualize your data with Grafana you will need to:

1. connect to correct tenant db (here: nbgopeniot)
2. Perform a query where you
   • Select observedat, temporalentity_id and value
   • Filter by kind of entites you want to visualize (here for example pig014 and only non-empty values)

   SELECT
     observedat AS "time",
     temporalentity_id AS metric,
     value
   FROM temporalentityattrinstance
   WHERE
     $__timeFilter(observedat) AND
     temporalentity_id = 'urn:ngsi-ld:Animal:pig014' AND
     value = value
   ORDER BY 1,2
   

3. Add a transformation to the query so that the value field will be converted to a numeric field (otherwise this field is String by default and can't be visualized by Grafana as a graph [just table])

I attached a successful screenshot for pig014.

Just as a suggestion to also help others: Maybe you can add to documentation / tutorial how I can visualize STH data in Grafana in the future as well - I needed quite a long time to figure that out (I asked the question initially at stack overflow 3 months ago with no answer so far https://stackoverflow.com/questions/72995260/question-on-visualization-of-fiware-sth-data-with-grafana).