DQL¶

DQL (Debug Query Language) is the core query language of the Guance platform, designed for efficient querying and analysis of time series data, log data, event data, and more. DQL combines the semantic expression of SQL with the syntactic structure of PromQL, aiming to provide a flexible and powerful query tool.

This document will help you quickly understand the basic syntax and design philosophy of DQL, and demonstrate how to write DQL queries through examples.

Basic Query Structure¶

The basic query structure of DQL is as follows:

namespace[index]::datasource[:select-clause] [{where-clause}] [time-expr] [group-by-clause] [having-clause] [order-by-clause] [limit-clause] [sorder-by-clause] [slimit-clause] [soffset-clause]

The index can be omitted; when omitted, the default index is used by default.

Execution Order¶

The execution order of DQL queries is very important; it determines the semantics and performance of the query.

1. Data Filtering: Filter data based on namespace::datasource, where-clause, and time-expr

   • Determine the data source
   • Apply WHERE conditions to filter raw data rows
   • Apply time range filtering
   • Filter data early in this stage to improve subsequent processing efficiency

2. Time Aggregation: If time-expr contains a rollup, execute the rollup logic first

   • The Rollup function preprocesses data on the time dimension
   • For Counter type metrics, the rate or increment is usually calculated instead of using the raw value directly
   • For Gauge type metrics, aggregation functions such as last, avg, etc. may be used

3. Group Aggregation: Execute group-by-clause to group data, and within each group, execute the aggregation functions in select-clause

   • Group data according to the expressions in the BY clause
   • Calculate aggregation functions (sum, count, avg, max, min, etc.) within each group
   • If there is also a time window, a two-dimensional data structure is formed

4. Group Filtering: Execute having-clause to filter aggregated groups

   • The HAVING clause acts on the aggregated results
   • It can use the results of aggregation functions for filtering
   • This is the key difference from the WHERE clause

5. Non-Aggregation Functions: Execute the non-aggregation functions in select-clause

   • Process expressions and functions that do not require aggregation
   • Perform further calculations on aggregated results

6. Within-Group Sorting: Execute order-by-clause and limit-clause to sort and paginate data within each group

   • ORDER BY is executed independently within each group
   • LIMIT restricts the number of data rows returned per group

7. Between-Group Sorting: Execute sorder-by-clause, slimit-clause, and soffset-clause to sort and paginate groups

   • SORDER BY sorts the groups themselves
   • Requires dimensionality reduction of the group results (using functions such as max, avg, last, etc.)
   • SLIMIT restricts the number of groups returned

Complete Example¶

Let's understand the DQL structure through a complete example:

M::cpu:(avg(usage) as avg_usage, max(usage) as max_usage) {host =~ 'web-.*', usage > 50} [1h::5m] BY host, env HAVING avg_usage > 60 ORDER BY time DESC LIMIT 100 SORDER BY avg_usage DESC SLIMIT 10

The meaning of this query is:

• Namespace: M (metric data)
• Index: production
• Datasource: cpu
• Select fields: Average and maximum of the usage field
• Time range: Past 1 hour, aggregated in 5-minute buckets
• Filter conditions: Hostname starting with web- and CPU usage greater than 50%
• Grouping: By hostname and environment
• Group filtering: Average CPU usage greater than 60%
• Sorting: By time descending, up to 100 rows per group
• Between-group sorting: By average usage descending, up to 10 groups

Namespace¶

Namespaces are used to distinguish different types of data. Each data type has its own query method and storage strategy. DQL supports querying multiple business data types:

Index¶

Indexes are an important optimization mechanism for DQL queries, which can be understood as tables or partitions in a traditional database. Within a single namespace, the system may split data based on factors such as data source, data volume, and access patterns to improve query performance and management efficiency.

Role of Indexes¶

• Performance optimization: Indexes distribute data storage, reducing the amount of data scanned per query
• Data isolation: Data from different businesses, environments, or time ranges can be stored in different indexes
• Permission management: Different indexes can be assigned different access permissions
• Lifecycle management: Different indexes can have different data retention policies

Index Naming Rules¶

• Index names can be explicitly declared; when not explicitly declared, default is used
• When explicitly declaring an index name, wildcards or regular expressions are not supported for matching
• Index names typically reflect the business attribute of the data, e.g., production, staging, web-logs, api-logs

Basic Syntax¶

// Use the default index (automatically uses `default` when not specified)
M::cpu           // Equivalent to M("default")::cpu
L::nginx         // Equivalent to L("default")::nginx

// Specify a single index
M("production")::cpu     // Query CPU metrics in the production index
L("web-logs")::nginx     // Query Nginx logs in the web-logs index

// Multi-index query (query data from multiple indexes simultaneously)
M("production", "staging")::cpu     // Query CPU metrics for production and staging environments
L("web-logs", "api-logs")::nginx     // Query Web and API logs

Indexes and Performance¶

Using indexes wisely can significantly improve query performance:

• Exact index: When you know exactly which index the data is in, specify that index directly
• Multi-index query: When you need to query across multiple indexes, use the multi-index syntax instead of wildcards
• Avoid full index scan: Try to reduce the data scan range by combining indexes and WHERE conditions

Legacy Syntax (Not Recommended)¶

For historical reasons, DQL also supports specifying an index in the WHERE clause, but this is not recommended:

// Legacy syntax, not recommended
L::nginx { index = "web-logs" }
L::nginx { index IN ["web-logs", "api-logs"] }

Application Examples¶

// Query CPU usage in the production environment
M::cpu:(avg(usage)) [1h] BY host

// Compare production and staging environments
M("production", "staging")::cpu:(avg(usage)) [1h] BY index, host

// Analyze web server logs
L("web-logs")::nginx:(count(*)) {status >= 400} [1h] BY status

