PerfettoSQL Pipe Syntax Reference

This page is the language syntax and semantics reference for PerfettoSQL pipelines.

Warning: Pipe syntax is experimental and unstable. Do not rely on it in dashboards, scripts, or production queries. Anything can and will break without notice. Outside the PerfettoSQL standard library, enable it on a connection with PERFETTO PRAGMA pipelines = 1;.

`PERFETTO PRAGMA pipelines`

Controls whether pipe syntax is permitted on the current connection outside the standard library:

PERFETTO PRAGMA pipelines = 1;  -- Enable pipelines
PERFETTO PRAGMA pipelines = 0;  -- Disable pipelines

The right-hand side must evaluate to a whole number. Unknown pragma names produce an error.

Top-level forms

A pipeline is valid in two statement positions:

-- 1. Standalone query
<pipeline>;

-- 2. Materialized Perfetto table
CREATE [OR REPLACE] PERFETTO TABLE <table_name> [(<schema>)] AS
<pipeline>;

A <pipeline> consists of a source, either FROM or INTERVAL INTERSECTION OF, followed by zero or more |> stages:

<source>
[|> <stage_1>]
[|> <stage_2>]
...

Note: The pipe operator |> must be written with no whitespace between | and >. The pipeline keywords TREE, ACCUMULATE, UP, DOWN, INTERVAL, INTERSECTION, PER, and EXTEND are non-reserved and remain valid as normal identifiers in standard SQL statements.

Pipeline Sources

`FROM`

Starts a pipeline from a single relation:

FROM [<schema>.]<table_name> [[AS] <alias>]
FROM (<select_stmt>) [[AS] <alias>]

<table_name> may be any table or view, or a macro invocation that expands to a table or parenthesized subquery.
(<select_stmt>) may be any valid SQL SELECT statement enclosed in parentheses.
Table qualifier:
- If <alias> is given, columns of the source can be qualified with <alias>.<col>. When the source is a table name, <alias> replaces <table_name> as the qualifier.
- If the source is an unaliased <table_name>, columns can be qualified with <table_name>.<col>.
- If the source is an unaliased (<select_stmt>), its columns have no table qualifier unless a later |> AS <alias> stage binds one.

`INTERVAL INTERSECTION OF`

Starts a pipeline with the overlapping time regions across two or more relations:

INTERVAL INTERSECTION OF (
  <source_1> [AS] <alias_1>,
  <source_2> [AS] <alias_2>
  [, <source_N> [AS] <alias_N> ...]
) [PER <col_1> [, <col_2> ...]]

Requirements

At least two operand sources must be listed.
Every operand must have a distinct alias. An unaliased table name acts as its own alias; a parenthesized subquery must specify [AS] <alias>.
Every operand must contain ts and dur columns, and every column listed in PER must exist in every operand.

Output columns and scoping

For each overlapping region [ts, ts + dur) shared by all operands within a matching PER key group, one row is produced with the following columns:

ts and dur: 64-bit integer columns holding the intersected region's start and duration. These are the only ts and dur columns visible to unqualified column lookups.
Each operand's columns in operand order:
- The first operand's PER key columns are visible both unqualified, such as cpu, and qualified, such as <alias_1>.cpu.
- All other operand columns are qualified-only. This includes each operand‘s original ts and dur, as well as subsequent operands’ copies of PER columns. They can only be referenced via <alias_i>.<col> or expanded via * or <alias_i>.*.

Interval semantics

Half-open bounds: Each interval is [ts, ts + dur). Two intervals touching at their endpoints, such as [0, 10) and [10, 20), do not intersect.
Zero-duration points: A row with dur = 0 is a point at instant ts. It intersects an interval [a, b) when a <= ts < b, and intersects another point when both sit at the same instant ts, producing a region with dur = 0.
Missing bounds: A row with ts IS NULL or dur IS NULL covers no time and is skipped.
Negative bounds: A row with ts < 0 or dur < 0 fails with the error ts or dur is below zero.
NULL in PER columns: Two rows with NULL in a PER column agree on that column, matching GROUP BY semantics.

Source column naming rules

Every column of any pipeline source must have a valid identifier name matching ^[a-zA-Z_][a-zA-Z0-9_]*$ and must be distinct within that source, even if a later stage drops the column:

Unaliased SQL expressions like 1 + 1 or max(ts) are rejected with: expected every column to have a valid name, but '<expr>' is not one: give it one with AS.
Duplicate column names in a source are rejected with: expected distinct column names, but there are two named '<name>'.

Pipeline Stages

`TREE ACCUMULATE`

Accumulates sums up or down a tree defined by id and parent_id:

|> TREE ACCUMULATE UP|DOWN
  SUM([<qualifier>.]<col_1>) AS <out_1>
  [, SUM([<qualifier>.]<col_2>) AS <out_2> ...]

