Skip to content
Klutch.sh Docs

Deploying ColumnStore

Introduction

MariaDB ColumnStore is a powerful open-source columnar storage engine designed for massively parallel distributed data processing. Built for analytical workloads and data warehousing, ColumnStore excels at business intelligence queries, often delivering 100x faster performance than traditional row-based databases. Deploying ColumnStore on Klutch.sh provides scalable infrastructure for your analytics with persistent storage and automated deployments.

Prerequisites

Installation and Setup

Create Your Dockerfile

Create a Dockerfile in your project root directory:

FROM mariadb/columnstore:latest


# Set environment variables for ColumnStore configuration
ENV MARIADB_ROOT_PASSWORD=rootpassword
ENV MARIADB_DATABASE=analytics
ENV MARIADB_USER=csuser
ENV MARIADB_PASSWORD=cspassword


# Expose the MariaDB/ColumnStore port
EXPOSE 3306

Push to GitHub

Commit your Dockerfile to your GitHub repository:

Terminal window
git add Dockerfile
git commit -m "Add ColumnStore Dockerfile"
git push origin main

Deploying to Klutch.sh

    1. Log in to Klutch.sh

      Navigate to klutch.sh/app and sign in to your account.

    2. Create a New Project

      Go to Create Project and give your project a name (e.g., “ColumnStore Analytics”).

    3. Create a New App

      Navigate to Create App and configure your deployment.

    4. Select Your Repository

      • Choose GitHub as your Git source
      • Select the repository containing your Dockerfile
      • Choose the branch to deploy (usually main)

    5. Configure Traffic Type

      • Traffic Type: Select TCP (ColumnStore requires TCP for database connections)
      • Internal Port: Set to 3306 (the default MariaDB/ColumnStore port)

    6. Set Environment Variables

      Configure your ColumnStore settings:

      • MARIADB_ROOT_PASSWORD: Strong password for the root user
      • MARIADB_DATABASE: Database name (e.g., analytics)
      • MARIADB_USER: Application database username
      • MARIADB_PASSWORD: Strong password for the database user

    7. Attach a Persistent Volume

      • In the Volumes section, click “Add Volume”
      • Mount Path: /var/lib/mysql (where ColumnStore stores database files)
      • Size: Choose based on your data volume (e.g., 20GB, 50GB)

    8. Deploy

      Click “Create” to deploy. Klutch.sh will automatically detect your Dockerfile, build the image, and start your ColumnStore database.

    9. Access Your Database

      Connect to your ColumnStore database using port 8000:

      ******example-app.klutch.sh:8000/analytics

Connecting to ColumnStore

Once deployed, connect from your applications using the following examples:

Node.js (mysql2):

const mysql = require('mysql2/promise');


const connection = await mysql.createConnection({
  host: 'example-app.klutch.sh',
  port: 8000,
  user: 'csuser',
  password: 'cspassword',
  database: 'analytics'
});


// Run analytical queries
const [rows] = await connection.execute(
  'SELECT category, SUM(amount) as total FROM sales GROUP BY category'
);

Python (mysql-connector):

import mysql.connector


conn = mysql.connector.connect(
    host="example-app.klutch.sh",
    port=8000,
    user="csuser",
    ******,
    database="analytics"
)


cursor = conn.cursor()
cursor.execute("SELECT * FROM sales WHERE date >= '2024-01-01'")
rows = cursor.fetchall()

Production Best Practices

  • Security: Use strong, unique passwords for all database users
  • Backups: Implement regular backup strategy using mysqldump or mariabackup
  • Monitoring: Track query performance, disk usage, and resource utilization
  • Optimization: Create appropriate indexes and use column projection in queries
  • Storage: Monitor disk space growth and adjust volume size as needed

Additional Resources