专栏首页别先生Clickhouse基础语法、数据类型、数据表引擎学习

Clickhouse基础语法、数据类型、数据表引擎学习

1、Clickhouse创建数据库的语法,如下所示:

1 CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster] [ENGINE = engine(...)]

使用案例,如下所示:

 1 master :) CREATE DATABASE IF NOT EXISTS gab_db;
 2 
 3 CREATE DATABASE IF NOT EXISTS gab_db
 4 
 5 Ok.
 6 
 7 0 rows in set. Elapsed: 0.013 sec. 
 8 
 9 master :) show databases;
10 
11 SHOW DATABASES
12 
13 ┌─name───────────────────────────┐
14 │ _temporary_and_external_tables │
15 │ default                        │
16 │ gab_db                         │
17 │ system                         │
18 └────────────────────────────────┘
19 
20 4 rows in set. Elapsed: 0.012 sec. 
21 
22 master :) 

2、默认情况下,ClickHouse使用的是原生的数据库引擎Ordinary(在此数据库下可以使用任意类型的表引擎,在绝大多数情况下都只需使用默认的数据库引擎)。当然也可以使用Lazy引擎和MySQL引擎,比如使用MySQL引擎,可以直接在ClickHouse中操作MySQL对应数据库中的表。假设MySQL中存在一个名为Clickhouse的数据库,可以使用下面的方式连接MySQL数据库。

 1 -- --------------------------语法-----------------------------------
 2 CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
 3 ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')
 4 
 5 -- --------------------------示例------------------------------------
 6 CREATE DATABASE mysql_db ENGINE = MySQL('192.168.0.109:3306', 'clickhouse', 'root', '123456');
 7 
 8 
 9 -- ---------------------------操作-----------------------------------
10 master :) CREATE DATABASE mysql_db ENGINE = MySQL('192.168.0.109:3306', 'clickhouse', 'root', '123456');
11 
12 CREATE DATABASE mysql_db
13 ENGINE = MySQL('192.168.0.109:3306', 'clickhouse', 'root', '123456')
14 
15 Ok.
16 
17 0 rows in set. Elapsed: 0.011 sec. 
18 
19 master :) show databases;
20 
21 SHOW DATABASES
22 
23 ┌─name───────────────────────────┐
24 │ _temporary_and_external_tables │
25 │ default                        │
26 │ gab_db                         │
27 │ mysql_db                       │
28 │ system                         │
29 └────────────────────────────────┘
30 
31 5 rows in set. Elapsed: 0.006 sec. 
32 
33 master :) use mysql_db;
34 
35 USE mysql_db
36 
37 Ok.
38 
39 0 rows in set. Elapsed: 0.002 sec. 
40 
41 master :) show tables;
42 
43 SHOW TABLES
44 
45 Ok.
46 
47 0 rows in set. Elapsed: 0.012 sec. 
48 
49 master :) show tables;
50 
51 SHOW TABLES
52 
53 ┌─name────┐
54 │ user_db │
55 └─────────┘
56 
57 1 rows in set. Elapsed: 0.011 sec. 
58 
59 master :) select * from user_db;
60 
61 SELECT *
62 FROM user_db
63 
64 ┌─id─┬─name─────┬─age─┬─address──────┐
65 │  1 │ zhangsan │  22 │ 河南省新乡市 │
66 └────┴──────────┴─────┴──────────────┘
67 
68 1 rows in set. Elapsed: 0.020 sec. 
69 
70 master :) 

如果创建的时候报下面的错误,是因为权限的问题,执行下面的命令,解决问题即可。

 1 master :) CREATE DATABASE mysql_db ENGINE = MySQL('192.168.0.109:3306', 'clickhouse', 'root', '123456');
 2 
 3 CREATE DATABASE mysql_db
 4 ENGINE = MySQL('192.168.0.109:3306', 'clickhouse', 'root', '123456')
 5 
 6 
 7 Received exception from server (version 20.8.3):
 8 Code: 501. DB::Exception: Received from localhost:9000. DB::Exception: Cannot create MySQL database, because Poco::Exception. Code: 1000, e.code() = 1045, e.displayText() = mysqlxx::ConnectionFailed: Access denied for user 'root'@'192.168.0.109' (using password: YES) ((nullptr):3306), 
 9 
10 0 rows in set. Elapsed: 0.042 sec. 

执行下面两行命令,解决问题即可。

1 grant all privileges on *.* to root@'%' identified by '123456'; 
2 
3 FLUSH PRIVILEGES;

3、Clickhouse创建数据表的,语法如下所示:

1 CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
2 (
3     name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [compression_codec] [TTL expr1],
4     name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [compression_codec] [TTL expr2],
5     ...
6 ) ENGINE = engine

使用案例,如下所示:

 1 master :) 
 2 master :) show tables;
 3 
 4 SHOW TABLES
 5 
 6 Ok.
 7 
 8 0 rows in set. Elapsed: 0.008 sec. 
 9 
10 master :) create table user_db(
11 :-] id Int32,
12 :-] name String
13 :-] )engine=Memory;
14 
15 CREATE TABLE user_db
16 (
17     `id` Int32,
18     `name` String
19 )
20 ENGINE = Memory
21 
22 Ok.
23 
24 0 rows in set. Elapsed: 0.003 sec. 
25 
26 master :) show tables;
27 
28 SHOW TABLES
29 
30 ┌─name────┐
31 │ user_db │
32 └─────────┘
33 
34 1 rows in set. Elapsed: 0.007 sec. 
35 
36 master :) 

上面命令是创建了一张内存表,即使用的是Memory引擎。表引擎决定了数据表的特性,也决定了数据将会被如何存储及加载。Memory引擎是ClickHouse最简单的表引擎,数据只会被保存在内存中,在服务重启时数据会丢失。

