How To Install QuestDB database on Kubernetes

QuestDB is a relational, column-oriented time-series database that finds high use in event monitoring, IoT, machine learning, DevOps, financial services, sensor data e.t.c. It uses SQL with extra extensions for time series which help with real-time analytics. In addition to that, QuestDB also implements the PostgreSQL wire protocol, REST API, and an InfluxDB Line Protocol.

QuestDB was created in 2014 by Vlad Ilyushchenko with the aim to create a low-latency trading technique for databases to increase performance with minimal resources. From its release time, QuestDB has gained popularity mainly due to:

• Support for ANSI SQL with native time-series SQL extensions which help perform calculations on timestamped data for row- and column-based access.
• High performance by adopting a column-oriented storage model, parallelized vector execution, SIMD instructions, and low-latency techniques
• Time series, relational joins, downsampling, and unlimited sub-queries are supported.
• Data is massively parallelized using vectorized query execution.
• Easy to run queries and inspect table schemas using the web interface
• The entire codebase is built from the ground up in Java and C++ with no dependencies and is 100% free from garbage collection.
• Data is stored in chronological order and table partitions are applied based on time.

In this guide, we will learn how to install QuestDB database on Kubernetes. Kubernetes is a free and open-source container orchestration tool. It works by distributing the workload across a cluster of hosts. A workload is an application to be run, for this case, the application is QuestDB. In Kubernetes, these workloads run inside pods that have defined lifecycles. To manage the pods’ lifecycle easier to manage, workload resources are used. These resources work to ensure that the desired container state is attained.

Kubernetes provides the following workload resources:

• Deployment: Is used to manage stateless application workload. However, the application can be made stateful by attaching a persistent volume to it.
  • A persistent volume is used to store the related container data. The data availability is guaranteed even after the pod exits.
• StatefulSets: this resource is used to manage the deployment and scale a set of Pods. It provides the guarantee about ordering and uniqueness of these Pods.
• DaemonSets: it ensures all the pod runs on all the nodes of the cluster. In case a node is added/removed from the cluster, DaemonSet automatically adds or removes the pod.

Now let’s dive in and enjoy the awesomeness!

Before we Begin

This guide requires you to have a Kubernetes cluster set up. If not, use any of the below guides to set up a Kubernetes cluster:

• Deploy HA Kubernetes Cluster on Rocky Linux 8 using RKE2
• Run Kubernetes on Debian with Minikube
• Deploy Kubernetes Cluster on Linux With k0s
• Install Kubernetes Cluster on Ubuntu using K3s
• Install Kubernetes Cluster on Rocky Linux 8 with Kubeadm & CRI-O

Once the cluster is up, you need to install and configure the kubectl tool

curl -LO "https://storage.googleapis.com/kubernetes-release/release/$(curl -s https://storage.googleapis.com/kubernetes-release/release/stable.txt)/bin/linux/amd64/kubectl"
chmod +x kubectl
sudo mv kubectl /usr/local/bin

Export the admin config to be able to access the cluster:

##For RKE2
export PATH=$PATH:/var/lib/rancher/rke2/bin export KUBECONFIG=/etc/rancher/rke2/rke2.yaml

##For K0s
export KUBECONFIG=/var/lib/k0s/pki/admin.conf

Verify if kubectl is working as desired:

# kubectl get nodes
NAME     STATUS   ROLES           AGE     VERSION
master   Ready    control-plane   2m22s   v1.25.2+k0s
node1    Ready    <none>          74s     v1.25.2+k0s
node2    Ready    <none>          65s     v1.25.2+k0s

Step 1- Install Helm on Linux

You need to have Helm installed on your system. This can be done easily using the aid provided in the guide below:

• Install and Use Helm 3 on Kubernetes Cluster

Once installed, check the version:

$ helm version
version.BuildInfo{Version:"v3.10.1", GitCommit:"9f88ccb6aee40b9a0535fcc7efea6055e1ef72c9", GitTreeState:"clean", GoVersion:"go1.18.7"}

Step 2 – Create a Persistent Storage

QuestDB can now be deployed in a Kubernetes cluster using a StatefulSet and a persistent volume. To create persistent storage for QuestDB, first, create the isolated namespace:

kubectl create namespace questdb
kubectl config set-context --current --namespace questdb

Now manually create a StorageClass:

vim storageClass.yml

Paste the below lines to the file:

kind: StorageClass
apiVersion: storage.k8s.io/v1
metadata:
  name: questdb
provisioner: kubernetes.io/no-provisioner
volumeBindingMode: WaitForFirstConsumer

Apply the manifest

kubectl create -f storageclass.yml

Set it as the default SC:

kubectl patch storageclass questdb -p '{"metadata": {"annotations":{"storageclass.kubernetes.io/is-default-class":"true"}}}'

Verify the changes:

# kubectl get sc
NAME                PROVISIONER                    RECLAIMPOLICY   VOLUMEBINDINGMODE      ALLOWVOLUMEEXPANSION   AGE
questdb (default)   kubernetes.io/no-provisioner   Delete          WaitForFirstConsumer   false                  2m40s

Now create a persistent volume

vim questdb-pv.yml

