SQL Querying Guide

Write analytical SQL against Arc measurements: the database.measurement naming, time_bucket aggregation, window functions, and filtering that enables partition pruning.

Arc runs a full analytical SQL engine over data stored as Parquet files, so window functions, CTEs, and joins are all available.

SQL syntax

Queries use the format database.measurement as the table name:

SELECT * FROM mydb.cpu LIMIT 10

If your database is named default, you can omit it:

SELECT * FROM default.cpu LIMIT 10

Query endpoints

Endpoint	Response Format	Best For
`POST /api/v1/query`	JSON	Small results, debugging, dashboards
`POST /api/v1/query/arrow`	Apache Arrow IPC	Large result sets
`GET /api/v1/query/:measurement`	JSON	Quick measurement queries

JSON query

curl -X POST "http://localhost:8000/api/v1/query" \
  -H "Authorization: Bearer $ARC_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT * FROM default.cpu WHERE time > NOW() - INTERVAL '\''1 hour'\'' LIMIT 100"}'

Arrow query

For large result sets, Arrow IPC provides ~2x throughput vs JSON:

curl -X POST "http://localhost:8000/api/v1/query/arrow" \
  -H "Authorization: Bearer $ARC_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT * FROM default.cpu LIMIT 1000000"}' \
  -o results.arrow

Time filtering

Arc stores timestamps in the time column. Use standard SQL intervals for time-range queries:

-- Last hour
SELECT * FROM default.cpu
WHERE time > NOW() - INTERVAL '1 hour';

-- Last 7 days
SELECT * FROM default.cpu
WHERE time > NOW() - INTERVAL '7 days';

-- Specific date range
SELECT * FROM default.cpu
WHERE time BETWEEN '2026-01-01' AND '2026-01-31';

Time-series aggregation

time_bucket

Group data into fixed-size time intervals:

-- Hourly averages for the last 7 days
SELECT
  time_bucket('1 hour', time) AS bucket,
  AVG(cpu_usage) AS avg_cpu,
  MAX(cpu_usage) AS max_cpu,
  COUNT(*) AS samples
FROM default.cpu
WHERE time > NOW() - INTERVAL '7 days'
GROUP BY bucket
ORDER BY bucket;

date_trunc

Truncate timestamps to calendar boundaries:

-- Daily summary for the last 30 days
SELECT
  date_trunc('day', time) AS day,
  host,
  AVG(cpu_usage) AS avg_cpu,
  AVG(mem_usage) AS avg_mem
FROM default.cpu
WHERE time > NOW() - INTERVAL '30 days'
GROUP BY day, host
ORDER BY day DESC, host;

Window functions

Compute rolling metrics and detect anomalies:

-- 10-minute moving average with anomaly detection
SELECT
  time,
  host,
  cpu_usage,
  AVG(cpu_usage) OVER (
    PARTITION BY host
    ORDER BY time
    ROWS BETWEEN 10 PRECEDING AND CURRENT ROW
  ) AS moving_avg,
  cpu_usage - AVG(cpu_usage) OVER (
    PARTITION BY host
    ORDER BY time
    ROWS BETWEEN 60 PRECEDING AND CURRENT ROW
  ) AS deviation
FROM default.cpu
WHERE time > NOW() - INTERVAL '1 hour';

Common table expressions (CTEs)

Break complex queries into readable steps:

-- Find hosts with anomalous CPU spikes
WITH hourly_stats AS (
  SELECT
    host,
    time_bucket('1 hour', time) AS bucket,
    AVG(cpu_usage) AS avg_cpu,
    STDDEV(cpu_usage) AS std_cpu
  FROM default.cpu
  WHERE time > NOW() - INTERVAL '24 hours'
  GROUP BY host, bucket
),
anomalies AS (
  SELECT *
  FROM hourly_stats
  WHERE avg_cpu > 80 OR std_cpu > 20
)
SELECT host, bucket, avg_cpu, std_cpu
FROM anomalies
ORDER BY avg_cpu DESC;

Cross-database queries

Join data across databases and measurements:

-- Join CPU metrics with deployment events
SELECT
  c.time,
  c.host,
  c.cpu_usage,
  d.version
