ClickHouse 学习之从高级到监控以及备份（二）

第 1 章 Explain 查看执行计划

        在 clickhouse 20.6 版本之前要查看 SQL 语句的执行计划需要设置日志级别为 trace 才能可以看到，并且只能真正执行 sql，在执行日志里面查看。在 20.6 版本引入了原生的执行计划的语法。在 20.6.3 版本成为正式版本的功能。

1.1 基本语法

EXPLAIN [AST | SYNTAX | PLAN | PIPELINE] [setting = value, ...] 
SELECT ... [FORMAT ...]

1）PLAN：用于查看执行计划，默认值。

• header：打印计划中各个步骤的 head 说明，默认关闭，默认值 0;
• description：打印计划中各个步骤的描述，默认开启，默认值 1；
• actions：打印计划中各个步骤的详细信息，默认关闭，默认值 0。

2）AST：用于查看语法树;
3）SYNTAX：用于查看优化语法;
4）PIPELINE：用于查看 PIPELINE 计划。

• header：打印计划中各个步骤的 head 说明，默认关闭;
• graph：用 DOT 图形语言描述管道图，默认关闭，需要查看相关的图形需要配合 graphviz 查看；
• actions：如果开启了 graph，紧凑打印打，默认开启。

1.2 案例实操

1.2.1 使用 EXPLAIN

1）查看 PLAN

（1）简单查询

EXPLAIN PLAN SELECT arrayJoin([1, 2, 3, NULL, NULL]);

（2）复杂 SQL 的执行计划

EXPLAIN SELECT database, table, count(1) AS cnt FROM system.parts WHERE database IN ('datasets', 'system') GROUP BY database, table ORDER BY database ASC, cnt DESC LIMIT 2 BY database;

（3）打开全部的参数的执行计划

EXPLAIN header = 1, actions = 1, description = 1 SELECT number FROM system.numbers LIMIT 10;

2）AST 语法树

EXPLAIN AST SELECT number FROM system.numbers LIMIT 10;

3）SYNTAX 语法优化

-- 先做一次查询
SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'atguigu') FROM numbers(10);
-- 查看语法优化
EXPLAIN SYNTAX SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'atguigu') FROM numbers(10);
-- 开启三元运算符优化
SET optimize_if_chain_to_multiif = 1;
-- 再次查看语法优化
EXPLAIN SYNTAX SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'atguigu') FROM numbers(10);

4）查看 PIPELINE

EXPLAIN PIPELINE SELECT sum(number) FROM numbers_mt(100000) GROUP BY number % 20;
-- 打开其他参数
EXPLAIN PIPELINE header = 1, graph = 1 SELECT sum(number) FROM numbers_mt(10000) GROUP BY number % 20;

第 2 章 建表优化

2.1 数据类型

2.1.1 时间字段的类型

建表时能用数值型或日期时间型表示的字段就不要用字符串，全 String 类型在以 Hive 为中心的数仓建设中常见，但 ClickHouse 环境不应受此影响。

虽然 ClickHouse 底层将 DateTime 存储为时间戳 Long 类型，但不建议存储 Long 类型，因为 DateTime 不需要经过函数转换处理，执行效率高、可读性好。

CREATE TABLE t_type2
(
    `id` UInt32,
    `sku_id` String,
    `total_amount` Decimal(16, 2),
    `create_time` Int32
) ENGINE = ReplacingMergeTree(create_time)
PARTITION BY toYYYYMMDD(toDate(create_time)) -- 需要转换一次，否则报错
PRIMARY KEY id
ORDER BY (id, sku_id);

2.1.2 空值存储类型

官方已经指出 Nullable 类型几乎总是会拖累性能，因为存储 Nullable 列时需要创建一个额外的文件来存储 NULL 的标记，并且 Nullable 列无法被索引。因此除非极特殊情况，应直接使用字段默认值表示空，或者自行指定一个在业务中无意义的值（例如用-1 表示没有商品ID）

-- 创建表
CREATE TABLE t_null
(
    `x` Int8,
    `y` Nullable(Int8)
) ENGINE = TinyLog;
-- 插入语句
INSERT INTO t_null VALUES (1, NULL), (2, 3);
-- 查询语句
SELECT x + y FROM t_null;

查看存储的文件：（没有权限就用 root 用户）

官方说明：https://clickhouse.com/docs/zh/sql-reference/data-types/nullable/

2.2 分区和索引

分区粒度根据业务特点决定，不宜过粗或过细。一般选择按天分区，也可以指定为 Tuple()，以单表一亿数据为例，分区大小控制在 10-30 个为最佳。

必须指定索引列，ClickHouse 中的索引列即排序列，通过 order by 指定，一般在查询条件中经常被用来充当筛选条件的属性被纳入进来；可以是单一维度，也可以是组合维度的索引；通常需要满足高级列在前、查询频率大的在前原则；还有基数特别大的不适合做索引列，如用户表的 userid 字段；通常筛选后的数据满足在百万以内为最佳。

问题：为什么基数特别大的不适合做索引列？

官方文档：

When a query is filtering on at least one column that is part of a compound key, and is the first key column, then ClickHouse is running the binary search algorithm over the key column’s index marks.

When a query is filtering (only) on a column that is part of a compound key, but is not the first key column, then ClickHouse is using the generic exclusion search algorithm over the key column’s index marks.

官方文档：

We discussed that because a ClickHouse table’s row data is stored on disk ordered by primary key column(s), having a very high cardinality column (like a UUID column) in a primary key or in a compound primary key before columns with lower cardinality is detrimental for the compression ratio of other table columns.

A compromise between fastest retrieval and optimal data compression is to use a compound primary key where the UUID is the last key column, after low(er) cardinality key columns that are used to ensure a good compression ratio for some of the table’s columns.

结论：并不是基数特别大的不适合做索引列，而是基数特别大的不适合作为第一个索引列。因为如果查询条件不包含第一个索引时，这会使得通用排除搜索算法的效率变差。一个折中的做法是，索引列按照基数大小进行升序排列，基数特别大的作为索引的最后一列。

比如官方案例的 hits_v1 表：

……
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
……

visits_v1 表：

……
PARTITION BY toYYYYMM(StartDate)
ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID)
……

2.3 表参数

Index_granularity 是用来控制索引粒度的，默认是 8192，如非必须不建议调整。

如果表中不是必须保留全量历史数据，建议指定 TTL（生存时间值），可以免去手动清除过期历史数据的麻烦，TTL 也可以通过 alter table 语句随时修改。

2.4 写入和删除优化

1）尽量不要执行单条或小批量删除和插入操作，这样会产生小分区文件，给后台 Merge 任务带来巨大压力

2）不要一次写入太多分区，或数据写入太快，数据写入太快会导致 Merge 速度跟不上而报错，一般建议每秒钟发起 2-3 次写入操作，每次操作写入 2w~5w 条数据（依服务器性能而定）

写入过快报错，报错信息：

1. Code: 252, e.displayText() = DB::Exception: Too many parts(304). Merges are processing significantly slower than inserts
2. Code: 241, e.displayText() = DB::Exception: Memory limit (for query) exceeded:would use 9.37 GiB (attempt to allocate chunk of 301989888 bytes), maximum: 9.31 GiB

处理方式：

“ Too many parts 处理 ” ：使用 WAL 预写日志，提高写入性能。

in_memory_parts_enable_wal 默认为 true

在服务器内存充裕的情况下增加内存配额，一般通过 max_memory_usage 来实现

在服务器内存不充裕的情况下，建议将超出部分内容分配到系统硬盘上，但会降低执行速度，一般通过 max_bytes_before_external_group_by、max_bytes_before_external_sort 参数来实现。

2.5 常见配置

配置项主要在 config.xml 或 users.xml 中， 基本上都在 users.xml 里

config.xml 的配置项：https://clickhouse.com/docs/en/operations/server-configuration-parameters/settings/
users.xml 的配置项：https://clickhouse.com/docs/en/operations/settings/settings/

2.5.1 CPU 资源

2.5.2 内存资源

2.5.3 存储

ClickHouse 不支持设置多数据目录，为了提升数据 io 性能，可以挂载虚拟券组，一个券组绑定多块物理磁盘提升读写性能，多数据查询场景 SSD 会比普通机械硬盘快 2-3 倍。

第 3 章 语法优化规则

ClickHouse 的 SQL 优化规则是基于 RBO(Rule Based Optimization)，下面是一些优化规则。

3.1 准备测试用表

1）下载并导入官方的数据集

官网地址：https://clickhouse.com/docs/en/getting-started/example-datasets/metrica

下载 hits 压缩 TSV 文件：

curl https://datasets.clickhouse.com/hits/tsv/hits_v1.tsv.xz | unxz --threads=`nproc` > hits_v1.tsv
# Validate the checksum
md5sum hits_v1.tsv
# Checksum should be equal to: f3631b6295bf06989c1437491f7592cb

创建数据库和表：

clickhouse-client [--password] --query "CREATE DATABASE IF NOT EXISTS datasets"

