ClickHouse接入指南和排坑日记

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'