Mila WuBackground
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 quickly build, test, and deploy applications. Docker packages software into a 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-server
Client
Run the following command to connect to the ClickHouse server with the default user.
docker run -it --rm --link tutorial-clickhouse-server:clickhouse-server yandex/clickhouse-client --host clickhouse-server
Configuration
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 the following command
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.
Then run
docker cp users.xml <container>:/etc/clickhouse-server/users.xml
to replace the old one.
Create a user
CREATE USER user1 IDENTIFIED WITH PLAINTEXT_PASSWORD BY 'pass1';
You can check the users with the SHOW USERS command.
Create a database
CREATE DATABASE IF NOT EXISTS db1`
You can check the databases with the SHOW DATABASES command.
Grant database privileges to the user
GRANT ALL PRIVILEGES ON db1.* TO user1
Connect 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 pass1
You can run SHOW GRANTS to see the permissions the user has.
Create a table and run some queries
To create a table, run
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;
Use 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 │ │ │ │ │ │
└─────────────┴─────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
Now 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 the mysql_port setting in the 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, and 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-server
Client
Example of connecting using the command-line tool MySQL:
mysql --protocol tcp -u default -P 9004
Output 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 the 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, that helps you manage ClickHouse database, supporting SQL review, version control, backup and restore etc...
