clickhousesql(clickhousesqlalchemy)
本篇文章给大家谈谈clickhousesql,以及clickhousesqlalchemy对应的知识点,希望对各位有所帮助,不要忘了收藏本站喔。
本文目录一览:
- 1、分布式物化视图在clickhouse如何实现?
- 2、clickhouse常见的一些问题
- 3、ClickHouse数据压缩
- 4、clickhouse优化最佳实践(易企秀)
- 5、clickhouse使用一些优化和经验
分布式物化视图在clickhouse如何实现?
物化视图在数据层面做指标大宽表有着举足轻重的作用,分布式物化视图是对物化视图存储的数据进行分布式读取。
之前我们有一个介绍过物化视图的文章,详情请点击:clickhouse物化视图的应用,这里我们已经介绍过物化视图是什么,如何使用。
下面我们这里来介绍一下分布式物化视图的使用。我们这里使用的是分布式clickhouse集群。版本是:20.3.10.75,下面我们就来详解分布式物化视图在clickhouse的使用。
1:首先我们还是来建立三个表。
2:分别在不同的节点插入数据,我这里有两个节点,我们每个节点插入2条数据。 节点1如下:
节点2如下:
3:插入完数据之后,我们去每个节点查询,因为我们需要读所有的数据,则我们需要建一下分布式表来读数据。下面是建桐哗分布式表的语句。
建立好上面的分布式表之后就能读集群所有节点的数据了。我这里贴一下user表的所有数据。
4:上面是基础的数据表,这里我们开始建物化视图表。下面的sql是把用户表,用户信息表,绑定表进行组合成大宽表,下面的脚本我们是在每个节点上存了一份快照,实际业务中我们是写誉前数据到一个节点局虚行,不会一份数据存多份。我这里做例子就这么使用。
5:上面的物化视图表我们建立好了,下面我们在物化视图表上建分布式表。
好了,到这里我们已经可以通过物化视图分布式表读每个节点的物化视图了,业务中我们基于物化视图来做大宽表,读取物化视图分布式表是非常常见的。我之前记得之前clickhouse物化视图在微信的应用这篇文章也是比较类似。
总结 :
[img]clickhouse常见的一些问题
一般情况下,如果不是主动使用systemctl stop clickhouse-server 停止clickhouse
而是使用kill -9 pid关闭clickhouse,或者异常奔溃,那么如果一切正常的情况下clickhouse server 10s检测进程,自动重启。
登录机器cat /etc/cron.d/clickhouse-server
*/10 * * * * root (which service /dev/null 21 (service clickhouse-server condstart ||:)) || /etc/init.d/clickhouse-server condstart /dev/null 21
默认会燃租10s检测一下服务进程是否正常,否则重启,检测时间可以调。/etc/init.d/clickhouse-server
在执行分布式DDL的时候出现这个问瞎段袭题一般是有一个节点处于假死状态,但是节点又没有完全奔溃,一般报错如下
Code: 159. DB::Exception: Received from xxxxx:29000. DB::Exception: Watching task /clickhouse/task_queue/ddl/query-0000xxxxxxx is executing longer than distributed_ddl_task_timeout (=180) seconds. There are 1 unfinished hosts (0 of them are currently active), they are going to execute the query in background.
distributed_ddl_task_timeout 执行超过了默认的180s。
首先检查异常节点机器网络,磁盘等信息,然后检查ck状态。一般都磨兄是磁盘满了或者网络问题,很少有zk集群出问题。处理方式的话都是清理磁盘和修复网络。
Code: 458, e.displayText() = DB::ErrnoException: Cannot unlink file /data2/clickhouse/store/488/488da1e0-a9ee-4191-8376-0daaa4e0314d/format_version.txt, errno: 2, strerror: No such file or directory (version 21.3.4.25 (official build))
clickhouse 集群在建分布式表的时候出现 clickhouse zookeeper All connection tries failed
如果配置没啥问题,zk和ck集群也没啥问题,重启下zk即可恢复
查询出现AST is too big. Maximum: 500000
程序报错AST is too big. Maximum: 500000,语法树元素个数超过限制错误,说明查询sql很长很复杂,一般情况不会有,要木优化sql,要木修改集群配置
在user.xml 添加
max_ast_elements10000000/max_ast_elements
max_expanded_ast_elements10000000/max_expanded_ast_elements
报错 DB::Exception: Replica xxxxx already exists 。
CK会对同一个block保证重复插入的insert的幂等性,会检测重复,默认会去重,使用 insert_deduplicate 配置。如果不需要去重则可以使用 SET insert_deduplicate=0 ,但不推荐这样做。
查询超过了限制的时间(60s),需要优化sql,或者做预聚合
一次写入的分区数超过100,一般情况下不会出现一次写操作写100个分区的情况,解决方法1:查看写入的数据是否异常,为啥会写100个分区,一般是按时间分区,是不是时间解析错误了。解决方案2:在user.xml配置文件中添加max_partitions_per_insert_block配置项
ClickHouse数据压缩
ClickHouse支持多种方式的数据压缩:LZ4和ZSTD。
关于压缩算法的测试,见 这篇文章 。简而言之,LZ4在速度上会更快,但是压缩率较低,ZSTD正好相反。尽管ZSTD比LZ4慢,但是相比传统的压缩方式Zlib,无论是在压缩效率还是速度上,都可以作为Zlib的替代品。
下面我们对比一下这两种压缩方式。压缩测试所用的表(lineorder)结构和数据来自 这里 。未压缩的数据集是680GB。
把上述数据加载到ClickHouse后,默认的LZ4压缩算法下,数迅拿据容亩老搭量是184G(压缩到27%),而ZSTD达到了135GB(压缩到20%)。
如果想要使用ZSTD压缩方式,修改为如下配置即可:
压缩比率对比
压缩后的查询性能如何,我们来跑如下查询看看:
为了保持客观,查询测试会跑两次,第一次是冷数据请求,这次的数据没有被操作系统缓存,第二次是含态热数据情求,这次的数据已经被操作系统的内存缓存了。
LZ4的性能如下:
ZSTD性能如下:
冷数据查询情况下,两者区别不大,因为消耗在IO方面的时间,远大于消耗在数据解压缩上面的时间。
热数据请求下,LZ4会更快,此时IO代价小,数据解压缩成为性能瓶颈。
综上所述,默认的LZ4压缩方式,会给我们提供更快的执行效率,但是同时需要占用较多的磁盘容量。
ClickHouse抛开高效的SQL执行效率,数据压缩比率也是一个非常喜人的地方。使用Hadoop Node低配置服务器,再加上ClickHouse优秀的压缩性能,单机容量轻松可达几十T,推荐直接使用默认的LZ4压缩方式,用可以接受的少量空间来换查询执行效率的提升。
clickhouse优化最佳实践(易企秀)
Clickhouse堪称OLAP领域的黑马,最近发布的几个版本在多表关联分析上也有了极大的性能提升,尤其是还引入了MaterializeMySQL Database Engine做到了实时对齐业务线mysql中的数据。
采样修饰符只有在mergetree engine表中才有效,且在创建表时需要指定采样策略;
clickhouse不支持设置多数据缺喊目录,为了提升数据io性能,可以挂载虚拟券组,一个券组绑定多块物理磁盘提升读写性能;多数查询场景SSD盘会比普通机械硬盘快2-3倍。
新版clickhouse提供了一个实验性的功能,那就是我们可以将clickhouse伪装成mysql的一个备库去实时对齐mysql中的数据,当mysql库表数据发生变化时会实时同步到clickhouse中;这样就省掉了单独维护实时spark/flink任务读取kafka数据再存入clickhouse的环节,大大降低了运维成本提升了效率。
为了避免因个别慢查询引起的服务雪崩问题,除了可以为单个查询设置超时以外,还可以配置周期熔断;在一个查询周伏告野期内,如果用户频繁进行慢查询操作超出规定阈值后将无法继续进行查询操作:
clickhouse权限管理与资源隔离
clickhouse高级功能上线之mysql实时数据同步
clickhouse如何友脊构建复杂数据模型
clickhouse sql规范
clickhouse使用一些优化和经验
1,查询强烈要求带上分区键过滤和主键过滤,如 where day = today() and itime = now()。
2,建表的时候,选择合适的分区键和排序键是优化的关键。
3,如果不允许重复主键(且不要求去重时效性),建议使用表类型:ReplicatedReplacingMergeTree 建表语句可参考 ,注意只能保证单节点的数据不重复,无法保证集群的。
4,如果要对某一列过滤,且该列非partition key和orderby key, 且该列过滤前后数据量差异较大,建议使用prewhere clause过滤。参考: 。
5,日期和时间使用Date, DateTime类型,不要用String类型。
6,建表时,强烈建议低基数(基数小于10000)且类型为String的列,使用 LowCardinality 特性,例如国家(country),操作系统(os)皆可用LowCardinality。查询效益提高可以40~50%,具体参考 。
7,为了使复杂查询尽量本地完成,提前减小数据量和网络传输,加快查询速度州升,创建分布式表时,尽量按照主键hash分shard。例如欲加快select count(distinct uid) from table_all group by country, os的查询速度. 创建分布式表table_all时,shard key为cityHash64(country, os),hash函数参考 。
8,计算不同维度组合的指标值时,用with rollup或with cube替代union all子句。
9,建表时,请遵守命名规范:分布式表名 = 本地表名 + 后缀"_all"。 select请直接操作分布式表。
10,官方已经指出Nullable类型几乎总是会拖累性能,因为存储Nullable列时需要创建一个额外的文件来存储NULL的标记,并且Nullable列无法被索引。因此除非极特殊情况,应直接使用字段默认值表示空,或者自行指定一个在拿脊业务中无意义的值(例如用-1表示没有商品ID)
11,稀疏索引不同于mysql的B+树,不存在最左的原则,所以在ck查询的时候,where条件中,基数较大的列(即区分度较高的列)在前,基数较小的列(区分度较低的列)在后。
12,多表Join时要满足小表在右的原则,右表关联时被加载到内存中与左表进行比较
13,多维分析, 查询列不宜过多, 过滤条件带上分区筛选 (select dim1, dim2, agg1(xxx), agg2(xxx) from table where xxxx group by dim1, dim2 )
14,禁止SELECT *, 不能拉取原始数据!!!! (clickhouse不是数据仓库, 纯粹是拉原始表数据的查询应该禁止,如 select a, b, c, f, e, country from xxx )
分区键和排序键理论上不能修改,在建表建库的时候尽量考虑清册敏老楚 。
0,事实表必须分区,分区粒度根据业务特点决定,不宜过粗或过细。我们当前都是按天分区,按小时、周、月分区也比较常见(系统表中的query_log、trace_log表默认就是按月分区的)。
1,分区键能过滤大量数据,分区键建议使用toYYYYMMDD()按天分区,如果数据量很少,100w左右,建议使用toYYYYMM()按月分区,过多的分区会占用大量的资源,会对集群的稳定性造成很大的影响。
2,分区键必须使用date和datetime字段,避免string类型的分区键
3,每个sql必须要用分区键,否则会导致大量的数据被读取,到了集群的内存限制直接拒绝
4,排序键也是一个非常重要的过滤条件,考虑到ck是OLAP 库,排序键默认也是ck的主键,loap库建议分区键要使用基数比较少的字段,比如country就比timestramp要好。
5,不要使用过长的分区键,主键 。
6,CK的索引非MySQL的B树索引,而是类似Kafka log风格的稀疏索引,故不用考虑最左原则,但是建议基数较大的列(即区分度较高的列)在前,基数较小的列(区分度较低的列)在后。另外,基数特别大的列(如订单ID等)不建议直接用作索引。
分区数过多会导致一些致命的集群问题。 不建议分区数粒度过细,不建议分区数过多 ,经验来看,10亿数据建议1-10个分区差不多了,当然需要参考你的硬件资源如何。
1,select 查询性能降低,分区数过多会导致打开大量文件句柄,影响集群。
2,分区数过多会导致集群重启变慢,zk压力变大,insert变慢等问题。
关于clickhousesql和clickhousesqlalchemy的介绍到此就结束了,不知道你从中找到你需要的信息了吗 ?如果你还想了解更多这方面的信息,记得收藏关注本站。