ClickHouse setup

This is a setup guide for deploying ClickHouse with docker on remote servers.

Installation

You have to have docker preinstalled on your remote system.

Server

Run the following command:

$ docker run -d --name some-clickhouse-server -p 8123:8123 --ulimit nofile=262144:262144 --volume=$HOME/some_clickhouse_database:/var/lib/clickhouse yandex/clickhouse-server

ClickHouse server uses port 8123 as the default port but you can uses any other open port but remember to expose the port to the external network. The server comes with a default users with no password.

Client

Run the following command in the server to connect to the clickhouse server with the default user.

$ docker run -it --rm --link some-clickhouse-server:clickhouse-server yandex/clickhouse-client --host clickhouse-server

Configuration

Open the clickhouse server docker container

$ docker exec -it some-clickhouse-server bash

1. Enable SQL-driven access control and account management for the default user.

In the clickhouse server docker container:

$ cd etc/clickhouse-server

Comment out the following line in user.xml file to enable access control:

<access_management>1</access_management>

Note that this operation is unsafe and after finishing you work, you should change the access control to :

<access_management>0</access_management>

2. Listening to other networks:

In the etc/clickhouse-server/config.xml comment out <listen_host>::</listen_host> to allow remote connections. You should see the port is open in the systems network:

root@myvm:~# lsof -i :8123
COMMAND       PID USER   FD   TYPE   DEVICE SIZE/OFF NODE NAME
docker-pr 1141768 root    4u  IPv6 53989091      0t0  TCP *:8123 (LISTEN)

Creating a DB with users

In this part we create a simple database and a users. Then we Grant privileges of the database to that user.

There are two types of synchronization and they can complement each other:

1. Creating an users

CREATE USER IF NOT EXISTS user1 IDENTIFIED WITH PLAINTEXT_PASSWORD BY 'pass1'

You can check the users with the SHOW USERS command.

2. Creating a database

CREATE DATABASE IF NOT EXISTS db1

You can check the databases with the SHOW DATABASES command.

3. Grant database privileges to the user

You can grant limited privileges or all privileges to a users.

GRANT ALL PRIVILEGES ON db1.* TO user1

4. Connect with the new users to the database

Now we can connect to the server with the created account.

$ docker run -it --rm --link some-clickhouse-server:clickhouse-server yandex/clickhouse-client --host clickhouse-server -u user1 --password pass1 

Make sure the users has all the permissions and has access to the databases:

:) SHOW GRANTS
:) SHOW DATABASES

Creating a Sample Table

Clickhouse supports SQL .

To create a table:

:) USE db1

:) CREATE TABLE names (
 id UInt64,
 name String,
 created DateTime
 ) ENGINE = MergeTree()
 PRIMARY KEY id
 ORDER BY id;

:) SHOW TABLES

There you have it. You can connect to the databases from other networks with the clickhouse-client and your user/password.

Please clone this project and follow the instructions as per the README.md file. After running the docker compose you will have 2 shards and 2 replica setup of clickhouse.

https://github.com/vinitk95/clickhouse-cluster.git