主頁 > 資料庫 > Doris(七) -- 修改表、動態和臨時磁區、join的優化

Doris(七) -- 修改表、動態和臨時磁區、join的優化

2023-06-02 09:26:04 資料庫

修改表

修改表名

-- 1.將名為 table1 的表修改為 table2
ALTER TABLE table1 RENAME table2;

-- 示例
ALTER TABLE aggregate_test RENAME aggregate_test1;

-- 2.將表 example_table 中名為 rollup1 的 rollup index 修改為 rollup2
ALTER TABLE base_table_name RENAME ROLLUP old_rollup_name new_rollup_name;

ALTER TABLE ex_user RENAME ROLLUP rollup_u_cost new_rollup_u_cost;

desc ex_user all;

-- 3.將表 example_table 中名為 p1 的 partition 修改為 p2
ALTER TABLE example_table RENAME PARTITION old_partition_name new_partition_name ;

-- 示例：
ALTER TABLE expamle_range_tbl RENAME PARTITION p201701 newp201701; 

show partitions from expamle_range_tbl \G;

表結構變更

用戶可以通過 Schema Change 操作來修改已存在表的 Schema，目前 Doris 支持以下幾種修改:
? 增加、洗掉列
? 修改列型別
? 調整列順序
? 增加、修改 Bloom Filter index
? 增加、洗掉 bitmap index

原理介紹

執行 Schema Change 的基本程序，是通過原 Index 的資料，生成一份新 Schema 的 Index 的資料，其中主要需要進行兩部分資料轉換：
一是已存在的歷史資料的轉換；
二是在 Schema Change 執行程序中，新到達的匯入資料的轉換，

創建作業
Schema Change 的創建是一個異步程序，作業提交成功后，用戶需要通過 SHOW ALTER TABLE COLUMN 命令來查看作業進度，

-- 語法：
ALTER TABLE [database.]table alter_clause;

schema change 的 alter_clause 支持如下幾種修改方式：
1.向指定 index 的指定位置添加一列

ALTER TABLE db.table_name
-- 如果增加的是key列 那么，需要在 列型別后面增加key 這個關鍵字
-- 如果增加的是value列 那么，是聚合表模型，需要指定列的聚合型別   如果是明細模型和唯一模型，不需要指定
ADD COLUMN column_name column_type [KEY | agg_type] [DEFAULT "default_value"]
[AFTER column_name|FIRST]  -- 確定列的位置   如果不寫，默認插在最后
[TO rollup_index_name]   -- 如果你是針對rollup表新增一個列，那么這個列明基表中不能有
[PROPERTIES ("key"="value", ...)]

-- 明細模型中添加value列
ALTER TABLE test.expamle_range_tbl ADD COLUMN abc varchar AFTER age；

-- 明細模型中添加key 列
ALTER TABLE test.expamle_range_tbl ADD COLUMN abckey varchar key AFTER user_id;

-- 聚合模型中添加一個value列
mysql> ALTER TABLE test.ex_user ADD COLUMN abckey int sum AFTER cost;

注意：

  • 聚合模型如果增加 value 列，需要指定 agg_type
  • 非聚合模型（如 DUPLICATE KEY）如果增加key列，需要指定KEY關鍵字
  • 不能在 rollup index 中增加 base index 中已經存在的列（如有需要，可以重新創建一個 rollup index）

示例：

-- 源schema:

+-----------+-------+------+------+------+---------+-------+
| IndexName | Field | Type | Null | Key  | Default | Extra |
+-----------+-------+------+------+------+---------+-------+
| tbl1      | k1    | INT  | No   | true | N/A     |       |
|           | k2    | INT  | No   | true | N/A     |       |
|           | k3    | INT  | No   | true | N/A     |       |
|           |       |      |      |      |         |       |
| rollup2   | k2    | INT  | No   | true | N/A     |       |
|           |       |      |      |      |         |       |
| rollup1   | k1    | INT  | No   | true | N/A     |       |
|           | k2    | INT  | No   | true | N/A     |       |
+-----------+-------+------+------+------+---------+-------+

-- 源schema中沒有k4和k5列，所以可以往rollup表中添加 k4和k5列，在往rollup表中添加的程序，也會往base表中添加一份
ALTER TABLE tbl1
ADD COLUMN k4 INT default "1" to rollup1,
ADD COLUMN k4 INT default "1" to rollup2,
ADD COLUMN k5 INT default "1" to rollup2;

-- 改變完成后，Schema 變為       base表中也會相應的添加k4和k5
+-----------+-------+------+------+------+---------+-------+
| IndexName | Field | Type | Null | Key  | Default | Extra |
+-----------+-------+------+------+------+---------+-------+
| tbl1      | k1    | INT  | No   | true | N/A     |       |
|           | k2    | INT  | No   | true | N/A     |       |
|           | k3    | INT  | No   | true | N/A     |       |
|           | k4    | INT  | No   | true | 1       |       |
|           | k5    | INT  | No   | true | 1       |       |
|           |       |      |      |      |         |       |
| rollup2   | k2    | INT  | No   | true | N/A     |       |
|           | k4    | INT  | No   | true | 1       |       |
|           | k5    | INT  | No   | true | 1       |       |
|           |       |      |      |      |         |       |
| rollup1   | k1    | INT  | No   | true | N/A     |       |
|           | k2    | INT  | No   | true | N/A     |       |
|           | k4    | INT  | No   | true | 1       |       |
+-----------+-------+------+------+------+---------+-------+

-- 這樣的匯入方式錯誤
-- 因為base表中已經存在k3，匯入的時候無法將base表中在添加一個叫k3的列，重復
ALTER TABLE tbl1
ADD COLUMN k3 INT default "1" to rollup1

2.向指定 index 添加多列

ALTER TABLE db.table_name
ADD COLUMN (column_name1 column_type [KEY | agg_type] DEFAULT "default_value", ...)
[TO rollup_index_name]
[PROPERTIES ("key"="value", ...)]