4、Clickhouse的数据类型,在创建数据表的时候指定字段的数据类型,数据类型在使用的时候是区分大小写的,所以在定义字段的时候一定注意数据类型的书写。

4.1、整数数据类型Int Ranges ,Clickhouse直接使用Int8、Int16、Int32、Int64指代4种大小的Int类型,其末尾的数据正好表明了占用字节的大小(1个节点 = 8位)。

名称

大小(字节)

范围

普遍观念

Int8

1个字节

-128到127

Tinyint

Int16

2个字节

-32768到32767

Smallint

Int32

4个字节

-2147483648到2147483647

int

Int64

8个字节

-9223372036854775808到9223372036854775807

Bigint

具体对应关系,如下所示:

Int8 - [-128 : 127]
Int16 - [-32768 : 32767]
Int32 - [-2147483648 : 2147483647]
Int64 - [-9223372036854775808 : 9223372036854775807]

整数数据类型Uint Ranges,Clickhouse支持无符号的整数,使用前缀U表示,都表示的是正数,即无负数表示。

UInt8 - [0 : 255]
UInt16 - [0 : 65535]
UInt32 - [0 : 4294967295]
UInt64 - [0 : 18446744073709551615]

使用案例,如下所示:

 1 master :) create table tb_name(
 2 :-] id UInt8 , -- 指定数据类型
 3 :-] age UInt8 , -- 指定数据类型
 4 :-] flow Int64 -- 指定数据类型
 5 :-] ) engine=Log ; -- 指定表引擎
 6 
 7 CREATE TABLE tb_name
 8 (
 9     `id` UInt8,
10     `age` UInt8,
11     `flow` Int64
12 )
13 ENGINE = Log
14 
15 Ok.
16 
17 0 rows in set. Elapsed: 0.004 sec. 
18 
19 master :) 

4.2、小数数据类型。

Float32 - float,注意:和我们之前的认知是一样的,这种数据类型在数据特别精准的情况下可能出现数据精度问题。
Float64 - double
Decimal(P,S) Decimal32(s) Decimal64(s) ) Decimal128(s)。

名称

大小(字节)

有效精度(位数)

普遍概念

Float32

4个字节

7位

Float

Float64

8个字节

16位

Double

Select 8.0/0 INF表示的是正无穷,Select -8.0/0 INF表示负无穷,Select 0/0 NAN表示非数字。

使用案例,如下所示:

 1 master :) create table tb_user(
 2 :-] uid Int8 ,
 3 :-] sal Decimal32(2)-- 指定2位小数点 4 :-] ) engine=TinyLog ;
 5 
 6 CREATE TABLE tb_user
 7 (
 8     `uid` Int8,
 9     `sal` Decimal32(2)
10 )
11 ENGINE = TinyLog
12 
13 Ok.
14 
15 0 rows in set. Elapsed: 0.018 sec. 
16 
17 master :) insert into tb_user values(1,10000),(2,30000),(3,2000) ;
18 
19 INSERT INTO tb_user VALUES
20 
21 Ok.
22 
23 3 rows in set. Elapsed: 0.053 sec. 
24 
25 master :) select * from tb_user;
26 
27 SELECT *
28 FROM tb_user
29 
30 ┌─uid─┬──────sal─┐
31 │   1 │ 10000.00 │
32 │   2 │ 30000.00 │
33 │   3 │  2000.00 │
34 └─────┴──────────┘
35 
36 3 rows in set. Elapsed: 0.009 sec. 
37 
38 master :) 

  Decimal如果要求高精度的数值运算,则需要使用定点数。Clickhouse提供了Decimal32、Decimal64和Decimal128三种精度的定点数。可以通过两种行式表明定点,简写方式有Decimal32(S)、Decimal64(S)、Decimal128(S)三种,原生方式为Decimal(P,S),其中,P代表了精度,决定总位数(整数部分+ 小数部分),取值范围是1~38,S代表规模,决定小数位,取值范围是0~P。

名称

等效声明

范围

Decimal32(S)

Decimal(1 ~ 9,S)

-1 * 10^(9-S) 到 1 * 10^(9-S)

Decimal64(S)

Decimal(10 ~ 18,S)

-1 * 10^(18-S) 到 1 * 10^(18-S)

Decimal128(S)

Decimal(19 ~ 38,S)

-1 * 10^(38-S) 到 1 * 10^(38-S)

在使用两个不同精度的定点数进行四则运算的时候,它们的小数点,位数S会发生变化。

  在进行加法运算的时候,S取最大值,例如下面的查询toDecimal64(2,4)与toDecimal32(2,2)相加后S=4;   在进行减法运算的时候,S取最大值,例如下面的查询toDecimal(2,4)与toDecimal32(2,2)相减后S=4;   在进行乘法运算的时候,S取最大值,例如下面的查询toDecimal(2,4)与toDecimal32(2,2)相乘后S=4+2;   在进行除法运算的时候,S取最大值,例如下面的查询toDecimal(2,4)与toDecimal32(2,2)相除后S=4;

但是要保证被除数的S大于除数的S,否则会报错。

4.3、boolean类型,Clickhouse中没有对布尔类型进行支持,可以使用0 和1 来代表布尔数据类型。

4.4、字符串数据类型,字符串类型可以细分为String、FixedString、UUID三类,从命名来看彷佛是由一款数据库提供的类型,反而更像一门编程语言的设计,Clickhouse语法具备编程语言的特征(数据+运算)。

  a)、String,字符串由String定义,长度不限,因此在使用String的时候无须声明大小。它完全代替了传统意义上数据库的Varchar、Text、Clob和Blob等字符类型。String类型不限定字符集,因为它根本就没有这个概念,所以可以将任意编码的字符串存入其中。但是为了程序的规范性和可维护性,在同一套程序中应该遵守使用统一的编码,例如,统一使用UTF-8编码就是一种很好的约定。所以在对数据操作的时候我们不再需要去关注编码和乱码问题。

  b)、FixedString,类型和传统意义上的Char类型有些类似,对于一些字符有明确长度的场合,可以使用固定长度的字符串。定长字符串通过FixedString(N)声明,其中N表示字符串长度,但是与Char不同的是,FixedString使用Null字节填充末尾字符,而Char通常使用空格填充。

