ClickHouse系列教程 6. DDL 相关操作介绍

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 复制其他表的结构

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形式创建表

1. 不仅创建新的表，也会把查询结果插入到该表中

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 临时表

1. 使用 TEMPORARY 关键字，描述临时表

2. 临时表的生命周期是会话绑定的，所以它只支持Memory表引擎，如果会话结束，数据表就会被销毁

3. 临时表不属于任何数据库，所以在它的建表语句中，既没有数据库参数也没有表引擎参数

4. 临时表名称可以与普通表名称相同，查询时临时表的优先级大于普通表

5. 定义

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;