hive03_高階操作

Hive 分割槽表

https://blog.csdn.net/weixin_41122339/article/details/81584110
表在儲存時，可以將資料根據分割槽鍵的列值儲存在表目錄的子目錄中。這樣將資料切分到不同目錄儲存後，可以加快對分割槽鍵欄位的查詢和過濾速度，透過在查詢條件中指定過濾條件，可以只對指定目錄的資料進行掃描，避免了全表掃描。
分割槽是以欄位的形式在表結構中存在，透過 describe table 命令可以檢視到欄位存在，但是該欄位不存放實際的資料內容，僅僅是分割槽的表示。
分割槽的目的是將一些資料分散到特定的子目錄中，查詢時就只選擇查詢某些子目錄中的資料，以此加快查詢效率。

入門操作

靜態分割槽

-- 建立分割槽表並指定分割槽鍵
create table tb1(
    name string,
    age int
)
partitioned by (dt string)
stored as textfile;

-- 單條插入資料，但是不推薦，會生成大量小檔案
insert into tb1 
partition (dt='2020-11-08')
values('zs', 18);

分割槽表建立成功後，就可以向分割槽表中插入資料了，根據插入資料時存放的分割槽，可以將分割槽分為靜態分割槽和動態分割槽。

靜態分割槽在匯入資料時必須透過 partition (xxx=xxx) 顯式指定分割槽欄位名。

-- 靜態分割槽在資料匯入時必須手動指定目標分割槽
insert into tbl
partition (dt='2020-11-08')
values('1s', 25);

-- 建立分割槽表2，並將分割槽表1的資料匯入分割槽表2
create table tb2(
name string,
age int
)
partitioned by (dt string)
stored as textfile;

insert into table tb2
partition (dt='2020-11-08')
select name,age from tb1;

// 匯入外部檔案來載入資料
load data local inpath '/home/hadoop/datas/dept/detp_01.txt' into table tb2 partition(dt='xxxxx');

外部表同樣可以建立分割槽表，只需要將檔案上傳到 hdfs 指定的目錄下就可以：

create external table employees_ex
 (
 name   string,
 salary  float,
 subordinated array<string>,
 deductions map<string,float>,
 address  struct<street:string,city:string,state:string,zip:int>
 )
 partitioned by (country string,state string)
 row format delimited
 fields terminated by "\t"
 collection items terminated by ","
 map keys terminated by ":"
 lines terminated by "\n"
 stored as textfile;   
 location "/user/had/data/"    //他其實和普通的靜態分割槽表一樣就是多了一個external關鍵字

動態分割槽

如果想要在資料匯入時，由系統幫助判斷資料屬於哪個分割槽，可以使用動態分割槽，但是需要配置開啟。

# 開啟動態分割槽功能
set hive.exec.dynamic.partition=true;

# 指定動態分割槽模式
set hive.exec.dynamic.partition.mode=nostrict;

# 其他配置引數
set hive.exec.max.dynamic.partitions.pernode; --每一個執行mr節點上，允許建立的動態分割槽的最大數量(100)
set hive.exec.max.dynamic.partitions;  --所有執行mr節點上，允許建立的所有動態分割槽的最大數量(1000)
set hive.exec.max.created.files;  --所有的mr job允許建立的檔案的最大數量(100000)
hive.error.on.empty.partition; --當有空分割槽生成時,是否丟擲異常(false)

-- 開啟動態分割槽配置
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nostrict;

-- 對動態分割槽插入資料
insert into table tbl
partition (dt)
select * from tb2;

分桶

分割槽是為了將資料儲存到不同的子目錄中，而分桶是一種改變表的儲存模式，從而完成對錶最佳化的一種調優方式。
分桶是將表拆分到了不同檔案中進行儲存。透過改變資料的儲存分佈，提升查詢、取樣、Join 等任務執行效率。最常見的分桶操作就是對 Key 雜湊取模，這樣可以直接確定資料存放的桶檔案。
另外在 Hive 中兩張表如果需要執行 join 操作，轉換為 MapReduce/Spark 任務後，join 操作一定會涉及到 Shuffle，而 Shuffle 過程耗費大量時間，如果以關鍵字作為分桶鍵，相同 key 一定會定位到相同的桶檔案，那麼可以直接對兩張表的桶檔案進行 join 處理，以此提升效率。

分桶表操作

SQL中使用 DISTRIBUTE BY / CLUSTER BY 指定分桶鍵完成資料分桶寫入

CREATE [EXTERNAL] TABLE <table_name>
    (<col_name> <data_type> [, <col_name> <data_type> ...])]
    [PARTITIONED BY ...]            // 分割槽
    CLUSTERED BY (<col_name>)
        [SORTED BY (<col_name> [ASC|DESC] [, <col_name> [ASC|DESC]...])] 
        INTO <num_buckets> BUCKETS  // 分桶  
    [ROW FORMAT <row_format>] 
    [LOCATION '<file_path>']    
    [TBLPROPERTIES ('<property_name>'='<property_value>', ...)];