String                  -- 字符串数据类型   一般情况下使用String;类型就可以
FixedString(n)          -- 固定长度的数据类型

使用案例,如下所示:

 1 master :) drop table if exists tb_stu ;
 2 
 3 DROP TABLE IF EXISTS tb_stu
 4 
 5 Ok.
 6 
 7 0 rows in set. Elapsed: 0.013 sec. 
 8 
 9 master :) create table if not exists tb_stu(
10 :-] sid FixedString(8) ,
11 :-] name String ,
12 :-] address String
13 :-] ) engine = TinyLog ;
14 
15 CREATE TABLE IF NOT EXISTS tb_stu
16 (
17     `sid` FixedString(8),
18     `name` String,
19     `address` String
20 )
21 ENGINE = TinyLog
22 
23 Ok.
24 
25 0 rows in set. Elapsed: 0.013 sec. 
26 
27 master :)  
28 master :) insert into tb_stu values('aaaaaaaa' , 'HANGGE' ,'ZhongNanHai') ;
29 
30 INSERT INTO tb_stu VALUES
31 
32 Ok.
33 
34 1 rows in set. Elapsed: 0.011 sec. 
35 
36 master :) select * from tb_stu;
37 
38 SELECT *
39 FROM tb_stu
40 
41 ┌─sid──────┬─name───┬─address─────┐
42 │ aaaaaaaa │ HANGGE │ ZhongNanHai │
43 └──────────┴────────┴─────────────┘
44 
45 1 rows in set. Elapsed: 0.008 sec. 
46 
47 master :) 

4.5、UUID随机的一串字符串。

 1 master :) drop table if exists tb_uuid ;
 2 
 3 DROP TABLE IF EXISTS tb_uuid
 4 
 5 Ok.
 6 
 7 0 rows in set. Elapsed: 0.002 sec. 
 8 
 9 master :) create table tb_uuid(
10 :-] id UUID ,
11 :-] name String
12 :-] ) engine = TinyLog ;
13 
14 CREATE TABLE tb_uuid
15 (
16     `id` UUID,
17     `name` String
18 )
19 ENGINE = TinyLog
20 
21 Ok.
22 
23 0 rows in set. Elapsed: 0.003 sec. 
24 
25 master :)  
26 master :) insert into tb_uuid (name) values ('zss') , ('lss') ,('daa') ;
27 
28 INSERT INTO tb_uuid (name) VALUES
29 
30 Ok.
31 
32 3 rows in set. Elapsed: 0.004 sec. 
33 
34 master :) insert into tb_uuid select generateUUIDv4() , 'HANGGE' ;
35 
36 INSERT INTO tb_uuid SELECT 
37     generateUUIDv4(),
38     'HANGGE'
39 
40 Ok.
41 
42 0 rows in set. Elapsed: 0.010 sec. 
43 
44 master :) select * from tb_uuid;
45 
46 SELECT *
47 FROM tb_uuid
48 
49 ┌───────────────────────────────────id─┬─name───┐
50 │ 00000000-0000-0000-0000-000000000000 │ zss    │
51 │ 00000000-0000-0000-0000-000000000000 │ lss    │
52 │ 00000000-0000-0000-0000-000000000000 │ daa    │
53 │ c712b9e8-1c6c-4614-a836-f85da3de62aa │ HANGGE │
54 └──────────────────────────────────────┴────────┘
55 
56 4 rows in set. Elapsed: 0.008 sec. 
57 
58 master :) 

4.6、枚举类型,包括 Enum8 和 Enum16 类型。Enum 保存 'string'= integer 的对应关系。Enum8 用 'String'= Int8 对描述。Enum16 用 'String'= Int16 对描述。

用法演示,创建一个带有一个枚举 Enum8('hello' = 1, 'world' = 2) 类型的列:

 1 master :) CREATE TABLE t_enum
 2 :-] (
 3 :-]     x Enum8('hello' = 1, 'world' = 2)
 4 :-] )
 5 :-] ENGINE = TinyLog;
 6 
 7 CREATE TABLE t_enum
 8 (
 9     `x` Enum8('hello' = 1, 'world' = 2)
10 )
11 ENGINE = TinyLog
12 
13 Ok.
14 
15 0 rows in set. Elapsed: 0.008 sec. 
16 
17 master :) 
18 
19 -- 这个 x 列只能存储类型定义中列出的值:'hello'或'world'。如果尝试保存任何其他值,ClickHouse 抛出异常。
20 master :) INSERT INTO t_enum VALUES ('hello'), ('world'), ('hello')
21 :-] ;
22 
23 INSERT INTO t_enum VALUES
24 
25 Ok.
26 
27 3 rows in set. Elapsed: 0.006 sec. 
28 
29 master :) INSERT INTO t_enum VALUES;
30 
31 INSERT INTO t_enum VALUES
32 
33 Ok.
34 
35 0 rows in set. Elapsed: 0.004 sec. 
36 
37 master :) insert into t_enum values('a')
38 :-] ;
39 
40 INSERT INTO t_enum VALUES
41 
42 
43 Exception on client:
44 Code: 36. DB::Exception: Unknown element 'a' for type Enum8('hello' = 1, 'world' = 2)
45 
46 Connecting to localhost:9000 as user default.
47 Connected to ClickHouse server version 20.8.3 revision 54438.
48 
49 master :) 
50 
51 
52 -- 从表中查询数据时,ClickHouse 从 Enum 中输出字符串值。
53 master :) select * from t_enum;
54 
55 SELECT *
56 FROM t_enum
57 
58 ┌─x─────┐
59 │ hello │
60 │ world │
61 │ hello │
62 └───────┘
63 
64 3 rows in set. Elapsed: 0.009 sec. 
65 
66 master :) 
67 
68 
69 -- 如果需要看到对应行的数值,则必须将 Enum 值转换为整数类型。cast 强制数据类型转换... 将枚举类型字段转换成Int8数据类型
70 master :) SELECT CAST(x, 'Int8') FROM t_enum
71 :-] ;
72 
73 SELECT CAST(x, 'Int8')
74 FROM t_enum
75 
76 ┌─CAST(x, 'Int8')─┐
77 │               1 │
78 │               2 │
79 │               1 │
80 └─────────────────┘
81 
82 3 rows in set. Elapsed: 0.006 sec. 
83 
84 master :) 