FROM production.cpu c
JOIN production.deployments d
  ON c.host = d.host
  AND c.time BETWEEN d.time AND d.time + INTERVAL '1 hour'
WHERE c.time > NOW() - INTERVAL '24 hours';

Useful SQL functions

These are the functions most useful for analytical and time-series queries:

Function	Description	Example
`NOW()`	Current timestamp	`WHERE time > NOW() - INTERVAL '1h'`
`time_bucket(interval, time)`	Fixed-size time buckets	`time_bucket('5 minutes', time)`
`date_trunc(part, time)`	Calendar truncation	`date_trunc('day', time)`
`epoch(time)`	Timestamp to epoch seconds	`epoch(time)`
`PERCENTILE_CONT(p)`	Percentile (continuous)	`PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY latency)`
`APPROX_QUANTILE(col, p)`	Approximate percentile (faster)	`APPROX_QUANTILE(latency, 0.99)`
`STDDEV(col)`	Standard deviation	`STDDEV(cpu_usage)`
`LAG(col) OVER (...)`	Previous row value	`LAG(value) OVER (ORDER BY time)`
`LEAD(col) OVER (...)`	Next row value	`LEAD(value) OVER (ORDER BY time)`

Aggregate functions

Arc supports the full analytical SQL aggregate set — there is no allowlist. Beyond the standard COUNT/SUM/AVG/MIN/MAX, the following are commonly useful for analytics:

Function	Description	Example
`COUNT(DISTINCT col)`	Distinct count	`COUNT(DISTINCT host)`
`APPROX_COUNT_DISTINCT(col)`	Fast approximate distinct count	`APPROX_COUNT_DISTINCT(user_id)`
`MEDIAN(col)`	Median value	`MEDIAN(latency)`
`MODE(col)`	Most frequent value	`MODE(status_code)`
`QUANTILE_CONT(col, p)`	Continuous quantile	`QUANTILE_CONT(latency, 0.95)`
`STDDEV(col)` / `VARIANCE(col)`	Standard deviation / variance	`STDDEV(cpu_usage)`
`ARG_MAX(arg, val)` / `ARG_MIN(arg, val)`	`arg` at the row where `val` is max/min	`ARG_MAX(host, cpu_usage)`
`FIRST(col)` / `LAST(col)`	First / last value in group	`LAST(value)`
`STRING_AGG(col, sep)`	Concatenate values	`STRING_AGG(host, ', ')`
`LIST(col)` / `ARRAY_AGG(col)`	Collect values into a list	`LIST(value)`
`HISTOGRAM(col)`	Value-count map	`HISTOGRAM(status_code)`
`CORR(y, x)`	Correlation coefficient	`CORR(cpu_usage, mem_usage)`
`REGR_SLOPE(y, x)` / `REGR_INTERCEPT(y, x)`	Linear regression slope / intercept	`REGR_SLOPE(value, epoch(time))`
`ENTROPY(col)`	Shannon entropy	`ENTROPY(status_code)`

This is a selection, not the full set. There is no allowlist — if a standard analytical aggregate exists, Arc accepts it. An unrecognised function returns a query error naming it, so trying one is safe.

Performance tips

Always filter by time -- Partition pruning skips entire Parquet files outside the range, often by a wide margin.
Use Arrow for large results -- Arrow IPC provides ~2x throughput vs JSON for result sets over 100K rows.
Limit result sets -- Add LIMIT when exploring data. Scanning millions of rows without a limit is expensive.
Use aggregations server-side -- Compute AVG, COUNT, SUM in SQL rather than fetching raw rows and aggregating client-side.
Prefer APPROX_QUANTILE over PERCENTILE_CONT -- For large datasets, approximate percentiles are substantially faster.
Use time_bucket over date_trunc -- time_bucket supports arbitrary intervals (5 min, 15 min, 4 hours) while date_trunc is limited to calendar boundaries.

Next steps

API Reference -- Full endpoint documentation
Python SDK Querying -- Query with pandas, polars, and PyArrow
Retention Policies -- Automatic data expiration
Continuous Queries -- Real-time aggregations and downsampling

On this page