clickhouse SLB 服务搭建

前言

最近在使用flink消费kafka的数据同步到clickhouse，为了保证高可用，特此查了一下资料，网上大部分都是使用的BalancedClickhouseDataSource，但是这个包虽然可以做到一定程度的负载均衡，但是无法做到故障转移。有兴趣可以去看下BalancedClickhouseDataSource的源码解析：https://blog.csdn.net/anyitian/article/details/117953600。官方论坛中推荐使用SLB来做负载均衡。下面就来记录一下整个流程。

nginx安装

windows搭建Nginx服务器及常见问题
linux下安装nginx

nginx配置

在nginx的conf目录中找到nginx.conf，打开配置clickhouse的地址和代理。整个配置如下：

worker_processes  1;

events {
    worker_connections  1024;
}

http {
    include       mime.types;
    default_type  application/octet-stream;

    sendfile        on;

    keepalive_timeout  65;

    upstream clickhouse{
        server 124.70.101.18:8123;
        server 124.70.22.201:8123;
        server 124.70.36.237:8123;
        server 121.36.18.102:8123;
    }

    server {
        listen       8123;
        server_name localhost;

        location / {
        client_max_body_size 200m;
        proxy_pass http://clickhouse;

        }
        
        error_page   500 502 503 504  /50x.html;
        location = /50x.html {
            root   html;
        }

    }
}

使用

clickhouse-jdbc jar包

<dependency>
    <groupId>ru.yandex.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.2.4</version>
</dependency>

在代码中我们只需要在创建连接的时候访问server_name的地址和端口即可，nginx会自动帮我们做负载均衡。
下面附上springboot的使用方法，参考地址：https://www.jianshu.com/p/e7e26f8ddd8f

application.yml文件配置

clickhouse.test.address:jdbc:clickhouse://ip:8123
clickhouse.test.db:test
clickhouse.test.socketTimeout:30000
clickhouse.test.username:**
clickhouse.test.password:***
clickhouse.test.connection-timeout:2000
clickhouse.test.alive-test-interval:1
server.port=8181

spring boot 的config类

@Configuration
@MapperScan(basePackages = {"com.john.balanceclickhouse.demo"},
        sqlSessionFactoryRef ="dwstSqlSessionFactory" )
public class ClickhouseConfig {
    @Resource
    private LocalProperties localProperties;

    @Bean(name = "dataSource")
    public DataSource clickHouseDataSource() {
        ClickHouseProperties properties = new ClickHouseProperties();
        properties.setDatabase(localProperties.getDb());
        properties.setUser(localProperties.getUsername());
        properties.setPassword(localProperties.getPassword());
        properties.setConnectionTimeout(localProperties.getConnectionTimeout());
        properties.setSocketTimeout(localProperties.getSocketTimeout());
        //clickhouse jdbc 包，可以用来做负载均衡；策略是随机的，但是搭建SLB之后，就可以使用nginx 的负载均衡策略了
        BalancedClickhouseDataSource ckDataSource = new BalancedClickhouseDataSource(localProperties.getAddress(), properties);
        // 添加集群节点存活检查
        return ckDataSource.scheduleActualization(localProperties.getAliveTestInterval(), TimeUnit.SECONDS);
    }

    @Bean(name = "configuration")
    public org.apache.ibatis.session.Configuration globalConfiguration() {
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        configuration.setMapUnderscoreToCamelCase(true);
        return configuration;
    }

    @Bean(name = "SqlSessionFactory")
    public SqlSessionFactory clickHouseSqlSessionFactory(@Qualifier("dataSource") DataSource clickHouseDataSource, @Qualifier("configuration") org.apache.ibatis.session.Configuration configuration) throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(clickHouseDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/**/*.xml"));
        sessionFactory.setTypeAliasesPackage("com.john.balanceclickhouse.demo");
        sessionFactory.setConfiguration(configuration);
        return sessionFactory.getObject();
    }
}

配置文件

@Data
@Component
@ConfigurationProperties(prefix = "clickhouse.test")
public class LocalProperties {
    /**

   * 数据库地址
     /
         private String address;

    /**
     * 连接数据库
     */
    private String db;
    
    /**
     * socket超时时间
     */
    private Integer socketTimeout;
    
    /**
     * 用户名
     */
    private String username;
    
    /**
     * 密码
     */
    private String password;
    
    /**
     * 连接超时时间
     */
    private Integer connectionTimeout;
    
    /**
     * 判断存活实例时间间隔
     */
    private Integer aliveTestInterval;

}