Getting Started with ClickHouse: Your First Queries and Schema Design

This post walks you through installing ClickHouse, creating your first table, inserting data, and writing queries that actually return useful results fast.

Reading about ClickHouse is one thing. Running your first query and seeing it return in milliseconds is another. By the end of this post, you will have a working ClickHouse table, real data inside it, and a handful of queries that show you what ClickHouse can do. No prior ClickHouse experience needed.

How do you install ClickHouse?

There are two straightforward ways to get ClickHouse running locally. Pick the one that fits your setup.

Option 1: Install directly on Linux or macOS

On Ubuntu or Debian:

sudo apt-get update
sudo apt-get install -y apt-transport-https ca-certificates curl gnupg

curl -fsSL 'https://packages.clickhouse.com/rpm/lts/repodata/repomd.xml.key' \
  | sudo gpg --dearmor -o /usr/share/keyrings/clickhouse-keyring.gpg

ARCH=$(dpkg --print-architecture)
echo "deb [signed-by=/usr/share/keyrings/clickhouse-keyring.gpg arch=${ARCH}] https://packages.clickhouse.com/deb stable main" \
  | sudo tee /etc/apt/sources.list.d/clickhouse.list

sudo apt-get update
sudo apt-get install -y clickhouse-server clickhouse-client
sudo service clickhouse-server start

Run these commands separately in a new local practice installation and stop if a command fails. Complete any installer prompts. If a ClickHouse keyring or repository entry already exists, inspect that configuration instead of overwriting it. After the server starts, open the client with clickhouse-client. If you configured a password, use clickhouse-client --password to enter it interactively.
On macOS, use the official quick-install route for local learning. In a new practice directory, download the ClickHouse binary and start its server:

curl https://clickhouse.com/ | CLICKHOUSE_ONLY=1 sh
./clickhouse server

Leave that terminal running. Open a second terminal in the same directory and connect:

./clickhouse client

This runs the downloaded installer from ClickHouse’s official site. The server stores practice data in its working directory. Use the Docker route instead if you prefer a container and already have Docker running.
Option 2: Run with Docker

If you prefer to keep things contained, Docker is the fastest way to get a ClickHouse instance running without touching your system packages:

docker run -d \
  --name clickhouse-server \
  --ulimit nofile=262144:262144 \
  clickhouse/clickhouse-server:latest

Once the server has finished starting, inspect its logs and open the client inside the container:

docker logs clickhouse-server
docker exec -it clickhouse-server clickhouse-client

If startup is still in progress, wait and retry the client command after checking the logs. A host-installed ClickHouse client is not required. This learning example does not publish the server’s ports to the host.

After the selected route’s client starts, check the connection:

SELECT version();
SELECT 1;

Record the reported server version. The second query should return 1. Continue to the table-creation example only after both commands succeed.

What scenario will we use to learn with?

Throughout this post, we will work with a subscription events table for a fictional SaaS product called Trackly. Trackly is a simple project management tool. Every time a user does something related to their subscription, Trackly logs an event.

The events we care about are:

• trial_started: a new user signs up for a free trial
• upgraded: a trial user converts to a paid plan
• churned: a paid user cancels their subscription
• renewed: a paid user’s subscription renews for another month

Companies like Trackly generate millions of these events per year, and answering basic questions like “how many users upgraded last month” or “what is the churn rate by plan type” requires scanning every row in the events table. This is exactly the kind of workload ClickHouse handles well.

How do you create your first ClickHouse table?

Here is the CREATE TABLE statement for Trackly’s subscription events:

CREATE TABLE subscription_events (
user_id     UInt64,
plan       String,
country    String,
event_type String,
revenue    Float64,
event_date Date
) ENGINE = MergeTree()
ORDER BY (country, event_date)
PARTITION BY toYYYYMM(event_date);

Three things to understand here before moving on:

• ENGINE = MergeTree(). This tells ClickHouse to use the MergeTree storage engine. For any production table storing real data, this is almost always the right choice. The previous post in this series covers all engine types if you want more context.
• ORDER BY (country, event_date). This is the sorting key. ClickHouse physically sorts all data on disk by country first, then by date within each country. When you run a query filtered by country or date, ClickHouse skips over rows that cannot possibly match your filter. The closer your ORDER BY matches your most common query filters, the faster your queries will be. If you do not specify a PRIMARY KEY separately, ClickHouse uses ORDER BY as the primary key by default.
• PARTITION BY toYYYYMM(event_date). This groups data into monthly partitions. If you want to delete all events older than 12 months, ClickHouse drops the entire partition in one fast operation instead of scanning and deleting individual rows.

