ClickHouse本地开发环境搭建
Windows 10
拉取镜像
docker pull yandex/clickhouse-server
docker pull yandex/clickhouse-client
ClickHouse Server
核心目录:
/etc/clickhouse-server
:这个是ClickHouse Server
默认的配置文件目录,包括全局配置config.xml
和用户配置users.xml
等等。/var/lib/clickhouse
:这个是ClickHouse Server
默认的数据存储目录。/var/log/clickhouse-server
:这个是ClickHouse Server
默认的日志输出目录。
ClickHouse Server
启动前注意:ClickHouse Server
三个端口,这三个端口的默认值是9000
(TCP
协议)、8123
(HTTP
协议)和9009
(集群数据复制)。ClickHouse Server
正常使用需要修改容器系统的文件句柄数量配置ulimit nofile
,可以使用Docker
参数--ulimit nofile=262144:262144
指定文件句柄数。
临时容器拷贝配置
- 创建临时容器
docker run --rm -d --name=temp-clickhouse-server yandex/clickhouse-server
- 拷贝配置文件到宿主机
docker cp temp-clickhouse-server:/etc/clickhouse-server/config.xml E:/Docker/images/clickhouse-server/single/conf/config.xml docker cp temp-clickhouse-server:/etc/clickhouse-server/users.xml E:/Docker/images/clickhouse-server/single/conf/users.xml
- 获取用户密码
SHA256
摘要
进入容器
执行命令生成docker exec -it temp-clickhouse-server /bin/bash
default : default
和root : root
账户密码的SHA256
PASSWORD=$(base64 < /dev/urandom | head -c8); echo "default"; echo -n "default" | sha256sum | tr -d '-' PASSWORD=$(base64 < /dev/urandom | head -c8); echo "root"; echo -n "root" | sha256sum | tr -d '-'
- 修改宿主机配置文件
修改users.xml
,添加用户
修改<!-- Users and ACL. --> <users <default> <password_sha256_hex>37a8eec1ce19687d132fe29051dca629d164e2c4958ba141d5f4133a33f0688f</password_sha256_hex> <networks> <ip>::/0</ip> </networks> <profile>default</profile> <quota>default</quota> </default> <root> <password_sha256_hex>4813494d137e1631bba301d5acab6e7bb7aa74ce1185d456565ef51d737677b2</password_sha256_hex> <networks> <ip>::/0</ip> </networks> <profile>root</profile> <quota>root</quota> </root> </users>
config.xml
,设置listent_host
- 支持 IPV6:
<!-- Listen specified address. --> <listen_host>::</listen_host>
- 不支持 IPV6:
<!-- Listen specified address. --> <listen_host>0.0.0.0</listen_host>
- 支持 IPV6:
- 停止和销毁临时容器
docker stop temp-clickhouse-server
运行
启动服务前,确保 config.xml
和 users.xml
已经存在
命名和容器命名:docker run -d --name=single-clickhouse-server
端口映射:-p 8123:8123 -p 9000:9000 -p 9009:9009
文件句柄数配置:--ulimit nofile=262144:262144
数据目录映射:-v E:/Docker/images/clickhouse-server/single/data:/var/lib/clickhouse:rw
配置目录映射:-v E:/Docker/images/clickhouse-server/single/conf:/etc/clickhouse-server:rw
日志目录映射:-v E:/Docker/images/clickhouse-server/single/log:/var/log/clickhouse-server:rw
镜像:yandex/clickhouse-server
合并成一条命令:
docker run -d --name=single-clickhouse-server -p 8123:8123 -p 9000:9000 -p 9009:9009 --ulimit nofile=262144:262144 -v E:/Docker/images/clickhouse-server/single/data:/var/lib/clickhouse:rw -v E:/Docker/images/clickhouse-server/single/conf:/etc/clickhouse-server:rw -v E:/Docker/images/clickhouse-server/single/log:/var/log/clickhouse-server:rw yandex/clickhouse-server
上面的命令执行完后,Docker Desktop会有几个弹出框确认是否共享宿主机的目录,直接按 share it 按钮即可。
使用ClickHouse Client
连接:
docker run -it --rm --link single-clickhouse-server:clickhouse-server yandex/clickhouse-client -uroot --password root --host clickhouse-server
使用 JDBC 连接
ClickHouse
的 JDBC
驱动目前有三个:
clickhouse-jdbc
(官方):地址是https://github.com/ClickHouse/clickhouse-jdbc
,目前版本是基于Apache Http Client
实现。ClickHouse-Native-JDBC
(第三方):地址是https://github.com/housepower/ClickHouse-Native-JDBC
,基于Socket
实现。clickhouse4j
(第三方):地址是https://github.com/blynkkk/clickhouse4j
,比官方驱动轻量级。
使用官方驱动:
<dependency>
<groupId>ru.yandex.clickhouse</groupId>
<artifactId>clickhouse-jdbc</artifactId>
<version>0.2.4</version>
</dependency>
示例代码:
public class ClickHouseTest {
@Test
public void testCh() throws Exception {
ClickHouseProperties props = new ClickHouseProperties();
props.setUser("root");
props.setPassword("root");
// 不创建数据库的时候会有有个全局default数据库
ClickHouseDataSource dataSource = new ClickHouseDataSource("jdbc:clickhouse://localhost:8123/default", props); ClickHouseConnection connection = dataSource.getConnection();
ClickHouseStatement statement = connection.createStatement();
// 创建一张表,表引擎为Memory,这类表在服务重启后会自动删除
boolean execute = statement.execute("CREATE TABLE IF NOT EXISTS t_test(id UInt64,name String) ENGINE = Memory");
if (execute) {
System.out.println("创建表default.t_test成功");
} else {
System.out.println("表default.t_test已经存在");
}
ResultSet rs = statement.executeQuery("SHOW TABLES");
List<String> tables = Lists.newArrayList();
while (rs.next()) {
tables.add(rs.getString(1));
}
System.out.println("default数据库中的表:" + tables);
PreparedStatement ps = connection.prepareStatement("INSERT INTO t_test(*) VALUES (?,?),(?,?)");
ps.setLong(1, 1L);
ps.setString(2, "throwable");
ps.setLong(3, 2L);
ps.setString(4, "doge");
ps.execute();
statement = connection.createStatement();
rs = statement.executeQuery("SELECT * FROM t_test");
while (rs.next()) {
System.out.println(String.format("查询结果,id:%s,name:%s", rs.getLong("id"), rs.getString("name")));
}
}
}