> AI agents: this is one page from PostHog's docs. Full index of Markdown docs for LLMs: https://posthog.com/llms.txt

# Writing HogQL queries in Python - Handbook

> ❗️ This guide is intended only for development of PostHog itself. If you're looking for documentation on writing HogQL (or SQL) queries, go to the [SQL](https://posthog.com/docs/sql) docs.

HogQL is our layer on top of ClickHouse SQL which provides nice features such as:

-   Automatic person/group/etc property joins depending on the team/context
-   Customisable database schema per team
-   Flexible AST-powered templating language for building queries.

## Query templates

HogQL queries are built up from AST (Abstract Syntax Tree) nodes.

You can build the nodes yourself, or use the helpers `parse_expr` and `parse_select` to convert HogQL strings into AST nodes:

py

```py
from posthog.hogql import ast
from posthog.hogql.query import execute_hogql_query
from posthog.hogql.parser import parse_expr, parse_select

num_last_days = 2

stmt = parse_select(
    "select event, timestamp from events where {where} limit 100",
    {
        'where': parse_expr(
            'timestamp > interval {days} days',
            { 'days': ast.Constant(value=num_last_days) }
        )
    }
)

query_result = execute_hogql_query(query=stmt, team=team, query_type="used in logs")
query_result.results == [...]
query_result.columns == ['event', 'timestamp']  # might be useful if you select '*'
```

Few things to note:

-   `parse_select` parses full `SELECT` queries while `parse_expr` parses any expression (`1+1` or `event` or even a subquery `(select 1)`). It's not possible to parse parts of a select query, such as `limit 10`.
-   Placeholders like `{where}` are just nodes of type `ast.Placeholder(field='where')`. You can leave them in, and call `stmt = replace_placeholders(stmt, { where: parse_expr('1') })` later.
-   We wrote one AST node ourselves: `ast.Constant(value=num_last_days)`. We did it to sanitize the value by make sure it's treated as a constant. We might simplify constants further (e.g. `parse_const` or just `{days: 2}`), but we're not there yet.

Placeholder expansion allows at most 1,000 placeholders and shares a five-second deadline across the query. All placeholders also share a 64 MiB budget in Hog VM memory units, charged using each expression's peak stack usage, including temporary values. This accounting is not a limit on Python process memory. The `range()` builtin checks its result size against the remaining VM allowance before allocating the list. Queries that exceed these limits fail during expansion; reduce the number or size of the placeholder expressions to stay within them.

## Pattern matching during query preparation

Expressions inside HogQL placeholders execute in the Python HogVM. Its regex and LIKE functions and operators accept patterns up to 16,384 characters. Larger patterns raise a `HogVMException`; shorten the pattern before matching. Subject strings have no separate matching limit and use the VM's existing 64 MiB stack memory budget, so matching can process multi-megabyte response bodies. These limits also apply to `extractRegex`, which still returns an empty string for invalid regex syntax. Regex matching uses RE2 syntax, so backreferences and lookaround are unsupported.

SQL LIKE and ILIKE patterns sent to ClickHouse are not subject to these VM limits. For non-nullable materialized columns, patterns above 16,384 characters skip the optional sentinel-based rewrite and use the normal property read.

## ClickHouse query errors

`posthog/errors.py` maps ClickHouse error codes to exceptions. Query APIs expose `ExposedCHQueryError` messages and hide `InternalCHQueryError` messages. For additional user-correctable errors, set `ErrorCodeMeta.user_safe` to a fixed explanation with a next step. Parsing, array, regular expression, scalar subquery, JOIN, and LIMIT errors use these explanations where raw details cannot be exposed safely. Unrecognized error codes stay internal.

Do not assume an error code makes its raw message safe. ClickHouse can append expressions and query context after the initial exception. Those messages can contain storage credentials, signed URLs, settings, or source data values, including values that a shared insight does not otherwise reveal. Check both the throw sites and the exception enrichment paths before allowing raw text. Keep importable exception classes when adding a fixed explanation, and test the wrapped message as well as its string representation.

## AST nodes

If you want more control, you can build the AST nodes directly. The same query above can be written as:

py

```py
from posthog.hogql import ast
from posthog.hogql.query import execute_hogql_query
from posthog.hogql.parser import parse_expr

num_last_days = 2

stmt = ast.SelectQuery(
    select=[ast.Field(chain="event"), ast.Field(chain="timestamp")],
    select_from=ast.JoinExpr(table=ast.Field(chain=["events"])),
    where=parse_expr(
        "timestamp > interval {days} day",
        { 'days': ast.Constant(value=num_last_days) }
    ),
    limit=ast.Constant(value=100),
)

query_result = execute_hogql_query(query=stmt, team=team, query_type="used in logs")
query_result.results == [...]
query_result.columns == ['event', 'timestamp']  # might be useful if you select '*'
```

