DDL 操作

数据库

数据库起到了命名空间的作用，可以有效规避命名冲突的问题，也为后续的数据隔离提供了支撑。任何一张数据表，都必须归属在某个数据库之下。

创建数据库

语法：

  1. CREATE DATABASE -- 创建数据库
  2.  IF NOT EXISTS -- 如果存在同名数据库，则不进行创建
  3.  db_name  -- 要创建的数据库名称
  4.  [ENGINE = engine] -- 使用的数据库引擎，如未指定默认使用：Atomic ，关于数据库引擎会在后面介绍

使用示例：

  1. -- 创建数据库
  2. CREATE DATABASE db_test1 ENGINE = Atomic;
  4. -- 查看数据库列表
  5. SHOW DATABASES;
  6. -- ┌─name─────┐
  7. -- │ db1      │
  8. -- │ db_test  │
  9. -- │ default  │
  10. -- │ system   │
  11. -- └──────────┘

存储目录

创建数据库的实质是物理磁盘上的一个文件目录，所以在语句执行之后，ClickHouse 便会在数据存储目录下 data 下创建 db_test1 数据库的文件目录：

  1. cd /var/lib/clickhouse/data
  2. ll
  3. # 总用量 0
  4. # drwxr-x--- 2 clickhouse clickhouse  20 4月  15 11:49 db1
  5. # drwxr-x--- 2 clickhouse clickhouse   6 4月  27 11:56 db_test1 # 刚创建的数据库
  6. # drwxr-x--- 2 clickhouse clickhouse 118 4月  19 17:16 default
  7. # drwxr-x--- 2 clickhouse clickhouse 113 4月  15 11:36 system

元数据

创建好数据库文件夹之后，会在 metadata 路径下也会一同创建用于恢复数据库的 db_test1.sql 文件：

  1. cd /var/lib/clickhouse/metadata
  2. ll
  3. # 总用量 32
  4. # lrwxrwxrwx 1 clickhouse clickhouse 66 4月  15 11:48 db1 -> /var/lib/clickhouse/store/1b6/1b63d246-d32d-45f1-a6eb-553dde05598b
  5. # -rw-r----- 1 clickhouse clickhouse 78 4月  15 11:48 db1.sql
  6. # lrwxrwxrwx 1 clickhouse clickhouse 66 4月  27 11:56 db_test1 -> /var/lib/clickhouse/store/9fe/9feb6454-42b9-44e6-beb3-564e62e4456e
  7. # -rw-r----- 1 clickhouse clickhouse 78 4月  27 11:56 db_test1.sql
  8. # lrwxrwxrwx 1 clickhouse clickhouse 66 4月  15 11:30 default -> /var/lib/clickhouse/store/9ac/9ac12c64-4c51-4ea8-8bf1-46f5006e9baa
  9. # -rw-r----- 1 clickhouse clickhouse 78 4月  15 11:30 default.sql
  10. # lrwxrwxrwx 1 clickhouse clickhouse 66 4月  15 11:30 system -> /var/lib/clickhouse/store/c24/c2458088-31e5-4424-a78b-1629304d14b7
  11. # -rw-r----- 1 clickhouse clickhouse 78 4月  15 11:30 system.sql
  13. cat db_test1.sql
  14. # ATTACH DATABASE _ UUID '9feb6454-42b9-44e6-beb3-564e62e4456e' # UUID
  15. # ENGINE = Atomic # 默认引擎

删除数据库

  1. -- 删除数据库
  2. DROP DATABASE db_test;

数据表

创建数据表

常规方法创建表

语法：

  1. CREATE TABLE -- 创建表
  2.  [IF NOT EXISTS] -- 如果该表名已经存在则不进行创建
  3.  [db_name.] -- 指定数据库名，未指定则使用默认数据库：default
  4.  table_name ( -- 表名 
  5.    name1 -- 列名
  6.      [type] -- 列数据类型
  7.      [DEFAULT|MATERIALIZED|ALIASexpr],  -- 默认数据表达式
  8.      [COMMENT] -- 字段备注
  9.    name2[type][DEFAULT|MATERIALIZED|ALIASexpr],
  10.    省略…
  11.  ) ENGINE = engine -- 指定表引擎，表引擎决定了数据表的特性，也决定了数据将会被如何存储及加载
  13. ------------------------------------------------
  15. -- 使用常规方式创建表
  16. CREATE TABLE IF NOT EXISTS t1(
  17.     `id` Int8 DEFAULT 1 COMMENT 'id',
  18.     `name2` String DEFAULT 'xiaoming' COMMENT '姓名'
  19. )ENGINE = Memory;
  21. -- 查看表结构
  22. DESCRIBE t1;
  23. -- ┌─name──┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
  24. -- │ id    │ Int8   │ DEFAULT      │ 1                  │ id      │                  │                │
  25. -- │ name2 │ String │ DEFAULT      │ 'xiaoming'         │ 姓名    │                  │                │
  26. -- └───────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

复制其他表的结构

仅复制建表时指定表的结构，建表完成后，原表结构变化或者被删除不会影响新表。仅复制结构，不复制数据。