-- 添加的時候根據key和value列，添加在對應的列之后
ALTER TABLE test.expamle_range_tbl ADD COLUMN (abc int,bcd int);

mysql> ALTER TABLE test.expamle_range_tbl ADD COLUMN (a int key ,b int);
Query OK, 0 rows affected (0.01 sec)

mysql> desc expamle_range_tbl all;

3.從指定 index 中洗掉一列

ALTER TABLE db.table_name
DROP COLUMN column_name
[FROM rollup_index_name]

-- 洗掉明細表中的value列
ALTER TABLE test.expamle_range_tbl DROP COLUMN abc;

-- 洗掉明細表中的key列
ALTER TABLE test.expamle_range_tbl DROP COLUMN abckey;

-- 洗掉聚合模型中的value列
ALTER TABLE test.ex_user DROP COLUMN abckey;

-- 注意：
-- 不能洗掉磁區列
-- 如果是從 base index 中洗掉列，則如果 rollup index 中包含該列，也會被洗掉

4.修改指定 index 的列型別以及列位置

ALTER TABLE db.table_name
MODIFY COLUMN column_name column_type [KEY | agg_type] [NULL | NOT NULL] [DEFAULT "default_value"]
[AFTER column_name|FIRST]
[FROM rollup_index_name]
[PROPERTIES ("key"="value", ...)]

-- 注意：
-- 聚合模型如果修改 value 列，需要指定 agg_type
-- 非聚合型別如果修改key列，需要指定KEY關鍵字
-- 磁區列和分桶列不能做任何修改

5.對指定 index 的列進行重新排序

ALTER TABLE db.table_name
ORDER BY (column_name1, column_name2, ...)
[FROM rollup_index_name]
[PROPERTIES ("key"="value", ...)]

-- 注意：
-- index 中的所有列都要寫出來
-- value 列在 key 列之后

示例：

-- 1.向 example_rollup_index 的 col1 后添加一個key列 new_col(非聚合模型)
ALTER TABLE example_db.my_table
ADD COLUMN new_col INT KEY DEFAULT "0" AFTER col1
TO example_rollup_index;

-- 2.向example_rollup_index的col1后添加一個value列new_col(非聚合模型)
ALTER TABLE example_db.my_table  
ADD COLUMN new_col INT DEFAULT "0" AFTER col1 
TO example_rollup_index;

-- 3.向example_rollup_index的col1后添加一個key列new_col(聚合模型)
ALTER TABLE example_db.my_table   
ADD COLUMN new_col INT DEFAULT "0" AFTER col1    
TO example_rollup_index;

-- 4.向example_rollup_index的col1后添加一個value列new_col SUM聚合型別(聚合模型)
ALTER TABLE example_db.my_table   
ADD COLUMN new_col INT SUM DEFAULT "0" AFTER col1    
TO example_rollup_index;

-- 5.向 example_rollup_index 添加多列(聚合模型)
ALTER TABLE example_db.my_table
ADD COLUMN (col1 INT DEFAULT "1", col2 FLOAT SUM DEFAULT "2.3")
TO example_rollup_index;

-- 6.從 example_rollup_index 洗掉一列
ALTER TABLE example_db.my_table
DROP COLUMN col2
FROM example_rollup_index;

-- 7.修改 base index 的 key 列 col1 的型別為 BIGINT，并移動到 col2 列后面，
ALTER TABLE example_db.my_table 
MODIFY COLUMN col1 BIGINT KEY DEFAULT "1" AFTER col2;

-- 注意：無論是修改 key 列還是 value 列都需要宣告完整的 column 資訊

-- 8.修改 base index 的 val1 列最大長度，原 val1 為 (val1 VARCHAR(32) REPLACE DEFAULT "abc")
ALTER TABLE example_db.my_table 
MODIFY COLUMN val1 VARCHAR(64) REPLACE DEFAULT "abc";

-- 9.重新排序 example_rollup_index 中的列（設原列順序為：k1,k2,k3,v1,v2）
ALTER TABLE example_db.my_table
ORDER BY (k3,k1,k2,v2,v1)
FROM example_rollup_index;

-- 10.同時執行兩種操作
ALTER TABLE example_db.my_table
ADD COLUMN v2 INT MAX DEFAULT "0" AFTER k2 TO example_rollup_index,
ORDER BY (k3,k1,k2,v2,v1) FROM example_rollup_index;

查看作業

SHOW ALTER TABLE COLUMN 可以查看當前正在執行或已經完成的 Schema Change 作業，當一次 Schema Change 作業涉及到多個 Index 時，該命令會顯示多行，每行對應一個 Index

SHOW ALTER TABLE COLUMN\G;
*************************** 1. row ***************************
        JobId: 20021
    TableName: tbl1
   CreateTime: 2019-08-05 23:03:13
   FinishTime: 2019-08-05 23:03:42
    IndexName: tbl1
      IndexId: 20022
OriginIndexId: 20017
SchemaVersion: 2:792557838
TransactionId: 10023
        State: FINISHED
          Msg: 
     Progress: NULL
      Timeout: 86400
1 row in set (0.00 sec)

-- JobId：每個 Schema Change 作業的唯一 ID，
-- TableName：Schema Change 對應的基表的表名，
-- CreateTime：作業創建時間，
-- FinishedTime：作業結束時間，如未結束，則顯示 "N/A"，
-- IndexName： 本次修改所涉及的某一個 Index 的名稱，
-- IndexId：新的 Index 的唯一 ID，
-- OriginIndexId：舊的 Index 的唯一 ID，
-- SchemaVersion：以 M:N 的格式展示，其中 M 表示本次 Schema Change 變更的版本，N 表示對應的 Hash 值，每次 Schema Change，版本都會遞增，
-- TransactionId：轉換歷史資料的分水嶺 transaction ID，
-- State：作業所在階段，
-- 	PENDING：作業在佇列中等待被調度，
-- 	WAITING_TXN：等待分水嶺 transaction ID 之前的匯入任務完成，
-- 	RUNNING：歷史資料轉換中，
-- 	FINISHED：作業成功，
-- 	CANCELLED：作業失敗，
-- Msg：如果作業失敗，這里會顯示失敗資訊，
-- Progress：作業進度，只有在 RUNNING 狀態才會顯示進度，進度是以 M/N 的形式顯示，其中 N 為 Schema Change 涉及的總副本數，M 為已完成歷史資料轉換的副本數，
-- Timeout：作業超時時間，單位秒，

