docker 安装clickhouse（springboot mybatisplus clickhouse 整合）

最新推荐文章于 2024-08-25 21:35:04 发布

hunheidaode

最新推荐文章于 2024-08-25 21:35:04 发布

阅读量1.9k

点赞数 1

文章标签： docker clickhouse

本文链接：https://blog.csdn.net/hunheidaode/article/details/121747573

版权

该文详细介绍了如何在Docker中启动ClickHouse服务器，设置高文件描述符限制，并通过修改`users.xml`文件来更改默认用户密码。接着，通过停止容器并重新挂载配置目录以持久化设置，然后使用新密码连接到ClickHouse。文章还展示了如何创建数据库、表，插入数据以及通过SpringBoot整合MyBatisPlus进行数据操作。

摘要由CSDN通过智能技术生成

1。命令直接安装

 docker run -d --name ch-server --ulimit nofile=262144:262144 -p 8123:8123 -p 9000:9000 -p 9009:9009 yandex/clickhouse-server

[root@node2 ~]# docker run -d --name ch-server --ulimit nofile=262144:262144 -p 8123:8123 -p 9000:9000 -p 9009:9009 yandex/clickhouse-server
Unable to find image 'yandex/clickhouse-server:latest' locally
latest: Pulling from yandex/clickhouse-server
7b1a6ab2e44d: Already exists 
76f440f4db74: Pull complete 
f68868e956da: Pull complete 
3714e602564a: Pull complete 
5651e551712b: Pull complete 
85827a1464c6: Pull complete 
c976632aa296: Pull complete 
Digest: sha256:db5cbd0a3b4d8977a28f36fad505f056da56c20e08f56347d6daad41b277ab85
Status: Downloaded newer image for yandex/clickhouse-server:latest
99e7a777b161284b210aaecfcd0fcdd22fd2695ba79b5d7e7d799e2c6b2cc0fe
[root@node2 ~]# docker ps
CONTAINER ID   IMAGE                      COMMAND            CREATED              STATUS              PORTS                                                                                                                             NAMES
99e7a777b161   yandex/clickhouse-server   "/entrypoint.sh"   About a minute ago   Up About a minute   0.0.0.0:8123->8123/tcp, :::8123->8123/tcp, 0.0.0.0:9000->9000/tcp, :::9000->9000/tcp, 0.0.0.0:9009->9009/tcp, :::9009->9009/tcp   ch-server

2.当容器起来名字为ch-server；复制容器里面的文件到本地目录 /opt/clickhouse下

docker cp ch-server:/etc/clickhouse-server/ /opt/clickhouse/

[root@node2 opt]# docker cp ch-server:/etc/clickhouse-server/ /opt/clickhouse/
[root@node2 opt]# cd clickhouse/
[root@node2 clickhouse]# ll
总用量 0
drwxrwxrwx 4 root root 72 12月  2 21:33 clickhouse-server
[root@node2 clickhouse]# cd clickhouse-server/
[root@node2 clickhouse-server]# ll
总用量 68
drwxrwxrwx 2 root root    39 12月  2 21:33 config.d
-rw-rw-rw- 1 root root 60072 12月  2 19:21 config.xml
drwxrwxrwx 2 root root     6 12月  2 21:33 users.d
-rw-rw-rw- 1 root root  6248 12月  2 19:21 users.xml

3.看到有文件users.xml

vi users.xml

# 在users.xml配置修改密码<password>123456</password>

修改密码：

4.停止 ch-server容器（docker stop ch-server），重新根据挂载目录启动：

docker run -d --name clickhouse-server --ulimit nofile=262144:262144  -p 8123:8123 -p 9000:9000 -p 9009:9009 \
 -v /opt/clickhouse/clickhouse-server/:/etc/clickhouse-server/ yandex/clickhouse-server

进入容器

docker exec -it clickhouse-server bash

 clickhouse-client -h 127.0.0.1 -d default -m -u default --password '123456'

