ClickHouse Tutorial for Beginners: Ultimate Guide to Get Started in 2025

5. Creating Your First Database

In this section of our ClickHouse tutorial for beginners, let’s create a database and table to track website analytics. This is a real-world example you can use immediately.

1Create a Database

This part of this ClickHouse tutorial for beginners covers table creation.

-- Create database
CREATE DATABASE analytics;

-- Switch to the database
USE analytics;

-- Verify it was created
SHOW DATABASES;

2Create a Table

In this practical section of our ClickHouse tutorial for beginners, let’s create a table to store page view events:

CREATE TABLE page_views (
    event_time DateTime,
    user_id UInt32,
    page_url String,
    country String,
    device String,
    session_duration UInt32
) ENGINE = MergeTree()
ORDER BY (event_time, user_id);

Let’s break this down:

• DateTime – Timestamp of the event
• UInt32 – Unsigned 32-bit integer (perfect for IDs)
• String – Variable-length text
• ENGINE = MergeTree() – Use the MergeTree engine
• ORDER BY (event_time, user_id) – How data is sorted on disk (critical for query performance!)

Continuing with this ClickHouse tutorial for beginners…

3Insert Sample Data

This part of this ClickHouse tutorial for beginners covers insertion of sample data

-- Insert sample data
INSERT INTO page_views VALUES
    ('2025-01-01 10:00:00', 1001, '/home', 'USA', 'mobile', 45),
    ('2025-01-01 10:05:00', 1002, '/products', 'UK', 'desktop', 120),
    ('2025-01-01 10:10:00', 1001, '/cart', 'USA', 'mobile', 30),
    ('2025-01-01 10:15:00', 1003, '/home', 'Canada', 'tablet', 60),
    ('2025-01-01 10:20:00', 1002, '/checkout', 'UK', 'desktop', 90);

-- Verify the data
SELECT * FROM page_views LIMIT 5;

Continuing with this ClickHouse tutorial for beginners…

6. Understanding Data Types

As you’ll learn in this ClickHouse tutorial for beginners, ClickHouse has many data types optimized for different scenarios. Here are the most important ones for beginners:

Numeric Types

Date & Time Types

String Types

• String – Variable-length strings (most common)
• FixedString(N) – Fixed-length strings (like char codes)
• Enum8 / Enum16 – Predefined set of values (saves space)

Array & Complex Types

-- Array of integers
tags Array(String)

-- Nested structure
user Tuple(id UInt32, name String, email String)

-- Array of tuples
events Array(Tuple(name String, timestamp DateTime))

7. Writing Queries

This ClickHouse tutorial for beginners will now show you how to query data. ClickHouse uses SQL with some powerful extensions.

Basic SELECT Queries

-- Simple select
SELECT * FROM page_views LIMIT 10;

-- Select specific columns
SELECT event_time, page_url, country 
FROM page_views 
LIMIT 5;

-- Filter with WHERE
SELECT * FROM page_views 
WHERE country = 'USA' 
  AND device = 'mobile';

-- Count rows
SELECT count() FROM page_views;

-- Unique countries
SELECT DISTINCT country FROM page_views;

Aggregation Queries

Continue with ClickHouse tutorial for beginners…

This is where ClickHouse really shines! Aggregations are lightning-fast.

-- Page views by country
SELECT 
    country,
    count() as views
FROM page_views
GROUP BY country
ORDER BY views DESC;

-- Average session duration by device
SELECT 
    device,
    avg(session_duration) as avg_duration,
    count() as total_sessions
FROM page_views
GROUP BY device;

-- Page views per hour
SELECT 
    toStartOfHour(event_time) as hour,
    count() as views
FROM page_views
GROUP BY hour
ORDER BY hour;

Time-Series Analysis

As you’ve learned throughout this ClickHouse tutorial for beginners, ClickHouse has amazing time functions:

-- Daily page views
SELECT 
    toDate(event_time) as date,
    count() as views
FROM page_views
GROUP BY date
ORDER BY date;

-- Views by day of week
SELECT 
    toDayOfWeek(event_time) as day_of_week,
    count() as views
FROM page_views
GROUP BY day_of_week
ORDER BY day_of_week;

-- Last 7 days
SELECT 
    toDate(event_time) as date,
    count() as views
FROM page_views
WHERE event_time >= now() - INTERVAL 7 DAY
GROUP BY date;

Advanced Aggregations

-- Top 10 pages
SELECT 
    page_url,
    count() as views,
    uniq(user_id) as unique_users,
    avg(session_duration) as avg_duration
FROM page_views
GROUP BY page_url
ORDER BY views DESC
LIMIT 10;

-- Percentiles
SELECT 
    quantile(0.5)(session_duration) as median,
    quantile(0.95)(session_duration) as p95,
    quantile(0.99)(session_duration) as p99
FROM page_views;

JOINs in ClickHouse

Let’s create a users table and join it with page views:

-- Create users table
CREATE TABLE users (
    user_id UInt32,
    name String,
    signup_date Date
) ENGINE = MergeTree()
ORDER BY user_id;

-- Insert sample users
INSERT INTO users VALUES
    (1001, 'Alice', '2024-12-01'),
    (1002, 'Bob', '2024-12-15'),
    (1003, 'Charlie', '2025-01-01');

-- Join page views with user names
SELECT 
    u.name,
    count() as page_views
FROM page_views pv
JOIN users u ON pv.user_id = u.user_id
GROUP BY u.name
ORDER BY page_views DESC;

8. Performance Optimization

As covered in this ClickHouse tutorial for beginners, ClickHouse is fast by default, but these tips will make it even faster:

