大数据之ClickHouse极具战斗性的数据库-未来可期

ClickHouse

软件介绍

简介

  • 实时数据分析数据库
  • 工作速度比传统方法快100-1000倍,ClickHouse 的性能超过了目前市场上可比的面向列的DBMS
  • 每秒钟每台服务器每秒处理数亿至十亿多行和数十千兆字节的数据

特点

  • 开源的列存储数据库管理系统,支持线性扩展,简单方便,高可靠性
  • 容错跑分快:比Vertica快5倍,比Hive快279倍,比MySQL快800倍,其可处理的数据级别已达到10亿级别
  • 功能多:支持数据统计分析各种场景,支持类SQL查询,异地复制部署

优点

  • 真正的面向列的DBMS(ClickHouse是一个DBMS,而不是一个单一的数据库。它允许在运行时创建表和数据库、加载数据和运行查询,而无需重新配置和重新启动服务器)
  • 数据压缩(一些面向列的DBMS(INFINIDB CE 和 MonetDB)不使用数据压缩
  • 磁盘存储的数据(许多面向列的DBMS(SPA HANA和GooglePowerDrill))只能在内存中工作。但即使在数千台服务器上,内存也太小了。)
  • 多核并行处理(多核多节点并行化大型查询)
  • 在多个服务器上分布式处理(
  • SQL支持
  • 向量化引擎(数据不仅按列式存储,而且由矢量-列的部分进行处理,这使得开发者能够实现高CPU性能)
  • 实时数据更新
  • 支持近似计算
  • 数据复制和对数据完整性的支持

缺点

  • 没有完整的事务支持,不支持Transaction想快就别Transaction
  • 缺少完整Update/Delete操作,缺少高频率、低延迟的修改或删除已存在数据的能力,仅用于批量删除或修改数据。
  • 聚合结果必须小于一台机器的内存大小
  • 支持有限操作系统,正在慢慢完善
  • 不适合Key-value存储,不支持Blob等文档型数据库

系统架构

Column与Field

  • Column和Field是ClickHouse数据最基础的映射单元。内存中的一列数据由一个Column对象表
  • Column对象分为接口和实现两个部分,在IColumn接口对象中,定义了对数据进行各种关系运算的方法
  • 在大多数场合,ClickHouse都会以整列的方式操作数据,但凡事也有例外。如果需要操作单个具体的数值 ( 也就是单列中的一行数据 ),则需要使用Field对象,Field对象代表一个单值。
  • 与Column对象的泛化设计思路不同,Field对象使用了聚合的设计模式。在Field对象内部聚合了Null、UInt64、String和Array等13种数据类型及相应的处理逻辑

数据类型DataType

  • 负责序列化和反序列化:读写二进制或文本形式的列或单个值构成的块。直接与表的数据类型相对应
  • 仅存储元数据
  • 数据的序列化和反序列化工作由DataType负责
  • DataType虽然负责序列化相关工作,但它并不直接负责数据的读取,而是转由从Column或Field对象获取

块Block

  • Block 是表示内存中表的子集(chunk)的容器,是由三元组: (IColumn, IDataType, 列名)构成的集合
  • ClickHouse内部的数据操作是面向Block对象进行的,并且采用了流的形式。Block对象可以看作数据表的子集
  • Block并没有直接聚合Column和DataType对象,而是通过ColumnWithTypeAndName对象进行间接引用

块流BlockStreams

  • 块流用于处理数据

  • Block流操作有两组顶层接口

    • IBlockInputStream负责数据的读取和关系运算, IBlockInputStream 具有 read 方法,其能够在数据可用时获取下一个块。
    • IBlockOutputStream负责将数据输出到下一环节。 IBlockOutputStream 具有 write 方法,其能够将块写到某处。
  • IBlockInputStream接口总共有60多个实现类,这些实现类大致可以分为三类

    • 第一类用于处理数据定义的DDL操作
    • 第二类用于处理关系运算的相关操作
    • 第三类则是与表引擎呼应,每一种表引擎都拥有与之对应的BlockInputStream实现

Formats格式

  • 数据格式同块流一起实现。用于向客户端输出数据的»展示«格式

数据读写I/O

  • 对于面向字节的输入输出,有 ReadBuffer 和 WriteBuffer 这两个抽象类
  • ReadBuffer 和 WriteBuffer 由一个连续的缓冲区和指向缓冲区中某个位置的一个指针组成。
  • ReadBuffer 和 WriteBuffer 的实现用于处理文件、文件描述符和网络套接字(socket),也用于实现压缩和其它用途。

数据表Table

  • 在数据表的底层设计中并没有所谓的Table对象
  • 表由 IStorage 接口表示。该接口的不同实现对应不同的表引擎。
  • 表引擎是ClickHouse的一个显著特性,不同的表引擎由不同的子类实现。
  • IStorage 中最重要的方法是 read 和 write ,除此之外还有 alter 、 rename 和 drop 等方法
  • 表的 read 方法能够返回多个 IBlockInputStream 对象以允许并行处理数据。多个块输入流能够从一个表中并行读取。
  • AST 查询被传递给 read 方法,表引擎可以使用它来判断是否能够使用索引,从而从表中读取更少的数据。

解析器Parser

  • 解析sql语句
  • 查询由一个手写递归下降解析器解析
  • 解析器创建 AST 。 AST 由节点表示,节点是 IAST 的实例

解释器Interpreter

  • 解析sql语句

函数Functions

  • 普通函数(Functions)

    • 单行函数。不会改变行数 - 它们的执行看起来就像是独立地处理每一行数据
  • 聚合函数(Aggregate Functions)

    • 组函数

Cluster与Replication

  • ClickHouse的集群由分片 ( Shard ) 组成,而每个分片又通过副本 ( Replica ) 组成。

  • 这种分层的概念,在一些流行的分布式系统中十分普遍

    • ClickHouse的1个节点只能拥有1个分片,也就是说如果要实现1分片、1副本,则至少需要部署2个服务节点。
    • 分片只是一个逻辑概念,其物理承载还是由副本承担的

数据定义

数据类型

  • 基本数据类型

    • 整数Int8、Int16、Int32 和 Int64

    • 浮点数 Float32 和 Float64

    • 定点数 Decimal32、Decimal64 和Decimal128

    • 布尔 UInt8 限制值为0或1

  • 字符串

    • String

      • 不限制长度,相当于Varchar、Text、Clob 和 Blob 等字符类型
    • FixedString

      • 相当于Char,长度固定,数据长度不够时,添加空字节(null);长度过长返回错误消息
    • UUID

      • 32位,格式8-4-4-4-12,如果未被赋值,则用0填充

        CREATE TABLE UUID_TEST (
        c1 UUID,
        c2 String
        ) ENGINE = Memory;
        –第一行UUID有值
        INSERT INTO UUID_TEST SELECT generateUUIDv4(),‘t1’
        –第二行UUID没有值
        INSERT INTO UUID_TEST(c2) VALUES(‘t2’)

  • 日期时间

    • Date: 2020-02-02 精确到天

      CREATE TABLE Date_TEST (
      c1 Date
      ) ENGINE = Memory
      –以字符串形式写入
      INSERT INTO Date_TEST VALUES(‘2019-06-22’)
      SELECT c1, toTypeName(c1) FROM Date_TEST

    • DateTime: 2020-02-02 20:20:20 精确到秒

      CREATE TABLE Datetime_TEST (
      c1 Datetime
      ) ENGINE = Memory
      –以字符串形式写入
      INSERT INTO Datetime_TEST VALUES(‘2019-06-22 00:00:00’)
      SELECT c1, toTypeName(c1) FROM Datetime_TEST

    • DateTime64: 2020-02-02 20:20:20.335 精确到亚秒,可以设置精度

      CREATE TABLE Datetime64_TEST (
      c1 Datetime64(2)
      ) ENGINE = Memory
      –以字符串形式写入
      INSERT INTO Datetime64_TEST VALUES(‘2019-06-22 00:00:00’)
      SELECT c1, toTypeName(c1) FROM Datetime64_TEST

  • 复合类型

    • 数组

      • 创建数据:array(T)或[],类型必须相同

        SELECT array(1, 2) as a , toTypeName(a)
        SELECT [1, 2, null] as a , toTypeName(a)
        CREATE TABLE Array_TEST (
        c1 Array(String)
        ) engine = Memory

    • 元组

      • 由多个元素组成,允许不同类型

      • 创建数据:(T1, T2, …),Tuple(T1, T2, …)

        SELECT tuple(1,‘a’,now()) AS x, toTypeName(x)
        SELECT (1,2.0,null) AS x, toTypeName(x)
        CREATE TABLE Tuple_TEST (
        c1 Tuple(String,Int8)
        ) ENGINE = Memory;

    • 枚举类型

      • ClickHouse提供了Enum8和Enum16两种枚举类型,它们除了取值范围不同之外,别无二致

      • 枚举固定使用(String:Int)Key/Value键值对的形式定义数据,所以Enum8和Enum16分别会对应(String:Int8)和(String:Int16)

      • 用(String:Int) Key/Value键值对的形式定义数据,键值对不能同时为空,不允许重复,key允许
        为空字符串,需要看到对应的值进行转换

        CREATE TABLE Enum_TEST (
        c1 Enum8(‘ready’ = 1, ‘start’ = 2, ‘success’ = 3, ‘error’ = 4)
        ) ENGINE = Memory;
        –正确语句
        INSERT INTO Enum_TEST VALUES(‘ready’);
        INSERT INTO Enum_TEST VALUES(‘start’);
        –错误语句
        INSERT INTO Enum_TEST VALUES(‘stop’);

    • 嵌套类型

      • Nested(Name1 Type1,Name2 Type2,…)

      • 相当于表中嵌套一张表,插入时相当于一个多维数组的格式,一个字段对应一个数组

        CREATE TABLE nested_test (
         name String,
         age UInt8 ,
         dept Nested(
          id UInt8,
          name String
         )
        ) ENGINE = Memory;
        –行与行之间,数组长度无须对齐
        INSERT INTO nested_test VALUES (‘bruce’ , 30 , [10000,10001,10002], [‘研
        发部’,‘技术支持中心’,‘测试部’]);
        INSERT INTO nested_test VALUES (‘bruce’ , 30 , [10000,10001], [‘研发
        部’,‘技术支持中心’]);

  • 其他类型

    • Nullable(TypeName)

      • 只能与基础数据类型搭配使用,表示某个类型的值可以为NULL;Nullable(Int8)表示可以存储Int8类型的值,没有值时存NULL

        CREATE TABLE Null_TEST (
        c1 String,
        c2 Nullable(UInt8)
        ) ENGINE = TinyLog;
        –通过Nullable修饰后c2字段可以被写入Null值:
        INSERT INTO Null_TEST VALUES (‘nauu’,null)
        INSERT INTO Null_TEST VALUES (‘bruce’,20)
        SELECT c1 , c2 ,toTypeName(c2) FROM Null_TEST

      • 注意

        • 不能与复合类型数据一起使用、
        • 不能作为索引字段
        • 尽量避免使用,字段被Nullable修饰后会额外生成[Column].null.bin 文件保存Null值,增加开销
    • Domain

      • Pv4 使用 UInt32 存储。如 116.253.40.133

      • IPv6 使用 FixedString(16) 存储。如 2a02:aa08:e000:3100::2

        CREATE TABLE IP4_TEST (
        url String,
        ip IPv4
        ) ENGINE = Memory;
        INSERT INTO IP4_TEST VALUES (‘www.nauu.com’,‘192.0.0.0’)
        SELECT url , ip ,toTypeName(ip) FROM IP4_TEST

数据库

  • 数据库起到了命名空间的作用,可以有效规避命名冲突的问题,也为后续的数据隔离提供了支撑。任何一张数据表,都必须归属在某个数据库之下

  • 操作语法

    CREATE DATABASE IF NOT EXISTS db_name [ENGINE = engine]
    SHOW DATABASES
    DROP DATABASE [IF EXISTS] db_name

  • 数据库引擎

    • Ordinary:默认引擎

      • 在绝大多数情况下我们都会使用默认引擎,使用时无须刻意声明。在此数据库下可以使用任意类型的表引擎。
    • Dictionary:字典引擎

      • 此类数据库会自动为所有数据字典创建它们的数据表,关于数据字典的详细介绍会在第5章展开。
    • Memory:内存引擎

      • 用于存放临时数据。此类数据库下的数据表只会停留在内存中,不会涉及任何磁盘操作,当服务重启后数据会被清除a
    • Lazy:日志引擎

      • 此类数据库下只能使用Log系列的表引擎,关于Log表引擎的详细介绍会在第8章展开。
    • MySQL:MySQL引擎

      • 此类数据库下会自动拉取远端MySQL中的数据,并为它们创建MySQL表引擎的数据表

数据表

  • ClickHouse目前提供了三种最基本的建表方法

    • 常规定义方法

      CREATE TABLE [IF NOT EXISTS] [db_name.]table_name (
      name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
      name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
      省略…
      ) ENGINE = engine
      CREATE TABLE hits_v1 (
      Title String,
      URL String ,
      EventTime DateTime
      ) ENGINE = Memory;

      • 使用[db_name.]参数可以为数据表指定数据库,如果不指定此参数,则默认会使用default数据库
    • 复制其他表的结构

      CREATE TABLE [IF NOT EXISTS] [db_name1.]table_name AS [db_name2.]
      table_name2 [ENGINE = engine]
      –创建新的数据库
      CREATE DATABASE IF NOT EXISTS new_db
      –将default.hits_v1的结构复制到new_db.hits_v1
      CREATE TABLE IF NOT EXISTS new_db.hits_v1 AS default.hits_v1 ENGINE =
      TinyLog

      • 支持在不同的数据库之间复制表结构
    • 通过SELECT子句的形式创建

      CREATE TABLE [IF NOT EXISTS] [db_name.]table_name ENGINE = engine AS
      SELECT …
      CREATE TABLE IF NOT EXISTS hits_v1_1 ENGINE = Memory AS SELECT * FROM
      hits_v1

      • 根据SELECT子句建立相应的表结构,同时还会将SELECT子句查询的数据顺带写入
  • 删除表

    • 和大多数数据库一样,使用DESC查询可以返回数据表的定义结构

    • 如果想删除一张数据表,则可以使用下面的DROP语句

      • DROP TABLE [IF EXISTS] [db_name.]table_name
  • 临时表

    • 创建临时表的方法是在普通表的基础之上添加TEMPORARY关键字

      CREATE TEMPORARY TABLE [IF NOT EXISTS] table_name (
      name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
      name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
      )

    • 特点

      • 它的生命周期是会话绑定的,所以它只支持Memory表引擎,如果会话结束,数据表就会被销毁;
      • 临时表不属于任何数据库,所以在它的建表语句中,既没有数据库参数也没有表引擎参数。
      • 临时表的优先级是大于普通表的。当两张数据表名称相同的时候,会优先读取临时表的数据
  • 分区表

    • 数据分区(partition)和数据分片(shard)是完全不同的两个概念

    • 数据分区是针对本地数据而言的,是数据的一种纵向切分。而数据分片是数据的一种横向切分

    • 案例

      CREATE TABLE partition_v1 (
      ID String,
      URL String,
      EventTime Date
      ) ENGINE = MergeTree()
      PARTITION BY toYYYYMM(EventTime)
      ORDER BY ID
      INSERT INTO partition_v1 VALUES
      (‘A000’,‘www.nauu.com’, ‘2019-05-01’),
      (‘A001’,‘www.brunce.com’, ‘2019-06-02’)
      SELECT table,partition,path from system.parts WHERE table =
      ‘partition_v1’

  • 数据表操作

    • 追加新字段

      ALTER TABLE tb_name ADD COLUMN [IF NOT EXISTS] name [type]
      [default_expr] [AFTER name_after]

      ALTER TABLE testcol_v1 ADD COLUMN OS String DEFAULT ‘mac’

      ALTER TABLE testcol_v1 ADD COLUMN IP String AFTER ID

    • 修改字段类型

      ALTER TABLE tb_name MODIFY COLUMN [IF EXISTS] name [type] [default_expr]

      ALTER TABLE testcol_v1 MODIFY COLUMN IP IPv4

    • 修改备注

      ALTER TABLE tb_name COMMENT COLUMN [IF EXISTS] name ‘some comment’

      ALTER TABLE testcol_v1 COMMENT COLUMN ID ‘主键ID’
      DESC testcol_v1

    • 删除已有字段

      ALTER TABLE tb_name DROP COLUMN [IF EXISTS] name

      ALTER TABLE testcol_v1 DROP COLUMN URL

    • 清空数据表

      TRUNCATE TABLE [IF EXISTS] [db_name.]tb_name

      TRUNCATE TABLE db_test.testcol_v2

视图

  • 普通视图

    • CREATE VIEW [IF NOT EXISTS] [db_name.]view_name AS SELECT …
    • 普通视图不会存储任何数据,它只是一层单纯的SELECT查询映射,起着简化查询、明晰语义的作用,对查询性能不会有任何增强
  • 物化视图

    • CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name]
      [ENGINE = engine] [POPULATE] AS SELECT .

    • 物化视图创建好之后,如果源表被写入新数据,那么物化视图也会同步更新

      • 如果使用了POPULATE修饰符,那么在创建视图的过程中,会连带将源表中已存在的数据一并导入,如同执行了SELECT INTO一般
      • 如果不使用POPULATE修饰符,那么物化视图在创建之后是没有数据的,它只会同步在此之后被写入源表的数据
      • 物化视图目前并不支持同步删除,如果在源表中删除了数据,物化视图的数据仍会保留。

