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

Question

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?

It is part of multiple containers, and many of the services require a database and a few tables to be present for those to work. Same is the case for Clickhouse. — smaikap, Sep 07 '18 at 05:29
Well, you say "like in mysql", but don't show how it is done in mysql. In clickhouse you can add a layer, put sql script in it and run it on container start `cat script.sql | clickhouse-client` — Imaskar, Sep 07 '18 at 06:27

score 5 · Accepted Answer · answered Nov 16 '18 at 05:34

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

score 3 · Answer 2 · answered May 01 '22 at 06:43

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....

This should be the accepted answer – Buddhika Jayawardhana Jun 23 '23 at 22:52 — Buddhika Jayawardhana, Jun 23 '23 at 22:52

score 1 · Answer 3 · answered Feb 15 '22 at 16:03

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

score 0 · Answer 4 · answered Sep 06 '18 at 07:13

0

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.

answered Sep 06 '18 at 07:13

Mostafa Hussein

11,063
3
36
61

Thanks, I'm aware of that. Hoping against hope that there is some feature that Clickhouse has which makes creating DB as easy as MySQL – smaikap Sep 06 '18 at 08:27
1

It is not MySQL itself as an engine that made it easy. This is a feature that made within its docker image so you can do the same on your own with clickhouse image unless provided officially which seems not – Mostafa Hussein Sep 06 '18 at 08:34

score 0 · Answer 5 · answered Oct 01 '18 at 07:38

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.

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

5 Answers5