can't connect clickhouse db using superset occur ERROR: Could not load database driver: clickhouse

[calipeng]

URI String is : clickhouse://gpsec:xxx@xxx:8858/sec

docker-compose logs -f -t superset

uperset_app | 2021-02-02T03:23:36.323066230Z 172.17.0.233 - - [02/Feb/2021 03:23:36] "GET /api/v1/database/_info?q=(keys:!(permissions)) HTTP/1.1" 200 -
superset_app | 2021-02-02T03:23:36.323076390Z INFO:werkzeug:172.17.0.233 - - [02/Feb/2021 03:23:36] "GET /api/v1/database/_info?q=(keys:!(permissions)) HTTP/1.1" 200 -
superset_app | 2021-02-02T03:23:36.377123020Z 172.17.0.233 - - [02/Feb/2021 03:23:36] "GET /static/assets/images/favicon.png HTTP/1.1" 200 -
superset_app | 2021-02-02T03:23:36.377136655Z INFO:werkzeug:172.17.0.233 - - [02/Feb/2021 03:23:36] "GET /static/assets/images/favicon.png HTTP/1.1" 200 -
superset_app | 2021-02-02T03:23:48.713990050Z DEBUG:superset.models.core:Database.get_sqla_engine(). Masked URL: clickhouse://gpsec:[email protected]:8858/sec
superset_app | 2021-02-02T03:23:48.716697915Z DEBUG:superset.stats_logger:[stats_logger] (incr) DatabaseRestApi.test_connection.error
superset_app | 2021-02-02T03:23:48.716723303Z DEBUG:superset.stats_logger:[stats_logger] (timing) DatabaseRestApi.test_connection.time | 7.412515580654144
superset_app | 2021-02-02T03:23:48.717956405Z 172.17.0.233 - - [02/Feb/2021 03:23:48] "POST /api/v1/database/test_connection HTTP/1.1" 422 -
superset_app | 2021-02-02T03:23:48.717973565Z INFO:werkzeug:172.17.0.233 - - [02/Feb/2021 03:23:48] "POST /api/v1/database/test_connection HTTP/1.1" 422 -

what you expected to happen.

Actual results

what actually happens.

Screenshots

If applicable, add screenshots to help explain your problem.

How to reproduce the bug

1. Go to '...'
2. Click on '....'
3. Scroll down to '....'
4. See error

Environment

(please complete the following information):

• superset version: superset version
• python version: python --version
• node.js version: node -v

Checklist

Make sure to follow these steps before submitting your issue - thank you!

• I have checked the superset logs for python stacktraces and included it here as text if there are any.
• I have reproduced the issue with at least the latest released version of superset.
• I have checked the issue tracker for the same issue and I haven't found one similar.

Additional context

Add any other context about the problem here.

[calipeng]

dba@noslt-db7:~/incubator-superset$ ./venv/bin/superset --version
Python 3.7.3
Flask 1.1.2
Werkzeug 1.0.1

[calipeng]

ClickHouse server version 20.9.3.45 (official build).

[raphaelauv]

same for me , maybe sqlalchemy-clickhouse is not part of the docker image of dev

using the docker-compose of the repo for superset

superset/superset$ docker-compose up -d

and this clickhouse-server :

docker run --rm -e CLICKHOUSE_DB=my_database -e CLICKHOUSE_USER=username -e CLICKHOUSE_PASSWORD=password -p 9000:9000/tcp yandex/clickhouse-server:21.1.2

with this SQLALCHEMY URI :

clickhouse://username:password@localhost:9000/my_database

[nytai]

There's info for how to add python db drivers in the docker docs

https://github.com/apache/superset/blob/676e0bb28211ec8e588abb8898924771d3dd6f32/docker/README.md#local-packages

[raphaelauv]

Alright ! thank @nytai

by adding sqlalchemy-clickhouse inside superset/.docker/requirements-local.txt

