DQL VS Other Query Languages¶

Preface¶

To help everyone quickly understand and use DQL, we will compare it with three common query languages: PromQL, LogQL, and SQL.

PromQL is a query language used in Prometheus for querying its time series data. LogQL is a log query language used in Grafana Loki, which borrows from the syntax structure of PromQL and is very similar to PromQL in terms of writing style. SQL is the most commonly used general-purpose query language in our daily lives, and its syntax structure is quite different from the former two (various databases are similar, here MySQL is taken as an example).

The initial syntax structure of DQL was similar to PromQL, but as business expanded, DQL gradually evolved with different query functionalities. It combines the basic syntax structure of PromQL and borrows some syntax structures and semantic expressions from SQL, aiming to make it easier for everyone to write more complex queries.

The following sections will explain the differences between these query languages in terms of:

• Basic syntax structure
• Supported common predefined functions
• Common query writing styles

Basic Syntax Structure¶

PromQL¶

In Prometheus, related metrics are organized in a discrete form. In its queries, you can directly search for corresponding metrics, such as:

http_requests_total{environment="prometheus", method!="GET"}

Here, it searches for the metric http_requests_total, filtering data by specifying its label constraints (environment and method).

LogQL¶

As the name suggests, LogQL is mainly used for log content queries, such as:

{container="query-frontend", namespace="loki-dev"}
    |= "metrics.go"
    | logfmt
    | duration > 10s and throughput_mb < 500

Here, {...} is called a Stream Selector in LogQL, which aims to define the data query scope (similar to the FROM ... part in SQL); the latter part is called Log Pipeline, which mainly handles log information extraction and filtering.

From this, it can be seen that {...} in LogQL is similar to Label Matchers in PromQL, and we can also understand it as a type of where condition filtering.

SQL¶

For the most familiar query language, if we want to achieve the same effect as the above two, a simple translation is as follows (due to different storage structures, only the general idea is expressed here):

SELECT * FROM `loki-dev`
    WHERE container="query-frontend" AND
    duration > 10s AND
    throughput_mb < 500

DQL¶

DQL is essentially a query translator, and its backend does not directly manage data storage and organization. Therefore, theoretically, it can support any type of storage engine, such as information data storage (MySQL/Oracle/ES/Redis, etc.), file storage (HBASE/S3/OSS, etc.). Currently, DQL is mainly used for querying the following types of data:

• Time series data
• Log data
• Object data
• APM data
• RUM data
• Key event data
• ...

For example:

metric::cpu:(usage_system, usage_user) { usage_idle > 0.9 } [2d:1d:1h] BY hostname

Here, metric specifies the time series data to be queried (which can be simply understood as a DB in MySQL), and cpu is one of the measurements (similar to a Table in MySQL), and it specifies two fields usage_system and usage_user to be searched; then, {...} represents the filter condition, and finally [...] represents the query time range: from the day before yesterday to yesterday, with a 1h aggregation interval.

More examples:

# Query pod objects in K8s (object)
object::kubelet_pod:(name, age) { cpu_usage > 30.0 } [10m] BY namespace

# Find logs (message field) of an application named my_service
logging::my_service:(message) [1d]

# View span data in APM with duration > 1000us, grouped by operation
T::my_service { duration > 1000 } [10m] BY operation

Horizontal Comparison¶

Basic Function Comparison¶

Peripheral Tool Support¶

Data Processing Function Support¶

• PromQL Supported Functions List
• LogQL Supported Functions List
• MySQL Supported Functions List
• DQL Supported Functions List

Common Query Statement Writing Comparison¶

Ordinary Data Query and Filtering¶

# LogQL
{ cluster="ops-tools1", namespace="dev", job="query-frontend"}
  |= "metrics.go"
  !="out of order"
  | logfmt
  | duration > 30s or status_code!="200"

# PromQL (PromQL probably does not support ordinary OR filtering)
http_requests_total{ cluster='ops-tools1', job!='query=frontend', duration > 30s }

# SQL
SELECT * FROM dev
  WHERE cluster='ops-tools' AND
  job='query=frontend' AND
  (duration > 30000000000 OR stataus_code != 200)

# DQL: From the statement structure, it can be seen that DQL's semantic organization is closer to SQL
L::dev {
  cluster='ops-tools',
  job='query=frontend',
  message != match("out of order")
  (duraton > 30s OR stataus_code != 200) # DQL supports nested structure filtering
}

The following are various DQL statement writing styles:

# where-clause can be connected with AND, AND is semantically equivalent to `,'
L::dev {
  cluster='ops-tools' AND
  job='query=frontend' AND
  message != match("out of order") AND
  (duraton > 30s OR stataus_code != 200)}

# Supports AS alias/Supports Chinese variables
metric::cpu:(usage_system AS System Usage, usage_user AS User Usage)

# where-cluase supports array-list IN filtering
L::dev {
  cluster='ops-tools' AND
  job IN [ 'query=frontend', 'query=backend'] AND
  message != match("out of order") AND
  (duraton > 30s OR stataus_code != 200)
}

# Supports base64 value passing: for complex strings (such as multi-line), avoids troublesome escaping
T::dev {
  cluster='ops-tools' AND
  resourec IN [
    'some-raw-string', # Ordinary string
    b64'c2VsZWN0ICoKZnJvbSBhYmMKd2hlcmUgeCA+IDAK' # base64 string
  ]
}

Query and Filtering with Aggregation¶

# LogQL
sum by (org_id) ({source="ops-tools",container="app-dev"} |= "metrics.go" | logfmt | unwrap bytes_processed [1m])

# PromQL
histogram_quantile(0.9, sum by (job, le) (rate(http_request_duration_seconds_bucket[10m])))

# DQL (Note, ops-tools needs to be enclosed in `` to avoid being parsed as a subtraction expression)
L::`ops-tools`:(bytes_processed) {filename = "metrics.go", container="app-dev"} [2m] BY sum(orig_id)

Browsing Data¶

# LogQL/PromQL Similar query functionality not found

# MySQL
show tables;
show databases;

# DQL
show_measurement()    # View time series measurement list
show_object_source()  # View object classification list
show_rum_source()     # View RUM data classification list
show_logging_source() # View log classification list

Summary¶

The above content provides a basic introduction to several common query languages. Each language has its specific application scenarios, and the functional differences are significant. The design intention of DQL is to provide a mixed storage query solution, which makes it fundamentally different from other languages. Although DQL does not have an independent storage engine, its scalability far exceeds that of other languages, aligning with its mixed storage query positioning.

Currently, DQL is actively being developed and improved, with significant room for improvement in functionality and performance. In TrueWatch, all data queries are fully implemented using DQL, and its functionality, performance, and stability have been verified over a long period of time. As TrueWatch products continue to iterate, DQL will also continue to evolve to meet the needs of products and developers.