ClickHouse入門筆記

wang03發表於2021-08-03

筆記

ClickHouse筆記

尚矽谷clickhouse學習筆記

第 1 章 ClickHouse 入門

官方文件

ClickHouse 是開源的列式儲存資料庫，使用C++編寫，主要用於線上分析處理查詢(OLAP),使用SQL查詢實時生成分析析資料包告。
列式儲存的好處：

➢ 對於列的聚合，計數，求和等統計操作原因優於行式儲存。

➢ 由於某一列的資料型別都是相同的，針對於資料儲存更容易進行資料壓縮，每一列選擇更優的資料壓縮演算法，大大提高了資料的壓縮比重。

➢ 由於資料壓縮比更好，一方面節省了磁碟空間，另一方面對於 cache 也有了更大的發揮空間。
ClickHouse 和 MySQL 類似，把表級的儲存引擎外掛化，根據表的不同需求可以設定不同的儲存引擎。目前包括合併樹、日誌、介面和其他四大類 20 多種引擎。
ClickHouse 採用類 LSM Tree的結構，資料寫入後定期在後臺 Compaction。通過類 LSM tree 的結構，ClickHouse 在資料匯入時全部是順序 append 寫，寫入後資料段不可更改，在後臺 compaction 時也是多個段 merge sort 後順序寫回磁碟。順序寫的特性，充分利用了磁碟的吞吐能力，即便在 HDD 上也有著優異的寫入效能。
ClickHouse 將資料劃分為多個 partition，每個 partition 再進一步劃分為多個 index granularity(索引粒度)，然後通過多個 CPU核心分別處理其中的一部分來實現並行資料處理。在這種設計下，單條 Query 就能利用整機所有 CPU。極致的並行處理能力，極大的降低了查詢延時。以對於高 qps 的查詢業務， ClickHouse 並不是強項。
ClickHouse不適合關聯查詢，效能較差。

第 2 章 ClickHouse 的安裝

關閉防火牆

systemctl stop firewalld #關閉防火牆
systemctl disable firewalld#禁止開機啟動防火牆

CentOS 取消開啟檔案數限制
- /etc/security/limits.conf 檔案的末尾加入以下內容
```
* soft nofile 65536
* hard nofile 65536
* soft nproc 131072
* hard nproc 131072
```
- /etc/security/limits.d/20-nproc.conf 檔案的末尾加入以下內容
```
* soft nofile 65536
* hard nofile 65536
* soft nproc 131072
* hard nproc 131072
```

安裝依賴

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

CentOS 取消 SELINUX

修改/etc/selinux/config 中的 SELINUX=disabled

修改這個配置需要重啟，臨時生效命令setenforce 0 查詢狀態命令getenforce
下載安裝包

clickhouse官網倉庫下載

本次安裝為21.7.3.14-2版本

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

新建一資料夾，放置上述下載的4個rpm包，在資料夾中執行rpm -ivh *.rpm命令進行安裝。過程中需要設定密碼。

安裝完成後，執行rpm -qa|grep clickhouse進行安裝確認。

目錄位置:

bin/ > /usr/bin
conf/> /etc/clickhouse-server/
lib/ ==> /var/lib/clickhouse
log/ ==> /var/log/clickhouse
配置
- 修改 /etc/clickhouse-server/config.xml。把 :: 的註釋開啟，這樣的話才能讓 ClickHouse 被除本機以外的伺服器訪問
- 日誌配置也在/etc/clickhouse-server/config.xml這個檔案中
- 啟動命令：clickhouse start
  
  客戶端連線命令：clickhouse-client -m ，有設定密碼的話使用clickhouse-client -m --password 123456
  
  直接執行命令clickhouse-client --password 123456 --query "show databases;"

第 3 章資料型別

整型

固定長度的整型，包括有符號整型或無符號整型。

整型範圍（-2n-1~2n-1-1）：

Int8 - [-128 : 127]

Int16 - [-32768 : 32767]

Int32 - [-2147483648 : 2147483647]

Int64 - [-9223372036854775808 : 9223372036854775807]

