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

Question

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

docker-compose logs -f -t superset

superset_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:XXXXXXXXXX@10.200.xx.xx: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 -

docker-compose.yml (https://github.com/apache/superset/blob/master/docker-compose.yml)

#
x-superset-image: &superset-image apache/superset:latest-dev
x-superset-depends-on: &superset-depends-on
  - db
  - redis
x-superset-volumes: &superset-volumes
  # /app/pythonpath_docker will be appended to the PYTHONPATH in the final container
  - ./docker:/app/docker
  - ./superset:/app/superset
  - ./superset-frontend:/app/superset-frontend
  - superset_home:/app/superset_home
  - ./tests:/app/tests

version: "3.7"
services:
  redis:
    image: redis:3.2
    container_name: superset_cache
    restart: unless-stopped
    ports:
      - "127.0.0.1:6379:6379"
    volumes:
      - redis:/data

  db:
    env_file: docker/.env
    image: postgres:10
    container_name: superset_db
    restart: unless-stopped
    ports:
      - "127.0.0.1:5432:5432"
    volumes:
      - db_home:/var/lib/postgresql/data

  superset:
    env_file: docker/.env
    image: *superset-image
    container_name: superset_app
    command: ["/app/docker/docker-bootstrap.sh", "app"]
    restart: unless-stopped
    ports:
      - 8088:8088
    user: "root"
    depends_on: *superset-depends-on
    volumes: *superset-volumes
    environment:
      CYPRESS_CONFIG: "${CYPRESS_CONFIG}"

  superset-init:
    image: *superset-image
    container_name: superset_init
    command: ["/app/docker/docker-init.sh"]
    env_file: docker/.env
    depends_on: *superset-depends-on
    user: "root"
    volumes: *superset-volumes
    environment:
      CYPRESS_CONFIG: "${CYPRESS_CONFIG}"

  superset-node:
    image: node:12
    container_name: superset_node
    command: ["/app/docker/docker-frontend.sh"]
    env_file: docker/.env
    depends_on: *superset-depends-on
    volumes: *superset-volumes

  superset-worker:
    image: *superset-image
    container_name: superset_worker
    command: ["/app/docker/docker-bootstrap.sh", "worker"]
    env_file: docker/.env
    restart: unless-stopped
    depends_on: *superset-depends-on
    user: "root"
    volumes: *superset-volumes

  superset-tests-worker:
    image: *superset-image
    container_name: superset_tests_worker
    command: ["/app/docker/docker-bootstrap.sh", "worker"]
    env_file: docker/.env
    environment:
      DATABASE_HOST: localhost
      DATABASE_DB: test
      REDIS_CELERY_DB: 2
      REDIS_RESULTS_DB: 3
      REDIS_HOST: localhost
    network_mode: host
    depends_on: *superset-depends-on
    user: "root"
    volumes: *superset-volumes

volumes:
  superset_home:
    external: false
  db_home:
    external: false
  redis:
    external: false

Answer 1

To have access to ClickHouse should be installed its driver:

• either by rebuilding the image as described here Superset: Adding New Database Drivers in Docker
• or manually by install it to container

# make sure that 'docker-compose up' started

cd _folder_where_located_docker-compose.yaml_

# to access to CH through native protocol use package 'clickhouse-sqlalchemy' not 'sqlalchemy-clickhouse' (see description below)
# nevertheless I recommend always using 'clickhouse-sqlalchemy' (see https://stackoverflow.com/a/66067453/303298)
docker-compose exec superset pip install clickhouse-sqlalchemy
# docker-compose exec superset pip install sqlalchemy-clickhouse

docker-compose restart

---

case 1: connect to local ClickHouse

For testing let's use the ClickHouse that run on localhost:

• make localhost visible inside of docker-compose by adding option extra_hosts to superset-container in docker-compose.yaml

  ..
  superset:
    ..
    extra_hosts:
      - "host.docker.internal:host-gateway" 

  ..   

• run commands

docker-compose restart

# to access to CH through native protocol use package 'clickhouse-sqlalchemy' not 'sqlalchemy-clickhouse' (see description below)
# nevertheless I recommend always using 'clickhouse-sqlalchemy' (see https://stackoverflow.com/a/66067453/303298)
docker-compose exec superset pip install clickhouse-sqlalchemy
# docker-compose exec superset pip install sqlalchemy-clickhouse

docker-compose restart

• browse http://localhost:8088/databaseview/list and add CH database with url clickhouse://host.docker.internal

---

case 2: connect to external ClickHouse

There are two public ClickHouse:

• Demo Yandex CH

docker run -it --rm yandex/clickhouse-client:latest \
    --host gh-api.clickhouse.tech --user explorer -s

• Demo Altinity.Cloud CH

docker run -it --rm yandex/clickhouse-client:latest \
    --host github.demo.trial.altinity.cloud -s --user demo --password demo

Both servers are available through secure native protocol (not HTTP) so need to install clickhouse-sqlalchemy instead of sqlalchemy-clickhouse because the first one supports native protocol:

# uninstall 'sqlalchemy-clickhouse' if it used before
# docker-compose exec superset pip uninstall sqlalchemy-clickhouse

docker-compose exec superset pip install clickhouse-sqlalchemy
docker-compose restart

Now add a new database with required connection string:

# Demo Yandex ClickHouse
clickhouse+native://explorer@gh-api.clickhouse.tech/default?secure=true

# Demo Altinity.Cloud CH
clickhouse+native://demo:demo@github.demo.trial.altinity.cloud/default?secure=true

---

See also https://stackoverflow.com/a/66067453/303298.