Skip to content

SQL query

Better Stack exposes stored logs through the ClickHouse HTTP interface of a SQL connection (<region>-connect.betterstackdata.com, config queryUrl) with the connection's own HTTP Basic credentials (queryUsername, queryPassword). client.query() returns the BetterStackQuery client; it raises CONFIG when the SQL side is not configured. Basic credentials are never sent to the ingest host.

Function ClickHouse HTTP Result
query(sql, params, {settings}) POST {queryUrl}/, body = SQL, param_<name> URL parameters rows (objects keyed by column); FORMAT JSONEachRow is appended when the SQL has no FORMAT; FORMAT JSON / JSONEachRow (also with output_format_json_array_of_rows) are parsed, other formats raise VALIDATION
queryRaw(sql, params, {settings}) same the response body unchanged (any format)
searchLogs({source, contains, start, end, limit, order, cursor}) the SQL below LogSearchResult{records, nextCursor}
renderSearchSql(options) — the SQL searchLogs would run
escapeParamValue(text) — a String parameter value in ClickHouse's escaped (TSV) text format
sqlString(text), jsonEscape(text) — a quoted SQL string literal; JSON string escaping without quotes
logRecordFromRow(row) — a {dt, raw} row as a normalized LogRecord

Tables

Each source has a collection <team_id>_<table_name> (for example t1234_myapp; config sourceTable). Hot logs are remote(<collection>_logs), cold logs s3Cluster(primary, <collection>_s3) with WHERE _row_type = 1 (spans _row_type = 3), spans remote(<collection>_spans). Columns are dt (DateTime64, UTC) and raw (the event JSON). The collection is an identifier: it is validated against ^[A-Za-z_][A-Za-z0-9_]*$ and cannot be a bound parameter (use {t:Identifier} in your own SQL).

Parameters

query and queryRaw bind parameters server side: write ClickHouse placeholders {name:Type} in the SQL and pass the values; each is sent as the URL parameter param_<name>. Values are converted to ClickHouse text: null → \N (use a Nullable(…) type), booleans → true/false, numbers as decimals (NaN and infinity raise VALIDATION), time values → YYYY-MM-DD HH:MM:SS.ffffff (UTC), strings as they are. ClickHouse parses parameter values in the escaped (TabSeparated) text format, so string values are escaped first: backslash → \\, tab → \t, newline → \n, carriage return → \r (escapeParamValue). Quotes need no escaping. Parameter names must match ^[A-Za-z_][A-Za-z0-9_]*$; a placeholder without a value is rejected by the server (VALIDATION). settings are sent as ClickHouse settings (for example max_execution_time).

const q = client.query();
const rows = await q.query(
  "SELECT count() AS c FROM remote({t:Identifier}) " +
    "WHERE position(raw, {needle:String}) > 0 AND dt >= {since:DateTime64(6)}",
  { t: "t1234_myapp_logs", needle: 'C:\\tmp\\it\'s "quoted"', since: new Date(Date.now() - 3_600_000) },
  { settings: { max_execution_time: 30 } },
);
console.log(rows[0]?.c); // 64-bit integers arrive as strings: "5"

const csv = await q.queryRaw("SELECT dt, raw FROM remote(t1234_myapp_logs) LIMIT 5 FORMAT CSV");
use grow_better_stack_logs::{query_params, QueryOptions};

let q = client.query()?;
let rows = q
    .query(
        "SELECT count() AS c FROM remote({t:Identifier}) \
         WHERE position(raw, {needle:String}) > 0 AND dt >= {since:DateTime64(6)}",
        &query_params! {
            "t" => "t1234_myapp_logs",
            "needle" => r#"C:\tmp\it's "quoted""#,
            "since" => std::time::SystemTime::now() - std::time::Duration::from_secs(3600),
        },
        &QueryOptions::default().setting("max_execution_time", 30),
    )
    .await?;
