ClickHouse数据库
简介
使用场景
1.绝大多数请求都是用于读访问的
2.数据需要以大批次(大于1000行)进行更新,而不是单行更新;或者根本没有更新操作
3.数据只是添加到数据库,没有必要修改
4.查询频率相对较低(通常每台服务器每秒查询数百次或更少)
安装
docker部署
参考:Docker部署clickhouse(超详细图文教程)
拉取镜像:
docker pull yandex/clickhouse-server
docker pull yandex/clickhouse-client
启动server端
1、可正常连接方法(推荐尝试)
docker run -d -p 8123:8123 -p 9000:9000 --name clickhouse yandex/clickhouse-server
2、官方方法
# 默认直接启动即可
docker run -d --name [启动之后的名称] --ulimit nofile=262144:262144 yandex/clickhouse-server
# 如果想指定目录启动,这里以clickhouse-test-server命令为例,可以随意写
mkdir /work/clickhouse/clickhouse-test-db ## 创建数据文件目录
# 使用以下路径启动,在外只能访问clickhouse提供的默认9000端口,只能通过clickhouse-client连接server
docker run -d --name clickhouse-test-server --ulimit nofile=262144:262144 --volume=/work/clickhouse/clickhouse_test_db:/var/lib/clickhouse yandex/clickhouse-server
数据操作
注意:官方提供JDBC驱动包,可以使用SQL语句进行数据库操作
开始
-- 数据库创建
create database iot_zdly;
-- 数据表创建
create table iot_zdly.Iot_data(
timestamp datetime64 comment '时间维度',
id UInt64,
cpu_tmp Int16 comment 'cpu温度',
cpu_use_ratio UInt8 comment 'cpu使用率'
)engine = MergeTree -- 指定引擎
primary key id -- 指定主键
comment '物联网测试数据';
-- 查询表
select * from iot_zdly.Iot_data;
-- 插入数据
insert into iot_zdly.Iot_data VALUES ('2023-10-03 11:22:33.000', 12, 32, 22);
-- 删除数据
delete from iot_zdly.Iot_data where id = 12;
-- 修改数据
alter table Iot_data update cpu_tmp = 120, cpu_use_ratio = 0 where id = 1
其中,数据库表创建中指定的engine参考https://blog.csdn.net/qq_41106844/article/details/107067525,primary key id 为主键,并且必须创建,否则将会报错。
Java简易开发示例
pom文件:
<dependencies>
<!--解决clickhouse lz4报错-->
<dependency>
<groupId>org.lz4</groupId>
<artifactId>lz4-java</artifactId>
<version>1.8.0</version>
</dependency>
<dependency>
<groupId>com.clickhouse</groupId>
<artifactId>clickhouse-jdbc</artifactId>
<version>0.4.6</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
</dependencies>
连接配置类:
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class ConnectionObj {
private String driverName; // 驱动器
private String url; // 连接地址
private String user; // 用户名
private String password; // 密码
}
接口类:
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.List;
import java.util.Map;
public interface Utils {
/**
* 基本SQL执行类
* @param connectionObj 连接配置对象
* @return 连接对象
*/
Connection connection(ConnectionObj connectionObj);
/**
* 连接对象批量关闭
* @param autoCloseable 连接对象集
*/
void close(AutoCloseable... autoCloseable);
/**
* 基本操作SQL执行类
* @param con 连接对象
* @param sql sql命令
* @param params 参数
* @return 布尔
*/
boolean baseSqlExecute(Connection con, String sql, String...params);
boolean insert(Connection con, String sql, String...params);
boolean delete(Connection con, String sql, String...params);
/**
* 基本查询SQL执行类
* @param con 连接对象
* @param sql sql命令
* @param params 参数
* @return 数据集合
*/
List<Map<String, Object>> queryResultSet(Connection con, String sql, String...params);
}
clickhouse接口实现类:
import com.clickhouse.jdbc.ClickHousePreparedStatement;
import lombok.extern.slf4j.Slf4j;
import org.yaml.snakeyaml.util.EnumUtils;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Slf4j
public class ClickHouseUtils implements Utils{
@Override
public Connection connection(ConnectionObj connectionObj) {
Connection con = null;
try {
// 获取驱动类
Class.forName(connectionObj.getDriverName());
// 建立连接获取连接对象
con = DriverManager.getConnection(connectionObj.getUrl(), connectionObj.getUser(), connectionObj.getPassword());
}catch (Exception e){
log.error("Connection fail, please check your config:", e);
}
return con;
}
@Override
public void close(AutoCloseable... autoCloseable) {
for (AutoCloseable closeable : autoCloseable) {
if (closeable != null){
try {
closeable.close();
}catch (Exception e){
log.error("Close fail:", e);
}finally {
closeable = null;
}
}
}
}
@Override
public boolean baseSqlExecute(Connection con, String sql, String... params) {
boolean is = false;
ClickHousePreparedStatement pst = null;
if (con == null){
log.error("Connection is empty");
return is;
}
try {
// 获取控制台
pst = (ClickHousePreparedStatement) con.prepareStatement(sql); // 需要做安全处理
for (int i = 0; i < params.length; i++) {
pst.setObject(i+1, params[i]);
}
is = pst.execute();
}catch (SQLException e){
log.error("SQLException:", e);
}finally {
// close(pst, con);
}
return is;
}
@Override
public boolean insert(Connection con, String sql, String... params) {
return false;
}
@Override
public boolean delete(Connection con, String sql, String... params) {
return false;
}
@Override
public List<Map<String, Object>> queryResultSet(Connection con, String sql, String... params) {
List<Map<String, Object>> list = new ArrayList<>();
ResultSet r = null;
ClickHousePreparedStatement pst = null;
if (con == null){
log.error("Connection is empty");
return null;
}
try {
// 获取控制台
pst = (ClickHousePreparedStatement) con.prepareStatement(sql); // 需要做安全处理
for (int i = 0; i < params.length; i++) {
pst.setObject(i+1, params[i]);
}
r = pst.executeQuery();
ResultSetMetaData metaData = r.getMetaData();
while (r.next()){
Map<String, Object> row = new HashMap<>();
for (int i = 0; i < metaData.getColumnCount(); i++) {
row.put(metaData.getColumnName(i+1), r.getObject(metaData.getColumnName(i+1)));
}
list.add(row);
}
}catch (SQLException e){
log.error("SQLException:", e);
}finally {
// close(r, pst, con);
}
return list;
}
}
测试类:
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
public class ClickHouseTest {
public static void main(String[] args) throws SQLException {
String driverName = "com.clickhouse.jdbc.ClickHouseDriver";
String url = "jdbc:clickhouse://localhost:8123/iot_zdly";
String sqli="insert into Iot_data";
String sql="select * from Iot_data";
String[] paramsi={"2023-10-03 11:22:33.000", "1", "32", "22"};
String[] params={};
ConnectionObj connectionObj = new ConnectionObj(driverName, url, null, null);
Utils clickHouseUtils = new ClickHouseUtils();
Connection connection = clickHouseUtils.connection(connectionObj);
// 插入数据
clickHouseUtils.baseSqlExecute(connection, sqli, paramsi);
// 查询数据
List<Map<String, Object>> maps = clickHouseUtils.queryResultSet(connection, sql, params);
maps.forEach(System.out::println);
}
}
执行结果:
10:57:54.280 [main] DEBUG com.clickhouse.jdbc.ClickHouseDriver - ClickHouse Driver 0.4.6.0(JDBC: 4.2.0.0) registered
10:57:54.303 [main] DEBUG com.clickhouse.jdbc.ClickHouseDriver - Creating connection
10:57:54.371 [main] DEBUG com.clickhouse.client.ClickHouseNodeSelector - Checking [com.clickhouse.client.cli.ClickHouseCommandLineClient@35851384] against [HTTP]...
10:57:54.373 [main] DEBUG com.clickhouse.client.ClickHouseNodeSelector - Checking [com.clickhouse.client.http.ClickHouseHttpClient@256216b3] against [HTTP]...
10:57:54.379 [main] DEBUG com.clickhouse.jdbc.internal.ClickHouseConnectionImpl - Connecting to: ClickHouseNode [uri=http://localhost:8123/iot_zdly]@-532860298
10:57:54.380 [main] DEBUG com.clickhouse.client.AbstractClient - Connecting to: ClickHouseNode [uri=http://localhost:8123/iot_zdly]@-532860298
10:57:54.388 [main] DEBUG com.clickhouse.client.AbstractClient - Connection established: com.clickhouse.client.http.HttpUrlConnectionImpl@3cb5cdba
10:57:54.388 [main] DEBUG com.clickhouse.client.http.ClickHouseHttpClient - Query: select currentUser() user, timezone() timezone, version() version, toUInt8(ifnull((select value from system.settings where name = 'readonly'), '0')) as readonly, toInt8(ifnull((select value from system.settings where name = 'throw_on_unsupported_query_inside_transaction'), '-1')) as throw_on_unsupported_query_inside_transaction, (ifnull((select value from system.settings where name = 'wait_changes_become_visible_after_commit_mode'), '')) as wait_changes_become_visible_after_commit_mode,toInt8(ifnull((select value from system.settings where name = 'implicit_transaction'), '-1')) as implicit_transaction, toUInt64(ifnull((select value from system.settings where name = 'max_insert_block_size'), '0')) as max_insert_block_size, toInt8(ifnull((select value from system.settings where name = 'allow_experimental_lightweight_delete'), '-1')) as allow_experimental_lightweight_delete, (ifnull((select value from system.settings where name = 'custom_jdbc_config'), '')) as custom_jdbc_config FORMAT RowBinaryWithNamesAndTypes
10:57:54.798 [main] DEBUG com.clickhouse.client.AbstractClient - Connecting to: ClickHouseNode [uri=http://localhost:8123/iot_zdly]@-532860298
10:57:54.799 [main] DEBUG com.clickhouse.client.AbstractClient - Connection established: com.clickhouse.client.http.HttpUrlConnectionImpl@6cc4c815
10:57:54.799 [main] DEBUG com.clickhouse.client.http.ClickHouseHttpClient - Query: SELECT * FROM `Iot_data` WHERE 0
10:57:54.866 [main] DEBUG com.clickhouse.client.AbstractClient - Connecting to: ClickHouseNode [uri=http://localhost:8123/iot_zdly]@-532860298
10:57:54.866 [main] DEBUG com.clickhouse.client.AbstractClient - Connection established: com.clickhouse.client.http.HttpUrlConnectionImpl@1ce92674
10:57:54.866 [main] DEBUG com.clickhouse.client.http.ClickHouseHttpClient - Query: insert into Iot_data
FORMAT RowBinary
10:57:54.889 [main] DEBUG com.clickhouse.client.AbstractClient - Connecting to: ClickHouseNode [uri=http://localhost:8123/iot_zdly]@-532860298
10:57:54.889 [main] DEBUG com.clickhouse.client.AbstractClient - Connection established: com.clickhouse.client.http.HttpUrlConnectionImpl@5f2108b5
10:57:54.890 [main] DEBUG com.clickhouse.client.http.ClickHouseHttpClient - Query: select * from Iot_data
{cpu_use_ratio=22, cpu_tmp=32, id=1, timestamp=2023-10-03T11:22:33}
SpringBoot集成
使用mybatis plus作为开发框架
参考:SpringBoot 集成 clickhouse + mybatis-plus 配置及使用问题说明(含建表语句、demo源码、测试说明)
pom文件:
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<!--解决clickhouse lz4报错-->
<dependency>
<groupId>org.lz4</groupId>
<artifactId>lz4-java</artifactId>
<version>1.8.0</version>
</dependency>
<dependency>
<groupId>com.clickhouse</groupId>
<artifactId>clickhouse-jdbc</artifactId>
<version>0.4.6</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.21</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.baomidou/mybatis-plus-boot-starter -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.3.2</version>
</dependency>
</dependencies>
application.yml:
server:
port: 18999
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
clickhouse:
driverClassName: com.clickhouse.jdbc.ClickHouseDriver
url: jdbc:clickhouse://localhost:8123/iot_zdly
# username:
# password:
initialSize: 10
maxActive: 100
minIdle: 10
maxWait: 60
配置类:
import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
/**
* @author dra
*/
@Configuration
public class ClickHouseDataSourceConfiguration {
@Bean
@ConfigurationProperties(prefix = "spring.datasource.clickhouse")
public DataSource clickDruidDataSource(){
return new DruidDataSource();
}
}
实体类:
import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.time.LocalDateTime;
/**
* @author dra
*/
@TableName("Iot_data")
@Data
public class IotData {
Long id;
Integer cpuTmp;
Short cpuUseRatio;
// 时间相关属性必须使用@JsonFormat
@JsonFormat(locale="zh", timezone="GMT+8", pattern="yyyy-MM-dd HH:mm:ss")
LocalDateTime timestamp;
}
持久化层:
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.dra.iot.entity.click.IotData;
/**
* @author dra
*/
public interface IotDataMapper extends BaseMapper<IotData> {
}
业务层接口:
import com.baomidou.mybatisplus.extension.service.IService;
import com.dra.iot.entity.click.IotData;
/**
* @author dra
*/
public interface IotDataService extends IService<IotData> {
}
业务层实现类:
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.dra.iot.entity.click.IotData;
import com.dra.iot.mapper.IotDataMapper;
import com.dra.iot.serivce.IotDataService;
import org.springframework.stereotype.Service;
/**
* @author dra
*/
@Service
public class IotDataServiceImpl extends ServiceImpl<IotDataMapper, IotData> implements IotDataService {
}
控制层:
import com.dra.iot.serivce.IotDataService;
import lombok.RequiredArgsConstructor;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
/**
* @author dra
*/
@RestController
@RequestMapping("/iot")
@RequiredArgsConstructor
public class IotDataController {
final IotDataService iotDataService;
@GetMapping("/list")
public Object list(){
return iotDataService.list();
}
}
响应结果:
[
{
"id": 1,
"cpuTmp": 120,
"cpuUseRatio": 0,
"timestamp": "2023-10-03 11:22:33"
}
]
Tips:MP对于clickhouse数据只有在读与插入操作是兼容的,修改与删除由于与MySQL的SQL语法不一致需要在Mapper中的xml单独编写。
多数据源集成
参考:MP官方-多数据源
配置:
server:
port: 18999
spring:
datasource:
dynamic:
primary: mysql # 默认数据源
datasource:
mysql:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/Iot_test?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&zeroDateTimeBehavior=convertToNull&rewriteBatchedStatements=true
username: xxx
password: xxx
clickhouse:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.clickhouse.jdbc.ClickHouseDriver
url: jdbc:clickhouse://localhost:8123/iot_zdly
# 本教程默认clickhouse无username与password 需要可以添加以下代码
username: xxx
password: xxx
实体类、持久化层Mapper接口、业务层接口写法都相同,该处不多赘述。
业务层实现类:
@Service
@DS("clickhouse")
public class IotDataServiceImpl extends ServiceImpl<IotDataMapper, IotData> implements IIotDataService {
}
当需要使用与clickhouse库相关的操作业务类上加上**@DS**注解去指定当前业务类的数据源。
当然,@DS可以放在方法上,并且其遵从就近原则。方法上注解 优先于 类上注解。
控制层:
@RestController
@RequestMapping("/mysql")
@RequiredArgsConstructor
public class MysqlTestController {
final IMysqlTestService iMysqlTestService;
@GetMapping("/list")
public Object list(){
return iMysqlTestService.list();
}
}
响应结果:
# clickhosue
[
{
"id": 1,
"cpuTmp": 120,
"cpuUseRatio": 0,
"timestamp": "2023-10-03 11:22:33"
}
]
# mysql
[
{
"id": 1,
"name": "test success"
}
]