HogQL

HogQL is our take on SQL (Structured Query Language), a language used to manage and access data. It is effectively a wrapper around ClickHouse SQL, with tweaks such as simplified event and person property access, null handling, and visualization integrations.

HogQL is currently in public beta. This means it's not yet a perfect experience, and the language itself may still change. Follow along with the development here.

HogQL expressions

HogQL expressions enable you to use database identifiers and functions to directly access, aggregate, filter, transform, and breakdown your data.

They can be used by selecting the "HogQL" tab or "HogQL expression" option in filters, breakdowns, dashboards, trends, funnels (aggregating by), user paths (event types), the activity tab (columns), and more.

For example, to group pageviews into "desktop" or "mobile," you can breakdown with the expression multiIf(properties.$os == 'Android', 'mobile', properties.$os == 'iOS', 'mobile', 'desktop')

SQL insights

SQL insights enable you to directly query your data with SQL commands like SELECT, FROM, JOIN, WHERE, GROUP BY along with many of ClickHouse SQL's function. This enables more complex and customizable queries and results than other insights.

For example, to get a count of the most popular first pageview current_url values, we can use an SQL query like this:

SELECT 
    properties.$current_url AS current_url,
    count() AS url_count
FROM events
WHERE event = '$pageview'
    AND (distinct_id, timestamp) IN (
        SELECT distinct_id, min(timestamp)
        FROM events
        WHERE event = '$pageview'
        GROUP BY distinct_id
    )
    AND {filters}
GROUP BY current_url
ORDER BY url_count DESC

You can use SQL insights within notebooks and with external sources using the data warehouse.

Query API

To query events using HogQL via the PostHog API, get your project ID, a personal API key with the project query read permission and make a POST request to /api/projects/:project_id/query endpoint with the following JSON payload:

{"query": {"kind": "HogQLQuery", "query": "select * from events"}}

For example, to get a count of the most common event values, you can make a request like this (change us.posthog.com to eu.posthog.com if you're on EU cloud):

curl -X POST "<ph_app_host>/api/projects/:project_id/query" \
     -H "Content-Type: application/json" \
     -H "Authorization: Bearer <personal_api_key>" \
     -d '{
            "query": {
              "kind": "HogQLQuery", 
              "query": "SELECT event, COUNT() FROM events GROUP BY event ORDER BY COUNT() DESC"
            }
         }'

The response is in the format:

export interface HogQLQueryResponse {
    /** The input query */
    query?: string
    /** An array of result arrays */
    results?: any[][]
    /** Returned column types */
    types?: string[]
    /** Returned column names/aliases */
    columns?: string[]
    /** Generated HogQL query with expressions inlined */
    hogql?: string
    /** Generated ClickHouse query for debugging */
    clickhouse?: string
}

While in the public beta, the response format may still change.

Will there be API pricing? The HogQL API is free to use while it's in the public beta and we work out the details. After we launch for real, we plan to charge a competitive rate for heavy usage. Stay tuned.

Data warehouse

To get a list of all the sources you can query with HogQL, enable the data warehouse beta and check out the "Data warehouse" tab. You can click on every table listed to see the data included and query them.

The data warehouse also enables you to add external sources, like Stripe and Hubspot, and query them alongside your PostHog data.