Collect OPC-UA Data using Telegraf into QuestDB in a dense format

Question

I am collecting OPC UA data with Telegraf using the OPC-UA plugin. All is good and works as expected. I can read data from my server and I can see the data into QuestDB. However, since Telegraf is getting each different metric as a different row, I end up with a sparse dataset in which for each row I have only the timestamp, the tags, and the value for one column, even when the metrics in such columns are from the same exact timestamp.

Any pointers?

This my working telegraf.conf file

[agent]
  omit_hostname = true  

[[inputs.opcua]]
  endpoint = "${OPCUA_ENDPOINT}"
  connect_timeout = "30s"
  request_timeout = "30s"
  security_policy = "None"
  security_mode = "None"
  auth_method = "Anonymous"
  name_override = "${METRICS_TABLE_NAME}"  # Common measurement name for all metrics

  [[inputs.opcua.nodes]]
    name = "ServerLoad"
    namespace = "2"
    identifier_type = "s"
    identifier = "Server/Load"
   
  [[inputs.opcua.nodes]]
    name = "ServerRAM"
    namespace = "2"
    identifier_type = "s"
    identifier = "Server/RAM"
   

  [[inputs.opcua.nodes]]
    name = "ServerIO"
    namespace = "2"
    identifier_type = "s"
    identifier = "Server/IO"
 

[[outputs.influxdb_v2]]
  urls = ["${QUESTDB_HTTP_ENDPOINT}"]
  token = "${QUESTDB_HTTP_TOKEN}"
  content_encoding = "identity"  # Important to ensuring no gzip encoding
 

Answer 1

In Telegraf you can merge your metrics into one if the timestamp for the metrics is the same and all the tags match by using an aggregator. I don't know if your metrics have any common tags, but if not, you can add an extra tag to each of them. The name of the tag is not important.

  [[inputs.opcua.nodes]]
    name = "ServerLoad"
    namespace = "2"
    identifier_type = "s"
    identifier = "Server/Load"
    default_tags = { source="opcua_merge" }

  [[inputs.opcua.nodes]]
    name = "ServerRAM"
    namespace = "2"
    identifier_type = "s"
    identifier = "Server/RAM"
    default_tags = { source="opcua_merge" }

  [[inputs.opcua.nodes]]
    name = "ServerIO"
    namespace = "2"
    identifier_type = "s"
    identifier = "Server/IO"
    default_tags = { source="opcua_merge" }

And now, before the output section you can add the merge definition

[[aggregators.merge]]
  drop_original = true
  tags = ["source"]  # Merge metrics with the same value for the source tag

In this case, since we are adding the same value for the source tag, as long as the timestamp is the same they will merge. For this to work, you need to make sure you don't have any other tags with different values in each metric. If you have other tags and they can have different values but you still want to merge, you could either convert from tags to strings using the telegraf converter processor, or just drop the tags if you don't need them using the override.tags processor, as in:

[[processors.override]]
  [processors.override.tags]
    id = ""  # Removing the 'id' tag

You could also drop the source tag if you don't want it on the final table using the same processor.