Writing HogQL queries in Python

Contents

❗️ 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 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
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.

AST nodes

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

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.

The most up to date resource is hogql/database.py on Github. At the time of writing, these tables were available:

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
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.

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?