语法：

  1. CREATE TABLE -- 创建表
  2.   [IF NOT EXISTS] -- 如果该表名已经存在则不进行创建
  3.   [db_name1.] table_name -- 指定数据库与要创建的表名
  4.   AS [db_name2.] table_name2 -- 要复制哪个库里面的哪张表 ，可以将表复制到不同的数据库
  5.   [ ENGINE = engine ] -- 指定表引擎，原表与新表的表引擎可以不同
  7. ------------------------------------------------
  9. -- 以复制其他表结构的方式创建表
  10. CREATE TABLE IF NOT EXISTS db1.default_t1 AS default.t1 ENGINE = TinyLog;

通过 SELECT 形式创建表

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

语法：

  1. CREATE TABLE -- 创建表
  2.   [IF NOT EXISTS] -- 如果该表名已经存在则不进行创建
  3.   [db_name.]table_name  -- 指定数据库与要创建的表名
  4.   ENGINE = engine -- 指定表引擎
  5.   AS SELECT … -- 指定查询数据的逻辑
  7. ------------------------------------------------
  9. CREATE TABLE IF NOT EXISTS default.t2 ENGINE = TinyLog AS SELECT id from default.t1;

删除表

语法：

  1. DROP TABLE -- 删除表
  2.    [IF EXISTS] -- 如果表存在则删除下面的表
  3.    [db_name.]table_name -- 指定数据库下面的表

默认值表达式

ClickHouse 表字段支持三种默认值表达式的定义方法，分别是DEFAULT、MATERIALIZED和ALIAS。不管使用哪种方式，当定义了默认值之后，就可以从默认值中推断出字段类型，便不会强制要求描述字段的类型了。

默认方式 写入数据 查询数据 数据存储
DEFAULT 可以出现在 INSERT 语句中 可以通过 SELECT * 返回 支持持久化
MATERIALIZED 不能被显示的赋值 不会出现在 SELECT * 查询的返回结果集中 支持持久化
ALIAS 不能被显示的赋值 不会出现在 SELECT * 查询的返回结果集中 不支持持久化，取值总依靠计算产生

实时示例：

  1. -- 创建表
  2. CREATE TABLE IF NOT EXISTS t1_default
  3. (
  4.     `id` Int16,
  5.     `age` Int8 DEFAULT 18,
  6.     `name` String MATERIALIZED concat('employee', CAST(age, 'String')),
  7.     `salary` ALIAS age * 100
  8. )
  9. ENGINE = Memory
  11. -- 查看表结构
  12. DESCRIBE TABLE t1_default
  13. -- ┌─name───┬─type───┬─default_type─┬─default_expression──────────────────────┬─comment─┬─codec_expression─┬─ttl_expression─┐
  14. -- │ id     │ Int16  │              │                                         │         │                  │                │
  15. -- │ age    │ Int8   │ DEFAULT      │ 18                                      │         │                  │                │
  16. -- │ name   │ String │ MATERIALIZED │ concat('employee', CAST(age, 'String')) │         │                  │                │
  17. -- │ salary │ Int16  │ ALIAS        │ age * 100                               │         │                  │                │
  18. -- └────────┴────────┴──────────────┴─────────────────────────────────────────┴─────────┴──────────────────┴────────────────┘
  20. -- 插入数据
  21. INSERT INTO TABLE t1_default (id) VALUES (1);
  23. -- 使用 select * 方式查询数据，并没有查询出 name,salary 两个字段
  24. SELECT * FROM t1_default;
  25. -- ┌─id─┬─age─┐ 
  26. -- │  1 │  18 │
  27. -- └────┴─────┘
  29. -- 指定列名查询字段，name 与 salary 根据表达式各自算出自己的数值
  30. SELECT id, age, name, salary FROM t1_default;
  31. -- ┌─id─┬─age─┬─name───────┬─salary─┐
  32. -- │  1 │  18 │ employee18 │   1800 │
  33. -- └────┴─────┴────────────┴────────┘
  35. -- 更新 age 值为 20 查看数据变化
  36. ALTER TABLE t1_default UPDATE age = 20 WHERE id = 1;
  38. -- 查询数据，在 Memory 引擎中 name 与 salary 两个字段都进行了更新
  39. SELECT id,age,name,salary FROM t1_default;
  40. -- ┌─id─┬─age─┬─name───────┬─salary─┐
  41. -- │  1 │  20 │ employee20 │   2000 │
  42. -- └────┴─────┴────────────┴────────┘

临时表

使用 TEMPORARY 关键字，描述临时表。临时表的生命周期是会话绑定的，所以它只支持 Memory 表引擎，如果会话结束，数据表就会被销毁。临时表不属于任何数据库，所以在它的建表语句中，既没有数据库参数也没有表引擎参数。

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

语法：

  1. CREATE TEMPORARY TABLE 
  2.   [IF NOT EXISTS ] 
  3.   table_name(
  4.     name1[type][DEFAULT|MATERIALIZED|ALIASexpr],
  5.     name2[type][DEFAULT|MATERIALIZED|ALIASexpr],
  6.     ...
  7.   )