無符號整型範圍（0~2n-1）：

UInt8 - [0 : 255]

UInt16 - [0 : 65535]

UInt32 - [0 : 4294967295]

UInt64 - [0 : 18446744073709551615]
浮點型

Float32 - float

Float64 – double
布林型

沒有單獨的型別來儲存布林值。可以使用 UInt8 型別，取值限制為 0 或 1。
Decimal 型

有符號的浮點數，可在加、減和乘法運算過程中保持精度。對於除法，最低有效數字會被丟棄（不捨入）。

有三種宣告：

➢ Decimal32(s)，相當於 Decimal(9-s,s)，有效位數為 1~9

➢ Decimal64(s)，相當於 Decimal(18-s,s)，有效位數為 1~18

➢ Decimal128(s)，相當於 Decimal(38-s,s)，有效位數為 1~38

s 標識小數位
字串

1）String 字串可以任意長度的。它可以包含任意的位元組集，包含空位元組。

2）FixedString(N) 固定長度 N 的字串，N 必須是嚴格的正自然數。當服務端讀取長度小於 N 的字元串時候，通過在字串末尾新增空位元組來達到 N 位元組長度。當服務端讀取長度大於 N 的字串時候，將返回錯誤訊息。
列舉型別

包括 Enum8 和 Enum16 型別。Enum 儲存 'string'= integer 的對應關係。

Enum8 用 'String'= Int8 對描述。

Enum16 用 'String'= Int16 對描述。

建表示例：

CREATE TABLE t_enum
(
x Enum8('hello' = 1, 'world' = 2)
)
ENGINE = TinyLog；

插入示例：

insert into t_enum values('hello');

insert into t_enum values(2);
時間型別

目前 ClickHouse 有三種時間型別

➢ Date 接受年-月-日的字串比如 ‘2019-12-16’

➢ Datetime 接受年-月-日時:分:秒的字串比如 ‘2019-12-16 20:50:10’

➢ Datetime64 接受年-月-日時:分:秒.亞秒的字串比如‘2019-12-16 20:50:10.66’

日期型別，用兩個位元組儲存，表示從 1970-01-01 (無符號) 到當前的日期值。
陣列

Array(T)：由 T 型別元素組成的陣列。 T 可以是任意型別，包含陣列型別。但不推薦使用多維陣列，ClickHouse 對多維陣列的支援有限。

第 4 章表引擎

表引擎的使用

表引擎決定了如何儲存表的資料。包括：

➢ 資料的儲存方式和位置，寫到哪裡以及從哪裡讀取資料。

➢ 支援哪些查詢以及如何支援。

➢ 併發資料訪問。

➢ 索引的使用（如果存在）。

➢ 是否可以執行多執行緒請求。

➢ 資料複製引數。

表引擎的使用方式就是必須顯式在建立表時定義該表使用的引擎，以及引擎使用的相關引數。特別注意：引擎的名稱大小寫敏感
TinyLog

以列檔案的形式儲存在磁碟上，不支援索引，沒有併發控制。一般儲存少量資料的小表，生產環境上作用有限。可以用於平時練習測試用。
Memory

記憶體引擎，資料以未壓縮的原始形式直接儲存在記憶體當中，伺服器重啟資料就會消失。讀寫操作不會相互阻塞，不支援索引。簡單查詢下有非常非常高的效能表現（超過 10G/s）。
MergeTree

MergeTree | ClickHouse文件

