## ClickHouse MergeTree原理解析 朱凯@深圳2019.10  ## 朱凯 ## 远光软件 大数据事业部/平台开发部 总经理 资深架构师，腾讯云TVP专家 10多年IT从业经验，精通Java、Node.js等语言方向 ## 合并树 这众多的表引擎中，又属合并树(MergeTree)表引擎及其家族系列( $ *MergeTree $ )最为强大，在生产环境绝大部分场景中都应该使用此系列的表引擎。 只有合并树系列的表引擎才支持主键索引、数据分区、数据副本和数据采样这些特性，同时也只有此系列的表引擎支持ALTER相关操作。 ## 合并树家族 其中MergeTree作为家族中最基础的表引擎，提供了主键索引、数据分 区、数据副本和数据采样等所有的基本能力，而家族中其他的表引擎则在MergeTree的基础之上各有所长。  ## MergeTree的名称由来 MergeTree在写入一批数据时，数据总会以数据片段的形式写入磁盘，且数据片段不可修改。为了

каким образом. • Использование индексов, если есть. • Параметры репликации данных. 1 Семейство MergeTree 2 Семейство Log 3 Движки для интеграции 4 Специализированные движки ## Возможности для интеграции

DateTime, `Name` String, `Age` UInt8, ..., `HeartRate` UInt8, `Humidity` Float32, ... ) ENGINE = MergeTree() PARTITION BY toYYYYMM(Time) ORDER BY (Name, Time, Age, ...); |Time|Name|Age|Humidity|Hear >90116.3010111...) ENGINE = MergeTree()PARTITION BY toYYYYMM(Time) ORDER BY

[Image](/uploads/documents/a/5/4/5/a5458db7fb86ab4e1f5c1167e010676e/p9_2.jpg) ## 数仓建设-主题事实清单表 主题事实清单表采用引擎MergeTree. 同步策略: 每日从 oracle 数据平台增量同步到 ck 数仓. create table dw_hr.fct_rpt_dc_shop_vender_day ( stat_year rpt_qty Decimal(18,4), rpt_boxes Decimal(18,4), rpt_cost Decimal(18,4), ); engine = MergeTree PARTITION BY toYYYYMM(stat_day) ORDER BY (stat_day, dc_id) SETTINGS index_granularity = 8192; ##

data LIMIT N SELECT min(pickup_date), max(pickup_date) FROM SELECT pickup_date FROM trips_mergetree_third LIMIT 1000 min(pickup_date) max(pickup_date) 2009-01-01 2009-01-01 ## How to sample data for fixed sample query > Only for MergeTree ## How to sample data SAMPLE x OFFSET y CREATE TABLE trips_sample_time pickup_datetime DateTime ENGINE = MergeTree ORDER BY sipHash64(pickup_datetime) store model as aggregate function state in a separate table Example CREATE TABLE models ENGINE = MergeTree ORDER BY tuple() AS stochasticLinearRegressionState(total_amount, trip_distance) FROM trips WHERE

data LIMIT N SELECT min(pickup_date), max(pickup_date) FROM SELECT pickup_date FROM trips_mergetree_third LIMIT 1000 min(pickup_date) max(pickup_date) 2009-01-01 2009-01-01 ## How to sample data for fixed sample query > Only for MergeTree ## How to sample data SAMPLE x OFFSET y CREATE TABLE trips_sample_time pickup_datetime DateTime ENGINE = MergeTree ORDER BY sipHash64(pickup_datetime) store model as aggregate function state in a separate table Example CREATE TABLE models ENGINE = MergeTree ORDER BY tuple() AS stochasticLinearRegressionState(total_amount, trip_distance) FROM trips WHERE

Hub ## Support For Semistructured Data JSO data type: CREATE TABLE games (data JSON) ENGINE = MergeTree; • You can insert arbitrary nested JSONs • Types are automatically inferred on INSERT and merge String) ENGINE = MergeTree ORDER BY tuple(); SELECT JSONExtractString(data, 'teams', 1, 'name') FROM games; — 0.520 sec. CREATE TABLE games (data JSON) ENGINE = MergeTree; SELECT data.teams

Hub ## Support For Semistructured Data JSO data type: CREATE TABLE games (data JSON) ENGINE = MergeTree; • You can insert arbitrary nested JSONs • Types are automatically inferred on INSERT and merge ENGINE = MergeTree ORDER BY tuple(); SELECT JSONExtractString(data, 'teams', 1, 'name') FROM games; — 0.520 sec. CREATE TABLE games (data JSON) ENGINE = MergeTree; SELECT data

faster for MergeTree tables • table A join table B on id • If table B is a MergeTree table, and id is its primary key, • can we skip unmatching blocks of table B quickly, using MergeTree index of table

упорядоченность События поступают (почти) упорядоченными по времени А нам нужно по первичному ключу! MergeTree: поддерживаем небольшое количество упорядоченных кусков Идея та же, что и в LSM-дереве ![Imag