// Compare error rates between Web and API servers
L("web-logs", "api-logs")::*:(count(*)) {status >= 500} [1h] BY index

Datasource¶

The datasource specifies the specific data source for the query. It can be a dataset name, a wildcard pattern, a regular expression, or a subquery.

Basic Datasources¶

The definition of a datasource varies by namespace:

Datasource Syntax¶

Specify a Datasource Name¶

M::cpu:(usage)                    // Query CPU metrics
L::nginx:(count(*))                // Query Nginx logs
T::user-service:(traces)           // Query user service traces

Wildcard Matching¶

M::*:(usage)                      // Query all metrics

Regular Expression Matching¶

M::re('cpu.*'):(usage)            // Query metrics starting with cpu
L::re('web.*'):(count(*))          // Query logs starting with web
T::re('.*-service'):(traces)      // Query services ending with -service

Subquery Datasource¶

Subqueries are an important feature in DQL for implementing complex analysis. They allow the result of one query to be used as the data source for another query. This nested query mechanism supports multi-level analysis requirements.

Execution Mechanism¶

Subquery execution follows these principles:

1. Serial execution: The inner subquery executes first, and its result is used as the data source for the outer query
2. Result encapsulation: The subquery result is encapsulated into a temporary table structure for use by the outer query
3. Mixed namespace: Subqueries support mixed queries across different namespaces, enabling cross-data-type analysis
4. Performance considerations: Subqueries increase computational complexity, so query logic should be designed wisely

Basic Syntax¶

namespace::(subquery):(projections)

Execution Process¶

Take a typical subquery as an example:

L::(L::*:(count(*)) {level = 'error'} BY app_id):(count_distinct(app_id))

The execution process is:

1. Inner subquery: L::*:(count(*)) {level = 'error'} BY app_id

   • Scan all log data
   • Filter out error-level logs
   • Group by app_id and count the number of errors
   • Generate a temporary table: app_id | count(*)

2. Outer query: L::(...):(count_distinct(app_id))

   • Use the subquery result as the data source
   • Count how many distinct app_id values exist
   • Final result: number of applications with errors

Application Examples¶

// Count the number of applications with errors
L::(L::*:(count(*)) {level = 'error'} BY app_id):(count_distinct(app_id))

// Analyze servers with high CPU usage
M::(M::cpu:(avg(usage)) [1h] BY host {avg(usage) > 80}):(count(host))

// First find service endpoints with error rate > 1%, then count the number of affected services
M::(M::http_requests:(sum(request_count), sum(error_count)) [1h] BY service, endpoint
   {sum(error_count) / sum(request_count) > 0.01}
):(count(service))

Select Clause¶

The Select clause is used to specify the fields or expressions to be returned by the query. It is one of the most basic and important parts of DQL queries.

Field Selection¶

Basic Syntax¶

// Select a single field
M::cpu:(usage)

// Select multiple fields
M::cpu:(usage, system, user)

// Select all fields
M::cpu:(*)

Field Name Rules¶

Field names can be written in the following forms:

1. Direct name: Suitable for regular identifiers

   • ✅ message
   • ✅ host_name
   • ✅ response_time

2. Backtick-enclosed: Suitable for field names containing special characters or keywords

   • ✅ message
   • ✅ limit
   • ✅ host-name
   • ✅ column with spaces

3. Avoid these forms: Single and double quotes enclose strings, not field names

   • ❌ 'message'
   • ❌ "message"

JSON Field Extraction¶

When data fields contain JSON-formatted content, a subset of the JSON Path syntax can be used to extract the data of internal fields.

Basic Syntax¶

field-name@json-path
@json-path                    // Uses the message field by default

JSON Path Syntax¶

• Object property access with dot: .field_name
• Object property access with brackets: ["key"] (for keys containing spaces or special characters)
• Array index access: [index]

Application Examples¶

Assume the following JSON log data:

{
  "message": "User login attempt",
  "request": {
    "method": "POST",
    "path": "/api/login",
    "headers": {
      "user-agent": "Mozilla/5.0",
      "content-type": "application/json"
    },
    "body": {
      "username": "john.doe",
      "password": "***",
      "permissions": ["read", "write", "admin"]
    }
  },
  "response": {
    "status": 200,
    "time": 156,
    "data": [
      {"id": 1, "name": "user1"},
      {"id": 2, "name": "user2"}
    ]
  }
}

// Extract the request method
L::auth_logs:(message@request.method)

// Extract the request path
L::auth_logs:(message@request.path)

// Extract the username
L::auth_logs:(message@request.body.username)

// Extract the response status
L::auth_logs:(message@response.status)

// Extract the User-Agent (contains a hyphen, so brackets are needed)
L::auth_logs:(message@request.headers["user-agent"])

// Extract the first element of the permissions array
L::auth_logs:(message@request.body.permissions[0])

// Extract the name of the first object in the response data
L::auth_logs:(message@response.data[0].name)

// Count the number of different request methods
L::auth_logs:(count(*)) [1h] BY message@request.method

// Analyze response time distribution
L::auth_logs:(avg(message@response.time), max(message@response.time)) [1h] BY message@request.method

// Extract multiple fields
L::auth_logs:(
    message,
    message@request.method as method,
    message@request.path as path,
    message@response.status as status,
    message@response.time as response_time
) {message@response.status >= 400} [1h]

Computed Fields¶

Expression Computation¶

Supports basic arithmetic operations:

// Unit conversion (milliseconds to seconds)
L::nginx:(response_time / 1000) as response_time_seconds

// Percentage calculation
M::memory:(used / total * 100) as usage_percentage

// Compound calculation
M::network:((bytes_in + bytes_out) / 1024 / 1024) as total_traffic_mb

Function Computation¶

Supports various aggregation and transformation functions:

// Aggregation functions
M::cpu:(max(usage), min(usage), avg(usage)) [1h] BY host

