Storing data in two tables based on data type

Store
1

Hello folks,

I am using telegraf to collect JSON data from a source and insert it into a database in influxdb. Primarily it works well. But I have a specific requirement. This is a sample JSON structure that I receive:

{
  "Topic" : "TEST",
  "Parameters" : [ {
    "Connection" : "192.168.35.4",
    "Name" : "Data_1",
    "DataType" : "UInt32",
    "Value" : 11
  }, {
    "Connection" : "192.168.35.4",
    "Name" : "Data_2",
    "DataType" : "String",
    "Value" : "This is a String"
  } ]
}

This is an extract from the current telegraf.conf file:

[[inputs.mqtt_consumer]]
    servers = ["192.168.5.104:1883"]
    qos = 0
    topics = ["TEST"]
    data_format = "json"
    tag_keys = ["Name", "Connection"]
    json_string_fields = ["Value"]
    json_query = "Parameters"

This inserts the integer value (since it’s the first object in the json list) to my database. I want to enter just the Value with Data Type String in the database. How can we do this using telegraf? Or rather, is it possible to selectively enter the Integer value and String values in different tables of the same database using telegraf?

Thanks in Advance.

Cheers,
Subhadeep Datta

2

Hello @sahebdatta,
You could use the execd processor plugin which makes telegraf extensible in any language to split up the types… otherwise I’m not sure.

3

In your case you might actually do that using processor plugins.

Anais is right you can’t "detect" the datatype of incoming data and apply logic over it, but since you actually receive it as a value in your JSON data you can use it as any other tag/field, which gives us the possibility to apply filter on it.

The idea is to * temporarily* keep the “DataType” info you receive in the data, and use it as a filter, to redirect the data to different measurements. (you could also just change the name of the field Value -> ValueString instead of the name of the measurement)
below the sample (I didn’t test it), here is what happens

  1. add “DataType” as tag
  2. change measurement name for points with a specific “DataType” (ie: string)
    2.1 repeat point 2 for every datatype you have to manage
  3. clean the data by dropping the “DataType” tag from your renamed measurements
[[inputs.mqtt_consumer]]
    servers = ["192.168.5.104:1883"]
    qos = 0
    topics = ["TEST"]
    data_format = "json"
    #Note that "DataType" has been added
    tag_keys = ["Name", "Connection","DataType"]
    json_string_fields = ["Value"]
    json_query = "Parameters"

#Manage Strings
[[processors.rename]]
  order = 1
  # namepass = ["__MeasurementName__"] ##Optional, to apply only on a defined input measurement
  ## Specify one sub-table per rename operation.
  [[processors.rename.replace]]
    measurement = "__MeasurementName__"
    dest = "__NewString_MeasurementName__"

  [processors.rename.tagpass]
    "DataType" = ["String"]

#Manage integers
[[processors.rename]]
  order = 2
  # namepass = ["__MeasurementName__"] ##Optional, to apply only on a defined input measurement
  [[processors.rename.replace]]
    measurement = "__MeasurementName__"
    dest = "__NewInt_MeasurementName__"

  [processors.rename.tagpass]
    "DataType" = ["UInt32"]

##remove Datatype tag
[[processors.override]]
  order = 3 #execute this after managing it
  # namepass = ["__NewString_MeasurementName__","__NewInt_MeasurementName__"] ##Optional, to apply only on a defined input measurement
  tagexclude = ["DataType"]

ofc you will need to map every data type you receive. unmapped types will be written in the original measurement, and since the field name is the same Value you might end up having data type conflicts. (you can do some magic here too, maybe by keeping the “DataType” info and also cast all the data to strings in order to avoid conflicts (mainly to be able to notice the anomaly and eventually recover data).
Or you can just drop any data related to unmapped types.

2 Likes
4

Hi @Giovanni_Luisotto,

thanks for the reply. I get the idea, but unfortunately, this doesn’t work. This is what happens:
The whole EVENT table gets moved to EVENTs and the “String” data types are excluded as before. Here is an SS of the telegaf.conf file.

image
(Note: I added “DataType” in the key field and these are commented since I reverted back to the original script)

Also unfortunately, I do not have the option to change the JSON object.