Create my first database

The goal of this guide 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 at http://localhost:9000.

You can also run the same SQL via the Postgres endpoint 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 TABLE sensors (ID LONG, make STRING, city STRING);

For more information about this statement, please refer to the CREATE TABLE reference documentation.

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
;

For more information about this statement, please refer to the INSERT reference documentation. About the functions, please refer to the random generator and the row generator pages.

Our sensors table now contains 10,000 randomly generated sensor values 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;

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):

readings;

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

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

and the average reading:

SELECT avg(temp) FROM readings;
average
18.997

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

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
............

For more information about these statements, please refer to the SELECT and JOIN pages.

Deleting tables#

Upon dropping the table, all data is deleted.