Skip to content
Tutorials Center
My learning

Topics

Lesson 3 of 35 ยท Getting Started

Your First Table and Queries

Create your first ClickHouse table with the MergeTree engine, insert rows, and run SELECT queries, with SQLite stand-ins you can run in the browser.

Time to write some ClickHouse. This lesson creates a table, inserts rows and runs queries. The syntax is close to standard SQL with two important additions: every table needs an engine (it decides how data is stored), and MergeTree tables need an ORDER BY (the sorting key). You will see both in the first statement.

ClickHouse cannot run on this site, so the ClickHouse statements are read-only. Each has an SQLite stand-in that you can run. The stand-in drops the engine and ORDER BY clauses, which SQLite does not have, and says so each time.

Create a table

SQL
CREATE TABLE page_views
(
    event_time DateTime,
    user_id    UInt64,
    page       String,
    country    LowCardinality(String),
    duration_ms UInt32
)
ENGINE = MergeTree
ORDER BY (page, event_time);

Reading it line by line:

  • Column types are explicit and non-nullable by default. UInt64 is an unsigned 64-bit integer; LowCardinality(String) is a dictionary-encoded string for columns with few distinct values. See data types.
  • ENGINE = MergeTree is the workhorse engine for almost all analytical tables.
  • ORDER BY (page, event_time) sorts the data on disk by those columns and builds the sparse primary index from them. Queries that filter on page are fast. This is explained fully in MergeTree ORDER BY and the primary key.

There is no PRIMARY KEY line: when you leave it out, it equals the ORDER BY. And there is no auto-increment id. ClickHouse tables rarely need one.

Insert rows

SQL
INSERT INTO page_views VALUES
    ('2026-05-01 09:00:01', 1, '/home',    'PT', 120),
    ('2026-05-01 09:00:05', 1, '/pricing', 'PT', 340),
    ('2026-05-01 09:01:10', 2, '/home',    'DE',  95);

This works, but in real use you insert thousands to hundreds of thousands of rows per statement, usually from a file or a stream. Each INSERT creates a new data part on disk; thousands of tiny inserts create thousands of parts and slow everything down. The rule of thumb: about one insert per second per table, each with a large batch. More on this in architecture and parts.

Query

SQL
SELECT page, count() AS views, avg(duration_ms) AS avg_ms
FROM page_views
WHERE event_time >= '2026-05-01 00:00:00'
GROUP BY page
ORDER BY views DESC
LIMIT 10;

A few ClickHouse habits show up already:

  • count() with no argument counts rows (so does count(*)).
  • Aliases defined in SELECT (views) can be used later in ORDER BY, and even in WHERE.
  • ClickHouse SQL is case-sensitive for functions and table names (count and toDate must be spelled exactly), while keywords such as SELECT are not.
  • The result prints in a format you choose with FORMAT (for example FORMAT Pretty, CSV, JSON). See clients, HTTP and formats.

Run the same shape in your browser

SQLite has no engines and no UInt64, but the SQL below mirrors the ClickHouse example. Column types become SQLite's looser types and ENGINE/ORDER BY disappear.

SQL Stand-in: create, insert, select (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

Aliases, expressions and WHERE

ClickHouse lets you reuse an alias in the same query, which keeps long expressions short:

SQL
SELECT
    toDate(event_time) AS day,
    duration_ms / 1000 AS seconds,
    seconds > 0.2 AS is_slow
FROM page_views
WHERE seconds > 0.1
ORDER BY day;

Standard SQL does not allow WHERE seconds > 0.1 when seconds is an alias; ClickHouse does. SQLite permits aliases in WHERE too in many cases, so the stand-in below runs.

SQL Stand-in: alias reuse (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

Look around the server

Some statements you will use in the first hour:

SQL
SHOW DATABASES;
SHOW TABLES;
DESCRIBE TABLE page_views;      -- columns and types
SHOW CREATE TABLE page_views;   -- the full DDL, including the engine
SELECT version();
SELECT count() FROM page_views;

ClickHouse also keeps metadata in system tables, which you query like any other table:

SQL
SELECT name, engine, total_rows
FROM system.tables
WHERE database = currentDatabase();

Dropping and truncating

SQL
TRUNCATE TABLE page_views;   -- remove all rows, keep the table
DROP TABLE page_views;       -- remove the table and its data

These act immediately and cannot be undone, so check which database you are in before running them.

Common beginner mistakes

  • Omitting ENGINE. A plain CREATE TABLE with no engine fails in classic setups; in some newer versions or ClickHouse Cloud a default engine is used. Write the engine explicitly while you learn.
  • Inserting one row per request. Batch them or use an async insert.
  • Expecting an auto-increment id or unique constraint. There is none; duplicates are allowed. See replacing and collapsing engines for deduplication.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does the ENGINE clause of CREATE TABLE decide?

    The engine (for example MergeTree) defines storage, indexing and behaviour of the table.
  2. 2. What happens if you omit PRIMARY KEY in a MergeTree table?

    The primary key defaults to the sorting key when not specified.
  3. 3. Why should you insert large batches instead of one row at a time?

    Small inserts create many parts that background merges must combine; large batches avoid that.
  4. 4. Which statement shows the full CREATE statement of an existing table, including its engine?

    SHOW CREATE TABLE prints the DDL; DESCRIBE lists only columns and types.

Summary

  • A ClickHouse table has typed columns, an ENGINE (usually MergeTree) and an ORDER BY.
  • Insert big batches, not single rows.
  • Queries are SQL with ClickHouse functions such as count() and toDate(); aliases can be reused within a query.
  • DESCRIBE, SHOW CREATE TABLE and the system tables describe what exists.
  • ClickHouse code is read-only on this site; SQLite stand-ins show the SQL shape.

Next: ClickHouse data types