SpringBoot配置clikhouse数据库

SpringBoot配置clikhouse数据库

在面临大数据的时代，数据量大是我们必须面对的一个坎，目前大家用的比较多的是分库分表去处理，根据时间戳建库建表，分开去处理数据，这里不详细介绍，在做项目时有一块业务一天的数据量是60w，当时考虑分库分表去处理，但是这次我尝试使用clickhouse去处理这一块。这里不介绍clickhouse了，自行百度哈。
另外强调一下，目前clickhouse只能部署在Linux系统上。是不支持windows系统的！！！

一、在yml里面配置对应的clickhouse连接参数

 clickhouse:
    address: jdbc:clickhouse://ip:端口
    username: 用户名
    password: 密码
    db: jqxx
    socketTimeout: 600000

二、在pom引入maven的jar包

<!--clickHouse-->
        <dependency>
            <groupId>ru.yandex.clickhouse</groupId>
            <artifactId>clickhouse-jdbc</artifactId>
            <version>0.1.40</version>
        </dependency>

三、加入clickhouse连接工具类

工具类如下

package com.swsk.data.util;

import com.alibaba.fastjson.JSONObject;
import com.swsk.data.util.generate.util.DateUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import ru.yandex.clickhouse.ClickHouseConnection;
import ru.yandex.clickhouse.ClickHouseDataSource;
import ru.yandex.clickhouse.settings.ClickHouseProperties;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.regex.Pattern;

/**
 * clickHouse工具类
 * @author cy
 * @date 2020/8/3 20:23
 * @desc
 */
@Slf4j
@Component
public class ClickHouseUtil {


    private static String clickhouseAddress;

    private static String clickhouseUsername;

    private static String clickhousePassword;

    private static String clickhouseDB;

    private static Integer clickhouseSocketTimeout;

    private static final Pattern QUESTION = Pattern.compile("\\?{1}");

    @Value("${spring.clickhouse.address}")
    public  void setClickhouseAddress(String address) {
        ClickHouseUtil.clickhouseAddress = address;
    }
    @Value("${spring.clickhouse.username}")
    public  void setClickhouseUsername(String username) {
        ClickHouseUtil.clickhouseUsername = username;
    }
    @Value("${spring.clickhouse.password}")
    public  void setClickhousePassword(String password) {
        ClickHouseUtil.clickhousePassword = password;
    }
    @Value("${spring.clickhouse.db}")
    public  void setClickhouseDB(String db) {
        ClickHouseUtil.clickhouseDB = db;
    }
    @Value("${spring.clickhouse.socketTimeout}")
    public  void setClickhouseSocketTimeout(Integer socketTimeout) {
        ClickHouseUtil.clickhouseSocketTimeout = socketTimeout;
    }

    /**
     * 连接clickhouse
     * @return
     */
    public static Connection getConn() {

        ClickHouseConnection conn = null;
        ClickHouseProperties properties = new ClickHouseProperties();
        properties.setUser(clickhouseUsername);
        properties.setPassword(clickhousePassword);
        properties.setDatabase(clickhouseDB);
        properties.setSocketTimeout(clickhouseSocketTimeout);
        ClickHouseDataSource clickHouseDataSource = new ClickHouseDataSource(clickhouseAddress,properties);
        try {
            conn = clickHouseDataSource.getConnection();
            return conn;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

    /**
     * 执行sql
     * @param sql
     * @return
     */
    public static List<JSONObject> exeSql(String sql, Object[] args){
        log.info("cliockhouse 执行sql：" + sql);
        boolean flag = true;
        String allSql = sql;
        StringBuffer params = new StringBuffer();
        for (Object arg : args) {
            String paramStr = "'%s'";

            if (flag) {
                flag = false;
            } else {
                params.append(",");
            }

            if(arg instanceof java.util.Date){
                paramStr = String.format(paramStr, DateUtil.formatDate((Date)arg,"yyyy-MM-dd HH:mm:ss"));
            }else if(arg instanceof Integer || arg instanceof Long || arg instanceof Float || arg instanceof Double || arg instanceof BigDecimal){
                paramStr = arg.toString();
            }else{
                paramStr = String.format(paramStr,arg.toString());
            }
            params.append(arg.toString());
            allSql = QUESTION.matcher(allSql).replaceFirst(paramStr);
        }
        Connection connection = getConn();
        try {
            Statement statement = connection.createStatement();
            log.info("cliockhouse 执行完整sql：" + allSql);
            ResultSet results = statement.executeQuery(allSql);
            ResultSetMetaData rsmd = results.getMetaData();
            List<JSONObject> list = new ArrayList();
            while(results.next()){
                JSONObject row = new JSONObject();
                for(int i = 1;i<=rsmd.getColumnCount();i++){
                    row.put(rsmd.getColumnName(i),results.getString(rsmd.getColumnName(i)));
                }
                list.add(row);
            }

            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

}

工具类加入后就可以在dao的impl里面把sql语句和参数加入调用exeSql方法了

四、总结

实现的做法比较low，大神轻喷。不过，还是要承认clickhouse处理大数据的能力是十分优秀的，虽然有挺多槽点的。
首先目前使用clickhouse的人不多，网上的社区环境不完善，很多资料都没法搜索到，而且大家众说风云，有的理解是错的也总结成论坛，让我踩了不少坑，以上是自己临时用的，做法low，先用着，有时间再整合一个完整的。