取消作業

在作業狀態不為 FINISHED 或 CANCELLED 的情況下,可以通過以下命令取消Schema Change作業：

CANCEL ALTER TABLE COLUMN FROM tbl_name;

注意事項
? 一張表在同一時間只能有一個 Schema Change 作業在運行，
? Schema Change 操作不阻塞匯入和查詢操作，
? 磁區列和分桶列不能修改，
? 如果 Schema 中有 REPLACE 方式聚合的 value 列，則不允許洗掉 Key 列，
? 如果洗掉 Key 列，Doris 無法決定 REPLACE 列的取值，
? Unique 資料模型表的所有非 Key 列都是 REPLACE 聚合方式，
? 在新增聚合型別為 SUM 或者 REPLACE 的 value 列時，該列的默認值對歷史資料沒有含義，
? 因為歷史資料已經失去明細資訊，所以默認值的取值并不能實際反映聚合后的取值，
? 當修改列型別時，除 Type 以外的欄位都需要按原列上的資訊補全，
? 如修改列 k1 INT SUM NULL DEFAULT "1" 型別為 BIGINT，則需執行命令如下：
? ALTER TABLE tbl1 MODIFY COLUMN k1 BIGINT SUM NULL DEFAULT "1";
? 注意，除新的列型別外，如聚合方式，Nullable 屬性，以及默認值都要按照原資訊補全，
? 不支持修改列名稱、聚合型別、Nullable 屬性、默認值以及列注釋，

partition的增減

-- 1.增加磁區, 使用默認分桶方式：現有磁區 \[MIN, 2013-01-01)，增加磁區 \[2013-01-01, 2014-01-01)
ALTER TABLE example_db.my_table ADD PARTITION p1 VALUES LESS THAN ("2014-01-01");

-- 2.增加磁區，使用新的分桶數
ALTER TABLE example_db.my_table ADD PARTITION p1 VALUES LESS THAN ("2015-01-01") 
DISTRIBUTED BY HASH(k1) BUCKETS 20; 

-- 3.增加磁區，使用新的副本數 
ALTER TABLE example_db.my_table ADD PARTITION p1 VALUES LESS THAN ("2015-01-01") 
("replication_num"="1"); 

-- 4.修改磁區副本數 
ALTER TABLE example_db.my_table MODIFY PARTITION p1 SET("replication_num"="1"); 
-- 5.批量修改指定磁區
ALTER TABLE example_db.my_table MODIFY PARTITION (p1, p2, p4) SET("in_memory"="true"); 

-- 6.批量修改所有磁區 
ALTER TABLE example_db.my_table MODIFY PARTITION (*) SET("storage_medium"="HDD"); 

-- 7.洗掉磁區 
ALTER TABLE example_db.my_table DROP PARTITION p1; 
-- 8.增加一個指定上下界的磁區 
ALTER TABLE example_db.my_table ADD PARTITION p1 VALUES [("2014-01-01"), ("2014-02-01"));

rollup的增減

-- 1.創建 index: example_rollup_index，基于 base index（k1,k2,k3,v1,v2），列式存盤， 
ALTER TABLE example_db.my_table ADD ROLLUP example_rollup_index(k1, k3, v1, v2);

-- 2.創建 index: example_rollup_index2，基于 example_rollup_index（k1,k3,v1,v2）
ALTER TABLE example_db.my_table ADD ROLLUP example_rollup_index2 (k1, v1) 
FROM example_rollup_index;

-- 3.創建 index: example_rollup_index3, 基于base index (k1,k2,k3,v1), 自定義rollup超時時間一小時
ALTER TABLE example_db.my_table ADD ROLLUP example_rollup_index(k1, k3, v1) 
PROPERTIES("timeout" = "3600"); 

-- 4.洗掉 index: example_rollup_index2
ALTER TABLE example_db.my_table DROP ROLLUP example_rollup_index2;

動態磁區和臨時磁區

動態磁區

原理

在某些使用場景下，用戶會將表按照天進行磁區劃分，每天定時執行例行任務，這時需要使用方手動管理磁區，否則可能由于使用方沒有創建磁區導致資料匯入失敗，這給使用方帶來了額外的維護成本，通過動態磁區功能，用戶可以在建表時設定動態磁區的規則，FE 會啟動一個后臺執行緒，根據用戶指定的規則創建或洗掉磁區，用戶也可以在運行時對現有規則進行變更，

使用方式

動態磁區的規則可以在建表時指定，或者在運行時進行修改，當前僅支持對單磁區列的磁區表設定動態磁區規則

-- 建表時指定
CREATE TABLE tbl1
(...)
PROPERTIES
(
-- 添加動態磁區的規則
    "dynamic_partition.prop1" = "value1",
    "dynamic_partition.prop2" = "value2",
    ...
)

-- 運行時修改
ALTER TABLE tbl1 SET
(
    "dynamic_partition.prop1" = "value1",
    "dynamic_partition.prop2" = "value2",
    ...
)

