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,先用着,有时间再整合一个完整的。