Understanding Enum8 and Enum16 in ClickHouse with Real-World Examples

  

ClickHouse offers a compact and efficient way to store categorical string data using the Enum type. Instead of storing strings directly, ClickHouse maps them to integer values using Enum8 or Enum16 for optimizing storage and speeding up operations such as GROUP BY, ORDER BY, and comparisons.

 

1. What is Enum in ClickHouse?

An Enum is an enumerated type that allows you to store a set of predefined string values as compact integers, which helps in optimizing both storage and query performance.

 

ClickHouse supports:

·      Enum8: Up to 256 values (range: -128 to 127)

·      Enum16: Up to 65,536 values (range: -32,768 to 32,767)

 

Behind the scenes, Enums are stored as their corresponding integers (Int8 or Int16), but ClickHouse lets you interact with them using their string names.

 

2. Rules and Behavior

·      Each string is mapped to a unique integer in the allowed range.

·      The string and number pairs must be unique.

·      NULL is not allowed directly but can be used via Nullable(Enum8) or Nullable(Enum16) type.

·      Default value is the smallest numeric value.

 

·      During:

·      Text Reads: Input must match a defined Enum string. Invalid strings throw an error.

·      Text Writes: Output shows the string name.

·      When reading and writing in binary form, it works the same way as for Int8 and Int16 data types.

·      Enums compare and sort numerically.

·      Cannot perform arithmetic or string concatenation directly on Enums.

·      You can convert Enum to a string using toString() and to numbers using toInt8() or similar functions.

·      ALTER TABLE supports:

o   Adding/removing values (with care).

o   Changing between Enum8 <--> Enum16.

 

3. Example of Enum8

Imagine a typical e-commerce system. Every time someone places an order you need to store order_id, customer_id and status.

 

Column	What it holds	Why it’s that type
order_id	A unique, sequential ID for each order	UInt32 is plenty (up to 4 billion) and tiny on disk.
customer_id	Which customer placed the order	Also a compact UInt32; matches a primary-key in a customers table.
status	Where the order sits in your fulfilment pipeline	Enum8 keeps 5 human-readable states ('Pending', 'Processing', etc.) but stores them as one-byte integers (1-5).

 

CREATE TABLE demo_db.orders (
    order_id UInt32,
    customer_id UInt32,
    status Enum8(
        'Pending' = 1,
        'Processing' = 2,
        'Shipped' = 3,
        'Delivered' = 4,
        'Cancelled' = 5
    )
) ENGINE = MergeTree()
ORDER BY order_id;

INSERT INTO demo_db.orders VALUES
    (1001, 501, 'Processing'),
    (1001, 501, 'Shipped'),
    (1002, 502, 'Cancelled'),
    (1003, 503, 'Processing'),
    (1003, 503, 'Shipped'),
    (1003, 503, 'Delivered'),
    (1004, 501, 'Pending'),
    (1005, 502, 'Pending'),
    (1006, 503, 'Pending');

krishna :) SELECT * FROM demo_db.orders;

SELECT *
FROM demo_db.orders

Query id: 48f51cb1-8cbe-4380-acb5-7c3956b0d470

   ┌─order_id─┬─customer_id─┬─status─────┐
1. │     1001 │         501 │ Processing │
2. │     1001 │         501 │ Shipped    │
3. │     1002 │         502 │ Cancelled  │
4. │     1003 │         503 │ Processing │
5. │     1003 │         503 │ Shipped    │
6. │     1003 │         503 │ Delivered  │
7. │     1004 │         501 │ Pending    │
8. │     1005 │         502 │ Pending    │
9. │     1006 │         503 │ Pending    │
   └──────────┴─────────────┴────────────┘

9 rows in set. Elapsed: 0.017 sec.

   

3.1 Querying with enum string

 

SELECT *
FROM demo_db.orders
WHERE status = 'Shipped';

krishna :) SELECT *
FROM demo_db.orders
WHERE status = 'Shipped';

SELECT *
FROM demo_db.orders
WHERE status = 'Shipped'

Query id: ee004925-2165-4cb7-b2ec-2a6dad758f98

   ┌─order_id─┬─customer_id─┬─status──┐
1. │     1001 │         501 │ Shipped │
2. │     1003 │         503 │ Shipped │
   └──────────┴─────────────┴─────────┘

2 rows in set. Elapsed: 0.008 sec.

   

3.2 Querying with the enum’s numeric value

Since Enum8 is stored as an Int8, ClickHouse accepts either the name or the numeric code assigned to that name. Because of this ClickHouse lets you compare an Enum8 column directly to its integer codes, but for readability most teams stick to the string form in production SQL.

 

SELECT *
FROM demo_db.orders
WHERE status = 3;

krishna :) SELECT *
FROM demo_db.orders
WHERE status = 3;

SELECT *
FROM demo_db.orders
WHERE status = 3

Query id: 6c0a453c-9106-476e-82c7-c141d82ff950

   ┌─order_id─┬─customer_id─┬─status──┐
1. │     1001 │         501 │ Shipped │
2. │     1003 │         503 │ Shipped │
   └──────────┴─────────────┴─────────┘

2 rows in set. Elapsed: 0.003 sec.

   

We can even insert data into the table using their enum codes.

 

INSERT INTO demo_db.orders VALUES
    (1008, 504, 1), 
    (1009, 504, 2),
    (1009, 504, 3),
    (1009, 504, 4),
    (1010, 504, 5);

krishna :) SELECT * FROM demo_db.orders WHERE customer_id=504;

SELECT *
FROM demo_db.orders
WHERE customer_id = 504

Query id: 73cc3555-089a-47a6-aa88-49542b4f774e

   ┌─order_id─┬─customer_id─┬─status─────┐
1. │     1008 │         504 │ Pending    │
2. │     1009 │         504 │ Processing │
3. │     1009 │         504 │ Shipped    │
4. │     1009 │         504 │ Delivered  │
5. │     1010 │         504 │ Cancelled  │
   └──────────┴─────────────┴────────────┘

5 rows in set. Elapsed: 0.007 sec.

   

In summary,

·      Use Enum8 when your list is small and fixed (e.g., statuses, flags, roles).

·      Use Enum16 when your list is larger or expected to grow (e.g., country codes, languages).

·      Always define explicit mappings for clarity and maintainability.

·      Prefer Enums over strings for frequent grouping or filtering on categorical fields.

 

 

Previous                                                    Next                                                    Home