Clickhouse集群

1.注入三个clickhouse节点
package pro.nbbt.xulian.business.gps.config;

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import ru.yandex.clickhouse.ClickHouseDataSource;
import ru.yandex.clickhouse.settings.ClickHouseProperties;

@Configuration
@Slf4j
@Component
public class ClickhouseClusterConfig {
@Value("${clickhouseCluster.ip}")
private String ip;
@Value("${clickhouseCluster.port}")
private int port;
@Value("${clickhouseCluster.userName}")
private String userName;
@Value("${clickhouseCluster.password}")
private String password;
@Value("${clickhouseCluster.database}")
private String database;

@Bean(name="clickhouseTemplate0")
public JdbcTemplate clickhouseTemplate0(){

    ClickHouseProperties properties = new ClickHouseProperties();
    properties.setSocketTimeout(5000);
    properties.setUser(userName); // 设置用户名
    properties.setPassword(password); // 设置密码

    ClickHouseDataSource dataSource = new ClickHouseDataSource("jdbc:clickhouse://"+ip.split(",")[0]+":"+port+"/"+database, properties.asProperties());

    return new JdbcTemplate(dataSource);
}

@Bean(name="clickhouseTemplate1")
public JdbcTemplate clickhouseTemplate1(){

    ClickHouseProperties properties = new ClickHouseProperties();
    properties.setSocketTimeout(5000);
    properties.setUser(userName); // 设置用户名
    properties.setPassword(password); // 设置密码

    ClickHouseDataSource dataSource = new ClickHouseDataSource("jdbc:clickhouse://"+ip.split(",")[1]+":"+port+"/"+database, properties.asProperties());

    return new JdbcTemplate(dataSource);
}

@Bean(name="clickhouseTemplate2")
public JdbcTemplate clickhouseTemplate2(){

    ClickHouseProperties properties = new ClickHouseProperties();
    properties.setSocketTimeout(5000);
    properties.setUser(userName); // 设置用户名
    properties.setPassword(password); // 设置密码

    ClickHouseDataSource dataSource = new ClickHouseDataSource("jdbc:clickhouse://"+ip.split(",")[2]+":"+port+"/"+database, properties.asProperties());

    return new JdbcTemplate(dataSource);
}

}
2.将节点放入pool池子
package pro.nbbt.xulian.business.gps.config;

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import java.util.concurrent.BlockingQueue;
import java.util.concurrent.LinkedBlockingQueue;

@Component
@Slf4j
public class CLickhouseClientPool {
private static final BlockingQueue pool = new LinkedBlockingQueue<>(3);

@Autowired
private ApplicationContext applicationContext;
@PostConstruct
private void initializePool() {
    try {
        for (int i = 0; i < 3; i++) {
            JdbcTemplate jdbcTemplate = (JdbcTemplate) applicationContext.getBean("clickhouseTemplate"+String.valueOf(i));;
            pool.offer(jdbcTemplate);
        }
        log.debug("初始化clickhouse客户端成功：{}", pool);

    } catch (Exception e) {
        e.printStackTrace();
    }
}

public JdbcTemplate acquire(){
    try {
        JdbcTemplate jdbcTemplate = pool.take();
        pool.offer(jdbcTemplate);
        return jdbcTemplate;
    } catch (InterruptedException e) {
        log.error("=====================clickhouse获取连接中断异常====================");
        return null;
    }
}

}

3.clickhouse负载均衡
package pro.nbbt.xulian.business.gps.config;

import cn.hutool.extra.spring.SpringUtil;
import org.springframework.jdbc.core.JdbcTemplate;

public class ClickhouseClusterUtil {

//轮询负载均衡
public static JdbcTemplate get() {
    CLickhouseClientPool bean = SpringUtil.getBean(CLickhouseClientPool.class);
    JdbcTemplate clickHouseTemplate = bean.acquire();
    return clickHouseTemplate;
}

//设备id负载均衡
public static JdbcTemplate getByKey(String key) {
    JdbcTemplate jdbcTemplate = SpringUtil.getBean("clickhouseTemplate"+key);
    return jdbcTemplate;
}

}
4.clickhouse插入数据
@Override
public Boolean saveBatchLogsForCk(List preLogVoList) {
List sysLogs = preLogVoList.stream()
.map(pre -> {
SysLog2 log = new SysLog2();
log.setType(CommonConstants.STATUS_LOCK);
log.setTitle(pre.getInfo());
log.setException(pre.getStack());
log.setParams(pre.getMessage());
log.setCreateTime(LocalDateTime.now());
log.setRequestUri(pre.getUrl());
log.setCreateBy(pre.getUser());
return log;
})
.collect(Collectors.toList());
String insertQuery = "INSERT INTO sys_log_all " +
"(id,type, title, create_by, user_level_code, create_name, remote_addr, user_agent, request_uri, method, params, body, result) " +
" VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
JdbcTemplate clickhouseClusterTemplate = ClickhouseClusterUtil.get();

    clickhouseClusterTemplate.batchUpdate(insertQuery, new BatchPreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
            SysLog2 sysLog2 = sysLogs.get(i);
            preparedStatement.setLong(1, IdWorker.getId(IdWorker.get32UUID()));
            preparedStatement.setString(2, sysLog2.getType());
            preparedStatement.setString(3, sysLog2.getTitle());
            preparedStatement.setString(4, sysLog2.getCreateBy());
            preparedStatement.setString(5, sysLog2.getUserLevelCode());
            preparedStatement.setString(6, sysLog2.getCreateName());
            preparedStatement.setString(7, sysLog2.getRemoteAddr());
            preparedStatement.setString(8, sysLog2.getUserAgent());
            preparedStatement.setString(9, sysLog2.getRequestUri());
            preparedStatement.setString(10, sysLog2.getMethod());
            preparedStatement.setString(11, sysLog2.getParams());
            preparedStatement.setString(12, sysLog2.getBody());
            preparedStatement.setString(13, sysLog2.getResult());


        }

        @Override
        public int getBatchSize() {
            return sysLogs.size();
        }
    });
    return true;


}