Tree structure columns: Resolves bare id and parent_id in the current row. Root nodes have parent_id IS NULL.
Directions:
- UP: Bottom-up fold. Each node's aggregate is the sum of its own value and the values of all its descendants. Output rows are emitted child-first.
- DOWN: Top-down fold. Each node's aggregate is the sum of its own value and the values of all its ancestors. Output rows are emitted parent-first.
Aggregate expressions:
- Only SUM(<column_ref>) is currently supported. DISTINCT, FILTER, OVER, *, non-column expressions, and other aggregate functions are rejected.
- The target column must be an integer type: ID, UINT32, INT32, or LONG. NULL values contribute 0.
- All aggregates in a single stage resolve their input columns against the row before the stage. A stage cannot reference an output column defined in the same stage.
Output: Keeps all columns of the input row and existing table aliases, and appends <out_1>, <out_2>, ... as LONG columns.

`SELECT`

Replaces the current row with the specified items and clears all table aliases:

|> SELECT <select_item> [, <select_item> ...]

Each <select_item> is one of:

Column reference: [<qualifier>.]<col> [[AS] <alias>]
Star expansion: [<qualifier>.]* [EXCEPT (<col_1> [, <col_2> ...])] [REPLACE (<replace_item> [, ...])] where each <replace_item> is [<qualifier>.]<source_col> AS <target_col>. AS is required in REPLACE.

Star expansion rules:

* starts with all columns in the current row, including qualified-only operand columns after INTERVAL INTERSECTION OF. <qualifier>.* starts with the columns captured when <qualifier> was bound.
EXCEPT (<col>, ...) removes every column matching any listed bare name case-insensitively. Every listed name must match at least one column, no name may be listed twice, and at least one column must remain after EXCEPT.
REPLACE (<source_col> AS <target_col>, ...) resolves <source_col> against the input row or aliases before the stage and replaces the value of <target_col> in the star's expansion while keeping <target_col>'s position and name. <target_col> must match a single column remaining in the star after EXCEPT.
All columns produced by * or <qualifier>.* can be referenced without a qualifier in subsequent stages.

`EXTEND`

Appends columns to the end of the current row while keeping all existing columns and aliases:

|> EXTEND <extend_item> [, <extend_item> ...]

Accepts the same items as SELECT, except that a bare * is not allowed. Use <qualifier>.* to append all columns of a table alias.
All items in EXTEND resolve against the row before the stage, so an item cannot reference another column added in the same EXTEND stage.
Columns added by EXTEND are not added to any existing table aliases.

`DROP`

Removes columns from the current row by bare name:

|> DROP <col_1> [, <col_2> ...]

Every listed name must match at least one unqualified-accessible column in the current row, and removes all columns of that name from the bare row.
No name may be listed twice, and at least one column must remain in the row after DROP.
Existing table aliases keep the dropped columns, except that any table alias whose name matches a dropped column name is removed.

`RENAME`

Renames columns in the current row in place:

|> RENAME <old_1> [AS] <new_1> [, <old_2> [AS] <new_2> ...]

<old_i> must be a bare column name that resolves unambiguously in the current row, and may not be listed twice in the same RENAME.
All renames in a single stage take effect at the same time, so |> RENAME x AS y, y AS x swaps x and y.
Renaming a column to an existing column's name is allowed. This creates duplicate column names in the row, which only error if a later stage references the ambiguous name.
Existing table aliases are unaffected and continue to expose the column under its old name.

`SET`

Replaces the values of existing columns in the current row in place:

|> SET <target_1> = [<qualifier>.]<source_1> [, <target_2> = [<qualifier>.]<source_2> ...]

<target_i> must be a bare column name that resolves unambiguously in the current row, and may not be listed twice in the same SET.
Every <source_i> is resolved against the row and aliases before the stage, so |> SET x = y, y = x swaps the values of x and y.
Existing table aliases keep the pre-SET values, except that any table alias whose name matches <target_i> is removed.

`AS`

Replaces all existing table aliases with a single new alias bound to a snapshot of the current row:

|> AS <alias>

After |> AS u, earlier table aliases are no longer in scope, and u.<col> and u.* reflect the row as of the AS stage.

Name Resolution and Ambiguity Rules

Case insensitivity: Column names and table aliases are matched case-insensitively using ASCII folding, while keeping the casing of the defining column or alias in output schemas.
Duplicate column names: A stage is allowed to create duplicate column names in the row, such as |> EXTEND x or |> RENAME x AS y when y already exists. Duplicate names only cause an error if a later stage references the ambiguous name without a table qualifier. You can also drop or exclude all copies at once via DROP y or * EXCEPT (y).
Quoted identifiers: Identifiers may be quoted with "...", `...`, or [...]. Inside "..." and `...`, doubling the quote character escapes a literal quote, and "" spells the empty column name.

PerfettoSQL Pipe Syntax Reference

Pragma and Statement Forms

PERFETTO PRAGMA pipelines