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

2.1、数据库引擎

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、查看表的结构：

desc scott.emp;

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.删除字段：

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.1、修改表名 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.