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?
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
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
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.
Last updated