ClickHouse 中最強大的表引擎當屬 MergeTree（合併樹）引擎及該系列（*MergeTree）中的其他引擎，支援索引和分割槽，地位可以相當於 innodb 之於 Mysql。
- 建表語句
```
create table t_order_mt(
 id UInt32,
 sku_id String,
 total_amount Decimal(16,2),
 create_time Datetime
) engine =MergeTree
 partition by toYYYYMMDD(create_time)
 primary key (id)
 order by (id,sku_id);
```
- 插入資料
```
insert into t_order_mt values
(101,'sku_001',1000.00,'2020-06-01 12:00:00') ,
(102,'sku_002',2000.00,'2020-06-01 11:00:00'),
(102,'sku_004',2500.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 13:00:00'),
(102,'sku_002',12000.00,'2020-06-01 13:00:00'),
(102,'sku_002',600.00,'2020-06-02 12:00:00');
```
由於我們按照年月日進行了分割槽，所以查詢出來會有兩塊
1. partition by 分割槽(可選)
  - 作用
    
    主要是降低掃描的範圍，優化查詢速度
    
    如果不填只會使用一個分割槽。
  - 分割槽目錄
    
    MergeTree 是以列檔案+索引檔案+表定義檔案組成的，但是如果設定了分割槽那麼這些文件就會儲存到不同的分割槽目錄中。
  - 並行
    
    分割槽後，面對涉及跨分割槽的查詢統計，ClickHouse 會以分割槽為單位並行處理。
  - 資料寫入與分割槽合併
    
    任何一個批次的資料寫入都會產生一個臨時分割槽，不會納入任何一個已有的分割槽。寫入後的某個時刻（大概 10-15 分鐘後），ClickHouse 會自動執行合併操作（等不及也可以手動通過 optimize 執行），把臨時分割槽的資料，合併到已有分割槽中。
```
optimize table xxxx final;
```
    實際儲存位置：
  資料檔案位置：/var/lib/clickhouse/data/default/t_order_mt/20200601_1_1_0
  - primary key 主鍵(可選)
    
    只提供了資料的一級索引，但是卻不是唯一約束。這就意味著是可以存在相同 primary key 的資料的。
    
    主鍵的設定主要依據是查詢語句中的 where 條件。
    
    根據條件通過對主鍵進行某種形式的二分查詢，能夠定位到對應的 index granularity,避免了全表掃描。
    
    index granularity：直接翻譯的話就是索引粒度，指在稀疏索引中兩個相鄰索引對應數據的間隔。ClickHouse 中的 MergeTree 預設是 8192。
    
    稀疏索引：
    
    稀疏索引的好處就是可以用很少的索引資料，定位更多的資料，代價就是隻能定位到索引粒度的第一行，然後再進行進行一點掃描。
  - order by（必選）
    
    order by 是 MergeTree 中唯一一個必填項，甚至比 primary key 還重要，因為當使用者不設定主鍵的情況，很多處理會依照 order by 的欄位進行處理
    
    要求：主鍵必須是 order by 欄位的字首欄位。
    
    比如 order by 欄位是 (id,sku_id) 那麼主鍵必須是 id 或者(id,sku_id)，主鍵不能是sku_id
  - 二級索引
    
    二級索引的功能在 v20.1.2.4 之前是被標註為實驗性的，之後版本預設是開啟的。
```
create table t_order_mt2(
 id UInt32,
 sku_id String,
 total_amount Decimal(16,2),
 create_time Datetime,
INDEX a total_amount TYPE minmax GRANULARITY 5  #建立二級索引
) engine =MergeTree
 partition by toYYYYMMDD(create_time)
 primary key (id)
 order by (id, sku_id);
```
  - 資料 TTL
    
    TTL 即 Time To Live，MergeTree 提供了可以管理資料表或者列的生命週期的功能。
    
    TTL欄位不能使用主鍵欄位
    - 列級別 TTL
```
create table t_order_mt3(
 id UInt32,
 sku_id String,
 total_amount Decimal(16,2) TTL create_time+interval 10 SECOND,#設定這個欄位在createtime+10s後過期
 create_time Datetime 
) engine =MergeTree
partition by toYYYYMMDD(create_time)
 primary key (id)
 order by (id, sku_id);
```
    - 表級 TTL
      
      alter table t_order_mt3 MODIFY TTL create_time + INTERVAL 10 SECOND;
    涉及判斷的欄位必須是 Date 或者 Datetime 型別，推薦使用分割槽的日期欄位。
    
    能夠使用的時間週期：
    
    SECOND 、MINUTE、HOUR 、DAY 、 WEEK 、 MONTH 、 QUARTER 、 YEAR
