5

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
LoF10
  • 1,907
  • 1
  • 23
  • 64
  • did you try with `secure=False`? – mucio Sep 03 '19 at 20:58
  • 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 Sep 03 '19 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 Sep 04 '19 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 http://my_ip:8123 – LoF10 Sep 04 '19 at 19:27

2 Answers2

5

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
vladimir
  • 13,428
  • 2
  • 44
  • 70
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.

Kostya
  • 51
  • 4