Connection configuration

ClickHouse SQLAlchemy uses the following syntax for the connection string:

clickhouse<+driver>://<user>:<password>@<host>:<port>/<database>[?key=value..]

Where:

  • driver is driver to use. Possible choices: http, native, asynch. http is default. When you omit driver http is used.

  • database is database connect to. Default is default.

  • user is database user. Defaults to 'default'.

  • password of the user. Defaults to '' (no password).

  • port can be customized if ClickHouse server is listening on non-standard port.

Additional parameters are passed to driver.

Common options

  • engine_reflection controls table engine reflection during table reflection. Engine reflection can be very slow if you have thousand of tables. You can disable reflection by setting this parameter to false. Possible choices: true/false. Default is true.

  • server_version can be used for eliminating initialization select version() query. Generally you shouldn’t set this parameter and server version will be detected automatically.

Driver options

There are several options can be specified in query string.

HTTP

  • port is port ClickHouse server is bound to. Default is 8123.

  • timeout in seconds. There is no timeout by default.

  • protocol to use. Possible choices: http, https. http is default.

  • verify controls certificate verification in https protocol. Possible choices: true/false. Default is true.

Simple DSN example:

clickhouse+http://host/db

DSN example for ClickHouse https port:

clickhouse+http://user:password@host:8443/db?protocol=https

When you are using nginx as proxy server for ClickHouse server connection string might look like:

clickhouse+http://user:password@host:8124/test?protocol=https

Where 8124 is proxy port.

If you need control over the underlying HTTP connection, pass a requests.Session instance to create_engine(), like so:

from sqlalchemy import create_engine
from requests import Session

engine = create_engine(
    'clickhouse+http://localhost/test',
    connect_args={'http_session': Session()}
)

Native

Please note that native connection is not encrypted. All data including user/password is transferred in plain text. You should use this connection over SSH or VPN (for example) while communicating over untrusted network.

Simple DSN example:

clickhouse+native://host/db

All connection string parameters are proxied to clickhouse-driver. See it’s parameters.

Example DSN with LZ4 compression secured with Let’s Encrypt certificate on server side:

import certify

dsn = (
    'clickhouse+native://user:pass@host/db?compression=lz4&'
    'secure=True&ca_certs={}'.format(certify.where())
)

Example with multiple hosts

clickhouse+native://wronghost/default?alt_hosts=localhost:9000

Asynch

Same as Native.

Simple DSN example:

clickhouse+asynch://host/db

All connection string parameters are proxied to asynch. See it’s parameters.