Hive之學習之路

1、自定義函數UDF

UDF 一進一出(行) 改變大小寫、增加字元串之類的操作

UDAF 聚集函數(行) 多進一出 count max min sum

UDTF 一進多出(行) lateral view explore() 例如：explode

2、大資料解決什麼事情

傳輸 存儲 計算

3、hive定義

解決海量結構化日志的資料統計，結構化資料檔案映射為一張表，是一個資料倉庫工具，分析資料底層預設實作是MapReduce

4、hive的缺點

HQL表達能力有限

（1）疊代式算法無法表達

（2）資料挖掘不擅長（一般是疊代，挖掘兩個資料的關系）

5、hive的架構裡有哪些元件

中繼資料（meta store）：hive表與hdfs上檔案的映射關系，預設derby資料庫，正常時mysql存

解析器-編譯器-優化器-執行器

6、hive沒有索引，查詢資料，需要掃描整個表，延遲較高

7、顯示資料庫詳細資訊，extended 例子desc database extended db_hive;

8、如果資料庫不為空，可以采用 cascade 指令，強制删除

建表：

create table log_orc_snappy(

track_time string,

url string,

session_id string,

referer string,

ip string,

end_user_id string,

city_id string )

row format delimited fields terminated by ‘\t’

stored as orc tblproperties (“orc.compress”=“SNAPPY”);

壓縮模式是snappy的建表語句

查詢結構：

select

from

join in

where

group by

order by

having

limit

順序是：先from->join in->where->group by->select->having->order by->limit

select之後的才能用别名

SELECT count(A1) c,B1 from a JOIN b on a.A2=b.B2 GROUP BY B1 HAVING c>1能執行

是以，having排在select的後面，因為hiving能識别select後的别名，說明select運作過了

是以就涉及到一些優化的問題：

select

id,

name

from A

join B on A.id=B.id

where A.id=11;

可以優化成

select

from

(select id ,name from A where A.id=11) r1

join B

on r1.id =B.id

提前縮小資料集，因為join先執行，是以執行的資料集就比較大，那如果先把where執行完，再執行join，資料集就會小很多。不過where條件一定是原表有的才能用這個

對某個查詢結果再做加工的要用子查詢

例子：

select

count() ct

ct+1

明顯是錯誤的，因為count() ct和ct+1是平級的，count要查詢全表，才能出來，是以就找不到ct，更别說+1，是以對于某一個結果再進行加工的時候要用子函數

select

count(*) ct

from

A; t1

select

ct,

ct+1

from

t1

select

ct,

ct+1

from

(select

count(*) ct

from

A)

10、建表時候的語句：STORED AS file_format

COMMENT 為表和列添加注釋

CLUSTERED BY 分桶

PARTITIONED BY 分區

LOCATION 改變存儲路徑

hive中 full join 保留所有資料

11、show create table cc; 檢視建表語句

12、LIKE 允許使用者複制現有的表結構，但是不複制資料。

13、desc formatted dept;查詢表是外部表EXTERNAL_TABLE還是内部表 MANAGED_TABLE

14、修改内部表 student2 為外部表

alter table student2 set tblproperties(‘EXTERNAL’=‘TRUE’); 固定寫法，區分大小寫

修改外部表 student2 為内部表

alter table student2 set tblproperties(‘EXTERNAL’=‘FALSE’);

15、增加分區

建立單個分區：hive (default)> alter table dept_partition add partition(month=‘201706’) ;

同時建立多個分區：alter table dept_partition add partition(month=‘201705’) partition(month=‘201704’);

檢視分區表有多少分區： show partitions dept_partition;

删除單個分區：alter table dept_partition drop partition

(month=‘201704’);

檢視分區表結構：desc formatted dept_partition;

執行修複指令 msck repair table a;

16、重命名表：ALTER TABLE table_name RENAME TO new_table_name

增加列：alter table dept_partition add columns(deptdesc string);

更新列：alter table dept_partition change column a(之前列名) b(改後列名) int(對應格式 );

替換列：alter table dept_partition replace columns(a string, bstring, c string);直接把目标表中所有列都替換掉了

DML 資料操作

1、根據查詢結果建立表

create table if not exists student3 as select id, name from student;

2、清除表中資料 truncate table a;

3、select官方文檔 https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select

4、常用函數 sum count max min avg平均數

5、A<=>B 和A=B差別在于如果 A 和 B 都為 NULL，則傳回 TRUE，也就是說<=>可以比對null，=不可以

6、IN[數值 1, 數值 2] 兩邊是閉區間

7、like查詢 % 代表零個或多個字元(任意個字元)。_ 代表一個字元。relike 後邊可以使用java正則

例子：查找以 2 開頭薪水的員工資訊 select * from emp where sal LIKE ‘2%’;

查找第二個數值為 2 的薪水的員工資訊select * from emp where sal LIKE ‘_2%’;

查找薪水中含有 2 的員工資訊 select * from emp where sal RLIKE ‘[2]’;

8、where 後面不能寫聚合函數，而 having 後面可以使用聚合函數。having 隻用于 group by 分組統計語句。where 針對表中的列發揮作用，查詢資料；having 針對查詢結果中的列發揮作用，