// Transformation functions
L::logs:(int(response_time) as response_time_seconds)
L::logs:(floor(response_time) as response_time_seconds)

Conditional Expressions¶

CASE WHEN is used to select different values based on conditions in the query. It is typically used together with aggregation functions for conditional counting, conditional summation, or normalizing fields by condition.

Basic Syntax¶

CASE
  WHEN condition THEN value
  [WHEN condition THEN value ...]
  [ELSE default_value]
END

You can also use the simple CASE syntax for matching a single field against multiple values:

CASE field
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ELSE default_value
END

WHEN clauses are evaluated in order; the first matching condition returns its corresponding THEN value. If none match, the ELSE value is returned. If ELSE is not explicitly written, nil is returned by default.

Application Examples¶

Conditional sum: only count traffic from 5xx requests

L::nginx_access:(
    sum(CASE WHEN status >= 500 THEN bytes ELSE 0 END) as error_bytes
) [1h] BY host

Conditional count: count the number of error requests

L::nginx_access:(
    count(CASE WHEN status >= 500 THEN 1 ELSE nil END) as error_count
) [1h] BY service

Note: count(expr) counts non-nil values. 0 is also non-nil, so count(CASE WHEN condition THEN 1 ELSE 0 END) counts all rows, not just those matching the condition. For conditional counting, it is recommended to use ELSE nil, or use sum(CASE WHEN condition THEN 1 ELSE 0 END).

Multi-branch classification: generate a severity level based on status code

L::nginx_access:(
    max(CASE
        WHEN status >= 500 THEN 3
        WHEN status >= 400 THEN 2
        WHEN status >= 300 THEN 1
        ELSE 0
    END) as status_level
) [1h] BY service

Field cleaning before judgment: count error logs ignoring case

L::app_logs:(
    sum(CASE WHEN lower(level) = "error" THEN 1 ELSE 0 END) as error_count
) [1h] BY service

Type conversion before aggregation: parse string field to numeric for summation

L::nginx_access:(
    sum(CASE WHEN status >= 500 THEN int(bytes) ELSE 0 END) as error_bytes
) [1h] BY host

Supported Scope¶

CASE WHEN is currently a limited row-level conditional expression, designed to support high-performance pushdown execution. It can be placed inside aggregation functions, for example, sum(CASE ...), count(CASE ...), max(CASE ...).

CASE supports:

• Fields
• Literals and nil
• Boolean conditions
• The following scalar functions: int, float, string, md5, lower, upper, trim, ltrim, rtrim, length, regexp_replace

Aggregation functions are not supported inside CASE. Aggregation functions should be placed outside CASE:

// Recommended: evaluate CASE row by row, then aggregate
L::nginx_access:(
    sum(CASE WHEN status >= 500 THEN bytes ELSE 0 END) as error_bytes
) [1h] BY host

// Not supported: using aggregation functions inside CASE
L::nginx_access:(
    CASE WHEN sum(bytes) > 0 THEN "has_bytes" ELSE "empty" END
) [1h] BY host

Complex scalar functions not in the supported list, such as regexp_extract, cannot be used inside CASE. If other complex processing is needed, it is recommended to split the logic using query conditions, field cleaning, or subqueries to avoid triggering large-scale detailed scans inside CASE.

Aliases¶

Assign an alias to a field or expression to make the result more readable and easier to reference later.

Basic Syntax¶

expression as alias_name

Application Examples¶

// Simple alias
M::cpu:(avg(usage) as avg_usage, max(usage) as max_usage) [1h] BY host

// Expression alias
M::memory:((used / total) * 100 as usage_percent) [1h] BY host

// Function alias
L::logs:(count(*) as error_count) {level = 'error'} [1h] BY service

// JSON extraction alias
L::api_logs:(
    message@request.method as http_method,
    message@response.status as http_status,
    message@response.time as response_time_ms
) [1h]

Usage Tips¶

In DQL, the results of aggregation functions can be referenced directly using the original field name, which reduces the need for aliases:

M::cpu:(max(usage)) [1h] BY host

// The result includes a `max(usage)` column, which can be directly referenced as `usage` in subsequent queries:
M::(M::cpu:(max(usage)) [1h] BY host):(max(usage)) { usage > 80 }

However, when multiple aggregation functions use the same field, aliases must be used to distinguish them correctly:

// Aliases are required in this case
M::cpu:(max(usage) as max_usage, min(usage) as min_usage) [1h] BY host

Time Clause¶

The time clause is a core feature of DQL, used to specify the query time range, aggregation time window, and Rollup aggregation function.

Basic Syntax¶

[start_time:end_time:interval:rollup]

Time Range¶

Absolute Timestamps¶

[1672502400000:1672588800000]     // Millisecond timestamps
[1672502400:1672588800]           // Second timestamps

Relative Time¶

Supports multiple duration units, which can be mixed:

[1h]                             // Past 1 hour to now
[1h:5m]                          // From past 1 hour to past 5 minutes
[1h30m]                          // Past 1 hour 30 minutes
[2h15m30s]                       // Past 2 hours 15 minutes 30 seconds

Duration Expression Description¶

When used in a time clause, a duration expression indicates an offset from the current time backward. When used in the Select clause, Where clause, etc., it is treated as a millisecond integer for calculation.

When used in aggregation queries, two additional duration units are supported:

O::HOST:(count(*)){ `last_update_time` > (now()-10m) } // 10m is treated as 600,000 integer for calculation
L::*:( count(*) / 1i ) [::1m] // Divide by the time window size (1m) in seconds to calculate log write QPS

Predefined Time Ranges¶

Provides commonly used time range keywords:

[TODAY]                          // Today's data
[YESTERDAY]                      // Yesterday's data
[THIS WEEK]                      // This week's data
[LAST WEEK]                      // Last week's data
[THIS MONTH]                     // This month's data
[LAST MONTH]                     // Last month's data

