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

Log Service supports the following types of syntax for query and analysis on time series data:
  • SQL: You can use the SQL syntax to query and analyze time series data based on the encoding format of the data.
  • Combination of SQL and PromQL: This combination allows you to query and analyze time series data in an efficient manner. The combination is implemented by using nested queries. PromQL is the query language that is provided by Prometheus. For more information, see Prometheus documentation.

SQL

Examples of SQL-based query statements:
  • Query and analyze all data from a Metricstore.
    *| SELECT * FROM "my_metric_store.prom" WHERE __name__ != '' 
  • Query the data in which the value of the __labels__, 'domain' field is www.example.com and obtain the sum of the __value__ field.
    *| SELECT sum(__value__) FROM "my_metric_store.prom" WHERE element_at(__labels__, 'domain')='www.example.com' 
  • Query the data in which the value of the __labels__, 'domain' field is www.example.com, obtain the sum 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 element_at(__labels__, 'domain')='www.example.com'
    GROUP BY t
    ORDER BY t DESC
Description:
  • The SQL syntax for time series data is the same as the SQL syntax for log data. For more information, see Log analysis overview. When you query and analyze time series data by using the SQL syntax, the table name in the FROM clause must be {metrics_store_name}.prom. {metrics_store_name} specifies the name of the Metricstore that you create.
    Note You must enclose the 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

If you use the combination of SQL and PromQL, you can use the advanced features such as the machine learning feature and security check functions. For more information, see Overview and Security check functions.
Notice
  • If you use the combination of SQL and PromQL, the table name in the FROM clause must be metrics.
  • For more information about the API endpoints and descriptions of PromQL functions, see Prometheus documentation.

The following table describes the PromQL functions that are supported by Log Service. Among the functions, the promql_query, promql_labels, promql_label_values, and promql_series functions can be invoked only on the query and analysis page of a Metricstore.

Function Description Example
promql_query(string) Evaluates an instant query on the data at a point in time that is the closest to the end time of the query time range. 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 the data over a 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_label_values(string) Returns the values of a label. *| SELECT promql_label_values('__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 Data type Description
    metric varchar The metric name of the time series. If the 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 that represents a point in time.
  • The following table describes the schema of a table that is returned by the promql_labels() or promql_label_values(string) function.
    Field Data type Description
    label varchar The label keys.
  • The following table describes the schema of a table that is returned by the promql_series(string) function.
    Field Data type Description
    series map<varchar, varchar> The time 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 combination of SQL and PromQL for query and analysis, up to 11,000 points in time can be returned for a query.
  • If you use the combination of SQL and PromQL for query and analysis, the metric name and label that you specify must comply with the naming conventions. For more information, see Metric identifier.