springboot 使用clickhouse实时大数据分析引擎的方法

约定不等于承诺〃 2022-04-05 08:19 731阅读 1赞

# 声明： #

因项目中使用clickhouse引擎这里springboot使用的方式是jdbc方式连接，这种方式的好处是可以使用clickhouse 自带的fetch方法批量从clickhouse中获取数据，对于大量数据的下载来说，比较好

因为如果全部拿到内存中处理，大量数据会有内存溢出的结果

如果批量多次请求数据库对于数据库查询等也不靠谱，所有直接使用clickhouse jdbc连接来满足这种情况，不使用mybatis等框架来管理，这里根据大家不同的需求酌情参考即可

# 使用方式： #

第一步：加入clickhouse jar包依赖

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

第二步：配置数据库连接属性配置文件，yml方式 此处仅作为参数，不连接任何驱动

clickhouse:
      address: jdbc:clickhouse://172.20.xxx.xxx:8123
      username: default
      password: xxx
      db: marketing
      socketTimeout: 600000

第三步：添加数据库连接操作util工具类

import lombok.extern.slf4j.Slf4j;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.stereotype.Component;
    import net.sf.json.JSONObject;
    import ru.yandex.clickhouse.ClickHouseConnection;
    import ru.yandex.clickhouse.ClickHouseDataSource;
    import ru.yandex.clickhouse.settings.ClickHouseProperties;
    
    import java.sql.*;
    import java.util.*;
    
    /**
     * @Description:
     * @Date 2018/11/12
     */
    @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;
    
        @Value("${clickhouse.address}")
        public  void setClickhouseAddress(String address) {
            ClickHouseUtil.clickhouseAddress = address;
        }
        @Value("${clickhouse.username}")
        public  void setClickhouseUsername(String username) {
            ClickHouseUtil.clickhouseUsername = username;
        }
        @Value("${clickhouse.password}")
        public  void setClickhousePassword(String password) {
            ClickHouseUtil.clickhousePassword = password;
        }
        @Value("${clickhouse.db}")
        public  void setClickhouseDB(String db) {
            ClickHouseUtil.clickhouseDB = db;
        }
        @Value("${clickhouse.socketTimeout}")
        public  void setClickhouseSocketTimeout(Integer socketTimeout) {
            ClickHouseUtil.clickhouseSocketTimeout = socketTimeout;
        }
    
    
        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;
        }
    
        public static List<JSONObject> exeSql(String sql){
            log.info("cliockhouse 执行sql：" + sql);
            Connection connection = getConn();
            try {
                Statement statement = connection.createStatement();
                ResultSet results = statement.executeQuery(sql);
                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;
        }
    
    }

第四步：Test简单使用执行sql查询数据

import com.renrenche.databus.common.ClickHouseUtil;
    import com.renrenche.databus.common.Result;
    import com.renrenche.databus.domain.logdata.fem.FemParam;
    import com.renrenche.databus.service.fem.FemMainService;
    import net.sf.json.JSONObject;
    import org.junit.Test;
    import org.junit.runner.RunWith;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.boot.test.context.SpringBootTest;
    import org.springframework.test.context.junit4.SpringRunner;
    
    import java.util.List;
    
    /**
     * @Auther: qixin
     * @Date: 2018/12/11 15:05
     * @Description:
     */
    @RunWith(SpringRunner.class)
    @SpringBootTest
    public class SemTest {
    
        @Test
        public void getFrsDataTest(){
            System.out.println("******************");
            String sql="select * from marketing.sem_campaign_real_time_report";
            List<JSONObject> result= ClickHouseUtil.exeSql(sql);
            System.out.println("******************");
        }
    
    }

执行完毕打印结果查看即可，

fetch方法之后再补充

感谢观看