When using time range keywords, ensure the time zone setting of the workspace is correct. The conversion must be strictly based on the user's request time zone.

Time Window Aggregation¶

Time windows group data by specified time intervals for aggregation. The time column in the returned results represents the start time of each time window.

Single Time Window¶

The entire time range is aggregated into a single value:

M::cpu:(max(usage_total)) [1h]

Query Result:

{
  "columns": ["time", "max(usage_total)"],
  "values": [
    [1721059200000, 37.46]
  ]
}

Time Window Aggregation¶

Group and aggregate data by time intervals:

M::cpu:(max(usage_total)) [1h::10m]

Query Result:

{
  "columns": ["time", "max(usage_total)"],
  "values": [
    [1721059200000, 37.46],
    [1721058600000, 34.12],
    [1721058000000, 33.81],
    [1721057400000, 30.92],
    [1721058000000, 34.53],
    [1721057400000, 36.11]
  ]
}

Rollup Functions¶

Rollup functions are an important preprocessing step in DQL. They are executed before group aggregation to preprocess raw time series data.

Execution Timing¶

The position of Rollup in the query execution flow:

Raw data → WHERE filter → **Rollup preprocessing** → Group aggregation → HAVING filter → Final result

Execution Mechanism¶

The Rollup execution process consists of two phases:

1. Per-time-series processing: Apply the Rollup function to each individual time series independently
2. Aggregation calculation: Execute group aggregation on the Rollup-processed results

Rollup Shorthand, Rollup Function Call, and Explicit Aggregation Call¶

There are three easily confused time series function writing styles in DQL:

1. Rollup shorthand: Only the function name is written in the time clause, e.g., [rate], [1h::5m:slope].
2. Rollup function call: Algorithm parameters are passed to the Rollup function in the time clause, e.g., [1h::1m:ewma(0.3)], [1h::1m:moving_average(5)], [1h::1m:percentile(95)].
3. Explicit aggregation call: The full function call is written in the Select clause, e.g., rate(request_count), ewma(usage, 0.3), corr(cpu_usage, request_count).

The main differences between the three styles are the execution phase and parameter capabilities:

The input fields for Rollup in the time clause are determined by the Select fields, time window, and raw time series. Therefore, only additional algorithm parameters are passed in the Rollup function call, not field names. Single-input time series functions without extra parameters typically support Rollup shorthand; single-input functions that require additional algorithm parameters can use the Rollup function call; functions that require multiple input fields should use the explicit aggregation call.

Example: Counter metrics first Rollup then aggregate

Counter metrics should first calculate the rate of increase on each raw time series, then sum by business dimension:

// Recommended: first calculate rate per time series, then sum by service
M::http_requests:(sum(request_count)) [1h::5m:rate] BY service

// Not recommended: directly aggregate the raw cumulative value of Counter, the result is not QPS
M::http_requests:(sum(request_count)) [1h::5m] BY service

Example: Use Rollup function call when additional algorithm parameters are needed

ewma requires an explicit smoothing coefficient alpha. If you want to apply EWMA to each raw time series before group aggregation, you can write it in the time clause:

// Rollup function call: apply EWMA to each time series, then average by host
M::cpu:(avg(usage)) [1h::1m:ewma(0.3)] BY host

// Error: ewma requires alpha, cannot just write the function name
M::cpu:(avg(usage)) [1h::1m:ewma] BY host

// Error: Rollup parameters in the time clause only pass algorithm parameters, not field names
M::cpu:(avg(usage)) [1h::1m:ewma(usage, 0.3)] BY host

If you want to calculate EWMA during the Select aggregation phase, you can also use the explicit aggregation call:

// Explicit aggregation call: calculate EWMA on usage in the Select aggregation phase
M::cpu:(ewma(usage, 0.3)) [1h::1m] BY host

Other single-value parameterized functions also suitable for time clause Rollup include:

// Apply 5-point moving average to each time series, then average by host
M::cpu:(avg(usage)) [1h::1m:moving_average(5)] BY host

// Apply P95 to each time series, then average by service
M::response_time:(avg(duration)) [1h::5m:percentile(95)] BY service

Example: Use explicit aggregation call when multiple input fields are needed

corr requires two input fields, so it cannot be written in the time clause Rollup:

// Correct: explicitly specify two fields in the Select clause
M::service_metric:(corr(cpu_usage, request_count)) [1h::5m] BY service

// Error: time clause Rollup cannot express two input fields
M::service_metric:(avg(cpu_usage)) [1h::5m:corr] BY service

Example: Same function name, different execution phases

When a function supports both Rollup shorthand and explicit aggregation call, the two forms represent different execution phases and should not be assumed to be fully equivalent:

// Rollup shorthand: first calculate zscore on each raw time series, then group aggregate
M::cpu:(max(usage)) [1h::5m:zscore] BY host

// Explicit aggregation call: calculate zscore on usage in the Select aggregation phase
M::cpu:(zscore(usage)) [1h::5m] BY host

Application Scenarios¶

A typical use case for Rollup functions is Counter metric processing.

For Prometheus Counter type metrics, aggregating the raw values directly is meaningless because Counters are monotonically increasing. The rate of increase must be calculated per time series first, then aggregated.

*Problem Example: Assume two servers with request counters:

{
  "host": "web-server-01",
  "data": [
    {"time": "2024-07-15 08:25:00", "request_count": 150},
    {"time": "2024-07-15 08:20:00", "request_count": 140},
    {"time": "2024-07-15 08:15:00", "request_count": 130},
    {"time": "2024-07-15 08:10:00", "request_count": 120},
    {"time": "2024-07-15 08:05:00", "request_count": 110},
    {"time": "2024-07-15 08:00:00", "request_count": 100}
  ]
}

