All Products
Search
Document Center

Simple Log Service:Overview of query and analysis on metric data

Last Updated:Mar 18, 2024

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 3 CPU users grouped by app and proc in the previous 5 minutes. app specifies applications. 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 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__ != '' 
  • From the http_request_count metric data, query the data in which the value of the __labels__, 'domain' 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' 
  • From the http_request_count metric data, query the data in which the value of the __labels__, 'domain' field is www.example.com, obtain the sum of the values for 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. Among the functions, the promql_query, promql_labels, promql_label_values, and promql_series functions can be invoked only on the Custom 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 and analyze the closest data to a specified end time. 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 over a specified period of time. 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.

*| SELECT promql_labels() FROM metrics

promql_labels(string)

You can add the match[] parameter to return the label key of the corresponding <series_selector>.

You can add only one match[] parameter value. 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)

You can add the match[] parameter to return the values of a specific label of the corresponding <series_selector>.

You can add only one match[] parameter value. You must add the label parameter next to the match[] parameter. Example: promql_label_values('up', '__label_name__').

*| SELECT promql_label_values('up', '__label_name__') FROM metrics

promql_series(string)

Returns the time series that is matched.

*| 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.

    Sample queries:

    • promql_query(string) functionquery

    • promql_query_range(string, string) functionpromql_query_range

  • 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

    Label Key

    Sample queries:

    • promql_labels() functionlabels

    • promql_labels(string) functionlabels_match

    • promql_label_values(string) functionlabelValues

    • promql_label_values(string, string) functionlabel_values_match

  • 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.

    Sample query:series

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.