ClickHouse 使用技巧总结
文章目录
- 数据导入、导出技巧
- 外部文件导入导技巧
- 使用集成表引擎导入、导出数据
- 建表技巧
- 表引擎选择技巧
- 分区键选择技巧
- 数据结构选择技巧
- 分区技巧
- 高级技巧
- 物化视图
- 投影
- 位图
- 变更数据捕获
- 常见报错及处理方法
数据导入、导出技巧
外部文件导入导技巧
ClickHouse作为OLAP即席分析引擎,不可避免地需要将数据从业务数据库、传统数据仓库等数据源中提取数据,当数据计算完成后,也可能需要将数据导出为外部数据文件供其他系统使用。
CSV、TSV文件导入建议
- 尽量使用TSV代替CSV,CSV 中如果真实数据中也出现了逗号,此时引擎无法区分这个逗号是分隔符还是数据
- 尽可能使用时间戳代替时间文本
- 将ODS层数据表的时间类型设置为String 先将ClickHouse中目标表时间日期类型的字段设置为string,先将数据导入,接着对这个ODS的表进行数据清洗,通过ClickHouse内置的SQL函数解决问题
数据导出技巧
-
通过INTO OUTFILE导出
-
通过文件表引擎导入、导出数据,创建表文件引擎:
导入导出SQL: -
通过命令行重定向导出
使用集成表引擎导入、导出数据
-
利用MySQL表引擎实现数据的导入、导出
在创建MySQL表引擎时,需要注意ClickHouse中本地表的列名必须和远程MySQL的列名完全一致。
MySQL 与 Clickhouse 数据类型映射关系如下:
创建MySQL外部表后,即可通过下面的SQL语句实现数据的导入、导出。 -
利用MongoDB表引擎实现数据的导入、导出
利用下面的SQL语句创建外部MongoDB表引擎:
创建MongoDB外部表后,即可通过下面的SQL语句实现数据的导入: -
利用HDFS表引擎实现数据的导入、导出
利用下面的SQL语句创建外部HDFS表引擎
HDFS表引擎还支持对HDFS的路径使用通配符进行模糊处理,以支持更灵活的HDFS文件夹策略。ClickHouse支持的通配符如下图所示:
对数据进行导入、导出: -
利用S3表引擎实现数据的导入、导出
表引擎创建:
-
利用PostgreSQL表引擎实现数据的导入、导出
PostgreSQL和ClickHouse数据类型的对应关系
数据的导入、导出: -
利用JDBC表引擎实现数据的导入、导出。
要使用JDBC表引擎,必须先运行一个名为clickhouse-jdbc-bridge的Java进程,并做适当的配置。关键要配置好数据库驱动和数据源地址。下面展示一段clickhouse-jdbc-bridge的配置信息。其中数据源驱动的地址可以是一个远程的地址,也可以配置成本地的文件路径:
表引擎创建:
数据的导入、导出:
另外,JDBC表引擎由于配置信息已经存储在了clickhouse-jdbc-bridge的配置文件中,因此还有一种特殊的不需要创建ClickHouse虚拟表的访问方法。可以利用这种方式将数据导入ClickHouse本地表或外部数据文件,代码如下: -
不要利用外部表引擎进行复杂查询
在很多情况下,复杂SQL查询性能很差,而且有可能对业务产生影响。只有在满足如下条件时,利用该SQL查询的技巧才能获得比较高的收益。
- 远程数据表经常发生变动。
- 远程数据表数据量比较小。
- 在ClickHouse中的查询语句是低频的。
- 不会影响其他业务的正常运行,能够忍受这些影响。
-
对数据量大的数据表进行迁移时,利用TSV进行中转
在应对大批量数据时,建议按照年、月或日对任务进行切分,启动多个进程并行执行。另外,将数据先导入TSV进行中转,避免中途出错导致整个任务重新运行。
-
利用Kafka表引擎实现数据的导入、导出
Kafka表引擎一般和ClickHouse的物化视图一起使用,否则ClickHouse只会读取Kafka中最新的消息。通过ClickHouse的物化视图,在后台将Kafka中的数据源源不断地写入本地,以实现Kafka数据的持久化。下面展示创建Kafka表引擎的SQL语句:
创建Kafka表引擎后,可以对该表引擎进行SELECT查询,但是对该表的查询只会查询到Kafka中最新的一条数据。需要从该时刻将Kafka中的数据源源不断地持久化保存,必须利用ClickHouse提供的物化视图的能力,代码如下:
建表技巧
表引擎选择技巧
-
优先选择MergeTree家族的表。
基于MergeTree表引擎所派生出来的多个表引擎说明如下:
在使用时建议先创建基础的MergeTree表,在基础MergeTree表上再构建这些派生的MergeTree表引擎,避免由于使用不恰当的主键组合导致数据丢失。 -
利用Buffer表引擎解决大量INSERT带来的问题
由于Buffer表必须有底层物理表,因此创建Buffer表时不需要列出各列的类型,代码如下:
通过Buffer表,可以解决ClickHouse遇到突发大量INSERT语句时报错的问题,起到缓冲的作用。使用Buffer表也存在如下一些问题:- 由于ClickHouse没有使用WAL(Write Ahead Log,预写日志)技术,因此系统崩溃可能导致丢失数据。
- Buffer写入物理表时,可能由于物理表引擎的特性导致数据错乱。例如当底层表为折叠表时可能因为丢失顺序而造成错乱。
建议在满足如下条件的情况下使用Buffer表:
- 数据少量丢失不会影响业务。
- 底层表选择基础的MergeTree表引擎。
-
利用Memory表引擎提高并发查询能力
Memory也是一个内存表,和Buffer不同的是,Memory表引擎不需要底层的数据表。Memory表也不会将数据定期写入磁盘。
ClickHouse由于每次查询都会大量利用单机资源,因此并发能力并不高,解决该问题的一个策略是组建ClickHouse集群,在某些场景下还可以利用Memory表引擎提高ClickHouse的并发能力。
利用ClickHouse的Memory表引擎提高并发能力,并不是随意将查询所需的表载入内存后查询。而是根据业务进行判断,如果大量的并发查询是查询某一个固定的模型,那么需要将该模型固化为Cube,将Cube保存为Memory表,以应对高并发查询的需求。
Memory表引擎解决并发问题的核心在于,能够将模型转化为Cube,如果不能转化为Cube,那么使用Memory表引擎可能会得不偿失。需要根据业务的实际情况进行判断,千万不能将查询所涉及的表都塞入Memory表,否则ClickHouse的内存可能会溢出,导致服务器崩溃。
分区键选择技巧
ClickHouse的主键就是分区键,和传统事务数据库的主键不同,ClickHouse的主键不具备唯一性约束,只是分区键的别名,在选择分区键(主键)时也有一些技巧。
- 最左原则,一定要将最频繁使用的列放在最左边。很多情况下,放在右边的列可能无法得到加速。
- 适当冗余建表,ClickHouse是一个压缩率很高的数据库,我们完全不必强求数据在ClickHouse中只存一份,当遇到多个查询任务需要不同的排序键时,可以放心大胆地创建一个除了主键不同,其他都相同的数据表。
数据结构选择技巧
使用低基数类型
-
低基数类型(LowCardinality)是ClickHouse中的一个特殊的包装类型,通过该类型可以将数据类型进行字典编码,替换为更高效的存储格式。尤其当某一类去重后的数量少于10000时,可以大幅提高SELECT操作的效率。
-
LowCardinality支持对String、FixedString、Date、DateTime和不包含Decimal的数组类型进行自动化的字典编码:
-
在ClickHouse中可以使用低基数类型替换原始的String类型,也可以使用低基数类型替换枚举类型
分区技巧
慎重使用分区
- 不建议大量使用分区。在很多情况下,分区并不能提高查询效率,过多地分区有可能降低性能。ClickHouse中分区功能仅仅是为数据管理提供便利,例如以分区为单位进行删除等。
高级技巧
物化视图
使用物化视图代替视图
-
物化视图会将数据写入磁盘,而视图只是一个虚拟的表,并不会真正存储数据。通过使用物化视图可以大幅提高查询速度:
物化视图和物理表类型的区别在于物化视图会自动识别底层表的变动,当底层表变动时会自动映射到物化视图中。
投影
使用投影能力
-
ClickHouse的索引满足最左原则,当未按照最左原则进行查询时,速度会变慢,投影就是一个解决该问题的方案,其实现原理是将不满足最左原则的查询条件进行固化,本质上可以理解为创建了一个按照新的顺序排列的数据副本,当查询条件满足这个副本时,自动在该副本上查询,从而实现性能加速:
位图
使用位图结构
- 使用 Bitmap 等位图结构可以节省大量存储空间,并且位图的计算效率很高。
变更数据捕获
使用内置的CDC能力获取实时数据
- ClickHouse通过MaterializeMySQL和MaterializePostgreSQL两个引擎提供MySQL和PostgreSQL的CDC集成支持。
常见报错及处理方法
解决“too many parts”异常
- too many parts是ClickHouse经常会出现的错误,出现这种错误的原因在于短期内建立了太多的分区。要解决这个问题,可以在数据进入ClickHouse前进行预排序,或者使用前边提到的缓冲区表引擎。
解决“memory limit”异常
- 内存不足,优化SQL 或者将计算下推,使用 Spark 来查询复杂 SQL。
相关文章:

