ClickHouse--OLAP database

07 Jul 2021

概述

ClickHouse是一个快速开源的OLAP数据库管理系统，它是面向列的，允许使用SQL查询实时生成分析报告。

Clickhouse 发展历史

ROLAP : 传统关系型数据库OLAP，基于MySQL的MyISAM表引擎。

MOLAP :借助物化视图的形式实现数据立方体。预处理的结果存在HBase这类高性能的分布式数据库

HOLAP:R和M的结合体H

ROLAP :ClickHouse

Clickhouse 支持特性剖析

1.真正的面向列的DBMS
2.数据高效压缩
3.磁盘存储的数据
4.多核并行处理
5.在多个服务器上分布式处理
6.SQL语法支持
7.向量化引擎
8.实时数据更新
9.索引
10.适合在线查询
11.支持近似预估计算
12.支持嵌套的数据结构
13.支持数组作为数据类型
14.支持限制查询复杂性以及配额
15.复制数据和对数据完整性的支持

ClickHouse的不完美

Ø 1.不支持事物。
Ø 2.不支持Update/Delete操作。
Ø 3.支持有限操作系统。

ClickHouse应用场景

1.电信行业用于存储数据和统计数据使用。
2.新浪微博用于用户行为数据记录和分析工作。
3.用于广告网络和RTB,电子商务的用户行为分析。
4.信息安全里面的日志分析。
5.检测和遥感信息的挖掘。
6.商业智能。
7.网络游戏以及物联网的数据处理和价值数据分析。

安装

下载地址

官网:https://clickhouse.yandex/
下载地址:http://repo.red-soft.biz/repos/clickhouse/stable/el6/

单机模式

分布式集群安装

数据类型

支持DML， 为了提高性能，较传统数据库而言，clickhouse提供了复合数据类型。 ClickHouse的Upadate和Delete是由Alter变种实现。

整型

固定长度的整型，包括有符号整型或无符号整型。

浮点型

建议尽可能以整数形式存储数据。例如，将固定精度的数字转换为整数值，如时间用毫秒为单位表示，因为浮点型进行计算时可能引起四舍五入的误差。

与标准SQL相比，ClickHouse 支持以下类别的浮点数: Inf-正无穷:

:) select 1/0  
┌─divide(1, 0)─┐  
│      inf │  
└──────────────┘

-Inf-负无穷:

:) select -1/0  
┌─divide(1, 0)─┐  
│      -inf │  
└──────────────┘

NaN-非数字:

:) select 0/0  
┌─divide(0, 0)─┐  
│      nan │  
└──────────────┘

Decimal

如果要求更高精度，可以选择Decimal类型
格式:Decimal(P,S)
P:代表精度，决定总位数(正数部分+小数部分)，取值范围0-38
S:代表规模，决定小数位数，取值范围是0-P
ClickHouse对Decimal提供三种简写: Decimal32，Decimal64，Decimal128

字符串

clickhouse的字符串需要使用‘’,
“”会被认为是列名

UUID

ClickHouse将UUID这种在传统数据库中充当主键的类型直接做成了数据类型

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

insert into UUID_TEST select generateUUIDv4(),'t1';  
insert into UUID_TEST(c2) values('t2');  
  
  
SELECT *  
FROM UUID_TEST;  
  
┌───────────────────────────────────c1─┬─c2─┐  
│ 00000000-0000-0000-0000-000000000000 │ t2 │  
└──────────────────────────────────────┴────┘  
┌───────────────────────────────────c1─┬─c2─┐  
│ b5098c0b-d824-42b4-9c3c-9317e3ba5fb7 │ t1 │  
└──────────────────────────────────────┴────┘

枚举类型

包括 Enum8 和 Enum16 类型。Enum 保存 ‘string’= integer 的对应关系。 Enum8 用 ‘String’= Int8 对描述。
Enum16 用 ‘String’= Int16 对描述。

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

CREATE TABLE t_enum  
(  
  x Enum8('hello' = 1, 'world' = 2)  
)  
ENGINE = TinyLog

这个 x 列只能存储类型定义中列出的值:’hello’或’world’。如果尝试保存任何其他值，ClickHouse 抛出异常。

:) INSERT INTO t_enum VALUES ('hello'), ('world'), ('hello’);  
  
  
:) insert into t_enum values('a')  
INSERT INTO t_enum VALUES  
Exception on client:  
Code: 49. DB::Exception: Unknown element 'a' for type Enum8('hello' = 1, 'world' = 2)