2. ReplacingMergeTree
  
  ReplacingMergeTree 是 MergeTree 的一個變種，它儲存特性完全繼承 MergeTree，多了一個去重的功能。
  - 去重時機
    
    資料的去重只會在合併的過程中出現。
  - 去重範圍
    
    如果表經過了分割槽，去重只會在分割槽內部進行去重，不能執行跨分割槽的去重。
  ➢ 實際上是使用 order by 欄位作為唯一鍵
  
  ➢ 去重不能跨分割槽
  
  ➢ 只有同一批插入（新版本）或合併分割槽時才會進行去重
  
  ➢ 認定重複的資料保留，版本欄位值最大的
  
  ➢ 如果版本欄位相同則按插入順序保留最後一筆
3. SummingMergeTree
  
  對於不查詢明細，只關心以維度進行彙總聚合結果的場景,提供了一種能夠“預聚合”的引擎 SummingMergeTree
  
  ➢ 以 SummingMergeTree（）中指定的列作為彙總資料列
  
  ➢ 可以填寫多列必須數字列，如果不填，以所有非維度列且為數字列的欄位為彙總數據列
  
  ➢ 以 order by 的列為準，作為維度列
  
  ➢ 其他的列按插入順序保留第一行
  
  ➢ 不在一個分割槽的資料不會被聚合
  
  ➢ 只有在同一批次插入(新版本)或分片合併時才會進行聚合

第 5 章 SQL 操作

Insert

基本與標準 SQL（MySQL）基本一致

Update 和 Delete

這類操作被稱為 Mutation 查詢，它可以看做 Alter 的一種。

雖然可以實現修改和刪除，但是和一般的 OLTP 資料庫不一樣，Mutation 語句是一種很 “重”的操作，而且不支援事務。

“重”的原因主要是每次修改或者刪除都會導致放棄目標資料的原有分割槽，重建新分割槽。所以儘量做批量的變更，不要進行頻繁小資料的操作。

#刪除操作
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 語句分兩步執行，同步執行的部分其實只是進行新增資料新增分割槽和並把舊分割槽打上邏輯上的失效標記。直到觸發分割槽合併的時候，才會刪除舊資料釋放磁碟空間，一般不會開放這樣的功能給使用者，由管理員完成。

查詢操作

➢ 支援子查詢

➢ 支援 CTE(Common Table Expression 公用表表示式 with 子句)

➢ 支援各種 JOIN，但是 JOIN 操作無法使用快取，所以即使是兩次相同的 JOIN 語句， ClickHouse 也會視為兩條新 SQL

➢ 視窗函式URL函式 | ClickHouse文件

➢ 不支援自定義函式

➢ GROUP BY 操作增加了 with rollup\with cube\with total 用來計算小計和總計。

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;
匯出資料

Input and Output Formats | ClickHouse Documentation

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

第 6 章副本

三臺伺服器 clickhouse1、clickhouse2、clickhouse3

安裝zookeeper叢集

副本寫入流程.

配置步驟

（1）啟動 zookeeper 叢集

（2）在 clickhouse1的/etc/clickhouse-server/config.d目錄下建立一個名為 metric.xml(檔名自己指定) 的配置檔案,內容如下：

也可以不建立外部檔案，直接在 config.xml 中指定

<?xml version="1.0"?>
<yandex>
<zookeeper-servers>
 <node index="1">
 <host>clickhouse1</host>
 <port>2181</port>
 </node>
 <node index="2">
 <host>clickhouse2</host>
 <port>2181</port>
 </node>
 <node index="3">
 <host>clickhouse3</host>
 <port>2181</port>
 </node>
</zookeeper-servers>
</yandex>

（3）etc/clickhouse-server/config.xml 中增加

<!--查詢zookeeper配置檔案 進行增加配置-->
<zookeeper incl="zookeeper-servers" optional="true" />
<include_from>/etc/clickhouse-server/config.d/metric.xml</include_from>

（4）同步上述兩個檔案到 clickhouse2和 clickhouse3上

（5）重啟clickhouse叢集clickhouse restart

建立表