例如：

[root@node2 clickhouse-server]# docker run -d --name clickhouse-server --ulimit nofile=262144:262144  -p 8123:8123 -p 9000:9000 -p 9009:9009 \
>  -v /opt/clickhouse/clickhouse-server/:/etc/clickhouse-server/ yandex/clickhouse-server
3cf79b8a452bda4b80b8e740fa9d95ad6d682d4c578311c0b57c9e997311e0a7
[root@node2 clickhouse-server]# docker exec -it clickhouse-server bash
root@3cf79b8a452b:/# docker ps
bash: docker: command not found
root@3cf79b8a452b:/# clickhouse-client -h 127.0.0.1 -d default -m -u default --password '123456'
ClickHouse client version 21.11.5.33 (official build).
Connecting to database default at 127.0.0.1:9000 as user default.
Connected to ClickHouse server version 21.11.5 revision 54450.

3cf79b8a452b :) show databases;

SHOW DATABASES

Query id: ff5c3648-7967-4c22-98db-5f31b08d26f8

┌─name───────────────┐
│ INFORMATION_SCHEMA │
│ default            │
│ information_schema │
│ system             │
└────────────────────┘

4 rows in set. Elapsed: 0.002 sec. 

3cf79b8a452b :) create databases zhangqiang;

Syntax error: failed at position 8 ('databases'):

create databases zhangqiang;

Expected one of: FUNCTION, VIEW, LIVE, DATABASE, USER, PROFILE, TABLE, TEMPORARY, ROW POLICY, POLICY, DICTIONARY, QUOTA, OR REPLACE, ROLE, SETTINGS PROFILE, MATERIALIZED

3cf79b8a452b :) help
:-] ;

5.查看数据库：

show databases;
创建数据库：

create databases zhangqiang;
看到数据库已经创建


3cf79b8a452b :) show databases;

SHOW DATABASES

Query id: a1fb9920-1413-4eb8-bf64-e6c6a842ec55

┌─name───────────────┐
│ INFORMATION_SCHEMA │
│ default            │
│ information_schema │
│ system             │
│ zhangqiang         │
└────────────────────┘

5 rows in set. Elapsed: 0.002 sec. 

3cf79b8a452b :)

命令官网链接：

DATABASE | ClickHouse文档

7.创建表并插入数据

clickhouse创建数据库以及表_鸭梨的博客-CSDN博客_clickhouse 建表

root@3cf79b8a452b:/# clickhouse-client -h 127.0.0.1 -d default -m -u default --password '123456'
ClickHouse client version 21.11.5.33 (official build).
Connecting to database default at 127.0.0.1:9000 as user default.
Connected to ClickHouse server version 21.11.5 revision 54450.

3cf79b8a452b :) show databases;

SHOW DATABASES

Query id: 9b040e44-1e2e-4e76-945b-15a4a7a29da4

┌─name───────────────┐
│ INFORMATION_SCHEMA │
│ default            │
│ information_schema │
│ system             │
└────────────────────┘

4 rows in set. Elapsed: 0.006 sec. 

3cf79b8a452b :) create database zhangqiang;

CREATE DATABASE zhangqiang

Query id: 5c33b1f6-089d-4f51-be3e-1c1c676efc47

Ok.

0 rows in set. Elapsed: 0.008 sec. 

3cf79b8a452b :) show databases;

SHOW DATABASES

Query id: f845a5a7-5ded-4381-9fb8-6299f8f92170

┌─name───────────────┐
│ INFORMATION_SCHEMA │
│ default            │
│ information_schema │
│ system             │
│ zhangqiang         │
└────────────────────┘

5 rows in set. Elapsed: 0.002 sec. 

3cf79b8a452b :) use zhangqiang;

USE zhangqiang

Query id: dc3a5dd9-a1ed-445b-883e-41fa2552cb09

Ok.

0 rows in set. Elapsed: 0.001 sec. 

