HogQL & Data Exploration next steps

[mariusandra]

HogQL / Data Exploration TODO

These are the next steps to work on. Scroll below for old completed points.

Insight conversion

https://github.com/PostHog/meta/issues/130

HogQL

• [ ] Support dashboard filters https://github.com/PostHog/posthog/issues/15999
• [ ] Build a full type system, type all functions, fix a lot of null issues.
• [ ] Improve JSON support. Treat every string as a json field if accessed with a property. Extract array if passed string where array is expected.
• [ ] Date Units https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions
• [ ] HogVM action matching. It's done, but we should get it live. https://github.com/PostHog/posthog/pull/16189
• [ ] Make a decision on int & float properties (all "number" properties are presented the JSON number aka the SQL float now). Do we want "int" properties?
• [ ] Make a decision: move team_id guard into PREWHERE, or keep in WHERE
• [ ] Add all OrDefault aggregations
• [ ] Support empty strings '' and null strings 'null' in materialized properties.
• [ ] Connect the read only database user for HogQL queries
• [ ] Do we want to keep extracting macros from the WITH clause, or embrace CTE-s?
• [ ] Some other ClickHouse SQL grammar we need to support?
• [ ] Double check we can parse everything in the antlr grammar.
• [ ] Fuzzing testing - randomly generated allowed queries shouldn't cause exceptions and failures
• [ ] Recording and session filters
• [ ] Automatic benchmarks
• [ ] Query tables for other projects within an organization
• [ ] Performance boost: Transform table selects into subqueries https://github.com/PostHog/posthog/issues/15966
• [ ] Performance boost: Joined subquery AND optimisation (move persons AND filters into raw_persons subquery)
• [ ] Performance boost: https://github.com/PostHog/posthog/issues/21320
• [ ] Performance boost: https://github.com/PostHog/posthog/issues/21269
• [ ] Work out pricing for API usage

HogQL feature completeness

• [ ] https://github.com/PostHog/posthog/issues/20663
• [ ] https://github.com/PostHog/posthog/issues/20660
• [x] https://github.com/PostHog/posthog/issues/20661
• [ ] https://github.com/PostHog/posthog/issues/20662
• [ ] https://github.com/PostHog/posthog/issues/20667
• [x] https://github.com/PostHog/posthog/issues/20668
• [x] https://github.com/PostHog/posthog/issues/21469

Event explorer UX

• [ ] Why do we remove the http:// from the domains? (slack thread)
• [ ] Clicking on cells should filter by value (normal cell), or drill down further (aggregation number cell).
• [ ] "Load more" should also expand the time range when you're at the end of "last 24h".
• [ ] Finish the lemon data grid
• [ ] Implement UX feedback from https://github.com/PostHog/posthog/pull/13726#pullrequestreview-1251170166
• [ ] Sorting by "event" with autocapture is odd (table says "clicked on" not "$autocapture bla bla", so sort order makes it not clear)

Pivot tables

• [ ] Create a separate EventsPivotNode, source either HogQLQuery or EventsQuery
• [ ] Add buttons to the events table and BI UI to make a pivot table

Insights, Dashboards, Frontend

• [ ] Make <Query query={} /> something any other React app can import and use
• [ ] Make the Query tag work within sidebar apps too.
• [ ] Add throttling, caching and timeouts to dataNodeLogic, especially for dashboards.

---

Consolidated 2023-02-02

The lists above were getting long, so moved (2nd of Feb 2023) all the completed points below:

Events table

• [x] Add person and cohort tables @mariusandra
• [x] Get basic HogQL in @mariusandra
• [x] ~Aggregated node should nest the events node~ Make new EventsQuery node
• [x] Rework the Column Configurator to support formulas and aggregations while looking slick-ish.
• [x] Fix data table regressions @mariusandra
  • [x] Reported bug on slack
  • [x] Add pagination
  • [x] Restore date separator rows (and category rows if sorting by something else?)
  • [x] What to do with coalesce(url, screen) and filters?