从表中查询数据时，ClickHouse 从 Enum 中输出字符串值。

SELECT *  
FROM t_enum  
  
┌─x─────┐  
│ hello │  
│ world │  
│ hello │  
└───────┘

如果需要看到对应行的数值，则必须将 Enum 值转换为整数类型。

SELECT CAST(x, 'Int8')  
FROM t_enum  
  
┌─CAST(x, 'Int8')─┐  
│               1 │  
│               2 │  
│               1 │  
└─────────────────┘

为什么需要枚举类型? 后续对枚举的操作:排序、分组、去重、过滤等，会使用Int类型的Value值

数组

Array(T):由 T 类型元素组成的数组。
T 可以是任意类型，包含数组类型。 但不推荐使用多维数组，ClickHouse 对多维数组的支持有限。例如，不能在 MergeTree 表中存储多维数组。
可以使用array函数来创建数组:
array(T) 也可以使用方号:ClickHouse能够自动推断数据类型 []
创建数组案例:

--数组中可以有不同的数据类型，但是需要相互兼容。  
  
SELECT   
    [1, 2.] AS x,  
    toTypeName(x)  
  
┌─x─────┬─toTypeName(array(1, 2.))─┐  
│ [1,2] │ Array(Float64)           │  
└───────┴──────────────────────────┘  
  
  
:) SELECT array(1, 2) AS x, toTypeName(x);  
  
SELECT   
    [1, 2] AS x,  
    toTypeName(x)  
  
┌─x─────┬─toTypeName(array(1, 2))─┐  
│ [1,2] │ Array(UInt8)            │  
└───────┴─────────────────────────┘

如果是声明表字段的时候，需要指明数据类型:

 CREATE TABLE Array_test  
(  
    `c1` Array(String)  
)  
ENGINE = memory

元组

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

创建元组的示例:

:) SELECT tuple(1,'a') AS x, toTypeName(x) ;  
  
SELECT   
    (1, 'a') AS x,  
    toTypeName(x)  
  
┌─x───────┬─toTypeName(tuple(1, 'a'))─┐  
│ (1,'a') │ Tuple(UInt8, String)      │  
└─────────┴───────────────────────────┘

在定义表字段的时候也需要指明数据类型

Date、DateTime

日期类型，用两个字节存储，表示从 1970-01-01 (无符号) 到当前的日期值。

Date: yyyy-MM-dd
DateTime: yyyy-MM-dd HH:mm:ss

布尔型

没有单独的类型来存储布尔值。可以使用 UInt8 类型，取值限制为 0 或 1。

表引擎

表引擎(即表的类型)决定了:
1)数据的存储方式和位置，写到哪里以及从哪里读取数据
2)支持哪些查询以及如何支持。
3)并发数据访问。
4)索引的使用(如果存在)。
5)是否可以执行多线程请求。
6)数据复制参数。

ClickHouse的表引擎有很多，下面介绍其中几种，对其他引擎有兴趣的可以去查阅官方文档:https://clickhouse.yandex/docs/zh/operations/table_engines/

日志

Memory

内存引擎，数据以未压缩的原始形式直接保存在内存当中，服务器重启数据就会消失。读写操作不会相互阻塞，不支 持索引。简单查询下有非常非常高的性能表现(超过10G/s)。
一般用到它的地方不多，除了用来测试，就是在需要非常高的性能，同时数据量又不太大(上限大概 1 亿行)的场 景。

Merge

Merge 引擎 (不要跟 MergeTree 引擎混淆) 本身不存储数据，但可用于同时从任意多个其他的表中读取数据。 读是自动并行的，不支持写入。读取时，那些被真正读取到数据的表的索引(如果有的话)会被使用。 Merge 引擎的参数:一个数据库名和一个用于匹配表名的正则表达式。

案例:先建t1，t2，t3三个表，然后用 Merge 引擎的 t 表再把它们链接起来。

:)create table t1 (id UInt16, name String) ENGINE=TinyLog;  
:)create table t2 (id UInt16, name String) ENGINE=TinyLog;  
:)create table t3 (id UInt16, name String) ENGINE=TinyLog;  
  
:)insert into t1(id, name) values (1, 'first');  
:)insert into t2(id, name) values (2, 'second');  
:)insert into t3(id, name) values (3, 'i am in t3');  
  
:)create table t (id UInt16, name String) ENGINE=Merge(currentDatabase(), '^t');  
  
