ClickHouse學習筆記（一）：ClickHouse安裝、資料類型、表引擎、SQL操作

ClickHouse是俄羅斯的Yandex于2016年開源的列式存儲資料庫（DBMS），使用C++語言編寫，主要用于線上分析處理查詢（OLAP），能夠使用SQL查詢實時生成分析資料報告

一、ClickHouse的特點

1、列式存儲

以下面的表為例：

1）采用行式存儲時，資料在磁盤上的組織結構為：

好處是想查某個人所有的屬性時，可以通過一次磁盤查找加順序讀取就可以。但是當想查所有人的年齡時，需要不停的查找，或者全表掃描才行，周遊的很多資料都是不需要的

2）采用列式存儲時，資料在磁盤上的組織結構為：

這時想查所有人的年齡隻需把年齡那一列拿出來就可以了

3）列式存儲的好處：

• 對于列的聚合、計數、求和等統計操作原因優于行式存儲
• 由于某一列的資料類型都是相同的，針對于資料存儲更容易進行資料壓縮，每一列選擇更優的資料壓縮算法，大大提高了資料的壓縮比重
• 由于資料壓縮比更好，一方面節省了磁盤空間，另一方面對于cache也有了更大的發揮空間

2、DBMS的功能

幾乎覆寫了标準SQL的大部分文法，包括DDL和DML，以及配套的各種函數，使用者管理及權限管理，資料的備份與恢複

3、多樣化引擎

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

4、高吞吐寫入能力

ClickHouse采用類LSM Tree的結構，資料寫入後定期在背景Compaction。通過類LSM tree的結構，ClickHouse在資料導入時全部是順序append寫，寫入後資料段不可更改，在背景compaction時也是多個段merge sort後順序寫回磁盤。順序寫的特性，充分利用了磁盤的吞吐能力，即便在HDD上也有着優異的寫入性能

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

5、資料分區與線程級并行

ClickHouse将資料劃分為多個partition，每個partition再進一步劃分為多個index granularity（索引粒度），然後通過多個CPU核心分别處理其中的一部分來實作并行資料處理。在這種設計下，單條Query就能利用整機所有CPU。極緻的并行處理能力，極大的降低了查詢延時

是以，ClickHouse即使對于大量資料的查詢也能夠化整為零平行處理。但是有一個弊端就是對于單條查詢使用多cpu，就不利于同時并發多條查詢。是以對于高qps的查詢業務，ClickHouse并不是強項

二、ClickHouse的安裝

1、準備工作

1）确定防火牆處于關閉狀态

2）CentOS取消打開檔案數限制

在

/etc/security/limits.conf

檔案的末尾加入以下内容

[[email protected] ~]# sudo vim /etc/security/limits.conf
           

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

1. 第一列是限制的使用者和使用者組
2. soft軟限制，hard硬限制
3. nofile打開檔案數，nproc使用者程序數

在

/etc/security/limits.d/20-nproc.conf

檔案的末尾加入以下内容

[[email protected] ~]# sudo vim /etc/security/limits.d/20-nproc.conf
           

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

退出目前使用者，重新開機登入，

ulimit -a

檢視打開檔案數和使用者程序數是否更改

[[email protected] ~]# 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
           

3）安裝依賴

[[email protected] ~]# sudo yum install -y libtool
           

[[email protected] ~]# sudo yum install -y *unixODBC*
           

4）CentOS取消SELINUX

[[email protected] ~]# sudo vim /etc/selinux/config
           

SELINUX=disabled
           

修改完重新開機伺服器

2、單機安裝

官網：https://clickhouse.tech/

下載下傳位址：https://repo.yandex.ru/clickhouse/rpm/stable/x86_64/

1）下載下傳安裝

需要以下四個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
           

[[email protected] /]# mkdir clickhouse
[[email protected] /]# cd clickhouse/
           

通過wget下載下傳rpm包：

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

安裝這4個rpm包

[[email protected] clickhouse]# sudo rpm -ivh *.rpm
           

2）修改配置檔案

[[email protected] clickhouse]# cd /etc/clickhouse-server/
[[email protected] clickhouse-server]# sudo chmod 777 config.xml 
[[email protected] clickhouse-server]# sudo vim config.xml 
           

把

<listen_host>0.0.0.0</listen_host>

的注釋打開，這樣的話才能讓ClickHouse被除本機之外的伺服器通路

這個配置檔案中，ClickHouse一些預設路徑配置：

• 資料檔案路徑：

  <path>/var/lib/clickhouse/</path>

