Is it possible to format resulting numbers?

,
1

Hi, I’m querying InfluxDB OSS 2.7 with this query

from(bucket: "fve")
  |> range(start: -1m, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "realtime")
  |> filter(fn: (r) => r["_field"] == "Batpower_Charge1" or 
                       r["_field"] == "Battery_Capacity" or
                       r["_field"] == "feedin_power" or 
                       r["_field"] == "FeedinPower_Tphase" or 
                       r["_field"] == "FeedinPower_Sphase" or 
                       r["_field"] == "FeedinPower_Rphase")
  |> mean()
  |> pivot(rowKey: ["_stop"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({r with time: uint(v: r._stop) / uint(v: 1000000000)}))
  |> drop(columns: ["result", "_start", "_stop", "_measurement"])

and i get this response

,result,table,Batpower_Charge1,Battery_Capacity,FeedinPower_Rphase,FeedinPower_Sphase,FeedinPower_Tphase,feedin_power,time
,_result,0,0,98,350.45,350.78333333333336,351.9,1054,1724162788

I have several questions.

  • why there is leading comma? this creates empty column when parsing
  • why I can’t get rid of result and table columns?
  • I’m querying from system with limited resources and standard float number parsing takes a lot of flash and is akward to use. Is it possible constrain format of these numbers (for example scientific notation with fixed number of mantissa digits) i could then create simple custom parser.
2

@marun How are you querying the data? Directly through the API? Through a client library? Data Explorer?

The reason I ask is because the response format of the api/v2/query endpoint is annotated CSV, but your results don’t include the annotations I would expect to see (datatype, group, default). And the reason for the leading comma is actually because of the expected annotations.

3

HI, I’m using HTTP API directly.
For this query i used Cartero with these settings

version = 1
url = "http://fve.local:8086/api/v2/query?org=doma"
method = "POST"

[body]
type = "raw"
format = "octet-stream"
body = """
from(bucket: \"fve\")
  |> range(start: -1m, stop: now())
  |> filter(fn: (r) => r[\"_measurement\"] == \"realtime\")
  |> filter(fn: (r) => r[\"_field\"] == \"Batpower_Charge1\" or 
                       r[\"_field\"] == \"Battery_Capacity\" or
                       r[\"_field\"] == \"feedin_power\" or 
                       r[\"_field\"] == \"FeedinPower_Tphase\" or 
                       r[\"_field\"] == \"FeedinPower_Sphase\" or 
                       r[\"_field\"] == \"FeedinPower_Rphase\")
  |> mean()
  |> pivot(rowKey: [\"_stop\"], columnKey: [\"_field\"], valueColumn: \"_value\")
  |> map(fn: (r) => ({r with time: uint(v: r._stop) / uint(v: 1000000000)}))
  |> drop(columns: [\"result\", \"_start\", \"_stop\", \"_measurement\", \"table\"])
"""

[headers]
Accept = "application/csv"
Content-Type = "application/vnd.flux"
Authorization = "Token y-Gd72GSfzgvpk0QCG6XQ0SU744n7foy1axsP9NYCzeppswWn6aN_rx300nqEsqyM6W6p-vj2-pGt7eBaTQBYw=="

[variables]

This is HTTP header from wireshark

Frame 67: 784 bytes on wire (6272 bits), 784 bytes captured (6272 bits) on interface any, id 0
Linux cooked capture v1
Internet Protocol Version 4, Src: 192.168.1.67, Dst: 192.168.1.191
Transmission Control Protocol, Src Port: 50002, Dst Port: 8086, Seq: 328, Ack: 1, Len: 716
[2 Reassembled TCP Segments (1043 bytes): #66(327), #67(716)]
Hypertext Transfer Protocol
    POST /api/v2/query?org=doma HTTP/1.1\r\n
        [Expert Info (Chat/Sequence): POST /api/v2/query?org=doma HTTP/1.1\r\n]
            [POST /api/v2/query?org=doma HTTP/1.1\r\n]
            [Severity level: Chat]
            [Group: Sequence]
        Request Method: POST
        Request URI: /api/v2/query?org=doma
            Request URI Path: /api/v2/query
            Request URI Query: org=doma
                Request URI Query Parameter: org=doma
        Request Version: HTTP/1.1
    Host: fve.local:8086\r\n
    Accept-Encoding: deflate, gzip\r\n
    content-type: application/vnd.flux\r\n
    authorization: Token y-Gd72GSfzgvpk0QCG6XQ0SU744n7foy1axsP9NYCzeppswWn6aN_rx300nqEsqyM6W6p-vj2-pGt7eBaTQBYw==\r\n
    accept: application/csv\r\n
    user-agent: curl/8.6.0-DEV isahc/1.7.2\r\n
    Content-Length: 716\r\n
        [Content length: 716]
    \r\n
    [Full request URI: http://fve.local:8086/api/v2/query?org=doma]
    [HTTP request 1/1]
    [Response in frame: 70]
    File Data: 716 bytes
Media Type
    Media type: application/vnd.flux (716 bytes)

this is response header

Frame 70: 452 bytes on wire (3616 bits), 452 bytes captured (3616 bits) on interface any, id 0
Linux cooked capture v1
Internet Protocol Version 4, Src: 192.168.1.191, Dst: 192.168.1.67
Transmission Control Protocol, Src Port: 8086, Dst Port: 50002, Seq: 1, Ack: 1044, Len: 384
Hypertext Transfer Protocol, has 2 chunks (including last chunk)
    HTTP/1.1 200 OK\r\n
        [Expert Info (Chat/Sequence): HTTP/1.1 200 OK\r\n]
            [HTTP/1.1 200 OK\r\n]
            [Severity level: Chat]
            [Group: Sequence]
        Response Version: HTTP/1.1
        Status Code: 200
        [Status Code Description: OK]
        Response Phrase: OK
    Content-Type: text/csv; charset=utf-8\r\n
    Vary: Accept-Encoding\r\n
    X-Influxdb-Build: OSS\r\n
    X-Influxdb-Version: v2.7.10\r\n
    Date: Tue, 20 Aug 2024 15:54:40 GMT\r\n
    Transfer-Encoding: chunked\r\n
    \r\n
    [HTTP response 1/1]
    [Time since request: 0.093171441 seconds]
    [Request in frame: 67]
    [Request URI: http://fve.local:8086/api/v2/query?org=doma]
    HTTP chunked response
        Data chunk (175 octets)
            Chunk size: 175 octets
            Chunk data [truncated]: 2c726573756c742c7461626c652c426174706f7765725f436861726765312c426174746572795f43617061636974792c46656564696e506f7765725f5270686173652c46656564696e506f7765725f5370686173652c46656564696e506f7765725f5470686173652c66656
            Chunk boundary: 0d0a
        End of chunked encoding
            Chunk size: 0 octets
        \r\n
    File Data: 175 bytes
Media Type
    Media type: text/csv; charset=utf-8 (175 bytes)
4

Ok, so when using the API directly and passing just the query in the request body, the result doesn’t include any of the annotations. Instead, you should pass a JSON object that that includes the following fields:

  • query: The Flux query string
  • dialect: Results format options
    • annotations: What annotations to include
      • [“group”, “datatype”, “default”]

For example:

{
    "query": "from(bucket: \"fve\") range(start: -1m, stop: now() ...",
    "dialect": {"annotations": ["group", "datatype", "default"]},
}

This ensures all the annotations are returned with the CSV response body. You can then use the annotations to correctly type column values.

5

Thanks for this info.
I created new query with this payload

{
"dialect" :
{
  "annotations":
  [
    "group",
    "datatype"
  ],
  "encoding": "utf-8",
  "lineTerminators": ["\n"],
  "quoteChar": "\"",
  "doubleQuote": true,
  "skipRows": 0,
  "commentPrefix": "#",
  "header": true,
  "headerRowCount": 1,
  "delimiter": ",",
  "skipColumns": 0,
  "skipBlankRows": false,
  "skipInitialSpace": false,
  "trim": false,
  "dateTimeFormat": "RFC3339",
  "delimiter": ",",
  "header": true
},
"query": "from(bucket: \"fve\") |> range(start: -1m, stop: now()) |> filter(fn: (r) => r[\"_measurement\"] == \"realtime\") |> filter(fn: (r) => r[\"_field\"] == \"Batpower_Charge1\" or r[\"_field\"] == \"Battery_Capacity\" or r[\"_field\"] == \"feedin_power\" or r[\"_field\"] == \"FeedinPower_Tphase\" or r[\"_field\"] == \"FeedinPower_Sphase\" or r[\"_field\"] == \"FeedinPower_Rphase\") |> mean() |> pivot(rowKey: [\"_stop\"], columnKey: [\"_field\"], valueColumn: \"_value\") |> map(fn: (r) => ({r with time: uint(v: r._stop) / uint(v: 1000000000)})) |> drop(columns: [\"result\", \"_start\", \"_stop\",  \"_measurement\", \"table\"])"
}

result is

#group,false,false,false,false,false,false,false,false,false
#datatype,string,long,double,double,double,double,double,double,unsignedLong
,result,table,Batpower_Charge1,Battery_Capacity,FeedinPower_Rphase,FeedinPower_Sphase,FeedinPower_Tphase,feedin_power,time
,_result,0,-373.1666666666667,95,-5.266666666666667,-1.6833333333333333,4.166666666666667,-2.683333333333333,1724173883

I now have annotation in my query, but that’s it, i still have leading comma and i don’t see a way to format my result

6

Formatting the actual return values has to be done client-side. If you use a CSV parser that transforms the CSV data into a format native to whatever runtime you’re using, you can then use that runtime to format the values however you’d like. There’s no way to change the type representation in the returned CSV.

7

Ok, formatting can’t be done.
But what about that leading comma?
Why I can’t get rid of certain columns?
Also i forgot to mention, query result allways contains 2 line breaks at the end (\r\n\r\n), which i don’t like either, can I do something wit that?

8

But what about that leading comma?

The leading comma actual designates an empty column at the beginning of each row. This is due to the annotation rows where the annotation type is specified in the first column of the annotation rows. If you want to strip the leading comma/column, you’ll have to process the CSV after it returns from the server.

Why I can’t get rid of certain columns?

The result and table columns are essentially metadata columns that identify how InfluxDB has structured the data:

  • result: Identify a unique result set (yield()) that the row belongs to. If you have multiple yield()s, you will have multiple result values.
  • table: Identifies a table that the row belongs to. Flux queries return a stream of tables.

If you want to remove these columns, you’ll have to process the CSV after it returns from the server.

Also i forgot to mention, query result allways contains 2 line breaks at the end (\r\n\r\n ), which i don’t like either, can I do something wit that?

There are limited options for structuring the CSV as it gets sent back from the server. This is not one of them. If you want to remove trailing line breaks, you’ll have to process the CSV after it returns from the server.

9

What exactly are you building? Is it an application that’s interacting with InfluxDB? If so, what language are you using to build the application? There are multiple client libraries that will process the query results and parse them into a format the runtime can more easily work with.

10

I have stm32 running freertos and i’m using C++. I want to keep parsing of csv as minimal as possible to reduce FLASH usage but also RAM usage. That means I want as much processing to be done at server side.
None of your client libraries are suitable for my application right now.