Fast Analytics with MariaDB ColumnStore
Published: 2024-01-19 13:33:36

Slow query times in large datasets are a common headache in database management. [MariaDB ColumnStore](https://mariadb.com/products/columnstore/) offers a neat way out of this. It's a columnar storage engine that significantly speeds up data analytics. Typically, you can improve query performance in relational databases by adding appropriate indexes. However, maintaining indexes is hard, especially with ad-hoc queries where you don't really know where indexes are going to be needed. ColumnStore eases this pain. It's as if you had an index on each column, but without the hassle of creating and updating them. The price to pay? Well, inserts are not as fast as with [InnoDB](https://mariadb.com/kb/en/innodb/), so this is not the best option for operational/transactional databases but rather for analytical ones. [Bulk inserts](https://mariadb.com/docs/server/ref/col/cli/cpimport/) are very fast though.

There's plenty of [online documentation](https://mariadb.com/docs/columnstore/) about ColumnStore, so I won't go through all the details on how it works or how to deploy it on production. Instead, in this article, I'll show you how to try MariaDB ColumnStore on your computer using [Docker](https://hub.docker.com/r/mariadb/columnstore).

## Prerequisites

You'll need:

- The [`mariadb` command line tool](https://mariadb.com/docs/server/connect/clients/mariadb-client/)
- [Docker](https://www.docker.com/products/docker-desktop/)

## Setting up MariaDB ColumnStore

1. Run a container with MariaDB + ColumnStore:

```bash
docker run -d -p 3307:3306 -e PM1=mcs1 --hostname=mcs1 --name mcs1 mariadb/columnstore
```

This command runs a new Docker container using the official ColumnStore image, with several specified options:

- `docker run`: Starts a new Docker container.
- `-d`: Runs the container in detached mode (in the background).
- `-p 3307:3306`: Maps port 3307 on the host (your computer) to port 3306 inside the container. This makes the database accessible on port `3307` on the host machine.
- `-e PM1=mcs1`: The `PM1` environment variable `PM1` specifies the primary database node (`mcs1`).
- `--hostname=mcs1`: Sets the hostname of the container to `mcs1`.
- `--name mcs1`: Names the container `mcs1`.
- `mariadb/columnstore`: Specifies the Docker image to use, in this case, an image for MariaDB with the ColumnStore storage engine.

2. Provision ColumnStore:

```bash
docker exec -it mcs1 provision mcs1
```

The command `docker exec` is used to interact with a running Docker container. This is what each option does:

- `docker exec`: Executes a command in a running container.
- `-it`: This option ensures the command is run in interactive mode with a terminal.
- `mcs1` (first occurrence): This is the name of the Docker container in which the command is to be executed.
- `provision mcs1` This is the specific command being executed inside the container. `provision` is a script included in the Docker image that initialize and configure the MariaDB ColumnStore environment within the container. The argument `mcs1` is passed to the `provision` command to specify the host for the MariaDB server within the Docker container.

3. Connect to the MariaDB server using the default credentials defined in the MariaDB ColumnStore Docker image:

```bash
mariadb -h 127.0.0.1 -P 3307 -u admin -p'C0lumnStore!'
```

1. Check that ColumnStore is available as a storage engine by running the following SQL sentence:

```bash
SHOW ENGINES;
```

## Setting up a demo database

1. Create the `operations` database and its InnoDB tables:

```sql
CREATE DATABASE operations;

CREATE TABLE operations.doctors(
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL CHECK(TRIM(name) != '')
) ENGINE=InnoDB;

CREATE TABLE operations.appointments(
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL CHECK(TRIM(name) != ''),
    phone_number VARCHAR(15) NOT NULL CHECK(phone_number RLIKE '[0-9]+'),
    email VARCHAR(254) NOT NULL CHECK(TRIM(email) != ''),
    time DATETIME NOT NULL,
    reason ENUM('Consultation', 'Follow-up', 'Preventive', 'Chronic') NOT NULL,
    status ENUM ('Scheduled', 'Canceled', 'Completed', 'No Show'),
    doctor_id BIGINT UNSIGNED NOT NULL,
    CONSTRAINT fk_appointments_doctors FOREIGN KEY (doctor_id) REFERENCES doctors(id)
) ENGINE=InnoDB;
```

2. Create the `analytics` database and its ColumnStore table:

```sql
CREATE DATABASE analytics;

CREATE TABLE analytics.appointments(
    id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(200) NOT NULL,
    phone_number VARCHAR(15) NOT NULL,
    email VARCHAR(254) NOT NULL,
    time DATETIME NOT NULL,
    reason VARCHAR(15) NOT NULL,
    status VARCHAR(10) NOT NULL,
    doctor_id BIGINT UNSIGNED NOT NULL
) ENGINE=ColumnStore;
```

You can use the same database (or schema, they are [synonyms](https://mariadb.com/kb/en/create-database/) in MariaDB) for both the InnoDB and ColumnStore tables if you prefer. Use a different name for the ColumnStore table if you opt for this alternative.

## Inserting demo data

1. Insert a few doctors:

```sql
INSERT INTO operations.doctors(name)
VALUES ("Maria"), ("John"), ("Jane");
```

2. Create a new file with the name `test_data_insert.py` with the following content:

```python
import random
import os
import subprocess
from datetime import datetime, timedelta

# Function to generate a random date within a given range
def random_date(start, end):
    return start + timedelta(days=random.randint(0, int((end - start).days)))

# Function to execute a given SQL command using MariaDB
def execute_sql(sql):
    # Write the SQL command to a temporary file
    with open("temp.sql", "w") as file:
        file.write(sql)
    # Execute the SQL command using the MariaDB client
    subprocess.run(["mariadb", "-h", "127.0.0.1", "-P", "3307", "-u", "admin", "-pC0lumnStore!", "-e", "source temp.sql"])
    # Remove the temporary file
    os.remove("temp.sql")

print("Generating and inserting data...")

# Total number of rows to be inserted
total_rows = 4000000
# Number of rows to insert in each batch
batch_size = 10000

# Possible values for the 'reason' column and their associated weights for random selection
reasons = ["Consultation", "Follow-up", "Preventive", "Chronic"]
reason_weights = [0.5, 0.15, 0.25, 0.1]

# Possible values for the 'status' column and their associated weights for random selection
statuses = ["Scheduled", "Canceled", "Completed", "No Show"]
status_weights = [0.1, 0.15, 0.7, 0.05]

# Possible values for the 'doctor_id' column and their associated weights for random selection
doctors = [1, 2, 3]
doctors_weights = [0.4, 0.35, 0.25]

# List of patient names
names = [f"Patient_{i}" for i in range(total_rows)]

# Insert data in batches
for batch_start in range(0, total_rows, batch_size):
    batch_values = []

    # Generate data for each row in the batch
    for i in range(batch_start, min(batch_start + batch_size, total_rows)):
        name = names[i]
        phone_number = f"{random.randint(100, 999)}-{random.randint(100, 999)}-{random.randint(1000, 9999)}"
        email = f"patient_{i}@example.com"
        time = random_date(datetime(2023, 1, 1), datetime(2024, 1, 1)).strftime("%Y-%m-%d %H:%M:%S")
        reason = random.choices(reasons, reason_weights)[0]
        status = random.choices(statuses, status_weights)[0]
        doctor_id = random.choices(doctors, doctors_weights)[0]

        # Append the generated row to the batch
        batch_values.append(f"('{name}', '{phone_number}', '{email}', '{time}', '{reason}', '{status}', {doctor_id})")

    # SQL command to insert the batch of data into the 'appointments' table
    sql = "USE operations;\nINSERT INTO appointments (name, phone_number, email, time, reason, status, doctor_id) VALUES " + ", ".join(batch_values) + ";"
    # Execute the SQL command
    execute_sql(sql)
    # Print progress
    print(f"Inserted up to row {min(batch_start + batch_size, total_rows)}")

print("Data insertion complete.")
```

3. Insert 4 million appointments by running the Python script:

```bash
python3 test_data_insert.py
```

4. Populate the ColumnStore table by connecting to the database and running:

```sql
INSERT INTO analytics.appointments (
    id,
    name,
    phone_number,
    email,
    time,
    reason,
    status,
    doctor_id
)
SELECT
    appointments.id,
    appointments.name,
    appointments.phone_number,
    appointments.email,
    appointments.time,
    appointments.reason,
    appointments.status,
    appointments.doctor_id
FROM operations.appointments;
```

## Run cross-engine SQL queries

MariaDB ColumnStore is designed to run in a cluster of multiple servers. It is there where you see massive performance gains in analytical queries. However, we can also see this in action with the single-node setup of this article.

1. Run the following query and pay attention to the time it needs to complete (make sure it queries the `operations` database):

```sql
SELECT doctors.name, status, COUNT(*) AS count
FROM operations.appointments -- use the InnoDB table
JOIN doctors ON doctor_id = doctors.id
WHERE status IN (
    'Scheduled',
    'Canceled',
    'Completed',
    'No Show'
)
GROUP BY doctors.name, status
ORDER BY doctors.name, status;
```

On my machine, it took around 3 seconds.

2. Now modify the query to use the ColumnStore table instead (in the `analytics` database):

```sql
SELECT doctors.name, status, COUNT(*) AS count
FROM analytics.appointments -- use the ColumnStore table
JOIN doctors ON doctor_id = doctors.id
WHERE status IN (
    'Scheduled',
    'Canceled',
    'Completed',
    'No Show'
)
GROUP BY doctors.name, status
ORDER BY doctors.name, status;
```

It takes less than a second. Of course, you can speed up the first query by adding an index in this simplistic example, but imagine the situation in which you have hundreds of tables—it will become harder and harder to manage indexes. ColumnStore removes this complexity.