springboot集成clickhouse

1.clickhouse应用场景，copy：

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

2.整合springboot

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

 <!-- https://mvnrepository.com/artifact/ru.yandex.clickhouse/clickhouse-jdbc -->
        <dependency>
            <groupId>ru.yandex.clickhouse</groupId>
            <artifactId>clickhouse-jdbc</artifactId>
            <version>0.2.4</version>
        </dependency>

        <!-- Druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.4</version>
        </dependency>

        <!-- mybatis-plus依赖 -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.2.0</version>
        </dependency>

配置文件yml：

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    click:
      driverClassName: ru.yandex.clickhouse.ClickHouseDriver
      url: jdbc:clickhouse://xx.xx.xx.xx:8123/default
      username: default
      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.xrj.clickhouse.pojo

clickhouse与Druid配置类：

package com.xrj.clickhouse.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;

@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;
    }
}

package com.xrj.clickhouse.config;

import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

@Data
@Component
@ConfigurationProperties(prefix = "spring.datasource.click")
public class JdbcParamConfig {

    private String driverClassName ;
    private String url ;
    private String username ;
    private String password ;
    private Integer initialSize ;
    private Integer maxActive ;
    private Integer minIdle ;
    private Integer maxWait ;
}

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

@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;
}

@Repository
public interface UserInfoMapper extends BaseMapper<UserInfo> {
    // 写入数据
    void saveData (UserInfo userInfo) ;
    // ID 查询
    UserInfo selectById (@Param("id") Integer id) ;
    // 查询全部
    List<UserInfo> selectList () ;
}

public interface UserInfoService extends IService<UserInfo> {

    // 写入数据
    void saveData (UserInfo userInfo) ;
    // ID 查询
    UserInfo selectById (@Param("id") Integer id) ;
    // 查询全部
    List<UserInfo> selectList () ;
}

@Service
public class UserInfoServiceImpl extends ServiceImpl<UserInfoMapper, UserInfo> 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<UserInfo> selectList() {
        return userInfoMapper.selectList();
    }
}

@RestController
public class UserInfoController {
    @Autowired
    UserInfoService userInfoService;

    @GetMapping("/selectById/{id}")
    public UserInfo selectById(@PathVariable("id") Integer id){
        return userInfoService.selectById(id);
    }

    @PostMapping("/saveData")
    public void saveData(@RequestBody UserInfo userInfo){
         userInfoService.saveData(userInfo);
    }

    @GetMapping("/selectList")
    public List<UserInfo> selectList(){
        return userInfoService.selectList();
    }
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.xrj.clickhouse.mapper.UserInfoMapper">

    <!-- 可根据自己的需求，是否要使用 -->
    <resultMap type="com.xrj.clickhouse.pojo.UserInfo" id="UserInfoMap">
        <result property="id" jdbcType="INTEGER" column="id"/>
        <result property="userName" jdbcType="VARCHAR" column="user_name"/>
        <result property="passWord" jdbcType="VARCHAR" column="pass_word"/>
        <result property="phone" jdbcType="VARCHAR" column="phone"/>
        <result property="email" jdbcType="VARCHAR" column="email"/>
        <result property="createDay" jdbcType="VARCHAR" column="create_day"/>
    </resultMap>

    <sql id="Base_Column_List">
        id,user_name,pass_word,phone,email,create_day
    </sql>
    <insert id="saveData" parameterType="com.xrj.clickhouse.pojo.UserInfo" >
        INSERT INTO cs_user_info
        (id,user_name,pass_word,phone,email)
        VALUES
        (#{id,jdbcType=INTEGER},#{userName,jdbcType=VARCHAR},#{passWord,jdbcType=VARCHAR},
        #{phone,jdbcType=VARCHAR},#{email,jdbcType=VARCHAR})
    </insert>
    <select id="selectById" parameterType="java.lang.Integer" resultMap="UserInfoMap">
        select
        <include refid="Base_Column_List" />
        from cs_user_info
        where id = #{id,jdbcType=INTEGER}
    </select>
    <select id="selectList" resultMap="UserInfoMap" >
        select
        <include refid="Base_Column_List" />
        from cs_user_info
    </select>
</mapper>

后面接着更新docker安装clickhouse的流程