1、介紹

ClickHouse是俄羅斯的Yandex于2016年開源的列式存儲(chǔ)數(shù)據(jù)庫（DBMS），使用C++語言編寫，主要用于在線分析處理查詢（OLAP），能夠使用SQL查詢實(shí)時(shí)生成分析數(shù)據(jù)報(bào)告。

OLAP（On-Line Analytical Processing）翻譯為聯(lián)機(jī)分析處理，專注于分析處理，從對(duì)數(shù)據(jù)庫操作來看，OLAP是對(duì)數(shù)據(jù)的查詢；
OLTP（on-line transaction processing）翻譯為聯(lián)機(jī)事務(wù)處理，專注于事務(wù)處理，從對(duì)數(shù)據(jù)庫操作來看，OLTP主要是對(duì)數(shù)據(jù)的增刪改。

1.1、特點(diǎn)：列式存儲(chǔ)

以下面的表為例：

1）采用行式存儲(chǔ)時(shí)，數(shù)據(jù)在磁盤上的組織結(jié)構(gòu)為：

好處是想查某個(gè)人所有的屬性時(shí)，可以通過一次磁盤查找加順序讀取就可以。但是當(dāng)想查所有人的年齡時(shí)，需要不停的查找，或者全表掃描才行，遍歷的很多數(shù)據(jù)都是不需要的。

2）采用列式存儲(chǔ)時(shí)，數(shù)據(jù)在磁盤上的組織結(jié)構(gòu)為：

這時(shí)想查所有人的年齡只需把年齡那一列拿出來就可以了。

3）列式存儲(chǔ)的好處：

對(duì)于列的聚合、計(jì)數(shù)、求和等統(tǒng)計(jì)操作原因優(yōu)于行式存儲(chǔ)；
由于某一列的數(shù)據(jù)類型都是相同的，針對(duì)于數(shù)據(jù)存儲(chǔ)更容易進(jìn)行數(shù)據(jù)壓縮，每一列選擇更優(yōu)的數(shù)據(jù)壓縮算法，大大提高了數(shù)據(jù)的壓縮比重；
由于數(shù)據(jù)壓縮比更好，一方面節(jié)省了磁盤空間，另一方面對(duì)于cache也有了更大的發(fā)揮空間
DBMS的功能：幾乎覆蓋了標(biāo)準(zhǔn)SQL的大部分語法，包括DDL和DML，以及配套的各種函數(shù)，用戶管理及權(quán)限管理，數(shù)據(jù)的備份與恢復(fù)；

1.2、多樣化引擎

ClickHouse和MySQL類似，把表級(jí)的存儲(chǔ)引擎插件化，根據(jù)表的不同需求可以設(shè)定不同的存儲(chǔ)引擎。目前包括合并樹、日志、接口和其他四大類20多種引擎；

1.3、高吞吐寫入能力

ClickHouse采用類LSM Tree的結(jié)構(gòu)，數(shù)據(jù)寫入后定期在后臺(tái)Compaction。通過類LSM tree的結(jié)構(gòu)，ClickHouse在數(shù)據(jù)導(dǎo)入時(shí)全部是順序append寫，寫入后數(shù)據(jù)段不可更改，在后臺(tái)compaction時(shí)也是多個(gè)段merge sort后順序?qū)懟卮疟P。順序?qū)懙奶匦?，充分利用了磁盤的吞吐能力，即便在HDD上也有著優(yōu)異的寫入性能。

官方公開benchmark測(cè)試顯示能夠達(dá)到50MB-200MB/s的寫入吞吐能力，按照每行100Byte估算，大約相當(dāng)于50W-200W條/s的寫入速度

1.4、數(shù)據(jù)分區(qū)與線程級(jí)并行