clickhouse-client [--password] --query "CREATE TABLE datasets.hits_v1 ( WatchID UInt64,  JavaEnable UInt8,  Title String,  GoodEvent Int16,  EventTime DateTime,  EventDate Date,  CounterID UInt32,  ClientIP UInt32,  ClientIP6 FixedString(16),  RegionID UInt32,  UserID UInt64,  CounterClass Int8,  OS UInt8,  UserAgent UInt8,  URL String,  Referer String,  URLDomain String,  RefererDomain String,  Refresh UInt8,  IsRobot UInt8,  RefererCategories Array(UInt16),  URLCategories Array(UInt16), URLRegions Array(UInt32),  RefererRegions Array(UInt32),  ResolutionWidth UInt16,  ResolutionHeight UInt16,  ResolutionDepth UInt8,  FlashMajor UInt8, FlashMinor UInt8,  FlashMinor2 String,  NetMajor UInt8,  NetMinor UInt8, UserAgentMajor UInt16,  UserAgentMinor FixedString(2),  CookieEnable UInt8, JavascriptEnable UInt8,  IsMobile UInt8,  MobilePhone UInt8,  MobilePhoneModel String,  Params String,  IPNetworkID UInt32,  TraficSourceID Int8, SearchEngineID UInt16,  SearchPhrase String,  AdvEngineID UInt8,  IsArtifical UInt8,  WindowClientWidth UInt16,  WindowClientHeight UInt16,  ClientTimeZone Int16,  ClientEventTime DateTime,  SilverlightVersion1 UInt8, SilverlightVersion2 UInt8,  SilverlightVersion3 UInt32,  SilverlightVersion4 UInt16,  PageCharset String,  CodeVersion UInt32,  IsLink UInt8,  IsDownload UInt8,  IsNotBounce UInt8,  FUniqID UInt64,  HID UInt32,  IsOldCounter UInt8, IsEvent UInt8,  IsParameter UInt8,  DontCountHits UInt8,  WithHash UInt8, HitColor FixedString(1),  UTCEventTime DateTime,  Age UInt8,  Sex UInt8,  Income UInt8,  Interests UInt16,  Robotness UInt8,  GeneralInterests Array(UInt16), RemoteIP UInt32,  RemoteIP6 FixedString(16),  WindowName Int32,  OpenerName Int32,  HistoryLength Int16,  BrowserLanguage FixedString(2),  BrowserCountry FixedString(2),  SocialNetwork String,  SocialAction String,  HTTPError UInt16, SendTiming Int32,  DNSTiming Int32,  ConnectTiming Int32,  ResponseStartTiming Int32,  ResponseEndTiming Int32,  FetchTiming Int32,  RedirectTiming Int32, DOMInteractiveTiming Int32,  DOMContentLoadedTiming Int32,  DOMCompleteTiming Int32,  LoadEventStartTiming Int32,  LoadEventEndTiming Int32, NSToDOMContentLoadedTiming Int32,  FirstPaintTiming Int32,  RedirectCount Int8, SocialSourceNetworkID UInt8,  SocialSourcePage String,  ParamPrice Int64, ParamOrderID String,  ParamCurrency FixedString(3),  ParamCurrencyID UInt16, GoalsReached Array(UInt32),  OpenstatServiceName String,  OpenstatCampaignID String,  OpenstatAdID String,  OpenstatSourceID String,  UTMSource String, UTMMedium String,  UTMCampaign String,  UTMContent String,  UTMTerm String, FromTag String,  HasGCLID UInt8,  RefererHash UInt64,  URLHash UInt64,  CLID UInt32,  YCLID UInt64,  ShareService String,  ShareURL String,  ShareTitle String,  ParsedParams Nested(Key1 String,  Key2 String, Key3 String, Key4 String, Key5 String,  ValueDouble Float64),  IslandID FixedString(16),  RequestNum UInt32,  RequestTry UInt8) ENGINE = MergeTree() PARTITION BY toYYYYMM(EventDate) ORDER BY (CounterID, EventDate, intHash32(UserID)) SAMPLE BY intHash32(UserID) SETTINGS index_granularity = 8192"

导入 hits 数据：

cat hits_v1.tsv | clickhouse-client [--password] --query "INSERT INTO datasets.hits_v1 FORMAT TSV" --max_insert_block_size=100000

clickhouse-client [--password] --query "SELECT COUNT(*) FROM datasets.hits_v1"

下载 visits 压缩 TSV 文件：

curl https://datasets.clickhouse.com/visits/tsv/visits_v1.tsv.xz | unxz --threads=`nproc` > visits_v1.tsv
# Validate the checksum
md5sum visits_v1.tsv
# Checksum should be equal to: 6dafe1a0f24e59e3fc2d0fed85601de6

创建表：

clickhouse-client [--password] --query "CREATE TABLE datasets.visits_v1 ( CounterID UInt32,  StartDate Date,  Sign Int8,  IsNew UInt8,  VisitID UInt64,  UserID UInt64,  StartTime DateTime,  Duration UInt32,  UTCStartTime DateTime,  PageViews Int32,  Hits Int32,  IsBounce UInt8,  Referer String,  StartURL String,  RefererDomain String,  StartURLDomain String,  EndURL String,  LinkURL String,  IsDownload UInt8,  TraficSourceID Int8,  SearchEngineID UInt16,  SearchPhrase String,  AdvEngineID UInt8,  PlaceID Int32,  RefererCategories Array(UInt16),  URLCategories Array(UInt16),  URLRegions Array(UInt32),  RefererRegions Array(UInt32),  IsYandex UInt8,  GoalReachesDepth Int32,  GoalReachesURL Int32,  GoalReachesAny Int32,  SocialSourceNetworkID UInt8,  SocialSourcePage String,  MobilePhoneModel String,  ClientEventTime DateTime,  RegionID UInt32,  ClientIP UInt32,  ClientIP6 FixedString(16),  RemoteIP UInt32,  RemoteIP6 FixedString(16),  IPNetworkID UInt32,  SilverlightVersion3 UInt32,  CodeVersion UInt32,  ResolutionWidth UInt16,  ResolutionHeight UInt16,  UserAgentMajor UInt16,  UserAgentMinor UInt16,  WindowClientWidth UInt16,  WindowClientHeight UInt16,  SilverlightVersion2 UInt8,  SilverlightVersion4 UInt16,  FlashVersion3 UInt16,  FlashVersion4 UInt16,  ClientTimeZone Int16,  OS UInt8,  UserAgent UInt8,  ResolutionDepth UInt8,  FlashMajor UInt8,  FlashMinor UInt8,  NetMajor UInt8,  NetMinor UInt8,  MobilePhone UInt8,  SilverlightVersion1 UInt8,  Age UInt8,  Sex UInt8,  Income UInt8,  JavaEnable UInt8,  CookieEnable UInt8,  JavascriptEnable UInt8,  IsMobile UInt8,  BrowserLanguage UInt16,  BrowserCountry UInt16,  Interests UInt16,  Robotness UInt8,  GeneralInterests Array(UInt16),  Params Array(String),  Goals Nested(ID UInt32, Serial UInt32, EventTime DateTime,  Price Int64,  OrderID String, CurrencyID UInt32),  WatchIDs Array(UInt64),  ParamSumPrice Int64,  ParamCurrency FixedString(3),  ParamCurrencyID UInt16,  ClickLogID UInt64,  ClickEventID Int32,  ClickGoodEvent Int32,  ClickEventTime DateTime,  ClickPriorityID Int32,  ClickPhraseID Int32,  ClickPageID Int32,  ClickPlaceID Int32,  ClickTypeID Int32,  ClickResourceID Int32,  ClickCost UInt32,  ClickClientIP UInt32,  ClickDomainID UInt32,  ClickURL String,  ClickAttempt UInt8,  ClickOrderID UInt32,  ClickBannerID UInt32,  ClickMarketCategoryID UInt32,  ClickMarketPP UInt32,  ClickMarketCategoryName String,  ClickMarketPPName String,  ClickAWAPSCampaignName String,  ClickPageName String,  ClickTargetType UInt16,  ClickTargetPhraseID UInt64,  ClickContextType UInt8,  ClickSelectType Int8,  ClickOptions String,  ClickGroupBannerID Int32,  OpenstatServiceName String,  OpenstatCampaignID String,  OpenstatAdID String,  OpenstatSourceID String,  UTMSource String,  UTMMedium String,  UTMCampaign String,  UTMContent String,  UTMTerm String,  FromTag String,  HasGCLID UInt8,  FirstVisit DateTime,  PredLastVisit Date,  LastVisit Date,  TotalVisits UInt32,  TraficSource    Nested(ID Int8,  SearchEngineID UInt16, AdvEngineID UInt8, PlaceID UInt16, SocialSourceNetworkID UInt8, Domain String, SearchPhrase String, SocialSourcePage String),  Attendance FixedString(16),  CLID UInt32,  YCLID UInt64,  NormalizedRefererHash UInt64,  SearchPhraseHash UInt64,  RefererDomainHash UInt64,  NormalizedStartURLHash UInt64,  StartURLDomainHash UInt64,  NormalizedEndURLHash UInt64,  TopLevelDomain UInt64,  URLScheme UInt64,  OpenstatServiceNameHash UInt64,  OpenstatCampaignIDHash UInt64,  OpenstatAdIDHash UInt64,  OpenstatSourceIDHash UInt64,  UTMSourceHash UInt64,  UTMMediumHash UInt64,  UTMCampaignHash UInt64,  UTMContentHash UInt64,  UTMTermHash UInt64,  FromHash UInt64,  WebVisorEnabled UInt8,  WebVisorActivity UInt32,  ParsedParams    Nested(Key1 String,  Key2 String,  Key3 String,  Key4 String, Key5 String, ValueDouble    Float64),  Market Nested(Type UInt8, GoalID UInt32, OrderID String,  OrderPrice Int64,  PP UInt32,  DirectPlaceID UInt32,  DirectOrderID  UInt32,  DirectBannerID UInt32,  GoodID String, GoodName String, GoodQuantity Int32,  GoodPrice Int64),  IslandID FixedString(16)) ENGINE = CollapsingMergeTree(Sign) PARTITION BY toYYYYMM(StartDate) ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID) SAMPLE BY intHash32(UserID) SETTINGS index_granularity = 8192"

导入 visits 数据：

cat visits_v1.tsv | clickhouse-client [--password] --query "INSERT INTO datasets.visits_v1 FORMAT TSV" --max_insert_block_size=100000

clickhouse-client [--password] --query "SELECT COUNT(*) FROM datasets.visits_v1"

3.2 COUNT 优化

在调用 count 函数时，如果使用的是 count() 或者 count(*)，且没有 where 条件，则会直接使用 system.tables 的 total_rows，例如:

