2.ClickHouse数据类型和定义数据表四、数据类型五、定义数据表

54 阅读 0 评论 36 点赞

我是靠谱客的博主舒心彩虹，最近开发中收集的这篇文章主要介绍2.ClickHouse数据类型和定义数据表四、数据类型五、定义数据表，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

四、数据类型

数据类型划分：

基础类型、复合类型，特殊类型

4.1 基础类型

4.1.1 数值类型

Int 整型 (有符号)

名称	大小(字节)	范围	Mysql普通观念
Int8	1	-128到127	Tinyint
Int16	2	-32768到32767	Smallint
Int32	4	-2147483648到2147483647	int
Int64	8	-9223372036854775808到9223372036854775807	bigint

Int 整型（无符号）

名称	大小(字节)	范围	Mysql普通观念
UInt8	1	0到255	Tinyint Unsigned
UInt16	2	0到65535	Smallint Unsigned
UInt32	4	0到4294967295	int Unsigned
UInt64	8	0到18446744073709551615	bigint Unsigned

浮点型 Float

名称	大小(字节)	有效精度	Mysql普通观念	起始点
Float32(单精度)	4	7	Float	小数点后第8位
Float64(双精度)	8	16	Double	小数点后第17位起

Decimal 定点数

名称	等效声明	范围
Decimal32(S)	Decimal(1~9,S)	-110^(9~S)到110(9~S)
Decimal64(S)	Decimal(10~18,S)	-110^{(18~S)到110}(18~S)
Decimal128(S)	Decimal(19~38,S)	-110^{(38~S)到110}(38~S)

4.1.2 字符串类型

名称	长度	备注
String	长度不限	使用统一编码即可（utf-8）
FixedString	固定长度字符串	不够用null字节末尾填充
UUID	32位（8-4-4-4-12）	常用主键；未赋值使用0填充

String类型演示：

select toFixedString('abc',5) , LENGTH (toFixedString('abc',5)) as LENGTH

SELECT 
    toFixedString('abc', 5), 
    LENGTH(toFixedString('abc', 5)) AS LENGTH

┌─toFixedString('abc', 5)─┬─LENGTH─┐
│ abc                     │      5 │
└─────────────────────────┴────────┘

1 rows in set. Elapsed: 0.003 sec.

UUID类型演示：

create table uuid_test (c1 UUID, c2 String) ENGINE = Memory

CREATE TABLE uuid_test
(
    `c1` UUID, 
    `c2` String
)
ENGINE = Memory


0 rows in set. Elapsed: 0.015 sec. 


:) insert into uuid_test select generateUUIDv4(),'t1'

INSERT INTO uuid_test SELECT 
    generateUUIDv4(), 
    't1'

Ok.

0 rows in set. Elapsed: 0.002 sec. 

:) 
:) insert into uuid_test(c2) values('t2')

INSERT INTO uuid_test (c2) VALUES

Ok.

1 rows in set. Elapsed: 0.002 sec. 

:) 
:) select * from uuid_test;

SELECT *
FROM uuid_test

┌───────────────────────────────────c1─┬─c2─┐
│ 33509345-71c0-499f-be3c-37fdc09f818f │ t1 │
└──────────────────────────────────────┴────┘
┌───────────────────────────────────c1─┬─c2─┐
│ 00000000-0000-0000-0000-000000000000 │ t2 │
└──────────────────────────────────────┴────┘

2 rows in set. Elapsed: 0.002 sec.

4.1.3 时间类型

名称	格式	备注
DateTime	2020-12-03 13:33:32	日时分秒，精确到秒
DateTime64	2020-12-03 13:33:32.00	日时分秒亚秒，精确到亚秒
Date	2020-12-03	日，精确到天

4.2 复合类型

4.2.1 Array

数组有两种定义方式：

1.常规方式array(T)

select array(1,2) as a, toTypeName(a)

SELECT 
    [1, 2] AS a, 
    toTypeName(a)

┌─a─────┬─toTypeName([1, 2])─┐
│ [1,2] │ Array(UInt8)       │
└───────┴────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

2.简写方式[T]

select [1,2] as a,toTypeName(a)

