clickHouse接入指南和排坑日记
clickHouse分区和分片详解
1. ClickHouse简介和特点
ClickHouse是一个面向联机分析处理(OLAP)的开源的面向列式存储的DBMS,简称CK, 与Hadoop, Spark相比,ClickHouse很轻量级,由俄罗斯第一大搜索引擎Yandex于2016年6月发布, 开发语言为C++。上一款战斗名族开源的还是火爆全球的nginx。
ClickHouse优点:
- 不依赖Hadoop生态圈,引入jar开箱即用;
- 不同于hbase的列族式存储,ClickHouse是完全列式存储,除了数据本身以外不存在其他额外数据;
- 支持向量化计算vectorization,比如字符串函数( toUpper() )和数组函数,底层基于SIMD命令(即单条指令操作多条数据——原理即在CPU 寄存器层面实现数据的并行操作);
- 超高的数据压缩率,能达到15倍以上;
- 查询速度快,跑分要超过很多流行的商业MPP数据库软件,例如Vertica, greenplum等。底层会把cpu使用压榨到极致,默认单查询也会使用服务器核数的一半cpu计算;
- 支持分布式计算,在ClickHouse中,数据保存在不同的shard上,查询可以并行地在所有shard上进行处理;
- 丰富的函数支持,比如近似函数,抽样函数,时间窗口函数,用户留存retention函数;
- 丰富的文档和社区活跃度,各种框架和各种语言的兼容。
ClickHouse缺点:
- 不支持事务;
- 不支持真正的删除和更新操作,需要使用mutation操作,造成阻塞,删除和更新需要解压缩Block 然后计算然后重新压缩储存;
- 不支持高并发,官方建议单节点qps最大为100;
- join写法比较特殊,虽然最新版已支持类似SQL的join,但性能不好;
- 小批量高频率的insert可能导致merge报错;
- 不支持udf函数;
具体介绍和安装教程可以参考这篇文章
clickhouse官方地址
clickhouse中文文档地址
2. java接入clickHouse,jdbc方式
- pom依赖,这里有官方版和社区版,建议使用官方版:
<dependency>
<groupId>ru.yandex.clickhouse</groupId>
<artifactId>clickhouse-jdbc</artifactId>
<version>0.2.4</version>
<exclusions>
<exclusion>
<artifactId>jackson-databind</artifactId>
<groupId>com.fasterxml.jackson.core</groupId>
</exclusion>
</exclusions>
</dependency>
-
jdbc连接配置,connection模式
1.clickhouse用户信息配置,url参考mysql配置,jdbc驱动使用clickhouse,例如我的url配置如下:
jdbc:clickhouse://10.101.82.84:8123/default?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false
@Component
@Data
@ConfigurationProperties(prefix = "spring.datasource-clickhouse")
public class ClickHouseConfig {
private String url;
private String username;
private String password;
}
2.创建connection连接:
public Connection getConn() throws ClickHouseException {
try {
return DriverManager.getConnection(clickHouseConfig.getUrl(),
clickHouseConfig.getUsername(), clickHouseConfig.getPassword());
} catch (SQLException ex) {
log.error("connect clickhouse fail,exception is ", ex);
throw new ClickHouseException("Connect clickhouse fail");
}
}
3.获取连接,创建createStatement,查询clickHouse,解析resultSet:
List<Map<String, Object>> result = new ArrayList<>();
try {
@Cleanup Connection connection = getConn();
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
Map<String, Object> data = new HashMap<>();
//open_id, action_time, group_by
String openId = resultSet.getString("open_id");
data.put("open_id", openId);
result.add(data);
}
} catch (SQLException ex) {
log.error("executeQuery fail, exception is ", ex);
throw new ClickHouseException("executeQuery fail!");
}
return result;
这里在Connection前面使用了 @Cleanup注解,这个是lombok的注解,可以关闭指定资源。
- jdbc连接池配置,以DruidDataSource为例
连接池配置:
driver-class-name: ru.yandex.clickhouse.ClickHouseDriver
type: com.alibaba.druid.pool.DruidDataSource
#配置间隔多久才进行一次检测,检测需要关闭的空闲连接
timeBetweenEvictionRunsMillis: 60000
#配置一个连接在池中最小生存的时间
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 --注意,这里和mysql不一样,SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
clickhouse相关配置:
url: ${MYSQL_CLICKHOUSE_URL}
username: ${MYSQL_CLICKHOUSE_NAME}
password: ${MYSQL_CLICKHOUSE_PASSWORD}
druid:
#初始化大小
initialSize: 5
#最小值
minIdle: 5
#最大值
maxActive: 20
#最大等待时间,配置获取连接等待超时,时间单位都是毫秒ms
maxWait: 60000
然后就可以像使用mysql一样去使用clickhouse了。针对库名表名做对应的操作。
关于jdbc使用方式,这里建议使用connection的方式,或者自己简单的写一个简单的线程池,因为clickhouse的server不像mysql server那样,是长连接,通过维护thread维护连接,clickhouse的链接都是http短链接,如果复用的话,可能会遇到拿到被释放链接的线程,请求就会失败。所以建议使用connection方式操作clickhouse,如果图简便使用线程池,一定要做好重试机制
3. clickHouse常用sql
clickhouse基本兼容所有的mysql常用的sql语法,具体的可以参考官网上sql Reference:
4. clickhouse函数
clickhouse有很多丰富的算术函数,逻辑函数,json函数和高阶函数等,使用clickhouse时候进行复杂业务查询之前,一定要先在官方文档上找一下有没有可以简化查询的函数使用。比如有专门的留存函数(retention函数),漏斗函数(windowFunnel)等,以windowFunnel函数为例,介绍一下高阶函数的使用案例:
比如现在我们做活动,发放优惠券,我需要统计一定区间内,进入我的小程序,领取优惠券,浏览指定商品,并且下单,依次进行这些操作的所有用户的总数,用来分析这个活动的效果。如果自己去实现,还是比较麻烦的,尤其是数据量比较大并且条条框框比较多的时候。而windowFunnel则很简单。
windowFunnel(864000)(abs(action_time), action_type = ‘app_launch’ and country = ‘China’, action_type = ‘get_coupon’ and money >50 ,action_type = ‘add_order’ and money >50)
第一个参数是窗口期,也就是一次链路按照顺序完成的最大时间间隔;
第二个参数,首先第一个abs(action_time),这个是判定时间排序的字段,必须是正整数或者dateTime,第二个参数是第一个事件发生的条件,可以像sql那样追加条件;后面依次是第二个,第三个,第n个事件发生的条件;
而他最后返回的就是每个纬度达到事件的个数。比如我窗口期内只满足第一个事件,返回的就是1,如果我满足3个事件,则返回3。
类似强大的函数还有很多,大家使用clickhouse一定要多参考官方文档,网上现阶段资料还是较少:
5. clickhouse常见表结构操作
针对表的操作,本地表和主表(试图表)都需要执行一次sql,尽量少进行alter操作,会产生mutation操作,耗时长并且占用集群资源。
1.修改表的列名
ALTER TABLE ka_20_4bb7dea22e55415fbcec2778d2417eeb.t_event_local (on cluster default_cluster 根据是否分布式表)RENAME COLUMN aaaa TO aaa2
2.增加列
ALTER TABLE 表名称 ON 集群名称 ADD COLUMN 列名称 类型 DEFAULT 注解
3.删除列
alter table 表名称 ON 集群名称 drop column 列名称;
4.修改字段类型
ALTER table 表名称 ON 集群名称 MODIFY COLUMN 列名称 数据类型;
5.删除库
drop database IF EXISTS base_db (on CLUSTER cluster)
6.删除表
DROP table db.table ON CLUSTER cluster_name;
6. clickhouse系统system表sql
1.查看表的分区和数据记录相关信息
select * from system.parts where database = 'aaa' and table = 'bbbb';
2.查看集群名称和集群内机器信息
select * from system.clusters
3.查看库下所有的表
select name, create_table_query, partition_key from system.tables where database = '%s'
4.查看表的属性列和属性信息
select * from system.columns where database='aaa' and table='bbb'
5.查看zookeeper日志
select * from system.zookeeper where path='/clickhouse/tables/shard_01/aaa/log'