動態磁區規則引數

  1. dynamic_partition.enable：是否開啟動態磁區特性，默認是true
  2. dynamic_partition.time_unit：動態磁區調度的單位，可指定為 HOUR、DAY、WEEK、MONTH，分別表示按小時、按天、按星期、按月進行磁區創建或洗掉，
  3. dynamic_partition.time_zone：動態磁區的時區，如果不填寫，則默認為當前機器的系統的時區
  4. dynamic_partition.start：動態磁區的起始偏移，為負數，以當天（星期/月）為基準，磁區范圍在此偏移之前的磁區將會被洗掉，如果不填寫，則默認為 -2147483648，即不洗掉歷史磁區，
  5. dynamic_partition.end：動態磁區的結束偏移，為正數，根據 time_unit 屬性的不同，以當天（星期/月）為基準，提前創建對應范圍的磁區，
  6. dynamic_partition.prefix：動態創建的磁區名前綴，
  7. dynamic_partition.buckets：動態創建的磁區所對應的分桶數量
  8. dynamic_partition.replication_num：動態創建的磁區所對應的副本數量，如果不填寫，則默認為該表創建時指定的副本數量
  9. dynamic_partition.start_day_of_week：當 time_unit 為 WEEK 時，該引數用于指定每周的起始點，取值為 1 到 7，其中 1 表示周一，7 表示周日，默認為 1，即表示每周以周一為起始點，
  10. dynamic_partition.start_day_of_month：當 time_unit 為 MONTH 時，該引數用于指定每月的起始日期，取值為 1 到 28，其中 1 表示每月1號，28 表示每月28號，默認為 1，即表示每月以1號位起始點，暫不支持以29、30、31號為起始日，以避免因閏年或閏月帶來的歧義
  11. dynamic_partition.create_history_partition：為 true 時代表可以創建歷史磁區，默認是false
  12. dynamic_partition.history_partition_num：當 create_history_partition 為 true 時，該引數用于指定創建歷史磁區數量，默認值為 -1， 即未設定，
  13. dynamic_partition.hot_partition_num：指定最新的多少個磁區為熱磁區，對于熱磁區，系統會自動設定其 storage_medium 引數為SSD，并且設定 storage_cooldown_time ，hot_partition_num：設定往前 n 天和未來所有磁區為熱磁區，并自動設定冷卻時間
  14. dynamic_partition.reserved_history_periods：需要保留的歷史磁區的時間范圍，

修改動態磁區屬性

ALTER TABLE tbl1 SET
(
    "dynamic_partition.prop1" = "value1",
    ...
);


ALTER TABLE partition_test SET
(
    "dynamic_partition.time_unit" = "week",
    "dynamic_partition.start" = "-1",
    "dynamic_partition.end" = "1"
);

某些屬性的修改可能會產生沖突，假設之前磁區粒度為 DAY，并且已經創建了如下磁區：

p20200519: ["2020-05-19", "2020-05-20")
p20200520: ["2020-05-20", "2020-05-21")
p20200521: ["2020-05-21", "2020-05-22")

如果此時將磁區粒度改為 MONTH，則系統會嘗試創建范圍為 ["2020-05-01", "2020-06-01") 的磁區，而該磁區的磁區范圍和已有磁區沖突，所以無法創建，而范圍為 ["2020-06-01", "2020-07-01") 的磁區可以正常創建，因此，2020-05-22 到 2020-05-30 時間段的磁區，需要自行填補，

查看動態磁區表調度情況

-- 通過以下命令可以進一步查看當前資料庫下，所有動態磁區表的調度情況：
SHOW DYNAMIC PARTITION TABLES;

-- LastUpdateTime: 最后一次修改動態磁區屬性的時間
-- LastSchedulerTime: 最后一次執行動態磁區調度的時間
-- State: 最后一次執行動態磁區調度的狀態
-- LastCreatePartitionMsg: 最后一次執行動態添加磁區調度的錯誤資訊
-- LastDropPartitionMsg: 最后一次執行動態洗掉磁區調度的錯誤資訊

臨時磁區

規則

? 臨時磁區的磁區列和正式磁區相同，且不可修改，
? 一張表所有臨時磁區之間的磁區范圍不可重疊，但臨時磁區的范圍和正式磁區范圍可以重疊，
? 臨時磁區的磁區名稱不能和正式磁區以及其他臨時磁區重復，

操作

臨時磁區支持添加、洗掉、替換操作，

添加臨時磁區

可以通過 ALTER TABLE ADD TEMPORARY PARTITION 陳述句對一個表添加臨時磁區：

ALTER TABLE tbl1 ADD TEMPORARY PARTITION tp1 VALUES LESS THAN("2020-02-01");


ALTER TABLE tbl1 ADD TEMPORARY PARTITION tp2 VALUES LESS THAN("2020-02-02")
("in_memory" = "true", "replication_num" = "1")
DISTRIBUTED BY HASH(k1) BUCKETS 5;


ALTER TABLE tbl3 ADD TEMPORARY PARTITION tp1 VALUES IN ("Beijing", "Shanghai");

ALTER TABLE tbl3 ADD TEMPORARY PARTITION tp1 VALUES IN ("Beijing", "Shanghai")
("in_memory" = "true", "replication_num" = "1")
DISTRIBUTED BY HASH(k1) BUCKETS 5;


-- 添加操作的一些說明：
-- 臨時磁區的添加和正式磁區的添加操作相似，臨時磁區的磁區范圍獨立于正式磁區，
-- 臨時磁區可以獨立指定一些屬性，包括分桶數、副本數、是否是記憶體表、存盤介質等資訊，

洗掉臨時磁區

-- 可以通過 ALTER TABLE DROP TEMPORARY PARTITION 陳述句洗掉一個表的臨時磁區：
ALTER TABLE tbl1 DROP TEMPORARY PARTITION tp1;
-- 洗掉臨時磁區，不影響正式磁區的資料，

替換磁區

可以通過 ALTER TABLE REPLACE PARTITION 陳述句將一個表的正式磁區替換為臨時磁區，

-- 正式磁區替換成臨時磁區以后，正是磁區的資料會被洗掉，并且這個程序是不可逆的
-- 用之前要小心
ALTER TABLE tbl1 REPLACE PARTITION (p1) WITH TEMPORARY PARTITION (tp1);

ALTER TABLE partition_test REPLACE PARTITION (p20230104) WITH TEMPORARY PARTITION (tp1);

ALTER TABLE tbl1 REPLACE PARTITION (p1, p2) WITH TEMPORARY PARTITION (tp1, tp2)
PROPERTIES (
    "strict_range" = "false",
    "use_temp_partition_name" = "true"
);