使用示例：

  1. -- 创建临时表，与前面测试 默认值的数据表名称与结构相同
  2. CREATE TEMPORARY TABLE IF NOT EXISTS t1_default
  3. (
  4.     `id` Int16,
  5.     `age` Int8 DEFAULT 18,
  6.     `name` String MATERIALIZED concat('employee', CAST(age, 'String')),
  7.     `salary` ALIAS age * 100
  8. )
  10. -- 查询临时数据
  11. SELECT * FROM t1_default;
  13. -- 查询普通表数据 （需要加上数据库名称）
  14. SELECT * FROM db1.t1_default;
  15. -- ┌─id─┬─age─┐
  16. -- │  1 │  20 │
  17. -- └────┴─────┘
  19. -- 插入数据 优先向临时表插入数据
  20. INSERT INTO TABLE t1_default (id) VALUES (2);
  22. -- 查询数据 临时查询临时表的数据
  23. SELECT * FROM t1_default;
  24. -- ┌─id─┬─age─┐
  25. -- │  2 │  18 │
  26. -- └────┴─────┘

分区表

分区表介绍

数据分区是针对本地数据而言的，是数据的一种纵向切分。借助数据分区，在后续的查询过程中能够跳过不必要的数据目录，从而提升查询的性能。

合理地利用分区特性，还可以变相实现数据的更新操作，因为数据分区支持删除、替换和重置操作。MergeTree 家族系列的表引擎才支持数据分区。合理使用分区可以提高查询效率，但使用分区粒度不应过细，否则会致分区过多反而影响性能。

语法：

  1. CREATE TABLE -- 创建表
  2.  [IF NOT EXISTS] -- 如果该表名已经存在则不进行创建
  3.  [db_name.] -- 指定数据库名，未指定则使用默认数据库：default
  4.  table_name ( -- 表名 
  5.    name1 -- 列名
  6.      [type] -- 列数据类型
  7.      [DEFAULT|MATERIALIZED|ALIASexpr], -- 默认数据表达式
  8.    name2[type][DEFAULT|MATERIALIZED|ALIASexpr],
  9.    省略…
  10.  ) ENGINE = engine -- 指定表引擎，表引擎决定了数据表的特性，也决定了数据将会被如何存储及加载
  11.  PARTITION BY <PARTITION expr> -- 指定分区规则

创建分区表

使用示例：

  1. -- 创建分区表
  2. CREATE TABLE IF NOT EXISTS t_partition
  3. (
  4.     `ID` String,
  5.     `URL` String,
  6.     `EventTime` Date
  7. )
  8. ENGINE = MergeTree
  9. PARTITION BY toYYYYMM(EventTime)
  10. ORDER BY ID
  12. -- 插入数据
  13. INSERT INTO t_partition VALUES('A000','www.baidu.com','2019-05-01'),('A002','www.google.com','2019-06-01');
  15. -- 查询数据
  16. SELECT * FROM t_partition;
  17. -- ┌─ID───┬─URL────────────┬──EventTime─┐
  18. -- │ A002 │ www.google.com │ 2019-06-01 │
  19. -- └──────┴────────────────┴────────────┘
  20. -- ┌─ID───┬─URL───────────┬──EventTime─┐
  21. -- │ A000 │ www.baidu.com │ 2019-05-01 │
  22. -- └──────┴───────────────┴────────────┘
  24. -- 根据分区查询数据，可以利用分区索引跳过6月份的分区目录，只加载5月份的数据，从而带来查询的性能提升
  26. SELECT * FROM t_partition WHERE EventTime ='2019-05-01';
  27. -- ┌─ID───┬─URL───────────┬──EventTime─┐
  28. -- │ A000 │ www.baidu.com │ 2019-05-01 │
  29. -- └──────┴───────────────┴────────────┘

查询分区信息

可以通过 system 库中的parts 系统表查看 分区表信息。

使用示例：

  1. -- 查询表的分区存储信息
  3. SELECT partition_id,name,table,database,partition,path  from system.parts WHERE table = 't_partition';
  4. -- ┌─partition_id─┬─name─────────┬─table───────┬─database─┬─partition─┬─path─────────────────────────────────────────────────────────────────────────────┐
  5. -- │ 201905       │ 201905_1_1_0 │ t_partition │ db1      │ 201905    │ /var/lib/clickhouse/store/5f2/5f27038d-14b7-4b81-8a8d-8c40d2afe144/201905_1_1_0/ │
  6. -- │ 201906       │ 201906_2_2_0 │ t_partition │ db1      │ 201906    │ /var/lib/clickhouse/store/5f2/5f27038d-14b7-4b81-8a8d-8c40d2afe144/201906_2_2_0/ │
  7. -- └──────────────┴──────────────┴─────────────┴──────────┴───────────┴──────────────────────────────────────────────────────────────────────────────────┘
  9. -- 按年月划分后，目前拥有两个数据分区，且每个分区都对应一个独立的文件目录，用于保存各自部分的数据
  10. -- partitin_id 相当于分区主键，相同的 partition_id 的数据被分配到同一分区

删除指定分区

合理地设计分区键并利用分区的删除功能，就能够达到数据更新的目的。

语法：

  1. ALTER TABLE tb_name DROP PARTITION partition_expr

