How to Migrate from MariaDB to ClickHouse

Galaxy Glossary

How do I migrate data from MariaDB to ClickHouse?

Move schema and data from MariaDB to ClickHouse using the MySQL table engine, clickhouse-mysql, and INSERT…SELECT.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Thank you! Your submission has been received!

Oops! Something went wrong while submitting the form.

Description

Why choose ClickHouse over MariaDB?

ClickHouse delivers sub-second analytics over billions of rows thanks to columnar storage and vectorized execution. Migrating workloads that are read-heavy, dashboard-centric, or aggregation-focused can slash query latency and hardware cost.

What prerequisites are required?

Prepare network connectivity between the MariaDB and ClickHouse hosts, ensure identical time zones, and grant SELECT rights on the MariaDB source. Install clickhouse-client and, optionally, the clickhouse-mysql migration utility.

How do I mirror a MariaDB table inside ClickHouse?

Step 1—Create a MySQL engine table

The ENGINE = MySQL table acts as a live pointer to the MariaDB source, letting you read data without exporting files.

Step 2—Create a native ClickHouse table

Define columns with optimal ClickHouse types—including UInt, Decimal, and DateTime—and pick a primary key for the MergeTree engine.

Step 3—Copy data with INSERT…SELECT

Run a single INSERT INTO … SELECT * to batch-load rows.Use SET max_insert_block_size and max_threads to tune throughput.

.

How do I bulk-load multiple tables?

The clickhouse-mysql tool scans MariaDB metadata, creates matching MergeTree tables, and streams data in parallel. Flags like --migrate-data and --skip-sync control behavior.

How do I keep data in sync during cut-over?

Schedule incremental jobs that insert only new or updated rows using WHERE updated_at > last_cutover, or switch to Materialized Views that consume binlog-based Kafka streams.

Best practices for production moves

Validate row counts after each load, cast ENUMs to LowCardinality(String), disable MariaDB triggers during export, and checkpoint replication lags before flipping traffic.

Why How to Migrate from MariaDB to ClickHouse is important

How to Migrate from MariaDB to ClickHouse Example Usage


-- Migrate Orders table
CREATE TABLE maria_orders
ENGINE = MySQL('maria-host:3306', 'shop', 'Orders', 'maria_user', 'maria_pwd');

CREATE TABLE orders
(
    id UInt32,
    customer_id UInt32,
    order_date Date,
    total_amount Decimal(10,2)
) ENGINE = MergeTree
ORDER BY (order_date, id);

INSERT INTO orders SELECT * FROM maria_orders;

How to Migrate from MariaDB to ClickHouse Syntax


-- 1. Reference original table via MySQL engine
CREATE TABLE maria_customers
ENGINE = MySQL('maria-host:3306', 'shop', 'Customers', 'maria_user', 'maria_pwd');

-- 2. Define destination table with optimal ClickHouse types
CREATE TABLE customers
(
    id UInt32,
    name String,
    email String,
    created_at DateTime
) ENGINE = MergeTree
ORDER BY id;

-- 3. Copy data
INSERT INTO customers SELECT * FROM maria_customers;

-- 4. Automate multiple tables with clickhouse-mysql
clickhouse-mysql --host=maria-host --port=3306 --database=shop \
  --clickhouse-host=ch-host --clickhouse-database=shop --migrate-data

Common Mistakes

Treating VARCHAR as String without length review. ClickHouse stores full strings; migrate large text columns to LowCardinality(String) when cardinality is low to save space.
Copying data before creating proper ORDER BY keys. Without a good primary key, MergeTree read performance suffers. Always align ORDER BY with common query filters.

Frequently Asked Questions (FAQs)

Can I migrate without stopping writes on MariaDB?

Yes. Use MySQL engine tables for an initial bulk load, then run incremental INSERTs based on an updated_at column until cut-over.

Does ClickHouse support foreign keys?

No. Referential integrity must be enforced at the application layer or during ETL.

How large can a single INSERT be?

Up to several gigabytes, but optimal batches are 100–500 MB. Adjust max_insert_block_size to balance memory and throughput.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.