#clickhouse1上執行
create table t_order_rep2  (
 id UInt32,
 sku_id String,
 total_amount Decimal(16,2),
 create_time Datetime
) engine =ReplicatedMergeTree('/clickhouse/table/01/t_order_rep','rep_102')
 partition by toYYYYMMDD(create_time)
 primary key (id)
 order by (id,sku_id);
#當前報錯，待解決
#Code: 225. DB::Exception: Received from localhost:9000. DB::Exception: Can't create replicated table without ZooKeeper.

#clickhouse2上執行
create table t_order_rep2 (
 id UInt32,
 sku_id String,
 total_amount Decimal(16,2),
 create_time Datetime
) engine =ReplicatedMergeTree('/clickhouse/table/01/t_order_rep','rep_103')
 partition by toYYYYMMDD(create_time)
 primary key (id)
 order by (id,sku_id);
#當前報錯，待解決
#Code: 225. DB::Exception: Received from localhost:9000. DB::Exception: Can't create replicated table without ZooKeeper.

引數解釋
ReplicatedMergeTree 中，
第一個引數是分片的 zk_path 一般按照：/clickhouse/table/{shard}/{table_name} 的格式寫，如果只有一個分片就寫 01 即可。
第二個引數是副本名稱，相同的分片副本名稱不能相同。

第 7 章分片叢集

通過分片把一份完整的資料進行切分，不同的分片分佈到不同的節點上，再通過 Distributed 表引擎把資料拼接起來一同使用。

Distributed 表引擎本身不儲存資料，有點類似於 MyCat 之於 MySql，成為一種中介軟體，通過分散式邏輯表來寫入、分發、路由來操作多臺節點不同分片的分散式資料。

ClickHouse 的叢集是表級別的，實際企業中，大部分做了高可用，但是沒有用分片，避免降低查詢效能以及操作叢集的複雜性。

叢集寫入流程（3 分片 2 副本共 6 個節點）

叢集讀取流程（3 分片 2 副本共 6 個節點）
配置步驟

叢集及副本規劃（2 個分片，只有第一個分片有副本）
配置步驟

 在clickhouse1 伺服器/etc/clickhouse-server/config.d 目錄下建立 metrika-shard.xml 檔案

 ```xml
 <?xml version="1.0"?>
 <yandex>
 <remote_servers>
 <gmall_cluster> <!-- 叢集名稱--> 
 <shard> <!--叢集的第一個分片-->
 <internal_replication>true</internal_replication>
  <replica> <!--該分片的第一個副本-->
  <host>clickhouse1</host>
  <port>9000</port>
  </replica>
  <replica> <!--該分片的第二個副本-->
  <host>clickhouse2</host>
  <port>9000</port>
  </replica>
 </shard>
 <shard> <!--叢集的第二個分片-->
  <internal_replication>true</internal_replication>
  <replica> <!--該分片的第一個副本-->
  <host>clickhouse3</host>
  <port>9000</port>
  </replica>
 </shard>
 </gmall_cluster>
 </remote_servers>
 <zookeeper-servers>
 <node index="1">
 <host>clickhouse1</host>
 <port>2181</port>
 </node>
 <node index="2">
 <host>clickhouse2</host>
  <port>2181</port>
 </node>
 <node index="3">
  <host>clickhouse3</host>
  <port>2181</port>
 </node>
 </zookeeper-servers>
 <macros>
 <shard>01</shard> <!--不同機器放的分片數不一樣-->
 <replica>rep_1_1</replica> <!--不同機器放的副本數不一樣-->
 </macros>
 </yandex>
 ```

修改/etc/clickhouse-server/config.xm
同步 config.xml、metrika-shard.xml到clickhouse2、clickhouse3，重啟clickhouse restart

在clickhouse1上執行建表語句

➢ 會自動同步到叢集中其他伺服器

➢ 叢集名字要和配置檔案中的一致

➢ 分片和副本名稱從配置檔案的巨集定義中獲取

create table st_order_mt on cluster gmall_cluster (
 id UInt32,
 sku_id String,
 total_amount Decimal(16,2),
 create_time Datetime
) engine 
=ReplicatedMergeTree('/clickhouse/tables/{shard}/st_order_mt','{replica}')
 partition by toYYYYMMDD(create_time)
 primary key (id)
 order by (id,sku_id);