数据的CRUD

  • 数据的写入

    • 使用VALUES格式的常规语法
    • 使用指定格式的语法
    • 使用SELECT子句形式的语法
  • 数据的删除和修改

    • ClickHouse提供了DELETE和UPDATE的能力,这类操作被称为Mutation查询,它可以看作ALTER语句的变种

    • 虽然Mutation能最终实现修改和删除,但不能完全以通常意义上的UPDATE和DELETE来理解

      • 首先,Mutation语句是一种“很重”的操作,更适用于批量数据的修改和删除;
      • 其次,它不支持事务,一旦语句被提交执行,就会立刻对现有数据产生影响,无法回滚;
      • 最后,Mutation语句的执行是一个异步的后台过程,语句被提交之后就会立即返回。

MergeTree

概述

  • 表引擎是ClickHouse设计实现中的一大特色
  • ClickHouse拥有非常庞大的表引擎体系,其共拥有合并树、外部存储、内存、文件、接口和其他6大类20多种表引擎
  • MergeTree作为家族中最基础的表引擎,提供了主键索引、数据分区、数据副本和数据采样等基本能力

创建与存储

  • 概述

    • MergeTree在写入一批数据时,数据总会以数据片段的形式写入磁盘,且数据片段不可修改
    • 为了避免片段过多,ClickHouse会通过后台线程,定期合并这些数据片段,属于相同分区的数据片段会被合成一个新的片段
    • 这种数据片段往复合并的特点,也正是合并树名称的由来
  • 创建方式

    • 语法

    • 配置选项

      • PARTITION BY [选填]:分区键,用于指定表数据以何种标准进行分区

        • 分区键既可以是单个列字段,也可以通过元组的形式使用多个列字段,同时它也支持使用列表达式。
        • 如果不声明分区键,则ClickHouse会生成一个名为all的分区。
        • 合理使用数据分区,可以有效减少查询时数据文件的扫描范围
      • ORDER BY [必填]:排序键,用于指定在一个数据片段内,数据以何种标准排序

        • 默认情况下主键(PRIMARY KEY)与排序键相同。
        • 排序键既可以是单个列字段也可以通过元组的形式使用多个列字段
        • 当使用多个列字段排序时,以ORDERBY(CounterID,EventDate)为例,在单个数据片段内,数据首先会以CounterID排序,相同CounterID的数据再按EventDate排序
      • PRIMARY KEY [选填]:主键,顾名思义,声明后会依照主键字段生成一级索引,用于加速表查询

        • 默认情况下,主键与排序键(ORDER BY)相同
          通常直接使用ORDER BY代为指定主
          键,无须刻意通过PRIMARY KEY声明
        • 与其他数据库不同,MergeTree主键允许存在重复数据(ReplacingMergeTree可以去重)
      • SAMPLE BY [选填]:抽样表达式,用于声明数据以何种标准进行采样

        • 如果使用了此配置项,那么在主键的配置中也需要声明同样的表达式
      • SETTINGS

        • index_granularity [选填]

          • 对于MergeTree而言是一项非常重要的参数,它表示索引的粒度,默认值为8192
          • MergeTree的索引在默认情况下,每间隔8192行数据才生成一条索引
  • 存储格式

    • 数据会按照分区目录的形式保存到磁盘之上

    • 一张数据表的完整物理结构分为3个层级,依次是数据表目录、分区目录及各分区下具体的数据文件

      • partition:分区目录,余下各类数据文件(primary.idx、[Column].mrk、[Column].bin等)
        都是以分区目录的形式被组织存放的,属于相同分区的数据,最终会被合并到同一个分区目
        录,而不同分区的数据,永远不会被合并在一起
      • checksums.txt:校验文件
      • columns.txt:列信息文件
      • count.txt:计数文件,用于记录当前数据分区目录下数据的总行数
      • primary.idx:一级索引文件,使用二进制格式存储。用于存放稀疏索引
      • [Column].bin:数据文件,使用压缩格式存储
      • [Column].mrk:列字段标记文件,使用二进制格式存储。标记文件中保存了.bin文件中数据的偏移量信息
      • [Column].mrk2:如果使用了自适应大小的索引间隔,则标记文件会以.mrk2命名。它的工作原理和作用与.mrk标记文件相同
      • partition.dat与minmax_[Column].idx:如果使用了分区键,例如PARTITION BYEventTime,则会额外生成partition.dat与minmax索引文件
      • skp_idx[Column].idx与skp_idx[Column].mrk:如果在建表语句中声明了二级索引,则会额外生成相应的二级索引与标记文件

