posthog.com/contents/docs/sql/index.mdx

---
title: SQL access in PostHog
sidebar: Docs
showTitle: true
availability:
  free: full
  selfServe: full
  enterprise: full
---

export const ExpressionLight = "https://res.cloudinary.com/dmukukwp6/image/upload/posthog.com/contents/images/features/hogql/expression-light.png"
export const ExpressionDark = "https://res.cloudinary.com/dmukukwp6/image/upload/posthog.com/contents/images/features/hogql/expression-dark.png"
export const SQLLight = "https://res.cloudinary.com/dmukukwp6/image/upload/posthog.com/contents/images/features/hogql/sql-light.png"
export const SQLDark = "https://res.cloudinary.com/dmukukwp6/image/upload/posthog.com/contents/images/features/hogql/sql-dark.png"
export const WarehouseLight = "https://res.cloudinary.com/dmukukwp6/image/upload/posthog.com/contents/images/features/hogql/warehouse-light.png"
export const WarehouseDark = "https://res.cloudinary.com/dmukukwp6/image/upload/posthog.com/contents/images/features/hogql/warehouse-dark.png"

You can use SQL (Structured Query Language) throughout PostHog to manage, query, and modify data. 

Our "flavor" is effectively a wrapper around [ClickHouse SQL](https://clickhouse.com/docs/en/sql-reference), with tweaks such as simplified event and person property access, null handling, and visualization integrations.

## SQL expressions

[SQL expressions](/docs/sql/expressions) enable you to use database identifiers and functions to directly access, [aggregate](/docs/sql/aggregations), filter, transform, and break down your data.

They can be used by selecting the **SQL** tab or **SQL expression** option in filters, breakdowns, dashboards, trends, funnels (aggregating by), user paths (event types), the [activity tab](/docs/activity) (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')`

<ProductScreenshot
  imageLight={ExpressionLight}
  imageDark={ExpressionDark}
  alt="SQL expression example"
/>

## SQL insights

[SQL insights](/docs/product-analytics/sql) 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](/docs/sql/clickhouse-functions). 

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:

```sql
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
```

<ProductScreenshot
  imageLight={SQLLight}
  imageDark={SQLDark}
  alt="SQL insight"
/>

You can use SQL insights within [notebooks](/docs/notebooks) and with external sources using the [data warehouse](/docs/data-warehouse).

## Query API

To query events using SQL via the [PostHog API](/docs/api/queries), get [your project ID](https://us.posthog.com/settings/project-details#variables), a [personal API key](https://us.posthog.com/settings/user-api-keys) with the project query read permission and make a POST request to `/api/projects/:project_id/query` endpoint with the following JSON payload:

```json
{"query": {"kind": "HogQLQuery", "query": "select * from events", "name": "get all events"}}
```
Why HogQL? Because HogQL is the name we use for SQL access internally.

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):

<MultiLanguage>

```bash
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"
            },
            "name": "get count of most common events"
         }'
```

```python
import requests

headers = {
    "Content-Type": "application/json",
    "Authorization": "Bearer <personal_api_key>"
}

data = {
    "query": {
        "kind": "HogQLQuery",
        "query": "SELECT event, COUNT() FROM events GROUP BY event ORDER BY COUNT() DESC"
    },
    "name": "get count of most common events"
}

response = requests.post(
    '<ph_app_host>/api/projects/:project_id/query', 
    headers=headers, 
    json=data
)

print(response.json())
```
</MultiLanguage>

The response is in the format:

```ts
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 SQL 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 SQL, check out the ["SQL" tab](https://us.posthog.com/sql). You can click on every table listed to see the data included and query them.

<DataWarehouseScreenshot
  imageLight={WarehouseLight}
  imageDark={WarehouseDark}
  alt="Data warehouse example"
/>

The data warehouse also enables you to add external sources, like [Stripe](/tutorials/stripe-reports) and Hubspot, and query them alongside your PostHog data.