EXPLAIN SELECT count() FROM datasets.hits_v1;

注意 Optimized trivial count ，这是对 count 的优化。

如果 count 具体的列字段，则不会使用此项优化：

EXPLAIN SELECT count(CounterID) FROM datasets.hits_v1;

3.3 消除子查询重复字段

下面语句子查询中有两个重复的 id 字段，会被去重:

EXPLAIN SYNTAX
SELECT
    a.UserID,
    b.VisitID,
    a.URL,
    b.UserID
FROM datasets.hits_v1 AS a
LEFT JOIN
(
    SELECT
        UserID,
        UserID AS HaHa,
        VisitID
    FROM datasets.visits_v1
) AS b USING (UserID)
LIMIT 3;

3.4 谓词下推

当 group by 有 having 子句，但是没有 with cube、with rollup 或者 with totals 修饰的时候，having 过滤会下推到 where 提前过滤。例如下面的查询，HAVING name 变成了 WHERE name，在 group by 之前过滤：

EXPLAIN SYNTAX SELECT UserID FROM datasets.hits_v1 GROUP BY UserID HAVING UserID = '8585742290196126178';

子查询也支持谓词下推：

EXPLAIN SYNTAX
SELECT *
FROM
(
    SELECT UserID
    FROM datasets.visits_v1
)
WHERE UserID = '8585742290196126178';

再来一个复杂例子：

EXPLAIN SYNTAX
SELECT *
FROM
(
    SELECT *
    FROM
    (
        SELECT UserID
        FROM datasets.visits_v1
    )
    UNION ALL
    SELECT *
    FROM
    (
        SELECT UserID
        FROM datasets.visits_v1
    )
)
WHERE UserID = '8585742290196126178';

3.5 聚合计算外推

聚合函数内的计算，会外推，例如：

EXPLAIN SYNTAX
SELECT sum(UserID * 2)
FROM datasets.visits_v1;

3.6 聚合函数消除

如果对聚合键，也就是 group by key 使用 min、max、any 聚合函数，则将函数消除，例如：

EXPLAIN SYNTAX
SELECT
    sum(UserID * 2),
    max(VisitID),
    max(UserID)
FROM datasets.visits_v1
GROUP BY UserID;

3.7 删除重复的 order by key

例如下面的语句，重复的聚合键 id 字段会被去重：

EXPLAIN SYNTAX
SELECT *
FROM datasets.visits_v1
ORDER BY
    UserID ASC,
    UserID ASC,
    VisitID ASC,
    VisitID ASC;

3.8 删除重复的 limit by key

例如下面的语句，重复声明的 name 字段会被去重：

EXPLAIN SYNTAX
SELECT *
FROM datasets.visits_v1
LIMIT 3 BY
    VisitID,
    VisitID
LIMIT 10;

3.9 删除重复的 USING Key

例如下面的语句，重复的关联键 id 字段会被去重：

EXPLAIN SYNTAX
SELECT
    a.UserID,
    a.UserID,
    b.VisitID,
    a.URL,
    b.UserID
FROM datasets.hits_v1 AS a
LEFT JOIN datasets.visits_v1 AS b USING (UserID, UserID);

3.10 标量替换

如果子查询只返回一行数据，在被引用的时候用标量替换，例如下面语句中的 total_disk_usage 字段：

EXPLAIN SYNTAX
WITH (
        SELECT sum(bytes)
        FROM system.parts
        WHERE active
    ) AS total_disk_usage
SELECT
    (sum(bytes) / total_disk_usage) * 100 AS table_disk_usage,
    table
FROM system.parts
GROUP BY table
ORDER BY table_disk_usage DESC
LIMIT 10;

3.11 三元运算优化

如果开启了 optimize_if_chain_to_multiif 参数，三元运算符会被替换成 multiIf 函数，例如：

EXPLAIN SYNTAX 
SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'atguigu') 
FROM numbers(10) 
settings optimize_if_chain_to_multiif = 1;

第 4 章 查询优化

4.1 单表查询

4.1.1 Prewhere 替代 where

Prewhere 和 where 语句的作用相同，用来过滤数据。不同之处在于 prewhere 只支持 *MergeTree 族系列引擎的表，首先会读取指定的列数据，来判断数据过滤，等待数据过滤之后再读取 select 声明的列字段来补全其余属性。

当查询列明显多于筛选列时使用 Prewhere 可十倍提升查询性能，Prewhere 会自动优化执行过滤阶段的数据读取方式，降低 io 操作。

在某些场合下，prewhere 语句比 where 语句处理的数据量更少性能更高。

-- 关闭 where 自动转 prewhere(默认情况下， where 条件会自动优化成 prewhere)
set optimize_move_to_prewhere = 0;

-- 使用 where
SELECT
    WatchID,
    JavaEnable,
    Title,
    GoodEvent,
    EventTime,
    EventDate,
    CounterID,
    ClientIP,
    ClientIP6,
    RegionID,
    UserID,
    CounterClass,
    OS,
    UserAgent,
    URL,
    Referer,
    URLDomain,
    RefererDomain,
    Refresh,
    IsRobot,
    RefererCategories,
    URLCategories,
    URLRegions,
    RefererRegions,
    ResolutionWidth,
    ResolutionHeight,
    ResolutionDepth,
    FlashMajor,
    FlashMinor,
    FlashMinor2
FROM datasets.hits_v1
WHERE UserID = '3198390223272470366';
-- 152 rows in set. Elapsed: 0.897 sec. Processed 8.87 million rows, 3.86 GB (9.89 million rows/s., 4.30 GB/s.)

-- 使用 prewhere 关键字
SELECT
    WatchID,
    JavaEnable,
    Title,
    GoodEvent,
    EventTime,
    EventDate,
    CounterID,
    ClientIP,
    ClientIP6,
    RegionID,
    UserID,
    CounterClass,
    OS,
    UserAgent,
    URL,
    Referer,
    URLDomain,
    RefererDomain,
    Refresh,
    IsRobot,
    RefererCategories,
    URLCategories,
    URLRegions,
    RefererRegions,
    ResolutionWidth,
    ResolutionHeight,
    ResolutionDepth,
    FlashMajor,
    FlashMinor,
    FlashMinor2
FROM datasets.hits_v1
PREWHERE UserID = '3198390223272470366';
-- 152 rows in set. Elapsed: 0.083 sec. Processed 8.87 million rows, 116.54 MB (106.55 million rows/s., 1.40 GB/s.)

默认情况，我们肯定不会关闭 where 自动优化成 prewhere，但是某些场景即使开启优化，也不会自动转换成 prewhere，需要手动指定 prewhere：

• 使用常量表达式
• 使用默认值为 alias 类型的字段
• 包含了 arrayJOIN，globalIn，globalNotIn 或者 indexHint 的查询
• select 查询的列字段和 where 的谓词相同
• 使用了主键字段

4.1.2 数据采样

通过采样运算可极大提升数据分析的性能

SELECT
    Title,
    count(*) AS PageViews
FROM datasets.hits_v1
SAMPLE 1 / 10 -- 代表采样10%的数据,也可以是具体的条数
WHERE CounterID = 57
GROUP BY Title
ORDER BY PageViews DESC
LIMIT 1000;

采样修饰符只有在 MergeTree engine 表中才有效，且在创建表时需要指定采样策略。

4.1.3 列裁剪与分区裁剪

数据量太大时应避免使用 select * 操作，查询的性能会与查询的字段大小和数量成线性表换，字段越少，消耗的 io 资源越少，性能就会越高。

-- 反例
SELECT * FROM datasets.hits_v1;
-- 正例
SELECT
    WatchID, 
    JavaEnable, 
    Title, 
    GoodEvent, 
    EventTime, 
    EventDate, 
    CounterID, 
    ClientIP, 
    ClientIP6, 
    RegionID, 
    UserID
FROM datasets.hits_v1;

分区裁剪就是只读取需要的分区，在过滤条件中指定。

SELECT
    WatchID, 
    JavaEnable, 
    Title, 
    GoodEvent, 
    EventTime, 
    EventDate, 
    CounterID, 
    ClientIP, 
    ClientIP6, 
    RegionID, 
    UserID
FROM datasets.hits_v1
WHERE EventDate = '2014-03-23';

4.1.4 orderby 结合 where、limit

千万以上数据集进行 order by 查询时需要搭配 where 条件和 limit 语句一起使用。

-- 正例：
SELECT
    UserID,
    Age
FROM datasets.hits_v1
WHERE CounterID = 57
ORDER BY Age DESC
LIMIT 1000;
-- 反例：
SELECT
    UserID,
    Age
FROM datasets.hits_v1 
ORDER BY Age DESC;

4.1.5 避免构建虚拟列

如非必须，不要在结果集上构建虚拟列，虚拟列非常消耗资源浪费性能，可以考虑在前端进行处理，或者在表中构造实际字段进行额外存储。

-- 反例：
SELECT Income, Age, Income/Age as IncRate FROM datasets.hits_v1;
-- 正例：拿到 Income 和 Age 后，考虑在前端进行处理，或者在表中构造实际字段进行额外存储
SELECT Income, Age FROM datasets.hits_v1;

4.1.6 uniqCombined 替代 distinct

性能可提升 10 倍以上，uniqCombined 底层采用类似 HyperLogLog 算法实现，能接受 2% 左右的数据误差，可直接使用这种去重方式提升查询性能。Count(distinct)会使用 uniqExact 精确去重。

不建议在千万级不同数据上执行 distinct 去重查询，改为近似去重 uniqCombined

-- 反例：
SELECT count(distinct rand()) FROM datasets.hits_v1;
-- 正例：
SELECT uniqCombined(rand()) FROM datasets.hits_v1;

4.1.7 使用物化视图

第 6 章详细介绍。

4.1.8 其他注意事项

1）查询熔断