数据分区

  • 数据分区规则

    • MergeTree数据分区的规则由分区ID决定,而具体到每个数据分区所对应的ID,则是由分区键的取值决定的

    • 针对取值数据类型的不同,分区ID的生成逻辑目前拥有四种规则

      • 不指定分区键

        • 如果不使用分区键,即不使用PARTITION BY声明任何分区表达式,则分区ID默认取名为all,所有的数据都会被写入这个all分区
      • 使用整型

        • 直接按照该整型的字符形式输出,作为分区ID的取值
      • 使用日期类型

        • 使用按照YYYYMMDD进行格式化后的字符形式输出,并作为分区ID的取值
      • 使用其他类型

        • 既不属于整型,也不属于日期类型
        • 例如String、Float等,则通过128位Hash算法取其Hash值作为分区ID的取值
  • 分区目录命名

    • 一个完整分区目录的命名公式

      • 201905表示分区目录的ID;
      • 1_1分别表示最小的数据块编号与最大的数据块编号;
      • 而最后的_0则表示目前合并的层级
    • PartitionID_MinBlockNum_MaxBlockNum_Level

      • PartitionID:分区ID
      • MinBlockNum和MaxBlockNum:顾名思义,最小数据块编号与最大数据块编号。
      • Level:合并的层级,可以理解为某个分区被合并过的次数,或者这个分区的年龄。数值越高表示年龄越大。
  • 分区目录合并

    • MergeTree的分区目录并不是在数据表被创建之后就存在的,而是在数据写入过程中被创建的

      • 也就是说如果一张数据表没有任何数据,那么也不会有任何分区目录存在
    • 其次,它的分区目录在建立之后也并不是一成不变的

      • 伴随着每一批数据的写入(一次INSERT语句),MergeTree都会生成一批新的分区目录
      • 即便不同批次写入的数据属于相同分区,也会生成不同的分区目录
      • 在之后的某个时刻(写入后的10~15分钟,也可以手动执行optimize查询语句)
      • ClickHouse会通过后台任务再将属于相同分区的多个目录合并成一个新的目录。
      • 已经存在的旧分区目录并不会立即被删除,而是在之后的某个时刻通过后台任务被删除(默认8分钟)。
    • 新目录名称的合并方式遵循规则

      • MinBlockNum:取同一分区内所有目录中最小的MinBlockNum值。
      • MaxBlockNum:取同一分区内所有目录中最大的MaxBlockNum值。
      • Level:取同一分区内最大Level值并加1。

