一、ClickHouse+SpringBoot2.X+MybatisPlus整合搭建
四、ClickHouse+SpringBoot2.X案例-基础模块搭建
五、ClickHouse+SpringBoot2.X案例-数据统计接口
一、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>
#时间范围内地区访问分布-城市级别
多天内的访问曲线图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
转载：https://blog.csdn.net/wnn654321/article/details/125952680
查看评论