Springboot集成ClickHouse及应用场景分析

Springboot集成ClickHouse及应用场景分析

ClickHouse应用场景：

1.绝大多数请求都是用于读访问的2.数据需要以大批次（大于1000行）进行更新，而不是单行更新；或者根本没有更新操作3.数据只是添加到数据库，没有必要修改4.读取数据时，会从数据库中提取出大量的行，但只用到一小部分列5.表很“宽”，即表中包含大量的列6.查询频率相对较低（通常每台服务器每秒查询数百次或更少）7.对于简单查询，允许大约50毫秒的延迟8.列的值是比较小的数值和短字符串（例如，每个URL只有60个字节）9.在处理单个查询时需要高吞吐量（每台服务器每秒高达数十亿行）10.不需要事务11.数据一致性要求较低12.每次查询中只会查询一个大表。除了一个大表，其余都是小表13.查询结果显著小于数据源。即数据有过滤或聚合。返回结果不超过单个服务器内存大小

行式存储对比列式存储：

(1)、行式数据

(2)、列式数据

(3)、对比分析

分析类查询，通常只需要读取表的一小部分列。在列式数据库中可以只读取需要的数据。数据总是打包成批量读取的，所以压缩是非常容易的。同时数据按列分别存储这也更容易压缩。这进一步降低了I/O的体积。由于I/O的降低，这将帮助更多的数据被系统缓存。

整合Springboot：

核心依赖（mybatis plus做持久层，druid做数据源）：

ru.yandehttp://x.clickhouse

clickhouse-jdbc

0.3.1-patch

com.alibaba

druid

1.2.6

com.baomidou

mybatis-plus-boot-starter

HARJlmqLyT 3.4.3.1

配置yml文件：

spring:

datasource:

type: com.alibaba.druid.pool.DruidDataSource

click:

driverClassName: ru.yandex.clickhouse.ClickHouseDriver

url: jdbc:clickhouse://127.0.0.1:8123/dbname

username: username

password: 123456

initialSize: 10

maxActive: 100

minIdle: 10

maxWait: 6000

mybatis-plus:

mapper-locations: classpath*:mapper/*.xml

configuration:

log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

map-underscore-to-camel-case: true

cache-enabled: true

lazy-loading-enabled: true

multiple-result-sets-enabled: true

use-generated-keys: true

default-statement-timeout: 60

default-fetch-size: 100

type-aliases-package: com.example.tonghp.entity

ClickHouse与Druid连接池配置类：

参数配置：

package com.example.tonghp.config;

import lombok.Data;

import org.springframework.boot.context.properties.ConfigurationProperties;

import org.springframework.stereotype.Component;

/**

* @author: tonghp

* @create: 2021/07/26 16:23

*/

@Data

@Component

@ConfigurationProperties(prefix = "spring.datasource.click")

public class JdbcParamConfig {

private String driverClassName ;

private String url ;

private Integer initialSize ;

private Integer maxActive ;

private Integer minIdle ;

private Integer maxWait ;

private String username;

private String password;

// 省略 GET 和 SET

}

Druid连接池配置

package com.example.tonghp.config;

import com.alibaba.druid.pool.DruidDataSource;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import javax.annotation.Resource;

import javax.sql.DataSource;

import javax.swing.*;

/**

* @author: tonghp

* @create: 2021/07/26 16:22

*/

@Configuration

public class DruidConfig {

@Resource

private JdbcParamConfig jdbcParamConfig ;

@Bean

public DataSource dataSource() {

DruidDataSource datasource = new DruidDataSource();

datasource.setUrl(jdbcParamConfig.getUrl());

datasource.setDriverClassName(jdbcParamConfig.getDriverClassName());

datasource.setInitialSize(jdbcParamConfig.getInitialSize());

datasource.setMinIdle(jdbcParamConfig.getMinIdle());

datasource.setMaxActive(jdbcParamConfig.getMaxActive());

datasource.setMaxWait(jdbcParamConfig.getMaxWait());

datasource.setUsername(jdbcParamConfig.getUsername());

datasource.setPassword(jdbcParamConfig.getPassword());

return datasource;

}

}

接下来配置实体类，mapper，service，controlle以及mapper.xml。与mybatisplus操作mysql一样的思路。

package com.example.tonghp.entity;

import lombok.Data;

import java.io.Serializable;