SELECT 
    [1, 2] AS a, 
    toTypeName(a)

┌─a─────┬─toTypeName([1, 2])─┐
│ [1,2] │ Array(UInt8)       │
└───────┴────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

注：同一数组内可以包含多种数据类型，但是各类型之间必须兼容。

select array(1,2,null) as a, toTypeName(a)

SELECT 
    [1, 2, NULL] AS a, 
    toTypeName(a)

┌─a──────────┬─toTypeName([1, 2, NULL])─┐
│ [1,2,NULL] │ Array(Nullable(UInt8))   │
└────────────┴──────────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

注：在定义表字段时，数组需要制定明确的元素类型

create table array_test_1 (c1 Array(String)) engine = Memory

4.2.2 Tuple

元组由1~n个元素组成，每个元素之间允许设置不同的数据类型，且彼此之间不要求兼容。

元组有两种定义方式：

1.常规方式tuple(T)

select tuple(1,'a',now()) AS x,toTypeName(x)

SELECT 
    (1, 'a', now()) AS x, 
    toTypeName(x)

┌─x─────────────────────────────┬─toTypeName(tuple(1, 'a', now()))─┐
│ (1,'a','2020-12-04 16:25:01') │ Tuple(UInt8, String, DateTime)   │
└───────────────────────────────┴──────────────────────────────────┘

1 rows in set. Elapsed: 0.005 sec.

2.简写方式（T）

select (1,2,0,null) as a, toTypeName(a)

SELECT 
    (1, 2, 0, NULL) AS a, 
    toTypeName(a)

┌─a────────────┬─toTypeName(tuple(1, 2, 0, NULL))──────────────┐
│ (1,2,0,NULL) │ Tuple(UInt8, UInt8, UInt8, Nullable(Nothing)) │
└──────────────┴───────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

注：在定义表字段时，元组需要制定明确的元素类型

create table tuple_test (c1 Tuple(String,Int8)) engine = Memory

4.2.3 Enum

枚举固定使用（String:Int）key/Value键值对的形式定义数据，所以Enum8和Enum16分别对应（String:Int8）和（String:Int16

create table enum_test ( c1 Enum8('ready' = 1,'start' = 2,'success' = 3,'error' = 4)) engine = Memory;

CREATE TABLE enum_test
(
    `c1` Enum8('ready' = 1, 'start' = 2, 'success' = 3, 'error' = 4)
)
ENGINE = Memory

0 rows in set. Elapsed: 0.001 sec. 

:) 
:) insert into enum_test values ('ready');

INSERT INTO enum_test VALUES

Ok.

1 rows in set. Elapsed: 0.002 sec. 

:) 
:) insert into enum_test values ('start');

INSERT INTO enum_test VALUES

Ok.

1 rows in set. Elapsed: 0.002 sec. 

:) 
:) select * from enum_test;

SELECT *
FROM enum_test

┌─c1────┐
│ ready │
└───────┘
┌─c1────┐
│ start │
└───────┘

2 rows in set. Elapsed: 0.002 sec.

# 不在范围抛出异常

insert into enum_test values ('stop');

4.2.4 Nested

嵌套表结构，一张数据表，可以定义任意多个嵌套类型字段，但是每个字段的嵌套层级只支持一级，即嵌套表内不能继续使用嵌套类型。

create table nested_test (name String,age UInt8,dept Nested (id UInt8,name String)) engine = Memory;

CREATE TABLE nested_test
(
    `name` String, 
    `age` UInt8, 
    `dept` Nested(
    id UInt8, 
    name String)
)
ENGINE = Memory

:) 
:)  into nested_test values ('zhang' , 18 , [10000],['测试部']);

0 rows in set. Elapsed: 0.001 sec. 

:) 
:) insert into nested_test values ('zhang',18,[10000,10001,10002],['研发部','技术部','技术支持']);

INSERT INTO nested_test VALUES

Ok.

1 rows in set. Elapsed: 0.002 sec. 

:) 
:) insert into nested_test values ('zhang' ,18, [10000,10001],['研发部','测试部']);

INSERT INTO nested_test VALUES