为了避免因个别慢查询引起的服务雪崩的问题，除了可以为单个查询设置超时以外，还可以配置周期熔断，在一个查询周期内，如果用户频繁进行慢查询操作超出规定阈值后将无法继续进行查询操作。

2）关闭虚拟内存

物理内存和虚拟内存的数据交换，会导致查询变慢，资源允许的情况下关闭虚拟内存。

3）配置 join_use_nulls

为每一个账户添加 join_use_nulls 配置，左表中的一条记录在右表中不存在，右表的相应字段会返回该字段相应数据类型的默认值，而不是标准 SQL 中的 Null 值。

4）批量写入时先排序

批量写入数据时，必须控制每个批次的数据中涉及到的分区的数量，在写入之前最好对需要导入的数据进行排序。无序的数据或者涉及的分区太多，会导致 ClickHouse 无法及时对新导入的数据进行合并，从而影响查询性能。

5）关注 CPU

cpu 一般在 50%左右会出现查询波动，达到 70%会出现大范围的查询超时，cpu 是最关键的指标，要非常关注。

4.2 多表关联

4.2.1 准备表和数据

-- 创建小表
CREATE TABLE datasets.visits_v2
ENGINE = CollapsingMergeTree(Sign)
PARTITION BY toYYYYMM(StartDate)
ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID)
SAMPLE BY intHash32(UserID)
SETTINGS index_granularity = 8192 AS
SELECT *
FROM datasets.visits_v1
LIMIT 10000;

-- 创建 join 结果表：避免控制台疯狂打印数据
CREATE TABLE datasets.hits_v2
ENGINE = MergeTree
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID)
SETTINGS index_granularity = 8192 AS
SELECT *
FROM datasets.hits_v1
WHERE 1 = 0;

4.2.2 用 IN 代替 JOIN

当多表联查时，查询的数据仅从其中一张表出时，可考虑用 IN 操作而不是 JOIN

INSERT INTO datasets.hits_v2 SELECT a.*
FROM datasets.hits_v1 AS a
WHERE a.CounterID IN (
    SELECT CounterID
    FROM datasets.visits_v1
);
-- 0 rows in set. Elapsed: 1.595 sec. Processed 6.19 million rows, 5.86 GB (3.88 million rows/s., 3.68 GB/s.)

-- 反例：使用 join
INSERT INTO datasets.hits_v2 SELECT a.*
FROM datasets.hits_v1 AS a
LEFT JOIN datasets.visits_v1 AS b ON a.CounterID = b.CounterID;
-- 0 rows in set. Elapsed: 25.152 sec. Processed 10.55 million rows, 8.47 GB (419.58 thousand rows/s., 336.60 MB/s.)

4.2.3 大小表 JOIN

多表 join 时要满足小表在右的原则，右表关联时被加载到内存中与左表进行比较，ClickHouse 中无论是 Left join 、Right join 还是 Inner join 永远都是拿着右表中的每一条记录到左表中查找该记录是否存在，所以右表必须是小表。

1）小表在右

INSERT INTO datasets.hits_v2 SELECT a.*
FROM datasets.hits_v1 AS a
LEFT JOIN datasets.visits_v2 AS b ON a.CounterID = b.CounterID;
-- 0 rows in set. Elapsed: 23.745 sec. Processed 8.88 million rows, 8.46 GB (374.11 thousand rows/s., 356.28 MB/s.)

2）大表在右

INSERT INTO datasets.hits_v2 SELECT a.*
FROM datasets.visits_v2 AS b
LEFT JOIN datasets.hits_v1 AS a ON a.CounterID = b.CounterID;
-- Code: 241. DB::Exception: Received from localhost:9000. DB::Exception: Memory limit (total) exceeded: would use 3.31 GiB (attempt to allocate chunk of 4202528 bytes), maximum: 3.31 GiB: (while reading column UTMMedium): (while reading from part /var/lib/clickhouse/store/f5f/f5f00661-967a-4a35-b5f0-0661967afa35/201403_25_30_1/ from mark 0 with max_rows_to_read = 8192): While executing MergeTreeThread.

4.2.4 注意谓词下推（版本差异）

ClickHouse 在 join 查询时不会主动发起谓词下推的操作，需要每个子查询提前完成过滤操作，需要注意的是，是否执行谓词下推，对性能影响差别很大（新版本中已经不存在此问题，但是需要注意谓词的位置的不同依然有性能的差异）

EXPLAIN SYNTAX
SELECT a.*
FROM datasets.hits_v1 AS a
LEFT JOIN datasets.visits_v2 AS b ON a.CounterID = b.CounterID
HAVING a.EventDate = '2014-03-17';

EXPLAIN SYNTAX
SELECT a.*
FROM datasets.hits_v1 AS a
LEFT JOIN datasets.visits_v2 AS b ON a.CounterID = b.CounterID
HAVING b.StartDate = '2014-03-17';

EXPLAIN SYNTAX
INSERT INTO datasets.hits_v2 SELECT a.*
FROM datasets.hits_v1 AS a
LEFT JOIN datasets.visits_v2 AS b ON a.CounterID = b.CounterID
WHERE a.EventDate = '2014-03-17';
-- 0 rows in set. Elapsed: 5.113 sec. Processed 6.70 million rows, 5.66 GB (1.31 million rows/s., 1.11 GB/s.)

INSERT INTO datasets.hits_v2 SELECT a.*
FROM
(
    SELECT *
    FROM datasets.hits_v1
    WHERE EventDate = '2014-03-17'
) AS a
LEFT JOIN datasets.visits_v2 AS b ON a.CounterID = b.CounterID;
-- 0 rows in set. Elapsed: 5.204 sec. Processed 6.70 million rows, 5.66 GB (1.29 million rows/s., 1.09 GB/s.)

4.2.5 分布式表使用 GLOBAL

两张分布式表上的 IN 和 JOIN 之前必须加上 GLOBAL 关键字，右表只会在接收查询请求的那个节点查询一次，并将其分发到其他节点上。如果不加 GLOBAL 关键字的话，每个节点都会单独发起一次对右表的查询，而右表又是分布式表，就导致右表一共会被查询 N²次（N是该分布式表的分片数量），这就是查询放大，会带来很大开销。

4.2.6 使用字典表

将一些需要关联分析的业务创建成字典表进行 join 操作，前提是字典表不宜太大，因为字典表会常驻内存。

4.2.7 提前过滤

通过增加逻辑过滤可以减少数据扫描，达到提高执行速度及降低内存消耗的目的。

第 5 章 数据一致性（重点）

查询 CK 手册发现，即便对数据一致性支持最好的 Mergetree，也只是保证最终一致性：

我们在使用 ReplacingMergeTree、SummingMergeTree 这类表引擎的时候，会出现短暂数据不一致的情况。

在某些对一致性非常敏感的场景，通常有以下几种解决方案。

5.1 准备测试表和数据

1）创建表

CREATE TABLE test_a
(
    `user_id` UInt64,
    `score` String,
    `deleted` UInt8 DEFAULT 0,
    `create_time` DateTime DEFAULT toDateTime(0)
)
ENGINE = ReplacingMergeTree(create_time)
ORDER BY user_id;

其中：

user_id 是数据去重更新的标识；

create_time 是版本号字段，每组数据中 create_time 最大的一行表示最新的数据；

deleted 是自定的一个标记位，比如 0 代表未删除，1 代表删除数据。

2）写入 1000 万 测试数据

INSERT INTO test_a (user_id, score) WITH (
        SELECT ['A', 'B', 'C', 'D', 'E', 'F', 'G']
    ) AS dict
SELECT
    number AS user_id,
    dict[(number % 7) + 1]
FROM numbers(10000000);

3）修改前 50 万 行数据，修改内容包括 name 字段和 create_time 版本号字段

INSERT INTO test_a (user_id, score, create_time) WITH (
        SELECT ['AA', 'BB', 'CC', 'DD', 'EE', 'FF', 'GG']
    ) AS dict
SELECT
    number AS user_id,
    dict[(number % 7) + 1],
    now() AS create_time
FROM numbers(500000);

4）统计总数

SELECT COUNT() FROM test_a;

还未触发分区合并，所以还未去重。

5.2 手动 OPTIMIZE

在写入数据后，立刻执行 OPTIMIZE 强制触发新写入分区的合并动作。

OPTIMIZE TABLE test_a FINAL;

-- 语法：OPTIMIZE TABLE [db.]name [ON CLUSTER cluster] [PARTITION partition | PARTITION ID 'partition_id'] [FINAL] [DEDUPLICATE [BY expression]]

5.3 通过 Group by 去重

1）执行去重的查询

SELECT
    user_id,
    argMax(score, create_time) AS score,
    argMax(deleted, create_time) AS deleted,
    max(create_time) AS ctime
FROM test_a
GROUP BY user_id
HAVING deleted = 0;

函数说明：

• argMax(field1，field2)：按照 field2 的最大值取 field1 的值。

当我们更新数据时，会写入一行新的数据，例如上面语句中，通过查询最大的 create_time 得到修改后的 score 字段值。

2）创建视图，方便测试

CREATE VIEW view_test_a AS
SELECT
    user_id,
    argMax(score, create_time) AS score,
    argMax(deleted, create_time) AS deleted,
    max(create_time) AS ctime
FROM test_a
GROUP BY user_id
HAVING deleted = 0;

3）插入重复数据，再次查询

-- 再次插入一条数据
INSERT INTO test_a (user_id, score, create_time) VALUES(0, 'AAAA', now());
-- 再次查询
SELECT *
FROM view_test_a
WHERE user_id = 0;

4）删除数据测试

-- 再次插入一条标记为删除的数据
INSERT INTO test_a (user_id, score, deleted, create_time) VALUES(0, 'AAAA', 1, now()); 
-- 再次查询，刚才那条数据看不到了
SELECT *
FROM view_test_a
WHERE user_id = 0;

这行数据并没有被真正的删除，而是被过滤掉了。在一些合适的场景下，可以结合表级别的 TTL 最终将物理数据删除。

