Skip to content

query.mdx

Latest commit

 

History

History
161 lines (108 loc) · 7.29 KB

query.mdx

File metadata and controls

161 lines (108 loc) · 7.29 KB
Raw
title Querying the data warehouse with SQL
sidebar Docs
showTitle true
availability
free selfServe enterprise
full
full
full

import { ProductScreenshot } from "components/ProductScreenshot"; import { CalloutBox } from "components/Docs/CalloutBox";

PostHog provides the full flexibility of SQL to query your data warehouse using the SQL editor.

To create a query, go to the SQL editor. Here you can see and search the schema of all available sources and PostHog tables as well as saved views.

Warehouse tables and views denied by access control are hidden.

Here you can:

  1. Write your SQL query using your table like SELECT * FROM stripe.prod.charge
  2. Click Run to see the results.
  3. Modify your query using SQL commands and functions as needed to get the data you want like select amount / 100 as dollar_charge, status from stripe.prod.charge.

  1. Click Create insight.
  2. Choose and customize your visualization.
  3. Click Save insight.

PostHog AI can help write SQL for you. Just click the icon in the corner of your screen and ask it to write or tweak your query for you.

Searching the schema sidebar

The schema sidebar supports searching by name and filtering by materialization status. Type in the search field to filter tables and views by name.

For materialized views, you can also filter by run status using search tokens:

  • status:failed — show only views whose last run failed
  • status:running — show only views currently running
  • status:completed — show only views whose last run succeeded

Combine status filters with name searches. For example, orders status:failed shows failed views with "orders" in their name. You can also use the funnel icon next to the search field to toggle status filters.

Status filters apply to the PostHog Warehouse sidebar and the Models tab but not to direct connections.

Working with multiple queries

The SQL editor supports writing multiple queries in a single tab, separated by semicolons. When you click Run or press Cmd+Enter (Mac) / Ctrl+Enter (Windows/Linux), only the query under your cursor executes. The active query is highlighted with a blue background so you can see which one runs next.

For example, if your editor contains:

SELECT * FROM events LIMIT 10;
SELECT count(*) FROM persons;
SELECT distinct event FROM events;

Placing your cursor on the second line and clicking Run executes only SELECT count(*) FROM persons.

If you manually select text in the editor, pressing Cmd+Enter runs the selected text instead.

Running subqueries

Press Cmd+Shift+Enter (Mac) / Ctrl+Shift+Enter (Windows/Linux) to run the innermost subquery at your cursor position. This is useful for testing nested queries independently.

For example, with the query:

SELECT * FROM (SELECT id, event FROM events WHERE event = 'pageview') LIMIT 10

Placing your cursor inside the inner SELECT and pressing Cmd+Shift+Enter runs only SELECT id, event FROM events WHERE event = 'pageview'.

Subqueries that may fail when run standalone — such as those referencing CTEs defined elsewhere — show a wavy yellow underline with a tooltip explaining the issue on hover.

SQL visualizations

SQL queries have multiple visualization options including:

  • Auto (default) - automatically selects the best chart type based on your query results
  • Table
  • Big number
  • Line chart
  • Bar chart
  • Stacked bar chart
  • Area chart
  • 2D heatmap

Note: Line chart and area chart require a date or datetime column in the query results.

By clicking on the Visualization tab below the query, you can customize the X-axis, Y-axis, legend, scale, goal lines, and more. Each visualization type has its own set of customization options.

In the Series tab, you can format numerical columns with different number styles:

  • None - Display raw values
  • Number - Standard number formatting
  • Short Number - Compact format for large numbers (e.g., 1,200 displays as "1.2K", 1,500,000 as "1.5M")
  • Percentage - Display values as percentages

You can also set a prefix, suffix, and decimal places for each series.

For example, with tables, you can add conditional formatting rules. These enable you to highlight cells based on their value and are set up in the Conditional formatting tab.

Querying multiple sources together

Much of the power of the data warehouse comes from combining multiple sources, like your Stripe or Hubspot data with your product analytics data. Two of the easiest ways of doing this are WHERE IN and JOIN SQL commands.

import QueryJoinExample from "./_snippets/query-join-example.mdx";

Query execution details

After running a query, execution details can help you understand the resource usage and execution time:

  • Memory usage: Total memory consumed by the query
  • Data read: Total amount of data read by the query
  • CPU time: Processing time used by the CPU
  • Duration: Duration of query execution in ClickHouse

Hover over Execution details to see additional information about how the query was executed.

These metrics are helpful for optimizing queries, especially when working with large datasets or complex joins. As you develop and change your query, keep an eye on the impact of your changes on the metrics. Seeing a spike in bytes read might prompt you to use a more selective filter.

Optimizing queries

import OptimalQueries from "../_snippets/optimal-queries.mdx";