Hello
I need some help!!
I’m trying to convert prometheus queries(PromQL) into SingleStore queries to show data from NODE_EXPORTER in graphs on GRAFANA.
Cause I’ve got data from NODE_EXPORTER by python packages without PROMETHEUS.
So I’m figuring out how to write SingleStore queries comparing with PromQL.
PromQL are different from SingleStore queries specially using RATE function.
For example, When I tried to write queries about network traffic(The time series graph as the amount of network receiving) converting from PromQL,
I can’t figure out how to write rate function in SingleStore queries. Actually It’s what I’m stuck at
The information of rate function from prometheus docs page says (Query functions | Prometheus)
- rate(v range-vector) calculates the per-second average rate of increase of the time series in the range vector.
Breaks in monotonicity (such as counter resets due to target restarts) are automatically adjusted for.
Also, the calculation extrapolates to the ends of the time range, allowing for missed scrapes or imperfect alignment of scrape cycles with the range’s time period.
With the information when calculating the value of rate fucntion as a formula in SQL,
If there’s a time range as t1 from t2 and values for each one It goes like (Sample(t2) - Sample(t1)) / (t2 - t1).
It could be done with lag function of SingleStore to calculate the range difference but I don’t know how to deal with extrapolation of rate function
Let me show you what I’ve been doing comparing PromQL with SingleStore queries
- Network traffic(receiving) from NODE_EXPORTER
prometheus :
sum(rate(node_network_receive_bytes_total{instance=“$node”,job=“$job”}[$__rate_interval])*8)
– The original query came without sum with each network showing but I sum all of it to show as one
-configuration
- scrape_interval: 5s
- evaluation_interval: 5s
- The $__rate_interval is 15s
I converted this query into like
SingleStore :
select $__timeGroupAlias(ts,15s), avg(Value) / tst *8
from (select ts, sum(Value) Value, sum(tst) tst from (
select ts, Value - lag(Value) over (partition by Labels::$device order by ts) Value,
timestampdiff(second, lag(ts) over (partition by Labels::$device order by ts), ts)/2 tst from nd_expor_py
where Name=‘node_network_receive_bytes_total’ and Hostname=‘ap-lab-lf1_10.0.0.235’) group by 1)
where
$__timeFilter(ts)
group by 1
order by $__timeGroup(ts,15s)
-configuration
I’ve got data by 5s by python, so the interval of elpased time is 5s
The table for the queries :
singlestore> desc nd_expor_py;
±---------±-------------±-----±-----±---------------------±------+
| Field | Type | Null | Key | Default | Extra |
±---------±-------------±-----±-----±---------------------±------+
| Hostname | varchar(100) | YES | | NULL | |
| Name | varchar(900) | YES | | NULL | |
| Labels | JSON | YES | | NULL | |
| Value | double | YES | | NULL | |
| TS | datetime(6) | YES | | NULL | |
| AF_TS | datetime(6) | YES | | CURRENT_TIMESTAMP(6) | |
±---------±-------------±-----±-----±---------------------±------+
singlestore> select * from nd_expor_py limit 10;
±----------------------±---------------------------------------±-----------------------------±------------------±---------------------------±---------------------------+
| Hostname | Name | Labels | Value | TS | AF_TS |
±----------------------±---------------------------------------±-----------------------------±------------------±---------------------------±---------------------------+
| ap-lab-ag1_10.0.0.144 | node_nf_conntrack_stat_invalid | {} | 149 | 2022-11-09 13:49:39.868116 | 2022-11-09 14:15:10.485340 |
| ap-lab-lf1_10.0.0.235 | node_time_seconds | {} | 1667969380.597706 | 2022-11-09 13:49:40.604455 | 2022-11-09 14:15:10.485340 |
| ap-lab-lf3_10.0.0.23 | node_memory_Hugetlb_bytes | {} | 0 | 2022-11-09 13:49:41.243531 | 2022-11-09 14:15:10.485340 |
| ap-lab-ag1_10.0.0.144 | node_netstat_Udp_InErrors | {} | 0 | 2022-11-09 13:49:45.094164 | 2022-11-09 14:15:10.485340 |
| ap-lab-ag1_10.0.0.144 | node_network_carrier | {“device”:“veth208d15a”} | 1 | 2022-11-09 13:49:45.094164 | 2022-11-09 14:15:10.485340 |
| ap-lab-ag1_10.0.0.144 | node_scrape_collector_success | {“collector”:“entropy”} | 1 | 2022-11-09 13:49:45.094164 | 2022-11-09 14:15:10.485340 |
| ap-lab-lf4_10.0.0.89 | node_scrape_collector_duration_seconds | {“collector”:“timex”} | 0.000089108 | 2022-11-09 13:49:49.584942 | 2022-11-09 14:15:10.485340 |
| ap-lab-lf4_10.0.0.89 | node_scrape_collector_success | {“collector”:“filesystem”} | 1 | 2022-11-09 13:49:49.584942 | 2022-11-09 14:15:10.485340 |
| ap-lab-ag1_10.0.0.144 | node_network_net_dev_group | {“device”:“br-6130f2e6f1d1”} | 0 | 2022-11-09 13:49:50.308588 | 2022-11-09 14:15:10.485340 |
| ap-lab-ag1_10.0.0.144 | node_network_receive_bytes_total | {“device”:“veth139670d”} | 11309972 | 2022-11-09 13:49:50.308588 | 2022-11-09 14:15:10.485340 |
±----------------------±---------------------------------------±-----------------------------±------------------±---------------------------±---------------------------+
It has Name, Labels, Value from NODE_EXPORTER and Labels are formatted as JSON
Converted queries into S2 looks a little different from the PromQL as the valleys between vertexes are much sharper and the scale of y-axis is also different even though the value from NODE_EXPORTER is similar.
I guess the reason is that extrapolation wasn’t applied to S2 queries.
So… What I want to ask is how I could apply the extrapolation from PromQL’s rate function in S2 queries.
There’s no function like PromQL’s rate funtion in SingleStore to fill the gap between elapsed time. That’s is to say gapfilling function. In my opinion, S2 needs gapfilling function.
I’m on self hosted service with version 7.8.17