3cf79b8a452b :) show tables;

SHOW TABLES

Query id: 38149f69-14d8-4742-bb54-93d4b5a75b5a

Ok.

0 rows in set. Elapsed: 0.002 sec. 

3cf79b8a452b :) CREATE TABLE IF NOT EXISTS user_local 
:-] (
:-]     id Int32,
:-]     name String
:-] )ENGINE = MergeTree()
:-] ORDER BY id
:-] PARTITION BY id
:-] PRIMARY KEY id;

CREATE TABLE IF NOT EXISTS user_local
(
    `id` Int32,
    `name` String
)
ENGINE = MergeTree
PARTITION BY id
PRIMARY KEY id
ORDER BY id

Query id: 26155112-516f-46cf-a041-a74e20b10cfa

Ok.

0 rows in set. Elapsed: 0.113 sec. 

3cf79b8a452b :) show tables;

SHOW TABLES

Query id: f2dd3cf7-744c-42a6-a9aa-5ff9739c1f78

┌─name───────┐
│ user_local │
└────────────┘

1 rows in set. Elapsed: 0.002 sec. 

3cf79b8a452b :) INSERT INTO user_local VALUES(1,'tom'),(2,'jack');

INSERT INTO user_local FORMAT Values

Query id: d4264188-8838-4170-bd13-0904da9710a8

Ok.

2 rows in set. Elapsed: 0.010 sec. 

3cf79b8a452b :) INSERT INTO user_local VALUES(3,'lilei'),(4,'lihua'); 

INSERT INTO user_local FORMAT Values

Query id: c3a60915-fecc-4b26-a4d2-f6e14a93bd65

Ok.

2 rows in set. Elapsed: 0.003 sec. 

3cf79b8a452b :) select * from user_local;

SELECT *
FROM user_local

Query id: 5509e615-a049-47a9-ad3d-f3a95d179068

┌─id─┬─name─┐
│  1 │ tom  │
└────┴──────┘
┌─id─┬─name──┐
│  3 │ lilei │
└────┴───────┘
┌─id─┬─name──┐
│  4 │ lihua │
└────┴───────┘
┌─id─┬─name─┐
│  2 │ jack │
└────┴──────┘

4 rows in set. Elapsed: 0.003 sec. 

3cf79b8a452b :)

8.springboot mybatisplus clickhouse 整合

github完整地址：GitHub - heidaodageshiwo/common at springboot-clickhouse

结构图：

sql:

 show databases;
 create database zhangqiang;
 use zhangqiang;
show tables;

CREATE TABLE IF NOT EXISTS user_local 
(
    id Int32,
    name String
)ENGINE = MergeTree()
ORDER BY id
PARTITION BY id
PRIMARY KEY id;



INSERT INTO user_local VALUES(1,'tom'),(2,'jack');
 INSERT INTO user_local VALUES(3,'lilei'),(4,'lihua'); 
 select * from user_local;

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="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>2.2.10.RELEASE</version>
    <relativePath/> <!-- lookup parent from repository -->
  </parent>
  <groupId>com.common</groupId>
  <artifactId>common</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <name>common</name>
  <description>common</description>
  <properties>
    <java.version>1.8</java.version>
  </properties>
  <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
    </dependency>

    <!-- https://mvnrepository.com/artifact/com.baomidou/mybatis-plus-boot-starter -->
    <dependency>
      <groupId>com.baomidou</groupId>
      <artifactId>mybatis-plus-boot-starter</artifactId>
      <version>3.4.3.4</version>
    </dependency>

    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.16</version>
    </dependency>
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>fastjson</artifactId>
      <version>1.2.76</version>
    </dependency>
    <dependency>
      <groupId>ru.yandex.clickhouse</groupId>
      <artifactId>clickhouse-jdbc</artifactId>
      <version>0.3.1</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.2.8</version>
    </dependency>

  </dependencies>

  <build>
    <plugins>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
      </plugin>
    </plugins>
  </build>

