数据分析数据库ClickHouse在大数据领域应用实践

目录
  • 一、序言
    • 1、应用场景
    • 2、学习姿势
  • 二、知识储备
    • (一)磁盘IO
      • 1、数据量与查询效率
    • (二)性能对比
      • 1、磁盘工作机制
      • 2、按行(列)存储
  • 三、基础知识
    • (一)表结构
      • 1、排序
      • 2、主键
      • 3、默认值
    • (二)表引擎
      • 1、MergeTree
      • 2、ReplacingMergeTree
      • 3、SummingMergeTree
    • (三)内置函数
      • 1、格式化日期
      • 2、哈希函数
      • 3、日期函数
  • 四、安装与配置
    • (一)安装
    • (二)配置
      • 1、正则替换注释
      • 2、服务端配置文件
  • 五、小结

一、序言

面向大数据量查询数据库,优点是在较大数据量(千万级)的前提下具有较好的查询性能。

1、应用场景

ClickHouse应用于OLAP(在线分析处理)领域,具体来说满足如下特点使用此技术比较合适:

  • 事务型数据库表通过连表查询转换成宽表
  • 聚合(统计)计算使用较多
  • 对查询效率要求较高,有限时间范围内能够容忍非幂等性查询(最终一致性)

2、学习姿势

大多数学习ClickHouse是从OLTP数据库开始的,比如Mysql数据库。对于千万级别的数据,以InnoDB为存储引擎的表,仅仅是统计表行数这一需求,执行效率很低,对于一些聚合函数,相应延迟同样无法接受。

提高数据库硬件水平,一定程度上能够改善查询效率问题,但仍然不能彻底解决查询效率问题。ClickHouse一推出就大火更加印证开发者在较大数据量的前提下希望有个合理查询效率的需求是多么的急切。

以典型的Mysql数据库读写分离为例,横向对比ClickHouse,对比Mysql为何查询慢以及ClickHouse为何查询要快,在此基础上综合考虑OLTP如何与OLAP协同工作。

二、知识储备

(一)磁盘IO

1、数据量与查询效率

数据量在超过一定边界后,查询效率急剧下降,造成查询效率低下的主要原因是磁盘IO。比如Mysql数据库,通过服务器优化(增加硬件资源消耗),能够提高一定的性能,并不能从软件层次有效提高查询效率。

千万级别的大表,查询性能较低,主要涉及磁盘这块,影响因素有两条:一是数据索引定位;二是磁盘IO。

(二)性能对比

1、磁盘工作机制

操作系统从磁盘读取数据到内存中,大体经过如下过程:索引到数据存储位置;以页为单位IO数据。其中数据索引完毕,IO过程相对较快(速度与内存IO不是一个数量级)。

磁盘页IO表示在磁盘页上命中一条记录与全部命中,IO时间相同。实际使用过程中,查询一条记录与多条连续记录有时候时间相似(底层逻辑都是从磁盘IO一个磁盘页的数据)。

2、按行(列)存储

通过简单示例比较按行存储与按列存储对查询的影响,主要以磁盘IO最为技术指标。测试数据量为千万级别。

