Convert to from InfluxQL to FLUX task

InfluxDB 2
, ,
1

Hello

This is the old query I used to create a Bitcoin chart.

drop continuous query trade_bitmex_to_xbt_usd_candles_1h on bitmex;

CREATE CONTINUOUS QUERY trade_bitmex_to_xbt_usd_candles_1h 
ON bitmex RESAMPLE EVERY 10s FOR 10h 
BEGIN SELECT
  first(price) AS open,
  max(price) AS high,
  min(price) AS low,
  last(price) AS close,
  sum(foreignNotional) AS volume 
 INTO trade_bitmex_to_xbt_usd_candles_1h 
 FROM trade where symbol='XBTUSD'
 GROUP BY time(1h) fill(previous) 
END

Any help would be appreciated.

2

Here is the answer I found.
Is there a better way?

import "strings"

option task = {name: "bitmex_1h", every: 5s}

dataset = from(bucket: "trades")
	|> range(start: -4h)
	|> filter(fn: (r) =>
		(r._measurement == "bitmex" and r._field == "price"))
open = dataset
	|> map(fn: (r) =>
		({r with _field: "open", _measurement: strings.toLower(v: r.symbol + "_1h")}))
	|> aggregateWindow(
		every: 1h,
		fn: first,
		column: "_value",
		createEmpty: false,
	)
high = dataset
	|> map(fn: (r) =>
		({r with _field: "high", _measurement: strings.toLower(v: r.symbol + "_1h")}))
	|> aggregateWindow(
		every: 1h,
		fn: max,
		column: "_value",
		createEmpty: false,
	)
low = dataset
	|> map(fn: (r) =>
		({r with _field: "low", _measurement: strings.toLower(v: r.symbol + "_1h")}))
	|> aggregateWindow(
		every: 1h,
		fn: min,
		column: "_value",
		createEmpty: false,
	)
close = dataset
	|> map(fn: (r) =>
		({r with _field: "close", _measurement: strings.toLower(v: r.symbol + "_1h")}))
	|> aggregateWindow(
		every: 1h,
		fn: last,
		column: "_value",
		createEmpty: false,
	)

open
	|> to(bucket: "candles", org: "bats2")
high
	|> to(bucket: "candles", org: "bats2")
low
	|> to(bucket: "candles", org: "bats2")
close
	|> to(bucket: "candles", org: "bats2")
3

Hello @Seungwoo,
nice job! That looks pretty good. I would consider flipping the map and aggregateWindow functions though to take advantage of pushdown patterns.

read about them here:

1 Like
4

@Anaisdg Thank you very much for the good tips.

Just flipping the map and aggregateWindow functions, Iā€™m having an amazing experience with CPU utilization dropping from 36-40% to 9-10%.

image
image2968Ɨ1116 135 KB

1 Like