• [x] Support HogQL filters in the interface (I mean editing the query node's where part in the UI, and not exactly this loosely related PR)
• [x] Add a date range selector on top of the table (defaults to 3 days if no aggregations, and 30 if with aggregations)
• [x] How to avoid too much load on our cluster? Users will be able to query for almost anything after all (all time sort by properties.bla).
• [x] Replace column header sort icons with dropdown menus, add filtering and sorting there.
• [x] Get query nodes into backend. Parse data from there.
• [x] Yank HogQL out of property filters and into where
• [x] Bring back saved columns Not happening, will save as insights instead.
• [x] Move events list query to posthog/queries/event_list.py and refactor.
  • [x] Create a property_filter_to_hogql abstraction
• [x] SQL errors into the frontend https://github.com/PostHog/posthog/pull/13703 (done in the hogql unleashed PR)

HogQL

• [x] Get basic HogQL expressions working for "select" and grouping
• [x] Get HogQL filtering working (including "having" and comparisons)
• [x] Figure out which clickhouse functions we want to enable (e.g. startOfDay), and which not (evalMLMethod).
• [x] Use HogQL expressions in property filters as a separate "sql expression" filter type
• [x] Create an interface to enter a HogQL expression in the app's UI
• [x] Support HogQL filters in all the filters and breakdowns
• [x] Add missing aggregations (count distinct property)
• [x] Fix problem with % breaking things due to python's string.format (moved vars to vars, but someone could still write a symbol that f"" interprets down the line. Make sure we combine the query in a clear way)
• [x] Add support for tracking person property types. @macobo
• [x] Create a technical/language document for HogQL, showing what is allowed and what isn't (we have the grammar)

Backend

• [x] Create a /query endpoint, and add a router on the backend. At first support just the new EventsQuery

Pivot tables

• [x] Map out the problem space on the query level
• [x] Add the relevant parameters to the data node queries and create a MVP

Insights, Dashboards, Frontend

• [x] Port over insights and their filters to use data exploration natively @thmsobrmlr (done, just cleaning up) https://github.com/PostHog/meta/issues/84
• [x] Port over dashboards to store data exploration queries @pauldambra
• [x] Get rid of antd @Twixes (done enough)

---

Consolidated 2023-06-06

This list was pasted on the 6th of June, 2023 after launching the HogQL public beta.

HogQL

Internal RFC

• [x] Python-based HogQL https://github.com/PostHog/posthog/pull/13203
• [x] HogQL filters in existing insights https://github.com/PostHog/posthog/pull/13264
• [x] Create an ANTLR based HogQL parser https://github.com/PostHog/posthog/pull/13971
  • [x] Merged
• [x] Templating/fragment system for query building https://github.com/PostHog/posthog/pull/14116
  • [x] And a bunch of other cleanups.
  • [x] Merged
• [x] SELECT statements https://github.com/PostHog/posthog/pull/14042
  • [x] Support most SELECT statements https://github.com/PostHog/posthog/pull/14131
  • [x] Add automatic where for team_id when making queries in ClickHouse
  • [x] Support name resolution, aliasing (e.g. select e.bla from events e), conflict avoidance https://github.com/PostHog/posthog/pull/14185
  • [x] Support * expansion on tables https://github.com/PostHog/posthog/pull/14271
  • [x] Intervals https://github.com/PostHog/posthog/pull/14370
  • [x] Virtual database schema that could be customised per team (if we choose to implement this)
  • [x] Merged
• [x] HogQLQuery node in /query https://github.com/PostHog/posthog/pull/14265
  • [x] Pretty print
  • [x] Flagged
  • [x] Merged
• [x] Person properties join
  • [x] As AST mangler for HogQL select statements
  • [x] Join person_distinct_id automatically https://github.com/PostHog/posthog/pull/14286
  • [x] Join persons automatically
  • [x] Still works with all the old stuff
  • [x] Clean code
  • [x] Merged
• [x] Events table built on top of HogQL https://github.com/PostHog/posthog/pull/14315
  • [x] Rename symbols to refs https://github.com/PostHog/posthog/pull/14410
  • [x] Property filters to HogQL
  • [x] Action steps to HogQL
  • [x] Merged
• [x] Finally launch HogQL expressions
  • [x] Basic documentation https://github.com/PostHog/posthog.com/pull/5416
  • [x] Remove autofocus https://github.com/PostHog/posthog/pull/14489
  • [x] Release flag
  • [x] Announce to users
  • [x] Remove flag https://github.com/PostHog/posthog/pull/14616
• [x] Launch events table via HogQL
  • [x] Fetch properties directly between shards https://github.com/PostHog/posthog/pull/14549
  • [x] Cohort filters https://github.com/PostHog/posthog/pull/14600
  • [x] Improve 5sec "load new results" interval https://github.com/PostHog/posthog/pull/14648
  • [x] Make it faster https://github.com/PostHog/posthog/pull/14648
  • [x] Launch to beta users
  • [x] Query cancellation at the dataNodeLogic level https://github.com/PostHog/posthog/pull/14662
• [x] Odds and ends
  • [x] UNION ALL https://github.com/PostHog/posthog/pull/14593
  • [x] Database schema (and PoE setting) per team https://github.com/PostHog/posthog/pull/14740
  • [x] Clean up the confusion with our bespoke countDistinct() function https://github.com/PostHog/posthog/pull/14786
  • [x] Database schema management view https://github.com/PostHog/posthog/pull/14591
  • [x] Automatic type conversion (e.g. toInt) for event and person properties, per team https://github.com/PostHog/posthog/pull/14795
  • [x] WITH and macros/expressions https://github.com/PostHog/posthog/pull/14495
• [x] #107
  • [x] HogQL/SQL insight https://github.com/PostHog/posthog/pull/14688
  • [x] HogQL on dashboards
  • [x] Rename /query to /debug https://github.com/PostHog/posthog/pull/14729
  • [x] Open as new insight https://github.com/PostHog/posthog/pull/14709
  • [x] Always add table names in front of all columns? https://github.com/PostHog/posthog/pull/14837
  • [x] Resource usage safeguards https://github.com/PostHog/posthog/pull/14870 https://github.com/PostHog/posthog/pull/14873
  • [x] Launch sql insights to beta users. https://github.com/PostHog/posthog/pull/14871
• [x] More odds and ends
  • [x] Add timezone support (pass zone to all toDateTime calls) https://github.com/PostHog/posthog/pull/14968
  • [x] JSON property access https://github.com/PostHog/posthog/pull/14976
  • [x] Expose more ClickHouse functions https://github.com/PostHog/posthog/pull/14969
  • [x] Really lazy tables https://github.com/PostHog/posthog/pull/14927
  • [x] Arrays https://github.com/PostHog/posthog/pull/14986
  • [x] Lambdas https://github.com/PostHog/posthog/pull/14987
  • [x] Get HogQL errors on insights out of sentry and into the interface https://github.com/PostHog/posthog/pull/15001
• [x] Beta development final stretch
  • [x] Rename "ref"-s to "type"-s https://github.com/PostHog/posthog/pull/15106
  • [x] Type classes https://github.com/PostHog/posthog/pull/15129
  • [x] Fix: rename toTimezone https://github.com/PostHog/posthog/pull/15127
  • [x] Fix: nested macros https://github.com/PostHog/posthog/pull/15126
  • [x] Tuple access https://github.com/PostHog/posthog/pull/15130
  • [x] More aggregations https://github.com/PostHog/posthog/pull/15131
  • [x] Window functions https://github.com/PostHog/posthog/pull/15132
  • [x] Merge resolver, macros, asterisks https://github.com/PostHog/posthog/pull/15175
  • [x] Support PoEv2 overrides table https://github.com/PostHog/posthog/pull/15120
  • [x] Null handling https://github.com/PostHog/posthog/pull/15244
  • [x] Group filters part 1 https://github.com/PostHog/posthog/pull/15231
  • [x] Trends HogQL volume https://github.com/PostHog/posthog/pull/15672
  • [x] ... and many other PRs
• [x] LAUNCH PUBLIC BETA
  • [x] Bring back event explorer saved columns https://github.com/PostHog/posthog/pull/15899
  • [x] Launch sql insights to all users.
  • [x] Launch events table to all users

Events table

• [x] Convert the events table to use HogQL internally (partially done)
• [x] Get the events table back up and running for beta users
• [x] Show reload button https://github.com/PostHog/posthog/pull/14785

Pivot tables

• [x] Implement Pivot tables MVP to prove it's doable
• [x] Implement pivot tables through HogQL

Consolidated 2023-12-07

HogQL

• [x] Post-beta shortlist
  • [x] Make errors more helpful
    • [x] Async Inline SQL expression validation https://github.com/PostHog/posthog/pull/15881
    • [x] Async full SQL select query validation https://github.com/PostHog/posthog/pull/15957
    • [x] Figure out which ClickHouse SQL errors we can expose to users https://github.com/PostHog/posthog/pull/15959
    • [x] Figure out which ClickHouse SQL errors (uncaught in the HogQL layer) happen most often. --> Will add as needed.
    • [x] Do we need to build a full type system to catch some things before they reach ClickHouse? --> no, but would be cool
  • [x] Write some docs for the /query API endpoint. Ideally autogenerated. https://github.com/PostHog/posthog.com/pull/6096
  • [x] String null handling for concat https://github.com/PostHog/posthog/pull/15975
  • [x] Make a decision on properties and NULLs (slack thread, older context) - going to use as many nulls as possible
  • [x] Null handling for == and != https://github.com/PostHog/posthog/pull/16259
  • [x] Nullish Coalesching https://github.com/PostHog/posthog/pull/16276
  • [x] Remove feature flag https://github.com/PostHog/posthog/pull/15936
  • [x] System of modifiers to swap performance optimisations in/out
• [x] Hotfixes requested by customers
  • [x] Timestamp in a subquery (slack thread) https://github.com/PostHog/posthog/pull/15970
  • [x] Fix empty list filter https://github.com/PostHog/posthog/pull/15971
  • [x] Array property access https://github.com/PostHog/posthog/pull/15965
  • [x] tumble doesn't work https://github.com/PostHog/posthog/pull/16000
  • [x] Selecting from the S3 table https://github.com/PostHog/posthog/pull/16038
  • [x] Selecting count() from a lazy table https://github.com/PostHog/posthog/pull/16061
  • [x] Add support for cohort matching from within the query https://github.com/PostHog/posthog/pull/16119
  • [x] toDate(timestamp) does not work. What's the alternative? https://github.com/PostHog/posthog/pull/16757
  • [x] Selecting from the numbers table https://github.com/PostHog/posthog/pull/16945
  • [ ] Add WITH FILL https://github.com/PostHog/posthog/issues/17002
• [ ] HogVM action matching https://github.com/PostHog/posthog/pull/16189
  • [x] Misc cleanup https://github.com/PostHog/posthog/pull/16274
  • [x] Python & JS VM https://github.com/PostHog/posthog/pull/16275
  • [x] Cohort matching https://github.com/PostHog/posthog/pull/16279
  • [ ] Get it live
• [x] PoEv2 part 2
  • [x] Custom SQL columns https://github.com/PostHog/posthog/pull/15180
  • [x] Wrap up this refactor: https://github.com/PostHog/posthog/pull/15194
  • [x] Swap out person_id with an if when PoEv2 enabled
• [x] Documentation improvements
  • [x] Link to the ClickHouse function equivalent in the list of HogQL functions.
• [x] Other developments
  • [x] Graphs directly from HogQL data (e.g. events table + aggregation -> graph)
  • [x] Charts from HogQL queries if possible
  • [x] Benchmark query parsing and printing (parsing very large blobs of SQL text seemed to be slow)
  • [x] Group filters with joins and aliases (where events.organization.name = 'PostHog') https://github.com/PostHog/posthog/pull/17950
  • [x] Fix toDate(now()) (we alias toDate to toDateOrNull, which only takes a String argument, not DateTime) https://github.com/PostHog/posthog/pull/16757
  • [x] Array joins
  • [x] Add time range selector (important for dashboard-level time range, ZEN-3438)
  • [x] Clean up the /debug view (add sidebar, better examples, etc)
  • [x] Support column aliases in event explorer https://github.com/PostHog/posthog/pull/15861
  • [x] Re-enable event explorer "turbo mode"
  • [x] select session.duration

Data Exploration / Interlinking

• [x] Support AST reuse with EventsQuery().to_ast() or HogQLQuery().to_ast()
• [x] Support persons modal with { kind: PersonsQuery, source: { kind: InsightQuery, day: 3, breakdown: 'fish' } }
• [x] Backend support for custom SQL expressions https://github.com/PostHog/posthog/pull/18811
• [x] Subqueries you can save as views you can select from (not released?)

[danielthedifficult]

Just came here to say I love this feature, it's solving a problem I was trying to figure out myself just now, and that you're a beast for working on this.