println!("{}", rows[0]["c"]);
q := client.Query()
rows, err := q.Query(ctx,
    "SELECT count() AS c FROM remote({t:Identifier}) "+
        "WHERE position(raw, {needle:String}) > 0 AND dt >= {since:DateTime64(6)}",
    map[string]any{"t": "t1234_myapp_logs", "needle": `C:\tmp\it's "quoted"`, "since": time.Now().Add(-time.Hour)},
    betterstack.QueryOptions{Settings: map[string]any{"max_execution_time": 30}})
if err != nil {
    return err
}
fmt.Println(rows[0]["c"])
csv, err := q.QueryRaw(ctx, "SELECT dt, raw FROM remote(t1234_myapp_logs) LIMIT 5 FORMAT CSV", nil)
from datetime import datetime, timedelta, timezone

q = client.query()
rows = q.query(
    "SELECT count() AS c FROM remote({t:Identifier}) "
    "WHERE position(raw, {needle:String}) > 0 AND dt >= {since:DateTime64(6)}",
    {
        "t": "t1234_myapp_logs",
        "needle": r'C:\tmp\it\'s "quoted"',
        "since": datetime.now(timezone.utc) - timedelta(hours=1),
    },
    settings={"max_execution_time": 30},
)
print(rows[0]["c"])
csv = q.query_raw("SELECT dt, raw FROM remote(t1234_myapp_logs) LIMIT 5 FORMAT CSV")
BetterStackQuery q = client.query();
List<Map<String, Object>> rows = q.query(
    "SELECT count() AS c FROM remote({t:Identifier}) "
        + "WHERE position(raw, {needle:String}) > 0 AND dt >= {since:DateTime64(6)}",
    Map.of("t", "t1234_myapp_logs", "needle", "C:\\tmp\\it's \"quoted\"",
        "since", Instant.now().minus(Duration.ofHours(1))),
    QueryOptions.defaults().withSetting("max_execution_time", 30));
System.out.println(rows.get(0).get("c"));
String csv = q.queryRaw("SELECT dt, raw FROM remote(t1234_myapp_logs) LIMIT 5 FORMAT CSV");

searchLogs

searchLogs searches hot and cold storage of one collection (default: the configured sourceTable) and returns normalized records. It renders one runnable SQL statement with escaped SQL literals (it does not use server-side parameters, so renderSearchSql output can be pasted into any SQL client):

SELECT dt, raw FROM (
  SELECT dt, raw FROM remote(t1234_myapp_logs)
  UNION ALL
  SELECT dt, raw FROM s3Cluster(primary, t1234_myapp_s3) WHERE _row_type = 1
) WHERE dt BETWEEN toDateTime64('2026-11-18 20:00:00.000000', 6, 'UTC')
             AND toDateTime64('2026-11-18 21:00:00.000000', 6, 'UTC')
  AND position(raw, 'order \\"42\\"') > 0
ORDER BY dt DESC LIMIT 101 FORMAT JSONEachRow
Option Default Notes
source sourceTable collection identifier; CONFIG when neither is set
contains match all case-sensitive literal substring of the raw event JSON; JSON-escaped first, so " and \ match inside string values
start end (or now) − 24 h inclusive
end no upper bound inclusive when given (robust to clock skew and ingest delay when unset)
limit 100 1 … 10 000 records per page
order desc asc or desc by dt
cursor — opaque nextCursor of the previous page

Rows are deduplicated on (dt, raw) (hot and cold storage overlap briefly) and normalized: body = message (else msg, else the raw text), severity from severity_number / level / severity, traceId / spanId from span.trace_id / span.span_id, resource from resource, every other field into attributes.

