ClickHouse 入门学习

Clickhouse的性能完爆MariaDB ColumnStore和 Spark。于是对Clickhouse产生了浓厚的兴趣，所以也打算进行学习。目前Clickhouse还没有中文文档，学习起来还是有点费劲。

那么ClickHouse到底是啥？

1. 开源的列存储数据库管理系统

2. 支持线性扩展

3. 简单方便

4. 高可靠性

5. 容错（支持多主机异步复制，可以跨多个数据中心部署。 单个节点或整个数据中心的停机时间不会影响系统的读写可用性）

clickhouse安装：

1. 添加yum源

yum-config-manager --add-repo http://repo.red-soft.biz/repos/clickhouse/repo/clickhouse-el6.repo

2.  安装：

yum install clickhouse-server clickhouse-client clickhouse-server-common clickhouse-compressor

3. 添加用户clickhouse

useradd clickhouse

4. 启动clickhouse

/etc/init.d/clickhouse-server start

5. 登录测试：

[root@db_server_yayun_01 ~]# clickhouse-client 
ClickHouse client version 1.1.54198.
Connecting to localhost:9000.
Connected to ClickHouse server version 1.1.54198.

:) select 1

SELECT 1

┌─1─┐
│ 1 │
└───┘
→ Progress: 1.00 rows, 1.00 B (64.77 rows/s., 64.77 B/s.) 
1 rows in set. Elapsed: 0.016 sec. 

:) select now();

SELECT now()

┌───────────────now()─┐
│ 2017-03-31 15:14:18 │
└─────────────────────┘
↘ Progress: 1.00 rows, 1.00 B (216.22 rows/s., 216.22 B/s.) 
1 rows in set. Elapsed: 0.005 sec. 

:) 

启动失败可以查看日志，日志的目录默认为

/var/log/clickhouse-server

数据库操作：

#clickhouse-client 
ClickHouse client version 1.1.54343.
Connecting to localhost:9000.
Connected to ClickHouse server version 1.1.54343.

:) 
:) 
:) create database testdb;

CREATE DATABASE testdb

Ok.

0 rows in set. Elapsed: 0.003 sec.

:)

:) use testdb

USE testdb

Ok.

0 rows in set. Elapsed: 0.001 sec.

:)

:) CREATE TABLE arrays_test (s String, arr Array(UInt8)) ENGINE = Memory;

CREATE TABLE arrays_test
(
s String, 
arr Array(UInt8)
)
ENGINE = Memory

Ok.

0 rows in set. Elapsed: 0.003 sec.

:)

:) INSERT INTO arrays_test VALUES ('Hello', [1,2]), ('World', [3,4,5]), ('Goodbye', [])

INSERT INTO arrays_test VALUES

Ok.

3 rows in set. Elapsed: 0.060 sec.

:)

:) SELECT * FROM arrays_test

SELECT *
FROM arrays_test

┌─s───────┬─arr─────┐
│ Hello │ [1,2] │
│ World │ [3,4,5] │
│ Goodbye │ [] │
└─────────┴─────────┘

3 rows in set. Elapsed: 0.003 sec.

:)

:) SELECT s, arr FROM arrays_test ARRAY JOIN arr

SELECT 
s, 
arr
FROM arrays_test 
ARRAY JOIN arr

┌─s─────┬─arr─┐
│ Hello │ 1 │
│ Hello │ 2 │
│ World │ 3 │
│ World │ 4 │
│ World │ 5 │
└───────┴─────┘

5 rows in set. Elapsed: 0.003 sec.

:)