You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@hertzbeat.apache.org by zh...@apache.org on 2024/09/13 02:42:36 UTC
(hertzbeat) branch doris updated: refactor(doris): optimize query for history interval with time buckets
This is an automated email from the ASF dual-hosted git repository.
zhaoqingran pushed a commit to branch doris
in repository https://gitbox.apache.org/repos/asf/hertzbeat.git
The following commit(s) were added to refs/heads/doris by this push:
new f905f5164 refactor(doris): optimize query for history interval with time buckets
f905f5164 is described below
commit f905f5164bbb5052f75cbb8d15b66a6e66d8b378
Author: zqr10159 <zq...@126.com>
AuthorDate: Fri Sep 13 10:41:41 2024 +0800
refactor(doris): optimize query for history interval with time buckets
Refactor the DorisDataStorage query to utilize time buckets and improve
the calculation of historical intervals. This update changes the SQL
query structure to include time bucketing, which enhances performance and
simplifies the query logic for historical data analysis.
---
.../store/history/doris/DorisDataStorage.java | 43 ++++++++++------------
1 file changed, 19 insertions(+), 24 deletions(-)
diff --git a/warehouse/src/main/java/org/apache/hertzbeat/warehouse/store/history/doris/DorisDataStorage.java b/warehouse/src/main/java/org/apache/hertzbeat/warehouse/store/history/doris/DorisDataStorage.java
index 99793885a..645eeb42b 100644
--- a/warehouse/src/main/java/org/apache/hertzbeat/warehouse/store/history/doris/DorisDataStorage.java
+++ b/warehouse/src/main/java/org/apache/hertzbeat/warehouse/store/history/doris/DorisDataStorage.java
@@ -91,33 +91,28 @@ public class DorisDataStorage extends AbstractHistoryDataStorage {
private static final String QUERY_INSTANCE_SQL = "SELECT DISTINCT label FROM %s WHERE ts >= now() - interval 1 WEEK and metrics = '%s';";
private static final String QUERY_HISTORY_INTERVAL_WITH_INSTANCE_SQL = """
- WITH data AS (
+ SELECT
+ CAST(UNIX_TIMESTAMP(time_bucket) * 1000 AS BIGINT) AS time_bucket,
+ first_value(his_value, 1) OVER (PARTITION BY time_bucket ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS origin,
+ avg(his_value) AS avg,
+ min(his_value) AS min,
+ max(his_value) AS max
+ FROM
+ (
SELECT
- CAST(UNIX_TIMESTAMP(ts) * 1000 as bigint) as ts,
- CAST(v['%s'] AS DOUBLE) AS value
+ ts,
+ CAST(v['%s'] AS DOUBLE) AS his_value,
+ DATE_TRUNC (ts,'HOUR') - INTERVAL (HOUR(ts)%4) HOUR AS time_bucket
FROM
%s
WHERE
- label = '%s'
- AND ts >= DATE_SUB(NOW(), INTERVAL 4 HOUR)
- AND v['%s'] IS NOT NULL
- ),
- first_value_cte AS (
- SELECT
- ts,
- value,
- FIRST_VALUE(value) OVER (ORDER BY ts ASC) AS first
- FROM
- data
- )
- SELECT
- MIN(ts) AS first_ts,
- MIN(first) AS first_value,
- AVG(value) AS avg,
- MIN(value) AS min,
- MAX(value) AS max
- FROM
- first_value_cte;
+ ts >= now() - INTERVAL 1 MONTH and label = '%S'
+ ) AS subquery
+ GROUP BY
+ time_bucket,
+ origin
+ ORDER BY
+ time_bucket;
""";
@@ -261,7 +256,7 @@ public class DorisDataStorage extends AbstractHistoryDataStorage {
}
Map<String, List<Value>> instanceValuesMap = new HashMap<>(instances.size());
for (String instanceValue : instances) {
- String selectSql = String.format(QUERY_HISTORY_INTERVAL_WITH_INSTANCE_SQL, metric, table, instanceValue, metric);
+ String selectSql = String.format(QUERY_HISTORY_INTERVAL_WITH_INSTANCE_SQL, metric, table, instanceValue);
List<Value> values = instanceValuesMap.computeIfAbsent(instanceValue, k -> new LinkedList<>());
try (Connection connection = hikariDataSource.getConnection();
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@hertzbeat.apache.org
For additional commands, e-mail: notifications-help@hertzbeat.apache.org