-- 指定分桶鍵並倒序排序
INSERT INTO TABLE tb_buckets_desc
    SELECT * FROM tb_buckets
        DISTRIBUTE BY id SORT BY id desc;

-- 設定分桶數並按照分桶鍵升序排列
set mapred.reduce.tasks=3;

INSERT INTO TABLE tb_buckets_desc
    SELECT * FROM tb_buckets
        DISTRIBUTE BY id SORT BY id asc;

INSERT INTO TABLE tb_buckets_desc
    SELECT * FROM tb_buckets
        CLUSTER BY id;

Hive 傾斜表、事務表

傾斜表

對於一列或者多列出現傾斜值的表，可以建立傾斜表來進一步提升效能。比如表中 Key 欄位包括的資料，有大部分為同一資料值，這就造成了資料傾斜。資料傾斜會導致資料處理時，部分節點 Reduce 任務完成，但是需要等待傾斜部分處理完成。

傾斜表使用

// 建立單欄位傾斜表
CREATE TABLE skewed_single (key string, value string)
SKEWED BY (key) ON ('1', '5', '6')
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

// 建立多欄位的傾斜表
CREATE TABLE skewed_multiple (key STRING, value STRING)
SKEWED BY (key, value) ON (('1','One'), ('3','Three'), ('5','Five'))
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

傾斜表建立後，會產生傾斜連線最佳化的效果。假設表 A id欄位有值（1,2,3,4），表 B id欄位有值（1,2,3），如果我們執行 join 操作：
select A.id from A join B on A.id = B.id;

此時會有一組 mappers 讀取兩個表並基於連線鍵 id 傳送到 reducers，如果我們在 id=1 上傾斜，那麼 Reducer2、Reducer3 會很快完成，Reducer1 會長時間執行，成為效能瓶頸。

如果能提前知道哪部分資料傾斜，可以分成兩個部分執行 join 操作：

--首先進行Join，排除傾斜資料A.id=1
select A.id from A join B on A.id = B.id where A.id <> 1;
--單獨對傾斜資料進行Join
select A.id from A join B on A.id = B.id where A.id = 1 and B.id = 1;

但是如果設定了傾斜表，會自動進行下面的最佳化：

• 將 B 表中 id=1 的資料載入到記憶體雜湊表中，然後分發到 A 表的所有 Mapper 任務中，直接與 A.id=1 的資料進行 join；
• 其它非傾斜資料，則執行普通的 Reduce 操作，並進行 join；

List Bucketing

List Bucketing 是特殊的傾斜表，它會將傾斜的資料儲存到單獨的目錄，這樣與其他資料分開儲存，提升查詢、join 效率。

// 建立傾斜表，並指定 List Bucketing
CREATE TABLE list_bucket_single (key STRING, value STRING)
partitioned by (dt string)
  SKEWED BY (key) ON (1,5,6) STORED AS DIRECTORIES
STORED AS ORCFile;

// 匯入資料
insert OVERWRITE table list_bucket_single
partition (dt='2020-11-19')
select * from skewed_single;

可以發現將傾斜欄位（1，5，6）和其他欄位分為不同目錄儲存。

傾斜表 DDL 操作

// 轉換傾斜表為普通表
ALTER TABLE <T> (SCHEMA) SKEWED BY  (keys) ON ('c1', 'c2') [STORED AS DIRECTORIES];

// 修改普通表為傾斜表
ALTER TABLE <T> (SCHEMA) NOT SKEWED;

// 轉換 ListBucketing 為普通傾斜表
ALTER TABLE <T> (SCHEMA) NOT STORED AS DIRECTORIES;

// 更改傾斜值的存放目錄
ALTER TABLE <T> (SCHEMA) SET SKEWED LOCATION (key1="loc1", key2="loc2");
--將list_bucket_multiple表的組合傾斜值('1','One')、('3','Three')存放目錄進行更改
ALTER TABLE list_bucket_multiple SET SKEWED LOCATION (('1','One')="hdfs://node01:9000/file/one_data" ,('3','Three')="hdfs://node01:9000/file/one_data");

事務表

hive 支援行級別的 ACID 語義，意味著可以同時向一個分割槽資料插入、查詢、刪除等操作。
事務功能僅支援 ORC 表儲存格式，而且依賴分桶的儲存格式，所以事務必須進行分桶操作。

// 建立事務表，分桶、開啟事務、指定儲存格式
CREATE TABLE employee (id int, name string, salary int)
CLUSTERED BY (id) INTO 2 BUCKETS
STORED AS ORC 
TBLPROPERTIES ('transactional' = 'true');

INSERT INTO employee VALUES
(1, 'Jerry', 5000),
(2, 'Tom',   8000),
(3, 'Kate',  6000);

檢視初始資料儲存狀態：

HDFS 上的事務表包含兩種型別檔案（base檔案、delta檔案）：

• base檔案：用來存放平常資料，會定期執行任務將 delta 檔案合併到 base 檔案；
• delta檔案：用來儲存新增、更新、刪除的資料，每一個事務處理都會單獨新建一個 delta 目錄用來儲存資料，目錄下儲存的資料按照桶劃分。

兩次事務操作後的表資料內容：