docker 安装 clickhouse 及 配置dbserver

1 安装

1.1 创建配置文件

mkdir -p /usr/local/clickhouse/data
mkdir -p /usr/local/clickhouse/conf
mkdir -p /usr/local/clickhouse/log

1.2 拉取镜像

docker pull yandex/clickhouse-server:20.4.5.36

1.3 创建目录

mkdir -p /usr/local/clickhouse/data/
mkdir -p /usr/local/clickhouse/config/
mkdir -p /usr/local/clickhouse/log/

1.4 创建临时容器

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

1.5 复制临时容器配置到主机

docker cp clickhouse:/etc/clickhouse-server/config.xml /usr/local/clickhouse/config/config.xml
docker cp clickhouse:/etc/clickhouse-server/users.xml  /usr/local/clickhouse/config/users.xml

1.6 生成密码

# 生成指定密码123456
[root@hadoop3 ~]# echo "123456"; echo -n "123456" | sha256sum | tr -d '-'
123456
8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92
 
 

1.7 修改配置文件

#修改default的密码，并且设置为readonly
#新增root配置
vim /usr/local/clickhouse/config/users.xml

<users>
        <!-- If user name was not specified, 'default' user is used. -->
        <default>
            <password_sha256_hex>8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92</password_sha256_hex>
            <networks incl="networks" replace="replace">
                <ip>::/0</ip>
            </networks>
            <!-- Settings profile for user. -->
            <profile>readonly</profile>
            <quota>readonly</quota>
        </default>

        <root>
                <password_sha256_hex>8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92</password_sha256_hex>
                <networks incl="networks" replace="replace">
                        <ip>::/0</ip>
                </networks>
                <profile>default</profile>
                <quota>default</quota>
        </root>
</users>

1.8 重启

docker rm -f clickhouse

docker run -d \
--name clickhouse \
--ulimit nofile=262144:262144 \
-p 8123:8123 \
-p 9000:9000 \
-p 9009:9009 \
-v /usr/local/clickhouse/data:/var/lib/clickhouse:rw \
-v /usr/local/clickhouse/log:/var/log/clickhouse-server:rw \
-v /usr/local/clickhouse/config/config.xml:/etc/clickhouse-server/config.xml \
-v /usr/local/clickhouse/config/users.xml:/etc/clickhouse-server/users.xml \
yandex/clickhouse-server:20.4.5.36

1.9 测试

docker exec -it clickhouse /bin/bash

clickhouse-client --password 
123456

2 java 连接测试

2.1 pom

  <dependency>
            <groupId>cc.blynk.clickhouse</groupId>
            <artifactId>clickhouse4j</artifactId>
            <version>1.4.4</version>
        </dependency>

2.2 Test

package com.rosh.flink.clickhouse;

import java.sql.*;

public class ClickhouseTest {


    public static void main(String[] args) throws ClassNotFoundException, SQLException {


        String driver = "cc.blynk.clickhouse.ClickHouseDriver";
        String ip = "hadoop3";
        String port = "8123";
        String db = "default";
        String user = "default";
        String pwd = "123456";

        Class.forName(driver);
        StringBuffer urlSb = new StringBuffer()
                .append("jdbc:clickhouse://")
                .append(ip).append(":").append(port).append("/").append(db)
                .append("?characterEncoding=utf8&useSSL=false");
        Connection connection = DriverManager.getConnection(urlSb.toString(), user, pwd);
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("show databases");
        if(resultSet != null) {
            while(resultSet.next()) {
                String tableName = resultSet.getString(1);
                System.err.println(tableName);
            }
        }


    }

}

3 dbserver

3.1 配置dbserver

#配置
click
cc.blynk.clickhouse.ClickHouseDriver
jdbc:clickhouse://{host}:{port}[/{database}]
8123

新建好后连接：

3.2 使用

3.3 创建库

 CREATE DATABASE rosh_shop

3.4 创建表

CREATE TABLE rosh_shop.clickstream (
    customer_id String, 
    time_stamp Date, 
    click_event_type String,
    page_code FixedString(20),  
    source_id UInt64
) 
ENGINE = MergeTree()
ORDER BY (time_stamp)

3.5 插入数据

INSERT INTO rosh_shop.clickstream
VALUES ('customer1', '2021-10-02', 'add_to_cart', 'home_enter', 568239 ) 

3.6 查询数据

select * from rosh_shop.clickstream