How convert fieldType in fieldKey conflict

[vincent1890]

Hello

I installed a third temperature probe and unfortunately improperly programmed my script at the beginning which sent a string type data instead of float and now I really do not see how to resolve the conflict.

Can you help me, please?

use influx_db_temperature

show field keys from temperature
name: temperature
fieldKey      fieldType
--------      ---------
temperature 1 float
temperature 2 float
temperature 3 string

I wish I had that :

fieldKey      fieldType
--------      ---------
temperature 1 float
temperature 2 float
temperature 3 float

I saw this on github “The Converter processor plugin Telegraf” but I don’t understand how to install and use the plugin

telegraf/plugins/processors/converter at release-1.14 · influxdata/telegraf

[[processors.converter]]
[processors.converter.fields]
float = [“temperature 3”]

[Giovanni_Luisotto]

That won’t really help, since it will convert the incoming data and not the existing one.

the data type is linked to the single shard, this means that if you start writing your new data as floats the write will fail due to shard data type, but once a new shard is created you will be able to store your data as floats… ofc this will leave you with some “broken” string data.

Therefore the nest option for you is to:

1. Export your data
2. Edit the exported data
3. drop the existing data
4. Import the edited data

[vincent1890]

Hello @Giovanni_Luisotto thank you for your answer

I have already exported the data with the command “influxd backup -database influx_db_temperature -host localhost:8088 /tmp/backup/influxdb/influx_db_temperature” to try to modify the data with notepad++, komodo-edit but the data is unreadable which tools to use.

[Giovanni_Luisotto]

You should use the influx_inspect export command, it should be able to export the data in CSV format (it has been a while since I used it)

Have a look here
https://docs.influxdata.com/influxdb/v1.8/tools/influx_inspect/#export

[vincent1890]

Thanks for the help I finally managed to export in an editable format which is really good.
Here is the command used:

runNum = fieldTag
temperature 1 = fieldKey
temperature 2 = fieldKey
temperature 3 = fieldKey

sudo /usr/bin/influx -database '<influx_db_Database>' -host 'localhost' -username '<UserDatabase>' -password '<Password>' -execute 'select * from temperature' -format 'csv' > /home/<user>/temperature-export.csv

csv result (extracted from 4 lines out of 36977):
name,time,runNum,temperature 1,temperature 2,temperature 3
temperature,1604331871100706000,RUN,21.4,15.2,
temperature,1604403256478881000,RUN,19.3,12.3,
temperature,1604403756958970000,RUN,19.3,12.4,19.6
temperature,1604403879758941000,RUN,19.3,12.4,19.6

I then tested with the following order to re-import
curl -i -XPOST "http://localhost:8086/write?db=test" --data-binary @txtfilewithlineprotocol.csv

unfortunately i have errors every line (about 36977 lines)
‘temperature,1604331871100706000,RUN,21.4,15.2,’: missing tag value\nunable to parse
‘temperature,1604403256478881000,RUN,19.3,12.3,’: missing tag value\nunable to parse
‘temperature,1604403756958970000,RUN,19.3,12.4,19.6’: missing tag value\nunable to parse
‘temperature,1604403879758941000,RUN,19.3,12.4,19.6’: missing tag value\nunable to parse

I looked everywhere for a native solution or script but found nothing for import csv to influxdb

[Giovanni_Luisotto]

The data you post must follow the influx line protocol format.

Write data with the InfluxDB API | InfluxDB OSS 1.8 Documentation

You can edit your current data and turn them into lime protocol, or export them again in line protocol.

Both are not that hard, for the First approach the first tool that comes to mind is Excel.

For the second approach, use influx_inspect, (link in my previous answer), it will export the data to a file in line protocol format, you will just need to remove some double quotes and that’s it.

I would make some some examples or snippets but answering form the phone is harder than I thought

edit:
Exporting line-protocol using influx_inspect might avoid you some escaping troubles in case you have spaces (or other line-protocol special chars), but also in case you are missing some values (from the example I see that temperature3 is not always valued. (even if this last one is manageable in excel)

In excel something like this should do the trick (note that I’m using a proper excel table)

=CONCATENATE(
  [@name];
  ",";
  Table2[[#Headers];[runNum]]&"="&[@runNum];
  " ";
  IF(ISBLANK([@temperature 1]);"";Table2[[#Headers];[temperature 1]]&"="&[@temperature 1]);
  IF(ISBLANK([@temperature 2]);"";","&Table2[[#Headers];[temperature 2]]&"="&[@temperature 2]);
  IF(ISBLANK([@temperature 3]);"";","&Table2[[#Headers];[temperature 3]]&"="&[@temperature 3]);
)

[vincent1890]

Hello @Giovanni_Luisotto
Thanks for your help for a week I try to apply your advice but I do not understand or how to use the concatenate function in Excel sorry I use very little Excel so I am lost from or I must copy this function.
So if you still want to help me and have some screenshot or tutorials on the internet I would be really pleased because the galley I don’t understand or put the function.
Thank you and have a good day

[Giovanni_Luisotto]

Ofc
In order to be able to use it, you will need to create a table (I forgot that part)

• select the “range of data”
• insert --> table
• flag my table has headers

you can then place the below formula into the output column, looks like the names already match so it should work, in general, the formula references the different columns by name, so if something does not match just delete it ad let Excel do his magic by clicking around (on the proper cell/column)

=CONCATENATE(
  [@name];
  ",";
  Table2[[#Headers];[runNum]]&"="&[@runNum];
  " ";
  IF(ISBLANK([@temperature 1]);"";Table2[[#Headers];[temperature 1]]&"="&[@temperature 1]);
  IF(ISBLANK([@temperature 2]);"";","&Table2[[#Headers];[temperature 2]]&"="&[@temperature 2]);
  IF(ISBLANK([@temperature 3]);"";","&Table2[[#Headers];[temperature 3]]&"="&[@temperature 3]);
)

also, you might need to replace the “;” present in the formula with “,”, because that’s based on the localization… (at least the ones that separates the arguments, not sure about the ones that are used to reference columns)

EDIT:
If you still have issues I can upload an excel file with the structure, so you can just copy paste the data in there to get the result