ClickHouse將數(shù)據(jù)劃分為多個(gè)partition，每個(gè)partition再進(jìn)一步劃分為多個(gè)index granularity（索引粒度），然后通過多個(gè)CPU核心分別處理其中的一部分來實(shí)現(xiàn)并行數(shù)據(jù)處理。在這種設(shè)計(jì)下，單條Query就能利用整機(jī)所有CPU。極致的并行處理能力，極大的降低了查詢延時(shí)

所以，ClickHouse即使對(duì)于大量數(shù)據(jù)的查詢也能夠化整為零平行處理。但是有一個(gè)弊端就是對(duì)于單條查詢使用多cpu，就不利于同時(shí)并發(fā)多條查詢。所以對(duì)于高qps的查詢業(yè)務(wù)，ClickHouse并不是強(qiáng)項(xiàng)

2、安裝

2.1、準(zhǔn)備工作

確定防火墻處于關(guān)閉狀態(tài)

CentOS取消打開文件數(shù)限制

sudo vim /etc/security/limits.conf

在末尾加入：

* soft nofile 65536
* hard nofile 65536
* soft nproc 131072
* hard nproc 131072

第一列是限制的用戶和用戶組，soft軟限制，hard硬限制，nofile打開文件數(shù)，nproc用戶進(jìn)程數(shù)，退出當(dāng)前用戶，重啟登錄，ulimit -a查看打開文件數(shù)和用戶進(jìn)程數(shù)是否更改

[root@aliyun ~]# ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 7284
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 65536
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 131072
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

2.2、安裝依賴

sudo yum install -y libtool
sudo yum install -y *unixODBC*

CentOS取消SELINUX（不知道為什么我修改后，就沒網(wǎng)了） vim /etc/selinux/config 修改為：SELINUX=disabled 修改完重啟服務(wù)器

單機(jī)安裝，下載安裝包，安裝包下載，需要以下四個(gè)rpm包：

clickhouse-client-21.7.3.14-2.noarch.rpm
clickhouse-common-static-21.7.3.14-2.x86_64.rpm
clickhouse-common-static-dbg-21.7.3.14-2.x86_64.rpm
clickhouse-server-21.7.3.14-2.noarch.rpm

mac下要下載arm的，注意！?。?/p>

wget https://repo.yandex.ru/clickhouse/rpm/stable/x86_64/clickhouse-client-21.7.3.14-2.noarch.rpm
wget https://repo.yandex.ru/clickhouse/rpm/stable/x86_64/clickhouse-common-static-21.7.3.14-2.x86_64.rpm
wget https://repo.yandex.ru/clickhouse/rpm/stable/x86_64/clickhouse-common-static-dbg-21.7.3.14-2.x86_64.rpm
wget https://repo.yandex.ru/clickhouse/rpm/stable/x86_64/clickhouse-server-21.7.3.14-2.noarch.rpm

2.3、修改配置文件

cd /etc/clickhouse-server/
sudo chmod 777 config.xml?
sudo vim config.xml?

把<listen_host>0.0.0.0</listen_host>的注釋打開，這樣的話才能讓ClickHouse被除本機(jī)之外的服務(wù)器訪問。

這個(gè)配置文件中，ClickHouse一些默認(rèn)路徑配置：

數(shù)據(jù)文件路徑： /var/lib/clickhouse/
日志文件路徑：/var/log/clickhouse-server/clickhouse-server.log
啟動(dòng)Server

sudo systemctl start clickhouse-server
或者
sudo clickhouse start

查看啟動(dòng)狀態(tài)：

sudo systemctl status clickhouse-server
或者
sudo clickhouse status

關(guān)閉開啟自啟

sudo systemctl disable clickhouse-server

2.4、使用client連接server

clickhouse-client -m

在連接的過程中出現(xiàn)了兩個(gè)錯(cuò)誤：錯(cuò)誤一：

Code: 210. DB::NetException: Connection 
refused (localhost:9000). (NETWORK_ERROR)

