# Query ClickHouse

Use the Superwall API to query your organization's ClickHouse-backed analytics data.

The ClickHouse query API gives you direct SQL access to the same analytics data Superwall uses for charts and campaign results. Use it when you need flexible reporting, internal dashboards, or ad hoc analysis without maintaining a separate data warehouse.

Requests are scoped to your organization and require an organization API key with the `data:read` scope. Superwall provisions a read-only ClickHouse user for your organization on first use, then applies row-level policies so queries only return data for your organization's applications.

> **Warning**

Treat `data:read` keys as sensitive. They can query analytics data for your organization, so create dedicated keys, store them in a secret manager, and revoke them when they are no longer needed.



Endpoint [#endpoint]

Use either `POST` or `GET`:

| Method | Path                                                                 | SQL location          |
| ------ | -------------------------------------------------------------------- | --------------------- |
| `POST` | `https://api.superwall.com/v2/organizations/{organization_id}/query` | Request body          |
| `GET`  | `https://api.superwall.com/v2/organizations/{organization_id}/query` | `query` URL parameter |

`POST` is recommended for most queries because SQL can be long and multiline.

Authentication [#authentication]

1. Open **Settings > API Keys** in the Superwall dashboard.
2. Create an organization API key.
3. Give the key the `data:read` scope.
4. Copy the token when Superwall shows it.

Pass the token as a bearer token:

```bash
Authorization: Bearer YOUR_SECRET_TOKEN
```

The token must belong to the organization in the path. A key from another organization cannot query this endpoint.

Send a query [#send-a-query]

Set your organization ID and API key:

```bash
export SUPERWALL_ORG_ID="123"
export SUPERWALL_API_KEY="sk_..."
```

Run a query with `POST`:

```bash
curl "https://api.superwall.com/v2/organizations/$SUPERWALL_ORG_ID/query" \
  --request POST \
  --header "Authorization: Bearer $SUPERWALL_API_KEY" \
  --data-binary "SELECT count() FROM sw.events_rep"
```

Run a query with `GET`:

```bash
curl --get "https://api.superwall.com/v2/organizations/$SUPERWALL_ORG_ID/query" \
  --header "Authorization: Bearer $SUPERWALL_API_KEY" \
  --data-urlencode "query=SELECT count() FROM sw.events_rep"
```

The response is the raw ClickHouse HTTP response. If you do not specify a format, ClickHouse returns its default text format. Add a `FORMAT` clause when you need JSON:

```bash
curl "https://api.superwall.com/v2/organizations/$SUPERWALL_ORG_ID/query" \
  --request POST \
  --header "Authorization: Bearer $SUPERWALL_API_KEY" \
  --data-binary "
    SELECT
      name,
      count() AS events
    FROM sw.events_rep
    WHERE ts >= now() - INTERVAL 7 DAY
    GROUP BY name
    ORDER BY events DESC
    LIMIT 20
    FORMAT JSONEachRow
  "
```

Use ClickHouse HTTP options [#use-clickhouse-http-options]

The endpoint proxies ClickHouse HTTP requests after Superwall authenticates your organization API key. You can pass standard ClickHouse URL parameters, such as `query`, `database`, or `default_format`, through the query string:

```bash
curl --get "https://api.superwall.com/v2/organizations/$SUPERWALL_ORG_ID/query" \
  --header "Authorization: Bearer $SUPERWALL_API_KEY" \
  --data-urlencode "query=SELECT name, count() FROM events_rep GROUP BY name LIMIT 20" \
  --data-urlencode "database=sw" \
  --data-urlencode "default_format=JSONEachRow"
```

Superwall does not expose the generated ClickHouse username and password. Authenticate to the Superwall endpoint with your bearer token instead of connecting directly to the ClickHouse cluster.

Available tables [#available-tables]

Your read-only user can query the analytics tables Superwall exposes for customer reporting:

| Table                                      | Use it for                                                                                                     |
| ------------------------------------------ | -------------------------------------------------------------------------------------------------------------- |
| `sw.events_rep`                            | Raw Superwall events, including event name, metadata, properties, sandbox flag, application ID, and timestamp. |
| `sw.events_hr_agg`                         | Hourly event aggregates.                                                                                       |
| `sw.demand_score_events_rep`               | Demand Score event data.                                                                                       |
| `open_revenue.attributed_events_by_ts_rep` | Revenue and attribution events ordered by event time.                                                          |
| `open_revenue.paywall_open_events_agg`     | Aggregated paywall open events.                                                                                |
| `sw.subscription_status_rep`               | Subscription status records.                                                                                   |
| `sw.user_attributes_rep`                   | User attributes set through the SDK or paywall flows.                                                          |
| `sw.applications_rep`                      | Application metadata available in ClickHouse.                                                                  |

Use ClickHouse introspection queries to inspect columns before writing a production query:

```sql
SHOW TABLES FROM sw;
SHOW TABLES FROM open_revenue;
DESCRIBE TABLE sw.events_rep;
DESCRIBE TABLE open_revenue.attributed_events_by_ts_rep;
```

Query JSON properties [#query-json-properties]

Some event details are stored in JSON strings such as `props` and `meta`. Use ClickHouse JSON functions to extract them:

```sql
SELECT
  JSONExtractString(props, '$placement_name') AS placement,
  count() AS opens
FROM sw.events_rep
WHERE name = 'paywall_open'
  AND ts >= now() - INTERVAL 30 DAY
GROUP BY placement
ORDER BY opens DESC
LIMIT 20
FORMAT JSONEachRow;
```

Limits [#limits]

Queries run as a read-only organization user with ClickHouse settings applied:

| Limit                  | Value       |
| ---------------------- | ----------- |
| Maximum execution time | 300 seconds |
| Maximum threads        | 4           |
| Maximum memory         | 8 GB        |
| Maximum bytes read     | 20 GB       |

If a query times out or uses too much memory, narrow the date range, add filters on `applicationId`, `isSandbox`, or event `name`, and avoid selecting large JSON columns unless you need them.

Troubleshooting [#troubleshooting]

| Status | What to check                                                                                                            |
| ------ | ------------------------------------------------------------------------------------------------------------------------ |
| `401`  | The request is missing a bearer token, or the token is invalid or revoked.                                               |
| `403`  | The API key does not include the `data:read` scope.                                                                      |
| `404`  | The requested organization resource could not be found.                                                                  |
| `429`  | Too many requests were sent in a short period. Retry later.                                                              |
| `500`  | ClickHouse returned an unexpected error or Superwall could not proxy the request. Check the SQL and try a smaller query. |

Related [#related]

* [Access Controls](/docs/dashboard/dashboard-settings/overview-settings-access-controls)
* [Charts](/docs/dashboard/charts)
* [Superwall Skill](/docs/dashboard/guides/superwall-skill)