Have you worked with tables for analysts in ClickHouse? What engines have you encountered?

In ClickHouse, analysts often create tables considering the specifics of queries and data volume. The main engines encountered are:

• MergeTree — the most popular engine for storing large volumes of data with support for primary key indexes, partitioning, and compression.
• ReplacingMergeTree — an extension of MergeTree that allows updating data by replacing old records with new ones based on a key.
• SummingMergeTree — aggregates data by key, useful for pre-aggregation.
• AggregatingMergeTree — for storing aggregated states, used with aggregate functions.
• CollapsingMergeTree — for storing data with the ability to logically delete records.

When creating tables, it is important to consider the partitioning scheme (e.g., by date), sorting (primary key) to speed up queries, and choosing the appropriate engine depending on the requirements for data updating and aggregation.

Example of creating a simple table:

CREATE TABLE analytics.events (
    event_date Date,
    user_id UInt64,
    event_type String,
    event_value Float64
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (user_id, event_date);