4.7、数组数据类型。Array(T):由 T 类型元素组成的数组。其中,T 可以是任意类型,包含数组类型。 但不推荐使用多维数组,ClickHouse 对多维数组的支持有限。

例如,不能在 MergeTree 表中存储多维数组。

Clickhouse支持数组这种复合数据类型,并且数据操作在数据分析中起到非常便利的效果,数组的定义方式有两种:
第一种是array(T),泛型的方式。
第二种是直接插入数据的行式[e1,e2,e3.....],我们在这里要求数组中的数据类型是一致的,数组是强数据类型。

使用案例,如下所示:

 1 master :) SELECT array(1, 2) AS x, toTypeName(x);
 2 
 3 SELECT 
 4     [1, 2] AS x,
 5     toTypeName(x)
 6 
 7 ┌─x─────┬─toTypeName(array(1, 2))─┐
 8 │ [1,2] │ Array(UInt8)            │
 9 └───────┴─────────────────────────┘
10 
11 1 rows in set. Elapsed: 0.022 sec. 
12 
13 master :) SELECT [1, 2] AS x, toTypeName(x);
14 
15 SELECT 
16     [1, 2] AS x,
17     toTypeName(x)
18 
19 ┌─x─────┬─toTypeName([1, 2])─┐
20 │ [1,2] │ Array(UInt8)       │
21 └───────┴────────────────────┘
22 
23 1 rows in set. Elapsed: 0.009 sec. 
24 
25 
26 -- 数组的取值,从1开始取值,中括号里面从1开始,就可以取出数组的第一个值。
27 
28 master :) SELECT ['a', 'b', 'c'][1]
29 :-] ;
30 
31 SELECT ['a', 'b', 'c'][1]
32 
33 ┌─arrayElement(['a', 'b', 'c'], 1)─┐
34 │ a                                │
35 └──────────────────────────────────┘
36 
37 1 rows in set. Elapsed: 0.006 sec. 
38 
39 master :)  select array('a','b','c')[2];
40 
41 SELECT ['a', 'b', 'c'][2]
42 
43 ┌─arrayElement(array('a', 'b', 'c'), 2)─┐
44 │ b                                     │
45 └───────────────────────────────────────┘
46 
47 1 rows in set. Elapsed: 0.007 sec. 
48 
49 master :) 

如果要创建Array数组类型的字段,需要使用()来指定泛型,比如Array(String),如下所示:

 1 master :) 
 2 master :) CREATE TABLE tb_array
 3 :-] (
 4 :-]     `id` UInt8,
 5 :-] `name` String,
 6 :-] `hobby` Array(String)
 7 :-] )ENGINE = Log;
 8 
 9 CREATE TABLE tb_array
10 (
11     `id` UInt8,
12     `name` String,
13     `hobby` Array(String)
14 )
15 ENGINE = Log
16 
17 Ok.
18 
19 0 rows in set. Elapsed: 0.029 sec. 
20 
21 master :) desc tb_array;
22 
23 DESCRIBE TABLE tb_array
24 
25 ┌─name──┬─type──────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
26 │ id    │ UInt8         │              │                    │         │                  │                │
27 │ name  │ String        │              │                    │         │                  │                │
28 │ hobby │ Array(String) │              │                    │         │                  │                │
29 └───────┴───────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
30 
31 3 rows in set. Elapsed: 0.014 sec. 
32 
33 master :) insert into tb_array values(1,'张三',['抽烟','喝酒','烫头']),(2,'岳岳',['说相声','唱歌']);
34 
35 INSERT INTO tb_array VALUES
36 
37 Ok.
38 
39 2 rows in set. Elapsed: 0.045 sec. 
40 
41 master :) 
42 master :) select * from tb_array;
43 
44 SELECT *
45 FROM tb_array
46 
47 ┌─id─┬─name─┬─hobby──────────────────┐
48 │  1 │ 张三 │ ['抽烟','喝酒','烫头'] │
49 │  2 │ 岳岳 │ ['说相声','唱歌']      │
50 └────┴──────┴────────────────────────┘
51 
52 2 rows in set. Elapsed: 0.071 sec. 
53 
54 master :) 

注意:根据数组创建的数据块数,会根据服务器的核数来决定创建几个块,服务器的核数决定了创建的块数。

 1 总核数 = 物理CPU个数 * 每颗物理CPU的核数。
 2 总逻辑CPU数 = 物理CPU个数 * 每颗物理CPU的核数 * 超线程数。
 3 
 4 查看物理CPU个数。
 5 cat /proc/cpuinfo | grep "physical id" | sort | uniq | wc -l
 6 
 7 查看每个物理CPU中的core的个数即核数。
 8 cat /proc/cpuinfo | grep "cpu cores" |uniq
 9 