一级索引

  • 概述

    • MergeTree的主键使用PRIMARY KEY定义,待主键定义之后,MergeTree会依据index_granularity间隔(默认8192行),为数据表生成一级索引并保存至primary.idx文件内,索引数据按照PRIMARYKEY排序
  • 稀疏索引

    • primary.idx文件内的一级索引采用稀疏索引实现

      • 稠密索引中每一行索引标记都会对应到一行具体的数据记录。

      • 稀疏索引中每一行索引标记对应的是一段数据,而不是一行。

      • 仅需使用少量的索引标记就能够记录大量数据的区间位置信息,且数据量越大优势越为明显

        • 由于稀疏索引占用空间小,所以primary.idx内的索引数据常驻内存,取用速度自然极快
  • 索引粒度

    • 索引粒度就如同标尺一般,会丈量整个数据的长度,并依照刻度对数据进行标注,最终将数据标记成多个间隔的小段
  • 索引规则

    • 由于是稀疏索引,所以MergeTree需要间隔index_granularity行数据才会生成一条索引记录,其索引值会依据声明的主键字段获取
    • 单主键
    • 多主键
  • 索引查询过程

二级索引

  • 概述

    • 二级索引又称跳数索引,由数据的聚合信息构建而成
    • 根据索引类型的不同,其聚合信息的内容也不同。跳数索引的目的与一级索引一样,也是帮助查询时减少数据扫描的范围
    • 需要手动开启
  • 粒度

    • granularity定义了一行跳数索引能够跳过多少个index_granularity区间的数据
  • 分类

    • minmax

      • 适合连续查询,重复少的数据
    • set

      • 适合大量重复的字典数据
    • ngrambf_v1

      • 布隆过滤,快速匹配,可能误判
    • tokenbf_v1

      • grambf_v1的变种,同样也是一种布隆过滤器索引
      • 除了短语token的处理方法外
      • 会自动按照非字符的、数字的字符串分割token

