docs/analysis/perfetto-sql-syntax.md

# PerfettoSQL Syntax
*This page documents the syntax of PerfettoSQL, a dialect of SQL used in trace
processor and other Perfetto analysis tools to query traces.*

PerfettoSQL is a direct descendent of the
[dialect of SQL implemented by SQLite](https://www.sqlite.org/lang.html).
Specifically, any SQL valid in SQLite is also valid in PerfettoSQL.

Unfortunately, the SQLite syntax alone is not sufficient for two reasons:
1. It is quite basic e.g. it does not support creating functions or macros
2. It cannot be used to access features which are only available in Perfetto
tooling e.g. it cannot be used to create efficient analytic tables, import
modules from the PerfettoSQL standard library etc.

For this reason, PerfettoSQL adds new pieces of syntax which make the experience
of writing SQL queries better. All such additions include the keyword `PERFETTO`
to make it clear that they are PerfettoSQL-only.

<!-- TODO(b/290185551): we should really talk about our "recommendations" (e.g.
using CREATE PERFETTO TABLE instead of CREATE TABLE) somewhere and reference it
here. -->

## Including PerfettoSQL modules
`INCLUDE PERFETTO MODULE` is used to import all tables/views/functions/macros
defined in a PerfettoSQL module (e.g. from the
[PerfettoSQL standard library](/docs/analysis/stdlib-docs.autogen)).

Note that this statement acts more similar to `#include` statements in C++
rather than `import` statements from Java/Python. Specifically, all objects
in the module become available in the global namespace without being qualified
by the module name.

Example:

```sql
-- Include all tables/views/functions from the android.startup.startups module
-- in the standard library.
INCLUDE PERFETTO MODULE android.startup.startups;

-- Use the android_startups table defined in the android.startup.startups
-- module.
SELECT *
FROM android_startups;
```

For interactive development, the key can contain a wildcards:
```sql
-- Include all modules under android/.
INCLUDE PERFETTO MODULE android.*;

-- Or all stdlib modules:
INCLUDE PERFETTO MODULE *;

-- However, note, that both patterns are not allowed in stdlib.
```

## Types

PerfettoSQL supports the following types, which can be used in table and view
schemas, function arguments and return types:

| Type | Description |
|------|-------------|
| `LONG` | 64-bit signed integer |
| `DOUBLE` | Double precision floating-point number |
| `BOOL` | Boolean value (true/false) |
| `STRING` | Text string |
| `BYTES` | Binary data |
| `TIMESTAMP` | Absolute timestamp in nanoseconds |
| `DURATION` | Time duration in nanoseconds |
| `ARGSETID` | An identifier for a set of arguments. This set can be obtained by joining with an `args` table on `arg_set_id` column. |
| `ID` | An ID column for this table. Each table can have only one ID column, whose values should be unique and fit into `uint32`. |
| `JOINID(table.column)` | A foreign key reference into a given table. `table` should exist, should have column named `column` of type `ID`. |
| `ID(table.column)` | A variant of the `ID` type, which is both primary key for this table and simultaneously is a foreign key reference into another table. Useful when a given table is based on a subset of rows from another table (e.g. `slice`). |

## Defining functions
`CREATE PERFETTO FUNCTION` allows functions to be defined in SQL, which can be
either scalar (returning a single value) or table-value (returning a set of rows).
The syntax is similar to the syntax in PostgreSQL or GoogleSQL:
- Scalar: `CREATE PERFETTO FUNCTION function_name(arg_list) RETURNS return_type AS sql_select_statement;`
- Table-valued: `CREATE PERFETTO FUNCTION function_name(arg_list) RETURNS TABLE(column_list) AS sql_select_statement;`

`arg_list` and `column_list` is a comma-separated list of an arbitrary number `argument_name argument_type` pairs.

`sql_select_statement` should be a valid SQL statement, which can use `$argument_name` syntax to refer to the argument values.

Examples:
```sql
-- Create a scalar function with no arguments.
CREATE PERFETTO FUNCTION constant_fn() RETURNS LONG AS SELECT 1;

-- Create a scalar function taking two arguments.
CREATE PERFETTO FUNCTION add(x LONG, y LONG) RETURNS LONG AS SELECT $x + $y;

-- Create a table function with no arguments
CREATE PERFETTO FUNCTION constant_tab_fn()
RETURNS TABLE(ts LONG, dur LONG) AS
SELECT column1 as ts, column2 as dur
FROM (
  VALUES
  (100, 10),
  (200, 20)
);

-- Create a table function with one argument
CREATE PERFETTO FUNCTION sched_by_utid(utid LONG)
RETURNS TABLE(ts LONG, dur LONG, utid LONG) AS
SELECT ts, dur, utid
FROM sched
WHERE utid = $utid;
```

## Creating efficient tables
`CREATE PERFETTO TABLE` allows defining tables optimized for analytic queries
on traces. These tables are both more performant and more memory efficient than
SQLite native tables created with `CREATE TABLE`.

Note however the full feature set of `CREATE TABLE` is not supported:
1. Perfetto tables cannot be inserted into and are read-only after creation
2. Perfetto tables must be defined and populated using a `SELECT` statement.
  They cannot be defined by column names and types.

Example:

```sql
-- Create a Perfetto table with constant values.
CREATE PERFETTO TABLE constant_table AS
SELECT column1 as ts, column2 as dur
FROM (
  VALUES
  (100, 10),
  (200, 20)
);

-- Create a Perfetto table with a query on another table.
CREATE PERFETTO TABLE slice_sub_table AS
SELECT *
FROM slice
WHERE name = 'foo';
```

### Schema

Perfetto tables can have an optional explicit schema. The schema syntax is the
same as the function argument or returned-from-a-function table,
i.e. a comma-separated list of (column name, column type) pairs in parenthesis
after table or view name.

```sql
CREATE PERFETTO TABLE foo(x LONG, y STRING) AS
SELECT 1 as x, 'test' as y
```

### Index

`CREATE PERFETTO INDEX` lets you create indexes on Perfetto tables, similar to
how you create indexes in SQLite databases. These indexes are built on specific
columns, and Perfetto internally maintains these columns in a sorted order.
This means operations benefiting from sorting on an indexed column (or group of
columns) will be significantly faster, as if you were operating on a column
that's already sorted.

NOTE: Indexes have non-trivial memory cost, so it's important to only use them
when there is a need for performance improvement.

NOTE: Indexes will be used by views created on the indexed table, but they will
not be inherited by any child tables, as shown in the below SQL.

NOTE: If the query filters/joins on `id` column of the table (one that is a
primary key of the table) there is no need to add a Perfetto index, as Perfetto
tables already have special performance optimizations for operations that can
benefit from sorting.

Example of usage:
```sql
CREATE PERFETTO TABLE foo AS
SELECT * FROM slice;

-- Creates and stores an index `foo_track` on column `track_id` of table foo.
CREATE PERFETTO INDEX foo_track ON foo(track_id);
-- Creates or replaces an index created on two columns. It will be used for
-- operations on `track_id` and can be used on operations on `name` only if
-- there has been an equality constraint on `track_id` too.
CREATE OR REPLACE PERFETTO INDEX foo_track_and_name ON foo(track_id, name);
```

The performance of those two queries should be very different now:
```sql
-- This doesn't have an index so it will have to linearly scan whole column.
SELECT * FROM slice WHERE track_id = 10 AND name > "b";

-- This has an index and can use binary search.
SELECT * FROM foo WHERE track_id = 10 AND name > "b";

-- The biggest difference should be noticeable on joins:
-- This join:
SELECT * FROM slice JOIN track WHERE slice.track_id = track.id;
-- will be noticeably slower than this:
SELECT * FROM foo JOIN track WHERE slice.track_id = track.id;
```

Indexes can be dropped:
```sql
DROP PERFETTO INDEX foo_track ON foo;
```


## Creating views with a schema

Views can be created via `CREATE PERFETTO VIEW`, taking an optional schema.
With the exception of the schema, they behave exactly the same as regular
SQLite views.

NOTE: the use of `CREATE PERFETTO VIEW` instead of `CREATE VIEW` is required in
the standard library where each column must be documented.

```sql
CREATE PERFETTO VIEW foo(x LONG, y STRING) AS
SELECT 1 as x, 'test' as y
```

## Defining macros
`CREATE PERFETTO MACRO` allows macros to be defined in SQL. The design of macros
is inspired by the macros in Rust.

The following are recommended uses of macros:
- Passing tables as arguments to a "function-like" snippet of SQL.
- Defining simple constants for performance-sensitive queries.

Macros are powerful but also dangerous if used incorrectly, making debugging
extremely difficult. For this reason, it's recommended that they are used
sparingly when they are needed and only for the recommended uses described
above.

If only passing around scalar SQL values, functions are generally preferred
for their clarity. However, for simple constants used many times in a
performance-sensitive query, a macro can be more efficient as it avoids the
potential overhead of function calls in a large number.

NOTE: Macros are expanded with a pre-processing step *before* any execution
happens. Expansion is a purely syntactic operation involves replacing the macro
invocation with the SQL tokens in the macro definition.

As macros are syntactic, the types of arguments and return types in macros are
different to the types used in functions and correspond to parts of the SQL
parse tree. The following are the supported types:

| Type name         | Description                                       |
| ---------         | -----------                                       |
| `Expr`            | Corresponds to any SQL scalar expression.         |
| `TableOrSubquery` | Corresponds to either an SQL table or a subquery  |
| `ColumnName`      | Corresponds to a column name of a table           |

Example:

```sql
-- Create a macro taking no arguments. Note how the returned SQL fragment needs
-- to be wrapped in brackets to make it a valid SQL expression.
--
-- Note: this is a strongly discouraged use of macros as a simple SQL
-- function would also work here.
CREATE PERFETTO MACRO constant_macro() RETURNS Expr AS (SELECT 1);

-- Using the above macro. Macros are invoked by suffixing their names with !.
-- This is similar to how macros are invoked in Rust.
SELECT constant_macro!();

-- This causes the following SQL to be actually executed:
-- SELECT (SELECT 1);

-- A variant of the above. Again, strongly discouraged.
CREATE PERFETTO MACRO constant_macro_no_bracket() RETURNS Expr AS 2;

-- Using the above macro.
SELECT constant_macro_no_bracket!();

-- This causes the following SQL to be actually executed:
-- SELECT 2;

-- Creating a macro taking a single scalar argument and returning a scalar.
-- Note: again this is a strongly discouraged use of macros as functions can
-- also do this.
CREATE PERFETTO MACRO single_arg_macro(x Expr) RETURNS Expr AS (SELECT $x);
SELECT constant_macro!() + single_arg_macro!(100);

-- Creating a macro taking both a table and a scalar expression as an argument
-- and returning a table. Note again how the returned SQL statement is wrapped
-- in brackets to make it a subquery. This allows it to be used anywhere a
-- table or subquery is allowed.
--
-- Note: if tables are reused multiple times, it's recommended that they be
-- "cached" with a common-table expression (CTE) for performance reasons.
CREATE PERFETTO MACRO multi_arg_macro(x TableOrSubquery, y Expr)
RETURNS TableOrSubquery AS
(
  SELECT input_tab.input_col + $y
  FROM $x AS input_tab;
)
```