• 日志檔案路徑：

  <log>/var/log/clickhouse-server/clickhouse-server.log</log>

3）啟動Server

[[email protected] clickhouse-server]# sudo systemctl start clickhouse-server
           

或者

[[email protected] clickhouse-server]# sudo clickhouse start
           

我在部署過程中，這裡啟動失敗，錯誤日志如下：

2021.09.12 20:07:12.545113 [ 4719 ] {} <Error> Application: DB::Exception: Listen [::]:8123 failed: Poco::Exception. Code: 1000, e.code() = 0, e.displayText() = DNS error: EAI: Address family for hostname not supported (version 21.7.3.14 (official build))
           

原因一開始使用的listen_host配置為

<listen_host>::</listen_host>

，而

::

是IPv6的通配符，我部署clickhouse的機器不支援ipv6，是以把listen_host的配置改為了

<listen_host>0.0.0.0</listen_host>

4）關閉開啟自啟

[[email protected] clickhouse-server]# sudo systemctl disable clickhouse-server
           

5）使用Client連接配接server

[[email protected] clickhouse-server]# clickhouse-client -m
ClickHouse client version 21.7.3.14 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 21.7.3 revision 54449.

aliyun :) show databases;

SHOW DATABASES

Query id: bf8aa5d4-3aa9-48ed-b35e-9385b72317c2

┌─name────┐
│ default │
│ system  │
└─────────┘

2 rows in set. Elapsed: 0.002 sec.
           

三、資料類型

1、整型

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

整型範圍（ 2 n − 1 2^n-1 2n−1 ~ 2 n − 1 − 1 2^{n-1}-1 2n−1−1）：Int8、Int16、Int32、Int64

無符号整型範圍（0 ~ 2 n − 1 2^n-1 2n−1）：UInt8、UInt16、UInt32、UInt64

2、浮點型

Float32、Float64

浮點數計算精度缺失問題：

3、布爾型

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

4、Decimal型

Decimal32(s)相當于Decimal(9-s,s)

Decimal64(s)相當于Decimal(18-s,s)

Decimal128(s)相當于Decimal(38-s,s)

5、字元串

1）String

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

2）FixedString(N)

固定長度N的字元串，N必須是嚴格的正自然數。當服務端讀取長度小于N的字元串時候，通過在字元串末尾添加空位元組來達到N位元組長度。當服務端讀取長度大于N的字元串時候，将傳回錯誤消息

6、枚舉類型

包括Enum8和Enum16類型。Enum儲存’string’=integer的對應關系

Enum8用’string’=Int8來描述

Enum16用’string’=Int16來描述

建立一個帶有一個枚舉Enum8(‘hello’ = 1, ‘world’ = 2)類型的列

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

這個x列隻能存儲類型定義中列出的值：‘hello’或’world’

如果嘗試儲存任何其他值，ClickHouse抛出異常

如果需要看到對應行的數值，則必須将Enum值轉換為整數類型

7、時間類型

目前ClickHouse有三種時間類型：

• Date接受年-月-日的字元串，比如：2019-12-16
• Datetime接受年-月-日 時:分:秒的字元串，比如2019-12-16 20:50:10
• Datetime64 接受年-月-日 時:分:秒.亞秒的字元串，比如2019-12-16 20:50:10.66

日期類型用兩個位元組存儲，表示從1970-01-01到目前的日期值

8、數組

Array(T)：由T類型元素組成的數組

T可以是任意類型，包含數組類型。但不推薦使用多元數組，ClickHouse對多元數組的支援有限。例如，不能在MergeTree表中存儲多元數組

建立數組方式：

1）使用array函數

2）使用方括号

四、表引擎

1、表引擎的使用

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

• 資料的存儲方式和位置，寫到哪裡以及從哪裡讀取資料
• 支援哪些查詢以及如何支援
• 并發資料通路
• 索引的使用（如果存在）
• 是否可以執行多線程請求
• 資料複制參數

表引擎的使用方式就是必須顯式在建立表時定義該表使用的引擎，以及引擎使用的相關參數

引擎的名稱大小寫敏感

2、TinyLog

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

3、Memory

記憶體引擎：資料以未壓縮的原始形式直接儲存在記憶體當中，伺服器重新開機資料就會消失。讀寫操作不會互相阻塞，不支援索引。簡單查詢下有非常非常高的性能表現（超過10G/s）。一般用到它的地方不多，除了用來測試，就是在需要非常高的性能，同時資料量又不太大（上限大概1億行）的場景

