clickhouse建表_[0] ClickHouse基础

0 CH介绍

CH是列式存储的OLAP数据库，2016年由俄罗斯的Yandex开源，近期大火，在各大厂(美团、携程、腾讯等）中都有比较好的落地案例。

相比于其它的OLAP数据库，CH有如下几个优点：

1. 速度快，尤其是单表查询，官方给出了一些性能测试，链接见:

Performance comparison of database management systems​clickhouse.tech

2. 功能多且易用，主要体现在：

• 支持类SQL的查询，简化上手难度
• 表结构支持数据Array、嵌套、字符串等，数据结构丰富
• 众多库函数支持，比如IP转换、日期转换、字符串计算等

3. 支持多表的关联和嵌套查询

4. 表引擎众多，部分场景下可以优化存储和计算

缺点：

1. 多表关联查询、复杂嵌套子查询场景下查询性能一般，需要人工优化
2. 缺乏UDF能力，在复杂需求下扩展能力较弱
3. 分布式表抽象不完整，用户需要先创建本地表（分片的副本），然后再创建分布式表，并完成分布式表到本地表的映射
4. 不支持自动的数据均衡，弹性伸缩比较困难

还有一个有利有弊的点：

CH不依赖Hadoop生态，自成一体，优点是不依赖诸如HDFS、HBase等组件，缺点是和其它组件/系统集成需要额外的工作。

总体来说，CH的优缺点明显，单表查询下性能突出、类SQL查询语句使用简单，支持多表关联查询但性能有待优化、难以弹性伸缩带来运维复杂性等

1 CH的安装和上手

1.1 CH安装

由于CH不依赖Hadoop生态，所以安装(单机or集群)其实很简单，详细的请见官方文档，此处不赘述

https://clickhouse.tech/docs/en/getting-started/install/​clickhouse.tech

Installation | ClickHouse Documentation Installation | ClickHouse Documentation​clickhouse.tech

本地最简单的方式就是使用Docker安装，此处记录下。

1、安装Docker环境，省略

2、启动Docker服务，省略

3、Docker安装

docker run -d --name ch-server --ulimit nofile=262144:262144 -p 8123:8123 -p 9000:9000 -p 9009:9009 yandex/clickhouse-server

如果出现错误:

driver failed programming external connectivity on endpoint clickhouse

直接重启docker服务即可

service docker restart

1.2 CH上手

本地Docker环境安装好以后，就可以使用了，由于CH支持类SQL的建库、建表和查询，所以实际使用和MySQL基本相似。

1.2.1 配置用户名和密码

CH默认创建了名叫default的库，用户名为default，密码为空，有需要的可以配置下用户名和密码，步骤如下：

修改配置文件：/etc/clickhouse-server/users.xml

生成密码密文：

PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD"; echo -n "$PASSWORD" | sha1sum | tr -d '-' | xxd -r -p | sha1sum | tr -d '-'

假设密码明文为: test，则执行和输出:

执行:
PASSWORD=$(base64 < /dev/urandom | head -c8); echo "test"; echo -n "test" | sha1sum | tr -d '-' | xxd -r -p | sha1sum | tr -d '-'
输出:
test
94bdcebe19083ce2a1f959fd02f964c7af4cfc29 (加密密文)

配置用户：

<users>
<test>
<password_sha256_hex>9f86d081884c7d659a2feaa0c55ad015a3bf4f1b2b0b822cd15d6c15b0f00a08</password_sha256_hex>
<networks incl="networks" replace="replace">
<ip>::/0</ip>
</networks>
<profile>default</profile>
<quota>default</quota>
</test>
</users>

配置的用户名为test，密码为test

1.2.2 Client登录

类似Mysql，登录参数如下：

• --host，-h：服务端的host名称， 默认是localhost
• --port ： 连接的端口，默认9000
• --user，-u：用户名
• --password：密码
• --query，-q：非交互模式下的查询语句
• --database， -d：默认当前操作的数据库。默认值default
• --multiline，-m：允许多行语句查询
• --format， -f：使用指定的默认格式输出结果
• --time，-t：非交互模式下回打印查询执行的时间到窗口
• --stacktrace：如果出现异常，会打印堆栈跟踪信息
• --config-file：配置文件名称

clickhouse-client --host 127.0.0.1 --port 9000 --user test --password test

1.2.3 CH基本操作

1、创建DB

语法:

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster] [ENGINE = engine(...)]

详见:

https://clickhouse.tech/docs/en/sql-reference/statements/create/database/​clickhouse.tech

2、创建表

CH中表有两种：本地表和分布式表，取决于表使用的engine是什么，具体区别请见:

Distributed | ClickHouse Documentation​clickhouse.tech

分布式表只是路由查询到多台server上然后并行的读数据，本身是不存储数据的，所以有分布式表之前需要先有本地表。

创建本地表

建表语法:

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|ALIAS expr1] [compression_codec] [TTL expr1],
    name2 [type2] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|ALIAS expr2] [compression_codec] [TTL expr2],
    ...
) ENGINE = engine

详见:

TABLE | ClickHouse Documentation​clickhouse.tech

需要注意默认情况下: CREATE，DROP， ALTER，RENAME都只是在当前的server上执行， 如果需要在集群上执行需要加上ON CLUSTER子句。

创建分布式表

分布式表结构和本地表结构一致，其需要接受几个额外参数：

• the cluster name in the server’s config file
• the name of a remote database
• the name of a remote table
• (optionally) sharding key
• (optionally) policy name, it will be used to store temporary files for async send

类似:

Distributed(logs, default, hits[, sharding_key[, policy_name]])
SETTINGS
    fsync_after_insert=0,
    fsync_directories=0;

3、查询

此部分和MySQL几乎一致，不再赘述，请参考官方文档

SELECT:

SELECT Query​clickhouse.tech

INSERT:

INSERT INTO | ClickHouse Documentation​clickhouse.tech

ALTER:

ALTER | ClickHouse Documentation​clickhouse.tech

DROP:

DROP | ClickHouse Documentation​clickhouse.tech

RENAME:

RENAME | ClickHouse Documentation​clickhouse.tech

Reference:

1、官方文档 Overview | ClickHouse Documentation