持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第16天，点击查看活动详情
在数据量日益增长的当下，传统数据库的查询性能已满足不了我们的业务需求。而 Clickhouse 在OLAP领域的快速崛起引起了我们的注意,于是我们引入Clickhouse并不断优化系统性能，提供高可用集群环境。
本文主要讲述如何通过Clickhouse结合大数据生态来定制一套完善的数据分析方案、如何打造完备的运维管理平台以降低维护成本，并结合具体案例说明Clickhouse的实践过程。
为什么选择Clickhouse
- 目前企业用户行为日志每天百亿量级，虽然经过数仓的分层以及数据汇总层通用维度指标的预计算，但有些个性化的分析场景还是需要直接编写程序或sql查询，这种情况下hive sql和spark sql的查询性能已无法满足用户需求，我们迫切的需要一个OLAP引擎来支持快速的即席查询。
- BI存储库主要采用的是Infobright，在千万量级能很快的响应BI的查询请求，但随着时间推移和业务的发展，Infobright的并发量与查询瓶颈日益凸显，我们尝试将大数据量级的表导入TiDB、Hbase、ES等存储库，虽然对查询有一定的提速，但是也存在着相应的问题（后续章节会详细介绍），这时我们考虑到Clickhouse。
- Clickhouse 社区活跃度高、版本迭代非常快,几乎几天到十几天更新一个小版本，我们非常看好它以后的发展。
@Component
public class CommonUtils {
@Value("${spring.datasource.dynamic.datasource.clickhouse.username}")
private String username;
@Value("${spring.datasource.dynamic.datasource.clickhouse.password}")
private String password;
@Value("${spring.datasource.dynamic.datasource.clickhouse.url}")
private String address;
private static ClickHouseDataSource clickHouseDataSource;
//public static void main(String[] args) {
// String driverName = "ru.yandex.clickhouse.ClickHouseDriver";
// String url = "jdbc:clickhouse://10.115.50.61:8123/ewsmp_real";
// String user = "lrsy";
// String password = "1234.lrsy";
//
// //String sql = "select * from carbonasset_carbon_emission;";
// String sql = "ALTER TABLE carbonasset_carbon_emission UPDATE area='丰台区',current_year=9090,carbon_emission=111 where id = 'c55f68d9599c07675ad7824a81812b39';";
// String[] params = {};
//
// ConnEntity connEntiy = new ConnEntity(driverName, url, user, password);
// ClickHouseJdbcUtils utils = new ClickHouseJdbcUtilsImpl();
// Connection conn = utils.connection(connEntiy);
//
// System.out.println(utils.delete(conn, sql));
//
//}
/**
* 获取用户的姓名
*/
public String getUserName(HttpServletRequest request){
return JwtUtil.getUserNameByToken(request);
}
/**
* JDBC连接数据库进行数据库的操作
*/
public void dataProcess(String sql) throws SQLException {
if(clickHouseDataSource == null){
ClickHouseProperties properties = new ClickHouseProperties();
properties.setUser(username);
properties.setPassword(password);
properties.setSocketTimeout(60000);
clickHouseDataSource = new ClickHouseDataSource(address, properties);
}
ClickHouseConnection connection = null;
try {
connection = clickHouseDataSource.getConnection();
Statement statement = connection.createStatement();
statement.executeQuery(sql);
connection.commit();
} catch (SQLException e) {
throw new RuntimeException("数据修改失败，请稍后重试");
}finally {
connection.close();
}
}
public Connection connection() {
Connection conn = null;
try {
Class.forName("ru.yandex.clickhouse.ClickHouseDriver");
conn = DriverManager.getConnection(address, username, password);
} catch (Exception e) {
System.out.println("connection fail ,please check your entities");
}
return conn;
}
public static void close(AutoCloseable... closes) {
for (AutoCloseable close : closes) {
if (close != null) {
try {
close.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
close = null;
}
}
}
}
public boolean insert(Connection connection, String sql, String... params) {
boolean b = false;
ClickHousePreparedStatement pst = null;
if (connection == null) {
System.out.println("connection is empty");
System.exit(-1);
}
try {
pst = (ClickHousePreparedStatement) connection.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
pst.setObject(i + 1, params[i]);
}
b = pst.execute();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
close(pst, connection);
}
return b;
}
public boolean delete(Connection connection, String sql, String... params) {
boolean b = false;
ClickHousePreparedStatement pst = null;
if (connection == null) {
System.out.println("connection is empty");
System.exit(-1);
}
try {
pst = (ClickHousePreparedStatement) connection.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
pst.setObject(i + 1, params[i]);
}
b = pst.execute();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
close(pst, connection);
}
return b;
}
public ResultSet QueryResultSet(Connection connection, String sql, String... params) {
ResultSet rst = null;
ClickHousePreparedStatement pst = null;
if (connection == null) {
System.out.println("connection is empty");
System.exit(-1);
}
try {
pst = (ClickHousePreparedStatement) connection.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
pst.setObject(i + 1, params[i]);
}
rst = pst.executeQuery();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
close(rst, pst, connection);
}
return rst;
}
}