</project>

application.yml

#spring:
#  datasource:
#    driver-class-name: com.mysql.cj.jdbc.Driver
#    url: jdbc:mysql://localhost:3306/zq?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=UTF-8
#    username: root
#    password: zhangqiang

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    click:
      driverClassName: ru.yandex.clickhouse.ClickHouseDriver
      url: jdbc:clickhouse://192.168.56.213:8123/zhangqiang
      username: default
      password: 123456
      initialSize: 10
      maxActive: 100
      minIdle: 10
      maxWait: 6000

mapper:

package com.common.common.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.common.common.entity.User;
import com.common.common.entity.UserLocal;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

/**
 * java类简单作用描述
 *
 * @ProjectName: common1
 * @Package: com.common.common.mapper
 * @ClassName: UserMapper
 * @Description: java类作用描述
 * @Author: zhangq
 * @CreateDate: 2021-11-18 14:04
 * @UpdateUser: zhangq
 * @UpdateDate: 2021-11-18 14:04
 * @UpdateRemark: The modified content
 * @Version: 1.0 *
 */
@Repository
public interface UserLocalMapper extends BaseMapper<UserLocal> {

  /*@Select("select * from user")
  List<User> getAll();

  @Select("select name,age from user")
  List<Map<String,Object>> getAllMap();*/
}

entity

package com.common.common.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.annotation.JsonInclude;
import com.fasterxml.jackson.annotation.JsonInclude.Include;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * java类简单作用描述
 *
 * @ProjectName: common1
 * @Package: com.common.common.entity
 * @ClassName: User
 * @Description: java类作用描述
 * @Author: zhangq
 * @CreateDate: 2021-11-18 14:03
 * @UpdateUser: zhangq
 * @UpdateDate: 2021-11-18 14:03
 * @UpdateRemark: The modified content
 * @Version: 1.0 *
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName("user_local")
@JsonInclude(Include.ALWAYS)
public class UserLocal {
  @TableId(type = IdType.ASSIGN_UUID)
  private Long id;
  @TableField(value = "name")
  private String name;
}

UserLocalClickHouseController

package com.common.common.controller;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.common.common.entity.User;
import com.common.common.entity.UserLocal;
import com.common.common.mapper.UserLocalMapper;
import com.common.common.mapper.UserMapper;
import com.common.common.service.UserService;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

/**
 * java类简单作用描述
 *
 * @ProjectName: common1
 * @Package: com.common.common.controller
 * @ClassName: UserController
 * @Description: java类作用描述
 * @Author: zhangq
 * @CreateDate: 2021-11-18 14:05
 * @UpdateUser: zhangq
 * @UpdateDate: 2021-11-18 14:05
 * @UpdateRemark: The modified content
 * @Version: 1.0 *
 */
@RestController
public class UserLocalClickHouseController {

  @Autowired
  private UserLocalMapper userLocalMapper;


  //mybatisplus分支操作
  @RequestMapping("/selectclickHouse")
  public List<UserLocal> testSelect() {
    System.out.println(("----- selectAll method test ------"));
    List<UserLocal> userList = userLocalMapper.selectList(null);
//    Assert.assertEquals(5, userList.size());
    userList.forEach(System.out::println);
    return userList;
  }

}

DruidConfig

package com.common.common.config;

import com.alibaba.druid.pool.DruidDataSource;
import javax.annotation.Resource;
import javax.sql.DataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * java类简单作用描述
 *
 * @ProjectName: common1
 * @Package: com.common.common.config
 * @ClassName: DruidConfig
 * @Description: java类作用描述
 * @Author: zhangq
 * @CreateDate: 2021-12-06 16:30
 * @UpdateUser: zhangq
 * @UpdateDate: 2021-12-06 16:30
 * @UpdateRemark: The modified content
 * @Version: 1.0 *
 */

@Configuration
public class DruidConfig {
  @Resource
  private JdbcParamConfig jdbcParamConfig ;

