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

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

See JavaScript tag events for the full per-event schema.

Example queries

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

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

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

Need help?

We're here for you! Let us know at hello@betterstack.com and we'll be happy to help.