# Querying with Ad-hoc SQL API

Query your events & time series with our read-only Ad-hoc SQL HTTP API.

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

## Getting started

1. Go to **Warehouse** -> [Ad-hoc SQL API](https://warehouse.betterstack.com/team/t0/sql-api ";_blank") -> [Create connection](https://warehouse.betterstack.com/team/t0/sql-api/new ";_blank").

2. Select your **Cluster** and **Teams** the connection should have access to.

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

![CleanShot 2025-10-17 at 9 .27.17.png](https://imagedelivery.net/xZXo0QFi-1_4Zimer-T0XQ/98f6283f-bded-4515-4d8b-42e0d73d4100/md2x =2924x1880)

## Basic usage

Fetch your data using a simple curl command:

[code-tabs]
```bash
[label Recent events]
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_events) 
    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 Hourly time series]
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_timeseries) 
      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 **Ad-hoc SQL API**:

![CleanShot 2025-10-17 at 9 .22.45.png](https://imagedelivery.net/xZXo0QFi-1_4Zimer-T0XQ/ded2c1e9-4d2f-4737-6077-970d75dba100/orig =3680x2276)

## Data sources explained

The Ad-hoc SQL API provides access to 2 types of data for each source: events and time series. 

### Events

`remote(t123456_your_source_events)` for fast access to recent events.

For historical events, use `s3Cluster(primary, t123456_your_source_s3)`.

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

### Time series

`remote(t123456_your_source_timeseries)` provides aggregated time series data.

[note]
Replace `t123456_your_source` depending on your available sources, which you can find in **Ad-hoc SQL API**.
[/note]

## Filtering and searching events

### Search by event content

Filter logs containing specific text or fields:

```sql
[label Accessing all events containing "My text"]
SELECT dt, raw
FROM (
  SELECT dt, raw
  FROM remote(t123456_your_source_events)
  UNION ALL
  SELECT dt, raw
  FROM s3Cluster(primary, t123456_your_source_s3)
)
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,
  json.level AS severity
  -- alternative syntax using the JSONExtract function and raw column
  -- 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
```

### 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
```

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

Consider upgrading your [**querying speed**](https://betterstack.com/docs/warehouse/querying-data/query-speeds/).

### 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](https://betterstack.com/docs/warehouse/querying-data/query-speeds/) make all your queries faster and have higher limits for concurrency.

Consider upgrading your [**querying speed**](https://betterstack.com/docs/warehouse/querying-data/query-speeds/).

## Output formats

The Ad-hoc SQL 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**.

You can [choose a higher Query speed](https://betterstack.com/docs/warehouse/querying-data/query-speeds/), which is applied to the data region for both your Telemetry and Warehouse sources.