使用示例：

  1. -- 查询数据
  2. SELECT * FROM db1.t_partition;
  3. -- ┌─ID───┬─URL───────────┬──EventTime─┐
  4. -- │ A000 │ www.baidu.com │ 2019-05-01 │
  5. -- └──────┴───────────────┴────────────┘
  6. -- ┌─ID───┬─URL────────────┬──EventTime─┐
  7. -- │ A002 │ www.google.com │ 2019-06-01 │
  8. -- └──────┴────────────────┴────────────┘
  10. -- 删除分区示例 ， 删除 201905 分区，并重新写入数据
  11. ALTER TABLE db1.t_partition DROP PARTITION 201905;
  13. -- 查询分区信息
  14. -- SELECT partition_id,name,TABLE,database FROM system.parts WHERE TABLE = 't_partition';
  15. -- ┌─partition_id─┬─name─────────┬─table───────┬─database─┐
  16. -- │ 201906       │ 201906_2_2_0 │ t_partition │ db1      │
  17. -- └──────────────┴──────────────┴─────────────┴──────────┘
  19. -- 查询数据，从结果可以看出 201905 分区被删除
  20. SELECT * FROM db1.t_partition;
  21. -- ┌─ID───┬─URL────────────┬──EventTime─┐
  22. -- │ A002 │ www.google.com │ 2019-06-01 │
  23. -- └──────┴────────────────┴────────────┘
  25. -- 重新插入 201905 分区数据
  26. INSERT INTO db1.t_partition VALUES('A003','www.sina.com','2019-05-02');
  28. -- 查询分区信息
  29. SELECT partition_id,name,table,database  from system.parts WHERE table = 't_partition';
  30. -- ┌─partition_id─┬─name─────────┬─table───────┬─database─┐
  31. -- │ 201905       │ 201905_4_4_0 │ t_partition │ db1      │
  32. -- │ 201906       │ 201906_2_2_0 │ t_partition │ db1      │
  33. -- └──────────────┴──────────────┴─────────────┴──────────┘

复制分区数据

当两张表的结构相同，并且有相同的分区键时，可以将将一张表的分区复制到另一张表中。当重复向目标表复制分区时，源表分区的数据会覆盖目标表分区的数据。

语法：

  1. -- 将 A 表的某个分区复制到 B 表中
  2. ALTER TABLE B REPLACE PARTITION partition_expr FROM A

使用示例：

  1. -- 复制分区示例，创建一个空的分区表，将 db1.t_partition 的 201905 分区复制到新分区表中
  2. CREATE TABLE IF NOT EXISTS t_partition_b
  3. (
  4.     `ID` String,
  5.     `URL` String,
  6.     `EventTime` Date
  7. )
  8. ENGINE = MergeTree
  9. PARTITION BY toYYYYMM(EventTime)
  10. ORDER BY ID
  12. -- 查看 t_partition_b 分区信息，t_partition_b 表目前没有分区
  13. SELECT partition_id,name,table,database  from system.parts WHERE table = 't_partition_b';
  15. -- 复制分区
  16. ALTER TABLE t_partition_b REPLACE PARTITION 201905 FROM t_partition;
  18. -- 查看 t_partition_b 分区信息，分区复制成功
  19. SELECT partition_id,name,table,database  from system.parts WHERE table = 't_partition_b';
  20. -- ┌─partition_id─┬─name─────────┬─table─────────┬─database─┐
  21. -- │ 201905       │ 201905_2_2_0 │ t_partition_b │ db1      │
  22. -- └──────────────┴──────────────┴───────────────┴──────────┘

重置分区数据

如果数据表某一列的数据有误，可以将其重置为初始值。如果声明了默认值表达式，则以表达式为准，如果没有声明默认值，以相应数据类型的默认值为准。

说明：

  • 不能将分区键重置
  • 不能将 order by 描述的列重置

语法：

  1. ALTER TABLE tb_name CLEAR COLUMN column_name IN PARTITION partition_expr

使用示例：

  1. -- 将 t_partition_default 表 的 201905 分区中的 URL 字段 重置
  2. ALTER TABLE t_partition_default CLEAR COLUMN URL IN PARTITION 201905;

卸载与装载分区

卸载与装载这一对伴生的操作，常用于分区数据的迁移和备份场景：

  • 通过 DETACH 语句卸载表分区。
  • 通过 ATTACH 语句装载表分区。

分区被卸载后，分区不可用，但物理数据并没有删除，而是被转移到了当前数据表目录的 detached 子目录下，一旦分区被移动到了 detached 子目录，就代表它已经脱离了 ClickHouse 的管理，ClickHouse 并不会主动清理这些文件。这些分区文件会一直存在，除非主动删除或者重新装载它们。

装载分区则是反向操作，它能够将 detached 子目录下的某个分区重新装载回去。

语法：

  1. -- 卸载分区
  2. ALTER TABLE tb_name DETACH PARTITION partition_expr
  4. -- 装载分区
  5. ALTER TABLE tb_name ATTACH PARTITION partition_expr

使用示例：

  1. -- 装载与卸载 t_partition 表中的 201905 分区
  2. ALTER TABLE t_partition DETACH PARTITION 201905;
  3. ALTER TABLE t_partition ATTACH PARTITION 201905;

视图

ClickHouse 拥有普通和物化两种视图，

  • 物化视图拥有独立的存储，支持表引擎，数据保存形式由它的表引擎决定。
  • 普通视图只是一层简单的查询代理，不存储任何数据，普通视图只起着简化查询的作用，不会对查询有任何的优化与增强。

创建视图

普通视图语法：

  1. CREATE VIEW -- 创建视图
  2.   [ IF NOT EXISTS ] -- 如果视图已经存在则不进行创建
  3.   [db_name.]view_name  -- 指定数据库与视图名
  4.   AS SELECT... -- 查询数据逻辑

