Create a route to influxdb instance

[bernd]

Does anyone have experience how to setup a route to an influxdb instance?
I am running Grafana with influxdb and would like to create a route to the database.

[zfields]

Hi @bernd,

Sorry for the delayed response, but we have two schools of thought. I didn’t quite get to the bottom of it, so I’ll just share what I’ve learned.

1. The InfluxDB website says they have a RESTful API, so in theory a simple HTTP route should be possible using our “General HTTP/HTTPS Request/Response” route.

2. Last time one of our folks used InfluxDB, they only really supported their “LINE PROTOCOL” (even over rest), not JSON. They then attempted a hack to use JSONata to map a known body into a line protocol ‘line’. They said it was unreasonably complex, so it would probably be easier to do this by just implementing a “json-to-line” service of your own.

I noticed they have a v1 and a v2 API. Since my colleague attempted this some time ago, it is unclear which version of the API they were using.

Personally, I would start down the path of #1 using the v2 API, while keeping #2 in mind.

Best,
Zak

[bernd]

Hi @zfields
Don’t worry, I am not in a hurry

I am using Grafana with influxDB V1.xxxx, so I have to check if any of the options would work.
If not, I will just add another data source to Grafana that works better with NoteHub.

It would just be easier to use the existing influxDB data source in Grafana.

[ssozonoff]

I am revisiting this topic (@zfields). While the JSONata transformation itself is fairly trivial I am wondering what happens to the output from the transformation. It seems like its getting escaped or something, could we have clarification on this ?

Here is my JSONata expression

(
    "sensor,id=" & best_id & " raw_distance=" & $number(body.raw_distance) & ",distance=" & $number(body.distance) & " " & $number(body.timestamp)
)

Some truncated test data

{
  "event": "488cf552-6093-8009-a3e5-8da9d589e000",
  "when": 1761853566,
  "file": "snow.qo",
  "best_id": "testId",
  "body": {
    "distance": 152.5,
    "raw_distance": 152.5,
    "timestamp": 1761853565
  },
  "session": "cca998f5-cd7a-4a92-b074-912f15f05ae2"
}

And the error when it tries to send it to influx

{"code":"invalid","message":"3/3 points rejected; first error: at line 1:58: invalid timestamp (\"1761853565\\\"\") (check rejected_points in your _monitoring bucket for further information)"}

Since we have no way of seeing whats actually being sent its hard to tell whats going on but the JSONata expression certainly output the timestamp as a number and not a String.

Thanks.

[zfields]

Hi Serge! Long time no see. I hope you are doing well.

I’m checking with our cloud team to see if we have any clever ways of checking the output of a JSONata transform.

I’ll let you know what they come up with.

Cheers,

Zak

[zfields]

Hi Serge,

Good news!

There has been a new Transform feature added to the events page.

First select an event (this will be the input to the JSONata transform) then click Transform in the top-right. Then choose which route has the JSONata transformation you wish to test. This will display the output of the JSONata transformation.

As far as the content itself, they also offered some suggestions.

Be sure to use a --precision=s. The default for Influx is nanoseconds, which may be the problem since the error message is complaining specifically about the timestamp.

If you wish to use nanoseconds, then you could update the JSONata to this instead:

```jsonata
(
“sensor,id=” & best_id & " raw_distance=" & $number(body.raw_distance) & “,distance=” & $number(body.distance) & " " & $number(body.timestamp) & “000000000”
)
```

Hopefully this helps!

Zak

[ssozonoff]

Hey Zak,

All good on my front thank you

So yes I am specifying a precision of “s” for Influx however the real problem as I interpret it from the error message is the “ and escape characters and I am not sure where those are coming from notehub side post transform or influx side when returning the error.

invalid timestamp (\"1761853565\\\"\")

The timestamp is a number and should have quotes or anything else around it. If I test my JSONata expression I see the following in JSONata exerciser

snowDepthSensor,id=123 raw_distance=152.5,distance=152.5 1761853565

Which looks correct to me.

Thanks for the tip regarding the new Transform feature. When testing it I see an empty body which I dont understand either. Even if I re-enable the route I get the same empty body.

Here is the transform defined for the route.

I will keep digging.

Thanks,

Serge

[ssozonoff]

OK so I tested this using Webhook site and it looks fine from the notehub side with the only exception of the double quotes around the string being sent as the body

"snowDepthSensor,id=snow-2 raw_distance=153,distance=153 1761896733"

If I test using Postman and the same string but removing the surrounding double quotes it works with InfluxDB.

I tested again thinking the “Plan text” switch my be the answer but this simply deletes my JSONata expression and writes “on” in its place, is this a bug (@zfields) or whats the purpose of that radio button ?

Thanks,

Serge

[ssozonoff]

@zfields Any feedback on what that “plain text” switch is supposed to do in this context ?

Thanks,

Serge

[RobLauer]

Hi @ssozonoff,

Sorry about the delay here! The “plain text” toggle just switches the JSONata expression editor for a code editing mode to a plain text mode with no formatting. It certainly should not be changing the content of the text box though. Let us look into this in more detail and we’ll get back to you.

Thanks,
Rob

[RobLauer]

Hi @ssozonoff,

I just tested one of your events with the JSONata transform you’re using in the JSONata Exerciser. I see the output is exactly as you showed above (the string literal). Unfortunately this isn’t something you can “fix” in JSONata…since JSONata ALWAYS returns JSON. So if the top-level result is a string, it will be JSON-encoded with quotes.

Now, I also tested this transform using the “Transform Event with Route” feature in Notehub and see that outputs something completely different (again can confirm what you show above)! This looks like a bug to me, so I’ve logged that with our team to look at ASAP.

Will be in touch.

Thanks,
Rob

[ssozonoff]

Thanks for looking into this. So it seems I need to go via some process, like NodeRed, to make the transformation and send. I was hoping to eliminate the extra step.

Cheers,

Serge