-- strict_range：默認為 true，
-- 	對于 Range 磁區，當該引數為 true 時，表示要被替換的所有正式磁區的范圍并集需要和替換的臨時磁區的范圍并集完全相同，當置為 false 時，只需要保證替換后，新的正式磁區間的范圍不重疊即可，
-- 	對于 List 磁區，該引數恒為 true，要被替換的所有正式磁區的列舉值必須和替換的臨時磁區列舉值完全相同，
-- use_temp_partition_name：默認為 false，當該引數為 false，并且待替換的磁區和替換磁區的個數相同時，則替換后的正式磁區名稱維持不變，如果為 true，則替換后，正式磁區的名稱為替換磁區的名稱，


LTER TABLE tbl1 REPLACE PARTITION (p1) WITH TEMPORARY PARTITION (tp1);
-- use_temp_partition_name 默認為 false，則在替換后，磁區的名稱依然為 p1，但是相關的資料和屬性都替換為 tp1 的， 如果 use_temp_partition_name 默認為 true，則在替換后，磁區的名稱為 tp1，p1 磁區不再存在，

ALTER TABLE tbl1 REPLACE PARTITION (p1, p2) WITH TEMPORARY PARTITION (tp1);
-- use_temp_partition_name 默認為 false，但因為待替換磁區的個數和替換磁區的個數不同，則該引數無效，替換后，磁區名稱為 tp1，p1 和 p2 不再存在，


-- 替換操作的一些說明：
-- 磁區替換成功后，被替換的磁區將被洗掉且不可恢復，

資料的匯入和查詢

匯入臨時磁區

根據匯入方式的不同，指定匯入臨時磁區的語法稍有差別，這里通過示例進行簡單說明

-- 查詢結果用insert匯入
INSERT INTO tbl TEMPORARY PARTITION(tp1, tp2, ...) SELECT ....

-- 查看資料
SELECT ... FROM
tbl1 TEMPORARY PARTITION(tp1, tp2, ...)
JOIN
tbl2 TEMPORARY PARTITION(tp1, tp2, ...)
ON ...
WHERE ...;

doris中join的優化原理

Shuffle Join（Partitioned Join）

和mr中的shuffle程序是一樣的,針對每個節點上的資料進行shuffle，相同資料分發到下游的節點上的join方式叫shuffle join

-- 訂單表
CREATE TABLE  test.order_info_shuffle
(
 `order_id` varchar(20) COMMENT "訂單id",
 `user_id` varchar(20) COMMENT "用戶id",
 `goods_id` VARCHAR(20) COMMENT "商品id",
 `goods_num` Int COMMENT "商品數量",
 `price` double COMMENT "商品價格"
)
duplicate KEY(`order_id`)
DISTRIBUTED BY HASH(`order_id`) BUCKETS 5;

-- 匯入資料：
insert into test.order_info_shuffle values\
('o001','u001','g001',1,9.9 ),\
('o001','u001','g002',2,19.9),\
('o001','u001','g003',2,39.9),\
('o002','u002','g001',3,9.9 ),\
('o002','u002','g002',1,19.9),\
('o003','u002','g003',1,39.9),\
('o003','u002','g002',2,19.9),\
('o003','u002','g004',3,99.9),\
('o003','u002','g005',1,99.9),\
('o004','u003','g001',2,9.9 ),\
('o004','u003','g002',1,19.9),\
('o004','u003','g003',4,39.9),\
('o004','u003','g004',1,99.9),\
('o004','u003','g005',4,89.9);


-- 商品表
CREATE TABLE  test.goods_shuffle
(
 `goods_id` VARCHAR(20) COMMENT "商品id",
 `goods_name`  VARCHAR(20) COMMENT "商品名稱",
 `category_id` VARCHAR(20) COMMENT "商品品類id"
)
duplicate KEY(`goods_id`)
DISTRIBUTED BY HASH(`goods_id`) BUCKETS 5;

-- 匯入資料：
insert into test.goods_shuffle values\
('g001','iphon13','c001'),\
('g002','ipad','c002'),\
('g003','xiaomi12','c001'),\
('g004','huaweip40','c001'),\
('g005','headset','c003');


-- sql示例
EXPLAIN 
select 
oi.order_id,
oi.user_id,
oi.goods_id,
gs.goods_name,
gs.category_id,
oi.goods_num,
oi.price
from order_info_shuffle as oi
-- 我們可以不指定哪一種join方式，doris會自己根據資料的實際情況幫我們選擇
JOIN goods_shuffle as gs
on oi.goods_id = gs.goods_id;

EXPLAIN select 
oi.order_id,
oi.user_id,
oi.goods_id,
gs.goods_name,
gs.category_id,
oi.goods_num,
oi.price
from order_info_shuffle as oi
-- 可以顯式的hint指定我們想要的join型別
JOIN [broadcast] goods_shuffle as gs
on oi.goods_id = gs.goods_id;

適用場景：不管資料量，不管是大表join大表還是大表join小表都可以用
優點：通用
缺點：需要shuffle記憶體和網路開銷比較大，效率不高

Broadcast Join

當一個大表join小表的時候，將小表廣播到每一個大表所在的每一個節點上(以hash表的形式放在記憶體中)這樣的方式叫做Broadcast Join，類似于mr里面的一個map端join

-- 顯式使用 Broadcast Join：
EXPLAIN 
select 
oi.order_id,
oi.user_id,
oi.goods_id,
gs.goods_name,
gs.category_id,
oi.goods_num,
oi.price
from order_info_broadcast as oi
JOIN [broadcast] goods_broadcast as gs
on oi.goods_id = gs.goods_id;

適用場景：
左表join右表，要求左表的資料量相對來說比較大，右表資料量比較小
優點：避免了shuffle，提高了運算效率
缺點：有限制，必須右表資料量比較小

Bucket Shuffle Join

利用建表時候分桶的特性，當join的時候，join的條件和左表的分桶欄位一樣的時候，將右表按照左表分桶的規則進行shuffle操作，使右表中需要join的資料落在左表中需要join資料的BE節點上的join方式叫做Bucket Shuffle Join，