物化视图语法：

  1. CREATE [MATERIALIZED] VIEW -- 创建物化视图
  2.   [IF NOT EXISTS] -- 如果视图已经存在则不进行创建
  3.   [db.]table_name -- 指定数据库与视图名
  4.   [TO[db.]name]
  5.   [ENGINE = engine] -- 指定引擎
  6.   [POPULATE] -- 物化视图的初始化策略，
  7.              -- 如果使用 POPULATE 修饰符，那么在创建视图的过程中，会连带将源表中已存在的数据一起倒入
  8.              -- 如果没有使用 POPULATE 修饰符，那么就创建一个空的视图，会同步之后写入源表的数据
  9.              -- 不支持当源表删除数据后，物化视图不支持同步删除数据
  10.   AS SELECT... -- 查询数据逻辑

使用示例：

  1. -- 创建普通视图
  2. CREATE VIEW IF NOT EXISTS  db1.v1 AS SELECT id,(age + 10) as age from db1.t1_default;
  4. -- 查询数据
  5. SELECT * FROM v1;
  6. -- ┌─id─┬─age─┐
  7. -- │  1 │  30 │
  8. -- └────┴─────┘
  10. -- 查看普通视图结构
  11. DESCRIBE v1;
  12. -- ┌─name─┬─type──┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
  13. -- │ id   │ Int16 │              │                    │         │                  │                │
  14. -- │ age  │ Int16 │              │                    │         │                  │                │
  15. -- └──────┴───────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
  17. -- 创建源表
  18. CREATE TABLE IF NOT EXISTS salary (
  19.     `id` Int8,
  20.     `name` String,
  21.     `department_id` Int8,
  22.     `salary` Float32
  23. ) ENGINE = TinyLog;
  25. -- 向源表写入数据
  26. INSERT INTO salary VALUES (1,'xiaoming',1,200),(2,'xiaohong',2,100),(3,'xiaogang',1,300),(4,'xiaoli',2,150);
  28. -- 创建物化视图 带有 POPULATE 描述
  29. CREATE MATERIALIZED VIEW db1.view_salary_count_populate
  30. ENGINE = TinyLog
  31. POPULATE 
  32. AS SELECT
  33.   department_id,
  34.   count(department_id) AS employee_count,
  35.   sum(salary) AS salary_count
  36. FROM salary 
  37. GROUP BY department_id;
  39. -- 查看创建的视图
  40. SHOW TABLES;
  41. -- ┌─name───────────────────────────────────────────┐
  42. -- │ .inner_id.e414e835-144b-4107-91f3-68a800a0afd0 │
  43. -- │ salary                                         │
  44. -- │ view_salary_count                              │
  45. -- └────────────────────────────────────────────────┘
  47. -- 从物化视图查询数据
  48. SELECT * FROM view_salary_count;
  49. -- 0 rows in set. Elapsed: 0.002 sec.
  51. -- 向 view_salary_count 插入数据
  52. INSERT INTO view_salary_count SELECT
  53.   department_id,
  54.   count(department_id) AS employee_count,
  55.   sum(salary) AS salary_count
  56. FROM salary 
  57. GROUP BY department_id;
  60. -- 查询写入数据后的物化视图
  61. SELECT * FROM view_salary_count;
  62. -- ┌─department_id─┬─employee_count─┬─salary_count─┐
  63. -- │             1 │              2 │          500 │
  64. -- │             2 │              2 │          250 │
  65. -- └───────────────┴────────────────┴──────────────┘
  67. -- 写入增量数据
  68. INSERT INTO salary VALUES 
  69. (5,'xiaod',1,100),
  70. (6,'xiaoq',2,200),
  71. (7,'xiaoh',1,300),
  72. (8,'xiaot',2,450);
  74. -- 查询写入增量数据后的物化视图
  75. SELECT * FROM view_salary_count;
  76. -- ┌─department_id─┬─employee_count─┬─salary_count─┐
  77. -- │             1 │              2 │          500 │
  78. -- │             2 │              2 │          250 │
  79. -- │             1 │              2 │          400 │
  80. -- │             2 │              2 │          650 │
  81. -- └───────────────┴────────────────┴──────────────┘

删除视图

语法：

  1.  DROP view if exists view_salary_count;

卸载与装载视图

  1. // 卸载视图
  2. DETACH view view_salary_count;
  4. // 装载视图
  5. ATTACH MATERIALIZED VIEW db1.view_salary_count
  6. ENGINE = TinyLog
  7. AS SELECT
  8.   department_id,
  9.   count(department_id) AS employee_count,
  10.   sum(salary) AS salary_count
  11. FROM salary 
  12. GROUP BY department_id;

数据表的基本操作

目前只有 MergeTree、Merge 和 Distributed 这三类表引擎支持 ALTER 查询。

追加新字段

语法：

  1. ALTER TABLE tb_name ADD COLUMN -- 为表添加字段
  2.   [ IF NOT EXISTS ] -- 如果已经存在该字段名则取消添加操作
  3.   name [type] -- 字段名字段数据类型
  4.   [default_expr] -- 默认操作
  5.   [AFTER name_after] -- 添加到某个字段后面