篩選資料

例子： 求每個部門的平均薪水大于 2000 的部門

select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;

9、在hive中，join文法隻支援等值連接配接，不支援非等值連接配接，和mysql不同

10、left join 指的是：首先取出a表中所有資料,然後再加上與a,b比對的的資料

11、連接配接操作，Hive 總是按照從左到右的順序執行的

12、沒有連接配接條件就會生成笛卡兒積，兩個表所有行對乘

排序

1、全局排序（Order By）隻有一個reducer

ASC（ascend）: 升序（預設）

DESC（descend）: 降序

2、每個 MapReduce 内部排序（Sort By）

設定 reduce 個數： set mapreduce.job.reduces=3; 再sort by 就生效了

檢視設定 reduce 個數：set mapreduce.job.reduces;

————但是如果隻用sort by 分的區是随機分區，是以加入Distribute By

注意：！！Hive 要求 DISTRIBUTE BY 語句要寫在 SORT BY 語句之前。

————如果當 distribute by 和 sorts by 字段相同時，可以使用 cluster by。cluster by 除了具有 distribute by 的功能外還兼具 sort by 的功能。但是排序隻能是升序排序，不能指定排序規則為 ASC 或者 DESC。

分桶

1、分區不是按照建表字段，分桶需要按照建表字段來分桶

建立分桶表時，資料通過子查詢的方式導入。直接用load導入，相當于在hdfs上使用put，改變的是中繼資料資訊，一個檔案不可能分成多個。

但是同過子查詢方式導入也需要先設定屬性：

set hive.enforce.bucketing=true; 開啟分桶功能

set mapreduce.job.reduces=-1;将reduce個數設定為-1，意思就是自己會根據桶的個數來決定reduce的個數，這也是預設值

2、分桶抽樣查詢

查詢表 stu_buck 中的資料

select * from stu_buck tablesample(bucket 1 out of 4 on id);

tablesample 是抽樣語句，文法：TABLESAMPLE(BUCKET x OUT OF y) 。

x是從哪個桶開始抽，y 必須是 table 總 bucket 數的倍數或者因子，y是抽取總數 bucket/y 桶的資料，如果抽的超過一個分區，例如一共12個桶，x=1,y=3 ，就是從桶1開始抽，抽4個桶，分别是1，4，7，10這4個桶，每次都+y

x值必須小于y值

常用函數

1、空字段指派

select nvl(comm,-1) from emp; 如果員工的 comm 為 NULL，則用-1 代替

如果員工的 comm 為 NULL，則用上司 id 代替 select nvl(comm,mgr) from emp;另外一列來代替

2、時間類

date_format:格式化時間 date_format(‘2019-06-29’,‘yyyy-MM-dd’) 後面是你想格式化後的格式

date_add:時間跟天數相加； date_sub:時間跟天數相減，可以傳負數，兩個記一個就行，select date_add(‘2019-06-29’,5);可以自動跨月，前面得是字元串

datediff:兩個時間相減 ：datediff(‘2019-06-29’,‘2019-06-24’);

select datediff(‘2019-06-24 12:12:12’,‘2019-06-29’);

OK

_c0

-5

因為2019/2/12 時間函數無法解析，是以用如下函數

regexp_replace(對哪個字段進行切換,你要替換的什麼東西,換成什麼)

例子：regexp_replace(‘2019/2/12’,’/’,’-’)

3、case when

列轉行就要用到case when

dept_id,

sum(case sex when ‘男’ then 1 else 0 end) male_count,

sum(case sex when ‘女’ then 1 else 0 end) female_count

from

emp_sex

group by

dept_id;

如果隻有兩個分支也可以用if來代替

dept_id,

if(sex=‘男’,1,0) male_count,

if(sex=‘女’,1,0) male_count if的結構：if（布爾值，true的結果，false的結果）

from

emp_sex

group by

dept_id;

4、CONCAT

CONCAT_WS(separator, str1, str2,…)：如果有多個同樣的分隔符，可以用這個！！必須是String

例子： concat_ws(’-’.‘as’,‘df’)

***COLLECT_SET(列)***：因為set函數沒有重複的值，是以這個函數有去重的作用，會生成一個array數組 聚合函數，列轉行

可以混合使用：concat_ws(’-’.COLLECT_SET(列)) 把一列資料形成一個-分割的字元串

5、EXPLODE(col)：将 hive 一列中複雜的 array 或者 map 結構拆分成多行。explode(炸裂的意思)