{
  "host": "web-server-02",
  "data": [
    {"time": "2024-07-15 08:25:00", "request_count": 250},
    {"time": "2024-07-15 08:20:00", "request_count": 240},
    {"time": "2024-07-15 08:15:00", "request_count": 230},
    {"time": "2024-07-15 08:10:00", "request_count": 220},
    {"time": "2024-07-15 08:05:00", "request_count": 210},
    {"time": "2024-07-15 08:00:00", "request_count": 200}
  ]
}

Problem with direct aggregation:

• web-server-01's request_count starts at 100
• web-server-02's request_count starts at 200
• Although both servers have the same request rate (10 requests per 5 minutes), their absolute values differ

Solution using Rollup:

M::http:(sum(request_count)) [rate]

Execution process:

1. Rollup phase (executed on each time series independently):

   • web-server-01: rate([100, 110, 120, 130, 140, 150]) = 2 requests/minute
   • web-server-02: rate([200, 210, 220, 230, 240, 250]) = 2 requests/minute

2. Aggregation phase:

   • sum([2, 2]) = 4 requests/minute

Function Types¶

Common Rollup functions include:

However, almost any aggregation function that returns a single value can be used, so the full list is not exhaustive here.

Default Rollup¶

If no Rollup function is explicitly specified, DQL does not perform Rollup calculation by default. PromQL's default Rollup is last, so if you are calculating Prometheus metrics, be sure to understand this difference and manually specify the Rollup function.

Application Examples¶

// Calculate the total request rate across all servers
M::http_requests:(sum(request_count)) [rate]

// Calculate the error rate
M::http_requests:(
    sum(error_count) as errors,
    sum(request_count) as requests
) [rate] BY service

Flexible Time Window Syntax¶

DQL supports multiple shorthand formats for time windows, making query writing more convenient.

Shorthand Formats¶

[1h]                             // Specify only the time range
[1h::5m]                         // Time range + aggregation interval
[1h:5m]                          // Start time + end time
[1h:5m:1m]                       // Start + end + interval
[1h:5m:1m:avg]                   // Full format
[::5m]                           // Specify only the aggregation interval
[:::sum]                         // Specify only the rollup function
[sum]                            // Specify only the rollup function (simplest form)

Time Shift (SHIFT)¶

SHIFT shifts the entire query's time window forward in time, reading data from a specified duration ago. The time column in the results still displays the current window:

<select-query> SHIFT <duration>

SHIFT immediately follows the time window and precedes clauses such as BY, HAVING, ORDER BY:

L::logs:(service, count(*) AS requests)[1d:1h] SHIFT 7d BY service

Filtering, grouping, aggregation, sorting, and pagination are all based on the shifted data; the structure of the result is the same as a query without SHIFT, only the time column falls within the current window. The duration must be a positive fixed duration, e.g., 1h, 7d.

Nested SELECT statements can each declare their own query-level SHIFT; the offsets are additive per level. Query-level SHIFT can be combined with expression-level SHIFT (see DQL Function Reference SHIFT): the query-level offset determines the base window for the entire query first, and the expression-level offset then reads earlier aggregated values relative to that base window.

WHERE Clause¶

The WHERE clause is used to filter data rows, retaining only those that meet the conditions for subsequent processing.

Basic Syntax¶

{condition1, condition2, condition3}

Multiple conditions can be connected using commas, AND, OR, &&, or ||.

Comparison Operators¶

Pattern Matching Operators¶

Set Operators¶

Logical Operators¶

Note: In addition to logical OR, the OR operator also provides a null-fallback semantics — when the left-hand expression returns NULL, the result of the right-hand expression is returned directly. This can be used to implement a priority fallback logic, e.g., status OR backup_status.

Application Examples¶

Basic Filtering¶

// Single condition
M::cpu:(usage) {host = 'web-01'} [1h]

// Multiple AND conditions
M::cpu:(usage) {host = 'web-01', usage > 80} [1h]

// Using the AND keyword
M::cpu:(usage) {host = 'web-01' AND usage > 80} [1h]

// Mixing logical operators
M::cpu:(usage) {(host = 'web-01' OR host = 'web-02') AND usage > 80} [1h]

Regular Expression Matching¶

// Match error logs
L::logs:(message) {message =~ 'ERROR.*\\d{4}'} [1h]

// Match logs with a specific format
L::logs:(message) {message =~ '\\[(ERROR|WARN)\\].*'} [1h]

// Exclude debug messages
L::logs:(message) {message !~ 'DEBUG.*'} [1h]

// Hostname pattern matching
M::cpu:(usage) {host =~ 'web-.*\\.prod\\.com'} [1h]

Set Operations¶

// Status code filtering
L::nginx:(count(*)) {status IN [200, 201, 202, 204]} [1h]

// Exclude specific status codes
L::nginx:(count(*)) {status NOT IN [404, 500, 502]} [1h]

// Log level filtering
L::app_logs:(count(*)) {level IN ['ERROR', 'WARN', 'CRITICAL']} [1h]

Array Field Handling¶

When a field type is an array, DQL supports multiple array matching operations.

Assume a field tags = ['web', 'prod', 'api']:

Recommended Syntax: using IN and NOT IN¶

// Check if the array contains a value
{tags IN ['web']}           // true, because tags contains 'web'
{tags IN ['mobile']}        // false, because tags does not contain 'mobile'

// Check if the array does not contain a value
{tags NOT IN ['mobile']}    // true, because tags does not contain 'mobile'
{tags NOT IN ['web']}       // false, because tags contains 'web'

// Check if the array contains all specified values
{tags IN ['web', 'api']}           // true, contains 'web' and 'api'
{tags IN ['web', 'api', 'mobile']} // false, does not contain 'mobile'

Legacy Syntax (Not Recommended)¶

