ClickHouse操作手册

⭐ClickHouse笔记

结合自己的实际使用及网上的使用教程对clickhouse数据库实际操作进行整理说明

🐶安装说明：

$clickhouse-\mathrm{20.2.1.2183}$

安装包地址：链接
提取码：4laj

安装方法：链接

$dbeaver-\mathrm{6.2.4}（clickhouse客户端）$

安装包地址：链接
提取码：7syb

安装方法：下载解压，打开安装dbeaver插件即可（会提示安装）

🐶配置clickhouse集群：

🐶常用命令帮助（单机）：

sql语句关键词为什么要大写

🐱查看clickhouse版本

select version();

🐱查看所有数据库

SHOW DATABASES;

🐱创建数据库

-- CREATE DATABASE IF NOT EXISTS `数据库名`;
-- 例：如果不存在test库，那么创建test库
CREATE DATABASE IF NOT EXISTS test;

-- 命名规范
库名、表名以及字段名小写以下划线连接
例：app_link

• 数据库命名规范

🐱删除数据库

-- DROP DATABASE IF EXISTS 数据库名;
-- 例：如果存在test库，那么删除test库
DROP DATABASE IF EXISTS test;

🐱使用数据库

-- USE 数据库名;
-- 例：使用test库
USE test;

-- 客户端DBeaver使用数据库会报错，需要手动在客户端指定数据库，或者操作表时以库名.表名操作数据库

-- ######以下示例对表操作都是以 库名.表名 对表进行操作#####

🐱创建表

-- 例：在test库下创建user表，引擎为Mergetree
-- test.user definition
CREATE TABLE IF NOT EXISTS test.user(
    `id` Int64 COMMENT 'ID',
    `name` Nullable(String) COMMENT '姓名',
    `age` UInt8 DEFAULT '18' COMMENT '年龄',
    `friends` Array(String) COMMENT '朋友',
    `create_time` DateTime DEFAULT now() COMMENT '创建时间',
    `update_time` DateTime DEFAULT now() COMMENT '修改时间'
) ENGINE = MergeTree PRIMARY KEY (id)
ORDER BY
    (id);

• clickhouse数据类型

-- 创建表时生成UUID类型字段
CREATE TABLE IF NOT EXISTS test.test(
`id` UUID
)ENGINE = MergerTree
ORDER BY (id)

-- 查看随机的UUID值
SELECT generateUUIDv4();

-- 往表中插入UUID，以上面创建的test表为例
INSERT INTO test.test VALUES(generateUUIDv4());

• clickhouse引擎

• clickhouse主键（PRIMARY KEY）

• clickhouse排序键（ORDER BY）

-- 排序键
说明：
  可以是一组列的元组或任意的表达式。 例如: ORDER BY (CounterID, EventDate) 。

作用：
1、设定了分区内的数据按照哪些字段顺序进行有序保存。
2、当表引擎为ReplacingMergeTree时，合并方式会根据排序键来合并。
3、根据排序键生成一级索引（稀疏索引），优化WHERE条件时的查询。

注意：
1、设置排序键不易过长，过长会影响插入效率，排序键数量保持在1-5个，不设置和超出数量都不好。
2、如果没有使用 PRIMARY KEY 显式指定的主键，ClickHouse会使用排序键作为主键。
3、如果不需要排序，可以使用 ORDER BY tuple().
4、主键必须是 order by 字段的前缀字段。
例：排序键(A,B,C) 
主键(A),(A,B),(A,B,C):可行
主键(B),(C),(B,C),(A,C):不可行

• clickhouse分区

🐱删除表

DROP TABLE IF EXISTS 库名.表名;
-- 例：如果test库下存在user表，那么删除test库下的user表
DROP TABLE IF EXISTS test.test;

🐱修改表

🦴增：

添加字段

-- ALTER TABLE 库名.表名 ADD COLUMN `字段名` 字段类型 DEFAULT '';
-- 例：给test库的user表添加类型为String的hobby字段，默认值为空字符串''，默认生成位置为表最后一个字段
-- 其中默认可以不加，表示默认为空字符串，当字段类型设置为Nullable时，若未给字段赋值，字段默认值为null
ALTER TABLE test.user ADD COLUMN hobby String DEFAULT '';
-- 设置字段类型为可null字符串
ALTER TABLE test.user ADD COLUMN hobby Nullable(String) DEFAULT '';

