資料倉儲建模工具之一——Hive學習第五天

shmil發表於2024-07-19
Hive

Hive的分割槽

1、Hive分割槽(十分重要！！)

分割槽的目的：避免全表掃描，加快查詢速度！

在大資料中，最常見的一種思想就是分治，我們可以把大的檔案切割劃分成一個個的小的檔案，這樣每次操作一個個小的檔案就會很容易了，同樣的道理，在hive當中也是支援這種思想的，就是我們可以把大的資料，按照每天或者每小時切分成一個個小的檔案，這樣去操作小的檔案就會容易很多了。

1.2 靜態分割槽（SP）

靜態分割槽（SP）static partition–partition by (欄位 型別)

​ 藉助於物理的資料夾分割槽，實現快速檢索的目的。

​ 一般對於查詢比較頻繁的列設定為分割槽列。

​ 分割槽查詢的時候直接把對應分割槽中所有資料放到對應的資料夾中。

建立單分割槽表語法：

CREATE TABLE IF NOT EXISTS t_student (
sno int,
sname string
) partitioned by(grade int)
row format delimited fields terminated by ',';
--  分割槽的欄位不要和表的欄位相同。相同會報錯error10035


1,zhangsan01,1
2,zhangsan02,1
3,zhangsan03,1
4,zhangsan04,1
5,zhangsan05,1

6,zhangsan06,2
7,zhangsan07,2
8,zhangsan08,2

9,zhangsan09,3
10,zhangsan10,3
11,zhangsan11,3
12,zhangsan12,3
13,zhangsan13,3
14,zhangsan14,3
15,zhangsan15,3

16,zhangsan16,4
17,zhangsan17,4
18,zhangsan18,4
19,zhangsan19,4
20,zhangsan20,4
21,zhangsan21,4
-- 載入資料
-- 將相應年級一次匯入
load data local inpath '/usr/local/soft/hive_test/grade2.txt' into table t_student partition(grade=2);
`

> 靜態多分割槽表語法：

```sql
CREATE TABLE IF NOT EXISTS t_teacher (
tno int,
tname string
) partitioned by(grade int,clazz int)
row format delimited fields terminated by ',';

--注意：前後兩個分割槽的關係為父子關係，也就是grade資料夾下面有多個clazz子資料夾。
1,zhangsan01,1,1
2,zhangsan02,1,1

3,zhangsan03,1,2
4,zhangsan04,1,2

5,zhangsan05,1,3
6,zhangsan06,1,3

7,zhangsan07,2,1
8,zhangsan08,2,1

9,zhangsan09,2,2

--載入資料
load data local inpath '/usr/local/soft/hive_test/hivedata/teacher_11.txt' into table t_teacher partition(grade=1,clazz=1);

分割槽表查詢

select * from t_student where grade = 1;

// 全表掃描，不推薦，效率低
select count(*) from students_pt1;

// 使用where條件進行分割槽裁剪，避免了全表掃描，效率高
select count(*) from students_pt1 where grade = 1;

// 也可以在where條件中使用非等值判斷
select count(*) from students_pt1 where grade<3 and grade>=1;

檢視分割槽

show partitions t_teacher;
  • DDL

新增分割槽

alter table t_student add partition (grade=6);

alter table t_teacher add partition (grade=3,clazz=1) location '/user/hive/warehouse/hive_test.db/t_teacher/grade=3/clazz=1';

刪除分割槽

alter table t_student drop partition (grade=5);

1.3 動態分割槽（DP）

  • 動態分割槽（DP）dynamic partition
  • 靜態分割槽與動態分割槽的主要區別在於靜態分割槽是手動指定，而動態分割槽是透過資料來進行判斷。
  • 詳細來說，靜態分割槽的列是在編譯時期透過使用者傳遞來決定的；動態分割槽只有在SQL執行時才能決定。

開啟動態分割槽首先要在hive會話中設定如下的引數

# 表示開啟動態分割槽
hive> set hive.exec.dynamic.partition=true;

# 表示動態分割槽模式：strict（需要配合靜態分割槽一起使用）、nostrict
# strict： insert into table students_pt partition(dt='anhui',pt) select ......,pt from students;
hive> set hive.exec.dynamic.partition.mode=nonstrict;

===================以下是可選引數======================

# 表示支援的最大的分割槽數量為1000，可以根據業務自己調整
hive> set hive.exec.max.dynamic.partitions.pernode=1000;

其餘的引數詳細配置如下

設定為true表示開啟動態分割槽的功能（預設為false）
--hive.exec.dynamic.partition=true;

設定為nonstrict，表示允許所有分割槽都是動態的（預設為strict）
-- hive.exec.dynamic.partition.mode=nonstrict; 
-- hive.exec.dynamic.partition.mode=strict; 

每個mapper或reducer可以建立的最大動態分割槽個數(預設為100) 
比如：源資料中包含了一年的資料，即day欄位有365個值，那麼該引數就需要設定成大於365，如果使用預設值100，則會報錯
--hive.exec.max.dynamic.partition.pernode=100; 

一個動態分割槽建立可以建立的最大動態分割槽個數（預設值1000）
--hive.exec.max.dynamic.partitions=1000;

全域性可以建立的最大檔案個數（預設值100000）
--hive.exec.max.created.files=100000; 

當有空分割槽產生時，是否丟擲異常（預設false） 
-- hive.error.on.empty.partition=false;
  • 案例1： 動態插入學生年級班級資訊
--建立外部表
CREATE EXTERNAL TABLE IF NOT EXISTS t_teacher_e (
sno int,
sname string,
grade int,
clazz int
) 
row format delimited fields terminated by ','
location "/hive_test/teachers";

--建立分割槽表
CREATE TABLE IF NOT EXISTS t_teacher_d (
sno int,
sname string
) partitioned by (grade int,clazz int)
row format delimited fields terminated by ',';



資料：

1,zhangsan01,1,1
2,zhangsan02,1,1
3,zhangsan03,1,1
4,zhangsan04,1,2
5,zhangsan05,1,2
6,zhangsan06,2,3
7,zhangsan07,2,3
8,zhangsan08,2,3
9,zhangsan09,3,3
10,zhangsan10,3,3
11,zhangsan11,3,3
12,zhangsan12,3,4
13,zhangsan13,3,4
14,zhangsan14,3,4
15,zhangsan15,3,4
16,zhangsan16,4,4
17,zhangsan17,4,4
18,zhangsan18,4,5
19,zhangsan19,4,5
20,zhangsan20,4,5
21,zhangsan21,4,5

如果靜態分割槽的話，我們插入資料必須指定分割槽的值。

如果想要插入多個班級的資料，我要寫很多SQL並且執行24次很麻煩。

而且靜態分割槽有可能會產生資料錯誤問題

-- 會報錯 
insert overwrite table t_student_d partition (grade=1,clazz=1) select * from t_student_e where grade=1;

如果使用動態分割槽，動態分割槽會根據select的結果自動判斷資料應該load到哪兒分割槽去。

insert overwrite table t_student_d partition (grade,clazz) select * from t_student_e;

優點：不用手動指定了，自動會對資料進行分割槽

缺點：可能會出現資料傾斜

相關文章