5.4 通过 FINAL 查询

在查询语句后增加 FINAL 修饰符，这样在查询的过程中将会执行 Merge 的特殊逻辑（例如数据去重，预聚合等）。

但是这种方法在早期版本基本没有人使用，因为在增加 FINAL 之后，我们的查询将会变成一个单线程的执行过程，查询速度非常慢。

在 v20.5.2.7-stable 版本中，FINAL 查询支持多线程执行，并且可以通过 max_final_threads 参数控制单个查询的线程数。但是目前读取 part 部分的动作依然是串行的。

FINAL 查询最终的性能和很多因素相关，列字段的大小、分区的数量等等都会影响到最终的查询时间，所以还要结合实际场景取舍。

参考链接：https://github.com/ClickHouse/ClickHouse/pull/10463

5.4.1 测试

1）普通语句查询

SELECT *
FROM datasets.visits_v1
WHERE StartDate = '2014-03-17'
LIMIT 100
SETTINGS max_threads = 2;

查看执行计划：

EXPLAIN PIPELINE
SELECT *
FROM datasets.visits_v1
WHERE StartDate = '2014-03-17'
LIMIT 100
SETTINGS max_threads = 2;

明显将由 2 个线程并行读取 part 查询。

2）FINAL 查询

SELECT *
FROM datasets.visits_v1
FINAL
WHERE StartDate = '2014-03-17'
LIMIT 100
SETTINGS max_final_threads = 2;

查询速度没有普通的查询快，但是相比之前已经有了一些提升,查看 FINAL 查询的执行计划：

EXPLAIN PIPELINE
SELECT *
FROM datasets.visits_v1
FINAL
WHERE StartDate = '2014-03-17'
LIMIT 100
SETTINGS max_final_threads = 2;

从 CollapsingSortedTransform 这一步开始已经是多线程执行，但是读取 part 部分的动作还是串行。

第 6 章 物化视图

ClickHouse 的物化视图是一种查询结果的持久化，它确实是给我们带来了查询效率的提升。用户查起来跟表没有区别，它就是一张表，它也像是一张时刻在预计算的表，创建的过程它是用了一个特殊引擎，加上后来 as select，就是 create 一个 table as select 的写法。

“查询结果集”的范围很宽泛，可以是基础表中部分数据的一份简单拷贝，也可以是多表 join 之后产生的结果或其子集，或者原始数据的聚合指标等等。所以，物化视图不会随着基础表的变化而变化，所以它也称为快照（snapshot）

6.1 概述

6.1.1 物化视图与普通视图的区别

普通视图不保存数据，保存的仅仅是查询语句，查询的时候还是从原表读取数据，可以将普通视图理解为是个子查询。物化视图则是把查询的结果根据相应的引擎存入到了磁盘或内存中，对数据重新进行了组织，你可以理解物化视图是完全的一张新表。

6.1.2 优缺点

优点：查询速度快，要是把物化视图这些规则全部写好，它比原数据查询快了很多，总的行数少了，因为都预计算好了。

缺点：它的本质是一个流式数据的使用场景，是累加式的技术，所以要用历史数据做去重、去核这样的分析，在物化视图里面是不太好用的。在某些场景的使用也是有限的。而且如果一张表加了好多物化视图，在写这张表的时候，就会消耗很多机器的资源，比如数据带宽占满、存储一下子增加了很多。

6.1.3 基本语法

也是 create 语法，会创建一个隐藏的目标表来保存视图数据。也可以 TO 表名，保存到一张显式的表。没有加 TO 表名，表名默认就是 .inner.物化视图名

CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name] [ENGINE = engine] [POPULATE] AS SELECT ...

1）创建物化视图的限制

（1）必须指定物化视图的 engine 用于数据存储

（2）TO [db].[table]语法的时候，不得使用 POPULATE。

