普通表

目前只有MergeTree、Merge和Distributed这三类表引擎支持ALTER查询,所以在进行alter操作的时候注意表的引擎。

所在建表的时候一般要求指定表的引擎

  1. # 建表方式1
  2. # 表引擎为MergeTree时需要指定主键primary key和排序order by 当两个字段一致时可以省略primary key关键字
  3. create table student (
  4. s_id Int8,
  5. s_name String,
  6. s_birth DateTime,
  7. s_sex Int8
  8. )
  9. engine = MergeTree()
  10. order by s_id;
  11. # 建表方式2 - 复制student表的表结构
  12. create table if not exists student_copy as student engine=MergeTree() order by s_id;
  13. # 建表方式3 - 通过select查询的方式创建表, 同时也会导入查询的结果数据
  14. create table if not exists student_select engine = MergeTree() primary key s_id order by s_id as select * from student;
  16. insert into student values(s);

临时表

创建临时表的方法是在普通表的基础之上添加 temporary关键字，相比普通表而言，临时表需要注意一下三点特殊之处。

  • 只支持Memory 表引擎
  • 临时表不属于任何数据库
  • 会话断开以后表删除 ,不会持久化
  • 如果本地表和临时表冲突 , 临时表优先
  1. # 临时表不需要指定引擎
  2. create temporary table student (
  3. s_id Int8,
  4. s_name String,
  5. s_birth DateTime,
  6. s_sex Int8
  7. );

分区表

目前只有MergeTree家族系列的表引擎才支持数据分区。分区的目的或概念和hive一致。

  1. create table student_partition (
  2. s_id Int8,
  3. s_name String,
  4. s_birth DateTime
  5. )
  6. engine = MergeTree()
  7. partition by toDate(s_birth)
  8. order by s_id;
  10. insert into student_partition values
  11. (1,'张三1','2021-07-07 01:13:12'),
  12. (2,'张三2','2021-07-26 11:00:21'),
  13. (3,'张三3','2021-07-25 11:00:21'),
  14. (4,'张三4','2021-07-27 11:00:21'),
  15. (5,'张三5','2021-07-25 11:00:21'),
  16. (6,'张三6','2021-07-27 11:00:21');
  17. # 查询分区状态
  18. select table ,name , partition , path from system.parts where table = 'student_partition' ;
  19. # 删除分区, 并且分区中分数据也会被删除
  20. alter table student_partition drop partition '2021-07-27';
  21. # 分区的卸载与装载，常用于分区数据的迁移和备份场景。
  22. # 卸载 ：会将数据移动到表目录下的detached目录，同时clickhouse不会在管理这些数据。
  23. alter table log2 detach partition '2021-07-27';
  24. # 装载：会将分区移cdetached目录
  25. alter table log2 attach partition '2021-07-27' ; # 装载以后数据就会被加载到表下

视图

视图分为普通视图和物化视图，其中物化视图拥有独立的存储，而普通视图只是一层简单的查询代理 。

  1. # 普通视图
  2. # 普通视图不会存储任何数据，它只是一层单纯的 SELECT 查询映射，起着简化查询、明晰语义的作用，对查询性能不会有任何增强
  3. create view student_view as select s_id*2,s_name,s_sex from student;
  4. select * from student_view;
  5. select * from student_view where s_sex = '男';
  7. # 物化视图
  8. # 物化视图支持表引擎，数据保存形式由它的表引擎决定，创建物化视图的完整语法如下所示
  9. create materialized view mv_log engine=Log populate as select * from plog;
  10. # 物化视图创建好之后，如果源表被写入新数据，那么物化视图也会同步更新
  11. # 如果在创建视图时添加了populates关键字的话会把源表数据同步到物化视图中，不加则反之，并只会同步之后写入源表的数据
  12. # 视图目前并不支持同步删除，如果在源表中删除了数据，物化视图的数据仍会保留
  13. # 物化视图就是一种特殊的表

分布式表

不存储数据，是本地表的代理

  1. # Distributed的参数
  2. # ck_cluster:集群名称；
  3. # my_db:库名；
  4. # my_table:表名，要求各个实例中库名、表名相同；
  5. # rand():选择路由分片的方式。或者指定分片
  6. CREATE TABLE log_test_all (
  7.      ts  DateTime,
  8.      uid String,
  9.      biz String
  10. ) ENGINE= Distributed(ck_cluster, my_db, my_table, rand());

表操作

  1. # 查看建表语句
  2. show create table student;
  3. # 修改表结构
  4. # 1.添加字段
  5. alter table student add column age UInt32 [after s_name(在s_name字段后添加)];
  6. # 2.修改字段
  7. alter table student modify column age UInt8;
  8. # 3.删除字段    
  9. alter table student dorp column age;
  10. # 4.字段添加/修改注释
  11. alter table student comment column age '年龄';
  12. # 5.修改表名或者移动表到另一个数据库
  13. rename table student to student1/[数据库].student
  14. # 删除表
  15. drop table if exists student