Write MySQL data with EPOCH timestamp to Influxdb bucket

Hi,

I am new to Influxdb. I have a MySql database which logs certain sensor data with dataPointID, point value and a timestamp in Epoch format. Below is the sample of the mysql table:
image

The influxdb task I have constructed is as below:

import “sql”
import “influxdata/influxdb”

option task = {name: “sql_query”, every: 10m}

info =
sql.from(
driverName: “mysql”,
dataSourceName: “root:root@tcp(192.168.1.xxx:3306)/scada”,
query:
“SELECT FROM_UNIXTIME(ts/1000) AS ‘_time’, dataPointId AS _field, pointValue AS _value FROM pointValues LIMIT 100000”,
)
|> yield(name: “info”)
|> map(fn: (r) => ({r with _time: now(), _measurement: “MyMeasurement”}))
|> group(columns: [“_measurement”])
|> influxdb.wideTo(bucket: “scada”)

Please note that, after searching on the internet I have converted the timestamp which is ‘ts’ as UNIX_TIMESTAMP.

This task executes successfully. However, when I go to the ‘Explore’ tab, the columns are not as expected:

The _field column should correspond to the mysql field ‘dataPointID’, but that’s not the case. It again displays “_field” for each row,

Could someone please help in correcting / formatting the query so that the three important fields I am calling in my query as written correctly in Influxdb?

Thanks you.

Hello @Shan,
you can use the fieldsAsCol() function to pivot your data in the expected shape.