SpringBoot2.X整合ClickHouse项目实战-从零搭建整合(三)

是小王同学啊~

已于 2023-02-25 19:40:11 修改

阅读量2.8k

点赞数 2

于 2023-02-19 19:54:09 首次发布

本文链接：https://blog.csdn.net/wnn654321/article/details/125952680

版权

ClickHouse 专栏收录该内容

5 篇文章 3 订阅

订阅专栏

一、ClickHouse+SpringBoot2.X+MybatisPlus整合搭建

二、需求描述和数据库准备

三、ClickHouse统计SQL编写实战和函数使用

四、ClickHouse+SpringBoot2.X案例-基础模块搭建

五、ClickHouse+SpringBoot2.X案例-数据统计接口

service层

mapper层

一、ClickHouse+SpringBoot2.X+MybatisPlus整合搭建

1.在线创建项目 https://start.spring.io/

idea导入刚下载下来的项目

在pom.xml中增加ClickHouse依赖

       <dependency>
            <groupId>ru.yandex.clickhouse</groupId>
            <artifactId>clickhouse-jdbc</artifactId>
            <version>0.1.55</version>
        </dependency>
                
        <!--mybatis plus-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
             <version>3.4.0</version>
        </dependency>

数据库连接配置

server.port=8080
spring.datasource.driver-class-name=ru.yandex.clickhouse.ClickHouseDriver
spring.datasource.url=jdbc:clickhouse://11x.xxx.xx.24x:8123/default
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
logging.level.root=INFO

二、需求描述和数据库准备

指定商品，统计指定日期范围内，各个省市的访问的pv数量，如7天内有各个城市访问某个商品分布

指定商品，多天内的访问曲线变化图，如，1~7号商品访问量波动图

建表语句：

CREATE TABLE default.visit_stats
(
    `product_id` UInt64,//商品id
    `is_new` UInt16,//是否是新用户 1新用户 0老用户
    `province` String,//省名称
    `city` String,//市名称
    `pv` UInt32,//轻度聚合后的访问量
    `visit_time` DateTime //访问时间
)
ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(visit_time)
ORDER BY (
 product_id,
 is_new,
 province,
 city
 );

插入sql：

INSERT into visit_stats values
('1','1','广东','广州',14323,'2023-01-01 12:11:13'),
('1','0','广东','广州',4232,'2023-02-12 16:16:13'),
('1','1','广东','佛山',54323,'2023-03-06 16:11:13'),
('1','0','广东','东莞',42341,'2023-03-02 16:12:13'),
('1','1','广东','梅州',52422,'2023-03-09 12:11:13'),
('2','1','广东','广州',14323,'2021-03-01 12:11:13'),
('2','0','广东','深圳',425232,'2023-04-12 16:16:13'),
('2','1','广东','佛山',543323,'2022-06-06 16:11:13'),
('2','0','广东','东莞',42341,'2021-05-02 16:12:13'),
('2','1','广东','梅州',52422,'2022-01-09 12:11:13'),
('3','1','北京','北京',13132,'2023-01-01 12:11:13'),
('3','0','广东','广州',533232,'2022-02-16 16:16:13'),
('4','1','浙江','杭州',663643,'2023-12-06 12:11:13'),
('4','0','广东','东莞',4142,'2023-11-02 16:12:13'),
('5','1','湖南','长沙',52123,'2022-01-09 12:11:13'),
('4','0','湖南','衡阳',4142,'2024-05-02 16:12:13'),
('5','1','广东','中山',52123,'2024-01-09 12:11:13'),
('2','1','上海','上海',14323,'2021-03-01 12:11:13'),
('5','0','浙江','宁波',425232,'2023-04-12 16:16:13'),
('3','1','广东','佛山',543323,'2022-06-06 16:11:13'),
('2','0','湖南','长沙',42341,'2021-05-02 16:12:13'),
('2','1','广东','深圳',52422,'2022-01-09 12:11:13')

三、ClickHouse统计SQL编写实战和函数使用

统计需求:某个商品再时间范围内地区访问分布-城市级别，天级别

select province,city, sum(pv) pv_count  
from visit_stats where  product_id =1 
and toYYYYMMDD(visit_time) BETWEEN '20200101' and '20241212' 
group by province,city order by pv_count desc

函数:
求和:sum(pv)
年格式:select toYear(toDateTime('2024-12-11 11:12:13'))
日期格式化:select toYYYYMMDD(toDateTime('2024-12-11 11:12:13'))
日期时间格式化：select toYYYYMMDDhhmmss(toDateTime('2024-12-11 11:12:13'))
周格式化,1~7，当前时间是本周第几天，下面是周三结果是3，周日结果是7
select toDayOfWeek(toDateTime('2024-12-11 11:12:13'))

小时格式化，提取时间里面的小时，比如 2023-12-29 10:05:10，格式化后是【10】点
select toHour(toDateTime('2024-12-11 11:12:13'))

