ClickHouse的负载均衡如何做。

阳光穿透心脏的1/2处 2022-12-17 08:42 196阅读 0赞

1，负载均衡。

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

## 安装nginx ##

安装nginx ,参考地址： [http://nginx.org/en/linux\_packages.html][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提供了帮助

3，如果是flink写入到clickhouse，调用的jar包

<dependency>
        <groupId>ru.ivi.opensource</groupId>
        <artifactId>flink-clickhouse-sink</artifactId>
        <version>1.0.0</version>
    </dependency>

也可用通过负载均衡做批量写入。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dwcHdwcDE_size_16_color_FFFFFF_t_70][]

地址写成： clickhouse-host=10.3.1.59:8123,10.3.1.60:8123,10.3.1.60:8123

jar包自动做负载均衡。

[http_nginx.org_en_linux_packages.html]: https://links.jianshu.com/go?to=http%3A%2F%2Fnginx.org%2Fen%2Flinux_packages.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dwcHdwcDE_size_16_color_FFFFFF_t_70]: /images/20221122/2fecb1adb64843329ea38160e9f5f38a.png