欄位型別

表的基本概念

行和列

一張表包括行（Row）和列（Column）；
Row 即用戶的一行資料，Column 用于描述一行資料中不同的欄位，
doris中的列分為兩類：key列和value列
key列在doris中有兩種作用：
聚合表模型中，key是聚合和排序的依據
其他表模型中，key是排序依據

資料表模型

Doris 的資料模型主要分為3類:
? Aggregate 聚合模型
? Unique 唯一模型
? Duplicate 明細模型

Aggregate 模型

是相同key的資料進行自動聚合的表模型，表中的列按照是否設定了 AggregationType，分為 Key（維度列）和 Value（指標列），沒有設定AggregationType 的稱為 Key，設定了 AggregationType 的稱為 Value，當我們匯入資料時，對于 Key 列相同的行會聚合成一行，而 Value 列會按照設定的AggregationType 進行聚合，AggregationType 目前有以下五種聚合方式：

1. SUM：求和，多行的 Value 進行累加，
2. REPLACE：替代，下一批資料中的 Value 會替換之前匯入過的行中的 Value，
3. REPLACE_IF_NOT_NULL ：當遇到 null 值則不更新，
4. MAX：保留最大值，
5. MIN：保留最小值，

有如下場景：需要創建一個表，來記錄公司每個用戶的每一次消費行為資訊，有如下欄位:

而且，公司對這份資料，特別關心一個報表
每一個用戶最后一次訪問我們頁面的時間，用戶消費的總金額，用戶停留在我們頁面上的最大最小時長

每次要看這個報表，都需要在“明細表”上運行一個統計sql

Select
    user_id,data,city,age,gender,
    max(visit_data) as last_visit_data,
    sum(cost) as cost,
    max(dwell_time) as max_dwell_time,
    min(dwell_time) as min_dwell_time
From  t
Group by  user_id,data,city,age,gender  -- 對應的是聚合模型型key

聚合模型