4、MergeTree

ClickHouse中最強大的表引擎當屬MergeTree（合并樹）引擎及該系列（*MergeTree）中的其他引擎，支援索引和分區，地位可以相當于innodb之于Mysql

1）、建表語句

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);
           

2）、插入資料

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. 主鍵可重複
2. 根據日期分區，2020-06-01、2020-06-02共兩個分區
3. 分區内根據id和sku_id排序

3）、partition by分區（可選）

分區的目的主要是降低掃描的範圍，優化查詢速度

如果不填，隻會使用一個分區

分區後，面對涉及跨分區的查詢統計，ClickHouse會以分區為機關并行處理

[[email protected] ~]# cd /var/lib/clickhouse/
[[email protected] clickhouse]# ls
access  data  dictionaries_lib  flags  format_schemas  metadata  metadata_dropped  preprocessed_configs  status  store  tmp  user_files
           

• data：資料存儲的路徑
• metadata：表結構資訊

[[email protected] clickhouse]# cd metadata
[[email protected] metadata]# ls
default  default.sql  system  system.sql
[[email protected] metadata]# cd default
[[email protected] default]# ls
t_enum.sql  t_order_mt.sql
           

[[email protected] clickhouse]# cd data/
[[email protected] data]# ls
default  system
[[email protected] data]# cd default/
[[email protected] default]# ls
t_enum  t_order_mt
[[email protected] default]# cd t_order_mt/
[[email protected] t_order_mt]# ls
20200601_1_1_0  20200602_2_2_0  detached  format_version.txt
           

20200601_1_1_0、20200602_2_2_0共兩個分區目錄

分區目錄命名格式：PartitionId_MinBlockNum_MaxBlockNum_Level，分表代表分區值、最小分區塊編号、最大分區塊編号、合并層級

PartitionId：

資料分區規則由分區ID決定，分區ID由partition by分區鍵決定。根據分區鍵字段類型，ID生成規則可分為：

1. 未定義分區鍵：沒有定義partition by，預設生成一個目錄名為all的資料分區，所有資料均存放在all目錄下
2. 整型分區鍵：分區鍵為整型，直接用該整型值的字元串形式作為分區ID
3. 日期類分區鍵：分區鍵為日期類型，或者可以轉換為日期類型
4. 其他類型分區鍵：String、Float類型等，通過128位的Hash算法娶妻Hash值作為分區ID

MinBlockNum：最小分區塊編号，自增類型，從1開始向上遞增。每産生一個新的目錄分區就向上遞增一個數字

MaxBlockNum：最大分區塊編号，新建立的分區MinBlockNum等于MaxBlockNum的編号

Level：合并的層級，被合并的次數。合并次數越多，層級值越大

[[email protected] t_order_mt]# cd 20200601_1_1_0
[[email protected] 20200601_1_1_0]# ls
checksums.txt  columns.txt  count.txt  data.bin  data.mrk3  default_compression_codec.txt  minmax_create_time.idx  partition.dat  primary.idx
           

• data.bin：資料檔案
• data.mrk3：标記檔案，标記檔案在idx索引檔案和bin資料檔案之間起到了橋梁作用
• count.txt：有幾條資料
• default_compression_codec.txt：預設壓縮格式
• columns.txt：列的資訊
• primary.idx：主鍵索引檔案
• partition.dat與minmax_[Column].idx：如果使用了分區鍵，則會額外生成這2個檔案，均使用二進制存儲。partition.dat儲存目前分區下分區表達式最終生成的值；minmax索引用于記錄目前分區下分區字段對應原始資料的最小值和最大值。以t_order_mt的20200601分區為例，partition.dat中的值為20200601，minmax索引中儲存的值為2020-06-01 12:00:002020-06-01 13:00:00

資料寫入與分區合并：

任何一個批次的資料寫入都會産生一個臨時分區，不會納入任何一個已有的分區。寫入後的某個時刻（大概10-15分鐘後），ClickHouse會自動執行合并操作（等不及也可以手動通過optimize執行），把臨時分區的資料，合并到已有分區中

案例：

執行插入操作

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');
           

檢視資料并沒有納入任何分區

手動optimize之後

再次查詢

插入資料合并前：

[[email protected] t_order_mt]# ls
20200601_1_1_0  20200601_3_3_0  20200602_2_2_0  20200602_4_4_0  detached  format_version.txt
           

插入資料合并後：

[[email protected] t_order_mt]# ls
20200601_1_1_0  20200601_1_3_1  20200601_3_3_0  20200602_2_2_0  20200602_2_4_1  20200602_4_4_0  detached  format_version.txt
           