-- 從 0.14 版本開始默認為 true，新版本可以不用設定這個引數了！
show variables like '%bucket_shuffle_join%'; 
set enable_bucket_shuffle_join = true;

-- 通過 explain 查看 join 型別
EXPLAIN 
select 
oi.order_id,
oi.user_id,
oi.goods_id,
gs.goods_name,
gs.category_id,
oi.goods_num,
oi.price
from order_info_bucket as oi
-- 目前 Bucket Shuffle Join不能像Shuffle Join那樣可以顯示指定Join方式，
-- 只能讓執行引擎自動選擇，
-- 選擇的順序：Colocate Join -> Bucket Shuffle Join -> Broadcast Join -> Shuffle Join，
JOIN goods_bucket as gs
where oi.goods_id = gs.goods_id;




EXPLAIN select 
oi.order_id,
oi.user_id,
oi.goods_id,
gs.goods_name,
gs.category_id,
oi.goods_num,
oi.price
from order_info_bucket as oi
-- 目前 Bucket Shuffle Join不能像Shuffle Join那樣可以顯示指定Join方式，
-- 只能讓執行引擎自動選擇，
-- 選擇的順序：Colocate Join -> Bucket Shuffle Join -> Broadcast Join -> Shuffle Join，
JOIN goods_bucket1 as gs
where oi.goods_id = gs.goods_id;

-- 注意事項:
-- Bucket Shuffle Join 只生效于 Join 條件為等值的場景
-- Bucket Shuffle Join 要求左表的分桶列的型別與右表等值 join 列的型別需要保持一致，否則無法進行對應的規劃， 
-- Bucket Shuffle Join 只作用于 Doris 原生的 OLAP 表，對于 ODBC，MySQL，ES 等外表，當其作為左表時是無法規劃生效的， 
-- Bucket Shuffle Join只能保證左表為單磁區時生效，所以在 SQL 執行之中，需要盡量使用 where 條件使磁區裁剪的策略能夠生效，

Colocation Join

中文意思叫位置協同分組join，指需要join的兩份資料都在同一個BE節點上，這樣在join的時候，直接本地join計算即可，不需要進行shuffle，

名詞解釋
? Colocation Group（位置協同組CG）：在同一個 CG內的 Table 有著相同的 Colocation Group Schema，并且有著相同的資料分片分布(滿足三個條件)，
? Colocation Group Schema（CGS）：用于描述一個 CG 中的 Table，和 Colocation 相關的通用 Schema 資訊，包括分桶列型別，分桶數以及磁區的副本數等，

使用限制

  1. 建表時兩張表的分桶列的型別和數量需要完全一致，并且桶數一致，才能保證多張表的資料分片能夠一一對應的進行分布控制，
  2. 同一個 CG 內所有表的所有磁區（Partition）的副本數必須一致，如果不一致，可能出現某一個Tablet 的某一個副本，在同一個 BE 上沒有其他的表分片的副本對應
  3. 同一個 CG 內的表，磁區的個數、范圍以及磁區列的型別不要求一致，

使用案例

-- 建兩張表，分桶列都為 int 型別，且桶的個數都是 5 個，副本數都為默認副本數

-- 撰寫查詢陳述句，并查看執行計劃
EXPLAIN 
select 
oi.order_id,
oi.user_id,
oi.goods_id,
gs.goods_name,
gs.category_id,
oi.goods_num,
oi.price
from order_info_colocation as oi
-- 目前 Colocation Join不能像Shuffle Join那樣可以顯示指定Join方式，
-- 只能讓執行引擎自動選擇，
-- 選擇的順序：Colocate Join -> Bucket Shuffle Join -> Broadcast Join -> Shuffle Join，
JOIN goods_colocation as gs
where oi.goods_id = gs.goods_id;


-- 查看 Group
SHOW PROC '/colocation_group';

-- 當 Group 中最后一張表徹底洗掉后（徹底洗掉是指從回收站中洗掉，通常，一張表通過DROP TABLE 命令洗掉后，會在回收站默認停留一天的時間后，再洗掉），該 Group 也會被自動洗掉，
-- 修改表 Colocate Group 屬性
ALTER TABLE tbl SET ("colocate_with" = "group2");
-- 如果被修改的表原來有group，那么會直接將原來的group洗掉后創建新的group, 如果原來沒有組，就直接創建

-- 洗掉表的 Colocation 屬性
ALTER TABLE tbl SET ("colocate_with" = ""); 
-- 當對一個具有 Colocation 屬性的表進行增加磁區（ADD PARTITION）、修改副本數時,Doris 會檢查修改是否會違反 Colocation Group Schema，如果違反則會拒絕

Runtime Filter

Runtime Filter會在有join動作的 sql運行時，創建一個HashJoinNode和一個ScanNode來對join的資料進行過濾優化，使得join的時候資料量變少，從而提高效率

使用

-- 指定 RuntimeFilter 型別 
set runtime_filter_type="BLOOM_FILTER,IN,MIN_MAX";

set runtime_filter_type="MIN_MAX";

引數解釋：

  • runtime_filter_type: 包括Bloom Filter、MinMax Filter、IN predicate、IN Or Bloom Filter
    • Bloom Filter: 針對右表中的join欄位的所有資料標注在一個布隆過濾器中，從而判斷左表中需要join的資料在還是不在
    • MinMax Filter: 獲取到右表表中資料的最大值和最小值，看左表中查看，將超出這個最大值最小值范圍的資料過濾掉
    • IN predicate: 將右表中需要join欄位所有資料構建一個IN predicate，再去左表表中過濾無意義資料
  • runtime_filter_wait_time_ms: 左表的ScanNode等待每個Runtime Filter的時間，默認1000ms
  • runtime_filters_max_num: 每個查詢可應用的Runtime Filter中Bloom Filter的最大數量，默認10
  • runtime_bloom_filter_min_size: Runtime Filter中Bloom Filter的最小長度，默認1M
  • runtime_bloom_filter_max_size: Runtime Filter中Bloom Filter的最大長度，默認16M
  • runtime_bloom_filter_size: Runtime Filter中Bloom Filter的默認長度，默認2M
  • runtime_filter_max_in_num: 如果join右表資料行數大于這個值，我們將不生成IN predicate，默認102400