#當前報錯，待解決
#Code: 139. DB::Exception: Received from localhost:9000. DB::Exception: There is no Zookeeper configuration in server config.

在clickhouse1 上建立 Distribute 分散式表

create table st_order_mt_all2 on cluster gmall_cluster
(
 id UInt32,
 sku_id String,
 total_amount Decimal(16,2),
 create_time Datetime
)engine = Distributed(gmall_cluster,default, st_order_mt,hiveHash(sku_id));
#當前報錯，待解決
#Code: 139. DB::Exception: Received from localhost:9000. DB::Exception: There is no Zookeeper configuration in server config.

引數含義： Distributed（叢集名稱，庫名，本地表名，分片鍵）

分片鍵必須是整型數字，所以用 hiveHash 函式轉換，也可以 rand()

BFS入門筆記
2024-12-08
筆記
DFS入門筆記
2024-11-23
筆記
NSIS入門筆記
2024-08-29
筆記
Go入門筆記
2024-09-03
Go筆記
ByteBuddy入門筆記
2023-04-27
筆記
Python 入門筆記
2019-07-16
Python筆記
vue入門筆記
2018-10-17
Vue筆記
Python入門筆記
2019-03-10
Python筆記
linux入門筆記
2019-03-14
Linux筆記
selenium 入門筆記
2018-10-09
筆記
Redis入門筆記
2018-07-05
Redis筆記
Android入門筆記12
2020-10-21
Android筆記
python入門筆記1
2020-04-05
Python筆記
XStream入門使用筆記
2020-12-08
筆記
Hibernate快速入門筆記
2018-04-09
筆記
初識ClickHouse——安裝與入門
2021-01-15
Unity學習筆記--入門
2023-10-29
Unity筆記
【PostgreSQL】入門學習筆記
2022-04-19
SQL筆記
TS入門學習筆記
2020-01-19
筆記
Spring入門筆記簡要
2022-03-10
Spring筆記
Golang 基礎入門筆記
2020-04-29
Golang筆記
es6 入門筆記
2021-09-09
筆記
MyBatis-Plus筆記(入門)
2021-04-17
MyBatis筆記
git入門學習筆記
2018-11-15
Git筆記
3.Hibernate入門筆記
2018-12-29
筆記
10.Spring入門筆記
2018-12-29
Spring筆記
webpack入門筆記——其他配置
2018-05-11
Web筆記
EntityFramework Core筆記：入門（1）
2018-05-27
Framework筆記
Docker入門學習筆記
2018-07-25
Docker筆記
Unix 入門經典筆記
2018-03-09
筆記
spring框架快速入門筆記
2018-04-14
Spring框架筆記
快應用入門筆記
2018-03-22
筆記
小洋的Python入門筆記😀
2024-11-20
Python筆記
Hadoop 入門筆記—核心元件 MapRuduce
2021-11-19
Hadoop筆記元件
Hadoop 入門筆記—核心元件 HDFS
2021-11-19
Hadoop筆記元件
Hadoop 入門筆記—核心元件 YARN
2021-11-22
Hadoop筆記元件Yarn
React入門指南(學習筆記)
2019-09-05
React筆記
vue 基礎入門筆記 09
2019-08-16
Vue筆記

ClickHouse入門筆記

ClickHouse筆記

第 1 章 ClickHouse 入門

列式儲存的好處：

第 2 章 ClickHouse 的安裝

第 3 章 資料型別

整型

浮點型

布林型

Decimal 型

字串

列舉型別

時間型別

陣列

第 4 章 表引擎

表引擎的使用

TinyLog

Memory

MergeTree

partition by 分割槽(可選)

primary key 主鍵(可選)

二級索引

資料 TTL

ReplacingMergeTree

SummingMergeTree

第 5 章 SQL 操作

第 6 章 副本

第 7 章 分片叢集

相關文章

第 3 章資料型別

第 4 章表引擎

第 6 章副本

第 7 章分片叢集