The following syntax is retained for historical compatibility and is not recommended for new queries. The semantics of these operators on array fields differ from those on regular fields, which can be confusing.

// Legacy syntax: single-value containment check (overloaded semantics of equals operator)
{tags = 'web'}           // true, because tags contains 'web'
{tags = 'mobile'}        // false, because tags does not contain 'mobile'

// Legacy syntax: single-value not-contains check (overloaded semantics of not-equals operator)
{tags != 'mobile'}       // true, because tags does not contain 'mobile'
{tags != 'web'}          // false, because tags contains 'web'

Function Filtering¶

Any function that returns a boolean value can be used as a filter condition.

// String matching
L::logs:(message) { match(message, 'error') }
L::logs:(message) { wildcard(message, 'error*') }

// Query requests with abnormal response times
L::access_logs:(*) {
    response_time > 1000 AND
    match(message, 'timeout')
}

// Query hosts with abnormal memory usage
M::memory:(usage) {
    (usage > 90 OR usage < 10) AND
    host =~ 'prod-.*' AND
    tags IN ['critical', 'important']
}

WHERE Subquery¶

The WHERE subquery is a powerful feature in DQL for dynamic filtering. It allows the result of one query to be used as a filter condition for another query. This mechanism supports dynamic filtering based on data analysis results.

Query Characteristics¶

• Dynamic filtering: Filter conditions are not fixed values but are dynamically computed through queries
• Mixed namespace: Supports cross-namespace queries, enabling correlation analysis of different data types
• Serial execution: The subquery executes first, and its result is used for filtering in the main query
• Array result: The subquery result is encapsulated as an array, so only IN and NOT IN operators are supported

Execution Flow¶

Take a typical WHERE subquery as an example:

M::cpu:(avg(usage)) { host IN (O::HOST:(hostname) {provider = 'cloud-a'}) } [1h] BY host

Execution process:

1. Subquery execution: O::HOST:(hostname) {provider = 'cloud-a'}

   • Query all infrastructure objects
   • Filter hosts with provider = 'cloud-a'
   • Return a list of hostnames: ['host-01', 'host-02', 'host-03']

2. Main query execution: M::cpu:(avg(usage)) [1h] BY host {host IN [...]}

   • Query CPU usage data
   • Only include hosts returned by the subquery
   • Calculate the average usage per host

Application Examples¶

// Monitor servers from a specific cloud provider
M::cpu:(avg(usage)) { host IN (O::HOST:(hostname) {provider = 'cloud-a'}) } [1h] BY host


// Compare performance across different cloud providers
M::memory:(avg(used / total * 100)) { host IN (O::HOST:(hostname) {provider IN ['cloud-a', 'cloud-b']}) } [1h] BY host


// Analyze logs for a specific business
L::app_logs:(count(*)) { service IN (T::services:(service_name) {business_unit = 'ecommerce'}) } [1h] BY level


// Monitor application performance for critical business services
M::response_time:(avg(response_time)) { service IN (T::services:(service_name) {criticality = 'high'}) } [1h] BY service

Group By Clause¶

Grouping is a core feature of data analysis, used to group and aggregate data by specified dimensions.

Basic Syntax¶

BY expression1, expression2, ...

Grouping Types¶

Field Grouping¶

// Single field grouping
M::cpu:(avg(usage)) [1h] BY host

// Multi-field grouping
M::cpu:(avg(usage)) [1h] BY host, env

// Nested grouping
M::cpu:(avg(usage)) [1h] BY datacenter, rack, host

Expression Grouping¶

// Compound mathematical expression
M::memory:(avg(used)) [1h] BY ((used / total) * 100) as usage_percent

// Multi-field mathematical operation
M::performance:(avg(response_time)) [1h] BY (response_time / 1000) as response_seconds

Function Grouping¶

// Drain clustering algorithm
L::logs:(count(*)) BY drain(message, 0.7) as sample

// Regex extraction grouping
L::logs:(count(*)) [1h] BY regexp_extract(message, 'error_code: (\\d+)', 1)

Group Result Handling¶

When a query includes both grouping and a time window, a two-dimensional data structure is produced. Group By can be used together with a time window, and the query result will be a two-dimensional array. The first dimension of this array consists of multiple groups differentiated by the group keys, and the second dimension is the multi-time-interval data within a single group.

Two-dimensional data structure example:

Query: M::cpu:(max(usage_total)) [1h::10m] by host

Query Result Structure:

{
  "series": [
    {
      "columns": ["time", "max(usage_total)"],
      "name": "cpu",
      "tags": {"host": "web-server-01"},
      "values": [
        [1721059200000, 78.5],
        [1721058600000, 82.3],
        [1721058000000, 75.8],
        [1721057400000, 88.2]
      ]
    },
    {
      "columns": ["time", "max(usage_total)"],
      "name": "cpu",
      "tags": {"host": "web-server-02"},
      "values": [
        [1721059200000, 45.2],
        [1721058600000, 52.8],
        [1721058000000, 48.5],
        [1721057400000, 61.3]
      ]
    },
    {
      "columns": ["time", "max(usage_total)"],
      "name": "cpu",
      "tags": {"host": "web-server-03"},
      "values": [
        [1721059200000, 92.1],
        [1721058600000, 95.7],
        [1721058000000, 89.4],
        [1721057400000, 97.6]
      ]
    }
  ]
}

To further process this two-dimensional array:

1. To filter the results of this two-dimensional array, use the HAVING clause
2. To sort or paginate data within a single group of the two-dimensional array, use the ORDER BY, LIMIT, OFFSET statements
3. To sort or paginate the groups themselves of the two-dimensional array, use the SORDER BY, SLIMIT, SOFFSET statements

For detailed information on the two-dimensional data structure and sorting/pagination features, refer to Sorting and Pagination.

HAVING Clause¶