使用示例：

  1. -- 创建表
  2. CREATE TABLE IF NOT EXISTS alter_add_column (
  3.     `id` Int8,
  4.     `name` String,
  5.     `department_id` Int8,
  6.     `salary` Float32
  7. ) ENGINE = MergeTree
  8. ORDER BY department_id ;
  10. -- 插入数据 
  11. INSERT INTO alter_add_column VALUES (1,'xiaoming',1,200),(2,'xiaohong',2,100)
  13. -- 查看表结构
  14. DESCRIBE alter_add_column;
  15. -- ┌─name──────────┬─type────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
  16. -- │ id            │ Int8    │              │                    │         │                  │                │
  17. -- │ name          │ String  │              │                    │         │                  │                │
  18. -- │ department_id │ Int8    │              │                    │         │                  │                │
  19. -- │ salary        │ Float32 │              │                    │         │                  │                │
  20. -- └───────────────┴─────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
  22. -- 在最后一列添加 address 字段
  23. ALTER TABLE alter_add_column ADD COLUMN IF NOT EXISTS address String;
  25. -- 查看表结构
  26. DESCRIBE alter_add_column;
  27. -- ┌─name──────────┬─type────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
  28. -- │ id            │ Int8    │              │                    │         │                  │                │
  29. -- │ name          │ String  │              │                    │         │                  │                │
  30. -- │ department_id │ Int8    │              │                    │         │                  │                │
  31. -- │ salary        │ Float32 │              │                    │         │                  │                │
  32. -- │ address       │ String  │              │                    │         │                  │                │
  33. -- └───────────────┴─────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
  35. -- 在 name 字段后增加 gender 字段，默认值为 ‘男’
  36. ALTER TABLE alter_add_column ADD COLUMN IF NOT EXISTS gender String DEFAULT '男' AFTER name;
  38. -- 查看表结构
  39. DESCRIBE alter_add_column;
  40. -- ┌─name──────────┬─type────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
  41. -- │ id            │ Int8    │              │                    │         │                  │                │
  42. -- │ name          │ String  │              │                    │         │                  │                │
  43. -- │ gender        │ String  │              │                    │         │                  │                │
  44. -- │ department_id │ Int8    │              │                    │         │                  │                │
  45. -- │ salary        │ Float32 │              │                    │         │                  │                │
  46. -- │ address       │ String  │              │                    │         │                  │                │
  47. -- └───────────────┴─────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
  49. -- 查询数据，数据表中已经存在的旧数据，当新追加的字段会使用默认值补全
  50. SELECT * FROM alter_add_column;
  51. -- ┌─id─┬─name─────┬─gender─┬─department_id─┬─salary─┬─address─┐
  52. -- │  1 │ xiaoming │ 男     │             1 │    200 │         │
  53. -- │  2 │ xiaohong │ 男     │             2 │    100 │         │
  54. -- └────┴──────────┴────────┴───────────────┴────────┴─────────┘

修改数据类型

修改某个字段的数据类型，实质上会调用相应的 toType 转型方法，如果当前的类型与期望的类型不能兼容，则修改操作将会失败。

语法：

  1. ALTER TABLE tb_name MODIFY COLUMN -- 修改列
  2.   [ IF EXISTS ] -- 如果列存在继续下面的操作
  3.   name [type] -- 列名，要改成哪个类型
  4.   [default_expr] -- 默认值

使用示例

  1. CREATE TABLE IF NOT EXISTS  (
  2.     `id` Int8,
  3.     `name` String,
  4.     `department_id` Int8,
  5.     `salary` Float32
  6. ) ENGINE = MergeTree
  7. ORDER BY department_id ;
  9. -- 将 alter_add_column表的 id 字段从 Int8 类型改为 String
  10. ALTER TABLE alter_add_column MODIFY COLUMN id String;
  12. -- 查看表结构
  13. describe alter_add_column;
  15. ┌─name──────────┬─type────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
  16. │ id            │ String  │              │                    │         │                  │                │
  17. │ name          │ String  │              │                    │         │                  │                │
  18. │ gender        │ String  │ DEFAULT      │ '男'               │         │                  │                │
  19. │ department_id │ Int8    │              │                    │         │                  │                │
  20. │ salary        │ Float32 │              │                    │         │                  │                │
  21. │ address       │ String  │              │                    │         │                  │                │
  22. └───────────────┴─────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
  24. -- 尝试将 alter_add_column表的 gender 字段从 String 类型改为 Int8（Int8 类型不能兼容 String 类型，所以修改失败）
  25. ALTER TABLE alter_add_column MODIFY COLUMN gender Int8;
  26. -- Received exception from server (version 21.4.3):
  27. -- 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..

修改备注

语法：

  1. ALTER TABLE tb_name COMMENT COLUMN [ IF EXISTS ] name 'somecomment'
  3. -- 为 gender 字段添加备注 “性别”
  4. ALTER TABLE alter_add_column COMMENT COLUMN  IF EXISTS  gender '性别';
  6. -- 查看表结构
  7. DESC alter_add_column;
  8. -- ┌─name──────────┬─type────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
  9. -- │ id            │ String  │              │                    │         │                  │                │
  10. -- │ name          │ String  │              │                    │         │                  │                │
  11. -- │ gender        │ String  │ DEFAULT      │ '男'               │ 性别    │                  │                │
  12. -- │ department_id │ Int8    │              │                    │         │                  │                │
  13. -- │ salary        │ Float32 │              │                    │         │                  │                │
  14. -- │ address       │ String  │              │                    │         │                  │                │
  15. -- └───────────────┴─────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

删除已有字段

