JDBC连接clickhouse cluster
两种方式，一种是使用clickhouse-jdbc连接集群中的每一个节点，另外一种是使用SLB提供一个对外的统一地址
使用BalancedClickhouseDataSource
参考以下clickhouse-jdbc中的代码中的注释：jdbc:clickhouse://localhost:8123,localhost:8123/database?compress=1&decompress=2
/**
* create Datasource for clickhouse JDBC connections
*
* @param url address for connection to the database
* must have the next format {@code jdbc:clickhouse://<first-host>:<port>,<second-host>:<port>/<database>?param1=value1¶m2=value2 }
* for example, {@code jdbc:clickhouse://localhost:8123,localhost:8123/database?compress=1&decompress=2 }
* @throws IllegalArgumentException if param have not correct format, or error happens when checking host availability
*/
public BalancedClickhouseDataSource(final String url) {
this(splitUrl(url), getFromUrl(url));
}
/**
* create Datasource for clickhouse JDBC connections
*
* @param url address for connection to the database
* @param properties database properties
* @see #BalancedClickhouseDataSource(String)
*/
public BalancedClickhouseDataSource(final String url, Properties properties) {
this(splitUrl(url), new ClickHouseProperties(properties));
}
/**
* create Datasource for clickhouse JDBC connections
*
* @param url address for connection to the database
* @param properties database properties
* @see #BalancedClickhouseDataSource(String)
*/
public BalancedClickhouseDataSource(final String url, ClickHouseProperties properties) {
this(splitUrl(url), properties.merge(getFromUrlWithoutDefault(url)));
}
使用SLB
使用LB均衡到各个副本，保证应用方查询单host，本次不使用BalancedClickhouseDataSource，从github issue上看BalancedClickhouseDataSource在之前版本出现副本故障时没能故障转移，不知道是否有修复。
配置LB：使用标准JDBC连接时需要映射http协议到clickhouse的8123端口(http监听端口)
验证LB配置是否生效
echo 'SELECT * from dm.delphi_membership_properties FORMAT Pretty' | curl 'internal-clickhouse-prod-621097858.
cn-north-1.elb.amazonaws.com.cn:80/?' --data-binary @-
贴一下在Springboot中使用标准JDBC数据源HikariDataSource÷连接clickhouse的配置：
package com.kezaihui.delphi.core.config;
import com.baomidou.mybatisplus.entity.GlobalConfiguration;
import com.baomidou.mybatisplus.enums.DBType;
import com.baomidou.mybatisplus.enums.IdType;
import com.baomidou.mybatisplus.spring.MybatisSqlSessionFactoryBean;
import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.annotation.MapperScan;
import org.mybatis.spring.boot.autoconfigure.MybatisProperties;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import javax.sql.DataSource;
/**
* clickhouse 数据源配置
*
* @author Suncle
* @date 2019-07-05
*/
@Slf4j
@Configuration
@MapperScan(basePackages = {
"com.kezaihui.delphi.core.membership.**.mapper"
}, sqlSessionFactoryRef = "ckSqlSessionFactory")
public class CkDataSourceConfig {
@Autowired
private MybatisProperties mybatisProperties;
/**
* 读取数据源
*
* @return javax.sql.DataSource 数据源
*/
@Bean(name = "ckDataSource")
@ConfigurationProperties(prefix = "spring.clickhouse.datasource")
public DataSource dataSource() {
return new HikariDataSource();
}
/**
* sql 会话工厂配置
*
* @param ckDataSource javax.sql.DataSource 数据源
* @return SqlSessionFactory
*/
@Bean(name = "ckSqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(@Qualifier("ckDataSource") DataSource ckDataSource) {
MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
bean.setDataSource(ckDataSource);
try {
GlobalConfiguration configuration = new GlobalConfiguration();
configuration.setDbType(DBType.OTHER.name());
configuration.setIdType(IdType.AUTO.getKey());
configuration.setDbColumnUnderline(true);
bean.setGlobalConfig(configuration);
ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
bean.setMapperLocations(mybatisProperties.resolveMapperLocations());
bean.setConfigLocation(resolver.getResource(mybatisProperties.getConfigLocation()));
return bean.