clickhouse(数据库-表-表字段相关操作)

1、查看数据库版本

select version();

2、创建数据库

create database tmp;
-- 创建数据库指定引擎，Ordinary是默认引擎，可以不指定，Ordinary引擎下面可以创建任意引擎表
create database tmp ENGINE = Ordinary;
-- 创建分布式库
create database tmp on cluster clickhouse_my_cluster_name;
create database tmp_local on cluster clickhouse_my_cluster_name;
-- 删除数据库
drop database tmp;
-- 删除分布式库
drop database tmp on cluster clickhouse_my_cluster_name;
-- 创建表 tmp.emp

ATTACH 也可以建库，但是metadata目录下不会生成.sql文件，一般用于metadata元数据sql文件被删除后，恢复库表结构使用
正常还是使用create

3、表操作：

3.1、创建表

-- 创建数据库
create database tmp;
-- 创建数据库指定引擎，Ordinary是默认引擎，可以不指定，Ordinary引擎下面可以创建任意引擎表
create database tmp ENGINE = Ordinary;
-- 创建分布式库
create database tmp on cluster clickhouse_my_cluster_name;
create database tmp_local on cluster clickhouse_my_cluster_name;
-- 删除数据库
drop database tmp;
-- 删除分布式库
drop database tmp on cluster clickhouse_my_cluster_name;
-- 创建表 tmp.emp
 
-- 3、表操作
-- 3.1、创建分布式表
-- 3.1.1、创建分布式本地表，会在 clickhouse_my_cluster_name 集群的各个节点上进行创建
CREATE TABLE tmp.emp on cluster clickhouse_my_cluster_name
(
    `empno` int NOT NULL COMMENT '员工编码',
    `ename` varchar(32) COMMENT '员工姓名',
    `job` varchar(32) COMMENT '职位',
    `mgr_no` int COMMENT '领导的员工编号',
    `hiredate` Date COMMENT '入职日期',
    `sal` decimal(7, 2) COMMENT '月薪',
    `comm` decimal(7, 2) COMMENT '奖金'
)
ENGINE = MergeTree()
ORDER BY empno;
 
-- 3.1.2、创建分布式表，这个是逻辑表，实际的数据是在集群中 tmp_local数据库的emp表中存储的数据
-- 查询本表时 clickhouse 会查询集群 clickhouse_my_cluster_name 各个节点中本地表数据合并后返回
CREATE TABLE tmp.emp on cluster clickhouse_my_cluster_name
(
    `empno` int NOT NULL COMMENT '员工编码',
    `ename` varchar(32) COMMENT '员工姓名',
    `job` varchar(32) COMMENT '职位',
    `mgr_no` int COMMENT '领导的员工编号',
    `hiredate` Date COMMENT '入职日期',
    `sal` decimal(7, 2) COMMENT '月薪',
    `comm` decimal(7, 2) COMMENT '奖金'
)
ENGINE =  Distributed('clickhouse_my_cluster_name', 'tmp_local', 'emp', rand());
ORDER BY empno;
 
-- 3.2.1、创建分布式表带主键表，首先我们还是创建一个本地表，指定分区、主键、排序
CREATE TABLE tmp_local.user_active on cluster clickhouse_my_cluster_name
(
    `id` Int64 NOT NULL COMMENT '主键',
	`pid` varchar(32) NOT NULL  COMMENT '产品线',
    `version` varchar(32) NOT NULL  COMMENT '版本',
    `os` varchar(32) NOT NULL  COMMENT '系统',
    `channel` int COMMENT '渠道',
    `city` Date COMMENT '城市',
    `createtime` datetime default now() COMMENT '月薪'
)
ENGINE = MergeTree() PARTITION BY toYYYYMM(createtime)
primary key `id`
order by (`id`,createtime) SETTINGS index_granularity=8192;
 
