clickhouse 使用笔记

发表于： 更新于： 分类于：
字数： 1676 阅读：≈ 4分钟

clickhouse 安装

安装的时候，最开始是在 Mac 系统上安装的，后来在 Windows 上又重新使用 Docker 和 WSL 进行复制。

在 Windows 上使用 Docker 有一些小问题需要注意。

Mac 系统

准备使用 orbstack 安装 clickhouse 的 docker 镜像，安装命令如下：

docker run -d --name some-clickhouse-server --ulimit nofile=262144:262144 clickhouse/clickhouse-server

连接客户端的命令为：

docker run -it --rm --link some-clickhouse-server:clickhouse-server --entrypoint clickhouse-client clickhouse/clickhouse-server --host clickhouse-server
# OR
docker exec -it some-clickhouse-server clickhouse-client

启停容器：

docker stop some-clickhouse-server
docker rm some-clickhouse-server

登录 clickhouse

clickhouse-client -u default -h localhost

Windows 系统

安装 docker desktop ，然后配置对应的国内镜像：

{
    ...,
    "registry-mirrors": [
        "https://registry.docker-cn.com",
        "http://hub-mirror.c.163.com",
        "https://docker.mirrors.ustc.edu.cn",
    ]
}

拉取对应的镜像后，需要要启用镜像容器时，在启动时需要填写对应有 Optional settings ，其中 Ports 需要指定对应的端口的映射方案。即 8123:8123 9000:9000 9009:9009 。

数据库终端

数据库客户端使用 dbeaver ，对应的驱动名为： com.clickhouse.jdbc.ClickHouseDriver 配置好 URL 模板后，使用 orbstack 地址进行 http 访问。

注意使用 localhost 的话需要调整对应的端口号。http 使用 8123 端口，本地 server 使用 9000 端口。

数据库的批量写入

使用 Python 下载 akshare 的数据保存到 clickhouse 时，查询了一上，一般建议是使用 pandahouse 或者 clickhouse_driver 。翻看了一下对应的项目源码，已经很久没做更新了。在阅读 clickhouse 的官方网站时，发现建议使用 clickhouse_connect 进行数据库的操作。

这里对数据时行计算后，将相同数量的数据写入到表中。

ph.to_clickhouse(df, tablename, index=False, connection=connection)

441 ms ± 65.2 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

client.insert_df(table_name, df, DB)

56.8 ms ± 3.53 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

大概写了一下数据库的写入，测试一下时间， client.insert() 大概在 60ms 左右，而使用 pandahouse.to_clickhouse() 则需要 500ms ，数量一共大概在 7703 rows x 39 columns 。

最终决定使用 clickhouse_connect 实现数据的写入和清理操作。

数据库查询

因为写入过程还需要再做一下测试，使用 client.query_df() 对数据表进行查询，语句如下：

sql = "SELECT * FROM db.table"
parameters = {}
mm = client.query_df(sql)

查询 63W Rows × 45 columns 数据的时间如下：

2.17 s ± 329 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

表引擎 ReplacingMergeTree

作用

MergeTree 支持主键，但主键是用来缩小查询范围的，不具备唯一性约束，而 ReplacingMergeTree 相对 MergeTree 增加了去重功能，它仅会在合并分区时，删除重复的数据，且其在写入时，不会因为插入相同主键的数据报错。

ver 参数

通过为表引擎增加 ver 参数来添加去重算法，如果没有设置该参数，合并时保留分组内的最后一条数据。如果指定了该参数，则保留 ver 字段聚会最大的那一行。

CREATE TABLE TEST
{
    id String,
    verstion DateTime
}
ENGINE = ReplacingMergeTree(version)
PARTITION BY toYYYYMM(version)
PRIMARY KEY ID
ORDER BY (ID)

插入重复数据后，可以使用 optimize table test FINAL; 命令强制者分区合并。

特点

  • 使用 ORDER BY 排序键，作为判断数据是否重复的唯一数据
  • 只有在合并分区时，才会触发数据的去重逻辑
  • 删除重复数据，是以数据分区为单位，同一个数据分区的重复数据才会被删除，不同数据分区的重复数据仍会保留
  • 去重策略：
    • 若指定了 ver 参数，则会保留重复数据中， ver 字段最大的那一行
    • 若未指定 ver 参数，则会保留重复数据中对最末的那一行数据。

物化视图

物化视图是对应一份持久化的存储，可以是物理表的一份数据子集拷贝，也可以是多表 JOIN 或都预聚合的一个结果或子集。 Clickhouse 的物化视图实现更像是触发器，如果 View 中预告定义了聚合函数，那么聚合函数仅适用于新插入的数据。对源表数据的更改都不会更改物化视图。

对于大型表，提前计算统计结果更快也更节省资源。可以将结果放在单独的物化视图中。

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER] [TO[db.]name] [ENGINE = engine] [POPULATE] AS SELECT ...
POPULATE
将表中现有数据存储到视图中，否则只会写入创建视图之后的数据。

Projection 功能

利用一组列的组合，可以按照与万年青不同的排序存储，并且支持聚合函数的查询。其特点如下：

part-level 存储
相比普通视图是一张独立的表， Projection 物化的数据就保存在万年青的分区目录中，支持明细数据的普通 Projection 和预聚合 Projection
无感使用，自动命中
可以对一张 MergeTree 创建多个 Projection ，当执行 select 语句时，能根据查询范围，自动匹配最优的 Projection 提供查询回事，没命中则查询底表
数据同源
数据保存在原表分区，所以数据的更新、合并都是同源的，也就不会出现不致的情况

创建 Projection 的语句如下：

alter table table_name add PROJECTION projection_name
(
    select x,y
    order by z
)

所有数据需要在创建 Projection 之后，才会自动物化，对于历史数据，需要手动触发物化。

alter table table_name MATERIALIZE PROJECTION projection_name;

执行相同查询时，可以利用参数增加效率优化：

set allow_experimental_projection_optimization = 1;

除了查询明细数据的优化外，还可以做聚合查询：

alter table table_name add PROJECTION projection_name
(
    select
        id,
        count(*),
        avg()
    group by id
)

需要删除时则使用 alter table table_name drop projection projection_name 进行删除。

PROJECTION 匹配原则

  1. 设置了 SET allow_experimental_projection_optimization = 1
  2. 返回的数据行小于基表总数
  3. 查询覆盖的分区 part 超过一半
  4. Where 必须是 PROJECTION 定义中 GROUP BY 的子集
  5. GROUP BY 必须是 PROJECTION 定义中 GROUP BY 的子集
  6. SELECT 必须是 PROJECTION 定义中 SELECT 的子集
  7. 匹配多个 PROJECTION 的时候，选取读取 part 最少的

如果你不知道查询是否匹配了 PROJECTION ，有两种方式可以校验：

使用 explain 查询执行计划

explain
select id from table_name where cond=cond1

显示查询计划中， with 0 projection projection_name 即表示命令 PROJECTION 。