数据存储

  • 列式存储

    • 每列对应一个bin数据文件

    • 优势

      • 一是可以更好地进行数据压缩
      • 二是能够最小化数据扫描的范围
    • 存储方式

      • 首先,数据是经过压缩的
      • 其次,数据会事先依照ORDER BY的声明排序
      • 最后,数据是以压缩数据块的形式被组织并写入.bin文件中的
  • 数据压缩

    • 一个压缩数据块由头信息和压缩数据两部分组成

      • 头信息固定使用9位字节表示,具体由1个UInt8(1字节)整型和2个UInt32(4字节)整型组成。分别代表使用的压缩算法类型、压缩后的数据大小和压缩前的数据大小
      • bin压缩文件是由多个压缩数据块组成的
      • 每个压缩数据块的体积,按照其压缩前的数据字节大小,都被严格控制在64KB~1MB
    • 数据写入过程

      • 依照索引粒度(默认情况下,每次取8192行),按批次获取数据并进行处理
      • 单个批次数据size<64KB :如果单个批次数据小于64KB,则继续获取下一批数据,直至累积到size>=64KB时,生成下一个压缩数据块
      • 64KB<=size<=1MB直接生成下一个压缩数据块
      • size>1MB首先按照1MB大小截断并生成下一个压缩数据块。剩余数据继续依照上述规则执行
    • 优势

      • 虽然数据被压缩后能够有效减少数据大小,降低存储空间并加速数据传输效率,但数据的压缩和解压动作,其本身也会带来额外的性能损耗
      • 进一步缩小数据读取的范围
    • 压缩会丢失块的位置信息,用数据标记可解决

  • 数据标记

    • 生成规则

      • 数据标记作为衔接一级索引和数据的桥梁
      • 数据标记和索引区间是对齐的,均按照index_granularity的粒度间隔
      • 为了能够与数据衔接,数据标记文件也与.bin文件一一对应
      • 一行标记数据使用一个元组表示,元组内包含两个整型数值的偏移量信息
      • 每一行标记数据都表示了一个片段的数据(默认8192行)在.bin压缩文件中的读取位置信息
    • 工作方式

      • MergeTree在读取数据时,必须通过标记数据的位置信息才能够找到所需要的数据。整个查找过程大致可以分为读取压缩数据块和读取数据两个步骤

      • 数据理解

        • 1B*8192=8192B,64KB=65536B,65536/8192=8

        • 头信息固定由9个字节组成,压缩后大小为8个字节

        • 12016=8+12000+8

          • 读取压缩数据块
      • 读取压缩数据块

        • 查询某一列数据时,MergeTree无须一次性加载整个.bin文件,而是可以根据需要,只加载特定的压缩数据块
        • 而这项特性需要借助标记文件中所保存的压缩文件中的偏移量
      • 读取数据

        • 读取解压后的数据时,MergeTree并不需要一次性扫描整段解压数据,它可以根据需要,以index_granularity的粒度加载特定的一小段
        • 为了实现这项特性,需要借助标记文件中保存的解压数据块中的偏移量
  • 数据标记与数据压缩

    • 概述

      • 由于压缩数据块的划分,与一个间隔(index_granularity)内的数据大小相关,每个压缩数据块的体积都被严格控制在64KB~1MB。
      • 而一个间隔(index_granularity)的数据,又只会产生一行数据标记。
      • 那么根据一个间隔内数据的实际字节大小,数据标记和压缩数据块之间会产生三种不同的对应关系
    • 多对一

      • 多个数据标记对应一个压缩数据块
      • 当一个间隔(index_granularity)内的数据未压缩大小size小于64KB时
    • 一对一

      • 一个数据标记对应一个压缩数据块
      • 当一个间隔(index_granularity)内的数据未压缩大小size大于等于64KB且小于等于1MB时
    • 一对多

      • 一个数据标记对应多个压缩数据块
      • 当一个间隔(index_granularity)内的数据未压缩大小size直接大于1MB时

数据读写流程

  • 写入数据

    • 第一步是生成分区目录,伴随着每一批数据的写入,都会生成一个新的分区目录
    • 后续的某一时刻,属于相同分区的目录会依照规则合并到一起续的某一时刻,属于相同分区的目录会依照规则合并到一起
    • 接着,按照index_granularity索引粒度,会分别生成primary.idx一级索引、每一个列字段的.mrk数据标记和.bin压缩数据文件
  • 查询数据

    • 数据查询的本质,可以看作一个不断减小数据范围的过程
    • 在最理想的情况下,MergeTree首先可以依次借助分区索引、一级索引和二级索引,将数据扫描范围缩至最小
    • 然后再借助数据标记,将需要解压与计算的数据范围缩至最小

