Writing queries using Pipelined Query Language

PQL is a pipelined query language designed for writing readable queries that compile to SQL. Dashboard queries use the same underlying metrics tables and query variables as SQL queries.

Getting started

Open Dashboards and create a blank dashboard. Add a chart and switch the query editor to PQL.

Start your query with {{source}} to query data from the source selected in the UI, then pipe operators using |.

A minimal PQL query looks like this:

{{source}}
  | take 10

Query variables

You can use the same query variables you already use in ClickHouse SQL dashboard queries, such as {{source}}, {{time}}, {{start_time}}, and {{end_time}}. Variables are expanded before the query runs, so you can use them anywhere PQL expects a table name or expression.

PQL basics

PQL passes the output of each step into the next step:

{{source}}
  | where level == "error"
  | take 50

Comparisons and boolean logic

PQL follows Kusto style comparisons:

• equality uses ==
• not equal uses !=

Combine conditions with the and and or operators.

Supported operators and functions

PQL currently supports these operators: as, count, extend, join, project, sort, summarize, take (alias limit), top, where (alias filter).

PQL includes a small set of built in scalar and aggregation functions, including count(), countif(), iff(), isnull(), isnotnull(), not(), now(), strcat(), tolower(), and toupper().

For all operators and functions, see the PQL documentation.

Using ClickHouse functions

If you call a function that PQL does not know about, PQL will pass it through to the underlying database. That means ClickHouse functions work, but typos or unsupported functions will surface as database errors.

Example using a ClickHouse function from the PQL ClickHouse guide:

users
  | project user_email
  | where match(user_email, "@gmail.com")

Dashboard query example

Query log metrics for log counts grouped by time and log level

{{source}}
  | summarize value = countMerge(events_count) by time = {{time}}, series = %{series_column}
  | where time >= {{start_time}} and time <= {{end_time}}  

Explore logs query example

Query logs for log counts grouped by time and log level

{{source}}
  | project time = {{time}}, level = COALESCE(JSONExtractCaseInsensitive(raw, 'level', 'Nullable(String)'), 'null')
  | where time >= {{start_time}} and time <= {{end_time}}
  | summarize value = count() / {{sampling}} by time, level