Part 7: Advanced Features

ClickHouse isn’t just for flat tables. It has first-class support for complex data types like Arrays and Maps, making it perfect for analyzing JSON logs and time-series data.

Working with Arrays

Arrays are everywhere in ClickHouse. You can store them, query them, and even unnest them.

SELECT
[‘a’, ‘b’, ‘c’] AS my_array,
has(my_array, ‘b’) AS contains_b,
length(my_array) AS len,
arrayJoin(my_array) AS unnested_value;

Maps (Key-Value Pairs)

Maps are great for semi-structured data where the schema might evolve.

CREATE TABLE logs (
timestamp DateTime,
attributes Map(String, String)
) ENGINE = MergeTree()
ORDER BY timestamp;
— Querying a map
SELECT attributes[‘user_agent’] FROM logs;

Window Functions

ClickHouse supports standard SQL window functions, allowing you to perform calculations across a set of table rows that are somehow related to the current row.

SELECT
user_id,
timestamp,
count() OVER (PARTITION BY user_id ORDER BY timestamp) AS user_event_count
FROM events;

Conclusion

With Arrays, Maps, and Window Functions, you can solve complex analytical problems directly in the database, without needing external processing tools. Next, we’ll tackle one of the most powerful features for real-time analytics: Materialized Views.