pTTu0CcJ

1CREATE TABLE IF NOT EXISTS db.table1 (
2    start_date     Date,
3    app_name LowCardinality(String),
4    hostname LowCardinality(String),
5    domain   LowCardinality(String),
6    text     LowCardinality(String),
7    channel_uuid UUID,
8    start_ts_ms UInt64 CODEC(DoubleDelta),
9    answer_ts_ms UInt64 CODEC(DoubleDelta),
10    end_ts_ms UInt64 CODEC(DoubleDelta),
11    db_insert_ts_ms UInt64 CODEC(DoubleDelta),
12    box LowCardinality(String),
13    box_tz_offset_sec Int32 CODEC(DoubleDelta),
14    bss_srv Int8,
15    bss_other String,
16    proc_term_type LowCardinality(String),
17    record_ms UInt32,
18    hangup_cause LowCardinality(String)
19    )
20ENGINE = MergeTree PARTITION BY toYYYYMMDD(start_date) ORDER BY (toStartOfTenMinutes(toDateTime(intDiv(start_ts_ms, 1000))), box, domain);
21
22-- Два типовых запроса
23-- Показать все записи по какому-то "box", "domain" в некотором окне времени окно это, как правило, значительно меньше размера партициии (20-60 минут)
24WITH 
25  '2020-02-01 00:00:00' AS from_datetime,
26  '2020-02-03 12:00:00' AS to_datetime,
27  'XXXXXXXXX' as box_s,
28  'YYYY' as domain_s
29SELECT * FROM db.table1
30WHERE start_date >= toDate(from_datetime) AND start_date >= toDate(to_datetime)
31  AND toStartOfTenMinutes(toDateTime(intDiv(start_ts_ms, 1000))) >= from_datetime
32  AND toStartOfTenMinutes(toDateTime(intDiv(start_ts_ms, 1000))) <= to_datetime
33  AND box = box_s AND domain = domain_s
34ORDER BY start_ts_ms;
35
36-- Другой типовой запрос это аналитика с GROUP BY и там ограничения по партициям.