20200601_1_1_0和20200601_3_3_0分區合并為20200601_1_3_1，在真正合并的時候20200601_1_1_0和20200601_3_3_0分區會被清理

4）、primary key主鍵（可選）

ClickHouse中的主鍵，和其他資料庫不太一樣，**它隻提供了資料的一級索引，但是卻不是唯一限制。**這就意味着是可以存在相同primary key的資料

主鍵的設定主要依據是查詢語句中的where條件

根據條件通過對主鍵進行某種形式的二分查找，能夠定位到對應的index granularity，避免了全表掃描

index granularity：直接翻譯的話就是索引粒度，指在稀疏索引中兩個相鄰索引對應資料的間隔。ClickHouse中的MergeTree預設是8192。官方不建議修改這個值，除非該列存在大量重複值，比如在一個分區中幾萬行才有一個不同資料

稀疏索引：

稀疏索引的好處就是可以用很少的索引資料，定位更多的資料，代價就是隻能定位到索引粒度的第一行，然後再進行進行一點掃描

5）、order by（必須）

order by設定了分區内的資料按照哪些字段順序進行有序儲存

order by是MergeTree中唯一一個必填項，甚至比primary key還重要，因為當使用者不設定主鍵的情況，很多處理會依照order by的字段進行處理

要求：主鍵必須是order by字段的字首字段

比如order by字段是(id,sku_id)，那麼主鍵必須是id或者(id,sku_id)

6）、二級索引

目前在ClickHouse的官網上二級索引的功能在v20.1.2.4之前是被标注為實驗性的，在這個版本之後預設是開啟的

1）老版本使用二級索引前需要增加設定

是否允許使用實驗性的二級索引（v20.1.2.4開始，這個參數已被删除，預設開啟）

2）建立測試表

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);
           

GRANULARITY N是設定二級索引對于一級索引粒度的粒度

minmax索引的聚合資訊是在一個index_granularity區間内資料的最小和最大值。以下圖為例，假設index_granularity=8192且granularity=3，則資料會按照index_granularity劃分為n等份，MergeTree從第0段分區開始，依次擷取聚合資訊。當擷取到第3個分區時（granularity=3），則彙總并會生成第一行minmax索引（前3段minmax彙總後取值為[1, 9]）

3）插入資料

insert into t_order_mt2 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');
           

4）對比效果

[[email protected] ~]# clickhouse-client --send_logs_level=trace <<< 'select * from t_order_mt2 where total_amount > toDecimal32(900., 2)';
           

日志中可以看到二級索引能夠為非主鍵字段的查詢發揮作用

分區下檔案

skp_idx_a.idx

和

skp_idx_a.mrk3

為跳數索引檔案：

[[email protected] t_order_mt2]# ls
20200601_1_1_0  20200602_2_2_0  detached  format_version.txt
[[email protected] t_order_mt2]# cd 20200601_1_1_0/
[[email protected] 20200601_1_1_0]# ls
checksums.txt  count.txt  data.mrk3                      minmax_create_time.idx  primary.idx    skp_idx_a.mrk3
columns.txt    data.bin   default_compression_codec.txt  partition.dat           skp_idx_a.idx
           

7）、資料TTL

MergeTree提供了可以管理資料表或者列的生命周期的功能

1）列級TTL

create table t_order_mt3(
id UInt32,
sku_id String,
total_amount Decimal(16,2) TTL create_time+interval 10 SECOND,
create_time Datetime 
) engine =MergeTree
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id, sku_id);
           

TTL的列必須是日志類型且不能為主鍵

插入資料（請根據實際時間修改資料）

insert into t_order_mt3 values
(106,'sku_001',1000.00,'2021-09-12 20:56:00'),
(107,'sku_002',2000.00,'2021-09-12 20:56:00'),
(110,'sku_003',600.00,'2021-09-12 20:56:00');
           

手動合并，檢視效果：到期後，指定的字段資料歸0

2）表級TTL

下面的這條語句是資料會在create_time之後10秒丢失

涉及判斷的字段必須是Date或者Datetime類型，推薦使用分區的日期字段

能夠使用的時間周期：

• SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• MONTH
• QUARTER
• YEAR

5、ReplacingMergeTree

ReplacingMergeTree是MergeTree的一個變種，它存儲特性完全繼承MergeTree，隻是多了一個去重的功能

1）去重時機

