# SQL API

Query your web events and session replays outside of Better Stack with ClickHouse SQL over a read-only HTTP API. Build custom reports, feed BI tools, or export data programmatically.

## Getting started

1. Create a connection by navigating to [**Integrations**](https://telemetry.betterstack.com/team/0/dashboards/connections?tab=sql-api ";_blank") and clicking **Connect** ClickHouse HTTP client.
2. **Select the teams** the connection should have access to.
3. **Copy the password** shown in the flash message and store it securely. **You won't be able to access the password again.**

The full setup, output formats, query limits, and optimization tips are covered in the [SQL API guide](https://betterstack.com/docs/logs/query-api/connect-remotely/). This page covers what's specific to real user monitoring data.

## What you can query

Each application exposes its real user monitoring data as separate collections. Find the exact identifiers on your connection's card under **Query with** in [Integrations](https://telemetry.betterstack.com/team/0/dashboards/connections?tab=sql-api ";_blank"):

- `remote(t123456_your_app_web_events)` for recent web events: page views, clicks, form interactions, and custom events.
- `remote(t123456_your_app_replays)` for recent session replay metadata.
- `s3Cluster(primary, t123456_your_app_s3)` for historical data. Filter with `_row_type = 8` for web events and `_row_type = 6` for replays.

Event details live in the `raw` JSON column. Use `JSONExtract` functions to access fields.

### Web event fields

Common top-level fields in the `raw` JSON of a web event:

| Field | Description |
|---|---|
| `event` | Event type, such as `page-load`, `page-change`, `click`, or a custom event name. |
| `dt` | Capture time as a Unix timestamp in milliseconds. |
| `session_id` | Session identifier. |
| `anonymous_user_id` | Stable anonymous visitor identifier. |
| `user` | Identified user object, present when `betterstack('user', ...)` was called, such as `user.email` and `user.id`. |
| `fingerprint` | Array with the `v1_<hash>` browser fingerprint, present when fingerprinting is enabled. |
| `payload` | Event-specific data. Autocapture events nest page details under `payload.meta`, such as `payload.meta.url`, `payload.meta.title`, and `payload.meta.referrer`. |
| `contexts` | Browser, operating system, and device, added during ingestion: `contexts.browser.name`, `contexts.client_os.name`, and `contexts.device.type`. |

See [JavaScript tag events](https://betterstack.com/docs/rum/js-tag/events/) for the full per-event schema.

## Example queries

```sql
[label Daily page views]
SELECT
  toDate(dt) AS day,
  count(*) AS page_views
FROM s3Cluster(primary, t123456_your_app_s3)
WHERE _row_type = 8
  AND JSONExtractString(raw, 'event') IN ('page-load', 'page-change')
  AND dt >= now() - INTERVAL 30 DAY
GROUP BY day
ORDER BY day
FORMAT JSONEachRow
```

```sql
[label Sessions of a specific user]
SELECT DISTINCT
  JSONExtractString(raw, 'session_id') AS session_id,
  min(dt) AS started_at
FROM s3Cluster(primary, t123456_your_app_s3)
WHERE _row_type = 8
  AND JSONExtract(raw, 'user', 'email', 'Nullable(String)') = 'user@example.com'
  AND dt >= now() - INTERVAL 7 DAY
GROUP BY session_id
ORDER BY started_at DESC
FORMAT JSONEachRow
```

```sql
[label Replays with errors]
SELECT
  JSONExtractString(raw, 'replay_id') AS replay_id,
  JSONExtract(raw, 'user', 'email', 'Nullable(String)') AS user_email,
  length(JSONExtractArrayRaw(raw, 'error_ids')) AS error_count
FROM s3Cluster(primary, t123456_your_app_s3)
WHERE _row_type = 6
  AND JSONExtractInt(raw, 'segment_id') = 0
  AND length(JSONExtractArrayRaw(raw, 'error_ids')) > 0
ORDER BY error_count DESC
LIMIT 50
FORMAT JSONEachRow
```

[note]
#### Listing replays?
A replay consists of multiple segments sharing one `replay_id`. Filter for `segment_id = 0` to get one row per replay.
[/note]

## Need help?

We're here for you! Let us know at [hello@betterstack.com](mailto:hello@betterstack.com) and we'll be happy to help.