ClickHouse介绍
ClickHouse
是俄罗斯搜索引擎Yandex
开发的一个高性能列式数据库系统,支持SQL
查询,具有高并发、高可靠、高扩展性等特点Java
连接ClickHouse
有以下几种方式- 1、使用
JDBC
连接:ClickHouse
提供了JDBC
驱动,可以使用JDBC API
连接到ClickHouse
,类似于连接其他关系型数据库。可以使用Java中
的JDBC API
对ClickHouse
进行操作,例如执行SQL
查询、插入、更新、删除等操作。连接方式如下:
Class.forName("ru.yandex.clickhouse.ClickHouseDriver");
Connection conn = DriverManager.getConnection("jdbc:clickhouse://localhost:8123/test","default","");
- 2、使用
ClickHouse-Java
客户端连接:ClickHouse-Java
是一个基于HTTP
协议的ClickHouse
客户端,具有跨平台、跨语言、易于使用等特点。可以使用Java
代码通过ClickHouse-Java
连接到ClickHouse
并执行查询、插入、更新、删除等操作。连接方式如下:
ClickHouseDataSource dataSource = new ClickHouseDataSource("jdbc:clickhouse://localhost:8123/test");
- 3、使用
ORM
框架连接:如果使用的Java应用涉及数据存储的逻辑比较复杂,可以使用ORM
框架连接到ClickHouse
,例如Hibernate
、MyBatis
等。ORM
框架会自动将Java
对象映射到ClickHouse
中的表结构,简化了代码编写和维护。ORM
框架连接方式和其他数据库连接方式类似,需要根据具体框架进行配置和使用。 - 我们使用
clickhouse
作为大数据组件,存储前端设备实时采集到的海量数据,用做大数据的存储和分析
问题描述
- clickhouse在客户服务器部署后,启动成功,但是程序无法连接到clickhouse,程序报错拒绝连接,关键报错
internal:8123 [host.docker.internal/172.17.0.1] failed: Connection refused
- 节选程序报错日志如下:
org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is com.alibaba.druid.pool.GetConnectionTimeoutException: wait millis 600, active 0, maxActive 100, creating 0, createErrorCount 1
Caused by: com.alibaba.druid.pool.GetConnectionTimeoutException: wait millis 600, active 0, maxActive 100, creating 0, createErrorCount 1
Caused by: java.lang.RuntimeException: ru.yandex.clickhouse.except.ClickHouseException: ClickHouse exception, code: 210, host: host.docker.internal, port: 8123; Connect to host.docker.internal:8123 [host.docker.internal/172.17.0.1] failed: Connection refused (Connection refused)
Caused by: org.apache.http.conn.HttpHostConnectException: Connect to host.docker.internal:8123 [host.docker.internal/172.17.0.1] failed: Connection refused (Connection refused)
Caused by: java.net.ConnectException: Connection refused (Connection refused)
- 客户的程序和
clickhouse
的配置都是和我们测试环境一致的,但是程序无法连接,继续排查clickhouse
的日志 - 查看
clickhouse
报错日志clickhouse-server.err.log
(默认在/var/log/clickhouse-server/
下),确实是有网络问题,在我们测试环境是没这个报错的
2023.02.25 11:42:11.223352 [ 16413 ] {} <Error> CertificateReloader: Cannot obtain modification time for certificate file /etc/clickhouse-server/server.crt, skipping update. errno: 2, strerror: No such file or directory
2023.02.25 11:42:11.223506 [ 16413 ] {} <Error> CertificateReloader: Cannot obtain modification time for key file /etc/clickhouse-server/server.key, skipping update. errno: 2, strerror: No such file or directory
2023.02.25 11:42:11.224947 [ 16413 ] {} <Error> CertificateReloader: Poco::Exception. Code: 1000, e.code() = 0, SSL context exception: Error loading private key from file /etc/clickhouse-server/server.key: error:02000002:system library:OPENSSL_internal:No such file or directory (version 22.2.2.1)
- 配置一致,初步判断是网络环境不一样,查看了客户的网络配置,发现客户网络只设置了ipv4,禁用了ipv6。查看了clickhouse的配置文件,发现对于ipv4/6,是有不同配置的
处理解决
clickhouse
的网络访问服务配置在config.xml
文件(默认在/etc/clickhouse-server
下),具体是这儿,如下:
<!-- Listen specified address.
Use :: (wildcard IPv6 address), if you want to accept connections both with IPv4 and IPv6 from everywhere.
Notes:
If you open connections from wildcard address, make sure that at least one of the following measures applied:
- server is protected by firewall and not accessible from untrusted networks;
- all users are restricted to subset of network addresses (see users.xml);
- all users have strong passwords, only secure (TLS) interfaces are accessible, or connections are only made via TLS interfaces.
- users without password have readonly access.
See also: https://www.shodan.io/search?query=clickhouse
-->
<listen_host>::</listen_host>
<!-- Same for hosts without support for IPv6: -->
<!-- <listen_host>0.0.0.0</listen_host> -->
<!-- Default values - try listen localhost on IPv4 and IPv6. -->
<!--
<listen_host>::1</listen_host>
<listen_host>127.0.0.1</listen_host>
-->
<!-- Don't exit if IPv6 or IPv4 networks are unavailable while trying to listen. -->
<!-- <listen_try>0</listen_try> -->
- 翻译解释下大致为,ipv4和ipv6的写法不同,如果是开启了ipv4/6,则使用
<listen_host>::</listen_host>
;如果只有ipv4,则使用<listen_host>0.0.0.0</listen_host>
- 看了配置文件的注释,已经了解了,就可以对应处理了,按照不同的网络环境修改配置即可
其他可能的原因和处理方式
- clickhouse服务未启动,或者服务器未联网
- clickhouse启动报错服务终止(例如异常断电造成的文件损坏),未正常提供服务
【已解决】异常断电文件损坏clickhouse启动不了:filesystem error Structure needs cleaning
服务器强制关闭、异常断电等导致clickhouse数据损坏Suspiciously many broken parts to remove
- 应用服务与clickhouse服务网络不通,无法访问
- clickhouse服务限定访问ip,未在允许范围内
- 用户名、密码不正确,拒绝访问
- 访问的数据库或表不存在,其他报错等