資料的去重隻會在合并的過程中出現。合并會在未知的時間在背景進行，是以你無法預先作出計劃。有一些資料可能仍未被處理

2）去重範圍

如果表經過了分區，去重隻會在分區内部進行去重，不能執行跨分區的去重

是以ReplacingMergeTree能力有限，ReplacingMergeTree适用于在背景清除重複的資料以節省空間，但是它不保證沒有重複的資料出現

3）案例

create table t_order_rmt(
id UInt32,
sku_id String,
total_amount Decimal(16,2) ,
create_time Datetime 
) engine =ReplacingMergeTree(create_time)
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id, sku_id);
           

ReplacingMergeTree()

填入的參數為版本字段，重複資料保留版本字段值最大的。如果不填版本字段，預設按照插入順序保留最後一條

插入資料：

insert into t_order_rmt 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');
           

執行查詢操作：

4）結論

1. 實際上是使用order by字段作為唯一鍵進行去重
2. 去重不能跨分區
3. 隻有同一批插入（新版本）或合并分區時才會進行去重
4. 認定重複的資料保留，版本字段值最大的
5. 如果版本字段相同則按插入順序保留最後一筆

6、SummingMergeTree

對于不查詢明細，隻關心以次元進行彙總聚合結果的場景。如果隻使用普通的MergeTree的話，無論是存儲空間的開銷，還是查詢時臨時聚合的開銷都比較大

ClickHouse為了這種場景，提供了一種能夠預聚合的引擎SummingMergeTree

1）案例

create table t_order_smt(
id UInt32,
sku_id String,
total_amount Decimal(16,2) ,
create_time Datetime 
) engine =SummingMergeTree(total_amount)
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id );
           

插入資料

insert into t_order_smt 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');
           

執行查詢操作：

2）結論

1. 以

   SummingMergeTree()

   中指定的列作為彙總資料列
2. 可以填寫多列必須數字列，如果不填，以所有非次元列（除了order by的列之外）且為數字列的字段為彙總資料列
3. 以order by的列為準，作為次元列
4. 其他的列按插入順序保留第一行
5. 不在一個分區的資料不會被聚合
6. 隻有在同一批次插入（新版本）或分片合并時才會進行聚合

五、SQL操作

1、Insert

基本與标準SQL（MySQL）基本一緻

1）标準

2）從表到表的插入

2、Update和Delete

ClickHouse提供了Delete和Update的能力，這類操作被稱為Mutation查詢，它可以看做Alter的一種

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

重的原因主要是每次修改或者删除都會導緻放棄目标資料的原有分區，重建新分區

是以盡量做批量的變更，不要進行頻繁小資料的操作

1）删除操作

2）修改操作

由于操作比較重，是以Mutation語句分兩步執行，同步執行的部分其實隻是進行新增資料、新增分區和并把舊分區打上邏輯上的失效标記。直到觸發分區合并的時候，才會删除舊資料釋放磁盤空間，一般不會開放這樣的功能給使用者，由管理者完成

3、查詢操作

ClickHouse基本上與标準SQL差别不大

• 支援子查詢
• 支援CTE（Common Table Expression公用表表達式with子句）
• 支援各種JOIN，但是JOIN操作無法使用緩存，是以即使是兩次相同的JOIN語句，ClickHouse也會視為兩條新SQL
• 不支援自定義函數
• GROUP BY操作增加了with rollup、with cube、with total用來計算小計和總計

插入資料

alter table t_order_mt delete where 1=1;
insert into t_order_mt values
(101,'sku_001',1000.00,'2020-06-01 12:00:00'),
(101,'sku_002',2000.00,'2020-06-01 12:00:00'),
(103,'sku_004',2500.00,'2020-06-01 12:00:00'),
(104,'sku_002',2000.00,'2020-06-01 12:00:00'),
(105,'sku_003',600.00,'2020-06-02 12:00:00'),
(106,'sku_001',1000.00,'2020-06-04 12:00:00'),
(107,'sku_002',2000.00,'2020-06-04 12:00:00'),
(108,'sku_004',2500.00,'2020-06-04 12:00:00'),
(109,'sku_002',2000.00,'2020-06-04 12:00:00'),
(110,'sku_003',600.00,'2020-06-01 12:00:00');
           

1）with rollup：從右至左去掉次元進行小計

2）with cube : 從右至左去掉次元進行小計，再從左至右去掉次元進行小計

3）with totals: 隻計算合計

4、alter操作

同MySQL的修改字段基本一緻

1）新增字段

2）修改字段類型

3）删除字段