clickhouse SLB 服务搭建

目前我们公司后台使用clickhouse，来做数据的离线分析；配置为四台集群（shard）
通过springboot+clickhouse-jdbc完成服务与clickhouse jdbc的连接；使用的是clickhouse官方，BalancedClickhouseDataSource(urls,properties)，做到四台服务之间的负载均衡；网上很多资料都表示
这个包虽然可以做到一定程度的负载均衡，但是无法做到故障转移；按照大家的推荐，我本地是nginx搭建clickhouse-jdbc 负载均衡的服务

安装nginx

安装nginx ,参考地址： http://nginx.org/en/linux_packages.html

配置config.nginx

upstream clickhouse{
        server 10.22.5.129:18123;
        #server 10.22.5.130:18123;
        #server 10.22.5.132:18123;
        #server 10.22.5.133:18123;
}
server {
    listen       18123;
    server_name localhost;

    #charset koi8-r;
    #access_log  /var/log/nginx/host.access.log  main;

#    location / {
#        root   /usr/share/nginx/html;
#        index  index.html index.htm;
#    }
        location / {
        proxy_pass http://clickhouse;

        }
    #error_page  404              /404.html;

    # redirect server error pages to the static page /50x.html
    #
    error_page   500 502 503 504  /50x.html;
    location = /50x.html {
        root   /usr/share/nginx/html;
    }

    # proxy the PHP scripts to Apache listening on 127.0.0.1:80
    #
    #location ~ \.php$ {
    #    proxy_pass   http://127.0.0.1;
    #}

    # pass the PHP scripts to FastCGI server listening on 127.0.0.1:9000
    #
    #location ~ \.php$ {
    #    root           html;
    #    fastcgi_pass   127.0.0.1:9000;
    #    fastcgi_index  index.php;
    #    fastcgi_param  SCRIPT_FILENAME  /scripts$fastcgi_script_name;
    #    include        fastcgi_params;
    #}

    # deny access to .htaccess files, if Apache's document root
    # concurs with nginx's one
    #
    #location ~ /\.ht {
    #    deny  all;
    #}
}

springboot 项目配置

application.yml文件配置

clickhouse.test.address:jdbc:clickhouse://ip:18123
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;

}

总结：

虽然BalancedClickhouseDataSource在一定程度上能做到负载均衡；如果不能做到故障转移，会很大程度上失去负载均衡的好处；本文通过实践，完成了clickhouse jdbc slb 的服务器搭建，为更好的使用clickhouse提供了帮助