Clickhouse -- SpringBoot集成ck和chproxy负载均衡

简介

  我自己搭建的ck是两节点的，两分片一备份。写入数据是生成随机数取余机器数来随机分配机器写入本地表的，然后建的分布式表供查询，外加一个web服务集成springboot、提供对外服务。
ck查询的负载均衡没有使用chproxy和nginx，因为我们项目比较简单，就没弄额外的组件了，直接在代码中控制了，下面是集成步骤。

1.pom

<dependency>
    <groupId>ru.yandex.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.2.4</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.60</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.10</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
 <dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-log4j12</artifactId>
    <version>1.6.1</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
    <exclusions>
        <exclusion>
            <artifactId>logback-classic</artifactId>
            <groupId>ch.qos.logback</groupId>
        </exclusion>
    </exclusions>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.10</version>
</dependency>

2.配置文件

  因为是两台机器，这里是用了两个datasource，然后随机分配其中一个去查询

server:
  port: 8086
  servlet:
    context-path: /ck-module
  tomcat:
    uri-encoding: utf-8
spring:
  http:
    encoding:
      charset: UTF-8
      enabled: true
      force: true
  datasource:
    clickhouse220:
      type: com.alibaba.druid.pool.DruidDataSource
      jdbcUrl: jdbc:clickhouse://xxx:8123/xxx
      username: default
      password: 
      driver-class-name: ru.yandex.clickhouse.ClickHouseDriver
      druid:
        initial-size: 5
        min-idle: 5
        max-active: 20
        max-wait: 60000
        time-between-eviction-runs-millis: 60000
        min-evictable-idle-time-millis: 300000
        test-while-idle: true
        validation-query: select 1
        pool-prepared-statements: true
        max-pool-prepared-statement-per-connection-size: 20
    clickhouse200:
      type: com.alibaba.druid.pool.DruidDataSource
      jdbcUrl: jdbc:clickhouse://xxx:8123/xxx
      username: default
      password: 
      driver-class-name: ru.yandex.clickhouse.ClickHouseDriver
      druid:
        initial-size: 5
        min-idle: 5
        max-active: 20
        max-wait: 60000
        time-between-eviction-runs-millis: 60000
        min-evictable-idle-time-millis: 300000
        test-while-idle: true
        validation-query: select 1
        pool-prepared-statements: true
        max-pool-prepared-statement-per-connection-size: 20
ck:
  200ip: xxx
  220ip: xxx

3.Datasource配置

package com.bigblue.clickhouse.config;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

/**
 * @Author: TheBigBlue
 * @Description:
 * @Date: 2020/12/24
 */
@Configuration
public class CK200Config {

    @Bean(name = "ck200DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.clickhouse200")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "ck200Template")
    public JdbcTemplate ck200Template(@Qualifier("ck200DataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
}

package com.bigblue.clickhouse.config;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

/**
 * @Author: TheBigBlue
 * @Description:
 * @Date: 2020/12/24
 */
@Configuration
public class CK220Config {

    @Bean(name = "ck220DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.clickhouse220")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "ck220Template")
    public JdbcTemplate ck220Template(@Qualifier("ck220DataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
}

4.负载均衡代码

package com.bigblue.clickhouse.service;

import com.alibaba.fastjson.JSONArray;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;


/**
 * @Author: TheBigBlue
 * @Description:
 * @Date: 2020/7/14
 */
@Service
public class WContentService {

    private static final Logger LOGGER = LoggerFactory.getLogger(WContentService.class);

    @Autowired
    @Qualifier("ck220Template")
    private JdbcTemplate ck220Template;

    @Autowired
    @Qualifier("ck200Template")
    private JdbcTemplate ck200Template;

 	@Value("${ck.200ip}")
    private String ck200IP;

 	@Value("${ck.220ip}")
    private String ck220IP;
    
    /**
     * @Author: TheBigBlue
     * @Description: 负载均衡，差不同机器
     * @Date: 2020/12/25
     **/
    private JdbcTemplate getSpecTemplate(String uuid) {
        String[] ips = new String[]{ck200IP, ck220IP};
        JdbcTemplate[] templates = new JdbcTemplate[]{ck200Template, ck220Template};
        int index = (int) ((Math.random() * 9 + 1)) % 2;
        LOGGER.info("UUID: 【{}】，当前查询,Index: 【{}】, 分配机器：【{}】", uuid, index, ips[index]);
        return templates[index];
    }
}

5.使用BalancedClickhouseDataSource做负载均衡

未完待续

6.使用ChProxy做负载均衡

1. 下载：chproxy
   

2. chproxy是clickhouse官方推荐的代理工具, 只需要配置config.yml即可

hack_me_please: true
log_debug: true
 
# chproxy的访问接口，你会通过这里配置的ip:port来访问你的chproxy
server:
  http:
    listen_addr: ":3380"
    # 允许访问的网络评断
    # allowed_networks: ["10.0.0.0/8", "146.0.0.0/100"]
    read_timeout: 3m
# 连接到clickhouse的集群配置
clusters:
  # 这个clickhouse的连接的集群是支持写操作
  - name: "writer-cluster"
    # clickhouse的集群的地址，如果是全分布式集群，那么多个节点之间使用逗号隔开即可
    nodes: ["node01:8123","node02:8123","node03:8123"]
    # clickhouse的用户配置
    users:
      - name: "default"
        password: ""
  # 这个clickhouse的连接集群只读操作      
  - name: "readonly-cluster"
    # clickhouse的集群的地址，如果是全分布式集群，那么多个节点之间使用逗号隔开即可
    nodes: ["node01:8123","node02:8123","node03:8123"]
    # clickhouse的用户配置
    users:
      - name: "clickhouse_r"
        password: "clickhouse_r"
 
# chproxy调用clickhouse的账户
users:
  # 用户[写入权限]，使用此用户连接clickhouse可以进行写入操作
  - name: "default"
    password: ""
    to_cluster: "writer-cluster"
    to_user: "default"
    allow_cors: true
    
  - name: "clickhouse_r"
    password: "clickhouse_r"
    to_cluster: "readonly-cluster"
    to_user: "clickhouse_r"
    allow_cors: true

3. 启动：

nohup /data/services/chproxy-1.16.3/chproxy \
-config=/data/services/chproxy-1.16.3/config.yml \
>> /data/services/chproxy-1.16.3/error.log 2>&1 &