springboot 使用clickhouse实 分析引擎的方法声明
声明:
因项目中使用clickhouse引擎这里springboot使用的方式是jdbc方式连接,这种方式的好处是可以使用clickhouse
自带的fetch方法批量从clickhouse中获取数据,对于大量数据的下载来说,比较好
因为如果全部拿到内存中处理,大量数据会有内存溢出的结果
如果批量多次请求数据库对于数据库查询等也不靠谱,所有直接使用clickhouse
jdbc连接来满足这种情况,不使用mybatis等框架来管理,这里根据大家不同的需求酌情参考即可
使用方式:
第一步:加入clickhouse jar包依赖
<!--clickhouse-->
<dependency>
<groupId>ru.yandex.clickhouse</groupId>
<artifactId>clickhouse-jdbc</artifactId>
<version>0.1.40</version>
</dependency>
[/code]
第二步:配置数据库连接属性配置文件,yml方式 此处仅作为参数,不连接任何驱动
```code
clickhouse:
address: jdbc:clickhouse://172.20.xxx.xxx:8123
username: default
password: xxx
db: marketing
socketTimeout: 600000
[/code]
第三步:添加数据库连接操作util工具类
```code
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("******************");
}
}
[/code]
执行完毕打印结果查看即可,
fetch方法之后再补充
感谢观看
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210608151750993.gif)