springboot使用mybatis应用clickhouse
一、clickhouse，说白了还是数据库，不一样的是clickhouse是列式存储，和传统的MySQL行式存储不同的地方在于，查询和所储。
1）查询，行式和列式的区别，图形说明
说明：理解上来说，行式对于一条数据的完整性索引会更快。而列式对于统计和查询指定数据会更加块。
2）数据存储方式
从数据上面来看，数据索引和查询肯定存在不同的地方。
二、优缺点
a、优点
1）列式存储，适用聚合计算
2）数据压缩，减少IO成本
3）并行查询，提升效率，横向拓展等
4）写入速度快
b、缺点
1）不支持事务
2）不支持高并发，官方qps100
3）避免小数据写入，尽量使用批量写入
4）clickhouse采用并行查询，一个简单的数据查询也需要并行，消耗过大。
三、部署（docker方式）
1）单机
docker run -itd --name=xbd-clickhouse -p 8123:8123 -v /var/lib/clickhouse/:/var/lib/clickhouse/ clickhouse/clickhouse-server:22.12.6.22
2）集群 TODO
（a）docker部署方式
version: "3" services: xbd-zk-1: image: zookeeper:3.6.4 restart: always container_name: xbd-zk-1 privileged: true ports: - 2181:2181 environment: ZOO_MY_ID: 1 ZOO_SERVERS: server.1=xbd-zk-1:2888:3888;2181 server.2=xbd-zk-2:2888:3888;2181 server.3=xbd-zk-3:2888:3888;2181 xbd-zk-2: image: zookeeper:3.6.4 restart: always container_name: xbd-zk-2 privileged: true environment: ZOO_MY_ID: 2 ZOO_SERVERS: server.1=xbd-zk-1:2888:3888;2181 server.2=xbd-zk-2:2888:3888;2181 server.3=xbd-zk-3:2888:3888;2181 xbd-zk-3: image: zookeeper:3.6.4 restart: always container_name: xbd-zk-3 privileged: true environment: ZOO_MY_ID: 3 ZOO_SERVERS: server.1=xbd-zk-1:2888:3888;2181 server.2=xbd-zk-2:2888:3888;2181 server.3=xbd-zk-3:2888:3888;2181 xbd-ch-1: build: context: ./ dockerfile: ./Dockerfile image: xbd/clickhoue:22.12.6.22 restart: always privileged: true container_name: xbd-ch-1 ports: - 8123:8123 volumes: - /var/lib/clickhouse-1:/var/lib/clickhouse environment: - TZ=Asia/Shanghai - CLICKHOUSE_DB=clickhouse - CLICKHOUSE_USER=xbd - CLICKHOUSE_PASSWORD=xbd depends_on: - xbd-zk-1 - xbd-zk-2 - xbd-zk-3 xbd-ch-2: build: context: ./ dockerfile: ./Dockerfile image: xbd/clickhoue:22.12.6.22 restart: always privileged: true container_name: xbd-ch-2 ports: - 8124:8123 volumes: - /var/lib/clickhouse-2:/var/lib/clickhouse environment: - TZ=Asia/Shanghai - CLICKHOUSE_DB=clickhouse - CLICKHOUSE_USER=xbd - CLICKHOUSE_PASSWORD=xbd depends_on: - xbd-zk-1 - xbd-zk-2 - xbd-zk-3 xbd-ch-3: build: context: ./ dockerfile: ./Dockerfile image: xbd/clickhoue:22.12.6.22 restart: always privileged: true container_name: xbd-ch-3 ports: - 8125:8123 volumes: - /var/lib/clickhouse-3:/var/lib/clickhouse environment: - TZ=Asia/Shanghai - CLICKHOUSE_DB=clickhouse - CLICKHOUSE_USER=xbd - CLICKHOUSE_PASSWORD=xbd depends_on: - xbd-zk-1 - xbd-zk-2 - xbd-zk-3
基于zk部署
（b）Dockerfile
FROM clickhouse/clickhouse-server:22.12.6.22 COPY ./config.xml /etc/clickhouse-server/config.xml
（c）config.xml通过临时的容器复制
docker cp xbd-ch-1:/etc/clickhouse-server/config.xml .
修改部分主要包含
<clickhouse> <listen_host>0.0.0.0</listen_host> <timezone>Asia/Shanghai</timezone> <remote_servers> <xbd_cluster> <shard> <weight>1</weight> <internal_replication>false</internal_replication> <replica> <host>xbd-ch-1</host> <port>9000</port> <user>xbd</user> <password>xbd</password> </replica> </shard> <shard> <weight>1</weight> <internal_replication>false</internal_replication> <replica> <host>xbd-ch-2</host> <port>9000</port> <user>xbd</user> <password>xbd</password> </replica> </shard> <shard> <weight>1</weight> <internal_replication>false</internal_replication> <replica> <host>xbd-ch-3</host> <port>9000</port> <user>xbd</user> <password>xbd</password> </replica> </shard> </xbd_cluster> </remote_servers> <zookeeper> <node> <host>xbd-zk-1</host> <port>2181</port> </node> <node> <host>xbd-zk-2</host> <port>2181</port> </node> <node> <host>xbd-zk-3</host> <port>2181</port> </node> </zookeeper> <macros> <layer>xbd_cluster</layer> <shard>01</shard> <replica>xbd-ch-01</replica> </macros> <macros> <layer>xbd_cluster</layer> <shard>02</shard> <replica>xbd-ch-02</replica> </macros> <macros> <layer>xbd_cluster</layer> <shard>03</shard> <replica>xbd-ch-03</replica> </macros> </clickhouse>
部署后进入容器测试
docker exec -it xbd-ch-1 /bin/bash clickhouse-client
select * from system.clusters;
（d）部分语法
CREATE TABLE test on cluster xbd_cluster(id Int64,name String) ENGINE = MergeTree() ORDER BY id
CREATE TABLE test_distributed2 on cluster xbd_cluster as test ENGINE = Distributed(xbd_cluster, clickhouse, test, rand())
四、springboot适用mybatis对接应用clickhouse
1）pom.xml
<dependencies> <dependency> <groupId>ru.yandex.clickhouse</groupId> <artifactId>clickhouse-jdbc</artifactId> <version>0.3.2</version> </dependency> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.5.2</version> </dependency> </dependencies>
2）application.yml配置
spring: datasource: driver-class-name: ru.yandex.clickhouse.ClickHouseDriver url: jdbc:clickhouse://192.168.5.14:8123/ch username: default password: mybatis-plus: mapper-locations: classpath:/mapper/*.xml
是不是感觉和mysql没有差异？no！no！no！
3）查询和添加语法基本上和mysql差不多，创建语句还是很多不一样的。举个例子
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN" "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd"> <mapper namespace="com.xbd.clickhouse.mapper.TestMapper"> <update id="create"> CREATE TABLE test ( id Int64, name String ) ENGINE = MergeTree() ORDER BY id </update> </mapper>