字段在被删除之后，它的数据也会被连带删除。

语法：

  1. ALTER TABLE tb_name DROP COLUMN [ IF EXISTS ] name

修改表名

RENAME：修改表的名称，在修改表名称时如果修改了数据库名，则可以将数据迁移到其他数据库。迁移数据只能在同一节点内执行，不能将数据迁移到其他节点。

语法：

  1. RENAME TABLE 
  2.  [ db_name11. ] tb_name11 TO [ db_name12. ] tb_name12,
  3.  [ db_name21. ] tb_name21 TO [ db_name22. ] tb_name22, 
  4.  ...

使用示例：

  1. -- 将 db1.salary 改名为  default.gongzi
  2. RENAME TABLE db1.salary TO default.employee

清空表

将表内的数据全部清空，而不是直接删除这张表。

语法：

  1. TRUNCATE TABLE [IF EXISTS ][ db_name.] tb_name

分布式执行 DDL

ClickHouse 支持集群模式，一个集群拥有 1 到多个节点。CREATE、ALTER、DROP、RENMAE 及 TRUNCATE 这些 DDL 语句，都支持分布式执行。这意味着，如果在集群中任意一个节点上执行 DDL 语句，那么集群中的每个节点都会以相同的顺序执行相同的语句。这项特性意义非凡，它就如同批处理命令一样，省去了需要依次去单个节点执行 DDL 的烦恼。将一条普通的 DDL 语句转换成分布式执行十分简单，只需加上 ON CLUSTER cluster_name 声明即可。

例如，执行下面的语句后将会对 ch_cluster 集群内的所有节点广播这条 DDL 语句：

  1. CREATE TABLE partition_v3 ON CLUSTER ch_cluster ( 
  2.   ID String,
  3.   URL String,
  4.   EventTime Date
  5. ) ENGINE = MergeTree() 
  6.   PARTITION BY toYYYYMM(EventTime) 
  7.   ORDER BY ID;

DML 操作

数据的写入

ClickHouse 内部所有的数据操作都是面向 Block 数据块的，所以 INSERT 查询最终会将数据转换为 Block 数据块。

INSERT 语句在单个数据块的写入过程中是具有原子性的。 在默认的情况下，每个数据块最多可以写入 1,048,576 行数据（由 max_insert_block_size 参数控制）。

如果一条 INSERT 语句写入的数据少于 max_insert_block_size 行，则这批数据的写入具有原子性，即要么全部成功，要么全部失败。

需要注意的是，只有在 ClickHouse 服务端处理数据的时候才具有这种原子写入的特性，例如使用 JDBC 或者 HTTP 接口时。因为 max_insert_block_size 参数在使用 CLI 命令行或者 INSERT SELECT 子句写入时是不生效的。

数据写入有三种方式：

使用 VALUES 格式

语法：

  1. INSERT INTO  -- 插入数据
  2.   [db.] table_name -- 指定数据库与数据表
  3.   [(c1,c2,c3…)]  -- 指定要插入数据的列
  4. VALUES
  5. (v11,v12,v13…), -- 插入的数据值
  6. (v21,v22,v23…),
  7. ...

使用示例：

  1. -- 创建表
  2. CREATE TABLE IF NOT EXISTS insert_test(
  3.     `id` Int8,
  4.     `name` String,
  5.     `insert_type` String
  6. )ENGINE = MergeTree()
  7. order by id;
  9. -- 常规方式写入数据
  10. INSERT INTO default.insert_test (id,name,insert_type) VALUES(1,'小明','insert'),(2,'小红','insert');
  11. INSERT INTO default.insert_test  VALUES(3,'小丽','insert');
  12. INSERT INTO default.insert_test (name,insert_type) VALUES('小雪','insert');

使用指定格式

语法：

  1. INSERT INTO -- 插入数据
  2.   [db.] table_name  -- 指定数据库与数据表
  3.   [(c1,c2,c3…)] -- 指定要插入数据的列
  4. FORMAT format_name -- 指定数据格式，支持的数据格式可以在下面文档查看
  5. data_set -- 插入的数据集

格式详见：ClickHouse 支持的输入 / 输出格式

使用示例：

  1. # 准备 2个 csv 格式文件，一个包含列名，一个不包含列名
  2. cat data_without_title.csv 
  3. # 4,xioaming,format_csv_without_title
  4. # 5,xiaohong,format_csv_without_title
  6. cat data_with_title.csv  # 包含列名
  7. # id,name,insert_type
  8. # 6,xiox,format_csv_with_title
  9. # 7,xiao,format_csv_with_title
  11. # 导入不包含表头的 csv 文件里的数据
  12. cat data_without_title.csv | clickhouse-client --port=9977 --query " INSERT INTO default.insert_test FORMAT CSV "
  14. # 导入包含表头的 csv 文件里的数据
  15. clickhouse-client --port=9977 --query="INSERT INTO default.insert_test FORMAT CSVWithNames" < format_csv_with_title.csv

第三种是使用 SELECT 子句形式

语法：

  1. INSERT INTO  -- 插入数据
  2. [db.] table_name  -- 指定数据库与数据表
  3. [(c1,c2,c3…)]  -- 指定要插入数据的列
  4. SELECT ... -- 指定查询语句

写入数据示例：

  1. INSERT INTO default.insert_test SELECT 8,'小刚','select';

修改与删除数据