but I still have error connecting to clickhouse, even when I add it to the docker-compose.yml of the repo

clickhouse_db:
  image: yandex/clickhouse-server:20.8
  environment:
    CLICKHOUSE_DB: my_database
    CLICKHOUSE_USER: username
    CLICKHOUSE_PASSWORD: password
  ports:
    - 9000:9000
    - 8123:8123

URI :

clickhouse://username:password@clickhouse_db:9000/my_database

DEBUG:superset.models.core:Database.get_sqla_engine(). Masked URL: clickhouse://username:XXXXXXXXXX@clickhouse_db:9000/my_database
DEBUG:urllib3.connectionpool:Starting new HTTP connection (1): clickhouse_db:9000
DEBUG:superset.stats_logger:[stats_logger] (incr) DatabaseRestApi.test_connection.error
DEBUG:superset.stats_logger:[stats_logger] (timing) DatabaseRestApi.test_connection.time | 11.717678004060872 
172.19.0.1 - - [02/Feb/2021 21:33:04] "POST /api/v1/database/test_connection HTTP/1.1" 422 -
INFO:werkzeug:172.19.0.1 - - [02/Feb/2021 21:33:04] "POST /api/v1/database/test_connection HTTP/1.1" 422 -

Any idee ? thank again

[nytai]

Looks like you got past the missing driver error though. I suspect your connection string is wrong, are you sure it's not port 9001? You could try to connect to your db outside of the superset context to make sure a connection is possible

[raphaelauv]

With dbeaver it's working : jdbc:clickhouse://localhost:8123/my_database

but with superset theses URI :

clickhouse://username:password@localhost:8123/my_database

or

clickhouse://username:password@clickhouse_db:8123/my_database

don't work

[hodgesrm]

Try leaving off the port. The ClickHouse SQLAlchemy driver uses native tcp connectivity and should connect to port 9000. If you leave it off the driver will do the right thing.

[suedschwede]

If you use a password for the default user in clickhouse, you need a different infi.clickhouse_orm version

pip uninstall infi.clickhouse_orm
pip install infi.clickhouse_orm==1.0.4

[raphaelauv]

@suedschwede thank for your help

I changed the clickhouse docker-compose configuration to :

clickhouse_db:
  image: yandex/clickhouse-server:20.8
  environment:
     CLICKHOUSE_DB: my_database
  ports:
    - 9000:9000
    - 8123:8123

I can connect from dbeaver without setting a user and password.

And this URI work in superset

clickhouse://clickhouse_db:8123/my_database

@hodgesrm not providing the port also work when there is no defined user and password

clickhouse://clickhouse_db/my_database

---

I will give a try to pip install infi.clickhouse_orm==1.0.4

[anilvpatel21]

Is there any way to connect with multiple hosts, as a clickhouse driver support alt_hosts as a parameters. Any help?
https://clickhouse-driver.readthedocs.io/en/latest/

[raphaelauv]

@anilvpatel21 I don't understand your question and also what is the relation with superset 😕

[anilvpatel21]

alt_hosts – list of alternative hosts for connection. Example: clickhouse://username:password@host:port/database?alt_hosts=host1:port1,host2:port2

It will assure that if the host is failing it will connect to the alternate host for the high availability.

As in Apache superset this parameters are not acceptable, its a problem of driver I used to connect clickhouse. (sqlachemy-clickhouse)

Also I tried clickhouse-sqlachemy driver which depends on clickhouse-driver and the driver gets loaded and connection is established using TCP protocol, but somehow did not received acknowledgement.

How to assure high availability of clickhouse database while connecting using superset?

8123 worked because it runs on the http protocol.

[Slach]

@anilvpatel21 did you try to use https://github.com/vertamedia/chproxy with HTTP protocol?

[anilvpatel21]

@Slach Thanks. Surely I will look into it. For now we used zookeeper and HAProxy for our systems.