分钟格式化，提取时间里面的分钟，比如 2023-12-29 10:05:10，格式化后是【5】分钟
select toMinute(toDateTime('2024-12-11 11:12:13'))

秒格式化，提取时间里面的秒
select toSecond(toDateTime('2024-12-11 11:12:13'))

获取当前日期时间
select now()

获取当前日期
select today()

某个商品，多天内的访问曲线图, 天级别

 select 
  toYYYYMMDD(visit_time) date_time_str, 
  sum(pv) pv_count from visit_stats
 where product_id =2 
 and toYYYYMMDD(visit_time) BETWEEN '20200101' and '20241212' 
 group by date_time_str ORDER BY date_time_str desc

所用函数:

逻辑判断:
SELECT if(cond, then, else)
例子：SELECT if(1, plus(3, 3), plus(6, 8))

如果条件 cond 的计算结果为非零值，则返回表达式 then 的结果，并且跳过表达式 else 的结果
如果 cond 为零或 NULL，则将跳过 then 表达式的结果，并返回 else 表达式的结果

字符串拼接(不能双引号):
select concat('我','上班的时候','没有摸鱼~')

最大、最小、平均值:
select max(pv), min(pv), avg(pv) from visit_stats

四、ClickHouse+SpringBoot2.X案例-基础模块搭建

controller/request层

package net.wnnck.demo.controller.request;

public class VisitRecordPageRequest {

    private long productId;

    private int page;

    private int size;

    public long getProductId() {
        return productId;
    }

    public void setProductId(long productId) {
        this.productId = productId;
    }

    public int getPage() {
        return page;
    }

    public void setPage(int page) {
        this.page = page;
    }

    public int getSize() {
        return size;
    }

    public void setSize(int size) {
        this.size = size;
    }
}


package net.wnnck.demo.controller;

import net.wnnck.demo.controller.request.VisitRecordPageRequest;
import net.wnnck.demo.model.JsonData;
import net.wnnck.demo.service.VisitStatsService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.Map;
@RestController
@RequestMapping("/api/v1/data")
public class DataController {

    @Autowired
    private VisitStatsService visitStatsService;

    @RequestMapping("page")
    public JsonData queryVisitRecord(@RequestBody VisitRecordPageRequest pageRequest){

        Map<String, Object> map = visitStatsService.pageVisitRecord(pageRequest);
        return JsonData.buildSuccess(map);

    }


}

mapper层

package net.wnnck.demo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import net.wnnck.demo.model.VisitStatsDO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

@Mapper
public interface VisitStatsMapper extends BaseMapper<VisitStatsDO> {

    /**
     * 统计总条数
     * @param productId
     * @return
     */
    int countTotal(@Param("productId") long productId);

    /**
     * 分页
     * @param from
     * @param size
     * @return
     */
    List<VisitStatsDO> pageVisitRecord(@Param("productId")Long productId  , @Param("from") int from, @Param("size") int size);


}

resources/mapper/VisitStatsMapper.xml

<?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="net.wnnck.demo.mapper.VisitStatsMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="net.wnnck.demo.model.VisitStatsDO">
        <result column="product_id" property="productId"/>
        <result column="is_new" property="isNew"/>
        <result column="province" property="province"/>
        <result column="city" property="city"/>
        <result column="pv" property="pv"/>
        <result column="visit_time" property="visitTime"/>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        product_id,is_new,province,city,pv,visit_time
    </sql>

    <!--统计总条数-->
    <select id="countTotal" resultType="java.lang.Integer">
        select count(1) from visit_stats  where   product_id=#{productId}
    </select>

    <!--分页查找-->
    <select id="pageVisitRecord" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from visit_stats where product_id=#{productId}
        order by visit_time desc limit #{from},#{size}
    </select>

</mapper>

model层

package net.wnnck.demo.model;

public class JsonData {
    /**
     * 状态码 0 表示成功
     */

    private Integer code;
    /**
     * 数据
     */
    private Object data;
    /**
     * 描述
     */
    private String msg;


    public JsonData(){

    }

    public JsonData(Integer code, Object data, String msg) {
        this.code = code;
        this.data = data;
        this.msg = msg;
    }

    /**
     * 成功，不传入数据
     *
     * @return
     */
    public static JsonData buildSuccess() {
        return new JsonData(0, null, null);
    }

    /**
     * 成功，传入数据
     *
     * @param data
     * @return
     */
    public static JsonData buildSuccess(Object data) {
        return new JsonData(0, data, null);
    }

    /**
     * 失败，传入描述信息
     *
     * @param msg
     * @return
     */
    public static JsonData buildError(String msg) {
        return new JsonData(-1, null, msg);
    }


    /**
     * 自定义状态码和错误信息
     *
     * @param code
     * @param msg
     * @return
     */
    public static JsonData buildCodeAndMsg(int code, String msg) {
        return new JsonData(code, null, msg);
    }

    public Integer getCode() {
        return code;
    }

    public void setCode(Integer code) {
        this.code = code;
    }