如果在配置文件中有<listen_host>::</listen_host>，就改成<listen_host>0.0.0.0</listen_host>，因?yàn)?:是IPv6的通配符，我部署clickhouse的機(jī)器不支持ipv6。

錯(cuò)誤二：

Code: 516. DB::Exception: Received from localhost:9000. DB::Exception: default: Authentication failed: password is incorrect or there is no user with such name. (AUTHENTICATION_FAILED)
在命令中帶上–password：clickhouse-client -m --password

3、數(shù)據(jù)類型

3.1、整形

固定長(zhǎng)度的整型，包括有符號(hào)整型或無符號(hào)整型：Int8、Int16、Int32、Int64

3.2、浮點(diǎn)數(shù)

Float32、Float64

浮點(diǎn)數(shù)計(jì)算精度缺失問題：select 1.0-0.9

┌──────minus(1., 0.9)─┐
│ 0.09999999999999998 │
└─────────────────────┘

3.3、布爾型

沒有單獨(dú)的類型來存儲(chǔ)布爾值?？梢允褂肬Int8類型，取值限制為0或1。

Decimal型

Decimal32(s)相當(dāng)于Decimal(9-s,s)

Decimal64(s)相當(dāng)于Decimal(18-s,s)

Decimal128(s)相當(dāng)于Decimal(38-s,s)

3.4、字符串

String：字符串可以任意長(zhǎng)度的。它可以包含任意的字節(jié)集，包含空字節(jié)；
FixedString(N)：固定長(zhǎng)度N的字符串，N必須是嚴(yán)格的正自然數(shù)。當(dāng)服務(wù)端讀取長(zhǎng)度小于N的字符串時(shí)候，通過在字符串末尾添加空字節(jié)來達(dá)到N字節(jié)長(zhǎng)度。當(dāng)服務(wù)端讀取長(zhǎng)度大于N的字符串時(shí)候，將返回錯(cuò)誤消息。

3.5、枚舉類型

包括Enum8和Enum16類型。Enum保存**’string’=integer**的對(duì)應(yīng)關(guān)系

Enum8用’string’=Int8來描述
Enum16用’string’=Int16來描述
創(chuàng)建一個(gè)帶有一個(gè)枚舉Enum8(‘hello’ = 1, ‘world’ = 2)類型的列：

create table t_enum(
x Enum8('hello' = 1,'world' = 2)
)engine = TinyLog;

這個(gè)x列只能存儲(chǔ)類型定義中列出的值：‘hello’或’world’：

inser tinto t_enum values ('hello'),('hello'),('world'),('world');

如果嘗試保存任何其他值，ClickHouse拋出異常：

insert into t_enum values('a');

如果需要看到對(duì)應(yīng)行的數(shù)值，則必須將Enum值轉(zhuǎn)換為整數(shù)類型：

select cast(x,'Int8') from t_enum;

3.6、時(shí)間類型

目前ClickHouse有三種時(shí)間類型：

Date接受年-月-日的字符串，比如：2019-12-16；
Datetime接受年-月-日 時(shí):分:秒的字符串，比如2019-12-16 20:50:10；
Datetime64 接受年-月-日 時(shí):分:秒.亞秒的字符串，比如2019-12-16 20:50:10.66。
日期類型用兩個(gè)字節(jié)存儲(chǔ)，表示從1970-01-01到當(dāng)前的日期值

3.7、數(shù)組

Array(T)：由T類型元素組成的數(shù)組，T可以是任意類型，包含數(shù)組類型。但不推薦使用多維數(shù)組，ClickHouse對(duì)多維數(shù)組的支持有限。例如，不能在MergeTree表中存儲(chǔ)多維數(shù)組，創(chuàng)建數(shù)組方式：使用array函數(shù)

select array(1, 2) as x, toTypeName(x);

使用方括號(hào)

select [1, 2] as x, toTypeName(x);

4、SQL操作

Insert
基本與標(biāo)準(zhǔn)SQL（MySQL）基本一致：

標(biāo)準(zhǔn)：

