Migrate data from MariaDB10 to Influxdb 2.0 - Timestamp problem

[PPee]

Hi All,
I’m trying to import data from an MySQL DB into an InfluxDB2.0 DB

sql.from(
  driverName: "mysql",
  dataSourceName: "<USER>:<PW>@tcp(192.168.1.242:3306)/JPHasRepo?charset=utf8",
  query: "SELECT entity_id, last_updated, state FROM states WHERE entity_id = 'sensor.ms_1_temperature'"
)

that works, giving me a table with

• entity_id
• state
• last_updated

This needs to be imported into a InfluxDB bucket named “JPHasRepo Import” with Organisation “JPOrg”.

|> to(org: "JPOrg", bucket: "JPHasRepo Import")

But when executing I get an error message: Runtime error @9:5-9:49: to: no time column detected

So I tried to point to “last_updated” as the timestamp column:

|> to(org: "JPOrg", bucket: "JPHasRepo Import", timeColumn: "last_updated")

But the reference to “last_updated” is wrong.

Any ideas how to solve this?
PPee

[Anaisdg]

Hello @PPee,
Great question!
Can you please share a screenshot of the raw data view (the little toggle in the data explorer) once you’ve queried for that table

that works, giving me a table with

• entity_id
• state
• last_updated

Or add a limit(n:5) function click the export csv button and share that output with me?
I’m asking for this because I’m curious to see what timestamp format last_updated is in. Because I agree, specifying the timeColumn explicitly should work if your timestamp is in the right format. You might need to use the time() function:

data
    |> map(fn: (r) => ({r with _time: time(v: last_updated)}))

This documentation might be helpful to you:

Work with time types | Flux Documentation

But also the to() function has the following requirements:
to() writes data structured data structure that includes, at a minimum, the following columns:

• _time
• _measurement
• _field
• _value

Which means that you’ll probably have to add something like:

data 
|> map( fn: (r) => ({_time: r.last_update, _field: "state:", _value: r.state, _measurement: "myMeasurement"})) 

But yah if you let me see what the output of your SQL table is when you query it with flux i can be more helpful.

PS what are you doing with InfluxDB? Learning about community use cases really makes my day. :))

[PPee]

Hi Anaisdg
I’m using homeassistant for some time now to monitor and control my house with about 700 sensors and switches. All information is in a MariaDB with infinite retention.

Now the database has grown to be 16G, I want to reorganize the data, keeping long term (infinite retention) info in InfluxDB and using MariaDB for short term data with retention of about 14 days.

Now I’m struggling to extract the MariaDB SQL data I would like to keep and migrate this information to InfluxDB
See below the some of the MariaDB entities, with their number of records.

.

In flux, I’m using this query

and the response in the raw table you requested looked like this:

When I add the map and to statements, i get the error no time column detected.

Is my approach OK? What goes wrong
PPee

[PPee]

Any ideas how to solve this?

[PPee]

I found the problem: a typo in the mapping. The time column is misspelled: last_update in stead off last_updated.
Thx for all your support.