-- 指定插入字段的位置
-- 1、某字段之前插入（不可行）
-- ALTER TABLE 库名.表名 ADD COLUMN `字段名` 字段类型 BEFORE `字段名`;
ALTER TABLE test.user ADD COLUMN `before_hobby` String BEFORE `hobby`;
-- 2、某字段之后插入（可行）
-- ALTER TABLE 库名.表名 ADD COLUMN `字段名` 字段类型 AFTER `字段名`;
ALTER TABLE test.user ADD COLUMN `after_hobby` String AFTER `hobby`;

🦴删：

1、删除字段

-- ALTER TABLE 库名.表名 DROP COLUMN `字段名`;
-- 例：删除test.user表的after_hobby字段
ALTER TABLE test.user DROP COLUMN `after_hobby`;

2、清空表

TRUNCATE TABLE 库名.表名;

3、按条件删除表数据

ALTER TABLE 库名.表名 DELETE where 条件;
-- 例：删除test库下test表的所有数据
ALTER TABLE test.test DELETE where 1=1;

表数据大于50G无法执行删除操作：

• 解决方法一：在 /etc/clickhouse-server/config.xml 中，修改max_table_size_to_drop参数，默认 50GB，如果你要删除的分区或表, 数据量达到了此参数值大小, 会删除失败，修改为0, 代表无论数据多大, 都可以删除。

• 解决方法二：实例节点机器下执行以下命令

• sudo touch /data/clickhouse/flags/force_drop_table && sudo chmod 666 /data/clickhouse/flags/force_drop_table
  

🦴改：

1、修改表名

-- rename TABLE 库名.表名 to 库名.表名;
-- 例：修改test库中user表的名字为test_user
rename table test.user to test.test_user;

2、修改表字段名

-- ALTER TABLE 库名.表名 RENAME COLUMN `字段名` to `字段名`;
-- 例：
ALTER TABLE test.user RENAME COLUMN `hobby` to `hobit`;

3、修改表字段类型 & 添加或修改表字段默认值 & 添加或修改表注释

-- ALTER TABLE 库名.表名 MODIFY COLUMN `hobit` Nullable(String) DEFAULT NULL COMMENT '爱好';
-- 例：修改test库user表hobit字段类型由String变为Nullable(String)，修改默认值由''变为NULL，添加注释为：爱好
ALTER TABLE test.user MODIFY COLUMN `hobit` Nullable(String) DEFAULT NULL COMMENT '爱好';

🐱查询表

1、查询表的建表语句

SHOW CREATE TABLE 库名.表名;
-- 例：查询test库下test表的建表语句
SHOW CREATE TABLE test.test;

2、查询表的所有字段及字段信息

DESC 库名.表名;
-- 例：查询test库下test表的字段及字段信息
DESC test.test;

3、按条件查询表数据

SELECT 范围 FROM 库名.表名 WHERE 条件;
-- 例：查询test库下test表所有id大于1的数据
SELECT * FROM test.test WHERE id > 1';

🐱索引相关

clickhouse索引

一级索引（稀疏索引）：

创建表时生成，根据排序键(ORDER BY)生成一级索引

二级索引（跳数索引）：

可以在创建表时生成，也可以在创建表后添加二级索引

-- 创建二级索引
ALTER TABLE 库名.表名 ADD INDEX 二级索引名 二级索引字段 TYPE 二级索引类型 GRANULARITY 2;

-- 使索引对所有数据生效
ALTER TABLE 库名.表名 MATERIALIZE INDEX 二级索引名;

-- 删除二级索引
ALTER TABLE 库名.表名 DROP INDEX 二级索引名;

🐱数据存活时间TTL（Time To Live）

数据存活时间

🐶常用命令帮助（集群）：

🐶注意事项：

🐱linux服务器登录clickhouse客户端

1、首次登陆客户端（第一次安装后未配置密码）

clickhouse-client

2、登录客户端（配置好密码后）

配置ClickHouse密码

首先找到clickhosue-server下的user.xml配置文件

在标签里填入要更改的密码，保存退出

clickhouse-client --password `密码`

🐱外部访问服务器clickhouse

1、修改配置文件–config.xml

2、关闭防火墙–使用firewall-cmd配置端口

3、重启clickhouse

sudo service clickhouse-server restart

4、验证：外部浏览器输入服务器ip+端口（默认8123），出现ok即本地可以连接

ip:8123

🐱修改clickhouse端口