# 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 [**Integrations**](https://telemetry.betterstack.com/team/0/dashboards/connections?tab=sql-api ";_blank") in the side menu.

2. Click **Connect** ClickHouse HTTP client.

3. **Select the teams** the connection should have access to. Connections are team-scoped and can query all sources that belong to the selected teams.

4. **Follow the instructions** in form and click **Create connection**.

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

![SQL API tab on the Telemetry Integrations page](https://imagedelivery.net/xZXo0QFi-1_4Zimer-T0XQ/3d4cfd86-e81b-4a7e-b90f-caf84f166600/orig =3120x2660)

## What can I query?

Each connection card in [Integrations](https://telemetry.betterstack.com/team/0/dashboards/connections?tab=sql-api ";_blank") lists its queryable data sources under **Query with**. Use **Copy sample cURL** on the card to get a ready-to-run query.

[note]
#### Prefer using APIs?

You can manage your ClickHouse connections using [Connection API](https://betterstack.com/docs/logs/api/connections/).
[/note]

## Basic usage

Fetch your data using a simple curl command:

[code-tabs]
```bash
[label Recent logs]
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"
```
```bash
[label Recent spans]
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"
```
```bash
[label Hourly metrics]
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"
```
[/code-tabs]

Replace `$USERNAME:$PASSWORD` with your connection credentials and `t123456_your_source` depending on your available sources, which you can find in [Integrations](https://telemetry.betterstack.com/team/0/dashboards/connections?tab=sql-api ";_blank"):

![Where to find the data sources](https://imagedelivery.net/xZXo0QFi-1_4Zimer-T0XQ/42ea7d55-7420-4fa5-f3db-13f97054fe00/lg1x =3818x2388)

## 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](https://betterstack.com/docs/logs/dashboards/).

For longer time ranges, use [pre-aggregated metrics sources](#leveraging-pre-aggregated-metrics-data).

[note]
Replace `t123456_your_source` depending on your available sources, which you can find in [Integrations](https://telemetry.betterstack.com/team/0/dashboards/connections?tab=sql-api ";_blank").
[/note]

## Filtering and searching logs

### Search by log content

Filter logs containing specific text or fields:

```sql
[label Accessing all logs containing "My text"]
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:

```sql
[label Accessing recent error 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:

```sql
[label Using LIMIT and ORDER BY for pagination]
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.

```bash
[label Optimizing using S3 sampling]
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
[highlight]
      FROM s3Cluster(primary, t123456_your_source_s3, sampling = 0.5)
[/highlight]
      WHERE _row_type = 1
        AND dt BETWEEN toDateTime64(1748449431, 0, 'UTC') AND toDateTime64(1748535831, 0, 'UTC')
      ORDER BY day ASC
      LIMIT 1000
      FORMAT JSONEachRow"
```

### Leveraging pre-aggregated metrics data

To ensure fast query performance over long time periods, Better Stack automatically stores metrics data in several pre-aggregated tables. This is especially effective for wide-ranging queries, like aggregating user activity over several months.

`remote(t123456_your_source_metrics)` contains the raw, high-resolution metrics data processed at 15-second intervals. Best for short time ranges or high-detail analysis.

`remote(t123456_your_source_metrics_5m)` contains data pre-aggregated into 5-minute buckets.

`remote(t123456_your_source_metrics_1h)` contains data pre-aggregated into 1-hour buckets. Best for long time ranges (weeks, months, or years).

To use the pre-aggregation, append the `_1h` or `_5m` suffix to the metrics source identifier. Since `avg()` is still accurate at a coarser granularity, the results are the same, but the performance is significantly better:

[code-tabs]
```sql
[label 1-hour pre-aggregation]
SELECT
    label('route') AS route,
    avgMerge(value_avg) AS avg_response_time
[highlight]
FROM remote(t123456_your_source_metrics_1h)
[/highlight]
WHERE name = 'page_response_time'
    AND dt >= now() - INTERVAL 12 MONTH
GROUP BY route
```
```sql
[label 5-minute pre-aggregation]
SELECT
    label('route') AS route,
    avgMerge(value_avg) AS avg_response_time
[highlight]
FROM remote(t123456_your_source_metrics_5m)
[/highlight]
WHERE name = 'page_response_time'
    AND dt >= now() - INTERVAL 12 MONTH
GROUP BY route
```
```sql
[label Raw metrics]
SELECT
    label('route') AS route,
    avgMerge(value_avg) AS avg_response_time
FROM remote(t123456_your_source_metrics)
WHERE name = 'page_response_time'
    AND dt >= now() - INTERVAL 12 MONTH
GROUP BY route
```
[/code-tabs]

In real-world use cases, this simple change can improve query speeds by over 10x.

## 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](#s3-sampling) for details.

### Too many simultaneous queries

To avoid hitting the [concurrent query limit](#query-limits):

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

## Output formats

The Query API supports various [ClickHouse output formats](https://clickhouse.com/docs/interfaces/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`

[info]
**Missing the brackets around your data when using JSONEachRow?**

You can use `SETTINGS output_format_json_array_of_rows = 1` in front of `FORMAT JSONEachRow` to make sure your data is wrapped by `[` and `]`, if you need valid JSON output.

Learn more about using [JSON in ClickHouse](https://clickhouse.com/docs/interfaces/formats/JSON).
[/info]

## 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**.