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: 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.
SQL: 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 five PromQL functions that are provided by Simple Log Service to perform nested queries. 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.

PromQL

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 three CPU users grouped by app and proc in 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 of 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 and Query examples.

SQL

The following examples show how to use the SQL syntax:

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 value of the __labels__, 'domain' field is www.example.com and obtain the sum of the values of 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 value of the __labels__, 'domain' field is www.example.com, obtain the sum of the values of the __value__ field, and aggregate the data by hour.

*| 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 Log analysis overview. 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. The following functions can be invoked only on the query and analysis page of a Metricstore: promql_query, promql_labels, promql_label_values, and promql_series. 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. This function is equivalent to the /query API of Prometheus. Parameter settings: query=<string> and time=<EndTime>.	*\| SELECT promql_query('up') FROM metrics
promql_query_range(string, string)	Evaluates a query on data within the time range specified by the StartTime and EndTime parameters. This function is equivalent to the /query_range API of Prometheus. Parameter settings: query=<string>, step=<duration>, start=<StartTime>, and end=<EndTime>.	*\| SELECT promql_query_range('up', '5m') FROM metrics
promql_labels()	Returns all label keys.	*\| SELECT promql_labels() FROM metrics
promql_labels(string)	Returns label keys that match specific conditions. You can add a match[] parameter to return the label keys from the time series matched by <series_selector>. You can specify only one condition in the match[] parameter. Example: promql_labels('up').	*\| SELECT promql_labels('up') FROM metrics
promql_label_values(string)	Returns the values of a label.	*\| SELECT promql_label_values('__name__') FROM metrics
promql_label_values(string, string)	Returns label values that match specific conditions. You can add a match[] parameter to return the values of a specific label from the time series matched by <series_selector>. You can specify only one condition in the match[] parameter. You must specify the label parameter after the match[] parameter. Example: promql_label_values('up', '__label_name__').	*\| SELECT promql_label_values('up', '__label_name__') FROM metrics
promql_series(string)	Returns the matched time series.	*\| SELECT promql_series('up') FROM metrics

A PromQL function is similar to a user-defined table generating 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 key.
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, values at a maximum of 11,000 points in time can be returned.
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.