Query & SQL Overview
Querying - as a base action - is performed in three primary ways:
- Query via the QuestDB Web Console
- Query via PostgreSQL
- Query via REST HTTP API
QuestDB provides SQL with enhanced time series extensions.
This makes analyzing, downsampling, processing and reading time series data an intuitive and flexible experience.
Queries can be written into many applications using the many rich and diverse drivers and clients of the PostgreSQL or REST-ful ecosystems. However, querying is also leveraged heavily by third-party tools to provide visualizations, such as within Grafana, or for connectivity into broad data infrastructure and application environments such as with a tool like Cube.
Need to ingest data first? Checkout our Ingestion overview.
#
QuestDB Web ConsoleThe Web Console is available by default at localhost:9000
. The GUI makes it
easy to write, return and chart queries. There is autocomplete, syntax
highlighting, errors, and more. If you want to test a query or interact direclty
with your data in the cleanest and simplest way, apply queries via the Web
Console.
#
PostgreSQLQuery QuestDB using the PostgreSQL endpoint via the default port 8812
.
Examples in multiple languages are shown below.
- Python
- Java
- NodeJS
- Go
- C#
- C
- Ruby
- PHP
#
PostgreSQL compatibilityQuestDB offers broad compatibility with the PostgreSQL ecosystem.
However, please note some limitations.
#
Forward-only cursorsQuestDB diverges from PostgreSQL in its handling of cursor commands. While PostgreSQL supports scrollable cursors, enabling backward and forward navigation through the results of a SQL query, QuestDB applies a different approach.
QuestDB does not support scrollable cursors that require explicit creation and
management through DECLARE CURSOR
and subsequent operations like FETCH
.
Instead, QuestDB supports non-scrollable, or "forward-only", cursors. This
distinction means that while you can iterate over query results sequentially,
you cannot navigate backwards or access result positions as you might with
scrollable cursors in PostgreSQL.
As a result, some PostgreSQL drivers and libraries that rely on scrollable cursors may not be fully compatible with QuestDB. For instance, psycopg2 โ a popular PostgreSQL driver for Python โ utilizes scrollable cursors extensively. If possible, select drivers that support non-scrollable cursors for optimal compatibility. One such example is asyncpg, which is database driver for asyncio and PostgreSQL.
#
REST HTTP APIQuestDB exposes a REST API for compatibility with a wide range of libraries and tools.
The REST API is accessible on port 9000
and has the following query-capable
entrypoints:
For details such as content type, query parameters and more, refer to the REST HTTP API reference.
Entrypoint | HTTP Method | Description | REST HTTP API Reference |
---|---|---|---|
/exp?query=.. | GET | Export SQL Query as CSV | Reference |
/exec?query=.. | GET | Run SQL Query returning JSON result set | Reference |
/exp
: SQL Query to CSV#
The /exp
entrypoint allows querying the database with a SQL select query and
obtaining the results as CSV.
For obtaining results in JSON, use /exec
instead, documented next.
- cURL
- Python
/exec
: SQL Query to JSON#
The /exec
entrypoint takes a SQL query and returns results as JSON.
This is similar to the /exp
entry point which returns results as CSV.
#
Querying Data- cURL
- Python
- NodeJS
- Go
The JSON response contains the original query, a "columns"
key with the schema
of the results, a "count"
number of rows and a "dataset"
with the results.
Alternatively, the /exec
endpoint can be used to create a table and the
INSERT
statement can be used to populate it with values:
- cURL
- NodeJS
- Python
The node-fetch
package can be installed using npm i node-fetch
.
#
What's next?Now... SQL! It's query time.
SQL is fairly intuitive and known to many.
Whether you want to use the Web Console, PostgreSQL or REST HTTP (or both), query construction is rich.
To brush up and learn what's unique in QuestDB, consider the following:
And to learn about some of our favourite, most powerful syntax:
- Window functions are a powerful analysis tool
- Aggregate functions - aggregations are key!
- Date & time operators to learn about date and time
SAMPLE BY
to summarize data into chunks based on a specified time interval, from a year to a microsecondWHERE IN
to compress time ranges into concise intervalsLATEST ON
for latest values within multiple series within a tableASOF JOIN
to associate timestamps between a series based on proximity; no extra indices required
Looking for visuals?
- Explore Grafana
- Jump quickly into the Web Console