10 查看逻辑CPU的个数
11 cat /proc/cpuinfo | grep "processor" | wc -l

数组居然还支持高阶函数,类似Java的lambda表达式,也是很强大的东西了,如下所示:

注意:Clickhouse的字符串不能使用双引号引起来,只能使用单引号引起来。

 1 master :) 
 2 master :) select arrayMap(e -> concat(e,'lambda'),hobby) from tb_array;
 3 
 4 SELECT arrayMap(e -> concat(e, 'lambda'), hobby)
 5 FROM tb_array
 6 
 7 ┌─arrayMap(lambda(tuple(e), concat(e, 'lambda')), hobby)─┐
 8 │ ['抽烟lambda','喝酒lambda','烫头lambda']               │
 9 │ ['说相声lambda','唱歌lambda']                          │
10 └────────────────────────────────────────────────────────┘
11 
12 2 rows in set. Elapsed: 0.048 sec. 
13 
14 master :) 

4.8、Nested数据类型,Nested(name1 Type1, Name2 Type2, …)。

  Nested是一种嵌套表结构,一张数据表,可以定义任意多个嵌套类型字段,但是每个字段的嵌套层级只支持一级,即嵌套表内不能继续使用嵌套类型,对于简单场景的层级关系或者关联关系,使用嵌套类型也是一个不错的选择。

Nested嵌套类型本质是一种多维数组的结构,嵌套表中的每个字段的都是一个数组,并且行与行之间数组的长度无须对齐,但是需要注意的是,在同一行数据内每个数组字段的长度必须相等。

 1 master :) 
 2 master :) CREATE TABLE tb_nested
 3 :-] (
 4 :-]     `id` String,
 5 :-]     `user` Nested(    uid Int,     name String,     age UInt8)
 6 :-] )ENGINE = TinyLog;
 7 
 8 CREATE TABLE tb_nested
 9 (
10     `id` String,
11     `user` Nested(    uid Int,     name String,     age UInt8)
12 )
13 ENGINE = TinyLog
14 
15 Ok.
16 
17 0 rows in set. Elapsed: 0.010 sec. 
18 
19 master :) desc tb_nested ;
20 
21 DESCRIBE TABLE tb_nested
22 
23 ┌─name──────┬─type──────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
24 │ id        │ String        │              │                    │         │                  │                │
25 │ user.uid  │ Array(Int32)  │              │                    │         │                  │                │
26 │ user.name │ Array(String) │              │                    │         │                  │                │
27 │ user.age  │ Array(UInt8)  │              │                    │         │                  │                │
28 └───────────┴───────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
29 
30 4 rows in set. Elapsed: 0.012 sec. 
31 
32 master :) 

插入数据,进行简单的测试,如下所示:

 1 master :) insert into tb_nested values(1 , [1,2,3],['zss','lss','ymm'],[21,33,18]) ;
 2 
 3 INSERT INTO tb_nested VALUES
 4 
 5 Ok.
 6 
 7 1 rows in set. Elapsed: 0.007 sec. 
 8 
 9 master :) SELECT 
10 :-]     user.uid,
11 :-]     user.name
12 :-] FROM tb_nested;
13 
14 SELECT 
15     user.uid,
16     user.name
17 FROM tb_nested
18 
19 ┌─user.uid─┬─user.name───────────┐
20 │ [1,2,3]  │ ['zss','lss','ymm'] │
21 └──────────┴─────────────────────┘
22 
23 1 rows in set. Elapsed: 0.008 sec. 
24 
25 master :) 

4.9、元组,Tuple(T1, T2, ...):元组,其中每个元素都有单独的类型。

  Tuple元组数据类型,是一个特殊的数据类型,可以理解为集合,可以存储任意的数据类型,这一点区别于数组。在定义的时候需要声明数据类型和数据元素的个数。

数据类型由1~n个元素组成,每个元素之间允许设置不同的数据类型,且彼此之间不要求兼容。元素同样支持类型推断,其推断依据仍然以最小存储代价为原则。与数据类型,元组也可以使用两种方式定义,常规方式是tuple(T),元组中可以存储多种数据类型,但是要注意数据类型的顺序。

使用案例,如下所示:

 1 master :) 
 2 master :) SELECT tuple(1,'a') AS x, toTypeName(x);  # SELECT tuple(1,'a',12.23)可以简写为SELECT (1,'a',12.23)
 3 
 4 SELECT 
 5     (1, 'a') AS x,
 6     toTypeName(x)
 7 
 8 ┌─x───────┬─toTypeName(tuple(1, 'a'))─┐
 9 │ (1,'a') │ Tuple(UInt8, String)      │
10 └─────────┴───────────────────────────┘
11 
12 1 rows in set. Elapsed: 0.008 sec. 
13 
14 master :) 

注意:创建数据表的时候使用元组的时候,需要指定元素的数据类型。

 1 master :) 
 2 master :) CREATE TABLE tb_tuple
 3 :-] (
 4 :-]     `id` UInt8,
 5 :-] `t1` Tuple(String,Int8,Date) 
 6 :-] )ENGINE = Log;
 7 
 8 CREATE TABLE tb_tuple
 9 (
10     `id` UInt8,
11     `t1` Tuple(String, Int8, Date)
12 )
13 ENGINE = Log
14 
15 Ok.
16 
17 0 rows in set. Elapsed: 0.066 sec. 
18 
19 master :) insert into tb_tuple values(1, ('red',25,'1994-05-18'));
20 
21 INSERT INTO tb_tuple VALUES
22 
23 Ok.
24 
25 1 rows in set. Elapsed: 0.145 sec. 
26 
27 master :) select * from tb_tuple;
28 
29 SELECT *
30 FROM tb_tuple
31 
32 ┌─id─┬─t1──────────────────────┐
33 │  1 │ ('red',25,'1994-05-18') │
34 └────┴─────────────────────────┘
35 
36 1 rows in set. Elapsed: 0.033 sec. 
37 
38 master :) 