MergeTree Family

MergeTree

  • 数据TTL

    • 顾名思义,它表示数据的存活时间

    • 可以为某个列字段或整张表设置TTL

      • 当时间到达时,如果是列字段级别的TTL,则会删除这一列的数据;
      • 如果是表级别的TTL,则会删除整张表的数据;
      • 如果同时设置了列级别和表级别的TTL,则会以先到期的那个为主
  • 多路径存储策略

    • 19.15版本之前,MergeTree只支持单路径存储,所有的数据都会被写入config.xml配置中path指定的路径下

    • 19.15版本开始,MergeTree实现了自定义存储策略的功能,支持以数据分区为最小移动单元,将分区目录写入多块磁盘目录

    • 存储策略

      • 默认策略

        • 同19.15版本之前
      • JBOD策略

        • 轮询策略,每执行一次INSERT或者MERGE,所产生的新分区会轮询写入各个磁盘
        • 适合服务器挂载了多块磁盘,但没有做RAID的场景
      • HOT/COLD策略

        • 将存储磁盘分为HOT与COLD两类区域

          • HOT区域使用SSD这类高性能存储媒介,注重存取性能;
          • COLD区域则使用HDD这类高容量存储媒介,注重存取经济性。
          • 数据在写入MergeTree之初,首先会在HOT区域创建分区目录用于保存数据,当分区数据大小累积到阈值时,数据会自行移动到COLD区域
          • 这种策略适合服务器挂载了不同类型磁盘的场景。

ReplacingMergeTree

  • 为了数据去重而设计的,它能够在合并分区时删除重复的数据

  • ReplacingMergeTree是以分区为单位删除重复数据的

    • 只有在相同的数据分区内重复的数据才可以被删除,而不同数据分区之间的重复数据依然不能被剔除。
    • 如果要求主键完全不重复,那么这张表就不能分区

SummingMergeTree

  • sum求和
  • 能够在合并分区的时候按照预先定义的条件聚合汇总数据,将同一分组下的多行数据汇总合并成一行
  • 既减少了数据行,又降低了后续汇总查询的开销

AggregatingMergeTree

  • 数据立方体

    • 通过以空间换时间的方法提升查询性能,将需要聚合的数据,预先计算出来,并将结果保存起来
    • 在后续进行聚合查询的时候,直接使用结果数据
  • AggregatingMergeTree更为常见的应用方式是结合物化视图使用,将它作为物化视图的表引擎

CollapsingMergeTree

  • 一种通过以增代删的思路,支持行级数据修改和删除的表引擎

  • 通过定义一个sign标记位字段,记录数据行的状态

  • 如果sign标记为1,则表示这是一行有效的数据;如果sign标记为-1,则表示这行数据需要被删除

  • 当CollapsingMergeTree分区合并时,同一数据分区内,sign标记为1和-1的一组数据会被抵消删除

  • 这种1和-1相互抵消的操作,犹如将一张瓦楞纸折叠了一般

  • 折叠规则

    • 如果sign=1比sign=-1的数据多一行,则保留最后一行sign=1的数据。
    • 如果sign=-1比sign=1的数据多一行,则保留第一行sign=-1的数据。
    • 如果sign=1和sign=-1的数据行一样多,并且最后一行是sign=1,则保留第一行sign=-1和最后一行sign=1的数据。
    • 如果sign=1和sign=-1的数据行一样多,并且最后一行是sign=-1,则什么也不保留。
    • 其余情况,ClickHouse会打印警告日志,但不会报错,在这种情形下,查询结果不可预知
  • 特点

    • 折叠数据并不是实时触发的,和所有其他的MergeTree变种表引擎一样,这项特性也只有在分区合并的时候才会体现

    • 只有相同分区内的数据才有可能被折叠

    • CollapsingMergeTree对于写入数据的顺序有着严格要求

      • 先写入sign=1,再写入sign=-1,则能够正常折叠
      • 先写入sign=-1,再写入sign=1,则不能够折叠

VersionedCollapsingMergeTree

  • VersionedCollapsingMergeTree表引擎的作用与CollapsingMergeTree完全相同
  • 它们的不同之处在于,VersionedCollapsingMergeTree对数据的写入顺序没有要求,在同一个分区内,任意顺序的数据都能够完成折叠操作

MergeTree关系梳理

  • MergeTree表引擎向下派生出6个变种表引擎

常见类型表引擎

