1. 数据库
- 数据库起到了命名空间的作用，可以有效规避命名冲突的问题，也为后续的数据隔离提供了支撑。
- 任何一张数据表，都必须归属在某个数据库之下
1.1 创建数据库
- 语法
CREATE DATABASE -- 创建数据库
IF NOT EXISTS -- 如果存在同名数据库，则不进行创建
db_name -- 要创建的数据库名称
[ENGINE = engine] -- 使用的数据库引擎，如未指定默认使用：Atomic ，关于数据库引擎会在后面介绍
- 使用示例
-- 创建数据库
CREATE DATABASE db_test1 ENGINE = Atomic;
-- 查看数据库列表
show databases;
┌─name─────┐
│ db1 │
│ db_test │
│ default │
│ system │
└──────────┘
-- 切换数据库
use db_test;
USE db_test
Query id: e7163622-fbda-4ff0-ba0b-6fb72e702e35
Ok.
0 rows in set. Elapsed: 0.001 sec.
- 存储目录
创建数据库的实质是物理磁盘上的一个文件目录，所以在语句执行之后，ClickHouse便会在数据存储目录下 data 下创建 db_test1 数据库的文件目录：
[root@node3 ~]# cd /var/lib/clickhouse/data
[root@node3 data]# ll
总用量 0
drwxr-x--- 2 clickhouse clickhouse 20 4月 15 11:49 db1
drwxr-x--- 2 clickhouse clickhouse 6 4月 27 11:56 db_test1 # 刚创建的数据库
drwxr-x--- 2 clickhouse clickhouse 118 4月 19 17:16 default
drwxr-x--- 2 clickhouse clickhouse 113 4月 15 11:36 system- 元数据
创建好数据库文件夹之后，会在metadata路径下也会一同创建用于恢复数据库的db_test1.sql文件：
[root@node3 data]# cd /var/lib/clickhouse/metadata
[root@node3 metadata]# ll
总用量 32
lrwxrwxrwx 1 clickhouse clickhouse 66 4月 15 11:48 db1 -> /var/lib/clickhouse/store/1b6/1b63d246-d32d-45f1-a6eb-553dde05598b
-rw-r----- 1 clickhouse clickhouse 78 4月 15 11:48 db1.sql
lrwxrwxrwx 1 clickhouse clickhouse 66 4月 27 11:56 db_test1 -> /var/lib/clickhouse/store/9fe/9feb6454-42b9-44e6-beb3-564e62e4456e
-rw-r----- 1 clickhouse clickhouse 78 4月 27 11:56 db_test1.sql
lrwxrwxrwx 1 clickhouse clickhouse 66 4月 15 11:30 default -> /var/lib/clickhouse/store/9ac/9ac12c64-4c51-4ea8-8bf1-46f5006e9baa
-rw-r----- 1 clickhouse clickhouse 78 4月 15 11:30 default.sql
lrwxrwxrwx 1 clickhouse clickhouse 66 4月 15 11:30 system -> /var/lib/clickhouse/store/c24/c2458088-31e5-4424-a78b-1629304d14b7
-rw-r----- 1 clickhouse clickhouse 78 4月 15 11:30 system.sql
[root@node3 metadata]# cat db_test1.sql
ATTACH DATABASE _ UUID '9feb6454-42b9-44e6-beb3-564e62e4456e' # UUID
ENGINE = Atomic # 默认引擎
1.2 删除数据库
-- 删除数据库
drop database db_test;
-- 查看数据库列表
show databases;
┌─name─────┐
│ db1 │
│ default │
│ system │
└──────────┘
- 查看数据库对应路径与文件是否删除
# 查看数据库路径
[root@node3 metadata]# cd /var/lib/clickhouse/data/
[root@node3 data]# ls
db1 default system
# 查看数据库元数据文件
[root@node3 data]# cd /var/lib/clickhouse/metadata
[root@node3 metadata]# ls
db1 db1.sql default default.sql system system.sql
# 在上面两个路径下 db_test 相关文件均被删除2. 数据表
2.1 创建数据表
2.1.1 常规方法创建表
CREATE TABLE -- 创建表
[IF NOT EXISTS] -- 如果该表名已经存在则不进行创建
[db_name.] -- 指定数据库名，未指定则使用默认数据库：default
table_name ( -- 表名
name1 -- 列名
[type] -- 列数据类型
[DEFAULT|MATERIALIZED|ALIASexpr], -- 默认数据表达式
[COMMENT] -- 字段备注
name2[type][DEFAULT|MATERIALIZED|ALIASexpr],
省略…
) ENGINE = engine -- 指定表引擎，表引擎决定了数据表的特性，也决定了数据将会被如何存储及加载
------------------------------------------------
-- 使用常规方式创建表
CREATE TABLE IF NOT EXISTS t1(
`id` Int8 DEFAULT 1 COMMENT 'id',
`name2` String DEFAULT 'xiaoming' COMMENT '姓名'
)ENGINE = Memory;
-- 查看表结构
describe t1;
┌─name──┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ Int8 │ DEFAULT │ 1 │ id │ │ │
│ name2 │ String │ DEFAULT │ 'xiaoming' │ 姓名 │ │ │
└───────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
-- 插入数据并测试默认值
insert into table default.t1 values (2,'xiaohong');
insert into table default.t1 (id)values (3);
insert into table default.t1 (name2)values ('xiaoli');
-- 查询数据
select * from default.t1;
┌─id─┬─name2────┐
│ 2 │ xiaohong │
└────┴──────────┘
┌─id─┬─name2──┐
│ 1 │ xiaoli │ -- 使用id默认值
└────┴────────┘
┌─id─┬─name2────┐
│ 3 │ xiaoming │ -- 使用name2默认值
└────┴──────────┘
2.1.2 复制其他表的结构
- 仅复制建表时指定表的结构，建表完成后，原表结构变化或者被删除不会影响新表
- 仅复制结构，不复制数据
CREATE TABLE -- 创建表
[IF NOT EXISTS] -- 如果该表名已经存在则不进行创建
[db_name1.] table_name -- 指定数据库与要创建的表名
AS [db_name2.] table_name2 -- 要复制哪个库里面的哪张表 ，可以将表复制到不同的数据库
[ ENGINE = engine ] -- 指定表引擎，原表与新表的表引擎可以不同
------------------------------------------------
-- 以复制其他表结构的方式创建表
CREATE TABLE IF NOT EXISTS db1.default_t1 AS default.t1 ENGINE = TinyLog;
CREATE TABLE IF NOT EXISTS db1.default_t1 AS default.t1
ENGINE = TinyLog
Query id: 34bbc217-ddb3-4758-ba0d-4c2cd5115289
Ok.
0 rows in set. Elapsed: 0.045 sec.
-- 查看表在 db1 数据库中是否创建成功
use db1;
-- 查看都有哪些表
show tables;
┌─name───────┐
│ default_t1 │
│ test01 │
└────────────┘
-- 查看 default_t1 表结构
describe default_t1;
┌─name──┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ Int8 │ DEFAULT │ 1001 │ │ │ │
│ name2 │ String │ DEFAULT │ 'xiaoming' │ │ │ │
└───────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
-- 表结构与前面创建的一致
2.1.3 通过SELECT形式创建表
- 不仅创建新的表，也会把查询结果插入到该表中
CREATE TABLE -- 创建表
[IF NOT EXISTS] -- 如果该表名已经存在则不进行创建
[db_name.]table_name -- 指定数据库与要创建的表名
ENGINE = engine -- 指定表引擎
AS SELECT … -- 指定查询数据的逻辑
------------------------------------------------
CREATE TABLE IF NOT EXISTS default.t2 ENGINE = TinyLog AS SELECT id from default.t1;
-- 查看表数据
select * from default.t2;
SELECT *
FROM t2
Query id: 9ed4c251-bd6f-473b-a163-3d714beae36d
┌─id─┐
│ 2 │
│ 3 │
│ 1 │
└────┘
-- 查看表结构
describe t2;
┌─name─┬─type─┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ Int8 │ │ │ │ │ │
└──────┴──────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
2.2 删除表
DROP TABLE -- 删除表
[IF EXISTS] -- 如果表存在则删除下面的表
[db_name.]table_name -- 指定数据库下面的表
------------------------------------------------
-- 切换库
use db1;
-- 查看表
show tables;
┌─name───────┐
│ default_t1 │
│ test01 │
└────────────┘
-- 删除表
drop table if exists default_t1;
-- 查看表
show tables;
┌─name───┐
│ test01 │
└────────┘
2.3 默认值表达式
ClickHouse 表字段支持三种默认值表达式的定义方法，分别是DEFAULT、MATERIALIZED和ALIAS。不管使用哪种方式，当定义了默认值之后，就可以从默认值中推断出字段类型，便不会强制要求描述字段的类型了。
| 默认方式 | 写入数据 | 查询数据 | 数据存储 |
|---|---|---|---|
| DEFAULT | 可以出现在INSERT语句中 | 可以通过 SELECT * 返回 | 支持持久化 |
| MATERIALIZED | 不能被显示的赋值 | 不会出现在SELECT * 查询的返回结果集中 | 支持持久化 |
| ALIAS | 不能被显示的赋值 | 不会出现在SELECT * 查询的返回结果集中 | 不支持持久化，取值总依靠计算产生 |
- DEFAULT
-- 创建表
CREATE TABLE IF NOT EXISTS t1_default
(
`id` Int16,
`age` Int8 DEFAULT 18,
`name` String MATERIALIZED concat('employee', CAST(age, 'String')),
`salary` ALIAS age * 100
)
ENGINE = Memory
-- 查看表结构
describe t1_default;
DESCRIBE TABLE t1_default
Query id: 64ae7af6-153c-4131-abbf-afc87b32d96e
┌─name───┬─type───┬─default_type─┬─default_expression──────────────────────┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ Int16 │ │ │ │ │ │
│ age │ Int8 │ DEFAULT │ 18 │ │ │ │
│ name │ String │ MATERIALIZED │ concat('employee', CAST(age, 'String')) │ │ │ │
│ salary │ Int16 │ ALIAS │ age * 100 │ │ │ │
└────────┴────────┴──────────────┴─────────────────────────────────────────┴─────────┴──────────────────┴────────────────┘
-- 插入数据
insert into table t1_default (id) values (1);
-- 使用 select * 方式查询数据
select * from t1_default;
┌─id─┬─age─┐ -- 并没有查询出 name,salary 两个字段
│ 1 │ 18 │
└────┴─────┘
-- 指定列名查询字段
select id,age,name,salary from t1_default;
┌─id─┬─age─┬─name───────┬─salary─┐
│ 1 │ 18 │ employee18 │ 1800 │
└────┴─────┴────────────┴────────┘
-- name 与 salary 根据表达式各自算出自己的数值
-- 更新 age 值为 20 查看数据变化
ALTER TABLE t1_default UPDATE age = 20 WHERE id = 1;
-- 查询数据
select id,age,name,salary from t1_default;
┌─id─┬─age─┬─name───────┬─salary─┐
│ 1 │ 20 │ employee20 │ 2000 │
└────┴─────┴────────────┴────────┘
-- 在 Memory 引擎中 name 与 salary 两个字段都进行了更新2.4 临时表
使用
TEMPORARY关键字，描述临时表临时表的生命周期是会话绑定的，所以它只支持Memory表引擎，如果会话结束，数据表就会被销毁
临时表不属于任何数据库，所以在它的建表语句中，既没有数据库参数也没有表引擎参数
临时表名称可以与普通表名称相同，查询时临时表的优先级大于普通表
定义
CREATE TEMPORARY TABLE
[IF NOT EXISTS ]
table_name(
name1[type][DEFAULT|MATERIALIZED|ALIASexpr],
name2[type][DEFAULT|MATERIALIZED|ALIASexpr],
...
)
- 临时表使用示例
-- 创建临时表，与前面测试 默认值的数据表名称与结构相同
CREATE TEMPORARY TABLE IF NOT EXISTS t1_default
(
`id` Int16,
`age` Int8 DEFAULT 18,
`name` String MATERIALIZED concat('employee', CAST(age, 'String')),
`salary` ALIAS age * 100
)
-- 查询临时数据
select * from t1_default;
SELECT *
FROM t1_default
Query id: ae1016a5-43c2-405e-9d63-b79a64b22047
Ok.
0 rows in set. Elapsed: 0.003 sec.
-- 查询普通表数据 （需要加上数据库名称）
select * from db1.t1_default;
┌─id─┬─age─┐
│ 1 │ 20 │
└────┴─────┘
-- 插入数据 优先向临时表插入数据
insert into table t1_default (id) values (2);
-- 查询数据 临时查询临时表的数据
select * from t1_default;
┌─id─┬─age─┐
│ 2 │ 18 │
└────┴─────┘
2.5 分区表
2.5.1 分区表介绍
- 数据分区是针对本地数据而言的，是数据的一种纵向切分
- 借助数据分区，在后续的查询过程中能够跳过不必要的数据目录，从而提升查询的性能。
- 合理地利用分区特性，还可以变相实现数据的更新操作，因为数据分区支持删除、替换和重置操作。
- MergeTree 家族系列的表引擎才支持数据分区。
- 合理使用分区可以提高查询效率，但使用分区粒度不应过细，否则会致分区过多反而影响性能。
- 语法
CREATE TABLE -- 创建表
[IF NOT EXISTS] -- 如果该表名已经存在则不进行创建
[db_name.] -- 指定数据库名，未指定则使用默认数据库：default
table_name ( -- 表名
name1 -- 列名
[type] -- 列数据类型
[DEFAULT|MATERIALIZED|ALIASexpr], -- 默认数据表达式
name2[type][DEFAULT|MATERIALIZED|ALIASexpr],
省略…
) ENGINE = engine -- 指定表引擎，表引擎决定了数据表的特性，也决定了数据将会被如何存储及加载
PARTITION BY <PARTITION expr> -- 指定分区规则
2.5.2 创建分区表
-- 创建分区表
CREATE TABLE IF NOT EXISTS t_partition
(
`ID` String,
`URL` String,
`EventTime` Date
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(EventTime)
ORDER BY ID
-- 插入数据
INSERT INTO t_partition VALUES('A000','www.baidu.com','2019-05-01'),('A002','www.google.com','2019-06-01');
-- 查询数据
select * from t_partition;
┌─ID───┬─URL────────────┬──EventTime─┐
│ A002 │ www.google.com │ 2019-06-01 │
└──────┴────────────────┴────────────┘
┌─ID───┬─URL───────────┬──EventTime─┐
│ A000 │ www.baidu.com │ 2019-05-01 │
└──────┴───────────────┴────────────┘
-- 根据分区查询数据
SELECT * FROM t_partition WHERE EventTime ='2019-05-01';
┌─ID───┬─URL───────────┬──EventTime─┐
│ A000 │ www.baidu.com │ 2019-05-01 │
└──────┴───────────────┴────────────┘
-- 可以利用分区索引跳过6月份的分区目录，只加载5月份的数据，从而带来查询的性能提升
2.5.3 查询分区信息
- 可以通过system库中的
parts系统表查看 分区表信息。
-- 查询表的分区存储信息
SELECT partition_id,name,table,database,partition,path from system.parts WHERE table = 't_partition';
┌─partition_id─┬─name─────────┬─table───────┬─database─┬─partition─┬─path─────────────────────────────────────────────────────────────────────────────┐
│ 201905 │ 201905_1_1_0 │ t_partition │ db1 │ 201905 │ /var/lib/clickhouse/store/5f2/5f27038d-14b7-4b81-8a8d-8c40d2afe144/201905_1_1_0/ │
│ 201906 │ 201906_2_2_0 │ t_partition │ db1 │ 201906 │ /var/lib/clickhouse/store/5f2/5f27038d-14b7-4b81-8a8d-8c40d2afe144/201906_2_2_0/ │
└──────────────┴──────────────┴─────────────┴──────────┴───────────┴──────────────────────────────────────────────────────────────────────────────────┘
2 rows in set. Elapsed: 0.053 sec.
-- 按年月划分后，目前拥有两个数据分区，且每个分区都对应一个独立的文件目录，用于保存各自部分的数据
-- partitin_id 相当于分区主键，相同的partition_id 的数据被分配到同一分区
2.5.4 删除指定分区
- 合理地设计分区键并利用分区的删除功能，就能够达到数据更新的目的。
- 语法：
ALTER TABLE tb_name DROP PARTITION partition_expr
- 删除分区示例 ， 删除 201905 分区，并重新写入数据
-- 查询数据
select * from db1.t_partition;
┌─ID───┬─URL───────────┬──EventTime─┐
│ A000 │ www.baidu.com │ 2019-05-01 │
└──────┴───────────────┴────────────┘
┌─ID───┬─URL────────────┬──EventTime─┐
│ A002 │ www.google.com │ 2019-06-01 │
└──────┴────────────────┴────────────┘
-- 删除 201905 分区
ALTER TABLE db1.t_partition DROP PARTITION 201905;
-- 查询分区信息
SELECT partition_id,name,table,database from system.parts WHERE table = 't_partition';
┌─partition_id─┬─name─────────┬─table───────┬─database─┐
│ 201906 │ 201906_2_2_0 │ t_partition │ db1 │
└──────────────┴──────────────┴─────────────┴──────────┘
-- 查询数据
select * from db1.t_partition;
┌─ID───┬─URL────────────┬──EventTime─┐
│ A002 │ www.google.com │ 2019-06-01 │
└──────┴────────────────┴────────────┘
-- 从上面结果可以看出 201905 分区被删除
-- 重新插入 201905 分区数据
INSERT INTO db1.t_partition VALUES('A003','www.sina.com','2019-05-02');
-- 查询分区信息
SELECT partition_id,name,table,database from system.parts WHERE table = 't_partition';
┌─partition_id─┬─name─────────┬─table───────┬─database─┐
│ 201905 │ 201905_4_4_0 │ t_partition │ db1 │
│ 201906 │ 201906_2_2_0 │ t_partition │ db1 │
└──────────────┴──────────────┴─────────────┴──────────┘2.5.5 复制分区数据
- 当两张表的结构相同，并且有相同的分区键时，可以将将一张表的分区复制到另一张表中
- 当重复向目标表复制分区时，源表分区的数据会覆盖目标表分区的数据
- 语法：
-- 将 A 表的某个分区复制到 B 表中
ALTER TABLE B REPLACE PARTITION partition_expr FROM A
- 复制分区示例，创建一个空的分区表，将db1.t_partition 的 201905分区复制到新分区表中
-- 创建分区表
CREATE TABLE IF NOT EXISTS t_partition_b
(
`ID` String,
`URL` String,
`EventTime` Date
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(EventTime)
ORDER BY ID
-- 查看 t_partition_b 分区信息
SELECT partition_id,name,table,database from system.parts WHERE table = 't_partition_b';
0 rows in set. Elapsed: 0.004 sec.
-- t_partition_b 表目前没有分区
-- 复制分区
ALTER TABLE t_partition_b REPLACE PARTITION 201905 FROM t_partition;
-- 查看 t_partition_b 分区信息
SELECT partition_id,name,table,database from system.parts WHERE table = 't_partition_b';
┌─partition_id─┬─name─────────┬─table─────────┬─database─┐
│ 201905 │ 201905_2_2_0 │ t_partition_b │ db1 │
└──────────────┴──────────────┴───────────────┴──────────┘
-- 分区复制成功
2.5.6 重置分区数据
- 如果数据表某一列的数据有误，可以将其重置为初始值
- 如果声明了默认值表达式，则以表达式为准
- 如果没有声明默认值，以相应数据类型的默认值为准
- 不能将分区键重置
- 不能将 order by 描述的列重置
- 语法：
ALTER TABLE tb_name CLEAR COLUMN column_name IN PARTITION partition_expr
- 重置分区数据示例
-- 创建分区表
CREATE TABLE IF NOT EXISTS t_partition_default
(
`ID` String,
`URL` String DEFAULT 'www.baidu.com',
`EventTime` Date
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(EventTime)
ORDER BY ID
-- 查看表结构
desc t_partition_default;
┌─name──────┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ ID │ String │ │ │ │ │ │
│ URL │ String │ DEFAULT │ 'www.baidu.com' │ │ │ │
│ EventTime │ Date │ │ │ │ │ │
└───────────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
-- 插入数据
INSERT INTO t_partition_default VALUES('A000','www.bing.com','2019-05-01'),('A002','www.google.com','2019-06-01');
-- 查询表数据
select * from t_partition_default;
┌─ID───┬─URL────────────┬──EventTime─┐
│ A002 │ www.google.com │ 2019-06-01 │
└──────┴────────────────┴────────────┘
┌─ID───┬─URL──────────┬──EventTime─┐
│ A000 │ www.bing.com │ 2019-05-01 │
└──────┴──────────────┴────────────┘
-- 查看 t_partition_default 分区信息
SELECT partition_id,name,table,database from system.parts WHERE table = 't_partition_default';
┌─partition_id─┬─name─────────┬─table───────────────┬─database─┐
│ 201905 │ 201905_1_1_0 │ t_partition_default │ db1 │
│ 201906 │ 201906_2_2_0 │ t_partition_default │ db1 │
└──────────────┴──────────────┴─────────────────────┴──────────┘
-- 将 t_partition_default 表 的 201905 分区中的 URL 字段 重置
ALTER TABLE t_partition_default CLEAR COLUMN URL IN PARTITION 201905;
-- 查询数据
select * from t_partition_default;
┌─ID───┬─URL────────────┬──EventTime─┐
│ A002 │ www.google.com │ 2019-06-01 │
└──────┴────────────────┴────────────┘
┌─ID───┬─URL───────────┬──EventTime─┐
│ A000 │ www.baidu.com │ 2019-05-01 │ -- 改为默认值
└──────┴───────────────┴────────────┘
2.5.7 卸载与装载分区
卸载与装载这一对伴生的操作，常用于分区数据的迁移和备份场景。
通过DETACH语句卸载表分区。
通过ATTACH语句装载表分区。
分区被卸载后，分区不可用，但物理数据并没有删除，而是被转移到了当前数据表目录的detached子目录下，一旦分区被移动到了detached子目录，就代表它已经脱离了ClickHouse的管理，ClickHouse并不会主动清理这些文件。这些分区文件会一直存在，除非主动删除或者重新装载它们。
装载分区则是反向操作，它能够将detached子目录下的某个分区重新装载回去。
语法：
-- 卸载分区
ALTER TABLE tb_name DETACH PARTITION partition_expr
-- 装载分区
ALTER TABLE tb_name ATTACH PARTITION partition_expr
- 使用示例，装载与卸载 t_partition 表中的 201905 分区
ALTER TABLE tb_name DETACH PARTITION partition_expr
-- 查看 t_partition 分区信息
SELECT partition_id,name,table,database from system.parts WHERE table = 't_partition';
┌─partition_id─┬─name─────────┬─table───────┬─database─┐
│ 201905 │ 201905_5_5_0 │ t_partition │ db1 │
│ 201906 │ 201906_2_2_0 │ t_partition │ db1 │
└──────────────┴──────────────┴─────────────┴──────────┘
-- 查询数据
select * from t_partition;
┌─ID───┬─URL────────────┬──EventTime─┐
│ A002 │ www.google.com │ 2019-06-01 │
└──────┴────────────────┴────────────┘
┌─ID───┬─URL─────────────┬──EventTime─┐
│ A004 │ www.sina.com.cn │ 2019-05-02 │
└──────┴─────────────────┴────────────┘
-- 卸载 t_partition 表的 201905 分区
ALTER TABLE t_partition DETACH PARTITION 201905;
-- 查看 t_partition 分区信息
SELECT partition_id,name,table,database from system.parts WHERE table = 't_partition';
┌─partition_id─┬─name─────────┬─table───────┬─database─┐
│ 201906 │ 201906_2_2_0 │ t_partition │ db1 │
└──────────────┴──────────────┴─────────────┴──────────┘
-- 查询数据
select * from t_partition;
┌─ID───┬─URL────────────┬──EventTime─┐
│ A002 │ www.google.com │ 2019-06-01 │
└──────┴────────────────┴────────────┘
- 查看 detached 文件夹内容
[root@node3 db1]# cd t_partition/detached/
[root@node3 detached]# pwd
/var/lib/clickhouse/data/db1/t_partition/detached
[root@node3 detached]# ll
总用量 0
drwxr-x--- 2 clickhouse clickhouse 201 4月 28 16:37 201905_5_5_0
[root@node3 detached]# cd 201905_5_5_0/
[root@node3 201905_5_5_0]# ll
总用量 36
-r--r----- 2 clickhouse clickhouse 256 4月 28 16:09 checksums.txt
-r--r----- 2 clickhouse clickhouse 79 4月 28 16:09 columns.txt
-r--r----- 2 clickhouse clickhouse 1 4月 28 16:09 count.txt
-r--r----- 2 clickhouse clickhouse 102 4月 28 16:09 data.bin
-r--r----- 2 clickhouse clickhouse 112 4月 28 16:09 data.mrk3
-r--r----- 2 clickhouse clickhouse 10 4月 28 16:09 default_compression_codec.txt
-r--r----- 2 clickhouse clickhouse 4 4月 28 16:09 minmax_EventTime.idx
-r--r----- 2 clickhouse clickhouse 4 4月 28 16:09 partition.dat
-r--r----- 2 clickhouse clickhouse 10 4月 28 16:09 primary.idx
[root@node3 201905_5_5_0]#
- 装载数据
-- 装载 t_partition 表的 201905 分区
ALTER TABLE t_partition ATTACH PARTITION 201905;
-- 查看 t_partition 分区信息
SELECT partition_id,name,table,database from system.parts WHERE table = 't_partition';
┌─partition_id─┬─name─────────┬─table───────┬─database─┐
│ 201905 │ 201905_6_6_0 │ t_partition │ db1 │
│ 201906 │ 201906_2_2_0 │ t_partition │ db1 │
└──────────────┴──────────────┴─────────────┴──────────┘
-- 查询数据
select * from t_partition;
┌─ID───┬─URL─────────────┬──EventTime─┐
│ A004 │ www.sina.com.cn │ 2019-05-02 │
└──────┴─────────────────┴────────────┘
┌─ID───┬─URL────────────┬──EventTime─┐
│ A002 │ www.google.com │ 2019-06-01 │
└──────┴────────────────┴────────────┘
2.5.8 备份与还原分区
2.6 视图
2.6.1 视图的分类
ClickHouse拥有普通和物化两种视图，
- 物化视图拥有独立的存储，支持表引擎，数据保存形式由它的表引擎决定。
- 普通视图只是一层简单的查询代理，不存储任何数据
- 普通视图只起着简化查询的作用，不会对查询有任何的优化与增强。
- 普通视图语法
CREATE VIEW -- 创建视图
[ IF NOT EXISTS ] -- 如果视图已经存在则不进行创建
[db_name.]view_name -- 指定数据库与视图名
AS SELECT... -- 查询数据逻辑
- 物化视图语法
CREATE [MATERIALIZED] VIEW -- 创建物化视图
[IF NOT EXISTS] -- 如果视图已经存在则不进行创建
[db.]table_name -- 指定数据库与视图名
[TO[db.]name]
[ENGINE = engine] -- 指定引擎
[POPULATE] -- 物化视图的初始化策略，
-- 如果使用 POPULATE 修饰符，那么在创建视图的过程中，会连带将源表中已存在的数据一起倒入
-- 如果没有使用 POPULATE 修饰符，那么就创建一个空的视图，会同步之后写入源表的数据
-- 不支持当源表删除数据后，物化视图不支持同步删除数据
AS SELECT... -- 查询数据逻辑
2.6.2 普通视图使用示例
-- 创建普通视图
CREATE VIEW IF NOT EXISTS db1.v1 AS SELECT id,(age + 10) as age from db1.t1_default;
-- 查询数据
select * from v1;
┌─id─┬─age─┐
│ 1 │ 30 │
└────┴─────┘
-- 查看普通视图结构
describe v1;
┌─name─┬─type──┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ Int16 │ │ │ │ │ │
│ age │ Int16 │ │ │ │ │ │
└──────┴───────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
2.6.3 物化视图使用示例
-- 创建源表
CREATE TABLE IF NOT EXISTS salary (
`id` Int8,
`name` String,
`department_id` Int8,
`salary` Float32
) ENGINE = TinyLog;
-- 向源表写入数据
insert into salary values (1,'xiaoming',1,200),(2,'xiaohong',2,100),(3,'xiaogang',1,300),(4,'xiaoli',2,150);
-- 创建物化视图 带有 POPULATE 描述
CREATE MATERIALIZED VIEW db1.view_salary_count_populate
ENGINE = TinyLog
POPULATE
AS SELECT
department_id,
count(department_id) as employee_count,
sum(salary) as salary_count
FROM salary
group by department_id;
-- 查询数据
select * from view_salary_count_populate;
┌─department_id─┬─employee_count─┬─salary_count─┐
│ 1 │ 2 │ 500 │
│ 2 │ 2 │ 250 │
└───────────────┴────────────────┴──────────────┘
-- 创建物化视图 不带 POPULATE 描述
CREATE MATERIALIZED VIEW db1.view_salary_count
ENGINE = TinyLog
AS SELECT
department_id,
count(department_id) as employee_count,
sum(salary) as salary_count
FROM salary
group by department_id;
-- 查看创建的视图
show tables;
┌─name───────────────────────────────────────────┐
│ .inner_id.e414e835-144b-4107-91f3-68a800a0afd0 │
│ salary │
│ view_salary_count │
└────────────────────────────────────────────────┘
-- 从物化视图查询数据
select * from view_salary_count;
0 rows in set. Elapsed: 0.002 sec.
-- 向 view_salary_count 插入数据
insert into view_salary_count SELECT
department_id,
count(department_id) as employee_count,
sum(salary) as salary_count
FROM salary
group by department_id;
-- 查询写入数据后的物化视图
select * from view_salary_count;
┌─department_id─┬─employee_count─┬─salary_count─┐
│ 1 │ 2 │ 500 │
│ 2 │ 2 │ 250 │
└───────────────┴────────────────┴──────────────┘
-- 写入增量数据
insert into salary values
(5,'xiaod',1,100),
(6,'xiaoq',2,200),
(7,'xiaoh',1,300),
(8,'xiaot',2,450);
-- 查询写入增量数据后的物化视图
select * from view_salary_count;
┌─department_id─┬─employee_count─┬─salary_count─┐
│ 1 │ 2 │ 500 │
│ 2 │ 2 │ 250 │
│ 1 │ 2 │ 400 │
│ 2 │ 2 │ 650 │
└───────────────┴────────────────┴──────────────┘
2.6.4 删除视图
drop view if exists view_salary_count;2.5.5 卸载与装载视图
// 卸载视图
DETACH view view_salary_count;
// 装载视图
ATTACH MATERIALIZED VIEW db1.view_salary_count
ENGINE = TinyLog
AS SELECT
department_id,
count(department_id) as employee_count,
sum(salary) as salary_count
FROM salary
group by department_id;3. 数据表的基本操作
目前只有MergeTree、Merge和Distributed这三类表引擎支持ALTER查询
3.1 追加新字段
- 语法：
ALTER TABLE tb_name ADD COLUMN -- 为表添加字段
[ IF NOT EXISTS ] -- 如果已经存在该字段名则取消添加操作
name [type] -- 字段名 字段数据类型
[default_expr] -- 默认操作
[AFTER name_after] -- 添加到某个字段后面
- 添加字段示例
-- 创建表
CREATE TABLE IF NOT EXISTS alter_add_column (
`id` Int8,
`name` String,
`department_id` Int8,
`salary` Float32
) ENGINE = MergeTree
ORDER BY department_id ;
-- 插入数据
insert into alter_add_column values (1,'xiaoming',1,200),(2,'xiaohong',2,100)
-- 查看表结构
describe alter_add_column;
┌─name──────────┬─type────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ Int8 │ │ │ │ │ │
│ name │ String │ │ │ │ │ │
│ department_id │ Int8 │ │ │ │ │ │
│ salary │ Float32 │ │ │ │ │ │
└───────────────┴─────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
-- 在最后一列添加 address 字段
ALTER TABLE alter_add_column ADD COLUMN IF NOT EXISTS address String;
-- 查看表结构
describe alter_add_column;
┌─name──────────┬─type────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ Int8 │ │ │ │ │ │
│ name │ String │ │ │ │ │ │
│ department_id │ Int8 │ │ │ │ │ │
│ salary │ Float32 │ │ │ │ │ │
│ address │ String │ │ │ │ │ │
└───────────────┴─────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
-- 在 name 字段后增加 gender 字段，默认值为 ‘男’
ALTER TABLE alter_add_column ADD COLUMN IF NOT EXISTS gender String DEFAULT '男' AFTER name;
-- 查看表结构
describe alter_add_column;
┌─name──────────┬─type────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ Int8 │ │ │ │ │ │
│ name │ String │ │ │ │ │ │
│ gender │ String │ │ │ │ │ │
│ department_id │ Int8 │ │ │ │ │ │
│ salary │ Float32 │ │ │ │ │ │
│ address │ String │ │ │ │ │ │
└───────────────┴─────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
-- 查询数据
select * from alter_add_column;
┌─id─┬─name─────┬─gender─┬─department_id─┬─salary─┬─address─┐
│ 1 │ xiaoming │ 男 │ 1 │ 200 │ │
│ 2 │ xiaohong │ 男 │ 2 │ 100 │ │
└────┴──────────┴────────┴───────────────┴────────┴─────────┘
-- 数据表中已经存在的旧数据，当新追加的字段会使用默认值补全3.2 修改数据类型
修改某个字段的数据类型，实质上会调用相应的toType转型方法
如果当前的类型与期望的类型不能兼容，则修改操作将会失败
语法：
ALTER TABLE tb_name MODIFY COLUMN -- 修改列
[ IF EXISTS ] -- 如果列存在继续下面的操作
name [type] -- 列名，要改成哪个类型
[default_expr] -- 默认值- 使用示例
CREATE TABLE IF NOT EXISTS (
`id` Int8,
`name` String,
`department_id` Int8,
`salary` Float32
) ENGINE = MergeTree
ORDER BY department_id ;
-- 将 alter_add_column表的 id 字段从 Int8 类型改为 String
ALTER TABLE alter_add_column MODIFY COLUMN id String;
-- 查看表结构
describe alter_add_column;
┌─name──────────┬─type────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ String │ │ │ │ │ │
│ name │ String │ │ │ │ │ │
│ gender │ String │ DEFAULT │ '男' │ │ │ │
│ department_id │ Int8 │ │ │ │ │ │
│ salary │ Float32 │ │ │ │ │ │
│ address │ String │ │ │ │ │ │
└───────────────┴─────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
-- 尝试将 alter_add_column表的 gender 字段从 String 类型改为 Int8
ALTER TABLE alter_add_column MODIFY COLUMN gender Int8;
Received exception from server (version 21.4.3):
Code: 6. DB::Exception: Received from localhost:9977. DB::Exception: Cannot parse string '男' as Int8: syntax error at begin of string. Note: there are toInt8OrZero and toInt8OrNull functions, which returns zero/NULL instead of throwing exception.: While processing CAST('男' AS gender_tmp_alter13583609393046912516, 'Int8') AS gender: default expression and column type are incompatible..
-- Int8 类型不能兼容 String 类型，所以修改失败3.3 修改备注
- 语法：
ALTER TABLE tb_name COMMENT COLUMN [ IF EXISTS ] name 'somecomment'
-- 为 gender 字段添加备注 “性别”
ALTER TABLE alter_add_column COMMENT COLUMN IF EXISTS gender '性别';
-- 查看表结构
desc alter_add_column;
┌─name──────────┬─type────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ String │ │ │ │ │ │
│ name │ String │ │ │ │ │ │
│ gender │ String │ DEFAULT │ '男' │ 性别 │ │ │
│ department_id │ Int8 │ │ │ │ │ │
│ salary │ Float32 │ │ │ │ │ │
│ address │ String │ │ │ │ │ │
└───────────────┴─────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘3.4 删除已有字段
字段在被删除之后，它的数据也会被连带删除
语法
ALTER TABLE tb_name DROP COLUMN [ IF EXISTS ] name
- 使用示例
-- 删除 salary 列
ALTER TABLE alter_add_column DROP COLUMN IF EXISTS salary;
desc alter_add_column;
┌─name──────────┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ String │ │ │ │ │ │
│ name │ String │ │ │ │ │ │
│ gender │ String │ DEFAULT │ '男' │ 性别 │ │ │
│ department_id │ Int8 │ │ │ │ │ │
│ address │ String │ │ │ │ │ │
└───────────────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
3.5 修改表名
- RENAME：修改表的名称，在修改表名称时如果修改了数据库名，则可以将数据迁移到其他数据库
- 迁移数据只能在同一节点内执行，不能将数据迁移到其他节点
- 语法：
RENAME TABLE
[ db_name11. ] tb_name11 TO [ db_name12. ] tb_name12,
[ db_name21. ] tb_name21 TO [ db_name22. ] tb_name22,
...
-- 将 db1.salary 改名为 default.gongzi
RENAME TABLE db1.salary TO default.employee
3.6 清空表
- 将表内的数据全部清空，而不是直接删除这张表
- 语法：
TRUNCATE TABLE [IF EXISTS ][ db_name.] tb_name
-- 清空 employee 表
-- 查询数据
select * from employee;
┌─id─┬─name─────┬─department_id─┬─salary─┐
│ 1 │ xiaoming │ 1 │ 200 │
│ 2 │ xiaohong │ 2 │ 100 │
│ 3 │ xiaogang │ 1 │ 300 │
│ 4 │ xiaoli │ 2 │ 150 │
│ 5 │ xiaod │ 1 │ 100 │
│ 6 │ xiaoq │ 2 │ 200 │
│ 7 │ xiaoh │ 1 │ 300 │
│ 8 │ xiaot │ 2 │ 450 │
└────┴──────────┴───────────────┴────────┘
-- 清空 employee 表
TRUNCATE TABLE employee ;
-- 查询数据
select * from employee;
0 rows in set. Elapsed: 0.003 sec.4. 分布式执行DDL
ClickHouse支持集群模式，一个集群拥有1到多个节点。CREATE、ALTER、DROP、RENMAE及TRUNCATE这些DDL语句，都支持分布式执行。这意味着，如果在集群中任意一个节点上执行DDL语句，那么集群中的每个节点都会以相同的顺序执行相同的语句。这项特性意义非凡，它就如同批处理命令一样，省去了需要依次去单个节点执行DDL的烦恼。将一条普通的DDL语句转换成分布式执行十分简单，只需加上 ON CLUSTER cluster_name 声明即可。
例如，执行下面的语句后将会对ch_cluster集群内的所有节点广播这条DDL语句：
CREATE TABLE partition_v3 ON CLUSTER ch_cluster (
ID String,
URL String,
EventTime Date
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventTime)
ORDER BY ID;
