ClickHouse 快速入门

1 ClickHouse 介绍

ClickHouse 是由 Yandex 开发的一款开源的列式存储 OLAP（联机分析处理）数据库管理系统，专为海量数据的高性能查询和分析场景设计。
它的核心目标是在秒级甚至毫秒级内，完成对亿级、十亿级乃至百亿级数据的复杂分析查询，广泛应用于大数据统计分析、用户行为分析、日志分析、实时监控报表等场景。
ClickHouse 支持 SQL 查询语法，具备向量化执行引擎、数据分区与分片、多副本高可用等特性，同时支持实时数据写入和批量数据导入，兼顾了查询性能和数据写入的灵活性。

1 行式存储VS列式存储

2 ClickHouse VS MySQL

3 ClickHouse VS Apache Doris

4 ClickHouse 的优缺点

5 ClickHouse 适用的场景

2 ClickHouse 安装

1 镜像下载

PS C:\Users\hyacinth\Desktop>docker pull clickhouse/clickhouse-server:latest latest: Pulling from clickhouse/clickhouse-server 7e49dc6156b0: Pull complete 73e01fe5c14e: Pull complete 4f4fb700ef54: Pull complete 33449e77f874: Pull complete 8c47c9d3110d: Pull complete eb8ee1043261: Pull complete d9d691d19f73: Pull complete d5d232e77897: Pull complete 00790804120e: Pull complete Digest: sha256:dcd74ae94f16bee4bb61a44ab0fc4ff973d5bb42180e52fc82c4fe68cfe70cf6 Status: Downloaded newer imageforclickhouse/clickhouse-server:latest docker.io/clickhouse/clickhouse-server:lates

2 容器运行

PS C:\Users\hyacinth\Desktop>docker run -d --name clickhouse-server --ulimitnofile=262144:262144 -p8123:8123 -p9000:9000 clickhouse/clickhouse-server 41fa47171498b983f257e246e70702342263aebe0e475d319ec1eb608921a837

3 创建用户

• 1 进入容器

PS C:\Users\hyacinth\Desktop>dockerexec-it clickhouse-serverbashroot@41fa47171498:/#

• 2 修改配置

root@41fa47171498:/# vi /etc/clickhouse-server/users.xml

• 3 修改XML
  在users里面增加用户
  标签 | 含义
  -------- | -----
  user_name | 用户名称
  user_pwd | 用户密码

<user_name><password>user_pwd</password><networks><ip>::/0</ip></networks><access_management>1</access_management></user_name>

3 ClickHouse 连接

1 连接

2 建表测试

CREATETABLEmy_first_table(user_id UInt32,message String,timestampDateTime,metric Float32)ENGINE=MergeTreePRIMARYKEY(user_id,timestamp)

4 SpringBoot 集成 ClickHouse

1 依赖

<?xml version="1.0" encoding="UTF-8"?><projectxmlns="http://maven.apache.org/POM/4.0.0"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>4.0.0</version><relativePath/><!-- lookup parent from repository --></parent><groupId>com.xu</groupId><artifactId>spring-clickhouse</artifactId><version>0.0.1-SNAPSHOT</version><name>spring-clickhouse</name><description>Demo project for Spring Boot</description><properties><java.version>25</java.version></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-webmvc</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-devtools</artifactId><scope>runtime</scope><optional>true</optional></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><dependency><groupId>com.clickhouse</groupId><artifactId>clickhouse-jdbc</artifactId><version>0.9.4</version></dependency><dependency><groupId>cn.hutool</groupId><artifactId>hutool-all</artifactId><version>5.8.42</version></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-spring-boot4-starter</artifactId><version>3.5.15</version></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-webmvc-test</artifactId><scope>test</scope></dependency></dependencies><build><plugins><plugin><groupId>org.apache.maven.plugins</groupId><artifactId>maven-compiler-plugin</artifactId><configuration><annotationProcessorPaths><path><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId></path></annotationProcessorPaths></configuration></plugin><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId><configuration><excludes><exclude><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId></exclude></excludes></configuration></plugin></plugins></build></project>

2 配置

# 服务配置spring:application:name:spring-clickhousedatasource:url:jdbc:clickhouse://127.0.0.1:8123/default?useSSL=false&socket_timeout=30000username:testpassword:123456type:com.zaxxer.hikari.HikariDataSourcehikari:maximum-pool-size:10# 连接池最大连接数minimum-idle:2# 最小空闲连接idle-timeout:60000# 空闲连接超时时间connection-timeout:30000# 连接超时时间# MyBatis-Plus 配置（替代原 MyBatis 配置）mybatis-plus:mapper-locations:classpath:mapper/*.xmltype-aliases-package:com.xu.*configuration:map-underscore-to-camel-case:true# 下划线转驼峰log-impl:org.apache.ibatis.logging.stdout.StdOutImplglobal-config:db-config:id-type:NONE# ClickHouse无自增主键，禁用默认主键策略# 日志配置logging:level:com.xu:debug