How do you insert data into ClickHouse?

For testing and learning, you can insert rows directly using VALUES:

INSERT INTO subscription_events VALUES
(1001, 'pro', 'Ireland', 'trial_started', 0.00, '2024-03-01'),
(1002, 'starter', 'USA', 'trial_started', 0.00, '2024-03-01'),
(1001, 'pro', 'Ireland', 'upgraded', 49.00, '2024-03-08'),
(1003, 'pro', 'Germany', 'trial_started', 0.00, '2024-03-10'),
(1002, 'starter', 'USA', 'churned', 0.00, '2024-03-15'),
(1004, 'starter', 'UK', 'trial_started', 0.00, '2024-03-18'),
(1003, 'pro', 'Germany', 'upgraded', 49.00, '2024-03-22'),
(1004, 'starter', 'UK', 'upgraded', 19.00, '2024-03-29');

What do your first ClickHouse queries look like?

Now for the part that makes ClickHouse worth understanding: your first queries on real data.

Query 1: How many events happened in total?

SELECT count()
FROM subscription_events;

Result: 8. Eight events in the table. Simple, but confirms your data loaded correctly.

Query 2: How many users upgraded by country?

SELECT
country,
count() AS upgrades
FROM subscription_events
WHERE event_type = 'upgraded'
GROUP BY country
ORDER BY upgrades DESC;

Result:

This tells you that in March 2024, one user upgraded in each of Germany, Ireland, and the UK. In a real dataset with millions of rows, this same query returns in under a second because ClickHouse reads only the country and event_type columns and skips the rest.

Query 3: What was total revenue by plan in March?

SELECT
plan,
sum(revenue) AS total_revenue
FROM subscription_events
WHERE event_date >= '2024-03-01'
AND event_date < '2024-04-01'
AND event_type = 'upgraded'
GROUP BY plan
ORDER BY total_revenue DESC;

Result:

Pro plan generated 98 euros in upgrades during March. Starter plan generated 19. At eight rows this is trivial, but imagine running this same query across 200 million rows of historical subscription events. ClickHouse reads only four columns out of six, applies the date filter using the partition boundary, and returns the result in milliseconds.

What should your ORDER BY be based on?

This is the most important schema design decision you will make when creating a ClickHouse table. Getting it right from the start saves a lot of pain later.

The rule is simple: your ORDER BY columns should match the filters you use most often in your WHERE clauses.

For Trackly’s events table, the most common queries filter by country and event_date. So ORDER BY (country, event_date) means ClickHouse can skip entire chunks of data for queries that filter on those columns.

If instead you put ORDER BY user_id but always filter by country, ClickHouse cannot skip anything useful. It reads more data than it needs to, and queries slow down.

Three practical guidelines for choosing ORDER BY:

• Choose columns and their order from your important query filters and data distribution, not a universal highest- or lowest-cardinality-first rule
• For frequent country-equality filters combined with date ranges, (country, event_date) is a candidate. Include date-only and other important queries when evaluating it; one ordering need not serve every query equally well
• Compare candidate keys using the same representative data and query workload. Use EXPLAIN indexes = 1 to inspect pruning, then run the queries and compare rows and bytes read, elapsed time and storage size. Choose the trade-off that suits that workload

What are the most common mistakes beginners make?

After setting up your first table and running your first queries, these are the mistakes that slow most people down:

• Choosing ORDER BY based on what looks logical, not what queries actually filter on. Your sort key should match your access patterns, not your instinct about what the “natural” key is.
• Over-partitioning. Partitioning by day when you have millions of rows per day creates too many small parts and slows things down. Partition by month unless you have a strong reason not to.
• Inserting one row at a time in production. ClickHouse is designed for batch inserts. Single-row inserts create one part per insert and force constant background merging. Always batch your inserts.
• Treating ClickHouse like PostgreSQL. Frequent updates, single-row lookups, and joins on large tables behave very differently in ClickHouse. Design your schema around append-heavy, scan-heavy workloads.

Looking for ClickHouse consulting or implementation support? Book a free ClickHouse consultation