（3）查询语句(select）可以包含下面的子句： DISTINCT, GROUP BY, ORDER BY, LIMIT…

（4）物化视图的 alter 操作有些限制，操作起来不大方便。

（5）若物化视图的定义使用了 TO [db.]name 子语句，则可以将目标表的视图 卸载 DETACH 再装载 ATTACH

2）物化视图的数据更新

（1）物化视图创建好之后，若源表被写入新数据则物化视图也会同步更新

（2）POPULATE 关键字决定了物化视图的更新策略：

• 若有 POPULATE 则在创建视图的过程会将源表已经存在的数据一并导入，类似于 create table … as
• 若无 POPULATE 则物化视图在创建之后没有数据，只会同步之后写入源表的数据
• clickhouse 官方并不推荐使用 POPULATE，因为在创建物化视图的过程中同时写入的数据不能被插入物化视图。

（3）物化视图不支持同步删除，若源表的数据不存在（删除了）则物化视图的数据仍然保留

（4）物化视图是一种特殊的数据表，可以用 show tables 查看

6.2 案例实操

对于一些确定的数据模型，可将统计指标通过物化视图的方式进行构建，这样可避免查询时重复计算的过程，物化视图会在有新数据插入时进行更新。

6.2.1 准备测试用表和数据

1）建表

-- 建表语句
CREATE TABLE datasets.hits_test
(
    `EventDate` Date,
    `CounterID` UInt32,
    `UserID` UInt64,
    `URL` String,
    `Income` UInt8
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID)
SETTINGS index_granularity = 8192;

2）导入一些数据

INSERT INTO datasets.hits_test SELECT
    EventDate,
    CounterID,
    UserID,
    URL,
    Income
FROM datasets.hits_v1
LIMIT 10000;

6.2.2 创建物化视图

-- 建表语句
CREATE MATERIALIZED VIEW datasets.hits_mv
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(EventDate)
ORDER BY (EventDate, intHash32(UserID)) AS
SELECT
    UserID,
    EventDate,
    count(URL) AS ClickCount,
    sum(Income) AS IncomeSum
FROM datasets.hits_test
WHERE EventDate >= '2014-03-20' -- 设置更新点,该时间点之前的数据可以另外通过 insert into select …… 的方式进行插入
GROUP BY
    UserID,
    EventDate;

-- 或者可以用下列语法，表 A 可以是一张 mergetree 表
CREATE MATERIALIZED VIEW 物化视图名 TO 表 A
AS SELECT FROM 表 B; -- 不建议添加 populate 关键字进行全量更新

6.3.3 导入增量数据

-- 导入增量数据
INSERT INTO datasets.hits_test SELECT
    EventDate,
    CounterID,
    UserID,
    URL,
    Income
FROM datasets.hits_v1
WHERE EventDate >= '2014-03-23'
LIMIT 10;
-- 查询物化视图
SELECT * FROM datasets.hits_mv;

6.3.4 导入历史数据

-- 导入增量数据
INSERT INTO datasets.hits_mv SELECT
    UserID,
    EventDate,
    count(URL) AS ClickCount,
    sum(Income) AS IncomeSum
FROM datasets.hits_test
WHERE EventDate = '2014-03-20'
GROUP BY
    UserID,
    EventDate;
-- 查询物化视图
SELECT * FROM datasets.hits_mv;

6.3.4 个人实际项目使用示例

1）建表

CREATE TABLE test.t_link_info
(

    `id` Int64 COMMENT '主键id',
    `create_time` DateTime64(6) COMMENT '创建时间',
    `create_userid` String COMMENT '创建人员',
    `delete_flag` Int32 COMMENT '是否删除0否1是',
    `sysid` String COMMENT '系统id',
    `unit_lv` Int32 COMMENT '录入单位级别',
    `unitcode` String COMMENT '录入单位',
    `update_time` DateTime64(6) COMMENT '修改时间',
    `beg_time` Int64 COMMENT '会话开始时间',
    `com_dur` Int32 COMMENT '会话持续时间',
    `data_len` Int32 COMMENT '数据长度',
    `data_pkt_num` Int32 COMMENT '包总数',
    `down_data_len` Int32 COMMENT '下行的数据长度',
    `down_data_pkt_num` Int32 COMMENT '下行的包个数',
    `down_pay_len` Int32 COMMENT '下行的载荷数据的长度',
    `down_pkt_num` Int32 COMMENT '下行的有载荷的包个数',
    `dst_addr` String COMMENT '目标IP地址',
    `dst_mac` String COMMENT '目标mac地址',
    `dst_port` Int32 COMMENT '目标端口',
    `end_time` Int64 COMMENT '会话结束时间',
    `ip_ver` Int32 COMMENT 'IP版本',
    `pay_len` Int32 COMMENT '会话的载荷长度',
    `pkt_num` Int32 COMMENT '会话的包个数',
    `prot_info` String COMMENT '会话协议栈信息',
    `prot_name` String COMMENT '会话协议名称',
    `prot_num` Int32 COMMENT '会话的传输层协议号',
    `src_addr` String COMMENT '源IP地址',
    `src_mac` String COMMENT '源Mac地址',
    `src_port` Int32 COMMENT '源端口',
    `uniqid` String COMMENT '唯一id,
用于业务关联',
    `up_data_len` Int32 COMMENT '上行的数据长度',
    `up_data_pkt_num` Int32 COMMENT '上行的包个数',
    `up_pay_len` Int32 COMMENT '上行的载荷长度',
    `up_pkt_num` Int32 COMMENT '上行的有载荷的包个数',
    `session_dip_addr` String COMMENT '目的ip归属地区',
    `session_dip_country` String COMMENT '目的ip归属国家',
    `session_dip_isp` String COMMENT '目的ip运营商',
    `session_dip_province` String COMMENT '目的ip归属地省份',
    `session_sip_addr` String COMMENT '源ip归属地区',
    `session_sip_country` String COMMENT '源ip归属国家',
    `session_sip_isp` String COMMENT '源IP运营商',
    `session_sip_province` String COMMENT '源ip归属地省份',
    `trans_type` String,
    `tcp_syn_cnt` Int32,
    `tcp_rst_cnt` Int32,
    `tcp_retry_cnt` Int32
)
ENGINE = MergeTree
PARTITION BY toInt64(toYYYYMM(create_time))
PRIMARY KEY (uniqid,
 id)
ORDER BY (uniqid,
 id,
 create_time,
 src_addr,
 dst_addr,
 session_sip_country,
 session_dip_country,
 src_port,
 dst_port,
 session_dip_isp,
 session_sip_isp,
 prot_name,
 prot_num,
 ip_ver,
 beg_time)
SETTINGS index_granularity = 8192;

2）导入一些数据

INSERT INTO t_link_info (id,create_time,create_userid,delete_flag,sysid,unit_lv,unitcode,update_time,beg_time,com_dur,data_len,data_pkt_num,down_data_len,down_data_pkt_num,down_pay_len,down_pkt_num,dst_addr,dst_mac,dst_port,end_time,ip_ver,pay_len,pkt_num,prot_info,prot_name,prot_num,src_addr,src_mac,src_port,uniqid,up_data_len,up_data_pkt_num,up_pay_len,up_pkt_num,session_dip_addr,session_dip_country,session_dip_isp,session_dip_province,session_sip_addr,session_sip_country,session_sip_isp,session_sip_province,transType,tcpSynCnt,tcpRstCnt,tcpRetryCnt,trans_type,tcp_syn_cnt,tcp_rst_cnt,tcp_retry_cnt) VALUES
	 (1698422839082,'2023-10-27 16:07:50.000','0',0,'0',0,'0','1970-01-01 00:00:00.000',1698422839082,30920,0,0,0,0,0,0,'128.140.73.62','08:10:7b:91:a5:e9',443,1698422870002,4,370,5,'ethernet.ipv4.tcp','unknown',6,'172.16.6.113','ac:1f:6b:ea:30:7a',39036,'000004e3-b408-0600-0000-00007c8dd8d9',0,0,370,5,'未知','德国','未知','未知','内网IP','未知','内网IP','未知','',0,0,0,'TCP',5,0,0),
	 (1698444000158,'2023-10-27 22:00:37.000','0',0,'0',0,'0','1970-01-01 00:00:00.000',1698444000158,37128,0,0,0,0,60,1,'ac10:6160::','00:00:00:00:00:00',0,1698444037286,6,180,3,'ethernet','arp',17,'b897:5ade:d3ef:ac10:140::','00:00:00:00:00:00',0,'00001cd1-b908-0600-0000-00007c8dd8d9',0,0,120,2,'0','0','0','0','0','0','0','0','',0,0,0,'UDP',0,0,0),
	 (1698402320754,'2023-10-27 10:25:55.000','0',0,'0',0,'0','1970-01-01 00:00:00.000',1698402320754,34774,0,0,0,0,300,5,'ac10:b617::','00:00:00:00:00:00',0,1698402355528,6,360,6,'ethernet','arp',17,'b897:5ade:d7cd:ac10:16b::','00:00:00:00:00:00',0,'0000251b-b008-0600-0000-00007c8dd8d9',0,0,60,1,'0','0','0','0','0','0','0','0','',0,0,0,'UDP',0,0,0),
	 (1698401131572,'2023-10-27 10:05:57.000','0',0,'0',0,'0','1970-01-01 00:00:00.000',1698401131572,26102,0,0,0,0,120,2,'ac10:b79a::','00:00:00:00:00:00',0,1698401157674,6,180,3,'ethernet','arp',6,'c85b:765b:110d:ac10:161::','00:00:00:00:00:00',0,'000027d4-af08-0600-0000-00007c8dd8d9',0,0,60,1,'0','0','0','0','0','0','0','0','',0,0,0,'TCP',0,0,0),
	 (1698401131572,'2023-10-27 10:15:46.000','0',0,'0',0,'0','1970-01-01 00:00:00.000',1698401131572,26102,0,0,0,0,120,2,'ac10:b79a::','00:00:00:00:00:00',0,1698401157674,6,180,3,'ethernet','arp',6,'c85b:765b:110d:ac10:161::','00:00:00:00:00:00',0,'000027d4-af08-0600-0000-00007c8dd8d9',0,0,60,1,'0','0','0','0','0','0','0','0','',0,0,0,'TCP',0,0,0),
	 (1698417344246,'2023-10-27 14:36:15.000','0',0,'0',0,'0','1970-01-01 00:00:00.000',1698417344246,30779,728,6,628,4,796,4,'172.16.1.107','b8:97:5a:de:d7:cd',137,1698417375025,4,980,6,'ethernet.ipv4.udp','unknown',17,'172.16.0.1','08:10:7b:91:a5:e9',16879,'0000439b-b308-0600-0000-00007c8dd8d9',100,2,184,2,'内网IP','未知','内网IP','未知','内网IP','未知','内网IP','未知','',0,0,0,'UDP',0,0,0),
	 (1698437832651,'2023-10-27 20:17:34.000','0',0,'0',0,'0','1970-01-01 00:00:00.000',1698437832651,22246,0,0,0,0,60,1,'ac10:c0c2::','00:00:00:00:00:00',0,1698437854897,6,60,1,'ethernet','arp',6,'810:7b91:a5e9:ac10:1:ffff:ffff:ffff','00:00:00:00:00:00',0,'00004f61-b808-0600-0000-00007c8dd8d9',0,0,0,0,'0','0','0','0','0','0','0','0','',0,0,0,'TCP',0,0,0),
	 (1698415215338,'2023-10-27 14:00:46.000','0',0,'0',0,'0','1970-01-01 00:00:00.000',1698415215338,30665,170,2,140,1,182,1,'8.8.8.8','08:10:7b:91:a5:e9',53,1698415246003,4,254,2,'ethernet.ipv4.udp.dns','dns',17,'172.16.1.68','b8:97:5a:77:ec:95',62992,'0000521c-b308-0600-0000-00007c8dd8d9',30,1,72,1,'未知','美国','Level3','未知','内网IP','未知','内网IP','未知','',0,0,0,'UDP',0,0,0),
	 (1698413154499,'2023-10-27 13:26:21.000','0',0,'0',0,'0','1970-01-01 00:00:00.000',1698413154499,26950,0,0,0,0,180,3,'ac10:3556::','00:00:00:00:00:00',0,1698413181449,6,360,6,'ethernet','arp',17,'b897:5ade:d449:ac10:1f4::','00:00:00:00:00:00',0,'000061a1-b208-0600-0000-00007c8dd8d9',0,0,180,3,'0','0','0','0','0','0','0','0','',0,0,0,'UDP',0,0,0),
	 (1698407589303,'2023-10-27 11:53:40.000','0',0,'0',0,'0','1970-01-01 00:00:00.000',1698407589303,30701,9453,16,8141,11,9035,16,'160.16.208.169','08:10:7b:91:a5:e9',443,1698407620004,4,10983,27,'ethernet.ipv4.tcp','unknown',6,'172.16.1.24','b8:97:5a:de:ce:b2',60724,'00006355-b108-0600-0000-00007c8dd8d9',1312,5,1948,11,'东京','日本','未知','东京都','内网IP','未知','内网IP','未知','',0,0,0,'TCP',0,0,0);

3）创建物化视图

--根据源IP分组按天物化
CREATE MATERIALIZED VIEW view_link_sip
ENGINE=SummingMergeTree
PARTITION BY toYYYYMM(create_time) 
ORDER BY (src_addr) 
populate
AS SELECT
	src_addr AS src_addr,
	any(uniqid) AS uniqid,
	toStartOfDay(create_time) as create_time,
    sum(up_data_len) AS up_data_len,
    sum(up_data_pkt_num) AS up_data_pkt_num,
    sum(down_data_len) AS down_data_len,
    sum(down_data_pkt_num) AS down_data_pkt_num,
    count() as count
FROM t_link_info
GROUP BY src_addr,create_time

4）查询物化视图数据

# 查询所有表名称，包括物化视图
show tables;

# 查询物化视图，在数据量大的时候，查询记得加limit，不然会造成内存溢出
select * from view_link_sip limit 10;

第 7 章 MaterializeMySQL 引擎

7.1 概述

MySQL 的用户群体很大，为了能够增强数据的实时性，很多解决方案会利用 binlog 将数据写入到 ClickHouse。为了能够监听 binlog 事件，我们需要用到类似 canal 这样的第三方中间件，这无疑增加了系统的复杂度。

ClickHouse 20.8.2.3 版本新增加了 MaterializeMySQL 的 database 引擎，该 database 能映射到 MySQL 中的某个 database ，并自动在 ClickHouse 中创建对应的ReplacingMergeTree。ClickHouse 服务做为 MySQL 副本，读取 Binlog 并执行 DDL 和 DML 请求，实现了基于 MySQL Binlog 机制的业务数据库实时同步功能。

7.1.1 特点

1）MaterializeMySQL 同时支持全量和增量同步，在 database 创建之初会全量同步 MySQL 中的表和数据，之后则会通过 binlog 进行增量同步。

2）MaterializeMySQL database 为其所创建的每张 ReplacingMergeTree 自动增加了 _sign 和 _version 字段。

其中，_version 用作 ReplacingMergeTree 的 ver 版本参数，每当监听到 insert、update 和 delete 事件时，在 databse 内全局自增。而 _sign 则用于标记是否被删除，取值 1 或者 -1。

目前 MaterializeMySQL 支持如下几种 binlog 事件：

• MYSQL_WRITE_ROWS_EVENT： _sign = 1，_version ++
• MYSQL_DELETE_ROWS_EVENT： _sign = -1，_version ++
• MYSQL_UPDATE_ROWS_EVENT：新数据 _sign = 1
• MYSQL_QUERY_EVENT：支持 CREATE TABLE 、DROP TABLE 、RENAME TABLE 等。

7.1.2 使用细则

1）DDL 查询

MySQL DDL 查询被转换成相应的 ClickHouse DDL 查询（ALTER, CREATE, DROP, RENAME）。如果 ClickHouse 不能解析某些 DDL 查询，该查询将被忽略。

2）数据复制

MaterializeMySQL 不支持直接插入、删除和更新查询，而是将 DDL 语句进行相应转换：

MySQL INSERT 查询被转换为 INSERT with _sign=1。

MySQL DELETE 查询被转换为 INSERT with _sign=-1。

MySQL UPDATE 查询被转换成 INSERT with _sign=1 和 INSERT with _sign=-1。

3）SELECT 查询

如果在 SELECT 查询中没有指定_version，则使用 FINAL 修饰符，返回_version 的最大值对应的数据，即最新版本的数据。

如果在 SELECT 查询中没有指定_sign，则默认使用 WHERE _sign=1，即返回未删除状态（_sign=1)的数据。