  @Bean
  public DataSource dataSource() {
    DruidDataSource datasource = new DruidDataSource();
    datasource.setUrl(jdbcParamConfig.getUrl());
    datasource.setDriverClassName(jdbcParamConfig.getDriverClassName());
    datasource.setInitialSize(jdbcParamConfig.getInitialSize());
    datasource.setMinIdle(jdbcParamConfig.getMinIdle());
    datasource.setMaxActive(jdbcParamConfig.getMaxActive());
    datasource.setMaxWait(jdbcParamConfig.getMaxWait());
    datasource.setUsername(jdbcParamConfig.getUsername());
    datasource.setPassword(jdbcParamConfig.getPassword());
    return datasource;
  }
}

JdbcParamConfig

package com.common.common.config;

import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

/**
 * java类简单作用描述
 *
 * @ProjectName: common1
 * @Package: com.common.common.config
 * @ClassName: JdbcParamConfig
 * @Description: java类作用描述
 * @Author: zhangq
 * @CreateDate: 2021-12-06 16:29
 * @UpdateUser: zhangq
 * @UpdateDate: 2021-12-06 16:29
 * @UpdateRemark: The modified content
 * @Version: 1.0 *
 */

@Data
@Component
@ConfigurationProperties(prefix = "spring.datasource.click")
public class JdbcParamConfig {
  private String driverClassName ;
  private String url ;
  private String username ;
  private String password ;
  private Integer initialSize ;
  private Integer maxActive ;
  private Integer minIdle ;
  private Integer maxWait ;

}

MybatisPlusConfig

package com.common.common.config;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.transaction.annotation.EnableTransactionManagement;

/**
 * java类简单作用描述
 *
 * @ProjectName: common1
 * @Package: com.common.common.config
 * @ClassName: MybatisPlusConfig
 * @Description: java类作用描述
 * @Author: zhangq
 * @CreateDate: 2021-11-19 8:48
 * @UpdateUser: zhangq
 * @UpdateDate: 2021-11-19 8:48
 * @UpdateRemark: The modified content
 * @Version: 1.0 *
 */
@EnableTransactionManagement
@Configuration
@MapperScan("com.common.common.mapper")
public class MybatisPlusConfig {

  // 最新版
  @Bean
  public MybatisPlusInterceptor mybatisPlusInterceptor() {
    MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
    interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.CLICK_HOUSE));
    return interceptor;
  }

}

运行效果：

插入数据在运行：

3cf79b8a452b :) INSERT INTO user_local VALUES(1111111111111,'最终测试');

INSERT INTO user_local FORMAT Values

Query id: 4de19a2c-1891-433e-b581-6fb575d4c560

Ok.

1 rows in set. Elapsed: 0.002 sec. 

3cf79b8a452b :) select * from user_local;

SELECT *
FROM user_local

Query id: 4b54b22a-a9ec-41da-b426-12915da36677

┌─id─┬─name─┐
│  2 │ jack │
└────┴──────┘
┌──────────id─┬─name─────┐
│ -1285418553 │ 最终测试 │
└─────────────┴──────────┘
┌─id─┬─name──┐
│  3 │ lilei │
└────┴───────┘
┌─id─┬─name─┐
│  1 │ tom  │
└────┴──────┘
┌─id─┬─name──┐
│  4 │ lihua │
└────┴───────┘

5 rows in set. Elapsed: 0.003 sec. 

3cf79b8a452b :)

hunheidaode

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
docker 安装clickhouse（springboot mybatisplus clickhouse 整合）

1。命令直接安装 docker run -d --name ch-server --ulimit nofile=262144:262144 -p 8123:8123 -p 9000:9000 -p 9009:9009 yandex/clickhouse-server[root@node2 ~]# docker run -d --name ch-server --ulimit nofile=262144:262144 -p 8123:8123 -p 9000:9000 -p 9009:9009 y
复制链接

扫一扫