:) select * from t; ┌─id─┬─name─┐  
│ 2│second│ └────┴──────┘ ┌─id─┬─name──┐  
│ 1│first│ └────┴───────┘ ┌─id─┬─name───────┐  
│ 3 │ i am in t3 │ └────┴────────────┘

MergeTree

https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/mergetree/

Clickhouse 中最强大的表引擎当属 MergeTree (合并树)引擎及该系列(*MergeTree)中的其他引擎。
MergeTree 引擎系列的基本理念如下。当你有巨量数据要插入到表中，你要高效地一批批写入数据片段，并希望这 些数据片段在后台按照一定规则合并。相比在插入时不断修改(重写)数据进存储，这种策略会高效很多。

MergeTree家族表引擎

集成外部表

HDFS

https://clickhouse.tech/docs/en/engines/table-engines/integrations/hdfs/

This engine provides integration with Apache Hadoop ecosystem by allowing to manage data on HDFS via ClickHouse. This engine is similar
to the File and URL engines, but provides Hadoop-specific features.

-- HDFS  
  
CREATE TABLE hdfs_engine_table (  
name String,   
value UInt32)   
ENGINE=HDFS('hdfs://centos7-1:9000/clickhouse_tabs/test1', 'TSV');  
-- 数据会以TSV的形式存在clickhouse_tabs/test1,  
-- 注意确保clickhouse有访问clickhouse_tabs的权限 hadoop fs -chown clickhouse:clickhouse /clickhouse_tabs  
  
INSERT INTO hdfs_engine_table VALUES ('one', 1), ('two', 2), ('three', 3);  
select * from hdfs_engine_table;  
-- Not supported:  
-- ALTER and SELECT...SAMPLE operations.  
-- Indexes.  
-- Replication.

MySQL

https://clickhouse.tech/docs/en/engines/table-engines/integrations/mysql/

The MySQL engine allows you to perform SELECT and INSERT queries on data that is stored on a remote MySQL server.

-- mysql  
create table mysql_table(  
	`name` String,  
	`age` Int32  
)  
ENGINE=MySQL('centos7-3:3306', 'test', 'student','hive','12345678');  
insert into mysql_table values('april', 20),('angie', 18);  
select * from mysql_table;  
-- 不支持update

Kafka

https://clickhouse.tech/docs/en/engines/table-engines/integrations/kafka/

Kafka lets you:

-- consumer table  
CREATE TABLE queue (  
    timestamp UInt64,  
    level String,  
    message String  
) ENGINE = Kafka('centos7-2:9092', 'topic3', 'group1', 'CSV');  
-- broker, topic, consumer group, text format  
   
-- result table  
CREATE TABLE daily (  
    day Date,  
    level String,  
    total UInt64  
) ENGINE = SummingMergeTree(day, (day, level), 8192);  
  
-- 物化视图,创建之后会在后台持续接收kafka消息,并把结果汇总到结果表中  
CREATE MATERIALIZED VIEW consumer TO daily  
   AS SELECT toDate(toDateTime(timestamp)) AS day, level, count() as total  
FROM queue GROUP BY day, level;  
  
SELECT level, sum(total) FROM daily GROUP BY level;  
  
-- consumer 的查询结果是当前窗口时间内收到的消息  
select * from queue;  
-- 1624715352,1,hello  
-- 1624715352,1,hello  
-- 1624715352,2,hello  
-- 1624715352,3,hello  
-- 1624715352,4,hello  
-- 1624715352,5,hello  
-- 1624715352,6,hello

JDBC

https://clickhouse.tech/docs/en/engines/table-engines/integrations/jdbc/

Allows ClickHouse to connect to external databases via JDBC.

To implement the JDBC connection, ClickHouse uses the separate program clickhouse-jdbc-bridge that should run as a daemon.

This engine supports the Nullable data type.

用法和mysql差不多

create table jdbc_table(  
	`name` String,  
	`age` Int32  
)  
ENGINE=JDBC('jdbc:mysql://centos7-3:3306/?user=hive&password=12345678',   
'test', 'student');  
  
insert into jdbc_table values('hello', 20);  
select * from jdbc_table where age > 15;

副本和分片

副本

https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/replication/#table_engines-replication

zk: 实现多个实例之间的通信。

Data Replication
Replication is only supported for tables in the MergeTree family:

ReplicatedMergeTree原理

分片shard

分布式保存,分区