Ask Your Question
1

Transform JSON data for ingest into InfluxDB

asked 2019-08-21 05:47:17 -0600

traviss gravatar image

updated 2019-08-21 11:53:52 -0600

metadaddy gravatar image

Hi, i am sending data with StreamSets in JSON format:

{ "host_ip":"myhostname", "src":"mysrc", "devicetype":"mytype", "rows":[{ "startTime":1566370700980, "endTime":1566370716231, "MemoryPoolType":1, "MemoryPoolName":"Processor", "MemoryPoolUsed":337798920 }]}

I have a problem to write them to InfluxDB with SELECT because this json has nested fields after

"rows": ...

Is there any way how to transform my input to format like:

{ "host_ip":"myhostname", "src":"mysrc", "devicetype":"mytype", "startTime":1566370700980, "endTime":1566370716231, "MemoryPoolType":1, "MemoryPoolName":"Processor", "MemoryPoolUsed":337798920 }

Thanks

edit retag flag offensive close merge delete

2 Answers

Sort by ยป oldest newest most voted
1

answered 2019-08-21 11:53:00 -0600

metadaddy gravatar image

Yes - you can do this with a combination of the Field Pivoter, Field Flattener and Field Renamer processors. See Transform Data in StreamSets Data Collector for a very similar example.

edit flag offensive delete link more

Comments

So, i tried first PIVOTER from /rows to / followed by Flattener with . and mu output looks like: { "startTime":1566370700980, "endTime":1566370716231, "MemoryPoolType":1, "MemoryPoolName":"Processor", "MemoryPoolUsed":337798920 } host_ip, src etc are gone. Why?

traviss gravatar imagetraviss ( 2019-08-27 03:28:17 -0600 )edit
0

answered 2019-08-27 03:57:22 -0600

traviss gravatar image

updated 2019-08-27 03:59:04 -0600

Let's start from the beginning, rawdata looks like this:

{ "host_ip":"myhost1", "src":"mysrc", "devicetype":"mytype", "rawdata":"{\"deviceid\":\"mydevceid\",\"serviceid\":\"asr9k\",\"rows\":[{\"startTime\":1566892824862,\"endTime\":1566892840381,\"cbQosConfigIndex\":747680847}]}","timestamp":1566892840381}

So in SS i used JSON Parser to parse filed /rawdata to /metrics and then Field Remover to remove field /rawdata and Field Merger to merge /metrics to / and output looks like this:

{ "host_ip":"myhost1", "src":"mysrc", "devicetype":"mytype", "devicetype":"mydeviceid", "rows": [ {"startTime":1566893359649,"endTime":1566893374955,"cbQosConfigIndex":748153215}, {"startTime":1566893359649,"endTime":1566893374955,"cbQosConfigIndex":748153215} ]}

Then i used PIVOTER from /rows to / followed by Flattener with . and my output looks like: { "startTime":1566370700980, "endTime":1566370716231, "cbQosConfigIndex":748153215}, "startTime":1566370700980, "endTime":1566370716231, "cbQosConfigIndex":748153215}, } host_ip, src etc are gone. Why?

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-08-21 05:47:17 -0600

Seen: 54 times

Last updated: Aug 27