When migrate MySQL to InfluxDB, How to change Int column to Time column?

InfluxDB 2
1
  • I write this query for migrate MySQL table. But it is not working.
  • I see “time()” function of document. But migration example was not there.
sql.from(
  driverName: "mysql",
  dataSourceName: "${MYSQL_ACCOUNT}@tcp(mysql)",
  query: "SELECT id, UNIX_TIMESTAMP(created_at) as created_at, gender, birth_year, bank_code FROM tbl_user limit 100"
)  
  |> rename(columns: {id: "user_id"})
  |> rename(columns: {created_at: "user_created_at"})
  |> rename(columns: {gender: "user_gender"})  
  |> rename(columns: {birth_year: "user_birth_year"})  
  |> rename(columns: {bank_code: "user_bank_code"})
  |> duplicate(column: "user_created_at", as: "_time")
  |> set(key: "_measurement", value: "tbl_user")
  |> to(org: "cnf",bucket: "push_user_filter")
  • Error message is here.
runtime error @18:6-21:4: to: column _time of type string is not of type time

I want to convert “user_created_at” to “_time” But it is very hard. Please save my life. help me!!!

4

Solution is here.

info = sql.from(
  driverName: "mysql",
  dataSourceName: "${MYSQL_ACCOUNT}@tcp(mysql)",
  query: "SELECT id, UNIX_TIMESTAMP(created_at ) * 1000000000 as created_at, gender FROM tbl_user limit 100"
)
  |> rename(columns: {id: "user_id"})
  |> rename(columns: {created_at: "user_created_at"})
  |> rename(columns: {gender: "user_gender"})  
  |> duplicate(column: "user_created_at", as: "_time")
  |> duplicate(column: "user_id", as: "_field")
  |> duplicate(column: "user_gender", as: "_value")
  |> map(fn:(r) => ({ r with _time: time(v: r.user_created_at) }))
  |> yield(name: "info")
  |> set(key: "_measurement", value: "tbl_user")
  |> to(
  bucket:"push_user_filter",
  org:"cnf",
)
  1. First i duplicate “user_created_at” column.
  2. Then use “map()” function with “time()” function.
  3. Create columns to require InfluxDB. For example, “_measurement”, “_value”, “_field”
  4. Use “to()” function for save another bucket
  5. Success my problem!