Workers Analytics Engine SQL Reference

​​ SHOW TABLES statement

SHOW TABLES can be used to list the tables on your account. The table name is the name you specified as dataset when configuring the workers binding (refer to Get started with Workers Analytics Engine, for more information). The table is automatically created when you write event data in your worker.

Refer to FORMAT clause for the available FORMAT options.

​​ SHOW TIMEZONES statement

SHOW TIMEZONES can be used to list all of the timezones supported by the SQL API. Most common timezones are supported.

​​ SHOW TIMEZONE statement

SHOW TIMEZONE responds with the current default timezone in use by SQL API. This should always be Etc/UTC.

​​ SELECT statement

SELECT is used to query tables.

Usage:

Below you can find the syntax of each clause. Refer to the SQL API docs for some example queries.

​​ SELECT clause

The SELECT clause specifies the list of columns to be included in the result. Columns can be aliased using the AS keyword.

Usage:

Examples:

Additionally, expressions using supported functions and operators can be used in place of column names:

​​ FROM clause

FROM is used to specify the source of the data for the query.

Usage:

Examples:

Note that queries can only operate on a single table. UNION, JOIN etc. are not currently supported.

​​ WHERE clause

WHERE is used to filter the rows returned by a query.

Usage:

<condition> can be any expression that evaluates to a boolean.

Comparison operators can be used to compare values and boolean operators can be used to combine conditions.

Expressions containing functions and operators are supported.

Examples:

​​ GROUP BY clause

When using aggregate functions, GROUP BY specifies the groups over which the aggregation is run.

Usage:

For example. If you had a table of temperature readings:

In the usual case the <expression> can just be a column name but it is also possible to supply a complex expression here. Multiple expressions or column names can be supplied separated by commas.

​​ ORDER BY clause

ORDER BY can be used to control the order in which rows are returned.

Usage:

<expression> can just be a column name.

ASC or DESC determines if the ordering is ascending or descending. ASC is the default, and can be omitted.

Examples:

​​ LIMIT clause

LIMIT specifies a maximum number of rows to return.

Usage:

Supply the maximum number of rows to return or ALL for no restriction.

For example:

​​ FORMAT clause

FORMAT controls how to the returned data is encoded.

Usage:

If no format clause is included then the default format of JSON will be used.

Override the default by setting a format. For example:

The following formats are supported:

​​ JSON

Data is returned as a single JSON object with schema data included:

​​ JSONEachRow

Data is returned with a separate JSON object per row. Rows are newline separated and there is no header line or schema data:

​​ TabSeparated

Data is returned with newline separated rows. Columns are separated with tabs. There is no header.

​​ Supported functions

​​ count

Usage:

Count is an aggregation function that returns the number of rows in each group or results set.

Count can also be used to count the number of distinct (unique) values in each column:

Example:

​​ sum

Usage:

Sum is an aggregation function that returns the sum of column values across all rows in each group or results set. Sum also supports DISTINCT, but in this case it will only sum the unique values in the column.

Example:

​​ avg

Usage:

Avg is an aggregation function that returns the mean of column values across all rows in each group or results set. Avg also supports DISTINCT, but in this case it will only average the unique values in the column.

Example:

​​ min

Usage:

Min is an aggregation function that returns the minimum value of a column across all rows.

Example:

​​ max

Usage:

Max is an aggregation function that returns the maximum value of a column across all rows.

Example:

​​ quantileWeighted

Usage:

quantileWeighted is an aggregation function that returns the value at the q^th quantile in the named column across all rows in each group or results set. Each row will be weighted by the value in weight_column_name. Typically this would be _sample_interval (refer to how sampling works, for more information).

Example:

​​ if

Usage:

Returns <true_expression> if <condition> evaluates to true, else returns <false_expression>.

Example:

​​ intDiv

Usage:

Divide a by b, rounding the answer down to the nearest whole number.

​​ toUInt32

Usage:

Converts any numeric expression, or expression resulting in a string representation of a decimal, into an unsigned 32 bit integer.

Behaviour for negative numbers is undefined.

​​ length

Usage:

Returns the length of a string. This function is UTF-8 compatible.

Examples:

​​ isEmpty

Usage:

Returns a boolean saying whether the string was empty. This computation can also be done as a binary operation: {string} = ''.

Examples:

​​ toLower

Usage:

Returns the string converted to lowercase. This function is Unicode compatible. This may not be perfect for all languages and users with stringent needs, should do the operation in their own code.

Examples:

​​ toUpper

Usage:

Returns the string converted to uppercase. This function is Unicode compatible. The results may not be perfect for all languages and users with strict needs. These users should do the operation in their own code.

Examples:

​​ startsWith

Usage:

Returns a boolean of whether the first string has the second string at its start.

Examples:

​​ endsWith

Usage:

Returns a boolean of whether the first string contains the second string at its end.

Examples:

​​ position

Usage:

Returns the position of one string, needle, in another, haystack. In SQL, indexes are usually 1-based. That means that position returns 1 if your needle is at the start of the haystack. It only returns 0 if your string is not found.

Examples:

​​ substring

Usage:

Extracts part of a string, starting at the Unicode code point indicated by the offset and returning the number of code points requested by the length. As previously mentioned, in SQL, indexes are usually 1-based. That means that the offset provided to substring should be at least 1.

Examples:

​​ format

Usage:

This function supports formatting strings, integers, floats, datetimes, intervals, etc, except NULL. The function does not support literal { and } characters in the format string.

Examples:

See also: formatDateTime

​​ toDateTime

Usage:

toDateTime converts an expression to a datetime. This function does not support ISO 8601-style timezones; if your time is not in UTC then you must provide the timezone using the second optional argument.

Examples:

​​ now

Usage:

Returns the current time as a DateTime.

​​ toUnixTimestamp

Usage:

toUnixTimestamp converts a datetime into an integer unix timestamp.

Examples:

​​ formatDateTime

Usage:

formatDateTime prints a datetime as a string according to a provided format string. See ClickHouse’s docs Open external link for a list of supported formatting options.

Examples:

​​ toStartOfInterval

Usage:

toStartOfInterval rounds down a datetime to the nearest offset of a provided interval. This can be useful for grouping data into equal-sized time ranges.

Examples:

​​ extract

Usage:

extract returns an integer number of time units from a datetime. It supports YEAR, MONTH, DAY, HOUR, MINUTE and SECOND.

Examples:

​​ Supported operators

The following operators are supported:

​​ Arithmetic operators

​​ Comparison operators

We also support the BETWEEN operator for checking a value is in an inclusive range: a [NOT] BETWEEN b AND c.

​​ Boolean operators

​​ Unary operators

​​ Literals