    public Object getData() {
        return data;
    }

    public void setData(Object data) {
        this.data = data;
    }

    public String getMsg() {
        return msg;
    }

    public void setMsg(String msg) {
        this.msg = msg;
    }
}


package net.wnnck.demo.model;

public class VisitStatsDO {

    /**
     * 商品
     */
    private Long productId;

    /**
     * 访问时间
     */
    private String visitTime;


    /**
     * 1是新访客，0是老访客
     */
    private Integer isNew;

    /**
     * 访问量
     */
    private Integer pv;

    /**
     * 省份
     */
    private String province;

    /**
     * 城市
     */
    private String city;

    /**
     * ========度量值=========
     */
    private Long pvCount=0L;

    /**
     * 时间的字符串映射，天、小时
     */
    private String dateTimeStr;

    public Long getProductId() {
        return productId;
    }

    public void setProductId(Long productId) {
        this.productId = productId;
    }

    public String getVisitTime() {
        return visitTime;
    }

    public void setVisitTime(String visitTime) {
        this.visitTime = visitTime;
    }

    public Integer getIsNew() {
        return isNew;
    }

    public void setIsNew(Integer isNew) {
        this.isNew = isNew;
    }

    public Integer getPv() {
        return pv;
    }

    public void setPv(Integer pv) {
        this.pv = pv;
    }

    public String getProvince() {
        return province;
    }

    public void setProvince(String province) {
        this.province = province;
    }

    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }

    public Long getPvCount() {
        return pvCount;
    }

    public void setPvCount(Long pvCount) {
        this.pvCount = pvCount;
    }

    public String getDateTimeStr() {
        return dateTimeStr;
    }

    public void setDateTimeStr(String dateTimeStr) {
        this.dateTimeStr = dateTimeStr;
    }
}

service层

package net.wnnck.demo.service;

import net.wnnck.demo.controller.request.VisitRecordPageRequest;

import java.util.Map;

public interface VisitStatsService {


    Map<String,Object> pageVisitRecord(VisitRecordPageRequest pageRequest);
}
package net.wnnck.demo.service.impl;

import net.wnnck.demo.controller.request.VisitRecordPageRequest;
import net.wnnck.demo.mapper.VisitStatsMapper;
import net.wnnck.demo.model.VisitStatsDO;
import net.wnnck.demo.service.VisitStatsService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Service
public class VisitStatsServiceImpl implements VisitStatsService {

    @Autowired
    private VisitStatsMapper visitStatsMapper;

    @Override
    public Map<String, Object> pageVisitRecord(VisitRecordPageRequest pageRequest) {

        Map<String,Object> data = new HashMap<>(3);

        Long productId = pageRequest.getProductId();
        int page = pageRequest.getPage();
        int size = pageRequest.getSize();

        int count = visitStatsMapper.countTotal(productId);

        int from = (page -1) * size;

        List<VisitStatsDO> visitStatsDOS = visitStatsMapper.pageVisitRecord(productId, from, size);

        data.put("total",count);
        data.put("current_page",page);
        data.put("data",visitStatsDOS);

        /**
         * 计算总页数
         */
        int totalPage;
        if(count % size == 0){
            totalPage = count / size;
        }else {
            totalPage = count / size +1 ;
        }

        data.put("total_page",totalPage);
        return data;
    }
}

启动后可正常访问，表示基础环境已搭建好。文末有贴代码下载地址~

五、ClickHouse+SpringBoot2.X案例-数据统计接口

第三节的需求sql整合mapper

service层

    @Override
    public List<VisitStatsDO> queryVisitTrend(VisitTrendQueryRequest queryRequest) {

        Long productId = queryRequest.getProductId();

        String type = queryRequest.getType();

        List<VisitStatsDO> list = null;

        if(type.equalsIgnoreCase("region")){

            list = visitStatsMapper.queryRegionTrendWithMultiDay(productId,queryRequest.getStartTime(),queryRequest.getEndTime());

        } else if(type.equalsIgnoreCase("day")){

            list = visitStatsMapper.queryVisitTrendWithMultiDay(productId,queryRequest.getStartTime(),queryRequest.getEndTime());
        }

        return list;
    }

mapper层

<select id="queryRegionTrendWithMultiDay" resultMap="BaseResultMap">
        select province ,city, sum(pv) pv_count from visit_stats
where product_id = #{productId} and toYYYYMMDD(visit_time) BETWEEN #{startTime} and #{endTime}
GROUP BY province ,city  order by pv_count desc
</select>


 <select id="queryVisitTrendWithMultiDay" resultMap="BaseResultMap">
  select toYYYYMMDD(visit_time) date_time_str,sum(pv) pv_count from visit_stats
  where product_id = #{productId} and toYYYYMMDD(visit_time) BETWEEN #{startTime} and #{endTime}
  GROUP BY date_time_str order by date_time_str desc
 </select>

#时间范围内地区访问分布-城市级别