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

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

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

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

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

controller/request层

mapper层 

 model层

 service层

 五、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_statswhere 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 {@Autowiredprivate 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 {@Autowiredprivate VisitStatsMapper visitStatsMapper;@Overridepublic 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层

    @Overridepublic 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_statswhere product_id = #{productId} and toYYYYMMDD(visit_time) BETWEEN #{startTime} and #{endTime}GROUP BY date_time_str order by date_time_str desc</select>

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

 多天内的访问曲线图pv

代码下载地址:

链接：https://pan.baidu.com/s/1g8dHKiZMQIhJTmuCO814hw?pwd=ex2x 
提取码：ex2x 

ClickHouse快速安装-可视化工具连接-创建第一个ck库表（一）_clickhouse可视化工具_这是王姑娘的微博的博客-CSDN博客OLAP是什么，以及快速安装ClickHouse(容器化部署),CK可视化工具的下载链接使用以及创建第一个CK数据库和表，然后新增数据，浏览3分钟即可快速掌握这些知识https://blog.csdn.net/wnn654321/article/details/125837194ClickHouse常见SQL语法和常见合并数引擎Demo(二)_这是王姑娘的微博的博客-CSDN博客分区是表的分区，把一张表的数据分成N多个区块，分区后的表还是一张表，数据处理还是由自己来完成PARTITION BY，指的是一个表按照某一列数据（比如日期）进行分区，不同分区的数据会写入不同的文件中建表时加入partition概念，可以按照对应的分区字段，允许查询在指定了分区键的条件下，尽可能的少读取数据注意:不是所有的表引擎都可以分区，合并树(MergeTree) 系列的表引擎才支持数据分区，Log系列引擎不支持。...https://blog.csdn.net/wnn654321/article/details/125920177