ClickHouse 提供了 DELETE 和 UPDATE 的能力，这类操作被称为 Mutation 查询，它可以看作 ALTER 语句的变种。虽然 Mutation 能最终实现修改和删除，但不能完全以通常意义上的 UPDATE 和 DELETE 来理解，我们必须清醒地认识到它的不同：

  • Mutation 语句是一种 “很重” 的操作，更适用于批量数据的修改和删除。
  • 它不支持事务，一旦语句被提交执行，就会立刻对现有数据产生影响，无法回滚。
  • Mutation 语句的执行是一个异步的后台过程，语句被提交之后就会立即返回。

所以这并不代表具体逻辑已经执行完毕，它的具体执行进度需要通过 system.mutations 系统表查询。

DELETE

DELETE 语法如下所示：

  1. -- 数据删除的范围由WHERE查询子句决定
  2. ALTER TABLE [db_name.] table_name DELETE WHERE filter_expr

实时示例：

  1. -- 查看存储目录文件
  2. -- cd /var/lib/clickhouse/data/default/insert_test
  4. -- ll
  5. -- 总用量 4
  6. -- drwxr-x--- 2 clickhouse clickhouse 152 4月  29 10:54 all_1_1_0
  7. -- drwxr-x--- 2 clickhouse clickhouse 152 4月  29 10:54 all_2_2_0
  8. -- drwxr-x--- 2 clickhouse clickhouse 152 4月  29 10:54 all_3_3_0
  9. -- drwxr-x--- 2 clickhouse clickhouse   6 4月  29 10:53 detached
  10. -- -rw-r----- 1 clickhouse clickhouse   1 4月  29 10:53 format_version.txt
  12. -- 删除 id =1 的数据，这是一个异步的后台执行动作，但是由于演示的数据很少，DELETE操作给人的感觉和常用的OLTP数据库无异
  13. ALTER TABLE default.insert_test DELETE WHERE id = 1;
  16. -- 查看存储目录变化，每个文件夹都增加了一个同名目录，末尾增加了一个 _4 的后缀
  17. -- ll
  18. -- 总用量 8
  19. -- drwxr-x--- 2 clickhouse clickhouse 152 4月  29 10:54 all_1_1_0
  20. -- drwxr-x--- 2 clickhouse clickhouse 152 4月  29 10:55 all_1_1_0_4
  21. -- drwxr-x--- 2 clickhouse clickhouse 152 4月  29 10:54 all_2_2_0
  22. -- drwxr-x--- 2 clickhouse clickhouse 152 4月  29 10:55 all_2_2_0_4
  23. -- drwxr-x--- 2 clickhouse clickhouse 152 4月  29 10:54 all_3_3_0
  24. -- drwxr-x--- 2 clickhouse clickhouse 152 4月  29 10:55 all_3_3_0_4
  25. -- drwxr-x--- 2 clickhouse clickhouse   6 4月  29 10:53 detached
  26. -- -rw-r----- 1 clickhouse clickhouse   1 4月  29 10:53 format_version.txt
  27. -- -rw-r----- 1 clickhouse clickhouse  81 4月  29 10:55 mutation_4.txt
  29. -- 增加了 mutation_4.txt 文件 记录了进行了哪些操作，与操作时间
  30. -- cat mutation_4.txt
  31. -- format version: 1
  32. -- create time: 2021-04-29 10:55:01
  33. -- commands: DELETE WHERE id = 1
  35. -- 查看 system.mutations 操作记录表
  36. SELECT database,table,mutation_id,block_numbers.number AS num,is_done FROM system.mutations;
  37. -- ┌─database─┬─table───────────────┬─mutation_id────┬─num─┬─is_done─┐
  38. -- │ db1      │ alter_add_column    │ mutation_2.txt │ [2] │       1 │
  39. -- │ db1      │ alter_add_column    │ mutation_3.txt │ [3] │       1 │
  40. -- │ db1      │ t_partition_default │ mutation_3.txt │ [3] │       1 │
  41. -- │ default  │ insert_test         │ mutation_4.txt │ [4] │       1 │
  42. -- └──────────┴─────────────────────┴────────────────┴─────┴─────────┘

至此，整个 Mutation 操作的逻辑就比较清晰了。每执行一条 ALTER DELETE 语句，都会在 mutations 系统表中生成一条对应的执行计划，当 is_done 等于 1 时表示执行完毕。

与此同时，在数据表的根目录下，会以 mutation_id 为名生成与之对应的日志文件用于记录相关信息。

而数据删除的过程是以数据表的每个分区目录为单位，将所有目录重写为新的目录，新目录的命名规则是在原有名称上加上 system.mutations.block_numbers.number。

数据在重写的过程中会将需要删除的数据去掉。旧的数据目录并不会立即删除，而是会被标记成非激活状态（active 为 0）。等到 MergeTree 引擎的下一次合并动作触发时，这些非激活目录才会被真正从物理意义上删除。

UPDATE

数据修改除了需要指定具体的列字段之外，整个逻辑与数据删除别无二致。

语法：

  1. -- UPDATE支持在一条语句中同时定义多个修改字段，分区键和主键不能作为修改字段。
  2. ALTER TABLE [db_name.]table_name UPDATE column1=expr1 [,...] WHERE filter_expr

使用示例：

  1. ALTER TABLE default.insert_test UPDATE name='小明' WHERE id=2;