The HAVING clause is used to filter the results after group aggregation, similar to the WHERE clause, but it acts on aggregated data.

Basic Syntax¶

HAVING condition

Difference from WHERE¶

WHERE and HAVING are both clauses used to filter data, but they operate at different stages of query execution and handle different types of filter conditions.

Execution Timing Difference¶

Raw data → WHERE filter → Group aggregation → HAVING filter → Final result

1. WHERE clause:

   • Executes before group aggregation
   • Acts on raw data rows
   • Filters out rows that do not meet the conditions, reducing the amount of data processed later

2. HAVING clause:

   • Executes after group aggregation
   • Acts on the aggregated results
   • Filters based on the results of aggregation functions

Application Scenarios¶

The HAVING clause is suitable for filtering aggregated results:

// Filter based on aggregation function values
M::cpu:(avg(usage) as avg_usage) [1h] BY host HAVING avg_usage > 80

// Filter based on multiple aggregation conditions
M::http_requests:(
    sum(request_count) as total,
    sum(error_count) as errors
) [1h] BY service, endpoint
HAVING errors / total > 0.01 AND total > 1000

// Filter based on group statistics
L::logs:(count(*) as count) [1h] BY service HAVING count > 100

// Filter based on compound aggregation conditions
M::response_time:(
    avg(response_time) as avg_time,
    max(response_time) as max_time,
    min(response_time) as min_time
) [1h] BY endpoint
HAVING avg_time > 1000 AND max_time > 5000 AND (max_time - min_time) > 2000

Sorting and Pagination¶

Sorting and pagination in DQL is a very important and unique feature. It is designed with a dual sorting mechanism specifically for time series data: within-group sorting and between-group sorting. This design enables DQL to efficiently handle complex multi-dimensional time series data analysis needs.

Understanding DQL's Data Structure¶

Before diving into sorting and pagination, let's first understand the two-dimensional data structure of DQL query results. When a query includes both grouping (BY) and a time window, a two-dimensional array is produced:

• First dimension (group dimension): multiple groups differentiated by the group keys
• Second dimension (time dimension): data aggregated by time window within each group

For a detailed description of the two-dimensional data structure and JSON format examples, refer to Group Result Handling. This two-dimensional structure is the foundation of DQL's sorting and pagination functionality. Understanding this structure is crucial for mastering DQL's sorting mechanism.

Within-Group Sorting and Pagination (ORDER BY, LIMIT, OFFSET)¶

Within-group sorting and pagination operate on the second dimension of the two-dimensional structure, i.e., the data within each group. This sorting is executed independently within each group and does not affect data in other groups.

Basic Syntax¶

ORDER BY expression [ASC|DESC]
LIMIT row_count
OFFSET row_offset

Execution Mechanism¶

The execution process of within-group sorting:

1. Group processing: Execute the sorting operation independently for each group
2. Sorting basis: Can use the time field, aggregation function results, or computed expressions
3. Pagination limit: LIMIT restricts the number of data rows returned per group
4. Offset handling: OFFSET skips the first N rows of data within each group

Application Examples¶

Basic Time Sorting¶

// Sort by time descending, showing the latest data for each host
M::cpu:(max(usage_total)) [1h::10m] BY host ORDER BY time DESC

// Sort by time ascending, showing historical trends
M::cpu:(max(usage_total)) [1h::10m] BY host ORDER BY time ASC

Execution Result (ORDER BY time DESC):

{
  "series": [
    {
      "columns": ["time", "max(usage_total)"],
      "name": "cpu",
      "tags": {"host": "web-server-01"},
      "values": [
        [1721059200000, 78.5],  // 12:00:00
        [1721058600000, 82.3],  // 11:50:00
        [1721058000000, 75.8],  // 11:40:00
        [1721057400000, 88.2],  // 11:30:00
        [1721056800000, 72.1],  // 11:20:00
        [1721056200000, 69.4]   // 11:10:00
      ]
    },
    {
      "columns": ["time", "max(usage_total)"],
      "name": "cpu",
      "tags": {"host": "web-server-02"},
      "values": [
        [1721059200000, 45.2],  // 12:00:00
        [1721058600000, 52.8],  // 11:50:00
        [1721058000000, 48.5],  // 11:40:00
        [1721057400000, 61.3],  // 11:30:00
        [1721056800000, 55.7],  // 11:20:00
        [1721056200000, 58.9]   // 11:10:00
      ]
    }
  ]
}

Value-Based Sorting¶

// Sort by CPU usage descending, find peak periods for each host
M::cpu:(max(usage_total) as max_usage_total) [1h::10m] BY host ORDER BY max_usage_total DESC

// Sort by response time ascending, find the best performance periods
M::response_time:(avg(response_time) as avg_response_time) [1h::5m] BY endpoint ORDER BY avg_response_time ASC

Within-Group Pagination¶

// Show only the latest 3 data points per host
M::cpu:(max(usage_total)) [1h::10m] BY host ORDER BY time DESC LIMIT 3

// Skip the latest 2 data points, show the next 3
M::cpu:(max(usage_total)) [1h::10m] BY host ORDER BY time DESC LIMIT 3 OFFSET 2

Execution Result (LIMIT 3):

{
  "series": [
    {
      "columns": ["time", "max(usage_total)"],
      "name": "cpu",
      "tags": {"host": "web-server-01"},
      "values": [
        [1721059200000, 78.5],  // 12:00:00 - latest
        [1721058600000, 82.3],  // 11:50:00
        [1721058000000, 75.8]   // 11:40:00
        // Only the first 3 rows are returned
      ]
    },
    {
      "columns": ["time", "max(usage_total)"],
      "name": "cpu",
      "tags": {"host": "web-server-02"},
      "values": [
        [1721059200000, 45.2],  // 12:00:00 - latest
        [1721058600000, 52.8],  // 11:50:00
        [1721058000000, 48.5]   // 11:40:00
        // Only the first 3 rows are returned
      ]
    }
  ]
}