You can mix and match `parse_expr` and `ast` nodes as you please. The example above *still* took a shortcut for the where clause because it was easier to write.

## Snowflake date formatting

For direct Snowflake queries, `formatDateTime` requires a literal format string and translates supported strftime specifiers into Snowflake format elements. The printer binds the translated format as a query parameter, preserving literal quotes and backslashes in the parameter value. Pass the printed SQL and `HogQLContext.values` together to the database driver.

## Database schema and features

The HogQL database schema is in flux. You will soon be able to explore it in the [PostHog app itself](https://github.com/PostHog/posthog/pull/14591).

The most up to date resource is [hogql/database.py](https://github.com/PostHog/posthog/blob/master/posthog/hogql/database.py) on Github. At the time of writing, these tables were available:

Python

```python
class Database(BaseModel):
    # Users can query from the tables below
    events: EventsTable = EventsTable()
    persons: PersonsTable = PersonsTable()
    person_distinct_ids: PersonDistinctIdTable = PersonDistinctIdTable()
    session_recording_events: SessionRecordingEvents = SessionRecordingEvents()
    cohort_people: CohortPeople = CohortPeople()
    static_cohort_people: StaticCohortPeople = StaticCohortPeople()
```

Some tables have some fields that are actually "lazy tables". When accessed they will add a join to the table. The events table is such an example:

Python

```python
class EventsTable(Table):
    uuid: StringDatabaseField = StringDatabaseField(name="uuid")
    event: StringDatabaseField = StringDatabaseField(name="event")
    properties: StringJSONDatabaseField = StringJSONDatabaseField(name="properties")
    timestamp: DateTimeDatabaseField = DateTimeDatabaseField(name="timestamp")
    team_id: IntegerDatabaseField = IntegerDatabaseField(name="team_id")
    distinct_id: StringDatabaseField = StringDatabaseField(name="distinct_id")
    elements_chain: StringDatabaseField = StringDatabaseField(name="elements_chain")
    created_at: DateTimeDatabaseField = DateTimeDatabaseField(name="created_at")

    # lazy join that joins in the person_distinct_ids table when accessed. The join is described
    # as plain data: a resolver tag naming a join recipe in `lazy_join_registry.RESOLVERS`.
    pdi: LazyJoin = LazyJoin(
        from_field=["distinct_id"], join_table=PersonDistinctIdsTable(), resolver=PERSON_DISTINCT_IDS
    )
    # person fields on the event itself
    poe: EventsPersonSubTable = EventsPersonSubTable()

    # These are swapped out if the user has PoE enabled
    person: FieldTraverser = FieldTraverser(chain=["pdi", "person"])
    person_id: FieldTraverser = FieldTraverser(chain=["pdi", "person_id"])
```

If you access `pdi.person.properties.$browser`, we make a join via `persons` (this is a HogQL table name, not ClickHouse name). We do a bunch of `argmax` magic in the join, and inline all accessed properties within the subquery for performance. For the user, it looks just like simple property access.

If you access `poe.properties.$browser`, we will actually access the field `person_properties` on the events table.

In practice, you should avoid both and access `person.properties.$browser`, which will choose the right approach for you.

On the native JSON events table, HogQL compares non-empty string constants directly with declared String paths and casts Dynamic paths to Nullable(String) for properties with String or unknown definition types. This keeps equality and membership predicates visible to the JSON skip index without failing on mixed scalar types. Numeric, Boolean and DateTime property definitions keep their conversions when compared with strings, for both event and person properties. Boolean conversion reads the native path directly because object and array text cannot match `true` or `false`; numeric conversions already read native paths directly. Constants starting with `[` or `{` keep the full property read because arrays and objects serialize differently from scalars. Single-property reads serialize sub-objects with ClickHouse's JSON formatter; whole-document reads still remove declared defaults. Raw SQL property readers preserve backslashes before forward slashes whether ClickHouse's `output_format_json_escape_forward_slashes` setting is enabled or disabled. Native queries disable forward-slash escaping so object and array text retains `/`.

Add new tables and fields as needed! Just make sure each table has a `team_id` column.

Internal marketing queries can read cached session dimensions from `posthog.web_sessions_dimensional_preaggregated`. Rows include a precompute job ID and the person ID at computation time; readers must resolve current identities separately.

### Was this page useful?

HelpfulCould be better