Issue connecting to Dockerized Clickhouse Server with Python driver

Question

I am having an issue connecting to clickhouse in a windows docker container with python's driver. Clickhouse server is running on my E drive in a docker container exposed to port 8123. I can connect easily in R with this package https://github.com/hannesmuehleisen/clickhouse-r as so:

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

But when I attempt the same thing in python using https://clickhouse-driver.readthedocs.io/en/latest/quickstart.html I run into an issue:

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)

Anyone know what the issue might be?

Update:

attempted secure = F and got:

  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

tried and got this error: 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 — LoF10
– LoF10, Commented Sep 3, 2019 at 23:19
are you sure my_ip same and belongs to docker container with runned clickhouse-server ? what show telnet my_ip 8123 and ping my_ip from host where you run python code? — Slach
– Slach, Commented Sep 4, 2019 at 18:28
yes it is the same ip address because when I run the same connection in R it works. I also use that IP address to access the server from the browser as my_ip:8123 — LoF10
– LoF10, Commented Sep 4, 2019 at 19:27

vladimir · Accepted Answer · 2020-04-19 07:29:11Z

Let's consider not-secure and secure communication separately:

TCP (non-secure communication)

clickhouse-driver communicate with ClickHouse server over the native protocol on 9000-port
docker container should publish port 9000 to the host

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

app code

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

TCP (secure communication)

clickhouse-driver communicate with ClickHouse server over the native protocol on 9440-port
docker container should publish port 9440 to the host

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

configure ClickHouse

Execute an interactive bash-shell on the container:

docker exec -it {CONTAINER_ID} bash

Make the required changes inside a container:

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

Restart container:

docker restart {CONTAINER_ID}

app code

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

Remarks:

the manual configuration the SSL inside Docker-container is used just for testing. Better to mount required SSL-files and config.xml to container or create custom Docker-image with required changes
see Altinity - ClickHouse Networking, Part 2-article to deep dive into SSL configs

Kostya · Accepted Answer · 2019-12-11 07:22:59Z

3

There are two protocols for communication between ClickHouse server and clients: http (port 8123) and native (port 9000).

Http is suitable for curl/wget and other tools. Most ClickHouse clients use http for data transfer. Including R in your case. But some clients use native protocol (go, and this python client). This protocol is supposed to be more efficient than http. https://clickhouse-driver.readthedocs.io/en/latest/#user-s-guide

Expose port 9000 from server container and use it in client. This port is also default in this client.

answered Dec 11, 2019 at 7:22

Kostya

514 bronze badges

Comments

onionj · Accepted Answer · 2023-09-05 21:41:49Z

0

I had the same issue and I fixed it with this simple action:

login into your Clickhouse with

clickhouse clinet -h 127.0.0.1 -u default --password

and

CREATE DATABASE test;

then connect via code.

answered Sep 5, 2023 at 21:41

onionj

11 bronze badge

Collectives™ on Stack Overflow

Issue connecting to Dockerized Clickhouse Server with Python driver

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related