Between-Group Sorting and Pagination (SORDER BY, SLIMIT, SOFFSET)¶

Between-group sorting and pagination are distinctive features of DQL. They operate on the first dimension of the two-dimensional structure, i.e., sorting the groups themselves. This sorting requires reducing the data of each group to a single value, and then comparing that value across different groups.

When the query does not use BY, the result usually has only one group. In this case, SORDER BY typically has no visible sorting effect, but SLIMIT / SOFFSET still apply with the semantics of "group count" (i.e., they act on that single group).

Basic Syntax¶

SORDER BY aggregate_function(expression) [ASC|DESC]
SLIMIT group_count
SOFFSET group_offset

Execution Mechanism¶

The execution process of between-group sorting:

1. Dimensionality reduction: Apply an aggregation function to each group to calculate a representative value
2. Group sorting: Sort all groups based on the reduced value
3. Group pagination: SLIMIT restricts the number of groups returned; SOFFSET skips the first N groups

Dimensionality Reduction Function Selection¶

Between-group sorting must use an aggregation function for dimensionality reduction. When no aggregation function is specified, the default function used is last.

Commonly used dimensionality reduction functions include:

However, almost any aggregation function that returns a single value can be used, so the full list is not exhaustive here.

Application Examples¶

Average-Based Sorting¶

// Sort by average CPU usage descending, find the hosts with the highest load
M::cpu:(avg(usage)) [1h::10m] BY host SORDER BY avg(usage) DESC SLIMIT 5

// Sort by average response time ascending, find the best performing services
M::response_time:(avg(response_time)) [1h] BY service SORDER BY avg(response_time) ASC SLIMIT 10

Execution Process Analysis:

1. Dimensionality reduction:

   • web-server-01: avg(usage) = 77.7
   • web-server-02: avg(usage) = 53.7
   • web-server-03: avg(usage) = 90.9

2. Group sorting (by avg(usage) DESC):

   • web-server-03: 90.9
   • web-server-01: 77.7
   • web-server-02: 53.7

3. Final result (SLIMIT 2):

{
  "series": [
    {
      "columns": ["time", "avg(usage)"],
      "name": "cpu",
      "tags": {"host": "web-server-03"},  // Average usage: 90.9 - Rank 1
      "values": [
        [1721059200000, 92.1],
        [1721058600000, 95.7],
        [1721058000000, 89.4]
      ]
    },
    {
      "columns": ["time", "avg(usage)"],
      "name": "cpu",
      "tags": {"host": "web-server-01"},  // Average usage: 77.7 - Rank 2
      "values": [
        [1721059200000, 78.5],
        [1721058600000, 82.3],
        [1721058000000, 75.8]
      ]
    }
    // web-server-02 (avg: 53.7) is filtered out because SLIMIT 2
  ]
}

Application Examples¶

// Sort by maximum CPU usage, find hosts with abnormal peaks
M::cpu:(max(usage_total)) [1h::10m] BY host SORDER BY max(usage_total) DESC SLIMIT 10

// Sort by minimum memory usage, find hosts with the lowest resource utilization
M::memory:(min(usage_percent)) [24h::1h] BY host SORDER BY min(usage_percent) ASC SLIMIT 5

// Sort by the latest CPU usage, find hosts with the highest current load
M::cpu:(usage) [1h::10m] BY host SORDER BY last(usage) DESC SLIMIT 10

// Sort by the latest error count, find services with the most current problems
L::logs:(count(*) as error_count) {level = 'error'} [1h] BY service SORDER BY error_count DESC SLIMIT 5

Combined Use of Dual Sorting and Pagination¶

In practice, within-group sorting and between-group sorting are often used together to achieve complex data display requirements. This combination allows controlling both the order of groups and the order of data within groups.

Execution Order¶

The execution order of dual sorting:

1. Between-group sorting: First, sort and paginate all groups
2. Within-group sorting: Then, sort and paginate the data within the selected groups

Application Examples¶

Monitoring Dashboard Scenario¶

// Find the 10 servers with the highest CPU usage, showing the latest 5 data points per server
M::cpu:(avg(usage)) [1h::10m] BY host
SORDER BY avg(usage) DESC SLIMIT 10    // Between-group sorting: find the top 10 by usage
ORDER BY time DESC LIMIT 5             // Within-group sorting: show the latest 5 points per server

Execution Result:

{
  "series": [
    {
      "columns": ["time", "avg(usage)"],
      "name": "cpu",
      "tags": {"host": "web-server-03"},  // Average usage: 90.9 - Rank 1
      "values": [
        [1721059200000, 92.1],  // 12:00:00 - latest
        [1721058600000, 95.7],  // 11:50:00
        [1721058000000, 89.4],  // 11:40:00
        [1721057400000, 97.6],  // 11:30:00
        [1721056800000, 87.3]   // 11:20:00
        // Only the latest 5 data points are returned (LIMIT 5)
      ]
    },
    {
      "columns": ["time", "avg(usage)"],
      "name": "cpu",
      "tags": {"host": "web-server-01"},  // Average usage: 77.7 - Rank 2
      "values": [
        [1721059200000, 78.5],  // 12:00:00 - latest
        [1721058600000, 82.3],  // 11:50:00
        [1721058000000, 75.8],  // 11:40:00
        [1721057400000, 88.2],  // 11:30:00
        [1721056800000, 72.1]   // 11:20:00
        // Only the latest 5 data points are returned (LIMIT 5)
      ]
    }
    // Other hosts are filtered out because SLIMIT 10 only returns the top 10 by usage
  ]
}

By mastering DQL's sorting and pagination features, you can build powerful monitoring dashboards, performance analysis tools, and business insight systems.