/**

* @author: tonghp

* @create: 2021/07/26 16:31

*/

@Data

public class UserInfo implements Serializable {

private static final long serialVersionUID = 1L;

private int id;

private String userName;

private String passWord;

private String phone;

private String email;

private String createDay;

}

package com.example.tonghp.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;

import com.example.tonghp.entity.UserInfo;

import org.apache.ibatis.annotations.Mapper;

import org.apache.ibatis.annotations.Param;

import org.springframework.stereotype.Repository;

import java.util.List;

/**

* @author: tonghp

* @create: 2021/07/26 16:32

*/

@Repository

public interface UserInfoMapper extends BaseMapper {

// 写入数据

void saveData (UserInfo userInfo) ;

// ID 查询

UserInfo selectById (@Param("id") Integer id) ;

// 查询全部

List selectList () ;

}

UserInfoMapper.xml

id,user_name,pass_word,phone,email,create_day

INSERT INTO cs_user_info

(id,user_name,pass_word,phone,email,create_day)

VALUES

(#{id,jdbcType=INTEGER},#{userName,jdbcType=VARCHAR},#{passWord,jdbcType=VARCHAR},

#{phone,jdbcType=VARCHAR},#{email,jdbcType=VARCHAR},#{createDay,jdbcType=VARCHAR})

select

from cs_user_info

where id = #{id,jdbcType=INTEGER}

select

from cs_user_info

Service

package com.example.tonghp.service;

import com.baomidou.mybatisplus.extension.service.IService;

import com.example.tonghp.entity.UserInfo;

import org.apache.ibatis.annotations.Param;

import org.springframework.stereotype.Service;

import java.util.List;

/**

* @author: tonghp

* @create: 2021/07/26 16:46

*/

public interface UserInfoService extends IService {

// 写入数据

void saveData (UserInfo userInfo) ;

// ID 查询

UserInfo selectById (@Param("id") Integer id) ;

// 查询全部

List selectList () ;

}

ServiceImpl

package com.example.tonghp.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;

import com.example.tonghp.entity.UserInfo;

import com.example.tonghp.mapper.UserInfoMapper;

import com.example.tonghp.service.UserInfoService;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Service;

import java.util.List;

/**

* @author: tonghp

* @create: 2021/07/26 16:48

*/

@Service

public class UserInfoServiceImpl extends ServiceImpl implements UserInfoService {

@Autowired

UserInfoMapper userInfoMapper;

@Override

public void saveData(UserInfo userInfo) {

userInfoMapper.saveData(userInfo);

}

@Override

public UserInfo selectById(Integer id) {

return userInfoMapper.selectById(id);

}

@Override

public List selectList() {

return userInfoMapper.selectList();

}

}

Controller

package com.example.tonghp.controller;

import com.example.tonghp.entity.UserInfo;

import com.example.tonghp.service.UserInfoService;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;

import java.util.List;

/**

* @author: tonghp

* @create: 2021/07/26 16:45

*/

@RestController

@RequestMapping("user")

public class UserInfoController {

@Autowired

private UserInfoService userInfoService ;

@RequestMapping("saveData")

public String saveData (){

UserInfo userInfo = new UserInfo () ;

userInfo.setId(4);

userInfo.setUserName("winter");

userInfo.setPassWord("567");

userInfo.setPhone("13977776789");

userInfo.setEmail("winter");

userInfo.setCreateDay("2020-02-20");

userInfoService.saveData(userInfo);

return "sus";

}

@RequestMapping("selectById")

public UserInfo selectById () {

return userInfoService.selectById(1) ;

}

@RequestMapping("selectList")

public List selectList () {

return userInfoService.selectList() ;

}

}

main()

package com.example.tonghp;

import org.mybatis.spring.annotation.MapperScan;

import org.springframework.boot.SpringApplication;

import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication

@MapperScan("com.example.tonghp.mapper")

public class TonghpApplication {

public static void main(String[] args) {

SpringApplication.run(TonghpApplication.class, args);

}

}

参考链接：

https://blog.csdn.net/weixin_46792649/article/details/115306384

https://cnblogs.com/ywjfx/p/14333974.html

https://cnblogs.com/cicada-smile/p/11632251.html

https://github.com/cicadasmile/middle-ware-parent

另外还有一种直接操作JDBC的：

https://blog.csdn.net/Alice_qixin/article/details/84957380

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。