外部存储

  • 概述

    • 外部存储表引擎直接从其他的存储系统读取数据
    • 例如直接读取HDFS的文件或者MySQL数据库的表。
    • 这些表引擎只负责元数据管理和数据查询,而它们自身通常并不负责数据的写入,数据文件直接由外部系统提供。
  • HDFS

    • ENGINE = HDFS(‘hdfs://node01:8020/clickhouse/hdfs_table1’,‘CSV’);
  • Mysql

    • MySQL表引擎可以与MySQL数据库中的数据表建立映射,并通过SQL向其发起远程查询,包括SELECT和INSERT
    • ENGINE = MySQL(‘192.168.88.101:3306’, ‘scott’, ‘dept’, ‘root’,‘123456’);
  • JDBC

    • JDBC表引擎不仅可以对接MySQL数据库,还能够与PostgreSQL、SQLite和H2数据库对接。
    • JDBC表引擎无法单独完成所有的工作,它需要依赖名为clickhouse-jdbc-bridge的查询代理服务
  • Kafka

    • 目前ClickHouse还不支持恰好一次(Exactly once)的语义,因为这需要应用端与Kafka深度配合才能实现

    • ENGINE = Kafka()
      SETTINGS
      kafka_broker_list = 'host:port,… ',
      kafka_topic_list = ‘topic1,topic2,…’,
      kafka_group_name = ‘group_name’,
      kafka_format = ‘data_format’[,]
      [kafka_row_delimiter = ‘delimiter_symbol’]
      [kafka_schema = ‘’]
      [kafka_num_consumers = N]
      [kafka_skip_broken_messages = N]
      [kafka_commit_every_batch = N]

      • CREATE TABLE kafka_table(
        id UInt32,
        code String,
        name String
        ) ENGINE = Kafka()
        SETTINGS
        kafka_broker_list = ‘node01:9092’,
        kafka_topic_list = ‘topic_clickhouse’,
        kafka_group_name = ‘clickhouse’,
        kafka_format = ‘TabSeparated’,
        kafka_skip_broken_messages = 10;
    • 再次执行SELECT查询会发现kafka_table数据表空空如也,这是因为Kafka表引擎在执行查询之后就会移动offset,导致数据无法重复读取。

    • 解决方法

      • 首先是Kafka数据表A,它充当的角色是一条数据管道,负责拉取Kafka中的数据。
      • 接着是另外一张任意引擎的数据表B,它充当的角色是面向终端用户的查询表,在生产环境中通常是MergeTree系列。
      • 最后,是一张物化视图C,它负责将表A的数据实时同步到表B。
  • File

    • File表引擎能够直接读取本地文件的数据,通常被作为一种扩充手段来使用
    • File表引擎的定义参数中,并没有包含文件路径这一项。所以,File表引擎的数据文件只能保存在config.xml配置中由path指定的路径下
    • 每张File数据表均由目录和文件组成,其中目录以表的名称命名,而数据文件则固定以data.format命名

内存类型

  • 概述

    • 将数据全量放在内存中,对于表引擎来说是一把双刃剑

      • 一方面,这意味着拥有较好的查询性能;
      • 另一方面,如果表内装载的数据量过大,可能会带来极大的内存消耗和负担
  • Memory

    • Memory表引擎直接将数据保存在内存中,数据既不会被压缩也不会被格式转换,数据在内存中保存的形态与查询时看到的如出一辙。
    • 当ClickHouse服务重启的时候,Memory表内的数据会全部丢失。
    • 当数据被写入之后,磁盘上不会创建任何数据文件
  • Set

    • Set表引擎是拥有物理存储的,数据首先会被写至内存,然后被同步到磁盘文件中

    • 当服务重启时,它的数据不会丢失,当数据表被重新装载时,文件数据会再次被全量加载至内存

    • Set表引擎具有去重的能力,在数据写入的过程中,重复的数据会被自动忽略

    • Set表引擎的存储结构由两部分组成

      • [num].bin数据文件:保存了所有列字段的数据

        • num是一个自增id,从1开始
        • 伴随着每一批数据的写入(每一次INSERT),都会生成一个新的.bin文件,num也会随之加1
      • tmp临时目录:数据文件首先会被写到这个目录,当一批数据写入完毕之后,数据文件会被移出此目录

  • Join

    • Join表引擎可以说是为JOIN查询而生的,它等同于将JOIN查询进行了一层简单封装

      • join_strictness:连接精度

        • 决定了JOIN查询在连接数据时所使用的策略,目前支持ALL、ANY和ASOF三种类型。
      • join_type:连接类型

        • 它决定了JOIN查询组合左右两个数据集合的策略,它们所形成的结果是交集、并集、笛卡儿积或其他形式,目前支持INNER、OUTER和CROSS三种类型
      • join_key:连接键

        • 它决定了使用哪个列字段进行关联

日志类型

  • TinyLog

    • TinyLog是日志家族系列中性能最低的表引擎,它的存储结构由数据文件和元数据两部分组成

      • TinyLog既不支持分区,也没有.mrk标记文件
      • 所以它只适合在非常简单的场景下使用
  • StripeLog

    • StripeLog表引擎的存储结构由固定的3个文件组成

      • data.bin:数据文件

        • 所有的列字段使用同一个文件保存,它们的数据都会被写入data.bin
      • index.mrk:数据标记

        • 保存了数据在data.bin文件中的位置信息
      • sizes.json:元数据文件

        • 记录了data.bin和index.mrk大小的信息
  • Log

    • Log表引擎结合了TinyLog表引擎和StripeLog表引擎的长处,是日志家族系列中性能最高的表引擎

    • 由3个部分组成

      • [column].bin:数据文件

        • 数据文件按列独立存储,每一个列字段都拥有一个与之对应的.bin文件
      • marks.mrk:数据标记

        • 统一保存了数据在各个[column].bin文件中的位置信息
      • sizes.json:元数据文件

        • 记录了[column].bin和__marks.mrk大小的信息

接口类型

  • Merge

    • Merge表引擎就如同一层使用了门面模式的代理,它本身不存储任何数据,也不支持数据写入

      • 它的作用就如其名,即负责合并多个查询的结果集。
      • Merge表引擎可以代理查询任意数量的数据表,这些查询会异步且并行执行,并最终合成一个结果集返回
      • 被代理查询的数据表被要求处于同一个数据库内,且拥有相同的表结构,但是它们可以使用不同的表引擎以及不同的分区定义
      • CREATE TABLE test_table_all as test_table_2018
        ENGINE = Merge(currentDatabase(), ‘^test_table_’)

数据查询方式

概述

  • 在日常运转的过程中,数据查询也是ClickHouse的主要工作之一
  • ClickHouse对于SQL语句的解析是大小写敏感的

With子句

  • ClickHouse支持CTE(Common Table Expression,公共表表达式),以增强查询语句的表达

    • 在改用CTE的形式后,可以极大地提高语句的可读性和可维护性

      • SELECT pow(pow(2, 2), 3)
        WITH pow(2, 2) AS a SELECT pow(a, 3)
  • With的四种使用方法

    • 定义变量

      • 可以定义变量,这些变量能够在后续的查询子句中被直接访问。
    • 调用函数

      • 可以访问SELECT子句中的列字段,并调用函数做进一步的加工处理。
    • 定义子查询

    • 在子查询中重复使用WITH

      • 在子查询中可以嵌套使用WITH子句

From子句

  • FROM子句表示从何处读取数据,目前支持如下3种形式

    • 从数据表中取数
    • 从子查询中取数
    • 从表函数中取数
  • 在ClickHouse中,并没有数据库中常见的DUAL虚拟表,取而代之的是system.one。

  • 在FROM子句后,可以使用Final修饰符

    • 它可以配合CollapsingMergeTree和Versioned-CollapsingMergeTree等表引擎进行查询操作,以强制在查询过程中合并
    • 但由于Final修饰符会降低查询性能,所以应该尽可能避免使用它

Sample子句

  • SAMPLE子句能够实现数据采样的功能,使查询仅返回采样数据而不是全部数据,从而有效减少查询负载

  • SAMPLE子句的采样机制是一种幂等设计,也就是说在数据不发生变化的情况下,使用相同的采样规则总是能够返回相同的数据

  • SAMPLE子句只能用于MergeTree系列引擎的数据表,并且要求在CREATE TABLE时声明SAMPLEBY抽样表达式

  • 支持如下3种用法

    • SAMPLE factor

      • 表示按因子系数采样,其中factor表示采样因子,它的取值支持0~1之间的小数
      • 如果factor设置为0或者1,则效果等同于不进行数据采样
    • SAMPLE rows

      • 表示按样本数量采样,其中rows表示至少采样多少行数据,它的取值必须是大于1的整数
      • 如果rows的取值大于表内数据的总行数,则效果等于rows=1
    • SAMPLE factor OFFSET n

      • 表示按因子系数和偏移量采样,其中factor表示采样因子,n表示偏移多少数据后才开始采样,它们两个的取值都是0~1之间的小数

Array Join子句

  • ARRAY JOIN子句允许在数据表的内部,与数组或嵌套类型的字段进行JOIN操作,从而将一行数组展开为多行

  • 在一条SELECT语句中,只能存在一个ARRAY JOIN(使用子查询除外)。目前支持INNER和LEFT两种

    • INNER ARRAY JOIN

      • 最终的数据基于value数组被展开成了多行,并且排除掉了空数组
    • LEFT ARRAY JOIN

      • ARRAY JOIN子句支持LEFT连接策略
      • 在INNER JOIN中被排除掉的空数组出现在了返回的结果集中
      • 当同时对多个数组字段进行ARRAY JOIN操作时,查询的计算逻辑是按行合并而不是产生笛卡儿积

Join 子句

  • 概述

    • JOIN子句可以对左右两张表的数据进行连接
    • JOIN的语法包含连接精度和连接类型两部分
    • JOIN查询还可以根据其执行策略被划分为本地查询和远程查询。
  • 连接精度

    • 决定了JOIN查询在连接数据时所使用的策略,目前支持ALL、ANY和ASOF三种类型。如果不主动声明,则默认是ALL。

      • all

        • 如果左表内的一行数据,在右表中有多行数据与之连接匹配,则返回右表中全部连接的数据
      • any

        • 如果左表内的一行数据,在右表中有多行数据与之连接匹配,则仅返回右表中第一行连接的数据
      • asof

        • ASOF是一种模糊连接,它允许在连接键之后追加定义一个模糊连接的匹配条件asof_column
        • 最终返回的查询结果符合连接条件a.id=b.id AND a.time>=b.time,且仅返回了右表中第一行连接匹配的数据
        • ASOF支持使用USING的简写形式,USING后声明的最后一个字段会被自动转换成asof_colum模糊连接条件
        • asof_colum必须是整型、浮点型和日期型这类有序序列的数据类型;
        • asof_colum不能是数据表内的唯一字段
  • 连接类型

    • Inner

      • 表示内连接,在查询时会以左表为基础逐行遍历数据,然后从右表中找出与左边连接的行,它只会返回左表与右表两个数据集合中交集的部分,其余部分都会被排除
    • OUTER

      • OUTER JOIN表示外连接,它可以进一步细分为左外连接(LEFT)、右外连接(RIGHT)和全外连接(FULL)三种形式
    • Cross

      • CROSS JOIN表示交叉连接,它会返回左表与右表两个数据集合的笛卡儿积

查询优化

  • 为了能够优化JOIN查询性能,首先应该遵循左大右小的原则 ,无论使用的是哪种连接方式,右表
    都会被全部加载到内存中与左表进行比较
  • JOIN查询目前没有缓存的支持
  • 如果是在大量维度属性补全的查询场景中,则建议使用字典代替JOIN查询
  • 连接查询的空值是由默认值填充的,这与其他数据库所采取的策略不同(由Null填充)

WHERE与PREWHERE子句

  • WHERE子句基于条件表达式来实现数据过滤
    果过滤条件恰好是主键字段,则能够进一步借助
    索引加速查询

  • PREWHERE目前只能用于MergeTree系列的表引擎,它可以看作对WHERE的一种优化
    其作用与WHERE相同,均是用来过滤数据。

    • 使用PREWHERE时,首先只读取PREWHERE指定的列字段数据,用于数据过滤的条件判断。
    • 待数据过滤之后再读取SELECT声明的列字段以补全其余属性。
  • ClickHouse实现了自动优化的功能,会在条件合适的情况下将WHERE替换为PREWHERE

    • 如果想开启这项特性,需要将optimize_move_to_prewhere设置为1

GROUP BY子句

  • 聚合查询

  • 能配合WITH ROLLUP、WITHCUBE和WITH TOTALS三种修饰符获取额外的汇总信息

  • WITH ROLLUP

    • ROLLUP能够按照聚合键从右向左上卷数据,基于聚合函数依次生成分组小计和总计
  • WITH CUBE

    • CUBE会像立方体模型一样,基于聚合键之间所有的组合生成小计信息。如果设聚合键的个数为n,则最终小计组合的个数为2的n次方
  • WITH TOTALS

    • 使用TOTALS修饰符后,会基于聚合函数对所有数据进行总计

Having子句

  • HAVING子句需要与GROUP BY同时出现,不能单独使用。它能够在聚合计算之后实现二次过滤数据

ORDER BY子句

  • ORDER BY子句通过声明排序键来指定查询数据返回时的顺序

LIMIT BY子句

  • 运行于ORDER BY之后和LIMIT之前,能够按照指定分组,最多返回前n行数据(如果数据少于n行,则按实际数量返回)

  • 常用于TOP N的查询场景。LIMIT BY的常规语法如下

    • LIMIT n BY express

LIMIT子句

  • LIMIT子句用于返回指定的前n行数据,常用于分页场景

  • 三种语法形式

    • LIMIT n
    • LIMIT n OFFSET m
    • LIMIT m,n
  • 使用LIMIT子句时有一点需要注意,如果数据跨越了多个分区,在没有使用ORDER BY指定全局顺序的情况下,每次LIMIT查询所返回的数据有可能不同。如果对数据的返回顺序敏感,则应搭配ORDER BY一同使用。

上一篇:八Clickhouse 应用案例


下一篇:python 无法连接上clickhouse