SQL API

Query your logs & metrics outside of the Better Stack dashboard with our read-only HTTP API.

You can connect from Grafana, the ClickHouse client via HTTP or any other HTTP client.

Getting started

1. Create a username and password by navigating to AI SRE → MCP and API in the side menu.

2. Click Connect ClickHouse HTTP client.

3. Follow the instructions in form and click Create connection.

4. Copy the password shown in the flash message and store it securely. You won't be able to access the password again.

Basic usage

Fetch your data using a simple curl command:

curl -u $USERNAME:$PASSWORD \
  -H 'Content-type: plain/text' \
  -X POST 'https://eu-nbg-2-connect.betterstackdata.com?output_format_pretty_row_numbers=0' \
  -d "SELECT dt, raw FROM (
    SELECT dt, raw FROM remote(t123456_your_source_logs) 
    UNION ALL 
    SELECT dt, raw FROM s3Cluster(primary, t123456_your_source_s3)
      WHERE _row_type = 1
  ) ORDER BY dt DESC LIMIT 100 FORMAT JSONEachRow"

curl -u $USERNAME:$PASSWORD \
  -H 'Content-type: plain/text' \
  -X POST 'https://eu-nbg-2-connect.betterstackdata.com?output_format_pretty_row_numbers=0' \
  -d "SELECT dt, raw FROM (
    SELECT dt, raw FROM remote(t123456_your_source_spans)
    UNION ALL
    SELECT dt, raw FROM s3Cluster(primary, t123456_your_source_s3)
      WHERE _row_type = 3
  ) ORDER BY dt DESC LIMIT 100 FORMAT JSONEachRow"

curl -u $USERNAME:$PASSWORD \
  -H 'Content-type: plain/text' \
  -X POST 'https://eu-nbg-2-connect.betterstackdata.com?output_format_pretty_row_numbers=0' \
  -d "SELECT toStartOfHour(dt) AS time, countMerge(events_count) 
      FROM remote(t123456_your_source_metrics) 
      GROUP BY time 
      ORDER BY time DESC 
      LIMIT 24 
      FORMAT Pretty"

Replace $USERNAME:$PASSWORD with your connection credentials and t123456_your_source depending on your available sources, which you can find in Connections:

Data sources explained

The Query API provides access to three types of telemetry data for each source: logs, spans, and metrics. This data is split between hot storage for recent data and cold storage for historical data.

Logs

remote(t123456_your_source_logs) for fast access to recent logs.

For historical data, use s3Cluster(primary, t123456_your_source_s3). Filter with WHERE _row_type = 1.

Use UNION ALL to combine recent and historical data for complete results.

Spans

remote(t123456_your_source_spans) for fast access to recent spans.

For historical data, use s3Cluster(primary, t123456_your_source_s3). Filter with WHERE _row_type = 3.

Metrics

remote(t123456_your_source_metrics) provides aggregated metrics data, similar to Dashboards.

Filtering and searching logs

Search by log content

Filter logs containing specific text or fields:

SELECT dt, raw
FROM (
  SELECT dt, raw
  FROM remote(t123456_your_source_logs)
  UNION ALL
  SELECT dt, raw
  FROM s3Cluster(primary, t123456_your_source_s3)
  WHERE _row_type = 1
)
WHERE raw LIKE '%My text%'
ORDER BY dt ASC
LIMIT 5000
FORMAT JSONEachRow

Extract JSON fields

Access nested JSON fields in your logs:

SELECT 
  dt,
  JSONExtract(raw, 'level', 'Nullable(String)') as severity,
  JSONExtract(raw, 'message', 'Nullable(String)') as message,
  JSONExtract(raw, 'context.hostname', 'Nullable(String)') as hostname
FROM remote(t123456_your_source_logs)
WHERE severity = 'ERROR'
LIMIT 100
FORMAT JSONEachRow

Optimizing queries for large datasets

Pagination

For large result sets, paginate using time-based ordering:

SELECT raw, dt
FROM s3Cluster(primary, t123456_your_source_s3)
WHERE _row_type = 1 
  AND dt BETWEEN toDateTime64(1748449431, 0, 'UTC') AND toDateTime64(1748535831, 0, 'UTC')
ORDER BY dt ASC
LIMIT 1000
FORMAT JSONEachRow

S3 Sampling

To help run extremely fast S3 queries over very wide time ranges, you can apply sampling, which uses a percentage of the stored S3 files as opposed to all of them; this is great if you want results quickly, and are comfortable with an estimated result.

curl -u $USERNAME:$PASSWORD \
  -H 'Content-type: plain/text' \
  -X POST 'https://eu-nbg-2-connect.betterstackdata.com?output_format_pretty_row_numbers=0' \
  -d "SELECT toStartOfInterval(dt, INTERVAL 1 DAY) AS day, count(*) AS count
      FROM s3Cluster(primary, t123456_your_source_s3, sampling = 0.5)
      WHERE _row_type = 1
        AND dt BETWEEN toDateTime64(1748449431, 0, 'UTC') AND toDateTime64(1748535831, 0, 'UTC')
      ORDER BY day ASC
      LIMIT 1000
      FORMAT JSONEachRow"

Common issues and solutions

Memory limit exceeded

If you encounter MEMORY_LIMIT_EXCEEDED errors:

• Use shorter time ranges: Limit your queries to smaller time windows.
• Add specific filters: Filter by dt, source, or other fields early in your query.
• Limit result size: Use LIMIT or max_result_rows settings.
• Apply S3 sampling: See dedicated section for details.

Too many simultaneous queries

To avoid hitting the concurrent query limit:

• Add delays between requests: Wait 1-2 seconds between API calls.
• Use shorter time ranges: Reduce the scope of each query.
• Implement retry logic: Detect the error and retry after a longer delay.
• Switch to a higher query speed: Higher query speeds make all your queries faster and have higher limits for concurrency.

Output formats

The Query API supports various ClickHouse output formats, most notably:

• JSON - A single JSON data structure.
• JSONEachRow - One JSON object per line, best for programmatic access.
• Pretty - Human-readable table format.
• CSV - Comma-separated values.
• TSV - Tab-separated values.

Specify the format at the end of your SQL query: FORMAT JSONEachRow

Best practices

• Always use LIMIT: Prevent accidentally fetching too much data.
• Order your results: Use ORDER BY dt DESC or ORDER BY dt ASC for consistent pagination.
• Filter early: Apply WHERE conditions to reduce data processing.
• Use appropriate time ranges: Shorter ranges perform better.
• Store credentials securely: Never hardcode usernames and passwords in your scripts.
• Handle errors gracefully when scripting: Implement retry logic for temporary failures or rate limiting.

Query limits

With Standard querying, you can run up to 4 concurrent queries for logs and 20 concurrent queries for metrics.

You can choose a higher Query speed, which is applied to the data region for both your Telemetry and Warehouse sources.