-- 這是一個用戶消費和行為記錄的資料表
CREATE TABLE IF NOT EXISTS test.ex_user
(
 `user_id` LARGEINT NOT NULL COMMENT "用戶 id",
 `date` DATE NOT NULL COMMENT "資料灌入日期時間",
 `city` VARCHAR(20) COMMENT "用戶所在城市",
 `age` SMALLINT COMMENT "用戶年齡",
 `sex` TINYINT COMMENT "用戶性別",
 
 `last_visit_date` DATETIME REPLACE  DEFAULT "1970-01-01 00:00:00" COMMENT "用戶最后一次訪問時間",
 `cost` BIGINT SUM DEFAULT "0" COMMENT "用戶總消費",
 `max_dwell_time` INT MAX DEFAULT "0" COMMENT "用戶最大停留時間",
 `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "用戶最小停留時間" 
 )
ENGINE=olap
AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)
-- 分桶 創建表時必須指定分桶 分桶的概念下面說
DISTRIBUTED BY HASH(`user_id`) BUCKETS 1;

-- 插入資料
insert into test.ex_user values\
(10000,'2017-10-01','北京',20,0,'2017-10-01 06:00:00',20,10,10),\
(10000,'2017-10-01','北京',20,0,'2017-10-01 07:00:00',15,2,2),\
(10001,'2017-10-01','北京',30,1,'2017-10-01 17:05:45',2,22,22),\
(10002,'2017-10-02','上海',20,1,'2017-10-02 12:59:12',200,5,5),\
(10003,'2017-10-02','廣州',32,0,'2017-10-02 11:20:00',30,11,11),\
(10004,'2017-10-01','深圳',35,0,'2017-10-01 10:00:15',100,3,3),\
(10004,'2017-10-03','深圳',35,0,'2017-10-03 10:20:22',11,6,6);

查看資料的時候發現，資料只剩下6條了，就是因為再key相同的時候，將后面的結果聚合了

UNIQUE 模型

是相同key的資料進行自動去重的表模型，在某些多維分析場景下，用戶更關注的是如何保證 Key 的唯一性，即如何獲得 Primary Key 唯一性約束，因此，引入了 Uniq 的資料模型，該模型本質上是聚合模型的一個特例，也是一種簡化的表結構表示方式，

-- 創建表
drop table if exists test.user;
CREATE TABLE IF NOT EXISTS test.user
(
-- key列
 `user_id` LARGEINT NOT NULL COMMENT "用戶 id",
 `username` VARCHAR(50) NOT NULL COMMENT "用戶昵稱",
 -- value列
 `city` VARCHAR(20) COMMENT "用戶所在城市",
 `age` SMALLINT COMMENT "用戶年齡",
 `sex` TINYINT COMMENT "用戶性別",
 `phone` LARGEINT COMMENT "用戶電話",
 `address` VARCHAR(500) COMMENT "用戶地址",
 `register_time` DATETIME COMMENT "用戶注冊時間"
  )
UNIQUE KEY(`user_id`, `username`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 1;

-- 插入資料
insert into test.user values\
(10000,'zss','北京',18,0,12345678910,'北京朝陽區 ','2017-10-01 07:00:00'),\
(10000,'zss','北京',19,0,12345678910,'北京順義區 ','2018-10-01 07:00:00'),\
(10000,'lss','北京',20,0,12345678910,'北京海淀區','2017-11-15 06:10:20');


insert into test.user1 values\
(10000,'zss','北京',18,0,12345678910,'北京朝陽區 ','2017-10-01 07:00:00'),\
(10000,'zss','北京',19,0,12345678910,'北京順義區 ','2018-10-01 07:00:00'),\
(10000,'lss','北京',20,0,12345678910,'北京海淀區','2017-11-15 06:10:20');

查詢結果后發現，相同的資料就會被替換掉

Duplicate 模型

就是存明細資料的表模型，既不做聚合也不做去重，在某些多維分析場景下，資料既沒有主鍵，也沒有聚合需求，Duplicate 資料模型可以滿足這類需求，資料完全按斬訓入檔案中的資料進行存盤，不會有任何聚合，即使兩行資料完全相同，也都會保留， 而在建表陳述句中指定的 DUPLICATE KEY，只是用來指明底層資料按照那些列進行排序，

-- 創建表
CREATE TABLE IF NOT EXISTS test.log_detail
(
 `timestamp` DATETIME NOT NULL COMMENT "日志時間",
 `type` INT NOT NULL COMMENT "日志型別",
 `error_code` INT COMMENT "錯誤碼",
 `error_msg` VARCHAR(1024) COMMENT "錯誤詳細資訊",
 `op_id` BIGINT COMMENT "負責人 id",
 `op_time` DATETIME COMMENT "處理時間" 
 )
DUPLICATE KEY(`timestamp`, `type`) -- key用作排序使用
DISTRIBUTED BY HASH(`timestamp`) BUCKETS 1;

-- 插入資料
insert into test.log_detail values\
('2017-10-01 08:00:05',1,404,'not found page', 101, '2017-10-01 08:00:05'),\
('2017-10-01 08:00:05',1,404,'not found page', 101, '2017-10-01 08:00:05'),\
('2017-10-01 08:00:05',2,404,'not found page', 101, '2017-10-01 08:00:06'),\
('2017-10-01 08:00:06',2,404,'not found page', 101, '2017-10-01 08:00:07');

資料模型的選擇

資料模型在建表時就已經確定，且無法修改；所以，選擇一個合適的資料模型非常重要，

• Aggregate 模型可以通過預聚合，極大地降低聚合查詢時所需掃描的資料量和查詢的計算量，非常適合有固定模式的報表類查詢場景，

• Uniq 模型針對需要唯一主鍵約束的場景，可以保證主鍵唯一性約束，但是無法利用 ROLLUP 等預聚合帶來的查詢優勢（因為本質是 REPLACE，沒有 SUM 這種聚合方式），

• Duplicate 適合任意維度的查詢，雖然同樣無法利用預聚合的特性，但是不受聚合模型的約束，可以發揮列存模型的優勢（只讀取相關列，而不需要讀取所有 Key 列）

磁區與分桶

• partition（磁區）：是在邏輯上將一張表按行(橫向)劃分
• tablet（又叫bucket，分桶）：在物理上對一個磁區再按行(橫向)劃分

磁區(Partition)

? Partition 列可以指定一列或多列，在聚合模型中，磁區列必須為 KEY 列，
? 不論磁區列是什么型別，在寫磁區值時，都需要加雙引號，
? 磁區數量理論上沒有上限，
? 當不使用 Partition 建表時，系統會自動生成一個和表名同名的，全值范圍的 Partition，該 Partition 對用戶不可見，并且不可刪改，
? 創建磁區時不可添加范圍重疊的磁區，

Range 磁區

單列磁區

drop table if exists test.expamle_range_tbl;
CREATE TABLE IF NOT EXISTS test.expamle_range_tbl
(
    `user_id` LARGEINT NOT NULL COMMENT "用戶id",
    `date` DATE NOT NULL COMMENT "資料灌入日期時間",
    `timestamp` DATETIME NOT NULL COMMENT "資料灌入的時間戳",
    `city` VARCHAR(20) COMMENT "用戶所在城市",
    `age` SMALLINT COMMENT "用戶年齡",
    `sex` TINYINT COMMENT "用戶性別"
)
ENGINE=OLAP
DUPLICATE KEY(`user_id`, `date`) -- 表模型
-- 磁區的語法
PARTITION BY RANGE(`date`) -- 指定磁區型別和磁區列
(
    -- 指定磁區名稱，磁區的上界   前閉后開
    PARTITION `p201701` VALUES LESS THAN ("2017-02-01"), 
    PARTITION `p201702` VALUES LESS THAN ("2017-03-01"),
    PARTITION `p201703` VALUES LESS THAN ("2017-04-01")
)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 1;

? 磁區列通常為時間列，以方便的管理新舊資料，
? Partition 支持通過 VALUES LESS THAN (...) 僅指定上界，系統會將前一個磁區的上界作為該磁區的下界，生成一個左閉右開的區間，同時，也支持通過 VALUES[...) 指定上下界，生成一個左閉右開的區間，
? 通過 VALUES ...) 同時指定上下界比較容易理解，這里舉例說明，當使用 VALUES LESS THAN (...) 陳述句進行磁區的增刪操作時，磁區范圍的變化情況：
如上 expamle_range_tbl 得建表陳述句中可以看到，當建表完成后，會自動生成如下3個磁區：

-- 查看表中磁區得情況
SHOW PARTITIONS FROM test.expamle_range_tbl \G;

-- 增加一個磁區
ALTER TABLE test.expamle_range_tbl ADD PARTITION p201705 VALUES LESS THAN ("2017-06-01");

--洗掉磁區
ALTER TABLE test.expamle_range_tbl DROP PARTITION p201703;

-- 此時的磁區狀態如下:
-- p201701: [MIN_VALUE,  2017-02-01)
-- p201702: [2017-02-01, 2017-03-01)
-- p201705: [2017-04-01, 2017-06-01)
-- 出現了一個空洞：[2017-03-01, 2017-04-01)
-- 如果匯入的資料范圍在這個空洞范圍內，是無法匯入的

多列磁區

PARTITION BY RANGE(`date`, `id`)     前閉后開
(
    PARTITION `p201701_1000` VALUES LESS THAN ("2017-02-01", "1000"),
    PARTITION `p201702_2000` VALUES LESS THAN ("2017-03-01", "2000"),
    PARTITION `p201703_all`  VALUES LESS THAN ("2017-04-01")-- 默認采用id型別的最小值
)
-- 當用戶插入資料時，磁區列值會按照順序依次比較，最終得到對應的磁區

List磁區

? 磁區列支持 BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, DATE, DATETIME, CHAR, VARCHAR 資料型別，磁區值為列舉值，只有當資料為目標磁區列舉值其中之一時，才可以命中磁區，

? Partition 支持通過 VALUES IN (...) 來指定每個磁區包含的列舉值，

-- 單列
CREATE TABLE IF NOT EXISTS test.expamle_list_tbl
(
    `user_id` LARGEINT NOT NULL COMMENT "用戶id",
    `date` DATE NOT NULL COMMENT "資料灌入日期時間",
    `timestamp` DATETIME NOT NULL COMMENT "資料灌入的時間戳",
    `city` VARCHAR(20) NOT NULL COMMENT "用戶所在城市",
    `age` SMALLINT NOT NULL COMMENT "用戶年齡",
    `sex` TINYINT NOT NULL COMMENT "用戶性別",
    `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "用戶最后一次訪問時間",
    `cost` BIGINT SUM DEFAULT "0" COMMENT "用戶總消費",
    `max_dwell_time` INT MAX DEFAULT "0" COMMENT "用戶最大停留時間",
    `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "用戶最小停留時間"
)
ENGINE=olap
AGGREGATE KEY(`user_id`, `date`, `timestamp`, `city`, `age`, `sex`)
PARTITION BY LIST(`city`)
(
    PARTITION `p_cn` VALUES IN ("Beijing", "Shanghai", "Hong Kong"),
    PARTITION `p_usa` VALUES IN ("New York", "San Francisco"),
    PARTITION `p_jp` VALUES IN ("Tokyo")
)
-- 指定分桶的語法
DISTRIBUTED BY HASH(`user_id`) BUCKETS 1
PROPERTIES
(
    "replication_num" = "3"
);



-- 多列
PARTITION BY LIST(`id`, `city`)
(
    PARTITION `p1_city` VALUES IN (("1", "Beijing",), ("2", "Shanghai")),
    PARTITION `p2_city` VALUES IN (("2", "Beijing"), ("1", "Shanghai")),
    PARTITION `p3_city` VALUES IN (("3", "Beijing"), ("4", "Shanghai"))

)

分桶(Bucket)

• 如果使用了 Partition，則 DISTRIBUTED ... 陳述句描述的是資料在各個磁區內的劃分規則，如果不使用 Partition，則描述的是對整個表的資料的劃分規則，
• 分桶列可以是多列，但必須為 Key 列，分桶列可以和 Partition 列相同或不同，
• 分桶列的選擇，是在 查詢吞吐 和 查詢并發 之間的一種權衡：
  • 如果選擇多個分桶列，則資料分布更均勻，如果一個查詢條件不包含所有分桶列的等值條件，那么該查詢會觸發所有分桶同時掃描，這樣查詢的吞吐會增加，單個查詢的延遲隨之降低，這個方式適合大吞吐低并發的查詢場景，
  • 如果僅選擇一個或少數分桶列，則對應的點查詢可以僅觸發一個分桶掃描，此時，當多個點查詢并發時，這些查詢有較大的概率分別觸發不同的分桶掃描，各個查詢之間的IO影響較小（尤其當不同桶分布在不同磁盤上時），所以這種方式適合高并發的點查詢場景，
• 分桶的數量理論上沒有上限

關于 Partition 和 Bucket的數量和資料量的建議，

1. 一個表的 Tablet 總數量等于 (Partition num * Bucket num)，
2. 一個表的 Tablet 數量，在不考慮擴容的情況下，推薦略多于整個集群的磁盤數量，
3. 單個 Tablet 的資料量理論上沒有上下界，但建議在 1G - 10G 的范圍內，如果單個 Tablet 資料量過小，則資料的聚合效果不佳，且元資料管理壓力大，如果資料量過大，則不利于副本的遷移、補齊，且會增加 Schema Change 或者 Rollup 操作失敗重試的代價（這些操作失敗重試的粒度是 Tablet），分桶應該控制桶內資料量 ，不易過大或者過小
4. 當 Tablet 的資料量原則和數量原則沖突時，建議優先考慮資料量原則，
5. 在建表時，每個磁區的 Bucket 數量統一指定，但是在動態增加磁區時（ADD PARTITION），可以單獨指定新磁區的 Bucket 數量，可以利用這個功能方便的應對資料縮小或膨脹，
6. 一個 Partition 的 Bucket 數量一旦指定，不可更改，所以在確定 Bucket 數量時，需要預先考慮集群擴容的情況，比如當前只有 3 臺 host，每臺 host 有 1 塊盤，如果 Bucket 的數量只設定為 3 或更小，那么后期即使再增加機器，也不能提高并發度，

注：表的資料量可以通過 SHOW DATA命令查看，結果除以副本數，即表的資料量，

復合磁區與單磁區的選擇

復合磁區
? 第一級稱為 Partition，即磁區，用戶可以指定某一維度列作為磁區列（當前只支持整型和時間型別的列），并指定每個磁區的取值范圍，
? 第二級稱為 Distribution，即分桶，用戶可以指定一個或多個維度列以及桶數對資料進行 HASH 分布，

以下場景推薦使用復合磁區
? 有時間維度或類似帶有有序值的維度，可以以這類維度列作為磁區列，磁區粒度可以根據匯入頻次、磁區資料量等進行評估，地域、時間
? 歷史資料洗掉需求：如有洗掉歷史資料的需求（比如僅保留最近N 天的資料），使用復合磁區，可以通過洗掉歷史磁區來達到目的，也可以通過在指定磁區內發送 DELETE 陳述句進行資料洗掉，
? 改善資料傾斜問題：每個磁區可以單獨指定分桶數量，如按天磁區，當每天的資料量差異很大時，可以通過指定磁區的分桶數，合理劃分不同磁區的資料,分桶列建議選擇區分度大的列，
用戶也可以不使用復合磁區，即使用單磁區，則資料只做 HASH 分布，

PROPERTIES

在建表陳述句的最后，可以用 PROPERTIES 關鍵字來設定一些表的屬性引數（引數有很多）

PROPERTIES(
  "引數名" = "引數值"
)

分片副本數(replication_num)

每個 Tablet 的副本數量，默認為 3，建議保持默認即可，在建表陳述句中，所有 Partition中的 Tablet 副本數量統一指定，而在增加新磁區時，可以單獨指定新磁區中 Tablet 的副本數量，

副本數量可以在運行時修改，強烈建議保持奇數，

最大副本數量取決于集群中獨立 IP 的數量（注意不是 BE 數量），Doris 中副本分布的原則是，不允許同一個 Tablet 的副本分布在同一臺物理機上，而識別物理機即通過 IP，所以，即使在同一臺物理機上部署了 3 個或更多 BE 實體，如果這些 BE 的 IP 相同，則依然只能設定副本數為 1，對于一些小，并且更新不頻繁的維度表，可以考慮設定更多的副本數，這樣在 Join 查詢時，可以有更大的概率進行本地資料 Join，

存盤介質 和 熱資料冷卻時間

? storage_medium
? storage_cooldown_time
建表時，可以統一指定所有 Partition 初始存盤的介質及熱資料的冷卻時間，如：

"storage_medium" = "SSD"

-- 要在當前時間之后，并且是一個datetime型別 
"storage_cooldown_time" = "2023-04-20 00:00:00"

-- 默認初始存盤介質可通過 fe 的組態檔 fe.conf 中指定 default_storage_medium=xxx,如果沒有指定，則默認為 HDD，如果指定為 SSD，則資料初始存放在 SSD 上，沒設storage_cooldown_time，則默認 30 天后，資料會從 SSD 自動遷移到 HDD上，如果指定了 storage_cooldown_time，則在到達 storage_cooldown_time 時間后，資料才會遷移，

注意，當指定 storage_medium 時，如果 FE 引數 enable_strict_storage_medium_check 為False 該引數只是一個“盡力而為”的設定，即使集群內沒有設定 SSD 存盤介質，也不會報錯，而是自動存盤在可用的資料目錄中， 同樣，如果 SSD 介質不可訪問、空間不足，都可能導致資料初始直接存盤在其他可用介質上，而資料到期遷移到 HDD 時，如果 HDD 介質不 可 訪 問 、 空 間 不 足 ， 也 可 能 遷 移 失 敗 （ 但 是 會 不 斷 嘗 試 ） ， 如 果 FE 參 數enable_strict_storage_medium_check 為 True 則當集群內沒有設定 SSD 存盤介質時，會報錯Failed to find enough host in all backends with storage medium is SSD，

標籤：大數據

上一篇：SQL進階教程讀后總結與感想

下一篇：返回列表