I have problem with my query. The query work, results are as expected, but it work very slow, 5-6 seconds for query with range of only one day (example query below). And if someone use range for couple of months or whole year (or sometimes more than year) i hit the query timeout (global Influx query limit is 90sec)
from(bucket: \"server_test\")
|> range(start: time(v: 2024-01-01T00:00:00Z), stop: time(v: 2024-01-07T23:59:00Z))
|> filter(fn: (r) => r._measurement == \"8\")
|> filter(fn: (r) => r.Event == \"click\")
|> map(fn: (r) => ({ r with test_1: if exists r.test_1 then r.test_1 else \"\" }))
|> map(fn: (r) => ({ r with test_2: if exists r.test_2 then r.test_2 else \"\" }))
|> map(fn: (r) => ({ r with test_3: if exists r.test_3 then r.test_3 else \"\" }))
|> group(columns: [\"hour\", \"day\", \"month\", \"year\", \"test_1\", \"test_2\", \"test_3\", \"Event\"], mode: \"by\")
|> sum(column: \"_value\")
|> pivot(rowKey: [\"hour\", \"day\", \"month\", \"year\", \"test_1\", \"test_2\", \"test_3\", ], columnKey: [\"Event\"], valueColumn: \"_value\")
|> drop(columns: [\"_start\", \"_stop\"])
All mentioned columns in above query are Tags in influx database. This is example of only 3 map functions per query, sometimes i need to use more. I know that map function slowing my query but i don’t know hot to do it without it.
Why i use map?
Some records in database has tags test_1, test_2 and test_3, but some records does not. They are dynamic params in my system and i can not insert in database with empty value (if tag is not present), that is why some records does have, and some records does not nave that tags (tag name and tag value).
If I just remove map i can’t group and pivot my results, because i have an error that said that column (tag) does not exist and cannot group or pivot with non existing column
I also have a lot of records in database per day, because I am working on some realtime event tracking.
Can i somehow optimize my query to speed up?
Thanks