ClickHouse

介绍

ClickHouse是一个用于联机分析(OLAP)的列式数据库管理系统(DBMS)。 官网介绍、特性、性能

安装

​x
# 配置repo
#sudo yum install -y yum-utils
#sudo yum-config-manager --add-repo https://packages.clickhouse.com/rpm/clickhouse.repo

cat > clickhouse.repo <<EOF
[clickhouse-stable]
name=ClickHouse - Stable Repository
baseurl=https://packages.clickhouse.com/rpm/stable/
gpgkey=https://packages.clickhouse.com/rpm/stable/repodata/repomd.xml.key
gpgcheck=0
repo_gpgcheck=1
enabled=1

[clickhouse-lts]
name=ClickHouse - LTS Repository
baseurl=https://packages.clickhouse.com/rpm/lts/
gpgkey=https://packages.clickhouse.com/rpm/lts/repodata/repomd.xml.key
gpgcheck=0
repo_gpgcheck=1
enabled=0
EOF

# 安装clickhouse
sudo yum install -y clickhouse-server clickhouse-client

# 启动clickhouse-server
sudo systemctl enable clickhouse-server
sudo systemctl start clickhouse-server
sudo systemctl status clickhouse-server
sudo systemctl stop clickhouse-server

# 启动客户端
clickhouse-client # or "clickhouse-client --password" if you set up a password.

使用

表引擎

Clickhouse创建本地表的时候需要指定表引擎，类似于mysql可以选择innodb或者myisam，不同的表引擎具有不同特性，发挥不同功能，需要根据具体业务场景选择。

日常业务中使用最多的就是MergeTree系列表引擎，如下：

以上表引擎为单副本情况下，如果使用多副本需要使用Replicated*系列的引擎，这样可以配合zookeeper实现副本数据复制

分布式表引擎：Distributed，用于创建分布式表

库表创建&查询

建库

xxxxxxxxxx
--建库语法：
create database if not exists 库名
on cluster 集群名
engine = Ordinary;  //engine就固定用这个

--例：
create database if not exists saas_aries
on cluster default_cluster
engine = Ordinary;

--使用库：
USE saas_aries;

建表

1、 本地表与分布式表

Clickhouse中的表有本地表和分布式表的概念：

本地表指各个分片节点自身的表，在各个分片节点上分别存储各自的数据，在不同的分片节点上查询本地表也只会展示当前节点上的数据；

分布式表需要关联到本地表，本身不存储数据，实际上相当于一张分布式视图，在任意节点查询分布式表，clickhouse会把计算分发到各分片节点，汇总各节点的计算结果后返回全局结果

例如：我们有3个分片节点，有一张本地表t_data_local，共100W数据，那么节点1可能存储30W数据，节点2有20W数据，节点3有50W数据；另外有一张分布式表t_data关联到t_data_local

在节点1上查询select count(*) from t_data_local，数据量为30W；节点2上查询为20W，节点3为50W

而不管在哪个节点查询select count(*) from t_data，均为100W数据

所以查询需要查询分布式表才能获取完整的数据

Clickhouse中建表步骤分为两步：1）建立本地表；2）建立分布式表，

2、创建本地表

建议规范：分布式表使用正常的表名，本地表在后面加_local以示区分