Add the below lines:

apiVersion: v1
kind: PersistentVolume
metadata:
  name: local-pv
spec:
  capacity:
    storage: 10Gi
  accessModes:
  - ReadWriteOnce
  persistentVolumeReclaimPolicy: Retain
  storageClassName: questdb
  local:
    path: /mnt/disk/vol1
  nodeAffinity:
    required:
      nodeSelectorTerms:
      - matchExpressions:
        - key: kubernetes.io/hostname
          operator: In
          values:
          - node1

In the above file, we have set node affinity for the PV and specified the node as node1, so create the path on the node:

DIRNAME="vol1"
sudo mkdir -p /mnt/disk/$DIRNAME 
sudo chmod 777 /mnt/disk/$DIRNAME

##Also run this On Rhel-based systems#####
sudo chcon -Rt svirt_sandbox_file_t /mnt/disk/$DIRNAME

Create the PV:

kubectl create -f questdb-pv.yml

Verify the creation:

# kubectl get pv
NAME       CAPACITY   ACCESS MODES   RECLAIM POLICY   STATUS      CLAIM   STORAGECLASS   REASON   AGE
local-pv   10Gi       RWO            Retain           Available           questdb    

Finally, create a PVC with the name my-questdb-my-questdb-0

$ vim sample-pvc.yml
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: my-questdb-my-questdb-0
  namespace: default
spec:
  #our storage class here
  storageClassName: questdb
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 10Gi

Apply the manifest:

kubectl apply -f sample-pvc.yml

Step 3 – Install QuestDB database on Kubernetes

Now we can deploy the QuestDB database on Kubernetes. First, add the QuestDB Helm chart

helm repo add questdb https://helm.questdb.io/

Update the Helm index:

helm repo update

Once added, deploy QuestDB with the command:

helm install my-questdb questdb/questdb

Sample Output:

NAME: my-questdb
LAST DEPLOYED: Mon Oct 31 13:42:52 2022
NAMESPACE: default
STATUS: deployed
REVISION: 1
NOTES:
1. Get the application URL by running these commands:
  export POD_NAME=$(kubectl get pods --namespace default -l "app.kubernetes.io/name=questdb,app.kubernetes.io/instance=my-questdb" -o jsonpath="{.items[0].metadata.name}")
  echo "Visit http://127.0.0.1:9000 to use your application"
  kubectl --namespace default port-forward $POD_NAME 9000:9000

Verify if the pods are ready:

$ kubectl get pods
NAME           READY   STATUS    RESTARTS   AGE
my-questdb-0   1/1     Running   0          28s

Verify if the PVC is bound:

# kubectl get pvc
NAME                      STATUS   VOLUME     CAPACITY   ACCESS MODES   STORAGECLASS   AGE
my-questdb-my-questdb-0   Bound    local-pv   10Gi       RWO            questdb        27s

In order to access QuestDB and run queries, you need to expose some ports. QuestDB used the following 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

For our deployment, we will expose port 9000, but you can still expose others.

kubectl port-forward --address 0.0.0.0 my-questdb-0 9000 &

Step 4 – Connect to QuestDB SQL database

You can use various methods to connect to the QuestDB database. The methods 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

For this guide, we will learn how to use the Web and REST API both listening on port 9000

a. Access the QuestDB SQL database Web Console.

To access the QuestDB SQL database from the web, use the URL http://IP_Address:9000. In case you are unable to access the service, allow the port through the firewall.

The QuestDB dashboard will appear as shown.

From the web console, you can execute queries. For example:

• Creating a table

CREATE TABLE sensors (ID LONG, make STRING, city STRING);

Once the table has been 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
;

Once the commands have been written, execute them by clicking on Run

You should have the data added to the table as shown.

• View data in Tables

To view the data added to the table, use the query below:

SELECT * FROM sensors WHERE city='Nairobi';

Execution Output:

b. Connect to QuestDB SQL database using REST API

The REST API is based on HTTP and provides the best and simple way to interact with QuestDB. There are several commands and queries you can execute here as well.

To import data, you can use the command with the syntax below. Replace file.csv with the name of the file to import.

curl -F data=@file.csv \
'http://localhost:9000/imp'

To create a table use the command:

curl -G \
  --data-urlencode "query=CREATE TABLE sensors2 (ID LONG, make STRING, city STRING);" \
  --data-urlencode "count=true" \
  http://localhost:9000/exec

Insert data into the created table:

curl -G \
  --data-urlencode "query=INSERT INTO sensors2
    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

View the data in the table:

curl -G \
  --data-urlencode "query=SELECT * FROM sensors2 WHERE city='Bamako' limit 5;" \
  --data-urlencode "count=true" \
  http://localhost:9000/exec

Execution output:

Books For Learning Kubernetes Administration:

• Best Kubernetes Study books

Closing Thoughts

That marks the end of this detailed guide. At this point, you should be able to install QuestDB database on Kubernetes. You should also be able to expose and access the QuestDB service. I hope this was insightful.

See more:

• How To Run QuestDB SQL database in Docker Container
• Run immudb SQL and Key-Value Database on Docker / Kubernetes
• How To Install MongoDB 6 on Debian