-- 3.2.2、创建分布式表带主键表,指定分区、主键、排序
CREATE TABLE tmp.user_active on cluster clickhouse_my_cluster_name
(
    `id` Int64 NOT NULL COMMENT '主键',
	`pid` varchar(32) NOT NULL  COMMENT '产品线',
    `version` varchar(32) NOT NULL  COMMENT '版本',
    `os` varchar(32) NOT NULL  COMMENT '系统',
    `channel` int COMMENT '渠道',
    `city` Date COMMENT '城市',
    `createtime` datetime default now() COMMENT '月薪'
)
ENGINE =  Distributed('clickhouse_my_cluster_name', 'tmp_local', 'user_active', rand());
ARTITION BY toYYYYMM(createtime)
primary key `id`
order by (`id`,createtime) SETTINGS index_granularity=8192;
 
 
-- 3.2.3、删除集群所有节点中的表
drop table tmp_local.user_active on cluster clickhouse_my_cluster_name;
drop table tmp.user_active on cluster clickhouse_my_cluster_name;

3.2、表引擎类型

ENGINE：是表的引擎类型，

3.2.1、MergeTree 引擎

适用于高负载任务的最通用和功能最强大的表引擎。这些引擎的共同特点是可以快速插入数据并进行后续的后台数据处理。
MergeTree系列引擎支持数据复制（使用Replicated* 的引擎版本），分区和一些其他引擎不支持的其他功能。
该类型的引擎：

• MergeTree
• ReplacingMergeTree
• SummingMergeTree
• AggregatingMergeTree
• CollapsingMergeTree
• VersionedCollapsingMergeTree
• GraphiteMergeTree

3.2.2、日志 引擎

具有最小功能的轻量级引擎。当您需要快速写入许多小表（最多约100万行）并在以后整体读取它们时，该类型的引擎是最有效的。
该类型的引擎：

• TinyLog
• StripeLog
• Log

3.2.3、集成引擎

用于与其他的数据存储与处理系统集成的引擎。
该类型的引擎：

• Kafka
• MySQL
• ODBC
• JDBC
• HDFS

3.2.4、用于其他特定功能的引擎

该类型的引擎：

• Distributed
• MaterializedView
• Dictionary
• Merge
• File
• Null
• Set
• Join
• URL
• View
• Memory
• Buffer

3.3、查看表的结构：

3.3.1、表结构

desc scott.emp;   // scott 为库名  emp 为表名

3.3.2、查询数据库所有表

select table  from system.parts where database = 'economic_brain' group by table

3.3.3、查询数据库表所有字段

select distinct column from system.parts_columns where database='economic_brain' and table='company'
select name  from system.columns where  table='company'
 

3.3.4、查询数据库中表name、count、size。

select 
  table ,
  sum(rows) as counts,
  formatReadableSize(sum(bytes_on_disk)) AS size 
from system.parts
where database = 'economic_brain' --数据库名称，自定义
group by table
order by counts DESC;

3.3、arrayStringConcat和groupArray的运用，分组时合并数组为字符串

select arrayStringConcat(groupArray(categoryCode),',') as categoryCode  ,name,cateFirst  from
            (select cate_first name,left(category_code,2) categoryCode , cate_first cateFirst
            from category_code GROUP BY categoryCode,cate_first)x GROUP BY name,cateFirst

3.4、添加字段：

--3.3.1、 添加本节点字段
alter table scott.emp add column createtime datetime  default now() comment '数据写入时间';
alter table scott.emp add column deptno int default 10 comment '部门编号';
--3.3.2、 如果是集群表则在表名字后面添加 on cluster clusterName
alter table tmp.emp on cluster clickhouse_my_cluster_name add column createtime datetime default now() comment '数据写入时间';
alter table tmp_local.emp on cluster clickhouse_my_cluster_name add column createtime datetime default now() comment '数据写入时间';
 
alter table tmp_local.emp on cluster clickhouse_my_cluster_name 
add column remark Nullable(String) comment '备注' ;
 