xxxxxxxxxx
--建表语法：
CREATE TABLE IF NOT EXISTS 库名.本地表名
ON CLUSTER 集群名
(
    字段1 类型 [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1], //clickhouse也支持列的ttl，过期后这一列数据删除
    字段2 类型 [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
    INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = 某引擎
ORDER BY (排序键)
[PARTITION BY expr] //分区键
[PRIMARY KEY expr] //主键，一般不设，默认跟排序键相同
[SAMPLE BY expr] //Clickhouse支持抽样
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...] //ttl，类似cassandra，clickhouse支持数据行的ttl，过期后自动删除数据
[SETTINGS name=value, ...] //一些表的设置项

--以ReplicatedReplacingMergeTree为例：
create table if not exists saas_aries.dim_ec_store_local on cluster default_cluster(
    `pid` String comment '商家ID'
    ,`store_id` String comment '所属门店ID'
    ,`store_name` String comment '所属门店名称'
    ,`is_head_store` String
    ,`create_time` DateTime comment '创建时间'
)
engine = ReplicatedReplacingMergeTree(
    '/clickhouse/tables/{shard}/saas_aries/dim_ec_store_local', '{replica}'
)
order by (pid, store_id)
settings index_granularity = 8192;

3、 创建分布式表

建好本地表后，需要建立分布式表关联本地表

xxxxxxxxxx
--建表语法(使用Distributed引擎)：
create table if not exists 库名.分布式表名 on cluster 集群名 as 库名.本地表名 engine = Distributed(集群名, 库名, 本地表名, 分布式规则);

--例：
create table if not exists saas_aries.dim_ec_store_distribute on cluster default_cluster(
    `pid` String comment '商家ID'
    ,`store_id` String comment '所属门店ID'
    ,`store_name` String comment '所属门店名称'
    ,`is_head_store` String
    ,`create_time` DateTime comment '创建时间'
)
engine = Distributed(default_cluster, saas_aries, dim_ec_store_local, rand(oneid));

--//rand表示随机分布，也可以定义其他分布策略，如hash函数等

备注:所有的DDL语句务必都加上on cluster 集群名，这样在任意一个节点执行就相当于在集群所有节点执行，不然就需要到每个节点上依次执行一遍这个语句

4、 创建视图

选择性创建视图

xxxxxxxxxx
--建表语法(使用Distributed引擎)：
create view if not exists 库名.视图名 on cluster 集群名 engine = Distributed(集群名, 库名, 本地表名, 分布式规则);

--例：
CREATE VIEW if not exists saas_aries.dim_ec_store_view on cluster default_cluster
(
    `oneid` String,
    `flag` Int32
) AS
SELECT
    oneid,
    argMax(flag, version) AS flag
FROM cdp_volvo.segment_18f24290fe0_distribute
GROUP BY oneid
HAVING flag = 0;

常用数据类型

DDL变更

ALTER语法

需要先在本地表操作，再在分布式表同样操作（所有的表结构变更语句都需要先在本地表执行，然后在分布式表执行，都执行完毕才可以）

xxxxxxxxxx
ALTER TABLE 库名.表名 ON CLUSTER 集群名 ADD|DROP|CLEAR|COMMENT|MODIFY COLUMN ...
新增字段

ALTER TABLE 库名.表名 ON CLUSTER 集群名 ADD COLUMN IF NOT EXISTS 字段名 类型 [默认值] AFTER 已存在的字段；

--先在本地表新增：
ALTER TABLE db_test.t_test_local ON CLUSTER default_cluster ADD COLUMN IF NOT EXISTS field5 Int32 default 0 AFTER field4；

--然后在分布式表新增：
ALTER TABLE db_test.t_test ON CLUSTER default_cluster ADD COLUMN IF NOT EXISTS field5 Int32 default 0 AFTER field4；
删除字段

ALTER TABLE 库名.表名 ON CLUSTER 集群名 DROP COLUMN IF EXISTS 字段名；

--先在本地表删除：
ALTER TABLE db_test.t_test_local ON CLUSTER default_cluster DROP COLUMN IF EXISTS field5；

--然后在分布式表删除：
ALTER TABLE db_test.t_test ON CLUSTER default_cluster DROP COLUMN IF EXISTS field5；
修改字段

ALTER TABLE 库名.表名 ON CLUSTER 集群名 MODIFY COLUMN IF EXISTS 字段名 类型 [默认值] [TTL]；
--//修改字段可以修改字段的类型、字段的默认值和字段的TTL，注意：不支持修改字段名称！

--先在本地表修改：
ALTER TABLE db_test.t_test_local ON CLUSTER default_cluster MODITY COLUMN IF EXISTS field5 Int64 default 1；
--//把field5的类型改为Int64，默认值改为1

--然后在分布式表修改：
ALTER TABLE db_test.t_test ON CLUSTER default_cluster MODITY COLUMN IF EXISTS field5 Int64 default 1；
--//把field5的类型改为Int64，默认值改为1
设置注释

ALTER TABLE 库名.表名 ON CLUSTER 集群名 COMMENT COLUMN IF EXISTS 字段名 注释；
--//设置字段的注释

--先在本地表设置：
ALTER TABLE db_test.t_test_local ON CLUSTER default_cluster COMMENT COLUMN IF EXISTS field5 'aaa'； //把field5的注释设置为aaa

--然后在分布式表设置：
ALTER TABLE db_test.t_test ON CLUSTER default_cluster COMMENT COLUMN IF EXISTS field5 'aaa'； //把field5的注释设置为aaa
清空列

这里例外，这个只在本地表执行，不需要在分布式表执行：因为这个实际是操作值，而分布式表是一个结构视图，本地表的值变化了自动会反映到分布式表

ALTER TABLE 库名.表名 ON CLUSTER 集群名 CLEAR COLUMN IF EXISTS 字段名 [IN PARTITION 分区名];
--//清空指定列的值，in partition 可选，如果设置，只清空在改分区下的指定列的值

--在本地表清空：
ALTER TABLE db_test.t_test_local ON CLUSTER default_cluster CLEAR COLUMN IF EXISTS field5；
--//把field5的值清空（会按照默认值填充，比如0或者null）

DML变更

使用Mutations更改数据

与某些数据库不同，默认情况下，ClickHouse的ALTER UPDATE语句是异步的，这意味着更新操作是发生在后台的，不会立即见效。这个更新表数据的过程被称为Mutations。检查Mutations的执行进度。可以操作MergerTree(及其家族)引擎的表，不能操作分布式表。

xxxxxxxxxx
-- UPDATE
ALTER TABLE table_local on cluster default_cluster UPDATE col1 = 'Hi' WHERE col2 = 2;

-- DELETE
ALTER TABLE table_local on cluster default_cluster DELETE WHERE col2 = 3;

其他

集群信息等

xxxxxxxxxx
-- 查看集群节点信息 clusters
SELECT * FROM system.clusters;

-- 表的分区信息 parts
select
  partition,
  database,
  table,
  sum(rows) as row,
  formatReadableSize(sum(bytes_on_disk)) as used_disk,
  formatReadableSize(sum(data_uncompressed_bytes)) as before_compress,
  formatReadableSize(sum(data_compressed_bytes)) as after_compress,
  round(sum(data_compressed_bytes) / sum(data_uncompressed_bytes) * 100, 0) compress_rate
from system.parts
where database='system'
and table='query_log'
group by table,database,partition
order by row desc limit 10;

-- 节点磁盘信息 disks
SELECT * FROM system.disks;

-- 同步表的相关信息 replicas
SELECT * FROM system.replicas WHERE is_readonly
    OR is_session_expired
    OR future_parts > 20
    OR parts_to_check > 10
    OR queue_size > 20
    OR inserts_in_queue > 10
    OR log_max_index - log_pointer > 10
    OR total_replicas < 2
    OR active_replicas < total_replicas;

-- 清空表，集群模式要加 ON CLUSTER cluster_name; 数据量大的话很慢。
truncate table event_flat_volvo_event_send_customer_profilebase ON CLUSTER default_cluster;
-- 查询记录
SELECT * FROM system.mutations WHERE table='event_flat_volvo_event_send_customer_profilebase_local';

-- 查看连接数
select * from system.metrics where metric like '%Connection%';

命令行参数

• --host, -h -– 服务端的host名称, 默认是localhost。您可以选择使用host名称或者IPv4或IPv6地址。
• --port – 连接的端口，默认值：9000。注意HTTP接口以及TCP原生接口使用的是不同端口。
• --user, -u – 用户名。 默认值：default。
• --password – 密码。 默认值：空字符串。
• --query, -q – 使用非交互模式查询。
• --database, -d – 默认当前操作的数据库. 默认值：服务端默认的配置（默认是default）。
• --multiline, -m – 如果指定，允许多行语句查询（Enter仅代表换行，不代表查询语句完结）。
• --multiquery, -n – 如果指定, 允许处理用;号分隔的多个查询，只在非交互模式下生效。
• --format, -f – 使用指定的默认格式输出结果。
• --vertical, -E – 如果指定，默认情况下使用垂直格式输出结果。这与–format=Vertical相同。在这种格式中，每个值都在单独的行上打印，这种方式对显示宽表很有帮助。
• --time, -t – 如果指定，非交互模式下会打印查询执行的时间到stderr中。
• --stacktrace – 如果指定，如果出现异常，会打印堆栈跟踪信息。
• --config-file – 配置文件的名称。
• --secure – 如果指定，将通过安全连接连接到服务器。
• --history_file — 存放命令历史的文件的路径。
• --param_<name> — 查询参数配置查询参数.

查看数据库/表

1、查看 Test 数据库的所有表

-- 方法一 select name from system.tables where database = 'Test';

-- 方法二 select distinct table from system.columns where database = 'Test';

2、查看 Test_table 表的所有字段

select name from system.columns where database = 'Test' and table='Test_table';

3、查看 Test_table 表的字段名、字段类型、字段注释

select database,table,name,type,comment from system.columns where database = 'Test' and table='Test_table';

4、若想就看某个表的某一两个字段的字段类型的话，可直接 调用 toTypeName() 函数来查看

select top 1 toTypeName(id),toTypeName(name) from Test_table;

输入多行insert命令

xxxxxxxxxx
clickhouse-client -m

命令行执行sql

xxxxxxxxxx
clickhouse-client --query="show databases;"

clickhouse-client -n < 1.sql

导出

注意：如果执行语句后面不加FORMAT CSV或FORMAT CSVWithNames，默认是\t作为分隔符。等同于--format CSV 或--format CSVWithNames

只有指定FORMAT CSV或FORMAT CSVWithNames后，指定--format_csv_delimiter才生效。

xxxxxxxxxx
clickhouse-client -n --query "select * from student FORMAT CSVWithNames" --format_csv_delimiter=',' > 'output.csv'

-- 控制输出行数
set output_format_pretty_max_rows = 3;
select * from student;

导入

xxxxxxxxxx
# 导入带表头的txt文件
clickhouse-client --query " insert into student FORMAT CSVWithNames" --format_csv_delimiter='|'  <  /data/student1.txt

# 导入不表头的txt文件
clickhouse-client --query " insert into student FORMAT CSV" --format_csv_delimiter='|'  <  /data/student1.txt

# 导入带表头的csv文件
clickhouse-client --query " insert into student FORMAT CSVWithNames" --format_csv_delimiter=','  <  /data/student1.csv

# 导入不表头的csv文件
clickhouse-client --query " insert into student FORMAT CSV" --format_csv_delimiter=','  <  /data/student1.csv

distributed_product_mode

xxxxxxxxxx
DB::Exception: Double-distributed IN/JOIN subqueries is denied (distributed_product_mode = 'deny'). You may rewrite query to use local tables in subqueries, or use GLOBAL keyword, or set distributed_product_mode to suitable value.

解决方法

xxxxxxxxxx
set distributed_product_mode = 'global';

执行计划

• PLAN：用于查看执行计划，默认值。
• AST：用于查看语法树;
• SYNTAX：用于优化语法，有时我们指定的查询语句未必是最优的，那么 ClickHouse 在底层会进行优化，EXPLAIN SYNTAX 可以返回对一条 SQL 语句进行优化后的结果。通过对比优化前和优化后的 SQL 语句，可以有助于我们理解 ClickHouse 的优化机制
• PIPELINE：用于查看 PIPELINE 计划。
• ESTIMATE：显示处理查询时要从表中读取的估计行数、标记数和部分数

explain

用于查看执行计划，默认值。

xxxxxxxxxx
-- 原sql
select name from system.tables where database = 'cdp_volvo' limit 1;

explain
select name from system.tables where database = 'cdp_volvo' limit 1;

explain syntax

在语法优化后返回查询。

可以使用此语法进行判断sql是否是当前查询方式的最佳性能，可以查看一些sql是会进行谓词下推。

xxxxxxxxxx
explain syntax
select name from system.tables where database = 'cdp_volvo' limit 1;

processes

查看当前正在执行的查询任务 SELECT 和 INSERT

当查系统表时，query只会在本地节点运行，如果需要获取cluster上所有的shards或者replicas，需要使用clusterAllReplicas表函数。

xxxxxxxxxx
SELECT * FROM clusterAllReplicas(default_cluster, system.processes) ORDER BY query_id;
SELECT query_id,user,FQDN(),elapsed,query FROM clusterAllReplicas(default_cluster, system.processes) WHERE elapsed > 0.0001 ORDER BY query_id;

通过 KILL QUERY 语句，可以终止正在执行的查询:
KILL QUERY WHERE query_id = 'query_id'

mutations

查看当前正在执行的mutation任务 ALTER DELETE 和 ALTER UPDATE

xxxxxxxxxx
SELECT * FROM system.mutations;
SELECT database, table, mutation_id, FQDN(), command, create_time, is_done FROM system.mutations;
SELECT database, table, mutation_id, FQDN(), command, create_time, is_done FROM system.mutations WHERE table = 'tablename';

同样的，可以使用 KILL MUTATION 终止正在执行的 Mutation 操作:
KILL MUTATION WHERE mutation_id = 'mutation_id';

is_done 用于记录该mutation是否执行 （0表示执行中，1表示已执行）
select * from system.mutations order by create_time desc limit 30;

query_log

查看配置：

xxxxxxxxxx
select * from system.settings where name='log_queries';

日志信息存储在表system.query_log中，包含两种查询：

1.客户端执行的查询

2.由其他查询发起的子查询（分布式查询执行）。对于这些类型的查询，有关父查询的信息显示在initial_*列中

一个查询会产生一条或两条记录，这个取决于状态(查看type列)：

1.执行成功的查询，会生成两条记录，type的值分别是QueryStart和QueryFinish，且query_id一致

event_date/event_time对应事件开始/完成的时间, sql启动时间为query_start_time. QueryFinish类型的event_time，也就是query_end_time.

2.查询执行过程中产生了错误，会生成两个事件，type的值分别是QueryStart和ExceptionWhileProcessing

3.在查询执行之前发生了错误，会生成一个事件，type的值是ExceptionBeforeStart

xxxxxxxxxx
SELECT type
    ,event_time
    ,query_start_time
    ,query_duration_ms
    ,read_rows
    ,read_bytes
    ,written_rows
    ,written_bytes
    ,result_rows
    ,result_bytes
    ,memory_usage
    ,query
    ,exception_code
    ,exception
    ,query_id
    ,FQDN()
FROM clusterAllReplicas(default_cluster, system.query_log)
WHERE query_start_time BETWEEN '2024-07-02 07:00:00' AND '2024-07-02 11:00:00'
 --   AND query_duration_ms > 900000
    AND query_id = '014ca520-2e60-444d-9ce5-22749bae26d6'
\G


Row 9:
───────
type:              QueryStart
event_time:        2024-03-07 14:44:10
query_start_time:  2024-03-07 14:44:10
query_duration_ms: 0
read_rows:         0
read_bytes:        0
written_rows:      0
written_bytes:     0
result_rows:       0
result_bytes:      0
query:             SELECT xxx FROM cdp_volvo.event_flat_volvo_event_send_customer_profilebase  WHERE 1=1 FORMAT TabSeparatedWithNamesAndTypes;
query_id:          907ea002-be5b-4692-89c2-ca1a72e0b09c

Row 12:
───────
type:              QueryFinish
event_time:        2024-03-07 15:00:09
query_start_time:  2024-03-07 14:44:10
query_duration_ms: 958799
read_rows:         57028593
read_bytes:        101278881259
written_rows:      0
written_bytes:     0
result_rows:       57028593
result_bytes:      172591161472
query:             SELECT xxx FROM cdp_volvo.event_flat_volvo_event_send_customer_profilebase  WHERE 1=1 FORMAT TabSeparatedWithNamesAndTypes;
query_id:          907ea002-be5b-4692-89c2-ca1a72e0b09c

query_log 解析

xxxxxxxxxx
type (Enum8)     ：执行查询时发生的事件的类型
    QueryStart’ = 1                        查询执行开始
    QueryFinish’ = 2                      查询执行成功
event_date (Date)                    查询开始日期
event_time (DateTime)            查询开始日期
query_start_time (DateTime)   查询执行的开始时间。
query_duration_ms (UInt64)    查询执行的持续时间（毫秒）
read_rows (UInt64)                 参与查询的所有表和表函数中读取的总数或行数。包括常用的子查，IN和JOIN的子查询。对于分布式查询，read_rows包括在所有副本上读取的行总数。每个副本发送它的read_rows值，查询的服务器启动器汇总所有接收到的值和本地值。缓存卷不会影响这个值。
read_bytes (UInt64)                 参与查询的所有表和表函数中读取的总数或行数。包括常用的子查，IN和JOIN的子查询。对于分布式查询，read_rows包括在所有副本上读取的行总数。每个副本发送它的read_rows值，查询的服务器启动器汇总所有接收到的值和本地值。缓存卷不会影响这个值。
written_rows (UInt64)             对于插入查询，表示写入的行数。对于其他查询，列值为0
written_bytes (UInt64)             对于插入查询，表示写入的字节数。对于其他查询，列值为0。
result_rows (UInt64)                选择查询结果中的行数，或插入查询中的行数
result_bytes (UInt64)                用于存储查询结果的RAM(字节)容量。
memory_usage (UInt64)           查询所消耗的内存。
query (String)                            查询语句
exception (String)                     异常输出
stack_trace (String)                   堆栈跟踪。如果查询成功完成，则为空字符串
is_initial_query (UInt8)              查询类型
1         客户端发起的查询
0         查询是作为分布式查询执行的一部分由另一个查询发起的
user (String)                             当前查询的用户名
query_id (String)                       查询编号
address (IPv6)                          进行查询的IP地址
port (UInt16)                            查询的客户端端口
initial_user (String)                  运行初始查询的用户名(用于分布式查询执行
initial_query_id (String)            初始查询的ID(用于分布式查询执行）
initial_address (IPv6)                父查询的IP地址
initial_port (UInt16)                  父查询的客户端端口
interface (UInt8)                       发起查询的接口
1 — TCP.
2 — HTTP.
os_user (String)                     运行clickhouse-client的操作系统用户名
client_hostname (String)      运行clickhouse-client或另一个TCP客户机的客户机的主机名
client_name (String)              Clickhouse-client或其他TCP客户端名称
client_revision (UInt32)         修改clickhouse-client或其他TCP客户端
client_version_major (UInt32)         Clickhouse-client或其他TCP客户端的主要版本。
client_version_minor (UInt32)        Clickhouse-client或其他TCP客户端的小版本。
client_version_patch (UInt32)        Clickhouse-client或其他TCP客户端版本的补丁组件.
http_method (UInt8)                       发起查询的HTTP方法
0 — 从TCP接口启动查询
1 — GET 方法
2 — POST 方法
http_user_agent (String)            在HTTP请求中传递的UserAgent头。
quota_key (String)                                 配额设置中指定的“配额键
revision (UInt32)                                    ClickHouse revision
thread_numbers (Array(UInt32))           参与查询执行的线程数
ProfileEvents.Names (Array(String))      衡量不同指标的计数器
ProfileEvents.Values (Array(UInt64))     ProfileEvents中列出的度量值
Settings.Names (Array(String))             客户端运行查询时更改的设置名称。要启用对设置的日志更改，将log_query_settings参数设置为1
Settings.Values (Array(String))              设置中列出的设置值

慢sql阈值

刚确认过了，这个告警的阈值配置需要在服务参数中设置才能生效，在集群—clickhouse服务—配置—全部配置中，搜索slow关键字，就可以看到一个参数slow_sql_cost_time，默认值180，单位是秒