My first database

The goal of this tutorial is to explore QuestDB's features to interact with time series data. This assumes you have an instance running. You can find guides to setup QuestDB on the introduction page.

In this tutorial, you will learn how to

As an example, we will look at hypothetical temperature readings from a variety of sensors.

info

All commands are run through the Web Console accessible on http://localhost:9000.

You can also run the same SQL using Postgres wire or the REST API.

Creating a table

The first step is to create tables. One will contain the metadata of our sensors, the other will contain the readings from these sensors.

Let's start by creating the sensors table.

Create a table
CREATE TABLE sensors (ID LONG, make STRING, city STRING);
info

The CREATE TABLE command comes with many more functions. For more information, please refer to the CREATE TABLE command reference.

Inserting data

Let's populate our sensors table with procedurally-generated data.

Insert as select
INSERT INTO sensors
SELECT
x ID, --increasing integer
rnd_str('Eberle', 'Honeywell', 'Omron', 'United Automation', 'RS Pro') make,
rnd_str('New York', 'Miami', 'Boston', 'Chicago', 'San Francisco') city
FROM long_sequence(10000) x
;
info

For more information on INSERT and INSERT as select, please refer to the INSERT reference.

For more information on procedurally generated data in the random generator functions and in the row generator functions.

Our sensors table now contains 10,000 randomly generated sensors of different makes and in various cities. It should look like the below:

IDmakecity
1RS ProNew York
2HoneywellChicago
3United AutomationMiami
4HoneywellChicago
.........

Let's now create some sensor readings. In this case, we will generate the table and the data at the same time.

Create table as
CREATE TABLE readings
AS(
SELECT
x ID,
timestamp_sequence(to_timestamp('2019-10-17T00:00:00', 'yyyy-MM-ddTHH:mm:ss'), rnd_long(1,10,2) * 100000L) ts,
rnd_double(0)*8 + 15 temp,
rnd_long(0, 10000, 0) sensorId
FROM long_sequence(10000000) x)
TIMESTAMP(ts)
PARTITION BY MONTH;
note

While creating this table we did the following:

  • TIMESTAMP(ts) elected ts as designated timestamp. This will enable time partitioning.
  • PARTITION BY MONTH created a monthly partition strategy. Our data will be sharded in monthly files.

The generated data will look like the below.

IDtstempsensorId
12019-10-17T00:00:00.000000Z19.373739119160
22019-10-17T00:00:00.600000Z21.911846179671
32019-10-17T00:00:01.400000Z16.583678348731
42019-10-17T00:00:01.500000Z16.693088153447
52019-10-17T00:00:01.600000Z19.679915697985
............

Running queries

Let's first select all records from the readings table (note the omission of SELECT * FROM):

Select *
readings;

Let's also select the count of records from readings:

Simple aggregation
SELECT count() FROM readings;
count
10,000,000

and the average reading:

Simple aggregation
SELECT avg(temp) FROM readings;
average
18.997

We can now leverage our sensors table to get more interesting data.

Simple JOIN
SELECT *
FROM readings
JOIN(
SELECT ID sensId, make, city
FROM sensors)
ON readings.sensorId = sensId;

Results should look like the data below:

IDtstempsensorIdsensIdmakecity
12019-10-17T00:00:00.000000Z19.3737391191609160RS ProBoston
22019-10-17T00:00:00.600000Z21.9118461796719671United AutomationNew York
32019-10-17T00:00:01.400000Z16.5836783487318731HoneywellMiami
42019-10-17T00:00:01.500000Z16.6930881534473447United AutomationMiami
52019-10-17T00:00:01.600000Z19.6799156979857985EberleSan Francisco
62019-10-17T00:00:01.600000Z15.3951403942304230United AutomationChicago
72019-10-17T00:00:02.100000Z15.0671956628292829HoneywellNew York
.....................
Aggregation keyed by city
SELECT city, max(temp)
FROM readings
JOIN(
SELECT ID sensId, city
FROM sensors)
ON readings.sensorId = sensId;

Results should look like the data below:

citymax
Boston22.99999233
New York22.99999631
Miami22.99999673
San Francisco22.99999531
Chicago22.9999988
Aggregation by hourly time buckets
SELECT ts, city, make, avg(temp)
FROM readings
JOIN (
SELECT ID sensId, city, make
FROM sensors
WHERE city='Miami' AND make='Omron')
ON readings.sensorId = sensId
WHERE ts ='2019-10-21;1d' -- this is an interval between 21-10 and 1day later
SAMPLE BY 1h;

Results should look like the data below.

tscitymakeaverage
2019-10-21T00:00:00.000000ZMiamiOmron18.97225935
2019-10-21T01:00:00.000000ZMiamiOmron19.15940157
2019-10-21T02:00:00.000000ZMiamiOmron18.92696357
2019-10-21T03:00:00.000000ZMiamiOmron19.09917038
2019-10-21T04:00:00.000000ZMiamiOmron19.1161127
2019-10-21T05:00:00.000000ZMiamiOmron18.93939597
............
info

Find more about these commands in the Select and Join sections.

Deleting tables

Upon dropping the table, all data is deleted.