QuestDB is an open-source high-performance time-series database(TSDB) for applications in IoT, event monitoring, machine learning, DevOps, financial services e.t.c. QuestDB written in Java and C programming language has support for the SQL query language and also implements PostgreSQL wire protocol, REST API, and an InfluxDB Line Protocol.
QuestDB was developed by Vlad Ilyushchenko in 2014 with the main goal of creating low latency trading techniques for databases to improve performance with minimal server resources. Ever since its first release, the QuestDB SQL database has gained popularity all over the world. This is highly contributed by its amazing features that include:
- Supports ANSI SQL and includes language extensions for time series, this helps perform calculations on timestamped data for row- and column-based access.
- Data is stored in a chronological order and table partitions are applied based on time.
- Data is massively parallelized using vectorized query execution.
- Time series, relational joins, downsampling, and unlimited sub-queries are supported.
- High performance achieved from a column-oriented storage model.
- No dependencies required, and is 100% free from garbage collection
- Easy to run queries and inspect table schemas using the web interface
By the end of this guide, you will have gained the required knowledge on how to run the QuestDB SQL database in Docker Container.
1. Install Docker CE on Linux
For this guide, we need to install docker engine on our Linux system. Below is a dedicated guide to aid you in installing Docker Engine.
Start and enable docker
sudo systemctl start docker sudo systemctl enable docker
Verify that docker is running:
$ systemctl status docker ● docker.service - Docker Application Container Engine Loaded: loaded (/lib/systemd/system/docker.service; enabled; vendor preset: enabled) Active: active (running) since Thu 2022-01-13 02:30:03 EST; 4min 5s ago TriggeredBy: ● docker.socket Docs: https://docs.docker.com Main PID: 2741 (dockerd) Tasks: 8 Memory: 30.4M CPU: 239ms CGroup: /system.slice/docker.service └─2741 /usr/bin/dockerd -H fd:// --containerd=/run/containerd/containerd.sock
Ensure that your system user is added to the docker group as below.
sudo usermod -aG docker $USER newgrp docker
2. Run QuestDB SQL database in Docker Container.
There are two methods on how to navigate along:
- Pull the container image and run directly
- Use Docker-compose
Option 1 – Pull the QuestDB image and Run QuestDB Container
Once Docker is installed on your system, you can pull the QuestDB container image and run it or combine both in one command as below.
cd ~ docker run -t -d \ -p 9000:9000 \ -p 9009:9009 \ -p 8812:8812 \ -p 9003:9003 \ --name docker_questdb \ questdb/questdb
In the above command, we have exposed the below ports:
- 9000:9000 -For REST API and Web Console
- 8812:8812 – For Postgres wire protocol
- 9009:9009 – For InfluxDB line protocol
- 9003:9003 – For Min health server
We can also add a persistent volume for data using the -v argument. Normally the QuestDB root directory will be at /root/.questdb/. You can mount Docker Volumes to the QuestDB Docker container so that data may be persisted or server configuration settings may be passed to an instance.
First stop and delete running container
$ docker rm -f docker_questdb docker_questdb $ docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
In the following example we demonstrate how to mount the directory ./questdb_data to a QuestDB container using the -v flag in a Docker run command:
cd ~ docker run -t -d \ -p 9000:9000 \ -p 9009:9009 \ -p 8812:8812 \ -p 9003:9003 \ --name docker_questdb \ -v "$(pwd)/questdb_data:/root/.questdb/" questdb/questdb
Now you will have data stored in the path convenient for migration or backups.
$ ls questdb_data/ conf db public
Now check the status of the container.
$ docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 9f69d05b8604 questdb/questdb "/usr/bin/env QDB_PA…" 5 seconds ago Up 4 seconds 0.0.0.0:8812->8812/tcp, :::8812->8812/tcp, 0.0.0.0:9000->9000/tcp, :::9000->9000/tcp, 0.0.0.0:9003->9003/tcp, :::9003->9003/tcp, 0.0.0.0:9009->9009/tcp, :::9009->9009/tcp docker_questdb
Option 2 – Run QuestDB SQL database with Docker-Compose
Here, we will create a QuestDB SQL database docker-compose YAML file. The file will contain all the required information about our container.
With Docker installed, proceed and install Docker-compose using the guide below.
Using your favorite editor, create the YAML file as below.
cd ~ mkdir questdb cd questdb vim docker-compose.yml
In the file, add the below lines:
version: "3" services: questdb: image: questdb/questdb container_name: docker_questdb restart: always ports: - "8812:8812" - "9000:9000" - "9009:9009" - "9003:9003" volumes: - ./data/db:/root/.questdb/db
Now run the container as below.
docker-compose up -d
Command execution output:
[+] Running 2/2 ⠿ Network questdb_default Created 0.1s ⠿ Container docker_questdb Started
Once complete, verify if the container is running:
$ docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 1bfbaa565e5a questdb/questdb "/usr/bin/env QDB_PA…" 19 seconds ago Up 17 seconds 0.0.0.0:8812->8812/tcp, :::8812->8812/tcp, 0.0.0.0:9000->9000/tcp, :::9000->9000/tcp, 0.0.0.0:9003->9003/tcp, :::9003->9003/tcp, 0.0.0.0:9009->9009/tcp, :::9009->9009/tcp docker_questdb
3. Connect to QuestDB SQL database.
There are several methods one can use to connect and interact with QuestDB. They include:
- Web Console listening on port 9000
- REST API on port 9000
- PostgreSQL wire protocol on port 8812
- InfluxDB line protocol for high-throughput ingestion on port 9009
In this guide, I will demonstrate how to connect and interact with QuestDB using the Web console and the REST API.
1. Connect to QuestDB SQL database Web Console.
The QuestDB web console is listening on port 9000 and can be accessed using the URL http://IP_Address:9000. If you have trouble accessing the page, allow the port through the firewall.
You should be able to see this page:
Here, you can run your scripts. For example, begin by creating a table:
CREATE TABLE sensors (ID LONG, make STRING, city STRING);
Once the table is created, insert data into it.
INSERT INTO sensors SELECT x ID, --increasing integer rnd_str('Eberle', 'Honeywell', 'Omron', 'United Automation', 'RS Pro') make, rnd_str('Kampala', 'Nairobi', 'Bamako', 'Segere', 'Dodoma') city FROM long_sequence(10000) x ;
On the web console, the commands should be entered as below and click Run.
You will have your data added as below.
View data in the tables as below
SELECT * FROM sensors WHERE city='Nairobi';
You can also import data from a file.
2. Connect to QuestDB SQL database using REST API
REST API is based on HTTP and provides the best and simple way to interact with QuestDB.
Here, API functions are keyed on the URL and use query parameters as arguments. For example, to import data, you will use the below command:
curl -F [email protected]file.csv \ 'http://localhost:9000/imp'
You can as well execute queries that return a JSON response.
For example. Create a table as below
curl -G \ --data-urlencode "query=CREATE TABLE sensors (ID LONG, make STRING, city STRING);" \ --data-urlencode "count=true" \ http://localhost:9000/exec
Insert data into the table.
curl -G \ --data-urlencode "query=INSERT INTO sensors SELECT x ID, --increasing integer rnd_str('Eberle', 'Honeywell', 'Omron', 'United Automation', 'RS Pro') make, rnd_str('Kampala', 'Nairobi', 'Bamako', 'Segere', 'Dodoma') city FROM long_sequence(10000) x ;" \ --data-urlencode "count=true" \ http://localhost:9000/exec
Now view the data in the table
curl -G \ --data-urlencode "query=SELECT * FROM sensors WHERE city='Nairobi' limit 5;" \ --data-urlencode "count=true" \ http://localhost:9000/exec
4. Manage the QuestDB SQL database Container.
The QuestDB SQL database container can be managed as below:
- Start the container.
docker start docker_questdb
- Stop the container.
docker stop docker_questdb
That marks the end of this guide on how to run the QuestDB SQL database in Docker Container. I hope this guide was of significance to you.