ClickHouse 使用技巧总结
文章目录 数据导入、导出技巧外部文件导入导技巧使用集成表引擎导入、导出数据 建表技巧表引擎选择技巧分区键选择技巧数据结构选择技巧分区技巧 高级技巧物化视图投影位图变更数据捕获 常见报错及处理方法 数据导入、导出技巧 外部文件导入导技巧 ClickHouse作为OLAP即席分析…...

论文浅尝 | THINK-ON-GRAPH:基于知识图谱的深层次且可靠的大语言模型推理方法...
笔记整理:刘佳俊,东南大学硕士,研究方向为知识图谱 链接:https://arxiv.org/pdf/2307.07697.pdf 1. 动机 本文是IDEA研究院的工作,这篇工作将知识图谱的和大语言模型推理进行了结合,在每一步图推理中利用大…...
前端科举八股文-VUE篇
前端科举八股文-VUE篇 Vue响应式的基本原理?computed和watch的区别computed和methods的区别Slot是什么 ? 作用域插槽是什么?组件缓冲keep-alive是什么? 讲讲原理v-if,v-show的区别v-modal如何实现双向绑定组件中的data属性为什么是一个函数而不是对象…...

Websocket服务端结合内网穿透发布公网实现远程访问发送信息
文章目录 1. Java 服务端demo环境2. 在pom文件引入第三包封装的netty框架maven坐标3. 创建服务端,以接口模式调用,方便外部调用4. 启动服务,出现以下信息表示启动成功,暴露端口默认99995. 创建隧道映射内网端口6. 查看状态->在线隧道,复制所创建隧道的公网地址加端口号7. 以…...