4）索引转换

ClickHouse 数据库表会自动将 MySQL 主键和索引子句转换为 ORDER BY 元组。

ClickHouse 只有一个物理顺序，由 ORDER BY 子句决定。如果需要创建新的物理顺序，请使用物化视图。

7.2 案例实操

7.2.1 MySQL 开启 binlog 和 GTID 模式

1）确保 MySQL 开启了 binlog 功能，且格式为 ROW

打开/etc/my.cnf，在[mysqld]下添加：

server-id=1 
log-bin=mysql-bin
binlog_format=ROW

2）开启 GTID 模式

如果如果 clickhouse 使用的是 20.8 prestable 之后发布的版本，那么 MySQL 还需要配置开启 GTID 模式, 这种方式在 mysql 主从模式下可以确保数据同步的一致性(主从切换时)。

gtid-mode=on
enforce-gtid-consistency=1 # 设置为主从强一致性
log-slave-updates=1 # 记录日志

GTID 是 MySQL 复制增强版，从 MySQL 5.6 版本开始支持，目前已经是 MySQL 主流复制模式。它为每个 event 分配一个全局唯一 ID 和序号，我们可以不用关心 MySQL 集群主从拓扑结构，直接告知 MySQL 这个 GTID 即可。

3）启用旧的加密方式

default_authentication_plugin=mysql_native_password

4）重启 MySQL

sudo systemctl restart mysqld

7.2.2 准备 MySQL 表和数据

1）在 MySQL 中创建数据表并写入数据

CREATE DATABASE testck;

CREATE TABLE `testck`.`t_organization` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `code` int NOT NULL,
 `name` text DEFAULT NULL,
 `updatetime` datetime DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY (`code`)
) ENGINE=InnoDB;

INSERT INTO testck.t_organization (code, name,updatetime) 
VALUES(1000,'Realinsight',NOW());
INSERT INTO testck.t_organization (code, name,updatetime) 
VALUES(1001, 'Realindex',NOW());
INSERT INTO testck.t_organization (code, name,updatetime) 
VALUES(1002,'EDT',NOW());

2）创建第二张表

CREATE TABLE `testck`.`t_user` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `code` int,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO testck.t_user (code) VALUES(1);

7.2.3 开启 ClickHouse 物化引擎

SET allow_experimental_database_materialize_mysql = 1;

7.2.4 创建复制管道

1）ClickHouse 中创建 MaterializeMySQL 数据库

CREATE DATABASE test_binlog
ENGINE = MaterializeMySQL('hadoop102:3306', 'testck', 'root', 'abc123');

其中 4 个参数分别是 MySQL 地址、databse、username 和 password。

2）查看 ClickHouse 的数据

USE test_binlog;
SHOW TABLES;
SELECT * FROM t_organization;
SELECT * FROM t_user;

7.2.5 修改数据

1）在 MySQL 中修改数据:

UPDATE t_organization SET name = CONCAT(name,'-v1') WHERE id = 1;

2）查看 clickhouse 日志可以看到 binlog 监听事件，查询 clickhouse

SELECT * FROM t_organization;

7.2.6 删除数据

1）MySQL 删除数据:

DELETE FROM t_organization WHERE id = 2;

2）ClicKHouse，日志有 DeleteRows 的 binlog 监听事件，查看数据：

SELECT * FROM t_organization;

3）在刚才的查询中增加 _sign 和 _version 虚拟字段

SELECT
    *,
    _sign,
    _version
FROM t_organization
ORDER BY
    _sign DESC,
    _version DESC;

在查询时，对于已经被删除的数据，_sign=-1，ClickHouse 会自动重写 SQL，将 _sign = -1 的数据过滤掉;

对于修改的数据，则自动重写 SQL，为其增加 FINAL 修饰符。

SELECT * FROM t_organization;
-- 等同于
SELECT * FROM t_organization FINAL WHERE _sign = 1;

7.2.7 删除表

1）在 mysql 执行删除表

DROP TABLE t_user;

2）此时在 clickhouse 处会同步删除对应表，如果查询会报错

SHOW TABLES;
SELECT * FROM t_user;
-- Code: 60. DB::Exception: Received from localhost:9000. DB::Exception: Table test_binlog.t_user doesn't exist. 

3）mysql 新建表，clickhouse 可以查询到

CREATE TABLE `testck`.`t_user` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `code` int,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO testck.t_user (code) VALUES(1);

-- ClickHouse 查询
SHOW TABLES;
SELECT * FROM t_user;

第 8 章 常见问题排查

8.1 分布式 DDL 某数据节点的副本不执行

1）问题：使用分布式 ddl 执行命令 create table on cluster xxxx 某个节点上没有创建表，但是 client 返回正常，查看日志有如下报错。

1）问题：使用分布式 ddl 执行命令 create table on cluster xxxx 某个节点上没有创建表，但是 client 返回正常，查看日志有如下报错。

2）解决办法：重启该不执行的节点。

8.2 数据副本表和数据不一致

1）问题：由于某个数据节点副本异常，导致两数据副本表不一致，某个数据副本缺少表，需要将两个数据副本调整一致。

2）解决办法：

在缺少表的数据副本节点上创建缺少的表，创建为本地表，表结构可以在其他数据副本通过 show crete table xxxx 获取。

表结构创建后，clickhouse 会自动从其他副本同步该表数据，验证数据量是否一致即可。

8.3 副本节点全量恢复

1）问题：某个数据副本异常无法启动，需要重新搭建副本。

2）解决办法：

清空异常副本节点的 metadata 和 data 目录。

从另一个正常副本将 metadata 目录拷贝过来（这一步之后可以启动数据库，但是只有表结构没有数据）。

执行sudo -u clickhouse touch /data/clickhouse/flags/force_restore_data

启动数据库。

8.4 数据副本启动缺少 zk 表

1）问题：某个数据副本表在 zk 上丢失数据，或者不存在，但是 metadata 元数据里存在，导致启动异常，报错：

Can’t get data for node /clickhouse/tables/01-02/xxxxx/xxxxxxx/replicas/xxx/metadata: node doesn’t exist (No node): Cannot attach table xxxxxxx

2）解决办法：

metadata 中移除该表的结构文件，如果多个表报错都移除

mv metadata/xxxxxx/xxxxxxxx.sql /tmp/

启动数据库

手工创建缺少的表，表结构从其他节点 show create table 获取。

创建后会自动同步数据，验证数据是否一致。

8.5 ZK table replicas 数据未删除，导致重建表报错

1）问题：重建表过程中，先使用 drop table xxx on cluster xxx，各节点在 clickhouse 上 table 已物理删除，但是 zk 里面针对某个 clickhouse 节点的 table meta 信息未被删除（低概率事件），因 zk 里仍存在该表的 meta 信息，导致再次创建该表 create table xxx on cluster, 该节点无法创建表(其他节点创建表成功)，报错：

Replica /clickhouse/tables/01-03/xxxxxx/xxx/replicas/xxx already exists..

2）解决办法：

从其他数据副本 cp 该 table 的 metadata sql 过来。

重启节点。

8.6 Clickhouse 节点意外关闭

1）问题：模拟其中一个节点意外宕机，在大量 insert 数据的情况下，关闭某个节点。

2）现象：数据写入不受影响、数据查询不受影响、建表 DDL 执行到异常节点会卡住，报错：

Code: 159. DB::Exception: Received from localhost:9000. DB::Exception: Watching task /clickhouse/task_queue/ddl/query-0000565925 is executing longer than distributed_ddl_task_timeout (=180) seconds. There are 1 unfinished hosts (0 of them are currently active), they are going to execute the query in background.

3）解决办法：启动异常节点，期间其他副本写入数据会自动同步过来，其他副本的建表 DDL 也会同步。

8.7 其他问题参考

https://help.aliyun.com/document_detail/162815.html?spm=a2c4g.11186623.6.652.312e79bd17U8IO

第 二 部分 监控及备份篇

第 1 章 ClickHouse 监控概述

ClickHouse 运行时会将一些个自身的运行状态记录到众多系统表中( system.*)。所以我们对于 CH 自身的一些运行指标的监控数据，也主要来自这些系统表。

但是直接查询这些系统表会有一些不足之处：

• 这种方式太过底层，不够直观，我们还需要在此之上实现可视化展示；
• 系统表只记录了 CH 自己的运行指标，有些时候我们需要外部系统的指标进行关联分析，例如 ZooKeeper、服务器 CPU、IO 等等。

现在 Prometheus + Grafana 的组合比较流行，安装简单易上手，可以集成很多框架，包括服务器的负载，其中 Prometheus 负责收集各类系统的运行指标；Grafana 负责可视化的部分。

ClickHouse 从 v20.1.2.4 开始，内置了对接 Prometheus 的功能，配置的方式也很简单，可以将其作为 Prometheus 的 Endpoint 服务，从而自动的将 metrics 、 events 和 asynchronous_metrics 三张系统的表的数据发送给 Prometheus。

第 2 章 Prometheus&Grafana 的安装

Prometheus 下载地址：https://prometheus.io/download/