示例

-- 建表
CREATE TABLE test (t1 INT) DISTRIBUTED BY HASH (t1) BUCKETS 2  
PROPERTIES("replication_num" = "1"); 

INSERT INTO test VALUES (1), (2), (3), (4); 

CREATE TABLE test2 (t2 INT) DISTRIBUTED BY HASH (t2) BUCKETS 2  
PROPERTIES("replication_num" = "1"); 

INSERT INTO test2 VALUES (3), (4), (5); 

-- 查看執行計劃
set runtime_filter_type="BLOOM_FILTER,IN,MIN_MAX";

EXPLAIN SELECT t1 FROM test JOIN test2 where test.t1 = test2.t2;

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/554101.html

標籤：其他

上一篇：Doris(七) -- 修改表、動態和臨時磁區、join的優化

下一篇：返回列表

標籤雲
其他(160197) Python(38196) JavaScript(25473) Java(18173) C(15235) 區塊鏈(8269) C＃(7972) AI(7469) 爪哇(7425) MySQL(7222) html(6777) 基礎類(6313) sql(6102) 熊猫(6058) PHP(5873) 数组(5741) R(5409) Linux(5344) 反应(5209) 腳本語言(PerlPython)(5129) 非技術區(4971) Android(4580) 数据框(4311) css(4259) 节点.js(4032) C語言(3288) json(3245) 列表(3129) 扑(3119) C++語言(3117) 安卓(2998) 打字稿(2995) VBA(2789) Java相關(2746) 疑難問題(2699) 细绳(2522) 單片機工控(2479) iOS(2434) ASP.NET(2403) MongoDB(2323) 麻木的(2285) 正则表达式(2254) 字典(2211) 循环(2198) 迅速(2185) 擅长(2169) 镖(2155) .NET技术(1979) 功能(1967) Web開發(1951) HtmlCss(1951) C++(1928) python-3.x(1918) 弹簧靴(1913) xml(1889) PostgreSQL(1879) .NETCore(1863) 谷歌表格(1846) Unity3D(1843) for循环(1842)

熱門瀏覽
  • GPU虛擬機創建時間深度優化

    **?桔妹導讀：**GPU虛擬機實體創建速度慢是公有云面臨的普遍問題，由于通常情況下創建虛擬機屬于低頻操作而未引起業界的重視，實際生產中還是存在對GPU實體創建時間有苛刻要求的業務場景。本文將介紹滴滴云在解決該問題時的思路、方法、并展示最終的優化成果。 從公有云服務商那里購買過虛擬主機的資深用戶，一 ......

    uj5u.com 2020-09-10 06:09:13 more
  • 可編程網卡芯片在滴滴云網路的應用實踐

    **?桔妹導讀：**隨著云規模不斷擴大以及業務層面對延遲、帶寬的要求越來越高，采用DPDK 加速網路報文處理的方式在橫向縱向擴展都出現了局限性。可編程芯片成為業界熱點。本文主要講述了可編程網卡芯片在滴滴云網路中的應用實踐，遇到的問題、帶來的收益以及開源社區貢獻。 #1. 資料中心面臨的問題 隨著滴滴 ......

    uj5u.com 2020-09-10 06:10:21 more
  • 滴滴資料通道服務演進之路

    **?桔妹導讀：**滴滴資料通道引擎承載著全公司的資料同步，為下游實時和離線場景提供了必不可少的源資料。隨著任務量的不斷增加，資料通道的整體架構也隨之發生改變。本文介紹了滴滴資料通道的發展歷程，遇到的問題以及今后的規劃。 #1. 背景 資料，對于任何一家互聯網公司來說都是非常重要的資產，公司的大資料 ......

    uj5u.com 2020-09-10 06:11:05 more
  • 滴滴AI Labs斬獲國際機器翻譯大賽中譯英方向世界第三

    **桔妹導讀：**深耕人工智能領域，致力于探索AI讓出行更美好的滴滴AI Labs再次斬獲國際大獎，這次獲獎的專案是什么呢？一起來看看詳細報道吧！ 近日，由國際計算語言學協會ACL（The Association for Computational Linguistics）舉辦的世界最具影響力的機器 ......

    uj5u.com 2020-09-10 06:11:29 more
  • MPP (Massively Parallel Processing)大規模并行處理

    1、什么是mpp？ MPP (Massively Parallel Processing)，即大規模并行處理，在資料庫非共享集群中，每個節點都有獨立的磁盤存盤系統和記憶體系統，業務資料根據資料庫模型和應用特點劃分到各個節點上，每臺資料節點通過專用網路或者商業通用網路互相連接，彼此協同計算，作為整體提供 ......

    uj5u.com 2020-09-10 06:11:41 more
  • 滴滴資料倉庫指標體系建設實踐

    **桔妹導讀：**指標體系是什么？如何使用OSM模型和AARRR模型搭建指標體系？如何統一流程、規范化、工具化管理指標體系？本文會對建設的方法論結合滴滴資料指標體系建設實踐進行解答分析。 #1. 什么是指標體系 ##1.1 指標體系定義 指標體系是將零散單點的具有相互聯系的指標，系統化的組織起來，通 ......

    uj5u.com 2020-09-10 06:12:52 more
  • 單表千萬行資料庫 LIKE 搜索優化手記

    我們經常在資料庫中使用 LIKE 運算子來完成對資料的模糊搜索，LIKE 運算子用于在 WHERE 子句中搜索列中的指定模式。 如果需要查找客戶表中所有姓氏是“張”的資料，可以使用下面的 SQL 陳述句： SELECT * FROM Customer WHERE Name LIKE '張%' 如果需要 ......

    uj5u.com 2020-09-10 06:13:25 more
  • 滴滴Ceph分布式存盤系統優化之鎖優化

    **桔妹導讀：**Ceph是國際知名的開源分布式存盤系統，在工業界和學術界都有著重要的影響。Ceph的架構和演算法設計發表在國際系統領域頂級會議OSDI、SOSP、SC等上。Ceph社區得到Red Hat、SUSE、Intel等大公司的大力支持。Ceph是國際云計算領域應用最廣泛的開源分布式存盤系統， ......

    uj5u.com 2020-09-10 06:14:51 more
  • es~通過ElasticsearchTemplate進行聚合~嵌套聚合

    之前寫過《es~通過ElasticsearchTemplate進行聚合操作》的文章，這一次主要寫一個嵌套的聚合，例如先對sex集合，再對desc聚合，最后再對age求和，共三層嵌套。 Aggregations的部分特性類似于SQL語言中的group by，avg，sum等函式,Aggregation ......

    uj5u.com 2020-09-10 06:14:59 more
  • 爬蟲日志監控 -- Elastc Stack（ELK）部署

    傻瓜式部署，只需替換IP與用戶 導讀： 現ELK四大組件分別為：Elasticsearch（核心）、logstash（處理）、filebeat（采集）、kibana（可視化） 下載均在https://www.elastic.co/cn/downloads/下tar包，各組件版本最好一致，配合fdm會 ......

    uj5u.com 2020-09-10 06:15:05 more
