RFD 463 The Oximeter Query Language

OxQL is designed to query the timeseries data produced by various components in the Oxide rack. The data model for such data is described in detail in [RFD 161]. [RFD 162] describes the architecture of the collection system itself. This RFD includes a brief overview of the data model, with a focus on the pieces relevant to OxQL. [RFD 442] explores how other, related platforms expose metric data. That survey led us to adopt our own DSL.

Let’s start by looking at a simple query. The query below selects the number of bytes sent out sleds' data links over the last minute.

This query shows a few key features of OxQL:

Running this query on a live system, we might see data like the following:

Let’s break down this output.

Selecting raw data can be quite useful, especially when first examining a system or its behavior. It can illuminate the basic shape and structure of the data, such as fields and their values; data and timestamp ranges; etc. Still, we often want to process the raw data in various ways to better to understand a system’s behavior and diagnose issues. The next queries will explore how to do that.

As we saw above, data can be piped from one table operation into another. In addition to filters, table operations can also be used to compute averages or other summaries of the data. The query below computes the number of bytes sent over the last hour, averaged into 5 minute windows.

Its output might look like this:

In the preceding example, we saw that we can average data over time within one timeseries. (The number of timeseries in the table didn’t change.) But in many situations one actually wants to average data between timeseries. For example, one might be interested in the aggregate network usage for each sled, regardless of which data link was used to actually send data. That can be done by grouping timeseries together.

The query below computes the number of bytes sent in the last hour, averaged over 5 minute windows, for every sled.

The data for this query might look like this:

We can see that each timeseries in the output table just has one field, sled_serial, which is the field listed in the group_by operation. Each timeseries in the output table is generated by averaging all the timeseries in the group, i.e., with the value shown here for the sled_serial field. See below for more details on the group_by table operation.

The above example shows how one might look at average network output over time. However, understanding system behavior or diagnosing issues often requires correlating multiple pieces of data at once. In this case, one might also want to know the number of bytes received during this same time period.

This requires merging data from two different tables, not just two timeseries within the same table. We can do that with a subquery and the join table operation, which looks like this:

The output of the above query might look like this:

By examining a few different example queries, we’ve seen many of the major features of OxQL:

After this quick tour of OxQL, the remaining sections are a detailed reference of the language. We’ll start with the main concepts and terminology.

A table is a set of data corresponding to a fixed schema. The schema includes the table name; a set of typed key-value pairs called fields; a data type>; and a <<_metric_type,metric type.

A timeseries is one element in a table, with unique values for the fields. There may be any number of timeseries in a table. All of them have the same schema, but each is uniquely identified by these field values. The values for the fields are used to generate a timeseries key, which is essentially a hash of the unique data identifying the timeseries. Note that the key is used during query processing and to organize the output data, but it is rarely useful in the query output.

A field is a typed key-value pair. It consists of a name and a value, which is one of the following types:

Fields are used to provide context for the data in a timeseries. They are also used to identify a timeseries, as discussed above, by generating the timeseries key.

In our examples above, the field sled_serial is a string serial number for a sled, while the sled_id field is a UUID. These identify the compute sled from which the data is derived, for example. Other fields provide additional context; for example, the link_name identifies the individual network data link in question.

The datum refers to the measured value of a timeseries. It is always associated with a timestamp, and may be associated with a start time if the datum is cumulative.

A datum may have one of the following types:

The datum type explored in the previous section describes the range of values that a datum can take. Data in a timeseries also has a metric type, which captures how data at different times relates to each other. The metric type takes one of three values:

An identifier is a name, such as for a table operation or the field of a table.

For example, in the table operation filter link_name == "foo", both the operation name filter and the field name link_name are identifiers.

Identifiers are formatted as lowercase, ASCII characters or digits, possibly delimited by underscores, and have the following structure:

Identifiers may not start with a digit, though the underscore-delimited words after the first may start that way. The special identifiers are implicitly added to a table by the OxQL query engine, and are used to refer to the common elements that all tables and timeseries share, such as their timestamp or the datum value itself. See Terminology for details on the anatomy of a timeseries.