1. Choose the Right ORDER BY

-- Good: Columns you filter by most
CREATE TABLE events (
    timestamp DateTime,
    user_id UInt32,
    event_type String
) ENGINE = MergeTree()
ORDER BY (timestamp, user_id);  -- ✅ Optimized for time-range queries

-- Bad: Random order
ORDER BY event_type;  -- ❌ Not useful for typical queries

2. Use PARTITION BY for Time-Series Data

CREATE TABLE events (
    timestamp DateTime,
    user_id UInt32,
    event_type String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)  -- Partition by month
ORDER BY (timestamp, user_id);

Why partition? You can delete old data quickly and queries on recent data are faster.

3. Add Indexes for Better Performance

-- Add index on frequently filtered column
ALTER TABLE events 
ADD INDEX idx_event_type event_type TYPE set(100) GRANULARITY 4;

-- Bloom filter index for string searches
ALTER TABLE events 
ADD INDEX idx_url page_url TYPE bloom_filter() GRANULARITY 4;

4. Use Materialized Views for Pre-Aggregation

-- Create aggregated table
CREATE TABLE daily_stats (
    date Date,
    country String,
    total_views UInt64,
    unique_users UInt64
) ENGINE = SummingMergeTree()
ORDER BY (date, country);

-- Create materialized view to auto-populate
CREATE MATERIALIZED VIEW daily_stats_mv TO daily_stats AS
SELECT 
    toDate(event_time) as date,
    country,
    count() as total_views,
    uniq(user_id) as unique_users
FROM page_views
GROUP BY date, country;

Performance Chart

5. Batch Inserts

-- Bad: One row at a time (slow)
INSERT INTO events VALUES (...);
INSERT INTO events VALUES (...);
INSERT INTO events VALUES (...);

-- Good: Batch insert (fast)
INSERT INTO events VALUES
    (...),
    (...),
    (...);

CREATE TABLE orders (
    order_id UInt64,
    customer_id UInt32,
    order_date DateTime,
    total_amount Decimal(10, 2),
    product_category String,
    country String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(order_date)
ORDER BY (order_date, customer_id);

-- Insert sample data
INSERT INTO orders VALUES
    (1001, 5001, '2025-01-01 10:00:00', 99.99, 'Electronics', 'USA'),
    (1002, 5002, '2025-01-01 11:30:00', 149.99, 'Clothing', 'UK'),
    (1003, 5001, '2025-01-02 14:20:00', 79.99, 'Electronics', 'USA'),
    (1004, 5003, '2025-01-02 16:45:00', 199.99, 'Home', 'Canada');

-- Daily revenue
SELECT 
    toDate(order_date) as date,
    sum(total_amount) as revenue
FROM orders
GROUP BY date
ORDER BY date;

-- Revenue by category
SELECT 
    product_category,
    count() as orders,
    sum(total_amount) as revenue,
    avg(total_amount) as avg_order_value
FROM orders
GROUP BY product_category
ORDER BY revenue DESC;

-- Customer lifetime value
SELECT 
    customer_id,
    count() as total_orders,
    sum(total_amount) as lifetime_value,
    min(order_date) as first_order,
    max(order_date) as last_order
FROM orders
GROUP BY customer_id
ORDER BY lifetime_value DESC
LIMIT 10;

CREATE TABLE app_logs (
    timestamp DateTime64(3),
    level Enum8('DEBUG'=1, 'INFO'=2, 'WARNING'=3, 'ERROR'=4),
    service String,
    message String,
    user_id UInt32
) ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(timestamp)
ORDER BY (timestamp, service, level);

-- Error rate by service
SELECT 
    service,
    countIf(level = 'ERROR') as errors,
    count() as total_logs,
    (errors / total_logs) * 100 as error_rate
FROM app_logs
WHERE timestamp >= now() - INTERVAL 1 HOUR
GROUP BY service
ORDER BY error_rate DESC;

-- Top error messages
SELECT 
    message,
    count() as occurrences
FROM app_logs
WHERE level = 'ERROR'
  AND timestamp >= now() - INTERVAL 24 HOUR
GROUP BY message
ORDER BY occurrences DESC
LIMIT 20;

CREATE TABLE sensor_data (
    timestamp DateTime,
    sensor_id UInt32,
    temperature Float32,
    humidity Float32,
    location String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (timestamp, sensor_id);

-- Average temperature by hour
SELECT 
    toStartOfHour(timestamp) as hour,
    avg(temperature) as avg_temp,
    max(temperature) as max_temp,
    min(temperature) as min_temp
FROM sensor_data
WHERE timestamp >= now() - INTERVAL 24 HOUR
GROUP BY hour
ORDER BY hour;

-- Detect anomalies (temperature > 2 standard deviations)
SELECT 
    sensor_id,
    timestamp,
    temperature,
    avg(temperature) OVER (PARTITION BY sensor_id) as avg_temp,
    stddevPop(temperature) OVER (PARTITION BY sensor_id) as stddev_temp
FROM sensor_data
WHERE temperature > avg_temp + (2 * stddev_temp)
ORDER BY timestamp DESC;

-- Check table sizes
SELECT 
    database,
    table,
    formatReadableSize(sum(bytes)) as size
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY sum(bytes) DESC;

-- Monitor running queries
SELECT 
    query,
    elapsed,
    read_rows,
    formatReadableSize(read_bytes) as read
FROM system.processes
WHERE query NOT LIKE '%system.processes%';

-- Check disk usage
SELECT 
    name,
    path,
    formatReadableSize(free_space) as free,
    formatReadableSize(total_space) as total
FROM system.disks;