最新发布
  • Doris(七) -- 修改表、動態和臨時磁區、join的優化

    # 修改表 ## 修改表名 ```SQL -- 1.將名為 table1 的表修改為 table2 ALTER TABLE table1 RENAME table2; -- 示例 ALTER TABLE aggregate_test RENAME aggregate_test1; -- 2.將表 e ......

    uj5u.com 2023-06-02 09:26:04 more
  • Doris(七) -- 修改表、動態和臨時磁區、join的優化

    # 修改表 ## 修改表名 ```SQL -- 1.將名為 table1 的表修改為 table2 ALTER TABLE table1 RENAME table2; -- 示例 ALTER TABLE aggregate_test RENAME aggregate_test1; -- 2.將表 e ......

    uj5u.com 2023-06-02 09:25:02 more
  • [MySQL--SQL優化]

    # [MySQL--SQL優化] # 1、insert優化（插入資料優化） - ## 建議使用批量插入 ```MYSQL # 批量插入避免頻繁連接斷開資料庫（一次連接插入多條資料） insert into 表名 values(資料1),(資料2),(資料3)...... ``` - ## 建議手動提 ......

    uj5u.com 2023-06-01 16:28:49 more
  • 海量資料運維要給力，GaussDB(for Cassandra)來助力

    摘要：應用運維管理平臺（AOM）和Cassandra是兩個不可分割的組成部分，它們共同構成了一個高效的解決方案，可以幫助企業在應用運維業務上取得巨大的優勢。在這篇文章中，我們將介紹AOM和Cassandra的優勢和特點，揭曉它們如何為企業保持市場競爭力的秘密。 本文分享自華為云社區《海量資料運維要給 ......

    uj5u.com 2023-06-01 16:15:15 more
  • 1萬9千多單詞大沖關含真人讀音ACCESS資料庫

    英語類的資料其實已經有很多很多人，但是不同的需求適合的也會是各種的不同，甚至可能需要將多個相關的資料整合起來應用，而今天這份資料庫對于整合來說是非常方便的，因為它是以單詞為關鍵詞進行各種關聯的，也就是說只要是英語類的資料都可以與這份資料關聯起來。 這個資料的好處在于有17830個英語的真人讀音MP3 ......

    uj5u.com 2023-06-01 15:59:53 more
  • ChunJun FTP Connector 功能擴展解讀

    本文將從 [FTP Connector ](https://www.dtstack.com/resources/1044?src=https://www.cnblogs.com/DTinsight/archive/2023/06/01/szsm)的功能詳解，[自定義檔案切割](https://www.dtstack.com/resources/1044?src=szsm)及[自定義 FileFormat ......

    uj5u.com 2023-06-01 15:35:15 more
  • 分而治之 -- 淺談分庫分表及實踐之路

    今天想聊一下分庫分表，因為對于快速增長的業務來說，這個是無法回避的一環。之前我在做商城相關的SAAS系統，商品池是一個存盤瓶頸，商品池數量會基于租戶增長和運營變得指數級增長，短短幾個月就能漲到幾千萬的資料，而運營半年后就可能過億。而對于訂單這種資料，也會跟著業務的成長，也會變得愈發巨大。 ......

    uj5u.com 2023-06-01 15:34:45 more
  • 海量資料運維要給力，GaussDB(for Cassandra)來助力

    摘要：應用運維管理平臺（AOM）和Cassandra是兩個不可分割的組成部分，它們共同構成了一個高效的解決方案，可以幫助企業在應用運維業務上取得巨大的優勢。在這篇文章中，我們將介紹AOM和Cassandra的優勢和特點，揭曉它們如何為企業保持市場競爭力的秘密。 本文分享自華為云社區《海量資料運維要給 ......

    uj5u.com 2023-06-01 15:18:57 more
  • [MySQL--SQL優化]

    # [MySQL--SQL優化] # 1、insert優化（插入資料優化） - ## 建議使用批量插入 ```MYSQL # 批量插入避免頻繁連接斷開資料庫（一次連接插入多條資料） insert into 表名 values(資料1),(資料2),(資料3)...... ``` - ## 建議手動提 ......

    uj5u.com 2023-06-01 15:18:04 more
  • 1萬9千多單詞大沖關含真人讀音ACCESS資料庫

    英語類的資料其實已經有很多很多人，但是不同的需求適合的也會是各種的不同，甚至可能需要將多個相關的資料整合起來應用，而今天這份資料庫對于整合來說是非常方便的，因為它是以單詞為關鍵詞進行各種關聯的，也就是說只要是英語類的資料都可以與這份資料關聯起來。 這個資料的好處在于有17830個英語的真人讀音MP3 ......

    uj5u.com 2023-06-01 15:14:11 more

Copyright © 2010-2020 有解無憂