Converting Continuous Query to Tasks

Fluxlang
,
1

Folks, I am newbie on v2 where I am converting from v1.8 to v2. I am struggling to convert some continuous queries to tasks. One example CQ looks like this:

cq_01m CREATE continuous query cq_01m ON openhab_db
BEGINSELECT   Last(state)                  AS state,
           Mean(grid_voltage)           AS grid_voltage,
           Mean(grid_current)           AS grid_current,
           Mean(charge_discharge_power) AS charge_discharge_power,
           Mean(batt_voltage)           AS batt_voltage,
           Mean(batt_current)           AS batt_current,
           Mean(batt_capacity)          AS batt_capacity,
           Mean(batt_temp)              AS batt_temp,
           Mean(feed_in_out_power)      AS feed_in_out_power,
           Mean(load_power)             AS load_power,
           Mean(input_output_power)     AS input_output_power,
           Mean(generation_power)       AS generation_power,
           Last(today_gen)              AS today_gen,
           Last(today_sell_grid)        AS today_sell_grid,
           Last(today_buy_grid)         AS today_buy_grid,
           Last(today_load_use)         AS today_load_use,
           Last(battery_cycle)          AS battery_cycle
  INTO     openhab_db.two_years.historical_me3000sp
  FROM     openhab_db.two_days.live_me3000sp
  GROUP BY time(1m)
END

One change here is I have another field in this new db where all these entries are for each given user by a field name “user”, e.g. user bob will have field “state” value completely different from user foo, tasks will either take last value, make average or sum of values of fields every minute. This is basically downsampling where original data is flowing every few seconds.

Will appreciate some help here.

2

Hello @ai001,
May I ask what is the reason for upgrading to 2.x?
I would first recommend holding off on committing to an upgrade to 2.x and waiting for OSS 3.x as InfluxQL and SQL will be supported, there are really significant performance benefits, and there is more support for Pandas which is my preference for data transformation. However there any plans around creating a task scheduler or CQ hasn’t been confirmed for 3.x so you’d have to use a FaaS like AWS lambda. But you can also use something like quix. (example shown below).

I know that doesn’t really answer your question. I’m just trying to save you from having to do a lot of work learning Flux…

3

Hi, thanks for getting back to me. I was just trying it out tbh and then stumbled upon these blockers for my existing setup. Not much issue in the v1.8, I just thought latest version will be better with more flexibility but I am more concentrated on the functionality achieved by a product not facing steep re-learning curve. I believe many users got on to the influx bus as the v1.8 queries were very close to the SQL but not look at v2 queries, they look more like programming language on their own, a bit daunting in the first impression. Ofcourse if good time is spent in it one can master the new query language just like any new technology, but given there are so many other technical part of projects is involved now it is additional bit of learning with not much extra to gain in my opinion.

4

Here is some documentation on it:

data = from(bucket: "openhab_db.two_days.live_me3000sp")
  |> range(start: -task.every)
  |> filter(fn: (r) => r._measurement == "example-measurement")
  


last_fields = data    |> filter(fn: (r) => r._field == "state" or r._field == "today_gen" or r._field == "today_sell_grid" or ...all your last fields) |> aggregateWindow(every: 1m, fn: last)
//the setting is optional
  |> set(key: "_measurement", value: "last")
  |> to(
    org: "example-org",
    bucket: "openhab_db.two_years.historical_me3000sp"
  )

mean_fields = data    |> filter(fn: (r) => r._field == "grid_voltage" or  r._field == "grid_current" or ...all your mean fields) |> aggregateWindow(every: 1m, fn: mean)
  |> set(key: "_measurement", value: "mean")
  |> to(
    org: "example-org",
    bucket: "openhab_db.two_years.historical_me3000sp"
  )
1 Like