Hey everyone!
I created a portable backup of collectd database and then deleted some data from there by using DELETE command to free some space. After some time I decided to restore the data I deleted so I restore data to collectd_bak database and from there with the following select:
SELECT * INTO collectd.autogen.cpu FROM collectd_bak.autogen.cpu WHERE time >= 26w
measurement by measurement I moved data from collectd_bak to collectd. Have to say that I’ve got two retention policies in both databases (autogen and aggregated). So for autogen I used WHERE clause with time and for aggregated retention policy I didn’t.
Everythig seemed going well but then, when I wanted to search some stuff with Grafana it shows me only data for Last 2days. When I try to search 7d and more it says no data. Also restored database collectd_bak has size around 5G and collectd after I moved databa has 13G. I think that Influx somehow changed the data with the SELECT or so…
Grafana query:
SELECT mean("value") FROM "snmp_value" WHERE ("type_instance" = 'load_5min' AND "host" = 'host1') AND $timeFilter GROUP BY time($__interval)
If anyone ran into the same problem please help ))
Thank you in advance for any suggestions!!!
So I figured out where the problem is.
I think that tags of measurements were overwritten while restoring the data to existing database and retention policies.
> select * from cpu_value where time > '2020-08-03T23:58:58Z' AND time < '2020-08-03T23:59:59Z'
name: cpu_value
time host host_1 instance instance_1 type type_1 type_instance type_instance_1 value
---- ---- ------ -------- ---------- ---- ------ ------------- --------------- -----
1596499140248239662 localhost 0 cpu user 11133579
1596499140248245535 localhost 1 cpu user 11110183
The right format of the measurement is
> select * from cpu_value where time > '2020-08-06T23:58:58Z' AND time < '2020-08-07T23:59:59Z' limit 1
name: cpu_value
time host instance type type_instance value
---- ---- -------- ---- ------------- -----
1596758339604275646 localhost 0 cpu user 1798908
The tags with names type_1, host_1 and type_instance_1 cause problem to Grafana to query the data.
Is there any option to change the names of the tags?
Thanks!
Hello @vladpov,
You can use flux to rename tag values with a map() function:map() function | Flux 0.x Documentation
Or you might have luck with the Telegraf rename processor plugin: telegraf/plugins/processors/rename at master · influxdata/telegraf · GitHub
@Anaisdg thank you for reply! Unfortunately I’m not sure if I really got how to use the map() function to rename the tags from the documentation you offer ((
My idea is to do something similar to the following:
SELECT host_1 as host, instance_1 AS instance, type_1 AS type, type_instance_1 AS type_instance INTO changed.value.autogen:MEASUREMENT FROM cpu_value WHERE time > 'sometime' AND time < 'sometime'
And after that I’d move data with the correct tag names back to the database…
Could you please use an example of what you suggest?
Thank you very much!!
Hello @vladpov,
To rename tags, more likely I’d use rename():
Something like
|> from(bucket:"mybucket")
|> range(start:-5m)
|> rename(columns: {host_1: "host", instance_1: "instance", etc...})
