Metric SQL User Manual

The Metrics SQL Language User Manual

This manual documents the Metrics SQL language from the user’s perspective.

# Table of Contents

# Convention

We use the word msql for the Ops language throughout this document for convenience.

Example code with problems will get a question mark, ?, at the beginning of each of its lines. For example:

? select *;

# Description

The Metrics SQL language is inspired by the SQL language but is never meant to be 100% compatible with the SQL standard nor some SQL dialect. Nevertheless, Metrics SQL code may look very similar to SQL code.

The Metrics SQL language is provided as part of the OpenResty Edge platform. It is a means to quickly create new dynamic metrics for OpenResty Edge.

Back to TOC

# Program Layout

A Metrics SQL program consists of multiple statements separated by semicolons. Currently only a single select query statement is allowed, but it may change in the future. Below is an example:

select uri, host, count(*)
from reqs group by uri, host limit 10;

The last statement’s trailing semicolon may be omitted.

Back to TOC

# Lexical Constructs

# Integers

Integers can be expressed as decimal or hexadecimal literals, as in

-3
519
0xdeadbeef

Back to TOC

# Floating-Point Numbers

Floating point numbers can be expressed like below:

3.1415926
2e8
4.2E2

Back to TOC

# String Literals

Character string literals are quoted by single-quotes, just like the standard SQL language, as in

'hello, world!'

Additionally we support the following special escape sequences to specify special characters:

\a          bell, ascii code 0x07
\b          backspace, ascii code 0x08
\f          form feed, ascii code 0x0c
\n          new line
\r          carriage return
\t          tab
\v          vertical tab
\\          backslash (\)
\'          single-quote character (')
\"          double-quote character (")
\<newline>  new line character, just like \n

Below is an example using escape sequences:

'hello\nworld\t!'

Back to TOC

# Regex Literals

Regex literals are a way to represent regular expressions for pattern matching (as with the like binary operator). A regex literal, say, /[0-9]+/, can be quoted by different delimiters, as in

/[0-9]+/

rx/[0-9]+/

rx{[0-9]+}

rx([0-9]+)

rx[[0-9]+]

rx'[0-9]+'

rx"[0-9]+"

rx![0-9]+!

You can choose the best delimiters to minimize the necessary escapes inside the regex pattern itself.

Currently only the common subset of the Perl compatible regex and POSIX regex syntax is allowed with the exception of the back-reference syntax (like \1, \2, and etc). We may lift this restriction in the future.

Submatch captures using parentheses are supported.

Back to TOC

# Boolean Literals

The boolean literals in Metrics SQL are just true and false, just like in SQL.

Back to TOC

# Null

The special null keyword indicates a null value (similar to the null value in SQL).

Metrics SQL does not usually involve null values and thus this literal is rarely used. It is included just for the sake of completeness.

Back to TOC

# Tables

Table names must always be specified in their fully qualified form unless an alias is given by the user (like with the as ALIAS syntax in a from clause).

Metrics SQL currently supports one built-in tables reqs. See “Builtin Tables” section for details.

Back to TOC

# Columns

Column names can be indicated by their bare names like uri and host when there is no ambiguity, or by their fully qualified form with the table name, as in

select uri, count(*)
from reqs group by uri

Where the uri column is specified using the fully qualified form, reqs.uri.

Columns can also be referenced indirectly via a user alias (as with the as ALIAS syntax in the select list).

Back to TOC

# Operators and Expressions

Metrics SQL supports a lot of SQL-like operators and expression types with some of its own additions.

The precedence table for all the operators is as follows:

3    unary !
4    - (unary minus), + (unary plus)
5    || (string concatenation)
7    *, /, %
8    binary -, +
12    = (comparison), >=, >, <=, <, <>, !=, is, like, in, prefix, suffix, contains
13    between
14    not
15    and
17    or
19    left join, inner join
20    , (cross join)

Usually parentheses (...) can be used to group operators with lower precedence.

The not ... variants like not between and is not are not listed in the operator precedence table.

Back to TOC

# Arithmetic Expressions

Metrics SQL supports the following binary arithmetic operators: add (+), subtract (’-’), multiple (*), division (/), and modulo (%).

It also supports the unary arithmetic operators - and +.

Back to TOC

# String Concatenation

The SQL-style string concatenation operator, ||, is supported.

Back to TOC

# Relational Expressions

Metrics SQL supports most of the common SQL-style relational operators:

• = (equal)
• != or <> (not equal)
• <=
• <
• >
• >=
• between ... and (WIP)
• in (value...) (WIP)
• like and not like
• is or is not (WIP)

The like operator does support two forms of patterns:

1. (WIP) SQL-style patterns with meta-characters like % (matching zero or more arbitrary characters) and _ (matching one arbitrary character). The meta-characters can be escaped by a backslash (/) to mean the % and _ characters themselves.
2. A regex literal like /.../ and rx{...}.

Additionally Metrics SQL supports the following string matching operators to test if the RHS string is a prefix, substring, or suffix in the LHS string, respectively:

• prefix
• contains
• suffix

Back to TOC

# Logical Expressions

The following SQL-style logical operators are supported:

• binary and
• binary or
• unary not

Back to TOC

# Subqueries

Subqueries are currently not supported by Metrics SQL though the Metrics SQL compiler can parse them just fine.

Back to TOC

# Select Query Statement

This statement contains a single select query with one or more of the following clauses. Note that the select clause is always required.

Back to TOC

# Select Clause

The SQL-style select clause is supported. Below are some examples:

select host, count(*) from reqs group by host;

aggregate functions and transformation function can appear in the select list. Relational expressions, arithmetic expressions, and string concatenation expressions Logical expressions are not allowed here, by now. We may lift this restriction in the future.

Each selected value can take an optional alias right after the value expression, just as in standard SQL, as in

select avg(active_conns) as active from http;

Aggregate functions like sum(), avg(), min(), max(), and count() can also be used in the selected value expressions, as in

select count(*), sum(latency_s)
from reqs;

Transformation functions like hist_log() can also be used in the selected value expressions, as in

select count(*), hist_log(latency_ms) as latency
from reqs group by latency;

Back to TOC

# From Clause

The from clause is also similar to the standard SQL language. One can specify a single table, as in

select uri, count(*)
from reqs group by uri;

Back to TOC

# Where Clause

The where clause is the same as in the standard SQL language. One can specify a logical expression as a search condition here. Below is an example:

select count(*)
from reqs
where uri prefix '/css/';

Back to TOC

# Group By Clause

The group by clause is similar to the standard SQL language, as in

select count(*) as cnt, host
from reqs
group by host
order by cnt desc
limit 10;

Back to TOC

# Order By Clause

The order by clause is similar to the standard SQL language. Metrics SQL currently has some limitations though:

1. Only one column is allowed in the order by clause.
2. The order by column can only be a column name or a count() aggregate function.

Below is an example:

select count(*), host
from reqs
group by host
order by count(*) desc;

It is also possible to reference the selected value expression’s alias (if any) in the order by clause, as in

select count(*) cnt, host
from reqs
group by host
order by cnt desc;

Back to TOC

# Limit Clause

The limit clause is similar to the standard SQL language. Below is an example:

select *
from reqs
limit 10;

Back to TOC

# Offset Clause(WIP)

The offset clause syntax is similar to the PL/SQL language provided by PostgreSQL. Below is an example:

select *
from reqs
offset 1
limit 10;

This will skip the first row and only output the 2nd through the 10th rows.

Back to TOC

# Builtin Tables

Below is a detailed list of the built-in tables currently supported by the Metrics SQL compiler. We are adding new tables and new columns quickly.

Back to TOC

# reqs

This table is used to inspect nginx (main) requests. Nginx sub-requests are excluded here.

It has the following columns:

• args

  The URI query-string of the HTTP request.

• host

  The host name of the HTTP request.

• latency_ms

  An integer number indicating the request latency in milliseconds, like 120.

• latency_s

  A floating-point number indicating the request latency in seconds, like 0.32.

• keepalive

  A boolean value indicating whether the current request enables HTTP keep-alive for the current TCP connection.

• method

  The method string of the HTTP request.

• scheme

  The scheme string like http and https of the current HTTP request.

• uri

  HTTP request URI (excluding any query-string).

• raw_uri

  HTTP raw request URI (include query-string).

• client_ip

  String. IP address (IPv4 or IPv6 or unix socket file path).

• req_size

  Integer. The whole request size (including header and body) in bytes.

• req_body_size

  Integer. The request body size in bytes.

• resp_size

  Integer. The whole response size (including header and body) in bytes (if compression is involved, it is the compressed size).

• resp_body_size

  Integer. The response body size in bytes.

• total_size

  Integer. Total size of the downstream request, including req_size and resp_size.

• server_city

  String. City name obtained by querying geographic information data based on server IP address

• server_province

  String. Province name obtained by querying geographic information data based on server IP address

• server_country

  String. Country name obtained by querying geographic information data based on server IP address

• server_continent

  String. Continent name obtained by querying geographic information data based on server IP address

• server_isp

  String. ISP name obtained by querying geographic information data based on server IP address

• server_latitude

  String. Latitude obtained by querying geographic information data based on server IP address

• server_longtitude

  String. Longitude obtained by querying geographic information data based on server IP address

• server_port

  Integer. The server port the current request is accessing.

• status

  Integer. The HTTP response status code (like 502 and 200) or the internal nginx error code if no meaningful HTTP status code is available.

• status_class

  String. The HTTP response status code after classification, like 2xx/3xx/4xx/5xx.

• is_ssl

  Boolean. Whether the current request uses SSL or TLS.

• tls_ver

  Number. The TLS protocol version number like 1.2 and 1.3.

• ssl_ver

  Number. The SSL protocol version number like 2 and 3.

• resp_is_chunked

  Boolean. Whether the response body uses the chunked encoding.

• req_is_chunked

  Boolean. Whether the request body uses the chunked encoding.

• http_ver

  Number. The HTTP protocol version (0.9, 1.0, 1.1, 2.0).

• is_websocket

  Boolean. Whether the current request has upgraded to the WebSocket protocol.

• resp_is_compressed

  Boolean. Whether the current request’s response body is compressed (by gzip or other encodings).

• mime_type

  String. Should be values like application/javascript. Any trailing parameters like ; charset=utf-8 should be excluded.

• uses_lua

  Boolean. Whether the current request uses any Lua code handlers in any of the request processing phases (like log_by_lua, rewrite_by_lua, and etc).

• for_static_file

  Boolean. Whether the current request uses the static file module to serve static files on the current machine’s file system.

• has_upstream

  Boolean. Whether the current request uses a proxy (i.e., using the nginx upstream module).

• uses_error_page

  Boolean. Whether the current request serves an error page.

• uri_changes

  Integer. The number of URI changes done in the request processing phases (corresponding to r->uri_changes).

• uri_changed

  Boolean. Whether the current request’s URI has ever changed by the request processing phases (corresponding to r->uri_changed).

• is_pipelined

  Boolean. Whether the current request is a pipelined request (i.e., r->pipeline on the C land).

• resp_header_only

  Boolean. Whether the current response has a header only (r->header_only).

• location

  String. The nginx location name last serving the current request.

• user_agent

  String. The User-Agent string.

• conn_is_reused

  Boolean. Whether the current request reuses a connection created by an earlier request.

• upstream_addr

  String. The last upstream address used to connect to the upstream server.

• upstream_cache_status

  String. Upstream cache status. Empty string if not upstream, or one of the following: MISS BYPASS EXPIRED STALE UPDATING REVALIDATED HIT

• is_upstream_cache_status_hit

  String. The current request’s cache status is HIT, indicating that the requested content was found in the cache and is valid, with the response served directly from cache. This variable was first introduced in version 25.6.20‑1.

• is_upstream_cache_status_miss

  String. The current request’s cache status is MISS, indicating that the requested content was not found in the cache and needs to be fetched from the upstream server. This variable was first introduced in version 25.6.20‑1.

• is_upstream_cache_status_bypass

  String. The current request’s cache status is BYPASS, indicating that the cache was bypassed and the request was forwarded directly to the upstream server without using cache. This variable was first introduced in version 25.6.20‑1.

• is_upstream_cache_status_expired

  String. The current request’s cache status is EXPIRED, indicating that the cached content has expired and needs to be refetched from the upstream server. This variable was first introduced in version 25.6.20‑1.

• is_upstream_cache_status_stale

  String. The current request’s cache status is STALE, indicating that the cached content has expired but is still usable, typically returned when the upstream server is unavailable. This variable was first introduced in version 25.6.20‑1.

• is_upstream_cache_status_updating

  String. The current request’s cache status is UPDATING, indicating that the cache is being updated, and the current request is served with the old cached content. This variable was first introduced in version 25.6.20‑1.

• is_upstream_cache_status_revalidated

  String. The current request’s cache status is REVALIDATED, indicating that the cached content has been revalidated with the upstream server and confirmed to still be valid. This variable was first introduced in version 25.6.20‑1.

• upstream_byte_sent

  Integer. Number of bytes sent to the upstream server. Values of multiple upstream servers will be added together.

• upstream_byte_received

  Integer. Number of bytes received from the upstream server. Values of multiple upstream servers will be added together.

• upstream_byte_total

  Integer. Number of bytes sent to and received from the upstream server. Values of multiple upstream servers will be added together.

• upstream_body_length

  Integer. The length of the response obtained from the upstream server. Values of multiple upstream servers will be added together.

• upstream_header_timer_ms

  Integer. Keeps time spent on receiving the response header from the upstream server; the time is kept in millisecond resolution. Times of several responses are added together.

• upstream_connect_timer_ms

  Integer. Keeps time spent on establishing a connection with the upstream server; the time is kept in millisecond resolution. In case of SSL, includes time spent on handshake. Times of several connections are added together.

• upstream_resp_timer_ms

  Integer. Keeps time spent on receiving the response from the upstream server; the time is kept in millisecond resolution. Times of several responses are added together.

• client_city

  String. City name obtained by querying geographic information data based on client IP address

• client_province

  String. Province name obtained by querying geographic information data based on client IP address

• client_country

  String. Country name obtained by querying geographic information data based on client IP address

• client_continent

  String. Continent name obtained by querying geographic information data based on client IP address

• client_isp

  String. ISP name obtained by querying geographic information data based on client IP address

• client_latitude

  String. Latitude obtained by querying geographic information data based on client IP address

• client_longtitude

  String. Longitude obtained by querying geographic information data based on client IP address

• is_status_2xx

  Boolean. Whether the current request’s response status is between 200 and 299.

• is_status_3xx

  Boolean. Whether the current request’s response status is between 300 and 399.

• is_status_4xx

  Boolean. Whether the current request’s response status is between 400 and 499.

• is_status_5xx

  Boolean. Whether the current request’s response status is between 500 and 599.

• is_status_other

  Boolean. Whether the current request’s response status is less than 200 or greater than 599.

• is_upstream_status_2xx

  Boolean. Whether the current request’s response status is between 200 and 299.

• is_upstream_status_3xx

  Boolean. Whether the upstream’s response status is between 300 and 399.

• is_upstream_status_4xx

  Boolean. Whether the upstream’s response status is between 400 and 499.

• is_upstream_status_5xx

  Boolean. Whether the upstream’s response status is between 500 and 599.

• is_upstream_status_other

  Boolean. Whether the upstream’s response status is less than 200 or greater than 599.

• is_status_400

  Boolean. Whether the response status is 400 or not.

• is_status_403

  Boolean. Whether the response status is 403 or not.

• is_status_404

  Boolean. Whether the response status is 404 or not.

• is_status_412

  Boolean. Whether the response status is 412 or not.

• is_status_429

  Boolean. Whether the response status is 429 or not.

• is_status_500

  Boolean. Whether the response status is 500 or not.

• is_status_502

  Boolean. Whether the response status is 502 or not.

• is_status_503

  Boolean. Whether the response status is 503 or not.

• is_status_504

  Boolean. Whether the response status is 400 or not.

• is_upstream_status_400

  Boolean. Whether the upstream’s’ response status is 400 or not.

• is_upstream_status_403

  Boolean. Whether the upstream’s’ response status is 403 or not.

• is_upstream_status_404

  Boolean. Whether the upstream’s’ response status is 404 or not.

• is_upstream_status_412

  Boolean. Whether the upstream’s’ response status is 412 or not.

• is_upstream_status_429

  Boolean. Whether the upstream’s’ response status is 429 or not.

• is_upstream_status_500

  Boolean. Whether the upstream’s’ response status is 500 or not.

• is_upstream_status_502

  Boolean. Whether the upstream’s’ response status is 502 or not.

• is_upstream_status_503

  Boolean. Whether the upstream’s’ response status is 503 or not.

• is_upstream_status_504

  Boolean. Whether the upstream’s’ response status is 504 or not.

# HTTP

This table is mainly used to count the number of connections.

It has the following columns:

• active_conns

  The current number of active client connections including Waiting connections..

• waiting_conns

  The current number of idle client connections waiting for a request.

• writing_conns

  The current number of connections where openresty edge is writing the response back to the client.

• reading_conns

  The current number of connections where openresty edge is reading the request header.

Back to TOC

# Example

# Example 1: Top 10 domains by cache hit rate

select host, sum(is_upstream_cache_status_hit) / count(*) * 100 as cache_hit_ratio_percent
from reqs
where upstream_cache_status != ''
group by host
order by cache_hit_ratio_percent desc
limit 10

Back to TOC

# Author

Yichun Zhang <yichun@openresty.com>, OpenResty Inc.

Back to TOC

# Copyright & License

Copyright (C) 2020-2021 by OpenResty Inc. All rights reserved.

This document is proprietary and contains confidential information. Redistribution of this document without written permission from the copyright holders is prohibited at all times.

Back to TOC