SpringBoot2 integrates ClickHouse database to achieve high performance data query and analysis

This article source code: GitHub. Click here || GitEE. Click here

Introduction to ClickHouse

1. Basic Introduction

Yandex open source data analysis database, named ClickHouse, is suitable for streaming or batch warehousing of sequential data. ClickHouse should not be used as a general database, but as a distributed real-time processing platform for fast queries of massive data with super high performance. In data aggregation queries (such as GROUP BY), ClickHouse's queries are very fast.

2. Data Analysis Ability

  • OLAP Scenario Features
· Most are read requests
 · Data is always written in fairly large batches (> 1000 rows)
· Do not modify the added data
 · Each query reads a large number of rows from the database, but only a small number of columns are needed at the same time.
· Wide tables, that is, each table contains a large number of columns
 · Less queries (usually hundreds of queries per server per second or less)
· For simple queries, delays of about 50 milliseconds are allowed
 · Data in columns is relatively small: numbers and short strings (for example, 60 bytes per URL)
· Processing a single query requires high throughput (billions of rows per second per server)
· Transactions are not necessary
 · Low requirement for data consistency
 · Each query is small except for a large table
 · Query results are significantly smaller than source data, in other words, data can be filtered or aggregated and stored in the memory of a single server.
  • Formula Data Storage

(1) Linear data

(2) Formula data

(3) Contrastive analysis

Analytical class queries usually require only a small number of columns of the table to be read. Only the data needed can be read in the column database. Data is always packaged and read in batches, so compression is very easy. It is also easier to compress data by column. This further reduces the volume of I/O. As I/O decreases, this will help more data to be cached by the system.

Integrating SpringBook Framework

The case is based on the integration of Druid connection pool and mybatis. The Druid 1.1.10 version of SQL Parser supports the start of clickhouse.

1. Core Dependence

<dependency>
    <groupId>ru.yandex.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.1.53</version>
</dependency>

2. Accessory Data Source

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    click:
      driverClassName: ru.yandex.clickhouse.ClickHouseDriver
      url: jdbc:clickhouse://127.0.0.1:8123/default
      initialSize: 10
      maxActive: 100
      minIdle: 10
      maxWait: 6000

3. Druid connection pool configuration

@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());
        return datasource;
    }
}

4. Parametric Configuration Class

@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 ;
    // Eliminate GET and SET
}

This completes the integration of the code.

3. Demonstration of operation cases

1. Mapper interface

public interface UserInfoMapper {
    // Write data
    void saveData (UserInfo userInfo) ;
    // ID query
    UserInfo selectById (@Param("id") Integer id) ;
    // Query all
    List<UserInfo> selectList () ;
}

Here are three simple interfaces.

2. Mapper.xml file

<mapper namespace="com.click.house.mapper.UserInfoMapper">
    <resultMap id="BaseResultMap" type="com.click.house.entity.UserInfo">
        <id column="id" jdbcType="INTEGER" property="id" />
        <result column="user_name" jdbcType="VARCHAR" property="userName" />
        <result column="pass_word" jdbcType="VARCHAR" property="passWord" />
        <result column="phone" jdbcType="VARCHAR" property="phone" />
        <result column="email" jdbcType="VARCHAR" property="email" />
        <result column="create_day" jdbcType="VARCHAR" property="createDay" />
    </resultMap>
    <sql id="Base_Column_List">
        id,user_name,pass_word,phone,email,create_day
    </sql>
    <insert id="saveData" parameterType="com.click.house.entity.UserInfo" >
        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})
    </insert>
    <select id="selectById" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List" />
        from cs_user_info
        where id = #{id,jdbcType=INTEGER}
    </select>
    <select id="selectList" resultMap="BaseResultMap" >
        select
        <include refid="Base_Column_List" />
        from cs_user_info
    </select>
</mapper>

Here create_day is converted as a string, which needs attention.

3. Control Layer Interface

@RestController
@RequestMapping("/user")
public class UserInfoController {
    @Resource
    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<UserInfo> selectList () {
        return userInfoService.selectList() ;
    }
}

4. Source code address

GitHub·address
https://github.com/cicadasmile/middle-ware-parent
GitEE·address
https://gitee.com/cicadasmile/middle-ware-parent

Tags: Java Database Druid github SQL

Posted on Tue, 08 Oct 2019 21:59:48 -0700 by ubersmuck