Grafana 下载地址：https://grafana.com/grafana/download

2.1 安装 Prometheus

Prometheus 基于 Golang 编写，编译后的软件包，不依赖于任何的第三方依赖。只需要下载对应平台的二进制包，解压并且添加基本的配置即可正常启动 Prometheus Server。

2.1.1 上传安装包

上传 prometheus-2.37.1.linux-amd64.tar.gz 到虚拟机的/opt/software 目录

2.1.2 解压安装包

1）解压到/opt/module 目录下

tar -zxvf prometheus-2.37.1.linux-amd64.tar.gz -C /opt/module

2）修改目录名

cd /opt/module
mv prometheus-2.37.1.linux-amd64 prometheus-2.37.1

2.1.3 修改配置文件 prometheus.yml

vim prometheus.yml 在 scrape_configs 配置项下添加配置：

scrape_configs:

  - job_name: "prometheus"
    static_configs:
      - targets: ["localhost:9090"]

  #添加 ClickHouse 监控配置
  - job_name: "clickhouse"
    static_configs:
    - targets: ['localhost:9363']

配置说明：

1）global 配置块：控制 Prometheus 服务器的全局配置

• scrape_interval：配置拉取数据的时间间隔，默认为 1 分钟。
• evaluation_interval：规则验证（生成 alert）的时间间隔，默认为 1 分钟。

2）rule_files 配置块：规则配置文件

3）scrape_configs 配置块：配置采集目标相关， prometheus 监视的目标。Prometheus 自身的运行信息可以通过 HTTP 访问，所以 Prometheus 可以监控自己的运行数据。

• job_name：监控作业的名称
• static_configs：表示静态目标配置，就是固定从某个 target 拉取数据
• targets ： 指定监控的目 标，其实就是从哪儿拉取数据。 Prometheus会从http://localhost:9090/metrics 上拉取数据。

Prometheus 是可以在运行时自动加载配置的。启动时需要添加：–web.enable-lifecycle

2.1.4 启动 Prometheus Server

nohup ./prometheus --config.file=prometheus.yml > ./prometheus.log 2>&1 &

• 浏览器输入：http://localhost:9090/
• 点击 Status，选中 Targets：

prometheus 是 up 状态，表示安装启动成功

2.2 Grafana 安装

2.2.1 上传并解压

1）将 grafana-9.1.5.linux-amd64.tar.gz 上传至/opt/software/目录下，解压：

tar -zxvf grafana-9.1.5.linux-amd64.tar.gz -C /opt/module/

2）更改名字：

cd /opt/module
# 如果解压后就是grafana-9.1.5，无需更改名字
mv grafana-9.1.5.linux-amd64 grafana-9.1.5

2.2.2 启动 Grafana

nohup ./bin/grafana-server web > ./grafana.log 2>&1 &

第 3 章 ClickHouse 配置

3.1 修改配置文件

编辑 /etc/clickhouse-server/config.xml，打开如下配置：

<prometheus>
    <endpoint>/metrics</endpoint>
    <port>9363</port>

    <metrics>true</metrics>
    <events>true</events>
    <asynchronous_metrics>true</asynchronous_metrics>
    <status_info>true</status_info>
</prometheus>

如果有多个 CH 节点，分发配置。

3.2 重启 ClickHouse

sudo clickhouse restart

3.3 访问 Web 查看

浏览器打开：http://localhost:9363/metrics，看到信息说明 ClickHouse 开启 Metrics 服务成功。

第 4 章 Grafana 集成 Prometheus

4.1 添加数据源 Prometheus

1）点击配置，点击 Data Sources：

2）点击添加按钮：

3）找到 Prometheus，单击

4）配置 Prometheus Server 地址：

5）点击下方的 Save&Test：

6）出现绿色的提示框，表示与 Prometheus 正常联通：

7）点击 Back 返回即可，可以看到 Data Sources 页面，出现了添加的 Prometheus:

4.2 添加监控

手动一个个添加 Dashboard 比较繁琐，Grafana 社区鼓励用户分享 Dashboard，通过 https://grafana.com/dashboards 网站，可以找到大量可直接使用的 Dashboard 模板。

Grafana 中所有的 Dashboard 通过 JSON 进行共享，下载并且导入这些 JSON 文件，就可以直接使用这些已经定义好的 Dashboard。

1）点击左侧 ”+”号，选择 import：

2）上传 JSON 文件：

第 5 章 备份及恢复

官网：https://clickhouse.com/docs/en/operations/backup/

5.1 手动实现备份及恢复

ClickHouse 允许使用 ALTER TABLE … FREEZE PARTITION … 查询以创建表分区的本地副本。这是利用硬链接(hardlink)到 /var/lib/clickhouse/shadow/ 文件夹中实现的，所以它通常不会因为旧数据而占用额外的磁盘空间。 创建的文件副本不由 ClickHouse 服务器处理，所以不需要任何额外的外部系统就有一个简单的备份。防止硬件问题，最好将它们远程复制到另一个位置，然后删除本地副本。

5.1.1 创建备份路径

1）创建用于存放备份数据的目录 shadow

sudo mkdir -p /var/lib/clickhouse/shadow/
# 修改所属组
chown clickhouse:clickhouse /var/lib/clickhouse/shadow

5.1.2 执行备份命令

echo -n 'alter table t_order_mt freeze' | clickhouse-client [--password]

5.1.3 将备份数据保存到其他路径

#创建备份存储路径
sudo mkdir -p /var/lib/clickhouse/backup/
# 修改所属组
chown clickhouse:clickhouse /var/lib/clickhouse/backup
#拷贝数据到备份路径
sudo cp -r /var/lib/clickhouse/shadow/ /var/lib/clickhouse/backup/my-backup-name
#为下次备份准备，删除 shadow 下的数据
sudo rm -rf /var/lib/clickhouse/shadow/*

5.1.4 恢复数据

1）模拟删除备份过的表

echo 'drop table t_order_mt' | clickhouse-client [--password]

2）重新创建表

CREATE TABLE t_order_mt
(
    `id` UInt32,
    `sku_id` String,
    `total_amount` Decimal(16, 2),
    `create_time` Datetime
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(create_time)
PRIMARY KEY id
ORDER BY (id, sku_id);

3）将备份复制到 detached 目录

sudo cp -rl backup/my-backup-name/1/store/d0e/d0ec02eb-e896-4fee-90ec-02ebe896cfee/* data/default/t_order_mt/detached/
# 修改该目录及其下文件所属组
chown -R clickhouse:clickhouse data/default/t_order_mt/detached

ClickHouse 使用文件系统硬链接来实现即时备份，而不会导致 ClickHouse 服务停机（或锁定）。这些硬链接可以进一步用于有效的备份存储。在支持硬链接的文件系统（例如本地文件系统或 NFS）上，将 cp 与 -l 标志一起使用（或将 rsync 与 –hard-links 和 –numeric-ids 标志一起使用）以避免复制数据。

注意：仅拷贝分区目录，注意目录所属的用户要是 clickhouse

4）执行 attach

echo 'alter table t_order_mt attach partition 20200601' | clickhouse-client [--password]

5）查看数据

echo 'select count() from t_order_mt' | clickhouse-client [--password]

5.2 使用 clickhouse-backup

上面的过程，我们可以使用 Clickhouse 的备份工具 clickhouse-backup 帮我们自动化实现。

工具地址：https://github.com/AlexAkulov/clickhouse-backup/

5.2.1 上传并安装

将 clickhouse-backup-2.0.0-1.x86_64.rpm 上传至/opt/software/目录下，安装：

sudo rpm -ivh clickhouse-backup-2.0.0-1.x86_64.rpm

5.2.2 配置文件

# 修改文件名
mv /etc/clickhouse-backup/config.yml.example /etc/clickhouse-backup/config.yml
# 查看配置文件
cat /etc/clickhouse-backup/config.yml

如果配置了密码，则需要修改配置文件

5.2.3 创建备份

1）查看可用命令

clickhouse-backup help

2）显示要备份的表

clickhouse-backup tables

3）创建备份

sudo clickhouse-backup create

4）查看现有的本地备份

sudo clickhouse-backup list

备份存储在 /var/lib/clickhouse/backup/BACKUPNAME。备份名称默认为日期，但是可以选择使用 –name 标志指定备份名称。备份包含两个目录：一个“metadata”目录，其中包含重新创建架构所需的 DDL SQL 语句；以及一个“shadow”目录，其中包含作为 ALTER TABLE … FREEZE 操作结果的数据。

5.2.4 从备份恢复数据

1）模拟删除备份过的表

echo 'drop table t_order_rmt' | clickhouse-client [--password]

2）从备份还原

sudo clickhouse-backup restore -t default.t_order_rmt 2022-09-19T16-31-15

• –schema 参数：只还原表结构。
• –data 参数：只还原数据。
• –table 参数：备份（或还原）特定表。也可以使用一个正则表达式，例如，针对特定的数据库：–table=dbname.*。

注意：实测，一定要添加 –table 参数，否则不仅恢复不成功，还会清空数据库！

5.2.5 其他说明

1）API 文档：https://github.com/AlexAkulov/clickhouse-backup#api

2）注意事项：切勿更改文件夹 /var/lib/clickhouse/backup 的权限，可能会导致数据损坏。

3）远程备份

（1）上传到远程存储：sudo clickhouse-backup upload xxxx
（2）从远程存储下载：sudo clickhouse-backup download xxxx
（3）保存周期：

• backups_to_keep_local，本地保存周期，单位天
• backups_to_keep_remote，远程存储保存周期，单位天
• 0 均表示不删除

参考资料

[1] 一套上手ClickHouse-OLAP分析引擎，囊括Prometheus与Grafana

[2] ClickHouse 官方文档文章来源地址https://www.toymoban.com/news/detail-745811.html

到了这里，关于ClickHouse 学习之从高级到监控以及备份（二）的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！