Skip to content
Tutorials Center
My learning

Topics

Lesson 4 of 35 ยท Getting Started

ClickHouse Data Types

Learn the ClickHouse data types: sized integers, floats and Decimal, String, Date and DateTime, Enum, LowCardinality, Nullable, Array, Tuple, Map and JSON.

Choosing column types is more important in ClickHouse than in most databases. A column's type decides how many bytes each value takes, how well it compresses and how fast it is scanned. Over a billion rows, storing a status code as UInt8 instead of String can save gigabytes and make queries several times faster. This lesson tours the types you will use every day.

ClickHouse cannot run on this site. The type listings below are read-only. The runnable examples use SQLite to demonstrate the ideas (ranges, choosing smaller types, dictionary encoding by hand); SQLite itself has only a few loose types.

Integers

ClickHouse integers have a fixed size in bits, signed (Int) or unsigned (UInt):

Type Bytes Range
UInt8 / Int8 1 0 to 255 / -128 to 127
UInt16 / Int16 2 0 to 65,535 / -32,768 to 32,767
UInt32 / Int32 4 0 to about 4.29 billion / about plus or minus 2.1 billion
UInt64 / Int64 8 0 to about 1.8e19 / about plus or minus 9.2e18
UInt128, UInt256, Int128, Int256 16, 32 Very large values

Pick the smallest type that safely fits: HTTP status codes fit in UInt16, a percentage in UInt8. Integer overflow wraps around silently, so leave headroom.

Floats and decimals

Float32 and Float64 are IEEE floating point, fast but inexact (0.1 + 0.2 is not exactly 0.3). For money use Decimal(P, S), for example Decimal(18, 2) holds up to 18 digits with 2 after the point and is exact. Floats also have NaN and inf, which break sorting and comparisons if you let them in.

Strings

String holds any bytes of any length (usually UTF-8) with no declared limit. FixedString(N) stores exactly N bytes, useful for hashes or country codes of fixed length, but padding makes it awkward for text.

Dates and times

Type Meaning
Date Days since 1970-01-01, 2 bytes, up to 2149
Date32 Wider range, 4 bytes
DateTime Seconds since the Unix epoch, 4 bytes, optional time zone: DateTime('Europe/Lisbon')
DateTime64(P) Sub-second precision: DateTime64(3) is milliseconds

Internally these are integers, so comparing and grouping them is cheap. A time zone given to the type changes only display and conversions; the stored value is always UTC seconds. We cover functions on them in date, time and string functions.

Enum, Bool and UUID

SQL
status Enum8('active' = 1, 'banned' = 2, 'deleted' = 3)
is_admin Bool            -- stored as UInt8, shown as true/false
request_id UUID          -- 16 bytes

Enum8 stores one byte and prints the name. It is compact and validates input, but changing the allowed values later needs an ALTER. For text categories that change, use LowCardinality(String) instead.

LowCardinality

LowCardinality(String) builds a dictionary of the distinct values and stores small integer codes. It is the single most useful trick for text columns that repeat a lot: country, browser, HTTP method, log level. The rule of thumb is up to about a few thousand distinct values (the cardinality). With millions of distinct values (user agent strings, URLs) it stops helping.

SQL
CREATE TABLE logs
(
    ts      DateTime,
    level   LowCardinality(String),   -- 'INFO', 'WARN', 'ERROR'
    service LowCardinality(String),
    message String
)
ENGINE = MergeTree ORDER BY (service, ts);

You can demonstrate dictionary encoding by hand: store each distinct string once, and refer to it by a small number.

SQL Stand-in: dictionary encoding by hand (SQLite)

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

ClickHouse does exactly this inside the column file, invisibly: the type still behaves like a String in queries.

Nullable

Add Nullable(T) to allow NULL. It costs an extra hidden column of null flags and slows scans, and it cannot be used in the sorting key by default. Prefer a default value (0, empty string, a sentinel date) over NULL when the meaning is clear. Use Nullable only when "unknown" truly differs from "empty".

Arrays, Tuples, Maps and JSON

SQL
tags    Array(String)                      -- ['a', 'b']
point   Tuple(x Float64, y Float64)        -- (1.5, 2.5)
headers Map(String, String)                -- {'host': 'a.com'}
payload JSON                               -- semi-structured documents

Array(T) is a first-class column type with its own functions (arrays and lambdas). Map is a column of key-value pairs. The JSON type stores documents as sub-columns, so common paths are read like normal columns; it is newer, so check your server version's docs before relying on it. When you only need to keep a payload, a String plus the JSONExtract* functions works on every version.

Aggregate and special types

  • AggregateFunction(name, T) and SimpleAggregateFunction store partial aggregation states, used with aggregating tables and materialized views.
  • IPv4 and IPv6 store addresses compactly with network functions.
  • Nested, Nothing, Interval and geo types (Point, Polygon) exist for specialised work.

Choosing types: a worked example

A web event has a status code, a country, a latency and a timestamp. A careless table might use Int64 for status, String for country, Float64 for milliseconds. A careful one is smaller:

SQL
CREATE TABLE requests
(
    ts          DateTime,
    status      UInt16,
    country     LowCardinality(String),
    duration_ms UInt32,
    bytes_sent  UInt32
)
ENGINE = MergeTree ORDER BY (country, ts);

You can estimate the saving with arithmetic. The query below compares bytes per row in the two designs, for one billion rows.

SQL Stand-in: size of two designs (SQLite)

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

The careless design takes about 40 GB per billion rows before compression; the careful one about 15 GB. Compression then narrows the gap, but smaller types are still faster to read.

Common beginner mistakes

  • Using String for everything, including numbers and dates.
  • Making a column Nullable "just in case".
  • Using Float64 for money.
  • Using LowCardinality on a column with millions of distinct values.
  • Choosing a type too small, and overflowing silently.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which type best stores an HTTP status code such as 404?

    UInt16 holds 0 to 65,535 in 2 bytes, which is enough for status codes and compact.
  2. 2. When is LowCardinality(String) a good choice?

    It builds a dictionary, which pays off only when there are few distinct values.
  3. 3. What is the downside of Nullable(T)?

    Nullable keeps a separate mask column, so it costs space and time; use defaults when you can.
  4. 4. What should you use for exact money amounts?

    Decimal is exact; floats have binary rounding errors.

Summary

  • Pick the smallest sized integer that fits; ClickHouse integers overflow silently.
  • Use Decimal for money, floats for approximate science and metrics.
  • Date, DateTime and DateTime64 are integers inside, so they are fast.
  • LowCardinality(String) is the default for repeating text; avoid it for unique values.
  • Avoid Nullable when a default value will do.

Next: Architecture: parts and granules