Ok.

1 rows in set. Elapsed: 0.001 sec. 

:) 
:) select * from nested_test;

SELECT *
FROM nested_test

┌─name──┬─age─┬─dept.id────┬─dept.name──────────────────────┐
│ zhang │  18 │ [16,17,18] │ ['研发部','技术部','技术支持'] │
└───────┴─────┴────────────┴────────────────────────────────┘
┌─name──┬─age─┬─dept.id─┬─dept.name───────────┐
│ zhang │  18 │ [16,17] │ ['研发部','测试部'] │
└───────┴─────┴─────────┴─────────────────────┘
┌─name──┬─age─┬─dept.id─┬─dept.name──┐
│ zhang │  18 │ [16]    │ ['测试部'] │
└───────┴─────┴─────────┴────────────┘

3 rows in set. Elapsed: 0.005 sec.

4.3 特殊类型

名称	备注
Nullable	辅助修饰符，，表示某个基础数据类型可以为Null
Domain	存储IPv4（UInt32）和IPv6

create table ipv4 (url String,ip IPv4) engine = Memory;

CREATE TABLE ipv4
(
    `url` String, 
    `ip` IPv4
)
ENGINE = Memory

0 rows in set. Elapsed: 0.001 sec. 

:) 
:) insert into ipv4 values ('www.oldba.cn','10.0.0.30')

INSERT INTO ipv4 VALUES

Ok.

1 rows in set. Elapsed: 0.001 sec. 

:) 
:) select url,ip,toTypeName(url),toTypeName(ip) from ipv4;

SELECT 
    url, 
    ip, 
    toTypeName(url), 
    toTypeName(ip)
FROM ipv4

┌─url──────────┬─────ip────┬─toTypeName(url)─┬─toTypeName(ip)─┐
│ www.oldba.cn │ 10.0.0.30 │ String          │ IPv4           │
└──────────────┴───────────┴─────────────────┴────────────────┘

1 rows in set. Elapsed: 0.003 sec.

五、定义数据表

5.1 数据库

数据库目前支持五种引擎：

Ordinary		默认引擎

Dictionary		字典引擎		此类数据库会自动为所有数据字典创建他们的数据表。

Memory			内存引擎		用于存放临时数据，此类数据库下的数据表只会停留在内存，重启数据丢失。

Lazy			日志引擎		此类数据库下只能使用log系列的表引擎。

Mysql			Mysql引擎		 此类数据库会自动拉取远端Mysql中的数据，并创建Mysql表引擎的数据表。


创建数据库：
create database if not exists testdb

数据路径下会有testdb文件夹；
metadata路径下会有testdb.sql文件。

查看数据库：

show databases

删除数据库：

drop database testdb

5.2 数据表

建表完整语法：

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]

(

name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],

name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],

...

INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,

INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2

) ENGINE = MergeTree()

数据表相关操作

建表举例
create table content (title String,url String,enenttime Datetime) engine = Memory;

复制同contnet一样的表结构为content_v1表
create table content_v1 as content engine = TinyLog						只有表结构
create table content_v1 engine = TinyLog as	select ...					只有表结构
create table content_v1 engine = TinyLog as	select * from content		表结构+数据

查看表定义结构：
show create table content			原样输出		
desc test							表格显示

删除表：
drop table content				物理删除，立即释放空间；删除表数据+表空间
truncate table content			物理删除，立即释放空间；删除表数据，保留表结构
delete table content [FROM]		逻辑删除，删除的数据做标记；逐行删除表数据，保留表空间

5.3 默认值表达式

表字段支持三种默认值表达式：

		DEFAULT

		MATERIALIZED

		ALIAS

三者区别：
		数据写入时，只有default类型的字段可以出现在INSERT语句中。
		数据查询时，只有default类型的字段可以通过在SELECT*返回。
		数据存储时，只有default和materialized类型的字段才支持持久化。

表字段被定义了默认值，不再强制要求定义数据类型，CK根据默认值类型推断；

如果同时对表字段定义了默认值表达是和数据类型，则按照定义的数据类型为主。

