python - 使用 Python 驱动程序连接到 Dockerized Clickhouse Server 时出现问题

Question

我在使用 python 驱动程序连接到 Windows docker 容器中的 clickhouse 时遇到问题。 Clickhouse 服务器正在我的 E 驱动器上的 docker 容器中运行，暴露于端口 8123。我可以使用此包 https://github.com/hannesmuehleisen/clickhouse-r 在 R 中轻松连接。如下:

conn = DBI::dbConnect(clickhouse::clickhouse(), 
              host = "my_ip", 
              port = 8123L,
              user = "myun",
              password = "mypwd")

但是当我使用 https://clickhouse-driver.readthedocs.io/en/latest/quickstart.html 在 python 中尝试同样的事情时我遇到一个问题:

from clickhouse_driver import Client
client = Client(host = 'my_ip',
                port = '8123',
                user='myun',
                password='mypwd',
                secure=True,
                verify=False,
                database='db_name')

print(client.execute('SELECT now()'))

File "d:\ProgramData\Anaconda3\lib\site-packages\clickhouse_driver\connection.py", line 249, in connect
    '{} ({})'.format(e.strerror, self.get_description())

NetworkError: Code: 210. [SSL: UNKNOWN_PROTOCOL] unknown protocol (_ssl.c:777) (my_ip:8123)

有人知道问题出在哪里吗？

更新:

尝试 secure = F 并得到:

  File "d:\ProgramData\Anaconda3\lib\site-packages\clickhouse_driver\connection.py", line 243, in connect
    '{} ({})'.format(e.strerror, self.get_description())

SocketTimeoutError: Code: 209. None

Answer 1

让我们分别考虑不安全和安全通信:

<小时/>

TCP(非安全通信)

• clickhouse-driver通过 9000 上的 native 协议(protocol)与 ClickHouse 服务器通信-端口
• docker 容器应将端口 9000 发布到主机

docker run -d -p 9000:9000 --ulimit nofile=262144:262144 yandex/clickhouse-server

• 应用代码

client = Client(host='localhost',
                port='9000', # this param can be missed because port 9000 is used by default
                # ..
                database='test')

<小时/>

TCP(安全通信)

• clickhouse-driver 通过 9440 上的 native 协议(protocol)与 ClickHouse 服务器进行通信-端口
• docker 容器应将端口 9440 发布到主机

docker run -d -p 9440:9440 --ulimit nofile=262144:262144 yandex/clickhouse-server

• 配置 ClickHouse

在容器上执行交互式 bash-shell:

docker exec -it {CONTAINER_ID} bash

在容器内进行所需的更改:

apt-get update

# modify config-file
apt-get install nano
# uncomment the '<tcp_port_secure>'-section in config-file & save changes
nano /etc/clickhouse-server/config.xml

# generate a self-signed certificate & 'dhparam.pem'-file
apt-get install openssl

openssl req -subj "/CN=my.host.name" -new -newkey rsa:2048 -days 365 -nodes -x509 -keyout /etc/clickhouse-server/server.key -out /etc/clickhouse-server/server.crt

openssl dhparam -out /etc/clickhouse-server/dhparam.pem 512 # 4096

# set required access mode to 'server.key'-file
chmod 644 /etc/clickhouse-server/server.key

# exit from interactive mode
exit

重新启动容器:

docker restart {CONTAINER_ID}

• 应用代码

client = Client(host='localhost',
                port='9440', # this param can be missed because port 9440 is used by default
                secure=True,
                verify=False,
                # ..
                database='test')

备注:

• 手动配置 Docker 容器内的 SSL 仅用于测试。最好将所需的 SSL 文件和 config.xml 挂载到容器或创建具有所需更改的自定义 Docker 镜像
• 参见Altinity - ClickHouse Networking, Part 2 -深入研究 SSL 配置的文章