如果直接用select explode(a）from b; 可以全部炸開

如果select f,explode(a) from b 報錯，文法不能這樣使用

這時就需要LATERAL VIEW （側寫）跟原來表關聯上

用法：

select

movie,

category_name

from

movie_info lateral view explode(category) table_tmp（表别名） as category_name（列别名）;

substring(orderdate,1,7)截取字元串從1到7個字元

視窗函數

當一個聚合和一個多行的在一起需要用到視窗函數

開窗：

一、對開窗的了解

開窗等于開一部分資料集出來

1、開窗函數必須在聚合函數後邊

2、開窗函數開出的資料集，隻給聚合函數使用

3、over()裡面沒有參數代表對整個資料集進行開窗

4、開窗是針對于每一條資料來開窗

我的了解是：over() 是用來標明聚合函數所使用的資料集的

例子：

（1）select name

from business

where substring(orderdate,1,7) = ‘2017-04’

小三

小三

小四

小屋

小氣

（2）select name，count() over()

from business

where substring(orderdate,1,7) = ‘2017-04’

小三 5

小三 5

小四 5

小屋 5

小氣 5

為什麼呢，首先在（2）中先做了一些（1）中的查詢名字的操作，形成了一個名字的資料集，也就是這5個人(不一定都是不同的人)。然後再做count()操作，針對于哪一部分進行count呢，over()裡面沒有參數，就是針對所有資料集做count(*)，那所有資料集是什麼，就是這5個人。

如果不加over()，那麼有聚合函數必須加group by ，那麼就會按照name分組，那麼結果就應該是

小三 2

小四 1

小屋 1

小氣 1

二、over()的那些參數

1、over(order by a) 對每個資料開窗的時候，加上了比目前資料小的資料，一起作為一個開窗資料集

例子：有一列資料

shuzi

4

5

6

7

對4開窗的時候，資料集就是4

對5 開窗的時候資料集是

4

5

對6開窗是

4

5

6以此類推

是以用sum函數的時候，結果集就是

4

9（4+5）

15（4+5+6）

22（4+5+6+7）

2、over(distrubute by a) 類似于group by 針對a 分組分資料集，也就是說小米的資料集就是3個小米

比如如下資料

a ——b

小米 30

小米 20

小米 10

小頂 40

小皮 50

sum(b)over(distrubute by a)

小米 60

小米 60

小米 60

小頂 40

小皮 50

那麼同理

如下資料

a ——b——c

小米 30 3

小米 20 2

小米 10 1

小頂 40 4

小皮 50 6

3、sum(b)over(distrubute by a sort by c) 就是先把資料集限定在小米裡，然後對小米對應的c排序累加，原理和order by 那個一樣 ，隻不過order by 那個是全局累加，這個是在分區的資料集裡累加

那麼結果是：

小米 10 1

小米 30 2

小米 60 3

小頂 40 4

小皮 50 6

4、over()裡面還有其他函數，都是劃分資料集的（不太常用）

CURRENT ROW：目前行；

n PRECEDING：往前 n 行資料；

n FOLLOWING：往後 n 行資料；

UNBOUNDED：起點，UNBOUNDED PRECEDING 表示從前面的起點，UNBOUNDED

FOLLOWING 表示到後面的終點；

例子：

select

xx,

sum(ss)over(rows between 2 preceding and current row) 目前行到前面2行的資料加和

from a;

5、over()跟在後面的（必須有over才能用）

（1）LAG(col,n)：往前第 n 行資料； 隻指一條資料 col哪一列 還可以在n後邊在加個參數

如果第一個資料的前面是沒有資料的，就把這個參數就是填在第一行 LAG(col,n,p)

例子：select name,orderdate,cost,

lag(orderdate,1,‘1900-01-01’) over(partition by name order by

orderdate ) as time1, 就是所有orderdate位置的資料寫的都是前1天

lag(orderdate,2) over (partition by name

order by orderdate) as time2

from business;

就是所有orderdate位置的資料寫的都是前2天

（2）LEAD(col,n)：往後第 n 行資料；——同理

partition by…order by=distrubute by … sort by

（3）NTILE(n)：把有序分區中的行分發到指定資料的組中，各個組有編号，編号從 1 開始，

對于每一行，NTILE 傳回此行所屬的組的編号。注意：n 必須為 int 類型。

例子：

select * from (

select name,orderdate,cost, ntile(5) over(order by orderdate)

sorted

from business

) t

where sorted = 1;

ntile(5) 就是把一個資料集分成5份，order by orderdate是哪一部分資料集呢，按順序的時間分成5份

也就是前20%的時間

6、rank 函數

RANK() 排序就是 1 1 3 4 排序相同時會重複，總數不會變

DENSE_RANK() 排序就是 1 1 2 3 排序相同時會重複，總數會減少 dense密集的意思

ROW_NUMBER() 排序就是 1 2 3 4 會根據順序計算

思考：group by 用不了的情況才用over，一般是聚合函數，有相同人，需要按每個人來聚合後面的數，就不能用group by ,group by 就把相同的姓名的都合在一起了，但是要每個人單獨顯示就得用over了

實際用到over的案例，了解一下

（1）查詢在 2017 年 4 月份購買過的顧客及總人數

（2）查詢顧客的購買明細及購買總額

（3）上述的場景,要将 cost 按照日期進行累加

（4）查詢顧客上次的購買時間

（5）查詢前 20%時間的訂單資訊

就是要展示明細的同時還要聚合

group by 有時候等同于 over(partition by ) over在group by後進行

distinct去重是針對于全表的，用一個reducer處理，資料量大的時候不适合使用