InfluxQL to Flux conversion issue for select into

InfluxDB 2
1

Hi,

I am trying to convert InfluxQL query with Select Into clause to Flux.

InfluxDB Version: 2.7

Scenario:
Converted flux query is calculating time spent (RT) field using End, Start, Wait, Wasted timestamp based fields in UI.

Issue:
However, when queried from outside UI (using Influx CLI) its not returning the same value, rather RT field doesn’t have any value.

from(bucket: “Measurebucket”)
|> range(start: -30d)
|> filter(fn: (r) => r._measurement == “MeasureRT”)
|> filter(fn: (r) => r._field == “End” or r._field == “Start” or r._field == “Wait” or r._field == “Wasted”)
|> pivot(rowKey: [“_time”], columnKey: [“_field”], valueColumn: “_value”)
|> map(fn: (r) => ({r with RT: float(v: (((r.End - r.Start) - r.Wait) - r.Wasted))/1000000.0, _measurement: “MeasureRT”, _field: “RT”}))

If we use to function, then it throws error in pivot function mentioning table has no _value column.

|> to(bucket: “Measurebucket”)

Any help to workaround this would be helpful.

Thanks.

2

Hi @Abbay31,
First i recommend holding off on converting influxql queries to Flux and waiting for v3 where you can query directly with influxql.

Now lets tackle this issue.
I recommend using multiple yield() functions like print statements to understand where your transformation is going wrong. ie

from(bucket: “Measurebucket”)
|> range(start: -30d)
|> filter(fn: (r) => r._measurement == “MeasureRT”)
|> filter(fn: (r) => r._field == “End” or r._field == “Start” or r._field == “Wait” or r._field == “Wasted”)
|> yield(name:"after filter")
|> pivot(rowKey: [“_time”], columnKey: [“_field”], valueColumn: “_value”)
|> yield(name:"after pivot")
|> map(fn: (r) => ({r with RT: float(v: (((r.End - r.Start) - r.Wait) - r.Wasted))/1000000.0, _measurement: “MeasureRT”, _field: “RT”}))
|> yield(name:"after map")

nothing looks wrong with your query. It makes sense that the to function isnt working if you dont have data.

Could you share some data with me after applying

from(bucket: “Measurebucket”)
|> range(start: -30d)
|> filter(fn: (r) => r._measurement == “MeasureRT”)
|> filter(fn: (r) => r._field == “End” or r._field == “Start” or r._field == “Wait” or r._field == “Wasted”)
|> limit(n:5)

You can export to annotated csv and share that with me.
and then i can also try it on my end?

3

Hi Anaisdg,

Thanks for your inputs.

Data exists as per given sample flux query:

I could not upload csv/gz format, hence pasting below:

#group,false,false,true,true,false,false,true,true,true,true,true,true,true,true,true,true,true,true
#datatype,string,long,dateTime:RFC3339,dateTime:RFC3339,dateTime:RFC3339,long,string,string,string,string,string,string,string,string,string,string,string,string
#default,_result,
,result,table,_start,_stop,_time,_value,Emulation,GroupName,HostName,Name,Product,Region,RunID,ScriptName,Status,VuserID,_field,_measurement
,0,2025-03-30T05:50:00Z,2025-03-30T05:50:13Z,2025-03-30T05:50:06.7891404Z,1743313806789140,N/A,tc_db,Host1,07_Demo_Purchase,PR,None,TestProject_690,tc_db_1,passed,1,End,Transaction
,1,2025-03-30T05:50:00Z,2025-03-30T05:50:13Z,2025-03-30T05:50:06.7891404Z,1743313804373141,N/A,tc_db,Host1,07_Demo_Purchase,PR,None,TestProject_690,tc_db_1,passed,1,Start,Transaction
,2,2025-03-30T05:50:00Z,2025-03-30T05:50:13Z,2025-03-30T05:50:06.7891404Z,0,N/A,tc_db,Host1,07_Demo_Purchase,PR,None,TestProject_690,tc_db_1,passed,1,Wait,Transaction
,3,2025-03-30T05:50:00Z,2025-03-30T05:50:13Z,2025-03-30T05:50:06.7891404Z,2323000,N/A,tc_db,Host1,07_Demo_Purchase,PR,None,TestProject_690,tc_db_1,passed,1,Wasted,Transaction

4

Hello @Abbay31,
Sorry for the delay.
I’m getting:

 error in csv.from(): failed to read metadata: failed to read annotations: wrong number of fields

with:

import "csv"

csvData = "
#group,false,false,true,true,false,false,true,true,true,true,true,true,true,true,true,true,true,true
#datatype,string,long,dateTime:RFC3339,dateTime:RFC3339,dateTime:RFC3339,long,string,string,string,string,string,string,string,string,string,string,string,string
#default,_result,
,result,table,_start,_stop,_time,_value,Emulation,GroupName,HostName,Name,Product,Region,RunID,ScriptName,Status,VuserID,_field,_measurement
,,0,2025-03-30T05:50:00Z,2025-03-30T05:50:13Z,2025-03-30T05:50:06.7891404Z,1743313806789140,N/A,tc_db,Host1,07_Demo_Purchase,PR,None,TestProject_690,tc_db_1,passed,1,End,Transaction
,,1,2025-03-30T05:50:00Z,2025-03-30T05:50:13Z,2025-03-30T05:50:06.7891404Z,1743313804373141,N/A,tc_db,Host1,07_Demo_Purchase,PR,None,TestProject_690,tc_db_1,passed,1,Start,Transaction
,,2,2025-03-30T05:50:00Z,2025-03-30T05:50:13Z,2025-03-30T05:50:06.7891404Z,0,N/A,tc_db,Host1,07_Demo_Purchase,PR,None,TestProject_690,tc_db_1,passed,1,Wait,Transaction
,,3,2025-03-30T05:50:00Z,2025-03-30T05:50:13Z,2025-03-30T05:50:06.7891404Z,2323000,N/A,tc_db,Host1,07_Demo_Purchase,PR,None,TestProject_690,tc_db_1,passed,1,Wasted,Transaction
"

csv.from(csv: csvData)

could you please copy and paste some annotatedCSV that works theres probably a copy and paste error in there but im not sure where it is…the challenge with annotated CSV

5

Hi @Anaisdg

Could you please try to create csv for below and upload to InfluxDB using write api (/write?db={Bucket ID} with Request Body (binary)?

Transaction,Emulation=N/A,GroupName=tc_db,HostName=host1,Name=07_Demo_Purchase,Product=PR,Region=None,RunID=TestProject_690,ScriptName=tc_db_1,Status=passed,VuserID=“1” End=1743357013000000000,Start=1727675406000000000,Wait=0,Wasted=2323000 1743357006000000000

Thanks.