const q = client.query();
let page = await q.searchLogs({ contains: "order 42", start: new Date(Date.now() - 3_600_000), limit: 50 });
for (const r of page.records) console.log(r.timestamp.toISOString(), r.severityText, r.body);
if (page.nextCursor) page = await q.searchLogs({ contains: "order 42", cursor: page.nextCursor });
console.log(q.renderSearchSql({ source: "t1234_other", contains: "x", order: "asc" }));
use grow_better_stack_logs::SearchLogsOptions;
use grow_better_stack_logs::logging::SortOrder;

let q = client.query()?;
let opts = SearchLogsOptions::default().contains("order 42").limit(50);
let page = q.search_logs(opts.clone()).await?;
for r in &page.records {
    println!("{:?} {} {}", r.timestamp, r.severity_text, r.body);
}
if let Some(cursor) = page.next_cursor {
    let _next = q.search_logs(opts.cursor(cursor)).await?;
}
println!("{}", q.render_search_sql(&SearchLogsOptions::default().source("t1234_other").order(SortOrder::Asc))?);
q := client.Query()
opts := betterstack.SearchLogsOptions{Contains: "order 42", Start: time.Now().Add(-time.Hour), Limit: 50}
page, err := q.SearchLogs(ctx, opts)
if err != nil {
    return err
}
for _, r := range page.Records {
    fmt.Println(r.Timestamp.Format(time.RFC3339Nano), r.SeverityText, r.Body)
}
if page.NextCursor != "" {
    opts.Cursor = page.NextCursor
    page, err = q.SearchLogs(ctx, opts)
}
sql, err := q.RenderSearchSQL(betterstack.SearchLogsOptions{Source: "t1234_other", Order: betterstack.OrderAsc})
from datetime import datetime, timedelta, timezone

q = client.query()
start = datetime.now(timezone.utc) - timedelta(hours=1)
page = q.search_logs(contains="order 42", start=start, limit=50)
for r in page.records:
    print(r.timestamp.isoformat(), r.severity_text, r.body)
if page.next_cursor:
    page = q.search_logs(contains="order 42", start=start, cursor=page.next_cursor)
print(q.render_search_sql(source="t1234_other", contains="x", order="asc"))
BetterStackQuery q = client.query();
SearchLogsOptions opts = SearchLogsOptions.builder()
    .contains("order 42").start(Instant.now().minus(Duration.ofHours(1))).limit(50).build();
LogSearchResult page = q.searchLogs(opts);
page.records().forEach(r -> System.out.println(r.timestamp() + " " + r.severityText() + " " + r.body()));
if (page.nextCursor().isPresent()) {
  page = q.searchLogs(opts.toBuilder().cursor(page.nextCursor().get()).build());
}
System.out.println(q.renderSearchSql(SearchLogsOptions.builder().source("t1234_other").build()));

Errors

The ClickHouse exception code wins over the HTTP status. It is read from the X-ClickHouse-Exception-Code header, else from Code: N. in the body, and also detected when ClickHouse appends an exception to a 200 response after streaming started.

ClickHouse code Error
516 AUTHENTICATION_FAILED, 192, 193, 194 AUTHENTICATION
497 ACCESS_DENIED PERMISSION_DENIED
60 UNKNOWN_TABLE, 81 UNKNOWN_DATABASE NOT_FOUND
62 SYNTAX_ERROR, 47 UNKNOWN_IDENTIFIER, 46, 36, 53, 42, 73, 456, 457 VALIDATION
241 MEMORY_LIMIT_EXCEEDED VALIDATION with a hint (narrow the time range)
202 TOO_MANY_SIMULTANEOUS_QUERIES RATE_LIMITED (retryable)
159, 160 (timeouts) TIMEOUT
48 NOT_IMPLEMENTED (or HTTP 501) UNSUPPORTED
other codes by HTTP status (SPEC rules); an unknown code after a 200 is a non-retryable UNAVAILABLE

Queries are idempotent and retried on 429, 5xx and network errors. Better Stack allows 4 concurrent log queries per account (Standard plan) and suggests 1–2 s between calls, so poll with waitForLog / tail at intervals of a second or more.