CREATE TABLE `human_name` (
  `id` bigint(20) NOT NULL COMMENT 'ID',
  `name` varchar(32) DEFAULT NULL COMMENT '名称',
  `deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `delete_time` datetime DEFAULT NULL COMMENT '删除时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='人名信息表';

通过不同的场景,对比不同存储方式在磁盘IO上的消耗,进而比较查询效率。

(1)通过id查询name

存储方式 索引方式 磁盘IO 执行过程
行存储 哈希索引O(1)
BTree索引O(logN)
整行数据 磁盘上执行选择操作,内存执行投影操作
列存储 主键稀疏索引+二级索引 单行name列数据 在磁盘上执行选择操作同时完成了投影操作

行存储在索引上节约时间;列存储在磁盘IO上节约时间,数据量较小可以忽略差异,本回合二者持平。

(2)通过批id查询name

批查询是指有限区间查询或者有限集合查询,数据量百条以内。有限区间查询与有限集合查询,对应的数据量较小,性能表现差别不大。仔细分析过程,二者仍然存在明显的差异。

区间查询的效率比有限集合查询效率要高,原因如下:区间查询数据存储是连续的,单次数据索引,单页磁盘IO(数据量较小),紧凑的数据查询,按行存储略占优势,考虑到是查询单个字段,因此磁盘数据索引次数均为一次(按列查询多少列即索引多少次)。

集合查询由于查询条件非连续,需要单独索引并完成磁盘IO,集合中有N个元素(随机)需要索引N次,以页为单位的磁盘IO

(3)通过id查询整行数据

按列存储通常比按行存储的查询效率要高,对于宽表(几十列以上的聚合表),效果更加明显。对于查询,更多的需求是查询某列数据或者某几列数据,按列存储的数据库能够大大减少磁盘数据的扫描范围以及磁盘与内存之间的IO,从IO层面提高了查询效率。

极端情况

数据库存储id和name数据,两者都是非空的必选数据,这种情况下按行(列)存储从IO层面来讲是相似的,数据在磁盘上扫描范围和读写IO差不多。通过id查询name或者批量id查询name,借助于哈希索引,按行存储可能具有O(1)的时间复杂度。

实际数据不可能这么纯粹,行记录通常会有保存时间、修改时间、删除时间、部分核心字段的修改时间,数据量较少时,附属字段对查询的影响较小,一旦数据量超过一定阀值,对查询的影响逐步凸显。按列存储能够忽略附属字段的磁盘扫描与IO。

综合来讲,从查询的角度来讲,按列存储要优于按行存储。

三、基础知识

(一)表结构

clickhouse使用的表结构与常见的关系数据库有一定的区别。

1、排序

在合并树家族引擎中,表排序属性是必选项。通过ORDER BY关键字设置分区内数据的排序策略,数据在导入或者保存时按照排序策略有序存储,有序数据直接存储在磁盘中,查询时具有较高的效率。

排序列也是索引列,高频用作查询条件的字段添加到排序列有利于提高查询效率。

2、主键

主键的定义比较奇怪,仅仅是起到过滤查询索引的作用,没有唯一约束的效果。

当设置有主键时,主键字段必需包含在排序属性中,且从左到右依次展开。

3、默认值

Null类型几乎总是会拖累性能,原因如下:空值无法被索引;需要使用额外的特殊占位符单独处理。按列存储每列数据个数一致有利于数据查询。

数据在导入之前需要做空值处理,将空值替换成与业务无关的数据。

(二)表引擎

clickhouse表引擎非常丰富,其中最常用的是合并树家族引擎。

1、MergeTree

MergeTree引擎能够实现较大数据量的查询需求,由于主键没有唯一索引约束,存在重复行的情况。在数据迁移的过程中,不可避免会出现重复数据导入的情况,业务上能够容忍部分重复数据,或者从应用端处理重复数据,可以选择此引擎。

CREATE TABLE test_tbl (
  id UInt16,
  create_time Date,
  comment Nullable(String)
) ENGINE = MergeTree()
	 PARTITION BY toYYYYMMDD(create_time)
     ORDER BY  (create_time)
     PRIMARY KEY (id)
     TTL create_time + INTERVAL 1 MONTH
     SETTINGS index_granularity=8192;

MergeTree引擎必需指定排序字段。

属性 含义 备注
ORDER BY 指定排序字段(必选) 指定一个或者多个字段作为排序字段(分区内排序)
PARTITION BY 指定分区规则 一般而言以日期作为表分区的策略
PRIMARY KEY 主键字段 主键元素可以重复并且能够指定多个字段
TTL 记录过期时间 可以指定记录的过期时间
SETTINGS 稀疏索引间隔 无特别需求使用默认值即可

MergeTree的主键的作用是加速查询,不是类似MySQL保持记录唯一。

2、ReplacingMergeTree

ReplacingMergeTree引擎用来去除重复行,此处的去重有三个层次的含义:在分区内去重;以主键字段为比较对象;数据去重实践只会在合并时发生。

-- 强制后台合并,去重时所在表停止服务
optimize table test_tbl_replacing final;

ReplacingMergeTree提供了主键去重的能力,但是仍旧有以下限制:

  • optimize是后台动作,无法预测具体执行时间点;
  • 在没有彻底optimize之前,不能确定是否仍有重复数据;
  • 手动执行optimize在海量数据场景下要消耗大量时间,无法满足业务即时查询的需求;
  • 在分布式场景下,相同primary key的数据可能被sharding到不同节点上,不同shard间可能无法去重;

ReplacingMergeTree更多用于确保数据最终被去重,无法保证查询过程中主键不重复。

ReplacingMergeTree(create_time)填入参数为版本字段,重复记录保留版本号最大最在行;允许为空,默认保留重复行最后插入的记录。

去重深刻理解

这里的去重并不能达到关系型数据库严格意义去重的目的,使用时需要注意这个现象。另外不能以非黑即白的想法考虑这个问题,ClickHouse在提高查询速度时做了一定的妥协。

3、SummingMergeTree

SummingMergeTree提供的是一种预聚合引擎,等效为以order by字段为单位分组,然后执行聚合求和操作,不过这些结果是提前计算好了的,查询时不需要实时计算。

如果聚合的值不满足要求,可以在查询结果集上通过聚合函数再次聚合,此时属于实时计算。

(三)内置函数

常见的内置函数需要特别指出,新建表模式、数据导入等方面会有应用。

1、格式化日期

格式化分区函数常用于表的分区设置,以天为单位的分区是常见的分区设置。

select toYYYYMMDD(now())

2、哈希函数

以name字段的哈希字符串作为分区策略。

CREATE TABLE default.test02 (
`id` UInt16,
`name` String,
`create_time` Date) ENGINE = MergeTree()
PARTITION BY LOWER(hex(MD5(name)))
PRIMARY KEY id
ORDER BY (id,create_time);

表可以不设置主键,一旦设置主键,那么表必选排序属性必需以主键的顺序依次展开。

直接用原始字符串字段值作为分区策略也是可行的,考虑到字符串的值域范围比较广,用哈希函数处理会比较安全。

3、日期函数

获取各种日期函数,如果不指定时区,默认读取宿主机的时区信息。

SELECT toDateTime(now()) AS t1,
       toDate(now()) AS t2,
       toDate(now(), 'Asia/Shanghai') AS t3,
       toString(now()) AS t4

四、安装与配置

版本选择长期支持版本20.8,采用手动安装的方式进行。

(一)安装

rpm -ivh clickhouse-server-20.8.19.4-2.noarch.rpm
rpm -ivh clickhouse-client-20.8.19.4-2.noarch.rpm
rpm -ivh clickhouse-common-static-20.8.19.4-2.x86_64.rpm

(二)配置

1、正则替换注释

使用模式<!-[\s\S]*?->$查询配置XML配置文件中所有注释。

# 格式化XML文件
xmllint --format config.xml

2、服务端配置文件

服务端配置文件有两个config.xmlusers.xml,前者是只读配置,后者可以在运行时动态修改。

五、小结

ClickHouse生态在快速迭代,很多亮眼的功能尚处于开发中或者测试中,这里选取部分特性与大家分享。

以上就是数据分析数据库ClickHouse在大数据领域应用实践的详细内容,更多关于ClickHouse大数据应用的资料请关注我们其它相关文章!

(0)

相关推荐

  • 以示例讲解Clickhouse Docker集群部署以及配置

    目录 写在前面 环境部署 Zookeeper集群部署 Clickhouse集群部署 1.临时镜像拷贝出配置 2.修改config.xml配置 3.拷贝到其他文件夹 4.分发到其他服务器 配置集群 1.修改配置 2.新增集群配置文件metrika.xml 集群运行及测试 写在前面 抽空来更新一下大数据的玩意儿了,起初架构选型的时候有考虑Hadoop那一套做数仓,但是Hadoop要求的服务器数量有点高,集群至少6台或以上,所以选择了Clickhouse(后面简称CH).CH做集群的话,3台服务器起步

  • 如何将Mybatis连接到ClickHouse

    场景 最近在做数据分析项目,里面有这样一个业务:把匹配的数据打上标签,放到新的索引中. 数据量:累计亿级的数据 使用场景:可能会单次查询大量的数据,但不会设置复杂的条件,且这些数据不会被再次修改 原来使用的数据库:ElasticSearch 问题:上面也说了我这里打上标记后,这些数据几乎不会再修改了.ES 是一个全文检索引擎,更适用于进行大量文本检索的情况.这里与我上面的使用场景就不太匹配了. 技术选型的考虑:改用战斗民族开发的 ClickHouse,它适用于 OLAP 也就是数据分析的场景,当

  • springboot 使用clickhouse实时大数据分析引擎(使用方式)

    声明: 因项目中使用clickhouse引擎这里springboot使用的方式是jdbc方式连接,这种方式的好处是可以使用clickhouse 自带的fetch方法批量从clickhouse中获取数据,对于大量数据的下载来说,比较好 因为如果全部拿到内存中处理,大量数据会有内存溢出的结果 如果批量多次请求数据库对于数据库查询等也不靠谱,所有直接使用clickhouse jdbc连接来满足这种情况,不使用mybatis等框架来管理,这里根据大家不同的需求酌情参考即可 使用方式: 第一步:加入cli

  • SpringBoot2 整合 ClickHouse数据库案例解析

    一.ClickHouse简介 1.基础简介 Yandex开源的数据分析的数据库,名字叫做ClickHouse,适合流式或批次入库的时序数据.ClickHouse不应该被用作通用数据库,而是作为超高性能的海量数据快速查询的分布式实时处理平台,在数据汇总查询方面(如GROUP BY),ClickHouse的查询速度非常快. 2.数据分析能力 OLAP场景特征 · 大多数是读请求 · 数据总是以相当大的批(> 1000 rows)进行写入 · 不修改已添加的数据 · 每次查询都从数据库中读取大量的行,

  • 使用 Apache Superset 可视化 ClickHouse 数据的两种方法

    Apache Superset是一个强大的BI工具,它提供了查看和探索数据的方法.它在 ClickHouse 用户中也越来越受欢迎. 我们将介绍安装 Superset 的 2 种方法,然后展示如何从 Superset 连接到您的第一个 ClickHouse 数据库.代码示例基于 Ubuntu 18.04.Superset 1.1.0 和 clickhouse-sqlalchemy 0.1.6. 方法一:Python虚拟环境 第一种方法直接在您的主机上安装 Superset.我们将首先创建一个 P

  • 数据分析数据库ClickHouse在大数据领域应用实践

    目录 一.序言 1.应用场景 2.学习姿势 二.知识储备 (一)磁盘IO 1.数据量与查询效率 (二)性能对比 1.磁盘工作机制 2.按行(列)存储 三.基础知识 (一)表结构 1.排序 2.主键 3.默认值 (二)表引擎 1.MergeTree 2.ReplacingMergeTree 3.SummingMergeTree (三)内置函数 1.格式化日期 2.哈希函数 3.日期函数 四.安装与配置 (一)安装 (二)配置 1.正则替换注释 2.服务端配置文件 五.小结 一.序言 面向大数据量查

  • 大数据时代的数据库选择:SQL还是NoSQL?

    一.专家简介VoltDB公司首席技术官Ryan Betts表示,SQL已经赢得了大型企业的广泛部署,大数据是它可以支持的另一个领域.Couchbase公司首席执行官Bob Wiederhold表示,NoSQL是可行的选择,并且从很多方面来看,它是大数据的最佳选择,特别是涉及到可扩展性时.二.SQL经历时间的考验,并仍然在蓬勃发展结构化查询语言(SQL)是经过时间考验的胜利者,它已经主宰了几十年,目前大数据公司和组织(例如谷歌.Facebook.Cloudera和Apache)正在积极投资于SQL

  • 浅谈用Python实现一个大数据搜索引擎

    搜索是大数据领域里常见的需求.Splunk和ELK分别是该领域在非开源和开源领域里的领导者.本文利用很少的Python代码实现了一个基本的数据搜索功能,试图让大家理解大数据搜索的基本原理. 布隆过滤器 (Bloom Filter) 第一步我们先要实现一个布隆过滤器. 布隆过滤器是大数据领域的一个常见算法,它的目的是过滤掉那些不是目标的元素.也就是说如果一个要搜索的词并不存在与我的数据中,那么它可以以很快的速度返回目标不存在. 让我们看看以下布隆过滤器的代码: class Bloomfilter(

  • 货拉拉大数据对BitMap的探索实践详解

    目录 关于Bitmap What BitMap的简单实现 BitSet源码理解 备注信息 核心片段理解 Why BitMap的特点 BitMap的优化 RoaringBitmap的核心原理 how BitMap在用户分群的应用 传统解决方案 使用BitMap的方案 BitMap在A/B实验平台业务的应用 结语 关于Bitmap 在大数据时代,想要不断提升基于海量数据获取的决策.洞察发现和流程优化等能力,就需要不停思考,如何在利用有限的资源实现高效稳定地产出可信且丰富的数据,从而提高赋能下游产品的

  • 如何将Oracle的一个大数据表快速迁移到 Sqlserver2008数据库(图文教程)

    oracle 服务器  版本  11.2.0.1.0 Sqlserver2008  R2 前提条件是 SQLSERVER服务器上安装了Oracle客户端并且进行了配置 不会配置的请参照 这个链接 1  登录MSSM 工具 2 选中其中一个数据库  右键⇒任务⇒导入数据 3   打开窗口 Sqlserver导入和导出向导   点击下一步 4 进入选择数据源画面 a: 数据源 选择  Microsoft OLE DB Provider for Oracle  然后 点击 右侧的 属性 按钮 5  数

  • 大数据量高并发的数据库优化详解

    如果不能设计一个合理的数据库模型,不仅会增加客户端和服务器段程序的编程和维护的难度,而且将会影响系统实际运行的性能.所以,在一个系统开始实施之前,完备的数据库模型的设计是必须的. 一.数据库结构的设计 在一个系统分析.设计阶段,因为数据量较小,负荷较低.我们往往只注意到功能的实现,而很难注意到性能的薄弱之处,等到系统投入实际运行一段时间后,才发现系统的性能在降低,这时再来考虑提高系统性能则要花费更多的人力物力,而整个系统也不可避免的形成了一个打补丁工程. 所以在考虑整个系统的流程的时候,我们必须

  • Python3实现将本地JSON大数据文件写入MySQL数据库的方法

    本文实例讲述了Python3实现将本地JSON大数据文件写入MySQL数据库的方法.分享给大家供大家参考,具体如下: 最近导师给了一个yelp上的评论数据,数据量达到3.55个G,如果进行分析时直接使用本地文件,选择python来分析,那么效率是非常低的:另一方面使用SQL来储存文本文件最为安全,之前使用CSV,txt存储的文本文件最后莫名其妙地出现一些奇怪字符,导致读取数据分割时出现错乱.下面给出一个简单的代码,将本地JSON文件内容存入数据库. 说明:python版本为3.5,使用第三方库为

  • 完美解决因数据库一次查询数据量过大导致的内存溢出问题

    刚开始接触项目的实习生,积累经验,欢迎交流 之前做项目,遇到过一次查询数据量过大而导致的内存溢出问题,找了很多办法一直未能实际解决问题, 今天又遇到了,经过前辈的指导,终于解决了问题!! 不过此方法只在DBug启动下有效 以上这篇完美解决因数据库一次查询数据量过大导致的内存溢出问题就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持我们.

  • postgresql数据库 timescaledb 时序库 把大数据量表转换为超表的问题

    前言 这几天工作的时候发现在 timescaledb 时序库 中有部分大数据量的表不是超表,估计是当时建库的时候没有改 影响插入,查询效率 ,因此需要改成超表 因工作原因 部分内容做保密处理了 一 创建新表 首先因为在 timescaledb 时序库 中创建超表必须是要没有数据的表 因此第一步是 创建一张跟原表一模一样的表(表名后面加个_cs) 你可以直接copy建表语句,也可以直接用工具复制表结构 二 把新表改为超表 把新建的表改为超表,7天一分区 --我是7天一分区 SELECT creat

随机推荐