Syntax and limits of query and analysis on metric data - Simple Log Service

This topic describes the syntax and limits of query and analysis on metric data.

Simple Log Service supports the following types of syntax for query and analysis on metric data:

PromQL syntax: PromQL is the query language provided by Prometheus. You can use the PromQL syntax to simplify the query and analysis on metric data. For more information, see Prometheus documentation and PromQL function usage examples.
SQL syntax: You can use the SQL syntax to query and analyze metric data based on the encoding format of the data.
Combination of SQL and PromQL: You can use the SQL syntax together with the PromQL functions that are provided by Simple Log Service to perform nested query. When you use the PromQL syntax, you can also use the machine learning syntax of Simple Log Service. For more information, see Machine learning syntax.

If you use the PromQL syntax or the combination of SQL and PromQL for query and analysis, the metric name and labels that you specify must comply with the naming conventions. For more information, see Metric identifier.

PromQL syntax

The following examples show how to use the PromQL syntax:

Query the time series in which the metric name is http_requests_total, the job label is apiserver, and the handler label is /api/comments.
```
http_requests_total{job="apiserver", handler="/api/comments"}
```
Query the top 3 CPU users grouped by app and proc within the previous 5 minutes. app specifies applications, and proc specifies process types.
```
topk(3, sum by (app, proc) (rate(instance_cpu_time_ns[5m])))
```

Query unhealthy pods.

min_over_time(sum by (namespace, pod) (kube_pod_status_phase{phase=~"Pending|Unknown|Failed"})[15m:1m]) > 0

Query the sum of the CPU utilization for Kubernetes DaemonSets.

sum (rate (container_cpu_usage_seconds_total{pod=~"^x.*$",cluster=~".*",namespace=~".*"}[1m])) / sum (kube_pod_container_resource_limits_cpu_cores{pod=~"^null.*$",cluster=~".*",namespace=~".*"}) * 100

For more information about the PromQL syntax, see Prometheus documentation, Querying examples, and PromQL use cases.

SQL syntax

The following examples show how to use the SQL syntax. For more information, see SQL use cases.

Query and analyze all data.

*| SELECT * FROM "my_metric_store.prom" WHERE __name__ != ''

For the http_request_count metric, query the data in which the domain value of the __labels__ field is www.example.com and obtain the sum of the values for the __value__ field.
```
*| SELECT sum(__value__) FROM "my_metric_store.prom" WHERE __name__='http_request_count' and element_at(__labels__, 'domain')='www.example.com' 
```

For the http_request_count metric, query the data in which the domain value of the __labels__ field is www.example.com, aggregate the values of the __value__ field by hour, and obtain the sum of the values.

*| SELECT sum(__value__),date_trunc('hour', __time_nano__/1000000) as t
FROM "my_metric_store.prom" 
WHERE __name__='http_request_count' and element_at(__labels__, 'domain')='www.example.com'
GROUP BY t
ORDER BY t DESC

The following list describes the SQL syntax:

The SQL syntax for metric data is the same as the SQL syntax for log data. For more information, see Analysis syntax. When you query and analyze metric data by using the SQL syntax, the table name in a FROM clause must be in the {metrics_store_name}.prom format. {metrics_store_name} specifies the name of the Metricstore that you create.
Note
You must enclose a table name in double quotation marks ("").
You can use the element_at() function to obtain the value of a key from the __labels__ field. Example: element_at(__labels__, 'key').
For more information about table schemas, see Encoding format.

Combination of SQL and PromQL

Simple Log Service provides seven PromQL functions. Among the functions, the promql_query, promql_labels, promql_label_values, and promql_series functions can be invoked only on the custom query and analysis page of a Metricstore. The following table describes the functions.

Important

If you use the combination of SQL and PromQL, the table name in a FROM clause must be metrics.
For more information about the API endpoints and descriptions of PromQL functions, see Prometheus documentation.

Function	Description	Example
promql_query(string)	Evaluates an instant query. You can call this function to query the closest data to the end time of a specific time range, specified by the EndTime parameter. This function is equivalent to the /query API of Prometheus. Parameter setting: query=<string>.	*\| SELECT promql_query('up') FROM metrics
promql_query_range(string, string)	Evaluates a query on data within a time range, specified by the StartTime and EndTime parameters. This function is equivalent to the /query_range API of Prometheus. Parameter settings: query=<string> and step=<duration>.	*\| SELECT promql_query_range('up', '5m') FROM metrics
promql_labels()	Returns all label keys. By default, this function returns only the data within the following time range: [<EndTime> - 5min, <EndTime>].	*\| SELECT promql_labels() FROM metrics
promql_labels(string)	Supports the match[] parameter. This parameter is used to return the label keys that correspond to <series_selector>. This function supports only one value for the match[] parameter. Example: promql_labels('up'). By default, this function returns only the data within the following time range: [<EndTime> - 5min, <EndTime>].	*\| SELECT promql_labels('up') FROM metrics
promql_label_values(string)	Returns the values of a label. By default, this function returns only the data within the following time range: [<EndTime> - 5min, <EndTime>].	*\| SELECT promql_label_values('__name__') FROM metrics
promql_label_values(string, string)	Supports the match[] parameter. This parameter is used to return the values for a specific label that corresponds to <series_selector>. This function supports only one value for the match[] parameter. You must configure the match[] parameter before the Label parameter. Example: promql_label_values('up', '__label_name__') . By default, this function returns only the data within the following time range: [<EndTime> - 5min, <EndTime>].	*\| SELECT promql_label_values('up', '__label_name__') FROM metrics
promql_series(string)	Returns the matched time series. By default, this function returns only the data within the following time range: [<EndTime> - 5min, <EndTime>].	*\| SELECT promql_series('up') FROM metrics

A PromQL function is similar to a user-defined table-valued function (UDTF) and returns a table.

The following table describes the schema of a table that is returned by the promql_query(string) or promql_query_range(string, string) function.

Field	Type	Description
metric	varchar	The metric name of the time series. If a GROUP BY clause is included in the query statement, this field may be empty.
labels	map<varchar, varchar>	The labels. The value is a map.
time	bigint	The time.
value	double	The value at a specific point in time.

Query examples:

promql_query(string) function
promql_query_range(string, string) function

The following table describes the schema of a table that is returned by the promql_labels(), promql_labels(string), promql_label_values(string), or promql_label_values(string, string) function.
Field
Type
Description
label
varchar
The label keys.
Query examples:
- promql_labels() function
- promql_labels(string) function
- promql_label_values(string) function
- promql_label_values(string, string) function
The following table describes the schema of a table that is returned by the promql_series(string) function.
Field
Type
Description
series
map<varchar, varchar>
The time series.
Query example:

Limits

A Metricstore supports only the query API endpoints of Prometheus, such as /query and /query_range. Other API endpoints, such as /admin, /alerts, and /rules, are not supported.
If you use the PromQL syntax or the combination of SQL and PromQL for query and analysis, the values at up to 11,000 points in time can be returned.