柚子快報(bào)邀請(qǐng)碼778899分享：hive3.1.2分區(qū)與排序

http://yzkb.51969.com/

1.Hive分區(qū)（很重要）

分區(qū)的目的：避免全局掃描，加快查詢速度！

思想：分而治之，把大的文件切割劃分成一個(gè)個(gè)小文件，然后操作一個(gè)個(gè)小文件。

1.2? 靜態(tài)分區(qū)（sp)

1.2.1 創(chuàng)建單分區(qū)表語(yǔ)法：

CREATE TABLE IF NOT EXISTS dongman (

id int,

name string

) partitioned by(grade int)

row format delimited fields terminated by ',';

-- 分區(qū)的字段不要和表的字段相同。相同會(huì)報(bào)錯(cuò)

1,shihao01,1 2,shihao02,1 3,shihao03,1 4,shihao04,1 5,shihao05,1 6,shihao06,2 7,shihao07,2 8,shihao08,2 9,shihao09,3 10,shihao10,3 11,shihao11,3 12,shihao12,4 13,shihao13,4

--載入數(shù)據(jù)，將相應(yīng)年紀(jì)一次導(dǎo)入

load data local inpath '/usr/local/soft/bigdata30/grade2.txt?' into? table dongman partition(grade=2);

1.2.2?靜態(tài)多分區(qū)表語(yǔ)法：

CREATE TABLE IF NOT EXISTS teacher (

tno int,

tname string

) partitioned by(grade int,clazz int)

row format delimited fields terminated by ',';

--注意：前后兩個(gè)分區(qū)的關(guān)系為父子關(guān)系，也就是grade文件夾下面有多個(gè)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

--載入數(shù)據(jù)

load data local inpath '/usr/local/soft/bigdata30/teacher1.txt' into table teacher partition(grade=1,clazz=1);

1.2.3? 分區(qū)表查詢

select * from students where grade = 1;

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

?// 使用where條件進(jìn)行分區(qū)裁剪，避免了全表掃描，效率高 select count(*) from teacher where grade = 1; ? // 也可以在where條件中使用非等值判斷 select count(*) from teacher where grade<3 and grade>=1;

查看分區(qū)

show partitions teacher;

添加分區(qū)

alter table dongman add partition (grade=5);

?

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

刪除分區(qū)

alter table t_student drop partition (grade=5);

1.3 動(dòng)態(tài)分區(qū)（DP）

靜態(tài)分區(qū)與動(dòng)態(tài)分區(qū)的主要區(qū)別在于靜態(tài)分區(qū)是手動(dòng)指定，而動(dòng)態(tài)分區(qū)是通過(guò)數(shù)據(jù)來(lái)進(jìn)行判斷。

靜態(tài)分區(qū)的列是在編譯時(shí)期通過(guò)用戶傳遞來(lái)決定的,動(dòng)態(tài)分區(qū)只有在SQL執(zhí)行時(shí)才能決定。

# 表示開啟動(dòng)態(tài)分區(qū)

hive> set hive.exec.dynamic.partition=true;

#設(shè)置為true表示開啟動(dòng)態(tài)分區(qū)的功能（默認(rèn)為false）

--hive.exec.dynamic.partition=true;

# 表示支持的最大的分區(qū)數(shù)量為1000，可以根據(jù)業(yè)務(wù)自己調(diào)整

hive> set hive.exec.max.dynamic.partitions.pernode=1000;

#全局可以創(chuàng)建的最大文件個(gè)數(shù)（默認(rèn)值100000）

--hive.exec.max.created.files=100000;

案例： 動(dòng)態(tài)插入學(xué)生年級(jí)班級(jí)信息

--創(chuàng)建外部表

CREATE EXTERNAL TABLE IF NOT EXISTS t_student_w (

sno int,

sname string,

grade int,

clazz int

)

row format delimited fields terminated by ','

location "/bigdata30/teachers";

--創(chuàng)建分區(qū)表

CREATE TABLE IF NOT EXISTS t_student_f (

sno int,

sname string

) partitioned by (grade int,clazz int)

row format delimited fields terminated by ',';

數(shù)據(jù):

1,lisi01,1,1

2,lisi02,1,1

3,lisi03,1,1

4,lisi04,1,2