A table name is used to identify a table in the database or the output of an OxQL query.

The initial data input to OxQL queries is a table stored in the telemetry database. These are identified by a name with a particular structure: a target resource and a metric name, separated by a :. For example, the sled_data_link:bytes_sent table lists the number of bytes sent on a single sled’s NIC.

Table names are also used to name the output of a query. In many cases, this is the same as the input table name, but may change during processing. For example, the name of the output of a join operation is a concatenation of the input table names.

OxQL supports the common types of literals, usually used in filtering expressions or as arguments to table operations. This includes things like booleans, timestamps, numeric values, and strings. Where possible and helpful, the regular expression matching each literal has been included below.

Comparison operators are used to compare two values, and return a boolean. Most familiar operators are supported, including equality, less-than, etc. Here is the full list:

The last operator, ~= implements regular expression matching, and is only supported for strings. Expressions such as "[a-f]+0$ can be used, for example, to match any string ending with one or more character a-f and a single digit 0. The full supported regular expression syntax is described here.

Logical operators can also be used to combine one or two boolean values together. The supported operators are:

Note that the XOR operator is logical, not bitwise. The precendece of the operators follows the common programming order: in order of increasing precedence, they are OR, AND, XOR, and NOT.

The fundamental unit of an OxQL query is a table operation. Each table is zero or more timeseries, all with the same field names, field types, and data types. In this sense, a table is analogous to a table in a traditional relational database: it contains entries all with the same schema. In this case, an entry is a timeseries, which has a unique set of field values along with its timestamped data points. (See Terminology for more details.) Below is a summary of each supported table operation, and more details on each are in the following sections.

Each table operation may take zero or more tables, and produce zero or more new tables after processing their input. The number of supported tables, and any restrictions on data types, are listed in the section describing each operation.

All queries or subqueries must begin by extracting some data from the timeseries database, on which the rest of the query operates. This is done using the get table operation, described in detail here.

Complex queries are constructed by stringing together table operations with the familiar pipe operator, |. The output of each table operation is fed into the next. For example, the query

will first get the raw data from the sled_data_link:bytes_sent table, and then restrict it to the last minute from the time the query is run. This will result in one table, since there is one table named in the query. There may be zero, one, or many timeseries in the table, depending on the data available in the database when the query is run.

The above query’s output might look like this:

As mentioned above, queries can be either flat or nested. A query is nested if it contains subqueries, two or more queries inside braces ({}), separated by a semicolon (;). Each subquery itself can be flat or nested, so that quite complex processing pipelines can be built up using these basic elements. For example the query:

will get the number of bytes sent and received on all sleds' physical data links, over the last minute. Further filtering can be done to limit the data, described in detail below. Here is an example output:

Note that there are two tables here, printed one after the other. The first shows the table sled_data_link:bytes_sent, with its timeseries. This is first because the subquery listed it first. Next is the sled_data_link:bytes_received table, with all its timeseries.

The primordial table operation is get. This retrieves a single table by its name name from the database. It takes zero tables as input, and produces exactly one table as output. That output table may have any number of timeseries in it.

Example:

A filter table operation passes through data which matches the provided filtering expression. Filters take an identifier, a comparison operator, and a literal. (In the future, more complex expressions beyond literals may be supported.)

See Comparison and logical operators and Literals for details on those components.

The provided identifier may name any one of the fields in the table, or refer to a special identifier, such as timestamp. For cumulative or delta metric types, the start_time identifier is also supported. For scalar (non-histogram) timeseries, the datum identifier is used to refer to the value of the data point. For histograms, the bins and counts identifiers refer to the values of the data point instead.

Filters may include compound expressions, joined with the logical operators defined above. The precedence rules are also defined in that section, but note that comparison operators always bind more tightly than logical operators. In other words, foo == 0 && bar == 1 is equivalent to (foo == 0) && (bar == 1). Parentheses may be used to group expressions.

