'creating db and tables in a dockerized Clickhouse instance from docker-compose file

My requirement is to create DB and Tables in Clickhouse when I'm bringing it up using docker-compose. If it is mysql, I do it as below :

mysql_1:
      image: mysql:5.7.16
      environment:
        MYSQL_DATABASE: "one"
        MYSQL_USER: "one_user"
        MYSQL_PASSWORD: "one_user_pass"
        MYSQL_ROOT_PASSWORD: "root"
        MYSQL_ALLOW_EMPTY_PASSWORD: "yes"
      volumes:
       - ./data/one:/docker-entrypoint-initdb.d
      ports:
        - "3306:3306"

Is there any way to achieve the same for a Clickhouse instance?

Solution 1:^[1]

I found a not so clean solution for this. It is basically mounting a host directory into the container and running all the "create tables" and "insert" statements, would be persisted as long as we do not clean up the mounted folder!!

clickhouse:
  image: yandex/clickhouse-server:18.10
  ports:
    - "8123:8123"
    - "9000:9000"
  volumes:
   - ./data/clickhouse/data:/var/lib/clickhouse

Solution 2:^[2]

You can use docker init service for the Clickhouse initialisation. Beside the Clickhouse service try to add service with the usage of the same docker image that will init your database

clickhouse-init:
    image: yandex/clickhouse-server
    volumes:
      - ./clickhouse:/var/clickhouse
    depends_on:
      - clickhouse
    networks:
      - ch_ntw
    entrypoint: [ '/bin/sh', '-c' ]
    command: |
      "
      while ! clickhouse-client --host clickhouse --user your-user --password your-password -q \"SHOW databases;\"; do
          echo waiting for clickhouse up
          sleep 1
      done

      clickhouse-client --host clickhouse --user your-user --password your-password --queries-file /var/clickhouse/schema/init_database.sql

      tail -f /dev/null
      "

This docker service will wait for the Clickhouse start and then execute your init_database.sql scripts

Solution 3:^[3]

I have checked Clickhouse and I don't think it provides the same features by default Clickhouse dockerfile.

You can customize Clickhouse image by adding your own requirements like executing files. So all you have to do is to use Clickhouse as a base image for your custom image then maybe writing a bash script that might be used as an entrypoint for your custom image and make it create the database/tables based on the environment variable that you will provide then make it start the service at the end.

Solution 4:^[4]

You can use nafigat0r/clickhouse-server:18.12 docker image. In docker-compose.yml set environment variable DATABASE_NAME for creating (or use) specific database. Optionally you can set DATABASE_PORT for specific TCP port usage.

Current image version has some limitations for SQL queries: one per file. Files with multi-queries not supported.

Solution 5:^[5]

As Add docker-entrypoint-initdb.d support #3695, Clickhouse-server now supports to run initialization scripts in the server's docker image.

for example, in Dockerfile:

FROM clickhouse/clickhouse-server 
ADD  ./docker-entrypoint-initdb.d /docker-entrypoint-initdb.d

add your sql files in docker-entrypoint-initdb.d and build it.

when you generate container from the image, all the script in the docker-entrypoint-initdb.d will run.

By the way, I cannot find the doc on it anywhere except the issue....

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	smaikap
Solution 2	Jozef Cechovsky
Solution 3	Mostafa Hussein
Solution 4	Alexander Yancharuk
Solution 5	shinseitaro

'creating db and tables in a dockerized Clickhouse instance from docker-compose file

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Solution 4:[4]

Solution 5:[5]