5,lisi05,1,2

6,lisi06,2,2

7,lisi07,2,3

8,lisi08,2,3

9,lisi09,3,3

10,lisi10,3,3

11,lisi11,3,4

12,lisi12,4,4

13,lisi13,4,4

14,lisi14,4,5

15,lisi15,4,5

insert overwrite table t_student_f partition (grade,clazz) select * from t_student_w;

優(yōu)點(diǎn)：不用手動(dòng)指定了，自動(dòng)會(huì)對(duì)數(shù)據(jù)進(jìn)行分區(qū)

缺點(diǎn)：可能會(huì)出現(xiàn)數(shù)據(jù)傾斜

2、 Hive分桶

2.1? 數(shù)據(jù)分桶原理

Hive采用對(duì)列值哈希，然后除以桶的個(gè)數(shù)取余的方式?jīng)Q定該條記錄存放在哪個(gè)桶當(dāng)中。

2.2 數(shù)據(jù)分桶優(yōu)勢(shì)

方便抽樣，提高join查詢效率

# 分區(qū)和分桶的區(qū)別

1、在HDFS上的效果區(qū)別，分區(qū)產(chǎn)生的是一個(gè)一個(gè)子文件夾，分桶產(chǎn)生的是一個(gè)一個(gè)文件

?

2、無(wú)論是分區(qū)還是分桶，在建表的時(shí)候都要指定字段，分區(qū)使用partitioned by指定分區(qū)字段，分桶使用clustered by指定分桶字段

?

3、partitioned by指定分區(qū)字段的時(shí)候，字段后面需要加上類型，而且不能在建表小括號(hào)中出現(xiàn)。clustered by指定分桶字段的時(shí)候，字段已經(jīng)出現(xiàn)定義過(guò)了，只需要指定字段的名字

?

4、分區(qū)字段最好選擇固定類別的，分桶字段最好選擇值各不相同的。

?

5、分桶不是必須要建立在分區(qū)之上，可以不進(jìn)行分區(qū)直接分桶

2.3? 分桶實(shí)例

首先我們需要開啟分桶的支持

set hive.enforce.bucketing=true;? ?//依然十分重要，不然無(wú)法進(jìn)行分桶操作?。。?！

數(shù)據(jù)準(zhǔn)備（id,name,age）

1,tom,11

2,cat,22

3,dog,33

4,hive,44

5,hbase,55

6,mr,66

7,alice,77

8,scala,88

創(chuàng)建一個(gè)普通的表

create table person

(

id int,

name string,

age int

)

row format delimited

fields terminated by ',';?

將數(shù)據(jù)load到這張表中

load data local inpath '文件在Linux上的絕對(duì)路徑' into table person;

創(chuàng)建分桶表

create table psn_bucket

(

id string,

name string,

age int,

gender String,

clazz String

)

clustered by(age) into 4 buckets

row format delimited fields terminated by ',';

將數(shù)據(jù)insert到表psn_bucket中

(注意：這里和分區(qū)表插入數(shù)據(jù)有所區(qū)別，分區(qū)表需要select 和指定分區(qū)，而分桶則不需要)

insert into psn_bucket select * from person;

查詢數(shù)據(jù)

linux中使用Hadoop的命令查看

