7、DDL
在 ClickHouse 中，DDL语言中修改表结构仅支持Merge表引擎、Distributed表引擎及MergeTree家族的表引擎,SQL 中的库、表、字段严格区分大小写。
7.1、数据库操作
-
建库：在创建数据库时，在
/var/lib/clickhouse/metadata/目录下会有对应的库目录和*.sql文件，库目录中会存入在当前库下建表的信息，*.sql文件中存放建库信息。当删除数据库时，`/var/lib/clickhouse/metadata/目录下对应的库目录和xx.sql文件也会被清空。:) CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster] [ENGINE = engine(...)]; -
查看数据库列表
:) SHOW DATABASES; -
查看当前数据库
:) SELECT database(); :) SELECT currentDatabase(); -
切换数据库
:) USE db_name; -
删除数据库
:) DROP DATABASE [IF EXISTS] db_name [ON CLUSTER cluster];
7.2、表操作
-
创建表
-- 直接创建表 CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] ( name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1], name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2], ... ) ENGINE = engine; -- 创建与other_db.other_table_name相同结构的表 -- 如果不指定ENGINE 默认与other_db.other_table_name表引擎一致 -- 不会将表other_db.other_table_name中数据填充到新表 CREATE TABLE [IF NOT EXISTS] [db.]table_name AS [other_db.]other_table_name [ENGINE = engine]; -- 指定引擎创建与SELECT子句结果相同结构的表 并将SELECT子句的结果填充至新表 CREATE TABLE [IF NOT EXISTS] [db.]table_name ENGINE = engine AS SELECT ...; -
查看表列表
:) SHOW TABLES; :) SHOW TABLES IN default; -
查看表定义
:) SHOW CREATE TABLE table_name; -
查看表字段
:) DESC table_name; -
删除表
:) DROP [TEMPORARY] TABLE [IF EXISTS] [db.]table_name [ON CLUSTER cluster]; -
修改表 - 添加列
:) ALTER TABLE [db].table_name [ON CLUSTER cluster] ADD COLUMN column_name UInt8; -
修改表 - 删除列
:) ALTER TABLE [db].table_name [ON CLUSTER cluster] DROP COLUMN column_name; -
修改表 - 清空列：不能清空排序、主键、分区字段。
-- 清空所有分区列 :) ALTER TABLE [db].table_name [ON CLUSTER cluster] CLEAR COLUMN column_name; -- 清空指定分区列 :) ALTER TABLE [db].table_name [ON CLUSTER cluster] CLEAR COLUMN column_name IN PARTITION 'partition'; -
修改表 - 修改列注释
:) ALTER TABLE [db].table_name [ON CLUSTER cluster] COMMENT COLUMN column_name 'comments'; -
修改表 - 修改列类型
:) ALTER TABLE [db].table_name [ON CLUSTER cluster] MODIFY COLUMN column_name UInt8; -
表重命名：可以作用于任意表引擎。
-- 如果表数据库命名发生变动 将物理移动表到新数据库 :) RENAME TABLE [db_1.]table_name_1 TO [db_2.]table_name_2, [db_3.]table_name_3 TO [db_4.]table_name_4, ... [ON CLUSTER cluster];
7.3、分区表操作
ClickHouse 中只有 MergeTree Family 引擎下的表才能分区，因此分区表就是 MergeTree Family 表引擎对应的分区表。
-
查看分区信息
:) SELECT database, table, name, partition FROM system.parts WHERE TABLE = 'table_name';也可以在 ClickHouse 节点上查看分区信息，路径为：
/var/lib/clickhouse/data/{database}/{table_name}/。 -
卸载分区：将指定分区的数据移动到
detached目录。服务器会忽略被分离的数据分区。只有当使用ATTACH时，服务器才会知晓这部分数据。当执行操作以后，可以对detached目录的数据进行任意操作，例如删除文件，或者放着不管。:) ALTER TABLE table_name DETACH PARTITION partition_expr; -
装载分区：可以将已经卸载的分区重新装载到对应的表分区中，将
detached目录中的数据重新移动到对应的表数据目录下。也可以将卸载的分区数据加载到其他表中，但是这个表需要与原来的表具有相同的表结构及相同的分区字段。:) ALTER TABLE table_name ATTACH PARTITION partition_expr; -
删除分区：可以针对分区表删除某个分区，之后再导入当前分区的数据，以达到数据更新的目的。执行删除分区命令是直接将对应分区数据删除，不会放入
detached目录。该操作会将分区标记为不活跃的，之后在大约10分钟内删除全部数据。:) ALTER TABLE table_name DROP PARTITION partition_expr; -
替换分区：替换分区支持将 A 表的分区数据复制到 B 表，并替换 B 表的已有分区。A 表中分区数据不会被删除，A 和 B 表必须要有相同的表结构且分区字段相同。此操作经常用作数据备份、表数据同步操作。
:) ALTER TABLE table_name_2 REPLACE PARTITION partition_expr FROM table_name_1; -
移动分区：该操作将 A 表的数据分区移动到 B 表，并删除 A 表的数据。
:) ALTER TABLE table_source MOVE PARTITION partition_expr TO TABLE table_dest; -
重置分区列：重置指定分区的特定列的值，就是将指定分区下某列的数据清空，如果建表时使用了 DEFAULT 语句，该操作会将列的值重置为该默认值。
:) ALTER TABLE table_name CLEAR COLUMN column_name IN PARTITION partition_expr;
7.4、临时表
ClickHouse支持临时表，临时表具备以下特征：
当会话结束或者链接中断时，临时表将随会话一起消失。
临时表仅能够使用Memory表引擎，创建临时表时不需要指定表引擎。
无法为临时表指定数据库。它是在数据库之外创建的，与会话绑定。
如果临时表与另一个表名称相同，那么当在查询时没有显式的指定
database的情况下，将优先使用临时表。-
对于分布式处理，查询中使用的临时表将被传递到远程服务器。
CREATE TEMPORARY TABLE [IF NOT EXISTS] table_name [ON CLUSTER cluster] ( name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1], name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2], ... )
在大多数情况下，临时表不是手动创建的，而是在使用外部数据进行查询或分布式时创建的，可以使用 ENGINE = Memory 表代替临时表。
7.5、视图
视图不存储数据，只是一层查询映射，类似于表的别名或者同义词，能简化查询，对原有表的查询性能没有增强的作用，具体性能依赖视图定义的语句，当从视图中查询时，视图只是替换了映射的查询语句。视图当基表删除后不可用。
-
创建视图
:) CREATE [OR REPLACE] VIEW [IF NOT EXISTS] [db.]view_name [ON CLUSTER] AS SELECT ...; -
删除视图
:) DROP VIEW view_name; :) DROP TABLE view_name;
7.6、物化视图
物化视图是查询结果集的一份持久化存储，所以它与普通视图完全不同，而非常趋近于表。“查询结果集”的范围很宽泛，可以是基础表中部分数据的一份简单拷贝，也可以是多表 join 之后产生的结果或其子集，或者原始数据的聚合指标等。
物化视图创建之后，若源表被写入新数据则物化视图也会同步更新，POPULATE 关键字决定了物化视图的更新策略。若有 POPULATE 则在创建视图的过程会将源表已经存在的数据一并导入，类似于 CREATE TABLE ... AS。若无 POPULATE 则物化视图在创建之后没有数据，只会同步创建之后写入源表的数据。ClickHouse 官方并不推荐使用 populated，因为在创建物化视图的过程中同时写入的数据不能被插入物化视图。
物化视图是种特殊的数据表，创建时需要指定引擎，可以用 SHOW TABLES 查看。另外，物化视图不支持 ALTER 操作。
产生物化视图的过程叫做物化（Materialization），广义地讲，物化视图是数据库中的预计算逻辑+显式缓存，典型的空间换时间思路，如果使用得当，可以避免对基础表的频繁查询并复用结果，从而显著提升查询的性能。
-
创建物化视图
:) CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db.]view_name [ON CLUSTER] [TO[db.]name] [ENGINE = engine] ORDER BY column [POPULATE] AS SELECT ...; -
删除物化视图
:) DROP TABLE view_name;
当创建好物化视图后，可以进入 /var/lib/clickhouse/data/{database}/ 目录下看到对应目录，当物化视图中同步基表数据时，目录中有对应的列文件和元数据记录文件，与普通创建表一样，有目录结构。
源表的删除或修改操作，不会触发物化视图数据的修改。
8、DML
ClickHouse 中 DML 语言包含插入、更新、删除数据操作，DML 操作仅适用 MergeTree 引擎，不能针对主键、分区键、排序键进行 DML 操作。DML操作不支持事务，一旦执行成功会立刻生效。
8.1、Insert 插入
:) INSERT INTO [db.]table_name [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...;
-- 使用 SELECT INSERT 需要保证结果集于目标表结构一致
:) INSERT INTO [db.]table_name SELECT ...;
8.2、Update 更新
由于 ClickHouse 针对的是 OLAP 业务分析，Update 操作在 ClickHouse 中不会经常使用。这种更新效率低下。
:) ALTER TABLE [db.]table_name UPDATE column1 = expr1 [, ...] WHERE filter_expr;
8.3、Delete 删除
由于 ClickHouse 针对的是 OLAP 业务分析，Delete 操作与 Update 操作一样在 ClickHouse 中不会经常使用。这种删除效率低下。
:) ALTER TABLE [db.]table_name [ON CLUSTER cluster] DELETE WHERE filter_expr;
8.4、导入导出
ClickHouse 中支持多种数据格式数据导入和导出，支持格式有 ORC、Parquet、Avro、Protobuf、Xml、Json、CSV 等。
具体操作参照官网：https://clickhouse.tech/docs/en/sql-reference/statements/alter/update/。
- 导入 CSV
_> clickhouse-client --format_csv_delimiter="," --query="INSERT INTO db.table_name FORMAT CSV" < /path/to/file.csv
- 导出 CSV
_> clickhouse-client --format_csv_delimiter="|" --query="SELECT * FROM db.table_name FORMAT CSV" > /path/to/file.csv
