influxDB: How to convert field to tag in influxDB v2.0

InfluxDB 2
1

Hello,

Is there any way to convert fields to tags on existing measurement with many points in influxDB v2.0 ?

We did found the solution but for v1.8 and below. Please any help appreciated.

We tried using below query to copy one measurement data into another but not sure how we can change field to tag using flux query.

from(bucket: "bucket_name")
    |> range(start: -10y)
    |> filter(fn: (r) => r._measurement == "cu_om")
    |> aggregateWindow(every: 5s, fn: last, createEmpty: false)
    |> yield(name: "last")
    |> set(key: "_measurement", value: "cu_om_new")
    |> to(org: "org_name", bucket: "bucket_name")

Regards,
Dhyanesh

2

Hello @dhyaneshnaik,
Welcome!
Can you please tell me a little bit more about your schema?
How many tags do you have? How many fields do you have?
Will this once-field, now-tag span multiple fields?
How much data are you querying for from the past 10 years?
What do you intend to do after this conversion?
Do you need to both convert the field to the tag and delete the old field? Because if you convert a field to a tag and then delete the field you’ll be deleting that series with your converted tag.

Thanks!

import "csv"
csvData = "#group,false,false,true,true,false,false,true,true,true
#datatype,string,long,dateTime:RFC3339,dateTime:RFC3339,dateTime:RFC3339,double,string,string,string
#default,mean,,,,,,,,
,result,table,_start,_stop,_time,_value,_field,_measurement,sensor_id
,,0,2021-04-01T15:15:27Z,2021-08-30T15:15:27.093Z,2021-08-20T01:28:00Z,34.854178689956115,humidity,airSensors,TLM0101
,,1,2021-04-01T15:15:27Z,2021-08-30T15:15:27.093Z,2021-08-20T01:28:00Z,34.81859620710558,humidity,airSensors,TLM0100
,,2,2021-04-01T15:15:27Z,2021-08-30T15:15:27.093Z,2021-08-20T01:28:00Z,0.4464200933827537,co,airSensors,TLM0100
,,3,2021-04-01T15:15:27Z,2021-08-30T15:15:27.093Z,2021-08-20T01:28:00Z,0.6347294407296836,co,airSensors,TLM0101
" 
csv.from(csv: csvData)
  |> map(fn: (r) => ({
    r with
    newtag: "humidity"
    })
  )

map will create a new tag across the two fields. You can copy and paste that code to see how it affect the schema.

Then you can filter out your old field and copy everything but your old field to a new bucket with a new measurement.

3

Hello @Anaisdg

  1. We have 10-15 tags and around 1000 fields
  2. It will span to multiple fields
  3. Actually past 1 month data I am querying
  4. I want to use this tag as filter after converting
  5. I want to delete the old field and series as well

Sure will try above code and check.

Regards,
Dhyanesh

4

Hello @Anaisdg ,

I tried using map but with map I am not able to meet the requirement.

Tried below query which filters field and convert it to tag in the new measurement, but it only copies a single field as I have used filter. How can I add other fields as well in the final data which will be inserted in the new measurement?

from(bucket: "bucketName")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "measurementName")
  |> filter(fn: (r) => r["_field"] == "fieldName")
  |> aggregateWindow(every: 5s, fn: last, createEmpty: false)
  |> yield(name: "last")
  |> map(fn: (r) => ({
    r with
    fieldName: r._value
    })
  )	
  |> set(key: "_measurement", value: "newMeasurementName")
  |> to(org: "orgName", bucket: "newBucketName")

Could you please help me out with the query ?

Our requirement is to change the field to tag for existing data which we have in influxdb so that we use it as filter on past data as well.
For new incoming data we have already updated the telegraf.conf file to consider that field as a tag.

Regards,
Dhyanesh

5

You can remove the field filter to get all your fields.
Here you go:

myField = from(bucket: "Air sensor sample dataset")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "airSensors")
  |> filter(fn: (r) => r["_field"] == "co")
  |> filter(fn: (r) => r["sensor_id"] == "TLM0100")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> findRecord(fn: (key) => true, idx: 0)

data = from(bucket: "Air sensor sample dataset")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "airSensors")
  |> filter(fn: (r) => r["_field"] != "co")
  |> filter(fn: (r) => r["sensor_id"] == "TLM0100")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)

data
  |> map(fn: (r) => ({ r with muyfield: string(v: myField._value)}))
  |> set(key: "_measurement", value: "my_new_measurement")
  |> to(
  bucket: "my-bucket",
  org: "my-org",
  timeColumn: "_time",
  tagColumns: ["myfield", "sensor_id"]
)

You don’t have to include the tagColumns as all columns with string values will default to tags, but you can define them explicitly.

1 Like
6

Thank you @Anaisdg. This solved my problem.

7

Hi @Anaisdg

Can I use above query to convert tag to field as well ?

Regards,
Dhyanesh

8

Hello @dhyaneshnaik,
Converting tag to field would be easier, but yes you can model it off of that.

data = from(bucket: "Air sensor sample dataset")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "airSensors")
  |> filter(fn: (r) => r["sensor_id"] == "TLM0100")

data
  |> set(key: "_measurement", value: "my_new_measurement")
  |> to(
  bucket: "my-bucket",
  org: "my-org",
  timeColumn: "_time",
  tagColumns: ["myfield"],
  fieldFn: (r) => ({ "sensor_id": r.sensor_id })
)
)

You can simply use the fieldFn parameter of the to() function to convert a tag into a field.

9

Hello @Anaisdg

We will use the above approach in future if needed for conversion. Thank you for your help.

1 Like
10

@dhyaneshnaik you’re so welcome! That’s what we’re here for :slight_smile:

11

How on earth do any of you look at the code golf discussed in this thread, and think its a solution.

12

Hello @Erich_Spaker,
Can you help me understand what part doesn’t meet your needs?
Thank you.

13

Hi @Anaisdg I have BCM_Commandmode as a field in my measurement .i wanted to convert field into Tag .How i can do it within the same measurement in influxdb itself. Can you please help me to do this?

14

Hi @Anaisdg
I have a similar problem where I need to lower the cardinality of my bucket. A tag in one of my measurement is causing this issue. I need to move the values of this tag to a new field and then remove this tag. I have tried your approach but, it only copies the values of the tag column to the new measurement, however I need all the rest of the fields and tags as well.