hadoop fs -cat /user/hive/warehouse/bigdata30.db/psn_bucket/*

3、 Hive查詢語(yǔ)法(DQL)

3.1? 全局排序

order by 會(huì)對(duì)輸入做全局排序，因此只有一個(gè)reducer，輸入規(guī)模較大時(shí)，需要較長(zhǎng)的計(jì)算時(shí)間 使用 order by子句排序 :ASC 升序（默認(rèn)）| DESC 降序 order by放在select語(yǔ)句的結(jié)尾

select * from 表名 order by 字段名1[，別名2...];

3.2 局部排序(對(duì)reduce內(nèi)部做排序)

sort by 不是全局排序,其在數(shù)據(jù)進(jìn)入reducer前完成排序。 如果用sort by進(jìn)行排序，并且設(shè)置mapred.reduce.tasks>1,則sort by 只保證每個(gè)reducer的輸出有序，不保證全局有序。 設(shè)置reduce個(gè)數(shù)

set mapreduce.job.reduces=3;

查看reduce個(gè)數(shù)

set mapreduce.job.reduces;

排序

select * from 表名 sort by 字段名[,字段名...];

3.3 分區(qū)排序(本身沒有排序)

distribute by（字段）根據(jù)指定的字段將數(shù)據(jù)分到不同的reducer，分發(fā)算法是hash散列。

類似MR中partition,進(jìn)行分區(qū)，結(jié)合sort by使用。（注意：distribute by 要在sort by之前）

設(shè)置reduce個(gè)數(shù)

set mapreduce.job.reduce=7;

排序

select * from 表名 distribute by 字段名[,字段名...] sort by 字段;

3.4?分區(qū)并排序

cluster by（字段）除了具有Distribute by的功能外，還會(huì)對(duì)該字段進(jìn)行排序 asc desc cluster by = distribute by + sort by 只能默認(rèn)升序，不能使用倒序

select * from 表名 cluster by 字段名[,字段名...];

select * from 表名 distribute by 字段名[,字段名...] sort by 字段名[,字段名...];

4、Hive內(nèi)置函數(shù)

-- 1.查看系統(tǒng)自帶函數(shù)

show functions;

-- 2.顯示自帶的函數(shù)的用法

desc function xxxx;

-- 3.詳細(xì)顯示自帶的函數(shù)的用法

desc function extended upper;

4.1 內(nèi)置函數(shù)分類

關(guān)系操作符：包括 = 、 <> 、 <= 、>=等

算數(shù)操作符：包括 + 、 - 、 *、／等

邏輯操作符：包括AND 、 && 、 OR 、 || 等

日期函數(shù)：包括from_unixtime(bigint unixtime[, string format])、unix_timestamp()等

條件函數(shù)：包括if(boolean testCondition, T valueTrue)等

字符串函數(shù)：包括acat(string|binary A, string|binary B…)等

4.2? UDTF Hive中特殊的一個(gè)功能

-- UDF 進(jìn)一出一

-- UDAF 進(jìn)多出一

-- collect_set()和collect_list()都是對(duì)多列轉(zhuǎn)成一行，區(qū)別就是list可重復(fù),而set里面是去重的

-- concat_ws(':',collect_set(type)) ':' 表示合并后用什么分隔，collect_set(stage)表示要合并表中的那一列數(shù)據(jù)

select 字段名,concat_ws(':',collect_set(列名)) as 別名 from 表名 group by id;

-- UDTF 進(jìn)一出多

-- explode 可以將一組數(shù)組的數(shù)據(jù)變成一列表

select explode(split(列名,"數(shù)據(jù)的分隔符")) from 表名;

-- lateral view 表生成函數(shù)，可以將explode的數(shù)據(jù)生成一個(gè)列表

select id,name,列名 from 表1,lateral view explode(split(表1.列名,"數(shù)據(jù)的分隔符"))新列名 as 別列名;

-- 創(chuàng)建數(shù)據(jù)庫(kù)表

create table t_movie1(

id int,

name string,

types string

)

row format delimited fields terminated by ','

lines terminated by '\n';

-- 電影數(shù)據(jù) movie1.txt

-- 加載數(shù)據(jù)到數(shù)據(jù)庫(kù) load data local inpath '/usr/local/soft/bigdata30/movie1.txt' into table t_movie1;

1,這個(gè)殺手不太冷靜,劇情-動(dòng)作-犯罪

2,七武海,動(dòng)作-冒險(xiǎn)-劇情

3,打狗棍,動(dòng)作-傳記-劇情-歷史-戰(zhàn)爭(zhēng)

4,瑯琊榜,劇情-動(dòng)作-愛情-武俠-古裝

5,霍比特人,動(dòng)作-奇幻-冒險(xiǎn)

-- lateral view 表生成函數(shù)，可以將explode的數(shù)據(jù)生成一個(gè)列表

select id,name,type from t_movie1 lateral view explode(split(types,"-")) typetable as type;

-- explode 可以將一組數(shù)組的數(shù)據(jù)變成一列表

select explode(split(types,"-")) from t_movie1;

柚子快報(bào)邀請(qǐng)碼778899分享：hive3.1.2分區(qū)與排序

http://yzkb.51969.com/

推薦文章