示例演示：
CREATE TABLE test ( id String, c1 DEFAULT 1000,  c2 String DEFAULT c1) ENGINE = TinyLog

INSERT INTO test(id) values('A000')

SELECT 
    c1, 
    c2, 
    toTypeName(c1), 
    toTypeName(c2)
FROM test

┌───c1─┬─c2───┬─toTypeName(c1)─┬─toTypeName(c2)─┐
│ 1000 │ 1000 │ UInt16         │ String         │
└──────┴──────┴────────────────┴────────────────┘

c1根据字段默认值推断为无符号的整数
c2根据定义的类型判断为字符型

5.4 数据表的基本操作

追加新字段
ALTER TABLE xxx add column item String DEFAULT 'mac'		默认最后一行加
ALTER TABLE xxx add column app  String AFTER ID				在ID列后面加新字段

修改数据类型
ALTER TABLE content MODIFY COLUMN ip IPv4

修改备注
ALTER TABLE content comment column ID '主键列'

删除已有字段
ALTER TABLE content DROP COLUMN [IF EXISTS] name

移动数据表
RENAME TABLE default.content TO test.test

清空数据表
drop table content				物理删除，立即释放空间；删除表数据+表空间
truncate table content			物理删除，立即释放空间；删除表数据，保留表结构
delete table content [FROM]		逻辑删除，删除的数据做标记；逐行删除表数据，保留表空间

5.5 数据分区的基本操作

5.5.1 查询分区表

SELECT 
    table, 
    partition_id, 
    name, 
    path
FROM system.parts
WHERE table = 'test'

┌─table─┬─partition_id─┬─name─────────┬─path───────────────────────────────────────────────┐
│ test  │ 202012       │ 202012_1_3_1 │ /data/clickhouse/data/data/test/test/202012_1_3_1/ │
│ test  │ 210506       │ 210506_4_4_0 │ /data/clickhouse/data/data/test/test/210506_4_4_0/ │
└───────┴──────────────┴──────────────┴────────────────────────────────────────────────────┘

5.5.2 删除指定分区

语法：
alter table tb_name drop partition partition_id

alter table test drop partition 201905

5.5.3 复制分区数据

ClickHosue支持将A表的分区数据复制到B表，这项特性可以用于快速写入、多表间数据同步和备份等场景。

语法：

ALTER TABLE B REPALCE PARTITION partition_expr FROM A

满足条件才可相互复制：

		两张表需要拥有相同的分区键；

		它们的表结构完全相同。


假设test_v1和test_v2表分区键和表结构完全相同

CREATE TABLE test.test_v1 (`id` String,url String,EventTime Date) ENGINE = MergeTree() PARTITION BY toYYYYMM(EventTime) ORDER BY id 

insert into test_v1 values ('A006-V1','www.v1.com','2019-08-05'),('A007-v1','www.v1.con','2019-08-20')

select * from test_v1

┌─id──────┬─url────────┬──EventTime─┐
│ A006-V1 │ www.v1.com │ 2019-08-05 │
│ A007-v1 │ www.v1.con │ 2019-08-20 │
└─────────┴────────────┴────────────┘

ALTER TABLE test_v2 REPLACE PARTITION 201908 FROM test_v1

SELECT * FROM test_v2 ORDER BY EventTime ASC

┌─id──────┬─url────────┬──EventTime─┐
│ A006-V1 │ www.v1.com │ 2019-08-05 │
│ A007-v1 │ www.v1.con │ 2019-08-20 │
└─────────┴────────────┴────────────┘

5.5.4 重置分区数据

如果数据表的某一列数据不正确，需要将其初始化。

语法:
ALTER TABLE tb_name CLEAR COLUMN cloumn_name IN PARTITION partition_expr

示例演示：
ALTER TABLE test_v2 CLEAR COLUMN url     IN PARTITION 201908

SELECT * FROM test_v2 ORDER BY EventTime ASC

┌─id──────┬─url─┬──EventTime─┐
│ A006-V1 │     │ 2019-08-05 │
│ A007-v1 │     │ 2019-08-20 │
└─────────┴─────┴────────────┘

5.5.5 卸载与装载分区