4.10、Date日期类型、DateTime日期时间类型,支持符合格式的字符串类型的插入。

DateTime64可以记录亚秒,它在DateTime之上增加了精度的设置,在指定数据类型的时候可以使用DateTime64(2)指定两位精度。

 1 master :) 
 2 master :) CREATE TABLE tb_name
 3 :-] (
 4 :-]     `id` UInt8,
 5 :-]     `age` UInt8,
 6 :-]     `birthday` Date,
 7 :-] `updateTime` DateTime
 8 :-] )ENGINE = Log;
 9 
10 CREATE TABLE tb_name
11 (
12     `id` UInt8,
13     `age` UInt8,
14     `birthday` Date,
15     `updateTime` DateTime
16 )
17 ENGINE = Log
18 
19 Ok.
20 
21 0 rows in set. Elapsed: 0.055 sec. 
22 
23 master :) 
24 master :) insert into tb_name values(1,22,'1994-05-16','2021-02-20 14:21:30'),(2,24,'1994-05-17','2021-02-20 14:21:30');
25 
26 INSERT INTO tb_name VALUES
27 
28 Ok.
29 
30 2 rows in set. Elapsed: 0.065 sec. 
31 
32 master :) select * from tb_name;
33 
34 SELECT *
35 FROM tb_name
36 
37 ┌─id─┬─age─┬───birthday─┬──────────updateTime─┐
38 │  1 │  22 │ 1994-05-16 │ 2021-02-20 14:21:30 │
39 │  2 │  24 │ 1994-05-17 │ 2021-02-20 14:21:30 │
40 └────┴─────┴────────────┴─────────────────────┘
41 
42 2 rows in set. Elapsed: 0.010 sec. 
43 
44 master :) 

4.11、Enum枚举类型,Clickhouse支持枚举类型,这是一种在定义常量的时候经常会使用的数据类型,Clickhouse提供了Enum8和Enum16两种枚举类型,他们除了取值范围不同之外,别无二致。枚举固定使用(String:Int),这种Key/Value键值对的行式定义数据,所以Enum8和Enum16分别会对应(String:Int8)和(String:Int16)。

 1 master :) 
 2 master :) CREATE TABLE tb_enum
 3 :-] (
 4 :-]     `id` UInt8,
 5 :-] `color` Enum('red' = 1,'green' = 2, 'blue' = 3)
 6 :-] )ENGINE = Log;
 7 
 8 CREATE TABLE tb_enum
 9 (
10     `id` UInt8,
11     `color` Enum('red' = 1, 'green' = 2, 'blue' = 3)
12 )
13 ENGINE = Log
14 
15 Ok.
16 
17 0 rows in set. Elapsed: 0.034 sec. 
18 
19 master :) insert into tb_enum values(1,'red'),(2,'green'),(3,'blue');
20 
21 INSERT INTO tb_enum VALUES
22 
23 Ok.
24 
25 3 rows in set. Elapsed: 0.055 sec. 
26 
27 master :) insert into tb_enum values(4,1),(5,2),(6,3);
28 
29 INSERT INTO tb_enum VALUES
30 
31 Ok.
32 
33 3 rows in set. Elapsed: 0.035 sec. 
34 
35 master :) 

4.12、Domain(pojo、beans实体类)里面的IPv4数据类型,域名分为IPv4和IPv6两类,本质上它们是对整数和字符串的进一步封装,其中IPv4类型是基于UInt32封装的。

  1)、出于便捷性的考量,例如IPv4类型支持格式检查,格式错误的IP数据是无法被写入的。

  2)、出于性能的考量,同样以IPv4为例,IPv4使用UInt32存储,相对String更加紧凑,占用空间更小,查询性能更快。IPv6类型是基于FixedString(16)封装的,它的使用彷佛与IPv4别无二致,在使用Domain类型的时候还有一点需要注意,虽然它从表象上看起来与String一样,但是Domain类型并不是字符串,所以它不支持隐式的自动类型转换。如果需要返回IP的字符串行式,需要显示调用IPv4NumToString或者IPv6NumToString函数进行转换。

 1 master :) 
 2 master :) CREATE TABLE tb_domin
 3 :-] (
 4 :-]     `id` UInt8,
 5 :-] `ip` IPv4
 6 :-] )ENGINE = Log;
 7 
 8 CREATE TABLE tb_domin
 9 (
10     `id` UInt8,
11     `ip` IPv4
12 )
13 ENGINE = Log
14 
15 Ok.
16 
17 0 rows in set. Elapsed: 0.024 sec. 
18 
19 master :) insert into tb_domin values(1, '192.168.110.133');
20 
21 INSERT INTO tb_domin VALUES
22 
23 Ok.
24 
25 1 rows in set. Elapsed: 0.058 sec. 
26 
27 master :) insert into tb_domin values(1, '192.168.110.133');
28 
29 INSERT INTO tb_domin VALUES
30 
31 Ok.
32 
33 1 rows in set. Elapsed: 0.006 sec. 
34 
35 master :) select * from tb_domin;
36 
37 SELECT *
38 FROM tb_domin
39 
40 ┌─id─┬──────────────ip─┐
41 │  1 │ 192.168.110.133 │
42 └────┴─────────────────┘
43 ┌─id─┬──────────────ip─┐
44 │  1 │ 192.168.110.133 │
45 └────┴─────────────────┘
46 
47 2 rows in set. Elapsed: 0.025 sec. 
48 
49 master :) 

5、Clickhouse为什么可以这么快?主要特点,如下所示:

