Análisis de Datos Rápido con MariaDB ColumnStore

Tabla de contenido

Tiempos de consulta lentos en bases de datos grandes son un dolor de cabeza común. MariaDB ColumnStore ofrece una salida ingeniosa a este problema. Es un motor de almacenamiento columnar que acelera el análisis de datos significativamente. Normalmente es posible mejorar el rendimiento de una consultas a una base de datos relacional usando índices donde sea apropiado. Sin embargo, mantener índices es difícil, especialmente con consultas “ad-hoc” donde realmente no se sabe dónde se necesitará un determinado índice. ColumnStore ayuda con esto. Es como si tuvieras un índice en cada columna, pero sin la complejidad asociada a crearlos y actualizarlos. ¿El precio a pagar? Bueno, las inserciones no son tan rápidas como con InnoDB, por lo que no es la mejor opción para bases de datos operacionales/transaccionales sino más bien para las analíticas. Sin embargo, las inserciones en lote son muy rápidas.

Hay suficiente documentación en línea sobre ColumnStore, así que no entraré detalles sobre cómo funciona o cómo desplegar en producción. En este artículo, mostraré cómo probar MariaDB ColumnStore localmente usando Docker.

Prerrequisitos
#

Necesitas:

El comando mariadb
Docker

Configurando MariaDB ColumnStore
#

Ejecuta un contenedor con MariaDB + ColumnStore:

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

Este comando corre un nuevo contenedor Docker usando la imagen oficial de ColumnStore con varias opciones especificadas:

docker run: Inicia un nuevo contenedor Docker.
-d: Ejecuta el contenedor en modo “detached” (en segundo plano).
-p 3307:3306: Mapea el puerto 3307 en el host (tu computadora) al puerto 3306 dentro del contenedor. Esto hace que la base de datos sea accesible en el puerto 3307 en la máquina host.
-e PM1=mcs1: La variable de entorno PM1 especifica el nodo principal de la base de datos (mcs1).
--hostname=mcs1: Establece el nombre de host del contenedor como mcs1.
--name mcs1: Nombra el contenedor como mcs1.
mariadb/columnstore: Especifica la imagen de Docker a utilizar, en este caso, una imagen para MariaDB con el motor de almacenamiento ColumnStore.

Provisiona ColumnStore:

docker exec -it mcs1 provision mcs1

El comando docker exec se usa para interactuar con un contenedor Docker en ejecución. Esto es lo que hace cada opción:

docker exec: Ejecuta un comando en un contenedor en ejecución.
-it: Esta opción asegura que el comando se ejecute en modo interactivo con un terminal.
mcs1 (primera aparición): Este es el nombre del contenedor Docker en el que se ejecutará el comando.
provision mcs1 Este es el comando específico que se ejecuta dentro del contenedor. provision es un script incluido en la imagen Docker que inicializa y configura el entorno de MariaDB ColumnStore dentro del contenedor. El argumento mcs1 se pasa al comando provision para especificar el host del servidor MariaDB dentro del contenedor Docker.

Conéctate al servidor MariaDB utilizando las credenciales predeterminadas definidas en la imagen Docker de MariaDB ColumnStore:

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

Verifica que ColumnStore esté disponible como motor de almacenamiento ejecutando la siguiente sentencia SQL:

SHOW ENGINES;

Configurando una base de datos de demostración
#

Crea la base de datos operations y sus tablas InnoDB:

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;

Crea la base de datos analytics y su tabla ColumnStore:

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;

Puedes usar la misma base de datos (o esquema, son sinónimos en MariaDB) para ambas tablas InnoDB y ColumnStore si lo prefieres. Usa un nombre diferente para la tabla de ColumnStore si optas por esta alternativa.

Insertando datos de demostración
#

Inserta algunos doctores:

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

Crea un nuevo archivo con el nombre test_data_insert.py con el siguiente contenido:

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

# Función para generar una fecha aleatoria dentro de un rango dado
def random_date(start, end):
    return start + timedelta(days=random.randint(0, int((end - start).days)))

# Función para ejecutar un comando SQL dado usando MariaDB
def execute_sql(sql):
    # Escribe el comando SQL en un archivo temporal
    with open("temp.sql", "w") as file:
        file.write(sql)
    # Ejecuta el comando SQL usando el cliente MariaDB
    subprocess.run(["mariadb", "-h", "127.0.0.1", "-P", "3307", "-u", "admin", "-pC0lumnStore!", "-e", "source temp.sql"])
    # Elimina el archivo temporal
    os.remove("temp.sql")

print("Generando e insertando datos...")

# Número total de filas a insertar
total_rows = 4000000
# Número de filas a insertar en cada lote
batch_size = 10000

# Valores posibles para la columna 'reason' y sus pesos asociados para la selección aleatoria
reasons = ["Consultation", "Follow-up", "Preventive", "Chronic"]
reason_weights = [0.5, 0.15, 0.25, 0.1]

# Valores posibles para la columna 'status' y sus pesos asociados para la selección aleatoria
statuses = ["Scheduled", "Canceled", "Completed", "No Show"]
status_weights = [0.1, 0.15, 0.7, 0.05]

# Valores posibles para la columna 'doctor_id' y sus pesos asociados para la selección aleatoria
doctors = [1, 2, 3]
doctors_weights = [0.4, 0.35, 0.25]

# Lista de nombres de pacientes
names = [f"Paciente_{i}" for i in range(total_rows)]

# Inserta datos en lotes
for batch_start in range(0, total_rows, batch_size):
    batch_values = []

    # Genera datos para cada fila en el lote
    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"paciente_{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]

        # Agrega la fila generada al lote
        batch_values.append(f"('{name}', '{phone_number}', '{email}', '{time}', '{reason}', '{status}', {doctor_id})")

    # Comando SQL para insertar el lote de datos en la tabla 'appointments'
    sql = "USE operations;\nINSERT INTO appointments (name, phone_number, email, time, reason, status, doctor_id) VALUES " + ", ".join(batch_values) + ";"
    # Ejecuta el comando SQL
    execute_sql(sql)
    # Imprime el progreso
    print(f"Insertadas hasta la fila {min(batch_start + batch_size, total_rows)}")

print("Inserción de datos completa.")

Inserta 4 millones de citas ejecutando el script de Python:

python3 test_data_insert.py

Puebla la tabla ColumnStore conectándote a la base de datos y ejecutando:

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;

Ejecuta consultas SQL cruzadas entre motores
#

MariaDB ColumnStore está diseñado para funcionar en un clúster con múltiples servidores. Es allí donde se ven ganancias masivas en rendimiento al ejecutar consultas analíticas. Sin embargo, podemos ver esto en acción con la configuración de un solo nodo usada en este artículo.

Ejecuta la siguiente consulta y presta atención al tiempo que necesita para completarse (asegúrate de que consulte la base de datos operations):

SELECT doctors.name, status, COUNT(*) AS count
FROM operations.appointments -- usa la tabla de InnoDB
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;

En mi máquina, tardó alrededor de 3 segundos.

Ahora modifica la consulta para usar la tabla ColumnStore en su lugar (en la base de datos analytics):

SELECT doctors.name, status, COUNT(*) AS count
FROM analytics.appointments -- usa la tabla de ColumnStore
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;

Tarda menos de un segundo. Por supuesto, puedes acelerar la primera consulta agregando un índice en este ejemplo simplista, pero imagina la situación en la que tengas cientos de tablas; se volverá cada vez más difícil gestionar índices. ColumnStore elimina esta complejidad.