How to use the QuestDB REST API

QuestDB comes with an HTTP server which exposes a REST API. This guide will teach you how to use the REST API to create tables, import data, run queries, and export results with curl. If you prefer a more visual approach, you can also use the Web Console.

For more information about our REST API, please consult the REST API

note

This requires a running instance of QuestDB with port 9000 exposed. You can learn how to do so with Docker

Get test data

The first step is to get data into the database. Here are some sample files you may want to try. You may use only one (we provide example queries for both), but using the two files will allow you to try asof join.

DataDescriptionDownloadFile SizeNumber of rows
NYC taxi data10 years of NYC taxi trips. Simplified to 2 trips per hour. Contains ride start and end times, distance, passenger count, fare, tip, and total amount paid.Download16.2 Mb183,000
NYX weather10 years of hourly weather data in central NYC. 137,000 rows. Contains timestamp, temperature, wind, snow, and moreDownload6.7 Mb137,000

Create tables

With your container running and port 9000 mapped, you can now send curl requests to the database server. This guide shows examples of how to interact with it.

First, we create the tables using /exec, which allows us to pass SQL statements. We also specify a designated timestamp column which will be useful for time based queries and time joins across tables.

Create trips table
curl -G http://localhost:9000/exec --data-urlencode \
"query=CREATE TABLE trips(pickupDatetime timestamp, \
dropoffDatetime timestamp, passengerCount int, tripDistance double, \
fareAmount double, tipAmount double, taxesAndTolls double, totalAmount double) \
timestamp(pickupDatetime);"
Create weather table
curl -G http://localhost:9000/exec --data-urlencode \
"query=CREATE TABLE weather(timestamp timestamp, windSpeed int, \
skyCover symbol, tempF int, rain1H double, snowDepth int) \
timestamp(timestamp);"

Note that the table creation step is optional as QuestDB automatically recognizes schema. However, creating the table manually allows us to specify a dedicated timestamp column which will be useful for time based queries, and to specify symbol which are more efficient than the automatically string type for skyCover.

Import data

We import both files using the /imp endpoint. Note that I set the flag name so the data flows into the tables we just created. Otherwise, the data would be inserted in a new table named after the file, for example weather.csv. We also set the timestamp flag to mark the designated timestamp column in the csv file.

Populate trips table
curl -i -F data=@trips.csv \
"http://localhost:9000/imp?\
name=trips&forceHeaders=true&overwrite=false&timestamp=pickupDatetime"
Populate weather table
curl -i -F data=@weather.csv \
"http://localhost:9000/imp?\
name=weather&forceHeaders=true&overwrite=false&timestamp=timestamp"

In addition to the csv import, we can also use exec to execute INSERT statements. You can either send all fields or a subset of the schema like in the example below. This is useful to send values in a different order from the table definition. It is also useful to skip values when they are not relevant. Missing values will be inserted as null.

Insert using SQL
curl -G http://localhost:9000/exec --data-urlencode \
"query=INSERT INTO weather(timestamp,tempF) values(systimestamp(),45);"

Run queries

Just like CREATE TABLE and INSERT INTO statements, we can use exec to pass SQL queries. exec returns results in JSON.

Simple query
curl -G http://localhost:9000/exec --data-urlencode \
"query=select timestamp, tempF from weather limit 2;"
JSON Response
{
"query": "select timestamp, tempF from weather limit 2;",
"columns": [
{
"name": "timestamp",
"type": "TIMESTAMP"
},
{
"name": "tempF",
"type": "INT"
}
],
"dataset": [
["2010-01-01T00:00:00.000000Z", 34],
["2010-01-01T00:51:00.000000Z", 34]
],
"count": 2
}

Here are a few example queries you could run against the dataset.

tabledescriptionquery
tripsAverage week by week trip distance over timeselect pickupDatetime, avg(tripDistance) from trips timestamp(pickupDatetime) sample by 7d;
tripsAverage monthly trip duration in minutesselect pickupDatetime, avg(datediff('m',pickupDatetime, dropoffDatetime)) from trips sample by 1M;
tripsAverage fare per passenger count bucketselect passengerCount, avg(tipAmount/fareAmount)*100 from trips order by passengerCount;
tripsAverage tip percentage per passenger count bucketselect passengerCount, avg(tipAmount/fareAmount)*100 from trips order by passengerCount;
weatherRainy daysselect timestamp, sum(rain1H)from weathersample by 1d;
weatherTemperature seasonalityselect timestamp, avg(tempF)from weather sample by 7d;
trips and weatherJoining trips and weather data. This query returns the prevailing weather conditions for every trip in 2017trips where pickupDatetime='2017' asof join weather;

Download results

You can use the /exp endpoint to export query results as follows.

Save results as csv
curl -G http://localhost:9000/exp --data-urlencode \
"query=select * from weather limit 100;" > results.csv

If you are querying from the Web Console, then you can download the results using the download to csv button.

Preview of the export function in the Web Console

Shut down and cleanup

As QuestDB is a persisted database, the data will remain after you shut down the server. If you would like to remove the data, you can run the following statements to drop the tables.

Cleanup
DROP TABLE trips;
DROP TABLE weather;