insert into [table_name] values(…),(…)

從表中插入表：

insert into [table_name] select a,b,c from [table_name_2]

Update和Delete

ClickHouse提供了Delete和Update的能力，這類操作被稱為Mutation查詢，它可以看做Alter的一種。雖然可以實(shí)現(xiàn)修改和刪除，但是和一般的OLTP數(shù)據(jù)庫不一樣，Mutation語句是一種很重的操作，而且不支持事務(wù)。

重的原因主要是每次修改或者刪除都會(huì)導(dǎo)致放棄目標(biāo)數(shù)據(jù)的原有分區(qū)，重建新分區(qū)，所以盡量做批量的變更，不要進(jìn)行頻繁小數(shù)據(jù)的操作。

刪除操作：

alter table t_order_smt delete where sku_id ='sku_001';

修改操作：

alter table t_order_smt update total_amount=toDecimal32(2000.00,2) 
where id=102;

由于操作比較重，所以Mutation語句分兩步執(zhí)行，同步執(zhí)行的部分其實(shí)只是進(jìn)行新增數(shù)據(jù)、新增分區(qū)和并把舊分區(qū)打上邏輯上的失效標(biāo)記。直到觸發(fā)分區(qū)合并的時(shí)候，才會(huì)刪除舊數(shù)據(jù)釋放磁盤空間，一般不會(huì)開放這樣的功能給用戶，由管理員完成。

查詢操作
ClickHouse基本上與標(biāo)準(zhǔn)SQL差別不大：

支持子查詢；
支持CTE（Common Table Expression公用表表達(dá)式with子句）；
支持各種JOIN，但是JOIN操作無法使用緩存，所以即使是兩次相同的JOIN語句，ClickHouse也會(huì)視為兩條新SQL；
不支持自定義函數(shù)；
GROUP BY操作增加了with rollup、with cube、with total用來計(jì)算小計(jì)和總計(jì)。
測(cè)試下GROUP BY的三種操作，看下數(shù)據(jù)：

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │         1000 │ 2020-06-01 12:00:00 │
│ 101 │ sku_001 │         1000 │ 2020-06-01 12:00:00 │
│ 102 │ sku_002 │         2000 │ 2020-06-01 11:00:00 │
│ 102 │ sku_002 │         2000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_002 │        12000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_002 │         2000 │ 2020-06-01 11:00:00 │
│ 102 │ sku_002 │         2000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_002 │        12000 │ 2020-06-01 13:00:00 │
│ 102 │ sku_004 │         2500 │ 2020-06-01 12:00:00 │
│ 102 │ sku_004 │         2500 │ 2020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │          600 │ 2020-06-02 12:00:00 │
│ 102 │ sku_002 │          600 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘

with rollup：從右至左去掉維度進(jìn)行小計(jì)

select id,sku_id,sum(total_amount) from t_order_mt 
group by id,sku_id with rollup;

with cube : 從右至左去掉維度進(jìn)行小計(jì)，再從左至右去掉維度進(jìn)行小計(jì)

select id,sku_id,sum(total_amount) from t_order_mt 
group by id,sku_id with cube;

with totals: 只計(jì)算合計(jì)

select id,sku_id,sum(total_amount) from t_order_mt 
group by id,sku_id with totals;

alter操作，同MySQL的修改字段基本一致。

1）新增字段：alter table tableName add column newcolname String after col1;
2）修改字段類型：alter table tableName modify column newcolname String;
3）刪除字段：alter table tableName drop column newcolname;

導(dǎo)出數(shù)據(jù)

clickhouse-client --query "select * from 
t_order_mt where create_time='2022-08-28 12:00:00'" 
--format CSVWithNames> /opt/module/data/rs1.csv

到此這篇關(guān)于clickhouse介紹、安裝、數(shù)據(jù)類型、sql操作的文章就介紹到這了,更多相關(guān)clickhouse安裝內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論