1)、开发语言是C++,可以更好的利用硬件优势来提升数据处理的效率。   2)、摒弃了hadoop生态体系。   3)、数据底层以列式数据存储。   4)、可以利用单节点的多核并行处理。   5)、为数据建立索引,分为一级索引、二级索引、稀疏索引。   6)、使用大量高效率算法处理数据。   7)、支持向量化处理数据。   8)、支持预先运算模型,预先计算,等等优势。

6、Clickhouse的引擎分为数据库引擎和数据表引擎,数据表的引擎,不同的引擎决定数据库的类型,Clickhouse的数据库引擎的作用。

  1)、引擎决定了数据存储位置。   2)、数据组织结构。   3)、是否分块、是否索引、是否持久化操作。   4)、是否可以并发读写。   5)、是否支持副本操作、是否支持索引。   6)、是否支持分布式。

7、Clickhouse的数据表引擎(即表的类型)决定了的特征,如下所示:

  1)、数据的存储方式和位置,写到哪里以及从那里读取数据。   2)、支持那些查询以及如何支持。   3)、并发数据访问。   4)、索引的使用(如果存在)。   5)、是否可以执行多线程请求。   6)、数据复制参数。在读取时候,引擎只需要输出所请求的列,但在某些情况下,引擎可以在响应请求时部分处理数据。对于大多数正式的任务,应该使用MergeTree族中的引擎。

8、数据库,数据库起到了命名空间的作用,可以有效规避命名冲突的问题,也是为后续的数据隔离提供了支撑。任何一张数据表,都必须归属在某个数据库之下。在Clickhouse中数据库也有自己的引擎,数据库目前支持的数据库引擎有6种,如下所示:

1)、Ordinary,默认引擎,在绝大多数情况下我们都会使用默认引擎,使用的时候无须刻意声明。在此数据库下可以使用任意类型的表引擎。

  2)、Dictionary,字段引擎,此类数据库会自动为所有数据字典创建它们的数据表。

  3)、Memory,内存引擎,了解即可,用于存放临时数据,此类数据库下的数据表只会停留在内存中,不会设计任何磁盘操作,当服务重启后数据会被清除。

  4)、Lazy,日志引擎,此类数据库下只能使用Log系列的表引擎。

  5)、Mysql,Mysql数据库的引擎,此类数据库下会自动拉取远端Mysql数据库中的数据,并为他们创建Mysql表引擎的数据表。

  6)、MaterializeMySQL,主要做Mysql的数据同步,Clickhouse 20.8将新增 MaterializeMySQL引擎 ,可通过binlog日志实时物化mysql数据,极大提升了数仓的查询性能和数据同步的时效性;原有mysql中承担的数据分析工作 可交由clickhouse去做,这么做可显著降低线上mysql的负载,从此OLTP与OLAP业务实现完美融合。

9、Clickhouse的数据表引擎,具体分类,如下所示:

9.1)、Log日志引擎,具有最小功能的轻量级引擎。当您需要快速写入许多小表(最多约100万行)并在以后整体读取它们时,该类型的引擎是最有效的。具体表现为,比较简单,数据少,测试使用,Log家族都属于本地表(在/var/lib/clickhouse/data下面),本地存储表数据。

  1)、TinyLog引擎(数据不分快)。最简单的数据表引擎,用于将数据存储在磁盘上。每列都存储在单独的压缩文件中,写入的时候,数据将附加到文件末尾。该引擎没有并发控制,只支持并发读。如果同时从表中读取和写入数据,则读取操作将抛出异常。如果同时写入多个查询中的表,则数据将被破坏。

     注意:TinyLog引擎(数据不分快)引擎,*.bin每个字段的压缩数据,数据插入时会进行追加数据。sizes.json文件,显示每个字段文件的大小。该引擎的特点,没有数据块,不能并行操作,不能同时读写操作,没有索引,写是追加写,数据以列字段文件存储。

 1 master :) 
 2 master :) 
 3 master :) CREATE TABLE tb_tinyLog
 4 :-] (
 5 :-]     `id` UInt8,
 6 :-] `log_name` String
 7 :-] )ENGINE = TinyLog;
 8 
 9 CREATE TABLE tb_tinyLog
10 (
11     `id` UInt8,
12     `log_name` String
13 )
14 ENGINE = TinyLog
15 
16 Ok.
17 
18 0 rows in set. Elapsed: 0.016 sec. 
19 
20 master :) insert into tb_tinyLog values(1, '张三三');
21 
22 INSERT INTO tb_tinyLog VALUES
23 
24 Ok.
25 
26 1 rows in set. Elapsed: 0.027 sec. 
27 
28 master :) select * from tb_tinyLog;
29 
30 SELECT *
31 FROM tb_tinyLog
32 
33 ┌─id─┬─log_name─┐
34 │  1 │ 张三三   │
35 └────┴──────────┘
36 
37 1 rows in set. Elapsed: 0.010 sec. 
38 
39 master :) 

  2)、StripeLog引擎,数据分块列在一起,在你需要写入许多小数据量(小于一百万行)的表的场景下使用这个引擎。

  3)、Log引擎。