Below are several examples of filter table operations.

Note that the literals used to compare against a field must be comparable to the actual data type of that field. It is an error, for example, to write filter foo == 0 if the field "foo" of the input table doesn’t have an integer type.

The align table operation is used to generate output samples that are evenly spaced in time. This is critical for combining data from more than one timeseries or table. Timeseries data is usually sampled on an approximately regular interval, but almost never exactly so. The align operation can be used to generate exactly-spaced samples. This allows points from one timeseries to be confidently matched up with those of another, letting us perform grouping, aggregation, or other multi-timeseries operations.

The align table operation takes one argument, which is the alignment method. Currently the only supported method is mean_within, which computes an average of the input timeseries over the provided duration. For example:

will compute the number of bytes sent on a data link over the last one hour, averaged in five minute windows. The output samples will be spaced exactly five minutes apart.

The alignment method also specifies what it means for an input sample to "overlap" with the output period. For the mean_within method, any input timeseries that has any part of its sample period within the output period is considered to overlap, with the amount of overlap determining the contribution of that value to the output. This effectively prorates each point by the amount its time range overlaps with the output window.

For example, suppose there is an input sample over the range [0.1s, 1.1s], and the output period is exactly [0.0s, 1.0s]. Then the input sample will contribute its value weighted by 0.8, since 80% of its interval overlaps with the output window. That is, the input is linearly weighted in the output, where the weight is the amount of its interval which overlaps with the output period.

If there are zero data points in an output period, then a missing value is produced in that period, represented by the JSON value null in the output data array.

The group_by table operation can be used to group more than one timeseries in a table together. One specifies a list of field names on which to group, and optional a method for reducing all the points within a group.

Note that the input timeseries must be aligned first, so that samples from the input timeseries can be correlated across time. An error is generated if the inputs are not aligned. Example:

The above query will compute the average number of bytes received on each sled’s NICs, within one minute periods, and then group by the sled’s UUID. This will remove all the other fields, leaving only the sled_id field. Within each group, all data points at the same timestamp will be summed to generate the output result. In other words, this computes the total number of bytes received for all links on a compute sled.

In contrast, the query:

Will compute the average number of bytes received in each group.

Grouping is one way to merge data from multiple timeseries. However, one may also wish to correlate data from more than one timeseries over time. For example, one might want to know how many bytes are sent or received for a given sled.

One way to do this is the join table operation, which combines two or more tables together. For example:

This query will first retrieve data for both the bytes sent and bytes received tables. They will separately be aligned to one minute periods by averaging, since the align operation takes any number of tables as input, and produces the same number as its output.

Finally the join table operation (which takes no arguments), performs a natural inner join one two or more tables. This merges data from timeseries in each table with the exact same values for their fields. It combines data points by concatenating those values which occur at the same timestamp.

For example, the query:

Will produce data at 20s intervals, each with two values for the point: the first will give the average number of bytes sent in that interval, and the second the average bytes received. For example, entries might look like this:

The first and last table operations are used to limit the number of data points from each query. They both take an unsigned integer argument k, limiting the output to at most k samples, independently for each timeseries in each table.

For example:

will select the timeseries from the sled_data_link:bytes_sent table corresponding to the physical data links, and take the very last sample from each. The output might look like this:

Select the last sample from all timeseries in the sled_data_link:bytes_sent table. See this section for more details.

Select the last 5 minutes of data from all timeseries in the sled_data_link:bytes_sent table. See the section on the filter table operation and the section on timestamps.

Average all timeseries in the sled_data_link:bytes_sent table into non-overlapping 5 minute time windows. See the section on the align table operation.

Average all timeseries in the sled_data_link:bytes_sent table into non-overlapping 5 minute time windows, and also average all timeseries with the same value for the sled_id field. See the section on the group_by table operation.

Average all timeseries independently from the sled_data_link:bytes_sent and sled_data_link:bytes_received tables into 5 minute windows, and then concatenate the samples from timeseries with the same field values, at the same timestamps. See the section on the join table operation.