表分区可以通过DETACH语句卸载，分区被卸载后，它的物理数据并没有删除，而是被转移到了当前数据表目录的detached子目录下。装载是卸载的反过程。常用于分区数据的迁移和备份场景。

卸载语法：

ALTER TABLE tb_name DETACH PARTITION partition_expr

示例演示：
ALTER TABLE test_v2 DETACH PARTITION 201908

SELECT * FROM test_v2 ORDER BY EventTime ASC

# ll /data/clickhouse/data/data/test/test_v2/
drwxr-x--- 3 clickhouse clickhouse 26 Dec  8 14:23 detached
-rw-r----- 1 clickhouse clickhouse  1 Dec  7 17:15 format_version.txt

# ll /data/clickhouse/data/data/test/test_v2/detached/
drwxr-x--- 2 clickhouse clickhouse 203 Dec  8 14:23 201908_2_2_0

# 此时一旦分区被移动到detached的目录下，就代表已经脱离了ClickHouse的管理。ClickHouse并不会主动清理这些文件。



装载语法：
ALTER TABLE tb_name ATTACH PARTITION partition_expr

示例演示：
ALTER TABLE test_v2 ATTACH PARTITION 201908

SELECT * FROM test_v2 ORDER BY EventTime ASC

┌─id──────┬─url─┬──EventTime─┐
│ A006-V1 │     │ 2019-08-05 │
│ A007-v1 │     │ 2019-08-20 │
└─────────┴─────┴────────────┘

# ll /data/clickhouse/data/data/test/test_v2/detached/
total 0

# ll /data/clickhouse/data/data/test/test_v2/
total 4
drwxr-x--- 2 clickhouse clickhouse 203 Dec  8 14:33 201908_3_3_0
drwxr-x--- 2 clickhouse clickhouse   6 Dec  8 14:33 detached
-rw-r----- 1 clickhouse clickhouse   1 Dec  7 17:15 format_version.txt

5.6 分布式DDL执行

DDL：数据定义语言。

CREATE、ALTER、DROP、RENAME、及TRUNCATE等DDL语句都支持分布式。

DDL语法:

CREATE TABLE tb_name ON CLUSTER ck_cluster (

		id String,

		url String,

		eventtime Date

)  ENGINE = MergeTree  ()

PARTITION BY toYYYYMM(eventtime)

ORDER BY id

5.7 数据的写入

INSERT支持三种范式写入。

第一种使用VALUES格式的常规语法：

INSERT INTO table [(c1,c2,c3...)]  VALUES (V11,V12,V13...),(V21,V22,V23)

示例演示：

INSERT INTO test_v2 VALUES ('A0011','www.xxx.com','2020-12-08'),('A0012','www.nnn.com','2020-11-08'),('A0013','www.qqq.com','2020-12-09')


第二种使用指定格式语法：

INSERT INTO table [(c1,c2,c3...)] FORMAT format_name data_set

示例演示：

INSERT INTO test_v2 FORMAT CSV 
'A0011','www.xxx.com','2020-12-08'
'A0012','www.nnn.com','2020-11-08'
'A0013','www.qqq.com','2020-12-09'


第三种使用SELECT子句形式的语法：

INSERT INTO table [(c1,c2,c3...)] SELECT ...

示例演示：

INSERT INTO test_v2 SELECT 'A0020','www.ooo.com','now()'

5.8 数据的删除与修改

ClickHouse提供了DELETE和UPDATE的能力，这类操作称Mutation查询。（是ALTER语句的变种）

适用于批量数据的修改和删除

不支持事务，一旦提交被执行，无法回滚；

Mutation语句的执行是一个异步的后台过程，语句被提交之后就会立即返回，但是具体逻辑并不一定执行完毕，需要通过system.mutations系统表查询。

DELETE语句完整语法：

ALTER TABLE table DELETE WHERE filter_expr

示例演示：

ALTER TABLE test_v2 DELETE WHERE id = 'A006-V1'			异步重操作。

进入目录发现，再执行了DETELE操作之后数据目录发生了变化，每个原有的目录后末尾都加了_6的后缀，此外，目录下还多了Mmutation_6.txt的文件夹。
# ll /data/clickhouse/data/data/test/test_v2/
total 8
drwxr-x--- 2 clickhouse clickhouse 234 Dec  8 15:27 201908_3_3_0_6
drwxr-x--- 2 clickhouse clickhouse 234 Dec  8 15:27 202011_5_5_0_6
drwxr-x--- 2 clickhouse clickhouse 234 Dec  8 15:27 202012_4_4_0_6
drwxr-x--- 2 clickhouse clickhouse   6 Dec  8 14:33 detached
-rw-r----- 1 clickhouse clickhouse   1 Dec  7 17:15 format_version.txt
-rw-r----- 1 clickhouse clickhouse  92 Dec  8 15:27 mutation_6.txt

# cat mutation_6.txt 
format version: 1
create time: 2020-12-08 15:27:39
commands: DELETE WHERE id = 'A006-V1' 


SELECT  database,table,mutation_id,block_numbers.number AS num, is_done FROM system.mutations

┌─database─┬─table───┬─mutation_id────┬─num─┬─is_done─┐
│ test     │ test_v2 │ mutation_6.txt │ [6] │       1 │
└──────────┴─────────┴────────────────┴─────┴─────────┘

每执行一条ALTER TABLE xxx DELETE FROM 语句，都会在mutation系统表中生成一条对应的执行计划，当is_done为1时，代表执行完毕。同时也会在数据目录下会以mutation_id为名生成与之对应的日志文件用于记录相关信息。

删除的过程是以数据表的每个分区目录为单位，将所有目录重写为新的目录，新目录的命名规则在原有名称之上加上system.mutations.block_numbers.number,数据在重写的过程中会将需要删除的数据去掉。就数据不会立即删除，标记为非激活状态（active 0）。等到MergeTree引擎的下一次合并动作触发时，这些非激活目录才会被真正从物理上删除。

更多精彩内容，请关注微信公众号获取
在这里插入图片描述

最后

以上就是舒心彩虹为你收集整理的2.ClickHouse数据类型和定义数据表四、数据类型五、定义数据表的全部内容，希望文章能够帮你解决2.ClickHouse数据类型和定义数据表四、数据类型五、定义数据表所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。

本文分类：数据库
浏览次数：54 次浏览
发布日期：2024-01-16 02:30:49
本文链接：https://www.kaopuke.com/article/k-p-k_13_u_23_ogf4_14_jk0.html

2.ClickHouse数据类型和定义数据表四、数据类型五、定义数据表

概述

四、数据类型

4.1 基础类型

4.1.1 数值类型

4.1.2 字符串类型

4.1.3 时间类型

4.2 复合类型

4.2.1 Array

4.2.2 Tuple

4.2.3 Enum

4.2.4 Nested

4.3 特殊类型

五、定义数据表

5.1 数据库

5.2 数据表

5.3 默认值表达式

5.4 数据表的基本操作

5.5 数据分区的基本操作

5.5.1 查询分区表

5.5.2 删除指定分区

5.5.3 复制分区数据

5.5.4 重置分区数据

5.5.5 卸载与装载分区

5.6 分布式DDL执行

5.7 数据的写入

5.8 数据的删除与修改

最后

评论列表共有 0 条评论

发表评论取消回复

2.ClickHouse数据类型和定义数据表四、数据类型五、定义数据表

概述

四、数据类型

4.1 基础类型

4.1.1 数值类型

4.1.2 字符串类型

4.1.3 时间类型

4.2 复合类型

4.2.1 Array

4.2.2 Tuple

4.2.3 Enum

4.2.4 Nested

4.3 特殊类型

五、定义数据表

5.1 数据库

5.2 数据表

5.3 默认值表达式

5.4 数据表的基本操作

5.5 数据分区的基本操作

5.5.1 查询分区表

5.5.2 删除指定分区

5.5.3 复制分区数据

5.5.4 重置分区数据

5.5.5 卸载与装载分区

5.6 分布式DDL执行

5.7 数据的写入

5.8 数据的删除与修改

最后

相关文章

评论列表共有 0 条评论

发表评论 取消回复

发表评论取消回复