9.2)、MergeTree引擎,主要包含下面几种。

  1)、MergeTree()。

  2)、ReplacingMergeTree。

  3)、SummingMergeTree引擎,将相同主键的数据分区内合并指定字段进行累加。

  4)、CollapsingMergeTree,ClickHouse实现了CollapsingMergeTree来消除ReplacingMergeTree的限制(只删除小版本字段的问题)。该引擎要求在建表语句中指定一个标记列Sign,后台Compaction时会将主键相同、Sign相反的行进行折叠,也即删除。

     CollapsingMergeTree虽然解决了主键相同的数据即时删除的问题,但是状态持续变化且多线程并行写入情况下,状态行与取消行位置可能乱序,导致无法正常折叠。只有保证老的状态行在在取消行的上面, 新的状态行在取消行的下面,但是多线程无法保证写的顺序。

  5)、VersionedCollapsingMergeTree,取消字段和数据版本同事使用,避免取消行数据无法删除的问题,为了解决CollapsingMergeTree乱序写入情况下无法正常折叠问题,VersionedCollapsingMergeTree表引擎在建表语句中新增了一列Version,用于在乱序情况下记录状态行与取消行的对应关系。主键相同,且Version相同、Sign相反的行,在Compaction时会被删除。与CollapsingMergeTree类似, 为了获得正确结果,业务层需要改写SQL,将count()、sum(col)分别改写为sum(Sign)、sum(col * Sign)。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 开个新坑,新系列启动

    在 2020 年的时候,曾经计划和某在线机构合作出一门 ClickHouse 相关的在线课程,后来很遗憾由于一些原因没有合作成功。

    Nauu
  • 《Everything is Table,我该使用哪种表引擎》- part 2

    书接上回,今天放出第一章节的第二部分,我专门新建了一个专辑方便大家回看,传送: ClickHouse实战系列课程

    Nauu
  • 初识ClickHouse——安装与入门

    久闻 ClickHouse 大名,一直没有去详细了解。近期看了下 ClickHouse 相关文档,决定安装体验下。想了解 ClickHouse 的小伙伴可以一起...

    MySQL技术
  • ClickHouse大数据领域企业级应用实践和探索总结

    2020年下半年在OLAP领域有一匹黑马以席卷之势进入大数据开发者的领域,它就是ClickHouse。在2019年小编也曾介绍过ClickHouse,大家可以参...

    王知无-import_bigdata
  • 为什么ClickHouse分析数据库这么强?(原理剖析+应用实践)

    2020年下半年在OLAP领域有一匹黑马以席卷之势进入大数据开发者的领域,它就是ClickHouse。在2019年小编也曾介绍过ClickHouse,大家可以参...

    PHP开发工程师
  • ClickHouse原理 | ClickHouse特性及底层存储原理

    ClickHouse是一款MPP架构的列式存储数据库,但MPP和列式存储并不是什么"稀罕"的设计。拥有类似架构的其他数据库产品也有很多,但是为什么偏偏只有Cli...

    大数据技术架构
  • Go 语言学习之基础数据类型

    整数类型按照有/无符号划分,可分为有符号整数和无符号整数,二者按照大小划分,有 8 位、16 位、32 位和 64 位:

    frank.
  • Go语言学习(三)| 基础数据类型

    字符串在Go中是 UTF-8 的由双引号(")或反引号(``)包裹的字符序列(类型为string)。如果使用单引号则表示一个字符(UTF-8编码)一旦给变量赋值...

    Mervyn
  • 【大数据哔哔集20210110】后起之秀ClickHouse的优缺点和核心特性

    ClickHouse全称是Click Stream,Data Warehouse,简称ClickHouse就是基于页面的点击事件流,面向数据仓库进行OLAP分析...

    大数据真好玩
  • ClickHouse的核心特性及架构

    导读:随着业务的迅猛增长,Yandex.Metrica目前已经成为世界第三大Web流量分析平台,每天处理超过200亿个跟踪事件。能够拥有如此惊人的体量,在它背后...

    zhisheng
  • 从Druid到ClickHouse | eBay广告平台数据OLAP实战

    本文介绍eBay广告数据平台的基本情况,并对比分析了ClickHouse与Druid的使用特点。基于ClickHouse表现出的良好性能和扩展能力,本文介绍了如...

    Spark学习技巧
  • 想抓住大数据时代?小众数据库不可小觑

    随着大数据时代的发展,诞生了一大批大数据时代下的新数据库产品,如今MongoDB、Redis、HBase这些NoSQL数据库已经成为了互联网开发的新标配,SQL...

    Python进阶者
  • Java 基础语法(2)- 基础数据类型

    另外,对于 boolean,官方文档未明确定义,它依赖于 JVM 厂商的具体实现。逻辑上理解是占用 1 位,但是实际中会考虑计算机高效存储因素

    小菠萝测试笔记
  • 10 月数据库排名:“三大王”无人能敌,PostgreSQL 紧随其后

    DB-Engines最近发布了2020年10月份的数据库排名。该网站根据数据库管理系统的受欢迎程度对其进行排名,实时统计了359种数据库的排名指数。前10名的排...

    逆锋起笔
  • Java基础语法(二)——数据类型

      在C语言中,有 有符号数 和 无符号数 ,但是记住了,在Java 中没有所谓的无符号数!

    RAIN7
  • 实时数仓ClickHouse学习小指南

    ClickHouse 是Yandex开源的一个用于实时数据分析的基于列存储的数据库,其处理数据的速度比传统方法快100-1000 倍。 ClickHouse的性...

    大数据真好玩
  • [业界方案] ClickHouse业界解决方案学习笔记

    本文通过分析总结几篇文章来看目前工业界可能偏好的解决方案。学习目的是:大致知道其应用领域,技术特点和未来方向,看看目前工作中是否可以用到,或者当以后选型时候能够...

    罗西的思考
  • ClickHouse性能优化?试试物化视图

    ClickHouse是一个用于联机分析(OLAP)的列式数据库管理系统(DBMS);目前我们使用CH作为实时数仓用于统计分析,在做性能优化的时候使用了 物化视图...

    陶陶技术笔记
  • 腾讯、字节争先部署,ClickHouse+Doris赶超MySQL810倍

    作为大数据从业者,你一定明白有数据是一回事,可要想让数据发挥价值、成为生产力是另一回事。手里得有两把刷子,才能成为大数据圈儿的“大拿”!

    Nauu

扫码关注云+社区

领取腾讯云代金券