Ad-hoc queries (Andreas)

CrateDB is built to support highly dynamic, ad-hoc querying, even on large-scale, real-time datasets.

Whether you're a developer, data analyst, or operator, CrateDB lets you ask new questions on the fly without waiting for data pipelines, ETL, or pre-aggregated views. Thanks to its distributed SQL engine, flexible data modeling, and support for semi-structured data, CrateDB is ideal for interactive exploration and live analytics.

---

What Are Ad-hoc Queries?

Ad-hoc queries are spontaneous, often one-off SQL queries used for:

• Troubleshooting

• Exploratory analysis

• Debugging systems

• Investigating anomalies

• Supporting customer questions

• Generating quick reports

They are unpredictable by nature—and CrateDB is designed to handle exactly that.

---

Why CrateDB for Ad-hoc Queries?

Feature	Benefit
Distributed SQL engine	Fast performance, even on complex queries
Real-time ingestion	Query new data moments after it arrives
Flexible schemas	Combine structured, JSON, text, and geospatial data
Full SQL support	Use familiar SQL for joins, filters, sorting, and more
Easy integrations	Query from CLI, notebooks, BI tools, or REST API

---

Common Query Patterns

Quick Filters

SELECT *
FROM logs
WHERE service = 'auth' AND log_level = 'error'
ORDER BY timestamp DESC
LIMIT 50;

Explore Nested JSON

SELECT payload['device']['os'], COUNT(*) AS count
FROM events
GROUP BY payload['device']['os'];

Geospatial Debugging

SELECT id, latitude, longitude
FROM vehicles
WHERE within(geo_point(latitude, longitude), geo_polygon([...]));

Time-bound Query

SELECT *
FROM sensor_data
WHERE timestamp > now() - INTERVAL '15 minutes';

Join Across Tables

SELECT o.order_id, c.name, o.total
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date >= CURRENT_DATE - 7;

---

Real-World Examples

DevOps & Observability

Investigate production issues by filtering logs or telemetry on the fly.

SELECT message
FROM logs
WHERE log_level = 'warn' AND host = 'api-01'
ORDER BY timestamp DESC;

Data Exploration

Test hypotheses by slicing data in new ways without waiting for prebuilt reports.

SELECT city, AVG(duration)
FROM rides
GROUP BY city
HAVING AVG(duration) > 10;

Product Analytics

Check how often a new product was bought after launch.

SELECT COUNT(*)
FROM orders
WHERE product_id = 'xyz123'
AND order_date >= '2025-07-01';

---

Performance Tips for Ad-hoc Queries

Tip	Description
Use targeted filters	Narrow your search with WHERE clauses
Leverage indexes	Fields are indexed by default—use them!
Avoid SELECT *	Select only the fields you need
Profile queries	Use EXPLAIN and ANALYZE to inspect performance
Use time filters	Especially on time-series or partitioned tables

---

Tools & Interfaces

CrateDB offers several interfaces for ad-hoc queries:

• Admin UI Console – Web-based SQL editor with result viewer

• PostgreSQL Clients – psql, DBeaver, DataGrip, etc.

• REST API / HTTP Client – Send ad-hoc queries over HTTP

• CrateDB Python Client – Ideal for notebooks and automation

• Grafana / Superset – Query builder UI and live dashboards

Example via REST:

curl -XPOST https://your.cratedb.cloud/_sql \
  -H "Content-Type: application/json" \
  -d '{"stmt": "SELECT * FROM logs WHERE log_level = ? LIMIT 10", "args": ["error"]}'

---

When to Use CrateDB for Ad-hoc Queries

Use CrateDB when you need to:

• Explore new patterns in operational or business data

• Run troubleshooting queries across complex systems

• Query fresh data instantly, without waiting for batch jobs

• Combine structured + JSON + full-text + spatial + vector data

• Avoid maintaining rigid ETL pipelines or OLAP cubes

---

Related Features

Feature	Description
Dynamic schemas	No need to predefine every field
Intelligent indexing	Works out of the box for ad-hoc querying
Time-series support	Perfect for time-bound diagnostics
Object columns	Flexible modeling of JSON data
Full-text & filter	Combine keyword search with structured queries

---

Learn More

• CrateDB SQL Reference

• CrateDB Console

• Blog – How to Run Exploratory Queries

• CrateDB Academy – Querying at Scale

---

Summary

CrateDB is your go-to database for fast, flexible, and reliable ad-hoc querying. Whether you’re debugging systems, answering tough questions, or uncovering hidden insights, CrateDB empowers you to work at the speed of thought on real-time data, at any scale.