One data point with a string and a value

Welcome & Getting Started
1

I just got stared with Influx and it feels really cool!

I have a scenario where I would like to store data points like this:

mesurement tagFoo tagBar fieldText fieldNumber
foo tag1 tagA demo 1
foo tag1 tagA demo 1
foo tag1 tagA demo 1
foo tag1 tagA foobar 1
foo tag1 tagA foobar 1
foo tag1 tagA foobar 1

I always want to store the value 1 for my data point as it’s a event and my idea is to use the sum()-function to calculate the number of events for given time frames. I have a “bucket” in Influx Cloud configured like this and populated with data but when i perform queries it comes back like to different sets:

#group1
mesurement tagFoo tagBar fieldNumber
foo tag1 tagA 1
foo tag1 tagA 1
foo tag1 tagA 1

#group1
mesurement tagFoo tagBar fieldText
foo tag1 tagA foobar
foo tag1 tagA foobar
foo tag1 tagA foobar

My query is very simple, something like this:

from(bucket: “test”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: ® => r._measurement == “foo”)
|> yield()

  1. First of all I would like to get the data back as one table (like below) all the examples I’ve seen presents the data like this - what I’m I doing wrong?

mesurement tagFoo tagBar fieldText fieldNumber
foo tag1 tagA demo 1
foo tag1 tagA demo 1
foo tag1 tagA demo 1
foo tag1 tagA foobar 1
foo tag1 tagA foobar 1
foo tag1 tagA foobar 1

  1. My end goal would be to come up with a query that would sum the “fieldNumber” and present the table like this:

mesurement tagFoo tagBar fieldText fieldNumber
foo tag1 tagA demo 3
foo tag1 tagA foobar 3

Any pointers towards how I would write a query to get that?

Thank you =D

2

1) Add this to your current query
image

2) You can probably do something similar to the link below:

3

Thanks for answering!

I’ll give the first part a go, I guess that in my case the fieldText-field would be the columnKey and fieldNumber would be columnValue right?

I can’t really understand how I would use the information from the link to accomplish the sum() that I’m looking for - I’ve read the text 3 times now :slight_smile: Do you have any other pointer or examples? Or maybe if I can get a explanation of what I need to do?

Thank you for helping a total newbie :slight_smile:

4

You could probably just use that as is, or tweak it a little.

For rowKey, that specifies which columns will be kept from your original group. So in your case, your original group (the columns that showed up on the Table after the query) most likely has _time, _measurement, tagFoo, tagBar, _field, and _value (correct me I’m wrong on this). Then your rowKey will probably have to be rowKey: ["_time", “_measurement”, “tagFoo”, “tagBar”].

The columnKey specifies which column’s values you want to be displayed in their own column.

The valueColumn is the column with the values that you want to be placed under the columns created from the columnKey you specified.

Your data point contains:

  • time
  • measurement
  • tag set: tagFoo,tagBar
  • field set: fieldText, fieldNumber

Since a group contains only the time, measurement, tag set, and ONE field key/value pair, you will end up with the two groups as you saw. In the Table from your query, the field key (which is either fieldText or fieldNumber) should show up under the _field column, and the field value (which is either “demo/foobar” or 1 for their respective field keys) should show up under the _value column.

I’m actually not too sure as I haven’t done something like that before. Also, yeah I guess that link isn’t that helpful after reading your question again.

What I was thinking you could do is group the columns by “fieldText”, but you would have to make that a tag first because I don’t think you can group by a field. After you have grouped by fieldText, you can do a |>cumulativeSum() on the fieldNumber values and then a |>last() to take the last row (which will contain the total)

5

Hi!

Thank you so much Anythony! Turns out that your first example just worked.

from(bucket: “test”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: ( r ) => r._measurement == “testing”)
|> pivot(
rowKey : [“_time”],
columnKey : [“_field”],
valueColumn : “_value”
)

I’m wondering here, when the table was rendered before the column headers was fieldTest and fieldNumber - how does InfluxDb knot what “_field” and “_value” represents above? Are they automatically mapped in some way?

Turns out that I could indeed group by a field-value, I’m not sure if this might be a performance-issue but this query will not be used frequently so that is not a big concern. I guess that these “tables” just contains “Columns” after they are generated and that Columns can be used for grouping? (Get started with Flux and InfluxDB | InfluxDB OSS 2.0 Documentation)

The complete query that gives the result i was looking for looks like this:

from(bucket: “test”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: ( r ) => r._measurement == “testing”)
|> pivot(
rowKey : [“_time”],
columnKey : [“_field”],
valueColumn : “_value”
)
|> group(columns: [“fieldText”], mode:“by”)
|> sum(column: “fieldCount”)

Once again, Thank you very much Anythony - save my day =D

6

I mentioned it in my answer. When you sent the data point in, you most likely specified whether it was a field or tag. All fields (I’m referring to the name of the field) will automatically be aggregated/combined into one column called _field. The _value field will contain the respective value of whatever _field is.

So if you have 10 fields (field1,field2,field3,…,field10), then _field will have 10 possible values. If one of the rows says that _field is field2, then _value will be the value of field2.

If a data point written in InfluxDB line protocol looks like:
foo,tagFoo=tag1,tagBar=tagA fieldText=demo,fieldNumber=1

Then if _field is fieldText, _value will be demo. If _field is fieldNumber, _value will be 1.

Awesome! Helps me learn too because I am also new to InfluxDB :slight_smile: