Metric SQL 用户手册
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.
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.
Lexical Constructs
Integers
Integers can be expressed as decimal or hexadecimal literals, as in
-3
519
0xdeadbeef
Floating-Point Numbers
Floating point numbers can be expressed like below:
3.1415926
2e8
4.2E2
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!'
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.
Boolean Literals
The boolean literals in Metrics SQL are just true
and false
, just like in SQL.
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.
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.
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).
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.
Arithmetic Expressions
Metrics SQL supports the following binary arithmetic operators: add (+
), subtract (’-’),
multiple (*
), division (/
), and modulo (%
).
It also supports the unary arithmetic operators -
and +
.
String Concatenation
The SQL-style string concatenation operator, ||
, is supported.
Relational Expressions
Metrics SQL supports most of the common SQL-style relational operators:
=
(equal)!=
or<>
(not equal)<=
<
>
>=
between ... and
(WIP)in (value...)
(WIP)like
andnot like
is
oris not
(WIP)
The like
operator does support two forms of patterns:
(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.- A regex literal like
/.../
andrx{...}
.
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
Logical Expressions
The following SQL-style logical operators are supported:
- binary
and
- binary
or
- unary
not
Subqueries
Subqueries are currently not supported by Metrics SQL though the Metrics SQL compiler can parse them just fine.
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.
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;
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;
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/';
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;
Order By Clause
The order by
clause is similar to the standard SQL language. Metrics SQL currently
has some limitations though:
- Only one column is allowed in the
order by
clause. - The
order by
column can only be a column name or acount()
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;
Limit Clause
The limit
clause is similar to the standard SQL language. Below is an example:
select *
from reqs
limit 10;
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.
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.
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
andhttps
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
andresp_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
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.
Author
Yichun Zhang <yichun@openresty.com>, OpenResty Inc.
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.