# 基础镜像FROM eclipse-temurin:25-jre-alpine# 设置工作目录WORKDIR /app# 复制应用COPY ./target/*.jar app.jar# 暴露端口EXPOSE8080# 启动命令ENTRYPOINT["java","-XX:+UseContainerSupport","-jar","app.jar"]

3 Mapper

packagecom.xu.test.mapper;importcom.baomidou.mybatisplus.core.mapper.BaseMapper;importcom.xu.test.entity.MyFirstTable;importorg.apache.ibatis.annotations.Mapper;importorg.apache.ibatis.annotations.Param;importorg.apache.ibatis.annotations.Update;/** * @author hyacinth */@MapperpublicinterfaceMyFirstTableMapperextendsBaseMapper<MyFirstTable>{/** * 自定义更新（ClickHouse UPDATE 语法：ALTER TABLE ... UPDATE） */@Override@Update("ALTER TABLE my_first_table UPDATE "+"message = #{entity.message}, metric = #{entity.metric} "+"WHERE user_id = #{entity.userId}")intupdateById(@Param("entity")MyFirstTableentity);}

4 XML

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPEmapperPUBLIC"-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mappernamespace="com.xu.test.mapper.MyFirstTableMapper"></mapper>

5 Service

packagecom.xu.test.service;importcom.baomidou.mybatisplus.extension.service.IService;importcom.xu.test.entity.MyFirstTable;/** * @author hyacinth */publicinterfaceMyFirstTableServiceextendsIService<MyFirstTable>{}

6 ServiceImpl

packagecom.xu.test.service.impl;importcom.baomidou.mybatisplus.extension.service.impl.ServiceImpl;importcom.xu.test.entity.MyFirstTable;importcom.xu.test.mapper.MyFirstTableMapper;importcom.xu.test.service.MyFirstTableService;importorg.springframework.stereotype.Service;/** * @author hyacinth */@ServicepublicclassMyFirstTableServiceImplextendsServiceImpl<MyFirstTableMapper,MyFirstTable>implementsMyFirstTableService{}

7 Entity

packagecom.xu.test.entity;importcom.baomidou.mybatisplus.annotation.TableField;importcom.baomidou.mybatisplus.annotation.TableId;importcom.baomidou.mybatisplus.annotation.TableName;importlombok.Data;importjava.util.Date;/** * 对应 ClickHouse 的 my_first_table * 注意：ClickHouse 的 MergeTree 主键非自增，且是复合主键 */@Data@TableName("my_first_table")// 明确指定表名（大小写敏感，需和ClickHouse一致）publicclassMyFirstTable{/** * 用户ID（复合主键1） */@TableId@TableField(value="user_id")// 显式指定字段名，避免驼峰转换问题privateIntegeruserId;/** * 消息内容 */@TableField(value="message")privateStringmessage;/** * 时间戳（复合主键2） */@TableField(value="timestamp")privateDatetimestamp;/** * 指标值 */@TableField(value="metric")privateFloatmetric;}

7 Test

packagecom.xu;importcn.hutool.json.JSONUtil;importcom.baomidou.mybatisplus.core.conditions.query.QueryWrapper;importcom.baomidou.mybatisplus.extension.plugins.pagination.Page;importcom.xu.test.entity.MyFirstTable;importcom.xu.test.service.MyFirstTableService;importorg.junit.jupiter.api.Test;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.boot.test.context.SpringBootTest;importjava.time.LocalDateTime;importjava.util.ArrayList;importjava.util.Date;importjava.util.List;@SpringBootTestclassAppTest{@AutowiredprivateMyFirstTableServicemyFirstTableService;@TestvoidcontextLoads(){}@Testpublicvoidsave(){List<MyFirstTable>list=newArrayList<>();for(inti=1;i<=10;i++){MyFirstTableentity=newMyFirstTable();entity.setUserId(i);entity.setMessage("MP测试消息"+i);entity.setTimestamp(newDate());entity.setMetric(20.0f+i);list.add(entity);}booleansuccess=myFirstTableService.saveBatch(list);System.out.println("批量插入是否成功："+success);}@Testpublicvoidpage(){Page<MyFirstTable>page=newPage<>(1,5);QueryWrapper<MyFirstTable>wrapper=newQueryWrapper<>();System.out.println(JSONUtil.toJsonPrettyStr(myFirstTableService.page(page,wrapper)));}@Testpublicvoidupdate(){MyFirstTableentity=newMyFirstTable();entity.setUserId(1);entity.setMessage("MP修改后的消息");entity.setMetric(99.9f);booleansuccess=myFirstTableService.updateById(entity);System.out.println("更新是否成功："+success);}// 4. 测试删除@Testpublicvoiddelete(){myFirstTableService.removeById(1);}}