GitHub 的底层数据库从 MySQL 5.7 无缝升级到 MySQL 8.0 的实践经验
提到 MySQL 这个数据库软件,相信大家再熟悉不过了,不论是市场流行度还是占有率一直一来都非常靠前。 那再提到 MySQL 5.7 这个具体的版本,大家是不是也同样感到非常熟悉? 相信不少个人或者团队的生产环境所用的 MySQL 数据库也曾…...
概率图模型在自然语言处理中的应用
概率图模型在自然语言处理(NLP)中的应用广泛且重要,它结合了概率论和图论,为处理复杂系统中变量之间的概率依赖关系提供了有效的建模方法。以下是概率图模型在NLP中的几个主要应用,结合参考文章中的相关信息进行详细说明: 核心概念与分类: 概率图模型的核心思想是利用图…...

AI网络爬虫:对网页指定区域批量截图
对网页指定区域批量截图,可以在deepseek的代码助手中输入提示词: 你是一个Python编程专家,一步一步的思考,完成一个对网页指定区域截图的python脚本的任务,具体步骤如下: 设置User-Agent: Mozilla/5.0 (…...

centos系统清理docker日志文件
centos系统清理docker日志文件 1.查看docker根目录位置2.清理日志 1.查看docker根目录位置 命令:docker info ,将Docker Root Dir 的值复制下来。如果目录中包含 等特殊符号的目录,需要转义 2.清理日志 创建文件:vim docker_logs_clean.…...

算法金 | Python 中有没有所谓的 main 函数?为什么?
大侠幸会,在下全网同名[算法金] 0 基础转 AI 上岸,多个算法赛 Top [日更万日,让更多人享受智能乐趣] 定义和背景 在讨论Python为何没有像C或Java那样的明确的main函数之前,让我们先理解一下什么是main函数以及它在其他编程语言…...

RocketMQ相关知识知多少
一、RocketMQ的定义 官网网址:领域模型概述 | RocketMQ Apache RocketMQ 自诞生以来,因其架构简单、业务功能丰富、具备极强可扩展性等特点被众多企业开发者以及云厂商广泛采用。历经十余年的大规模场景打磨,RocketMQ 已经成为业内共识的金…...
C++vector部分实现及感悟
myvector.h #pragma once #include<assert.h> #include<iostream> #include<vector> using namespace std; namespace bit {template<class T>//必须使用typename场景\template<typename T>与template<class T>一般情况下这两个通用&#…...
MySql什么时候表锁or行锁?
文章目录 锁的基本概念共享锁(读锁)排他锁(写锁)锁的兼容性锁的升级和降级 全局锁、表锁、行锁全局锁表锁行锁 何时使用行锁何时使用表锁额外思考: 在数据库的世界里,性能优化是一个永恒的话题。MySQL作为广…...

Stable Diffusion WebUI详细使用指南
Stable Diffusion WebUI(AUTOMATIC1111,简称A1111)是一个为高级用户设计的图形用户界面(GUI),它提供了丰富的功能和灵活性,以满足复杂和高级的图像生成需求。由于其强大的功能和社区的活跃参与&…...
Android中focusableInTouchMode会导致第一次点击事件失效
我们很多时候会对某些View设置点击事件,但是,当对这个View同时设置了focusableInTouchModetrue时,第一次点击事件会被消费为为此View获取焦点。 <Viewandroid:id"id/v_click"android:layout_width"match_parent"andr…...

C语言| 输出菱形*(梳理篇II)
C语言| 输出菱形*-CSDN博客 凡事还是得自己独立思考后,写一遍程序才能发现问题所在。 容易犯的错误: 【完整程序注释】 运行结果 /* 输出菱形 1 总行数 n为奇数,分上三角形下三角形,只考虑左边的空格和星号* 2 上三角形 行数…...
conda使用
因为时不时搜索conda操作,就想着逐步将先前的conda使用整理在一起,并添加新的内容。 目录 0.简介1.常用操作场景1.1 取消终端conda自动启动 0.简介 1.常用操作场景 1.1 取消终端conda自动启动 安装conda后取消命令行前出现的base,取消每次…...

适合小白学习的项目1949java图片管理系统 Myeclipse开发mysql数据库web结构java编程计算机网页项目
一、源码特点 java图片管理系统 是一套完善的web设计系统,对理解JSP java编程开发语言有帮助采用了java设计,系统具有完整的源代码和数据库,系统采用web模式,系统主要采用B/S模式开发。开发 环境为TOMCAT7.0,Myeclipse8.5开发&…...

Jmeter实战教程入门讲解
前言 通过前面对Jmeter元件的讲解,大家应该都知道常用元件的作用和使用了。编写Jmeter脚本前我们需要知道Jmeter元件的执行顺序,可以看看我这篇性能测试学习之路(三)—初识Jmeter来了解下。下面我将以工作中的一个简单的实例带大…...
Flutter 中的 PrimaryScrollController 小部件:全面指南
Flutter 中的 PrimaryScrollController 小部件:全面指南 Flutter 是一个由 Google 开发的跨平台 UI 框架,它允许开发者使用 Dart 语言构建高性能、美观的移动、Web 和桌面应用。在 Flutter 的滚动机制中,PrimaryScrollController 起着至关重…...
Windows线程同步的四种方式和区别
1. Windows线程同步的四种方式 2. 区别 Critical Section更多强调的是保护,Event对象、Mutex对象与Semaphore对象更多的强调的是同步;Critical Section对象是无法设置等待超时的,而其他三个对象则可以设置等待超时,从这一点来讲…...

深度学习在微纳光子学中的应用
深度学习在微纳光子学中的主要应用方向 深度学习与微纳光子学的结合主要集中在以下几个方向: 逆向设计 通过神经网络快速预测微纳结构的光学响应,替代传统耗时的数值模拟方法。例如设计超表面、光子晶体等结构。 特征提取与优化 从复杂的光学数据中自…...
SciencePlots——绘制论文中的图片
文章目录 安装一、风格二、1 资源 安装 # 安装最新版 pip install githttps://github.com/garrettj403/SciencePlots.git# 安装稳定版 pip install SciencePlots一、风格 简单好用的深度学习论文绘图专用工具包–Science Plot 二、 1 资源 论文绘图神器来了:一行…...

【入坑系列】TiDB 强制索引在不同库下不生效问题
文章目录 背景SQL 优化情况线上SQL运行情况分析怀疑1:执行计划绑定问题?尝试:SHOW WARNINGS 查看警告探索 TiDB 的 USE_INDEX 写法Hint 不生效问题排查解决参考背景 项目中使用 TiDB 数据库,并对 SQL 进行优化了,添加了强制索引。 UAT 环境已经生效,但 PROD 环境强制索…...

论文浅尝 | 基于判别指令微调生成式大语言模型的知识图谱补全方法(ISWC2024)
笔记整理:刘治强,浙江大学硕士生,研究方向为知识图谱表示学习,大语言模型 论文链接:http://arxiv.org/abs/2407.16127 发表会议:ISWC 2024 1. 动机 传统的知识图谱补全(KGC)模型通过…...
鱼香ros docker配置镜像报错:https://registry-1.docker.io/v2/
使用鱼香ros一件安装docker时的https://registry-1.docker.io/v2/问题 一键安装指令 wget http://fishros.com/install -O fishros && . fishros出现问题:docker pull 失败 网络不同,需要使用镜像源 按照如下步骤操作 sudo vi /etc/docker/dae…...
Python网页自动化Selenium中文文档
1. 安装 1.1. 安装 Selenium Python bindings 提供了一个简单的API,让你使用Selenium WebDriver来编写功能/校验测试。 通过Selenium Python的API,你可以非常直观的使用Selenium WebDriver的所有功能。 Selenium Python bindings 使用非常简洁方便的A…...
智能职业发展系统:AI驱动的职业规划平台技术解析
智能职业发展系统:AI驱动的职业规划平台技术解析 引言:数字时代的职业革命 在当今瞬息万变的就业市场中,传统的职业规划方法已无法满足个人和企业的需求。据统计,全球每年有超过2亿人面临职业转型困境,而企业也因此遭…...
LUA+Reids实现库存秒杀预扣减 记录流水 以及自己的思考
目录 lua脚本 记录流水 记录流水的作用 流水什么时候删除 我们在做库存扣减的时候,显示基于Lua脚本和Redis实现的预扣减 这样可以在秒杀扣减的时候保证操作的原子性和高效性 lua脚本 // ... 已有代码 ...Overridepublic InventoryResponse decrease(Inventor…...

EasyRTC音视频实时通话功能在WebRTC与智能硬件整合中的应用与优势
一、WebRTC与智能硬件整合趋势 随着物联网和实时通信需求的爆发式增长,WebRTC作为开源实时通信技术,为浏览器与移动应用提供免插件的音视频通信能力,在智能硬件领域的融合应用已成必然趋势。智能硬件不再局限于单一功能,对实时…...
Yii2项目自动向GitLab上报Bug
Yii2 项目自动上报Bug 原理 yii2在程序报错时, 会执行指定action, 通过重写ErrorAction, 实现Bug自动提交至GitLab的issue 步骤 配置SiteController中的actions方法 public function actions(){return [error > [class > app\helpers\web\ErrorAction,],];}重写Error…...