We do not find detailed instruction on running ClickHouse with Docker on the web, so we decide to provide our own step-by-step tutorial.
Background
ClickHouse® is an open-source, high performance columnar OLAP database management system for real-time analytics using SQL.
Docker is a software platform allowing developers to build, test, and deploy applications quickly. Docker packages software into container, a standardized unit abstracting the underlying operating system.
Installation
Install docker before you continue.
Server
Run the following command to start a ClickHouse server in a docker container.
docker run -d --name tutorial-clickhouse-server --ulimit nofile=262144:262144 --volume=$HOME/tutorial_clickhouse_database:/var/lib/clickhouse yandex/clickhouse-serverClient
Run the following command to connect to ClickHouse server with the default user.
docker run -it --rm --link tutorial-clickhouse-server:clickhouse-server yandex/clickhouse-client --host clickhouse-serverConfiguration
Enable SQL-driven access control and account management for the default user
By default, the ClickHouse server provides the default user account which is not allowed using SQL-driven access control and account management but has all the rights and permissions. To enable it, we need to edit a server configuration file.
Run
docker cp <container>:/etc/clickhouse-server/users.xml .which copies it to your current directory (don't miss the last dot) on your local machine. Then use whatever editor you want to edit the file locally.
In users.xml, add <access_management>1</access_management>.
After the change, the file structure should be something like
<users>
<default>
<access_management>1</access_management>
</default>
</users>Note that this is unsafe and you should change it back to <access_management>0</access_management> after finishing your work.
And then run
docker cp users.xml <container>:/etc/clickhouse-server/users.xmlto replace the old one.
Create a user
CREATE USER user1 IDENTIFIED WITH PLAINTEXT_PASSWORD BY 'pass1';You can check the users with SHOW USERS command.
Create a database
CREATE DATABASE IF NOT EXISTS db1You can check the databases with the SHOW DATABASES command.
Grant database privileges to the user
GRANT ALL PRIVILEGES ON db1.* TO user1Connect with the new user
Now we can connect to the server with the created user.
docker run -it --rm --link tutorial-clickhouse-server:clickhouse-server yandex/clickhouse-client --host clickhouse-server -u user1 --password pass1You can run SHOW GRANTS to see the permissions the user has.
Create a table and run some queries
To create a table.
USE db1;
DROP TABLE IF EXISTS employees;
CREATE TABLE employees(
id integer,
name text,
designation text,
manager integer,
hired_on date,
salary integer,
commission float,
dept integer
)
ENGINE = MergeTree()
PRIMARY KEY id
ORDER BY id;Run SHOW TABLES to check the tables.
Run DESCRIBE TABLE employees. You will see something like
┌─name────────┬─type────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ Int32 │ │ │ │ │ │
│ name │ String │ │ │ │ │ │
│ designation │ String │ │ │ │ │ │
│ manager │ Int32 │ │ │ │ │ │
│ hired_on │ Date │ │ │ │ │ │
│ salary │ Int32 │ │ │ │ │ │
│ commission │ Float32 │ │ │ │ │ │
│ dept │ Int32 │ │ │ │ │ │
└─────────────┴─────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘Do some inserts.
INSERT INTO employees (id, name, designation, manager, hired_on, salary, commission, dept) VALUES
(1,'JOHNSON','ADMIN',6,'1990-12-17',18000,NULL,4)
(2,'HARDING','MANAGER',9,'1998-02-02',52000,300,3)
(3,'TAFT','SALES I',2,'1996-01-02',25000,500,3)
(4,'HOOVER','SALES I',2,'1990-04-02',27000,NULL,3)
(5,'LINCOLN','TECH',6,'1994-06-23',22500,1400,4)
(6,'GARFIELD','MANAGER',9,'1993-05-01',54000,NULL,4)
(7,'POLK','TECH',6,'1997-09-22',25000,NULL,4)
(8,'GRANT','ENGINEER',10,'1997-03-30',32000,NULL,2)
(9,'JACKSON','CEO',NULL,'1990-01-01',75000,NULL,4)
(10,'FILLMORE','MANAGER',9,'1994-08-09',56000,NULL,2)
(11,'ADAMS','ENGINEER',10,'1996-03-15',34000,NULL,2)
(12,'WASHINGTON','ADMIN',6,'1998-04-16',18000,NULL,4)
(13,'MONROE','ENGINEER',10,'2000-12-03',30000,NULL,2)
(14,'ROOSEVELT','CPA',9,'1995-10-12',35000,NULL,1);Finally, we can run some queries now.
SELECT designation,COUNT(*) AS nbr, (AVG(salary)) AS avg_salary FROM employees GROUP BY designation ORDER BY avg_salary DESC;
SELECT name,hired_on FROM employees ORDER BY hired_on;Connect to ClickHouse Server with MySQL Client
ClickHouse supports MySQL wire protocol. It can be enabled by mysql_port setting in configuration file: /etc/clickhouse_server/config.xml.
By default, it should be<mysql_port>9004</mysql_port>.
Server
We start a ClickHouse server with the following command, notice that port 9004 is exposed this time.
docker run -d --name tutorial-clickhouse-server -p 9004:9004 --ulimit nofile=262144:262144 --volume=$HOME/tutorial_clickhouse_database:/var/lib/clickhouse yandex/clickhouse-serverClient
Example of connecting using command-line tool mysql:
mysql --protocol tcp -u default -P 9004Output if a connection succeeded:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 0
Server version: 21.8.10.19-ClickHouse
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>Conclusion
You've just started a ClickHouse server using docker, connected to it, created a user, a database, a table, and executed some queries against it. You've also learned how to connect to ClickHouse server using MySQL client.
If you like this tutorial, you might also be interested in our product Bytebase, an open source, web-based schema change management tool, which helps you manage ClickHouse database, supporting SQL review, version control, backup and restore and etc...
the