alter table tmp.emp  on cluster clickhouse_my_cluster_name 
add column remark Nullable(String)  comment '备注' ;

3.4.1、追加字段操作可指定追加到什么位置

通过after 关键字在指定字段后添加新的字段：

alter table tmp.emp add column updatetime datetime default now() after createtime ;

3.4.2、添加注释：

alter table tmp.emp comment column updatetime '末次修改时间';

3.4.3.修改字段类型

alter table tmp.emp modify column hiredate datetime;

3.4.4 修改默认值：

alter table tmp.emp modify column deptno default 20;
 
查看表结构 
 
desc tmp.emp;
 
可以看到默认值已经修改。

3.4.5 修改TTL的信息：

alter table tmp.emp add column remark varchar(128) comment '说明信息' TTL createtime + toIntervalDay(31);
修改保存为62天：
alter table tmp.emp modify column remark varchar(254) TTL createtime+ toIntervalDay(62);
可以查看表结构信息：
desc scott.emp;

3.4.5 删除字段：

alter table scott.emp drop column remark;

4.表的重命名：

create table default.dept  
(
    `deptno` Int32,
    `dname` String,
    `loc` String
)
ENGINE = MergeTree()
ORDER BY deptno;

4.1、可以将 default.dept ---> scott.dept:

rename table default.dept to scott.dept;

4.2、修改表名 dept 为 department

rename table scott.dept  to scott.department;

注意：
表的重命名智能在单个节点范围之内运行，即只能在同一服务节点之内，不能在集群中的远程节点。

5.清空表的数据：

truncate table scott.department;

6.复制表的结构：

create table if not exists t_emp as scott.emp engine=TinyLog;

7.复制表结构和数据：

reate table if not exists t_employee engine=Memory as select * from scott.emp;

8.表的字段重命名：(20.4.2+版本支持)

alter table t_city rename column city_level TO  cityLevel;

9、查看表的定义：

show create table scott.emp\G
SHOW CREATE TABLE scott.emp;

10、null字段的修改

10.1、创建表

create table t(id int ,name varchar(32)) ENGINE = MergeTree PARTITION BY id ORDER BY id;

10.2、插入数据

insert into t(id,name)values(3,null);

注意：这个时候会抛出异常
Exception on client:
Code: 53. DB::Exception: Cannot insert NULL value into a column of type 'String' at: null);
insert into t(id,name)values(1,'wuhan');
说明：此时定义的表不能插入null

10.3、修改表的定义：

alter table t modify column name Nullable(varchar(32));

在此插入：

 insert into t(id,name)values(3,null);
 select * from t order by id FORMAT PrettyCompactMonoBlock;
 
┌─id─┬─name──┐
│  1 │ wuhan │
│  2 │       │
│  3 │ ᴺᵁᴸᴸ  │
└────┴───────┘

3 rows in set. Elapsed: 0.002 sec.

将null字段修改非null字段：

Code: 349. DB::Exception: Received from localhost:9000. DB::Exception: Cannot convert NULL value to non-Nullable type: (while 
reading from part /var/lib/clickhouse/data/default/t/3_4_4_0/): While executing MergeTreeThread. 

2 rows in set. Elapsed: 0.105 sec.

查询的时候报错：

Clickhouse> create table t1(id Nullable(int),name Nullable(String)) engine=MergeTree() order by id;
 
CREATE TABLE t1
(
    `id` Nullable(int),
    `name` Nullable(String)
)
ENGINE = MergeTree()
ORDER BY id
 

Received exception from server (version 20.5.2):
Code: 44. DB::Exception: Received from localhost:9000. DB::Exception: Sorting key cannot contain nullable columns. 
0 rows in set. Elapsed: 0.011 sec. 

结论：

1.可以将非null字段修改为null字段，有了数据之后就不能修改会非null.
2.null 字段不能在MergeTree系列表引擎中作为order by 字段