2． explain dependency的用法

explain dependency用于描述一段SQL需要的數(shù)據(jù)來源,輸出是一個(gè)json格式的數(shù)據(jù),里面包含以下兩個(gè)部分的內(nèi)容:

input_partitions:描述一段SQL依賴的數(shù)據(jù)來源表分區(qū),里面存儲(chǔ)的是分區(qū)名的列表,如果整段SQL包含的所有表都是非分區(qū)表,則顯示為空。

input_tables:描述一段SQL依賴的數(shù)據(jù)來源表,里面存儲(chǔ)的是Hive表名的列表。

使用explain dependency查看SQL查詢非分區(qū)普通表,在 hive cli 中輸入以下命令:

explain dependency select s_age,count(1) num from student_orc;

得到結(jié)果:

{"input_partitions":[],"input_tables":[{"tablename":"default@student_tb _orc","tabletype":"MANAGED_TABLE"}]}

使用explain dependency查看SQL查詢分區(qū)表,在 hive cli 中輸入以下命令:

explain dependency select s_age,count(1) num from student_orc_partition;

得到結(jié)果:

{"input_partitions":[{"partitionName":"default@student_orc_partition@ part=0"},
{"partitionName":"default@student_orc_partition@part=1"},
{"partitionName":"default@student_orc_partition@part=2"},
{"partitionName":"default@student_orc_partition@part=3"},
{"partitionName":"default@student_orc_partition@part=4"},
{"partitionName":"default@student_orc_partition@part=5"},
{"partitionName":"default@student_orc_partition@part=6"},
{"partitionName":"default@student_orc_partition@part=7"},
{"partitionName":"default@student_orc_partition@part=8"},
{"partitionName":"default@student_orc_partition@part=9"}],
"input_tables":[{"tablename":"default@student_orc_partition", "tabletype":"MANAGED_TABLE"}]

explain dependency的使用場(chǎng)景有兩個(gè):

場(chǎng)景一:快速排除�？焖倥懦�因?yàn)樽x取不到相應(yīng)分區(qū)的數(shù)據(jù)而導(dǎo)致任務(wù)數(shù)據(jù)輸出異常。例如,在一個(gè)以天分區(qū)的任務(wù)中,上游任務(wù)因?yàn)樯�產(chǎn)過程不可控因素出現(xiàn)異�；蛘呖张�,導(dǎo)致下游任務(wù)引發(fā)異常。通過這種方式,可以快速查看SQL讀取的分區(qū)是否出現(xiàn)異常。

場(chǎng)景二:理清表的輸入,幫助理解程序的運(yùn)行,特別是有助于理解有多重子查詢,多表連接的依賴輸入。

下面通過兩個(gè)案例來看explain dependency的實(shí)際運(yùn)用:

案例一:識(shí)別看似等價(jià)的代碼

對(duì)于剛接觸SQL的程序員,很容易將

select * from a inner join b on a．no=b．no and a．f>1 and a．f<3;

等價(jià)于

select * from a inner join b on a．no=b．no where a．f>1 and a．f<3;

我們可以通過案例來查看下它們的區(qū)別:

代碼1:

select
a．s_no
from student_orc_partition a
inner join
student_orc_partition_only b
on a．s_no=b．s_no and a．part=b．part and a．part>=1 and a．part<=2;

代碼2:

select
a．s_no
from student_orc_partition a
inner join
student_orc_partition_only b
on a．s_no=b．s_no and a．part=b．part
where a．part>=1 and a．part<=2;

我們看下上述兩段代碼explain dependency的輸出結(jié)果:

代碼1的explain dependency結(jié)果:

{"input_partitions":
[{"partitionName":"default@student_orc_partition@part=0"},
{"partitionName":"default@student_orc_partition@part=1"},
{"partitionName":"default@student_orc_partition@part=2"},
{"partitionName":"default@student_orc_partition_only@part=1"},
{"partitionName":"default@student_orc_partition_only@part=2"}],
"input_tables": [{"tablename":"default@student_orc_partition","tabletype":"MANAGED_TABLE"}, {"tablename":"default@student_orc_partition_only","tabletype":"MANAGED_TABLE"}]}

代碼2的explain dependency結(jié)果:

{"input_partitions":
[{"partitionName":"default@student_orc_partition@part=1"},
{"partitionName" : "default@student_orc_partition@part=2"},
{"partitionName" :"default@student_orc_partition_only@part=1"},
{"partitionName":"default@student_orc_partition_only@part=2"}],
"input_tables": [{"tablename":"default@student_orc_partition","tabletype":"MANAGED_TABLE"}, {"tablename":"default@student_orc_partition_only","tabletype":"MANAGED_TABLE"}]}

通過上面的輸出結(jié)果可以看到,其實(shí)上述的兩個(gè)SQL并不等價(jià),代碼1在內(nèi)連接(inner join)中的連接條件(on)中加入非等值的過濾條件后,并沒有將內(nèi)連接的左右兩個(gè)表按照過濾條件進(jìn)行過濾,內(nèi)連接在執(zhí)行時(shí)會(huì)多讀取part=0的分區(qū)數(shù)據(jù)。而在代碼2中,會(huì)過濾掉不符合條件的分區(qū)。

案例二:識(shí)別SQL讀取數(shù)據(jù)范圍的差別

代碼1:

explain dependency
select
a．s_no
from student_orc_partition a
left join
student_orc_partition_only b
on a．s_no=b．s_no and a．part=b．part and b．part>=1 and b．part<=2;

代碼2:

explain dependency
select
a．s_no
from student_orc_partition a
left join
student_orc_partition_only b
on a．s_no=b．s_no and a．part=b．part and a．part>=1 and a．part<=2;

以上兩個(gè)代碼的數(shù)據(jù)讀取范圍是一樣的嗎?答案是不一樣,我們通過explain dependency來看下:

代碼1的explain dependency結(jié)果:

{"input_partitions":
[{"partitionName": "default@student_orc_partition@part=0"},
{"partitionName":"default@student_orc_partition@part=1"}, …中間省略7個(gè)分區(qū)
{"partitionName":"default@student_orc_partition@part=9"},
{"partitionName":"default@student_orc_partition_only@part=1"},
{"partitionName":"default@student_orc_partition_only@part=2"}],
"input_tables": [{"tablename":"default@student_orc_partition","tabletype":"MANAGED_TABLE"}, {"tablename":"default@student_orc_partition_only","tabletype":"MANAGED_TABLE"}]}

代碼2的explain dependency結(jié)果:

{"input_partitions":
[{"partitionName":"default@student_orc_partition@part=0"},
{"partitionName":"default@student_orc_partition@part=1"}, …中間省略7個(gè)分區(qū)
{"partitionName":"default@student_orc_partition@part=9"},
{"partitionName":"default@student_orc_partition_only@part=0"},
{"partitionName":"default@student_orc_partition_only@part=1"}, …中間省略7個(gè)分區(qū)
{"partitionName":"default@student_orc_partition_only@part=9"}],
"input_tables": [{"tablename":"default@student_orc_partition","tabletype":"MANAGED_TABLE"}, {"tablename":"default@student_orc_partition_only","tabletype":"MANAGED_TABLE"}]}

可以看到,對(duì)左外連接在連接條件中加入非等值過濾的條件,如果過濾條件是作用于右表(b表)有起到過濾的效果,則右表只要掃描兩個(gè)分區(qū)即可,但是左表(a表)會(huì)進(jìn)行全表掃描。如果過濾條件是針對(duì)左表,則完全沒有起到過濾的作用,那么兩個(gè)表將進(jìn)行全表掃描。這時(shí)的情況就如同全外連接一樣都需要對(duì)兩個(gè)數(shù)據(jù)進(jìn)行全表掃描。

在使用過程中,容易認(rèn)為代碼片段2可以像代碼片段1一樣進(jìn)行數(shù)據(jù)過濾,通過查看explain dependency的輸出結(jié)果,可以知道不是如此。

3． explain authorization 的用法

通過explain authorization可以知道當(dāng)前SQL訪問的數(shù)據(jù)來源(INPUTS) 和數(shù)據(jù)輸出(OUTPUTS),以及當(dāng)前Hive的訪問用戶 (CURRENT_USER)和操作(OPERATION)。

在 hive cli 中輸入以下命令:

explain authorization
select variance(s_score) from student_tb_orc;

結(jié)果如下:

INPUTS:
 default@student_tb_orc
OUTPUTS:
 hdfs://node01:8020/tmp/hive/hdfs/cbf182a5-8258-4157-9194- 90f1475a3ed5/-mr-10000
CURRENT_USER:
 hdfs
OPERATION:
 QUERY
AUTHORIZATION_FAILURES:
 No privilege 'Select' found for inputs { database:default, table:student_ tb_orc, columnName:s_score}

從上面的信息可知:

上面案例的數(shù)據(jù)來源是defalut數(shù)據(jù)庫中的 student_tb_orc表;

數(shù)據(jù)的輸出路徑是hdfs://node01:8020/tmp/hive/hdfs/cbf182a5-8258-4157-9194-90f1475a3ed5/-mr-10000;

當(dāng)前的操作用戶是hdfs,操作是查詢;

觀察上面的信息我們還會(huì)看到AUTHORIZATION_FAILURES信息,提示對(duì)當(dāng)前的輸入沒有查詢權(quán)限,但如果運(yùn)行上面的SQL的話也能夠正常運(yùn)行。為什么會(huì)出現(xiàn)這種情況?Hive在默認(rèn)不配置權(quán)限管理的情況下不進(jìn)行權(quán)限驗(yàn)證,所有的用戶在Hive里面都是超級(jí)管理員,即使不對(duì)特定的用戶進(jìn)行賦權(quán),也能夠正常查詢。

最后

通過上面對(duì)explain的介紹,可以發(fā)現(xiàn)explain中有很多值得我們?nèi)パ芯康膬?nèi)容,讀懂 explain 的執(zhí)行計(jì)劃有利于我們優(yōu)化Hive SQL,同時(shí)也能提升我們對(duì)SQL的掌控力。

八、Hive SQL底層執(zhí)行原理

本節(jié)結(jié)構(gòu)采用宏觀著眼,微觀入手,從整體到細(xì)節(jié)的方式剖析 Hive SQL 底層原理。第一節(jié)先介紹 Hive 底層的整體執(zhí)行流程,然后第二節(jié)介紹執(zhí)行流程中的 SQL 編譯成 MapReduce 的過程,第三節(jié)剖析 SQL 編譯成 MapReduce 的具體實(shí)現(xiàn)原理。

Hive 底層執(zhí)行架構(gòu)

我們先來看下 Hive 的底層執(zhí)行架構(gòu)圖, Hive 的主要組件與 Hadoop 交互的過程:

Hive底層執(zhí)行架構(gòu)

在 Hive 這一側(cè),總共有五個(gè)組件:

UI:用戶界面�？煽醋魑覀兲峤籗QL語句的命令行界面。

DRIVER:驅(qū)動(dòng)程序。接收查詢的組件。該組件實(shí)現(xiàn)了會(huì)話句柄的概念。

COMPILER:編譯器。負(fù)責(zé)將 SQL 轉(zhuǎn)化為平臺(tái)可執(zhí)行的執(zhí)行計(jì)劃。對(duì)不同的查詢塊和查詢表達(dá)式進(jìn)行語義分析,并最終借助表和從 metastore 查找的分區(qū)元數(shù)據(jù)來生成執(zhí)行計(jì)劃。

METASTORE:元數(shù)據(jù)庫。存儲(chǔ) Hive 中各種表和分區(qū)的所有結(jié)構(gòu)信息。

EXECUTION ENGINE:執(zhí)行引擎。負(fù)責(zé)提交 COMPILER 階段編譯好的執(zhí)行計(jì)劃到不同的平臺(tái)上。

上圖的基本流程是:

步驟1:UI 調(diào)用 DRIVER 的接口;

步驟2:DRIVER 為查詢創(chuàng)建會(huì)話句柄,并將查詢發(fā)送到 COMPILER(編譯器)生成執(zhí)行計(jì)劃;

步驟3和4:編譯器從元數(shù)據(jù)存儲(chǔ)中獲取本次查詢所需要的元數(shù)據(jù),該元數(shù)據(jù)用于對(duì)查詢樹中的表達(dá)式進(jìn)行類型檢查,以及基于查詢謂詞修建分區(qū);

步驟5:編譯器生成的計(jì)劃是分階段的DAG,每個(gè)階段要么是 map/reduce 作業(yè),要么是一個(gè)元數(shù)據(jù)或者HDFS上的操作。將生成的計(jì)劃發(fā)給 DRIVER。

如果是 map/reduce 作業(yè),該計(jì)劃包括 map operator trees 和一個(gè)  reduce operator tree,執(zhí)行引擎將會(huì)把這些作業(yè)發(fā)送給 MapReduce :

步驟6、6．1、6．2和6．3:執(zhí)行引擎將這些階段提交給適當(dāng)?shù)慕M件。在每個(gè) task(mapper/reducer) 中,從HDFS文件中讀取與表或中間輸出相關(guān)聯(lián)的數(shù)據(jù),并通過相關(guān)算子樹傳遞這些數(shù)據(jù)。最終這些數(shù)據(jù)通過序列化器寫入到一個(gè)臨時(shí)HDFS文件中(如果不需要 reduce 階段,則在 map 中操作)。臨時(shí)文件用于向計(jì)劃中后面的 map/reduce 階段提供數(shù)據(jù)。

步驟7、8和9:最終的臨時(shí)文件將移動(dòng)到表的位置,確保不讀取臟數(shù)據(jù)(文件重命名在HDFS中是原子操作)。對(duì)于用戶的查詢,臨時(shí)文件的內(nèi)容由執(zhí)行引擎直接從HDFS讀取,然后通過Driver發(fā)送到UI。

Hive SQL 編譯成 MapReduce 過程

編譯 SQL 的任務(wù)是在上節(jié)中介紹的 COMPILER(編譯器組件)中完成的。Hive將SQL轉(zhuǎn)化為MapReduce任務(wù),整個(gè)編譯過程分為六個(gè)階段:

Hive SQL編譯過程詞法、語法解析: Antlr 定義 SQL 的語法規(guī)則,完成 SQL 詞法,語法解析,將 SQL 轉(zhuǎn)化為抽象語法樹 AST Tree;

Antlr是一種語言識(shí)別的工具,可以用來構(gòu)造領(lǐng)域語言。使用Antlr構(gòu)造特定的語言只需要編寫一個(gè)語法文件,定義詞法和語法替換規(guī)則即可,Antlr完成了詞法分析、語法分析、語義分析、中間代碼生成的過程。

語義解析: 遍歷 AST Tree,抽象出查詢的基本組成單元 QueryBlock;

生成邏輯執(zhí)行計(jì)劃: 遍歷 QueryBlock,翻譯為執(zhí)行操作樹 OperatorTree;

優(yōu)化邏輯執(zhí)行計(jì)劃: 邏輯層優(yōu)化器進(jìn)行 OperatorTree 變換,合并 Operator,達(dá)到減少 MapReduce Job,減少數(shù)據(jù)傳輸及 shuffle 數(shù)據(jù)量;

生成物理執(zhí)行計(jì)劃: 遍歷 OperatorTree,翻譯為 MapReduce 任務(wù);

優(yōu)化物理執(zhí)行計(jì)劃: 物理層優(yōu)化器進(jìn)行 MapReduce 任務(wù)的變換,生成最終的執(zhí)行計(jì)劃。

下面對(duì)這六個(gè)階段詳細(xì)解析:

為便于理解,我們拿一個(gè)簡(jiǎn)單的查詢語句進(jìn)行展示,對(duì)5月23號(hào)的地區(qū)維表進(jìn)行查詢:

select * from dim．dim_region where dt = '2021-05-23';

階段一:詞法、語法解析

根據(jù)Antlr定義的sql語法規(guī)則,將相關(guān)sql進(jìn)行詞法、語法解析,轉(zhuǎn)化為抽象語法樹AST Tree:

ABSTRACT SYNTAX TREE:
TOK_QUERY
   TOK_FROM
   TOK_TABREF
          TOK_TABNAME
              dim
                dim_region
   TOK_INSERT
     TOK_DESTINATION
         TOK_DIR
             TOK_TMP_FILE
       TOK_SELECT
         TOK_SELEXPR
             TOK_ALLCOLREF
       TOK_WHERE
         =
             TOK_TABLE_OR_COL
                 dt
                   '2021-05-23'

階段二:語義解析

遍歷AST Tree,抽象出查詢的基本組成單元QueryBlock:

AST Tree生成后由于其復(fù)雜度依舊較高,不便于翻譯為mapreduce程序,需要進(jìn)行進(jìn)一步抽象和結(jié)構(gòu)化,形成QueryBlock。

QueryBlock是一條SQL最基本的組成單元,包括三個(gè)部分:輸入源,計(jì)算過程,輸出。簡(jiǎn)單來講一個(gè)QueryBlock就是一個(gè)子查詢。

QueryBlock的生成過程為一個(gè)遞歸過程,先序遍歷 AST Tree ,遇到不同的 Token 節(jié)點(diǎn)(理解為特殊標(biāo)記),保存到相應(yīng)的屬性中。

階段三:生成邏輯執(zhí)行計(jì)劃

遍歷QueryBlock,翻譯為執(zhí)行操作樹OperatorTree:

Hive最終生成的MapReduce任務(wù),Map階段和Reduce階段均由OperatorTree組成。

基本的操作符包括:

TableScanOperatorSelectOperatorFilterOperatorJoinOperatorGroupByOperatorReduceSinkOperator`

Operator在Map Reduce階段之間的數(shù)據(jù)傳遞都是一個(gè)流式的過程。每一個(gè)Operator對(duì)一行數(shù)據(jù)完成操作后之后將數(shù)據(jù)傳遞給childOperator計(jì)算。

由于Join/GroupBy/OrderBy均需要在Reduce階段完成,所以在生成相應(yīng)操作的Operator之前都會(huì)先生成一個(gè)ReduceSinkOperator,將字段組合并序列化為Reduce Key/value, Partition Key。

階段四:優(yōu)化邏輯執(zhí)行計(jì)劃

Hive中的邏輯查詢優(yōu)化可以大致分為以下幾類:

投影修剪推導(dǎo)傳遞謂詞謂詞下推將Select-Select,Filter-Filter合并為單個(gè)操作多路 Join查詢重寫以適應(yīng)某些列值的Join傾斜

階段五:生成物理執(zhí)行計(jì)劃

生成物理執(zhí)行計(jì)劃即是將邏輯執(zhí)行計(jì)劃生成的OperatorTree轉(zhuǎn)化為MapReduce Job的過程,主要分為下面幾個(gè)階段:

對(duì)輸出表生成MoveTask從OperatorTree的其中一個(gè)根節(jié)點(diǎn)向下深度優(yōu)先遍歷ReduceSinkOperator標(biāo)示Map/Reduce的界限,多個(gè)Job間的界限遍歷其他根節(jié)點(diǎn),遇過碰到JoinOperator合并MapReduceTask生成StatTask更新元數(shù)據(jù)剪斷Map與Reduce間的Operator的關(guān)系

階段六:優(yōu)化物理執(zhí)行計(jì)劃

Hive中的物理優(yōu)化可以大致分為以下幾類:

分區(qū)修剪(Partition Pruning)基于分區(qū)和桶的掃描修剪(Scan pruning)如果查詢基于抽樣,則掃描修剪在某些情況下,在 map 端應(yīng)用 Group By在 mapper 上執(zhí)行 Join優(yōu)化 Union,使Union只在 map 端執(zhí)行在多路 Join 中,根據(jù)用戶提示決定最后流哪個(gè)表刪除不必要的 ReduceSinkOperators對(duì)于帶有Limit子句的查詢,減少需要為該表掃描的文件數(shù)對(duì)于帶有Limit子句的查詢,通過限制 ReduceSinkOperator 生成的內(nèi)容來限制來自 mapper 的輸出減少用戶提交的SQL查詢所需的Tez作業(yè)數(shù)量如果是簡(jiǎn)單的提取查詢,避免使用MapReduce作業(yè)對(duì)于帶有聚合的簡(jiǎn)單獲取查詢,執(zhí)行不帶 MapReduce 任務(wù)的聚合重寫 Group By 查詢使用索引表代替原來的表當(dāng)表掃描之上的謂詞是相等謂詞且謂詞中的列具有索引時(shí),使用索引掃描

經(jīng)過以上六個(gè)階段,SQL 就被解析映射成了集群上的 MapReduce 任務(wù)。

SQL編譯成MapReduce具體原理

在階段五-生成物理執(zhí)行計(jì)劃,即遍歷 OperatorTree,翻譯為 MapReduce 任務(wù),這個(gè)過程具體是怎么轉(zhuǎn)化的呢

我們接下來舉幾個(gè)常用 SQL 語句轉(zhuǎn)化為 MapReduce 的具體步驟:

Join的實(shí)現(xiàn)原理

以下面這個(gè)SQL為例,講解 join 的實(shí)現(xiàn):

select u．name, o．orderid from order o join user u on o．uid = u．uid;

在map的輸出value中為不同表的數(shù)據(jù)打上tag標(biāo)記,在reduce階段根據(jù)tag判斷數(shù)據(jù)來源。MapReduce的過程如下:

MapReduce CommonJoin的實(shí)現(xiàn)Group By的實(shí)現(xiàn)原理

以下面這個(gè)SQL為例,講解 group by 的實(shí)現(xiàn):

select rank, isonline, count(*) from city group by rank, isonline;

將GroupBy的字段組合為map的輸出key值,利用MapReduce的排序,在reduce階段保存LastKey區(qū)分不同的key。MapReduce的過程如下:

MapReduce Group By的實(shí)現(xiàn)Distinct的實(shí)現(xiàn)原理

以下面這個(gè)SQL為例,講解 distinct 的實(shí)現(xiàn):

select dealid, count(distinct uid) num from order group by dealid;

當(dāng)只有一個(gè)distinct字段時(shí),如果不考慮Map階段的Hash GroupBy,只需要將GroupBy字段和Distinct字段組合為map輸出key,利用mapreduce的排序,同時(shí)將GroupBy字段作為reduce的key,在reduce階段保存LastKey即可完成去重:

MapReduce Distinct的實(shí)現(xiàn)九、Hive千億級(jí)數(shù)據(jù)傾斜數(shù)據(jù)傾斜問題剖析

數(shù)據(jù)傾斜是分布式系統(tǒng)不可避免的問題,任何分布式系統(tǒng)都有幾率發(fā)生數(shù)據(jù)傾斜,但有些小伙伴在平時(shí)工作中感知不是很明顯,這里要注意本篇文章的標(biāo)題—“千億級(jí)數(shù)據(jù)”,為什么說千億級(jí),因?yàn)槿绻�一個(gè)任務(wù)的數(shù)據(jù)量只有幾百萬,它即使發(fā)生了數(shù)據(jù)傾斜,所有數(shù)據(jù)都跑到一臺(tái)機(jī)器去執(zhí)行,對(duì)于幾百萬的數(shù)據(jù)量,一臺(tái)機(jī)器執(zhí)行起來還是毫無壓力的,這時(shí)數(shù)據(jù)傾斜對(duì)我們感知不大,只有數(shù)據(jù)達(dá)到一個(gè)量級(jí)時(shí),一臺(tái)機(jī)器應(yīng)付不了這么多的數(shù)據(jù),這時(shí)如果發(fā)生數(shù)據(jù)傾斜,那么最后就很難算出結(jié)果。

所以就需要我們對(duì)數(shù)據(jù)傾斜的問題進(jìn)行優(yōu)化,盡量避免或減輕數(shù)據(jù)傾斜帶來的影響。

在解決數(shù)據(jù)傾斜問題之前,還要再提一句:沒有瓶頸時(shí)談?wù)搩?yōu)化,都是自尋煩惱。

大家想想,在map和reduce兩個(gè)階段中,最容易出現(xiàn)數(shù)據(jù)傾斜的就是reduce階段,因?yàn)閙ap到reduce會(huì)經(jīng)過shuffle階段,在shuffle中默認(rèn)會(huì)按照key進(jìn)行hash,如果相同的key過多,那么hash的結(jié)果就是大量相同的key進(jìn)入到同一個(gè)reduce中,導(dǎo)致數(shù)據(jù)傾斜。

那么有沒有可能在map階段就發(fā)生數(shù)據(jù)傾斜呢,是有這種可能的。

一個(gè)任務(wù)中,數(shù)據(jù)文件在進(jìn)入map階段之前會(huì)進(jìn)行切分,默認(rèn)是128M一個(gè)數(shù)據(jù)塊,但是如果當(dāng)對(duì)文件使用GZIP壓縮等不支持文件分割操作的壓縮方式時(shí),MR任務(wù)讀取壓縮后的文件時(shí),是對(duì)它切分不了的,該壓縮文件只會(huì)被一個(gè)任務(wù)所讀取,如果有一個(gè)超大的不可切分的壓縮文件被一個(gè)map讀取時(shí),就會(huì)發(fā)生map階段的數(shù)據(jù)傾斜。

所以,從本質(zhì)上來說,發(fā)生數(shù)據(jù)傾斜的原因有兩種:一是任務(wù)中需要處理大量相同的key的數(shù)據(jù)。二是任務(wù)讀取不可分割的大文件。

數(shù)據(jù)傾斜解決方案

MapReduce和Spark中的數(shù)據(jù)傾斜解決方案原理都是類似的,以下討論Hive使用MapReduce引擎引發(fā)的數(shù)據(jù)傾斜,Spark數(shù)據(jù)傾斜也可以此為參照。

1． 空值引發(fā)的數(shù)據(jù)傾斜

實(shí)際業(yè)務(wù)中有些大量的null值或者一些無意義的數(shù)據(jù)參與到計(jì)算作業(yè)中,表中有大量的null值,如果表之間進(jìn)行join操作,就會(huì)有shuffle產(chǎn)生,這樣所有的null值都會(huì)被分配到一個(gè)reduce中,必然產(chǎn)生數(shù)據(jù)傾斜。

之前有小伙伴問,如果A、B兩表join操作,假如A表中需要join的字段為null,但是B表中需要join的字段不為null,這兩個(gè)字段根本就join不上啊,為什么還會(huì)放到一個(gè)reduce中呢?

這里我們需要明確一個(gè)概念,數(shù)據(jù)放到同一個(gè)reduce中的原因不是因?yàn)樽侄文懿荒躩oin上,而是因?yàn)閟huffle階段的hash操作,只要key的hash結(jié)果是一樣的,它們就會(huì)被拉到同一個(gè)reduce中。

解決方案:

第一種:可以直接不讓null值參與join操作,即不讓null值有shuffle階段

SELECT *
FROM log a
JOIN users b
ON a．user_id IS NOT NULL
 AND a．user_id = b．user_id
UNION ALL
SELECT *
FROM log a
WHERE a．user_id IS NULL;

第二種:因?yàn)閚ull值參與shuffle時(shí)的hash結(jié)果是一樣的,那么我們可以給null值隨機(jī)賦值,這樣它們的hash結(jié)果就不一樣,就會(huì)進(jìn)到不同的reduce中:

SELECT *
FROM log a
LEFT JOIN users b ON CASE
  WHEN a．user_id IS NULL THEN concat('hive_', rand())
  ELSE a．user_id
 END = b．user_id;
2． 不同數(shù)據(jù)類型引發(fā)的數(shù)據(jù)傾斜

對(duì)于兩個(gè)表join,表a中需要join的字段key為int,表b中key字段既有string類型也有int類型。當(dāng)按照key進(jìn)行兩個(gè)表的join操作時(shí),默認(rèn)的Hash操作會(huì)按int型的id來進(jìn)行分配,這樣所有的string類型都被分配成同一個(gè)id,結(jié)果就是所有的string類型的字段進(jìn)入到一個(gè)reduce中,引發(fā)數(shù)據(jù)傾斜。

解決方案:

如果key字段既有string類型也有int類型,默認(rèn)的hash就都會(huì)按int類型來分配,那我們直接把int類型都轉(zhuǎn)為string就好了,這樣key字段都為string,hash時(shí)就按照string類型分配了:

SELECT *
FROM users a
LEFT JOIN logs b ON a．usr_id = CAST(b．user_id AS string);
3． 不可拆分大文件引發(fā)的數(shù)據(jù)傾斜

當(dāng)集群的數(shù)據(jù)量增長到一定規(guī)模,有些數(shù)據(jù)需要?dú)w檔或者轉(zhuǎn)儲(chǔ),這時(shí)候往往會(huì)對(duì)數(shù)據(jù)進(jìn)行壓縮;當(dāng)對(duì)文件使用GZIP壓縮等不支持文件分割操作的壓縮方式,在日后有作業(yè)涉及讀取壓縮后的文件時(shí),該壓縮文件只會(huì)被一個(gè)任務(wù)所讀取。如果該壓縮文件很大,則處理該文件的Map需要花費(fèi)的時(shí)間會(huì)遠(yuǎn)多于讀取普通文件的Map時(shí)間,該Map任務(wù)會(huì)成為作業(yè)運(yùn)行的瓶頸。這種情況也就是Map讀取文件的數(shù)據(jù)傾斜。

解決方案:

這種數(shù)據(jù)傾斜問題沒有什么好的解決方案,只能將使用GZIP壓縮等不支持文件分割的文件轉(zhuǎn)為bzip和zip等支持文件分割的壓縮方式。

所以,我們?cè)趯?duì)文件進(jìn)行壓縮時(shí),為避免因不可拆分大文件而引發(fā)數(shù)據(jù)讀取的傾斜,在數(shù)據(jù)壓縮的時(shí)候可以采用bzip2和Zip等支持文件分割的壓縮算法。

4． 數(shù)據(jù)膨脹引發(fā)的數(shù)據(jù)傾斜

在多維聚合計(jì)算時(shí),如果進(jìn)行分組聚合的字段過多,如下:

select a,b,c,count(1)from log group by a,b,c with rollup;

注:對(duì)于最后的with rollup關(guān)鍵字不知道大家用過沒,with rollup是用來在分組統(tǒng)計(jì)數(shù)據(jù)的基礎(chǔ)上再進(jìn)行統(tǒng)計(jì)匯總,即用來得到group by的匯總信息。

如果上面的log表的數(shù)據(jù)量很大,并且Map端的聚合不能很好地起到數(shù)據(jù)壓縮的情況下,會(huì)導(dǎo)致Map端產(chǎn)出的數(shù)據(jù)急速膨脹,這種情況容易導(dǎo)致作業(yè)內(nèi)存溢出的異常。如果log表含有數(shù)據(jù)傾斜key,會(huì)加劇Shuffle過程的數(shù)據(jù)傾斜。

解決方案:

可以拆分上面的sql,將with rollup拆分成如下幾個(gè)sql:

SELECT a, b, c, COUNT(1)
FROM log
GROUP BY a, b, c;
SELECT a, b, NULL, COUNT(1)
FROM log
GROUP BY a, b;
SELECT a, NULL, NULL, COUNT(1)
FROM log
GROUP BY a;
SELECT NULL, NULL, NULL, COUNT(1)
FROM log;

但是,上面這種方式不太好,因?yàn)楝F(xiàn)在是對(duì)3個(gè)字段進(jìn)行分組聚合,那如果是5個(gè)或者10個(gè)字段呢,那么需要拆解的SQL語句會(huì)更多。

在Hive中可以通過參數(shù) hive．new．job．grouping．set．cardinality 配置的方式自動(dòng)控制作業(yè)的拆解,該參數(shù)默認(rèn)值是30。表示針對(duì)grouping sets/rollups/cubes這類多維聚合的操作,如果最后拆解的鍵組合大于該值,會(huì)啟用新的任務(wù)去處理大于該值之外的組合。如果在處理數(shù)據(jù)時(shí),某個(gè)分組聚合的列有較大的傾斜,可以適當(dāng)調(diào)小該值。

5． 表連接時(shí)引發(fā)的數(shù)據(jù)傾斜

兩表進(jìn)行普通的repartition join時(shí),如果表連接的鍵存在傾斜,那么在 Shuffle 階段必然會(huì)引起數(shù)據(jù)傾斜。

解決方案:

通常做法是將傾斜的數(shù)據(jù)存到分布式緩存中,分發(fā)到各個(gè) Map任務(wù)所在節(jié)點(diǎn)。在Map階段完成join操作,即MapJoin,這避免了 Shuffle,從而避免了數(shù)據(jù)傾斜。

MapJoin是Hive的一種優(yōu)化操作,其適用于小表JOIN大表的場(chǎng)景,由于表的JOIN操作是在Map端且在內(nèi)存進(jìn)行的,所以其并不需要啟動(dòng)Reduce任務(wù)也就不需要經(jīng)過shuffle階段,從而能在一定程度上節(jié)省資源提高JOIN效率。

在Hive 0．11版本之前,如果想在Map階段完成join操作,必須使用MAPJOIN來標(biāo)記顯示地啟動(dòng)該優(yōu)化操作,由于其需要將小表加載進(jìn)內(nèi)存所以要注意小表的大小。

如將a表放到Map端內(nèi)存中執(zhí)行,在Hive 0．11版本之前需要這樣寫:

select  +mapjoin(a)  a．id , a．name, b．a(chǎn)ge
from a join b
on a．id = b．id;

如果想將多個(gè)表放到Map端內(nèi)存中,只需在mapjoin()中寫多個(gè)表名稱即可,用逗號(hào)分隔,如將a表和c表放到Map端內(nèi)存中,則  +mapjoin(a,c)  。

在Hive 0．11版本及之后,Hive默認(rèn)啟動(dòng)該優(yōu)化,也就是不在需要顯示的使用MAPJOIN標(biāo)記,其會(huì)在必要的時(shí)候觸發(fā)該優(yōu)化操作將普通JOIN轉(zhuǎn)換成MapJoin,可以通過以下兩個(gè)屬性來設(shè)置該優(yōu)化的觸發(fā)時(shí)機(jī):

hive．a(chǎn)uto．convert．join=true 默認(rèn)值為true,自動(dòng)開啟MAPJOIN優(yōu)化。

hive．mapjoin．smalltable．filesize=2500000 默認(rèn)值為2500000(25M),通過配置該屬性來確定使用該優(yōu)化的表的大小,如果表的大小小于此值就會(huì)被加載進(jìn)內(nèi)存中。

注意:使用默認(rèn)啟動(dòng)該優(yōu)化的方式如果出現(xiàn)莫名其妙的BUG(比如MAPJOIN并不起作用),就將以下兩個(gè)屬性置為fase手動(dòng)使用MAPJOIN標(biāo)記來啟動(dòng)該優(yōu)化:

hive．a(chǎn)uto．convert．join=false (關(guān)閉自動(dòng)MAPJOIN轉(zhuǎn)換操作)

hive．ignore．mapjoin．hint=false (不忽略MAPJOIN標(biāo)記)

再提一句:將表放到Map端內(nèi)存時(shí),如果節(jié)點(diǎn)的內(nèi)存很大,但還是出現(xiàn)內(nèi)存溢出的情況,我們可以通過這個(gè)參數(shù) mapreduce．map．memory．mb 調(diào)節(jié)Map端內(nèi)存的大小。

6． 確實(shí)無法減少數(shù)據(jù)量引發(fā)的數(shù)據(jù)傾斜

在一些操作中,我們沒有辦法減少數(shù)據(jù)量,如在使用 collect_list 函數(shù)時(shí):

select s_age,collect_list(s_score) list_score
from student
group by s_age

collect_list:將分組中的某列轉(zhuǎn)為一個(gè)數(shù)組返回。

在上述sql中,s_age有數(shù)據(jù)傾斜,但如果數(shù)據(jù)量大到一定的數(shù)量,會(huì)導(dǎo)致處理傾斜的Reduce任務(wù)產(chǎn)生內(nèi)存溢出的異常。

collect_list輸出一個(gè)數(shù)組,中間結(jié)果會(huì)放到內(nèi)存中,所以如果collect_list聚合太多數(shù)據(jù),會(huì)導(dǎo)致內(nèi)存溢出。

有小伙伴說這是 group by 分組引起的數(shù)據(jù)傾斜,可以開啟hive．groupby．skewindata參數(shù)來優(yōu)化。我們接下來分析下:

開啟該配置會(huì)將作業(yè)拆解成兩個(gè)作業(yè),第一個(gè)作業(yè)會(huì)盡可能將Map的數(shù)據(jù)平均分配到Reduce階段,并在這個(gè)階段實(shí)現(xiàn)數(shù)據(jù)的預(yù)聚合,以減少第二個(gè)作業(yè)處理的數(shù)據(jù)量;第二個(gè)作業(yè)在第一個(gè)作業(yè)處理的數(shù)據(jù)基礎(chǔ)上進(jìn)行結(jié)果的聚合。

hive．groupby．skewindata的核心作用在于生成的第一個(gè)作業(yè)能夠有效減少數(shù)量。但是對(duì)于collect_list這類要求全量操作所有數(shù)據(jù)的中間結(jié)果的函數(shù)來說,明顯起不到作用,反而因?yàn)橐�入新的作業(yè)增加了磁盤和網(wǎng)絡(luò)I/O的負(fù)擔(dān),而導(dǎo)致性能變得更為低下。

解決方案:

這類問題最直接的方式就是調(diào)整reduce所執(zhí)行的內(nèi)存大小。

調(diào)整reduce的內(nèi)存大小使用mapreduce．reduce．memory．mb這個(gè)配置。

總結(jié)

通過上面的內(nèi)容我們發(fā)現(xiàn),shuffle階段堪稱性能的殺手,為什么這么說,一方面shuffle階段是最容易引起數(shù)據(jù)傾斜的;另一方面shuffle的過程中會(huì)產(chǎn)生大量的磁盤I/O、網(wǎng)絡(luò)I/O 以及壓縮、解壓縮、序列化和反序列化等。這些操作都是嚴(yán)重影響性能的。

所以圍繞shuffle和數(shù)據(jù)傾斜有很多的調(diào)優(yōu)點(diǎn):

Mapper 端的Buffer 設(shè)置為多大?Buffer 設(shè)置得大,可提升性能,減少磁盤I/O ,但是對(duì)內(nèi)存有要求,對(duì)GC 有壓力;Buffer 設(shè)置得小,可能不占用那么多內(nèi)存, 但是可能頻繁的磁盤I/O 、頻繁的網(wǎng)絡(luò)I/O 。十、Hive企業(yè)級(jí)性能優(yōu)化Hive性能問題排查的方式

當(dāng)我們發(fā)現(xiàn)一條SQL語句執(zhí)行時(shí)間過長或者不合理時(shí),我們就要考慮對(duì)SQL進(jìn)行優(yōu)化,優(yōu)化首先得進(jìn)行問題排查,那么我們可以通過哪些方式進(jìn)行排查呢。

經(jīng)常使用關(guān)系型數(shù)據(jù)庫的同學(xué)可能知道關(guān)系型數(shù)據(jù)庫的優(yōu)化的訣竅-看執(zhí)行計(jì)劃。如Oracle數(shù)據(jù)庫,它有多種類型的執(zhí)行計(jì)劃,通過多種執(zhí)行計(jì)劃的配合使用,可以看到根據(jù)統(tǒng)計(jì)信息推演的執(zhí)行計(jì)劃,即Oracle推斷出來的未真正運(yùn)行的執(zhí)行計(jì)劃;還可以看到實(shí)際執(zhí)行任務(wù)的執(zhí)行計(jì)劃;能夠觀察到從數(shù)據(jù)讀取到最終呈現(xiàn)的主要過程和中間的量化數(shù)據(jù)。可以說,在Oracle開發(fā)領(lǐng)域,掌握合適的環(huán)節(jié),選用不同的執(zhí)行計(jì)劃,SQL調(diào)優(yōu)就不是一件難事。

Hive中也有執(zhí)行計(jì)劃,但是Hive的執(zhí)行計(jì)劃都是預(yù)測(cè)的,這點(diǎn)不像Oracle和SQL Server有真實(shí)的計(jì)劃,可以看到每個(gè)階段的處理數(shù)據(jù)、消耗的資源和處理的時(shí)間等量化數(shù)據(jù)。Hive提供的執(zhí)行計(jì)劃沒有這些數(shù)據(jù),這意味著雖然Hive的使用者知道整個(gè)SQL的執(zhí)行邏輯,但是各階段耗用的資源狀況和整個(gè)SQL的執(zhí)行瓶頸在哪里是不清楚的。

想要知道HiveSQL所有階段的運(yùn)行信息,可以查看YARN提供的日志。查看日志的鏈接,可以在每個(gè)作業(yè)執(zhí)行后,在控制臺(tái)打印的信息中找到。如下圖所示:

Hive提供的執(zhí)行計(jì)劃目前可以查看的信息有以下幾種:

查看執(zhí)行計(jì)劃的基本信息,即explain;查看執(zhí)行計(jì)劃的擴(kuò)展信息,即explain extended;查看SQL數(shù)據(jù)輸入依賴的信息,即explain dependency;查看SQL操作相關(guān)權(quán)限的信息,即explain authorization;查看SQL的向量化描述信息,即explain vectorization。

在查詢語句的SQL前面加上關(guān)鍵字explain是查看執(zhí)行計(jì)劃的基本方法。用explain打開的執(zhí)行計(jì)劃包含以下兩部分:

作業(yè)的依賴關(guān)系圖,即STAGE DEPENDENCIES;每個(gè)作業(yè)的詳細(xì)信息,即STAGE PLANS。

Hive中的explain執(zhí)行計(jì)劃詳解可看我之前寫的這篇文章:

Hive底層原理:explain執(zhí)行計(jì)劃詳解

注:使用explain查看執(zhí)行計(jì)劃是Hive性能調(diào)優(yōu)中非常重要的一種方式,請(qǐng)務(wù)必掌握!

總結(jié):Hive對(duì)SQL語句性能問題排查的方式:

使用explain查看執(zhí)行計(jì)劃;查看YARN提供的日志。Hive性能調(diào)優(yōu)的方式

為什么都說性能優(yōu)化這項(xiàng)工作是比較難的,因?yàn)橐豁?xiàng)技術(shù)的優(yōu)化,必然是一項(xiàng)綜合性的工作,它是多門技術(shù)的結(jié)合。我們?nèi)绻�只局限于一種技術(shù),那么肯定做不好優(yōu)化的。

下面將從多個(gè)完全不同的角度來介紹Hive優(yōu)化的多樣性,我們先來一起感受下。

1． SQL語句優(yōu)化

SQL語句優(yōu)化涉及到的內(nèi)容太多,因篇幅有限,不能一一介紹到,所以就拿幾個(gè)典型舉例,讓大家學(xué)到這種思想,以后遇到類似調(diào)優(yōu)問題可以往這幾個(gè)方面多思考下。

1． union all
insert into table stu partition(tp)
select s_age,max(s_birth) stat,'max' tp
from stu_ori
group by s_age
union all
insert into table stu partition(tp)
select s_age,min(s_birth) stat,'min' tp
from stu_ori
group by s_age;

我們簡(jiǎn)單分析上面的SQl語句,就是將每個(gè)年齡的最大和最小的生日獲取出來放到同一張表中,union all 前后的兩個(gè)語句都是對(duì)同一張表按照s_age進(jìn)行分組,然后分別取最大值和最小值。對(duì)同一張表相同的字段進(jìn)行兩次分組,這造成了極大浪費(fèi),我們能不能改造下呢,當(dāng)然是可以的,為大家介紹一個(gè)語法:from ．．． insert into ．．． ,這個(gè)語法將from前置,作用就是使用一張表,可以進(jìn)行多次插入操作:

--開啟動(dòng)態(tài)分區(qū)
set hive．exec．dynamic．partition=true;
set hive．exec．dynamic．partition．mode=nonstrict;
from stu_ori
insert into table stu partition(tp)
select s_age,max(s_birth) stat,'max' tp
group by s_age
insert into table stu partition(tp)
select s_age,min(s_birth) stat,'min' tp
group by s_age;

上面的SQL就可以對(duì)stu_ori表的s_age字段分組一次而進(jìn)行兩次不同的插入操作。

這個(gè)例子告訴我們一定要多了解SQL語句,如果我們不知道這種語法,一定不會(huì)想到這種方式的。

2． distinct

先看一個(gè)SQL,去重計(jì)數(shù):

select count(1)
from(
 select s_age
 from stu
 group by s_age
) b;

這是簡(jiǎn)單統(tǒng)計(jì)年齡的枚舉值個(gè)數(shù),為什么不用distinct?

select count(distinct s_age)
from stu;

有人說因?yàn)樵跀?shù)據(jù)量特別大的情況下使用第一種方式能夠有效避免Reduce端的數(shù)據(jù)傾斜,但是事實(shí)如此嗎?

我們先不管數(shù)據(jù)量特別大這個(gè)問題,就當(dāng)前的業(yè)務(wù)和環(huán)境下使用distinct一定會(huì)比上面那種子查詢的方式效率高。原因有以下幾點(diǎn):

上面進(jìn)行去重的字段是年齡字段,要知道年齡的枚舉值是非常有限的,就算計(jì)算1歲到100歲之間的年齡,s_age的最大枚舉值才是100,如果轉(zhuǎn)化成MapReduce來解釋的話,在Map階段,每個(gè)Map會(huì)對(duì)s_age去重。由于s_age枚舉值有限,因而每個(gè)Map得到的s_age也有限,最終得到reduce的數(shù)據(jù)量也就是map數(shù)量*s_age枚舉值的個(gè)數(shù)。

distinct的命令會(huì)在內(nèi)存中構(gòu)建一個(gè)hashtable,查找去重的時(shí)間復(fù)雜度是O(1);group by在不同版本間變動(dòng)比較大,有的版本會(huì)用構(gòu)建hashtable的形式去重,有的版本會(huì)通過排序的方式, 排序最優(yōu)時(shí)間復(fù)雜度無法到O(1)。另外,第一種方式(group by)去重會(huì)轉(zhuǎn)化為兩個(gè)任務(wù),會(huì)消耗更多的磁盤網(wǎng)絡(luò)I/O資源。

最新的Hive 3．0中新增了 count(distinct ) 優(yōu)化,通過配置 hive．optimize．countdistinct,即使真的出現(xiàn)數(shù)據(jù)傾斜也可以自動(dòng)優(yōu)化,自動(dòng)改變SQL執(zhí)行的邏輯。

第二種方式(distinct)比第一種方式(group by)代碼簡(jiǎn)潔,表達(dá)的意思簡(jiǎn)單明了,如果沒有特殊的問題,代碼簡(jiǎn)潔就是優(yōu)!

這個(gè)例子告訴我們,有時(shí)候我們不要過度優(yōu)化,調(diào)優(yōu)講究適時(shí)調(diào)優(yōu),過早進(jìn)行調(diào)優(yōu)有可能做的是無用功甚至產(chǎn)生負(fù)效應(yīng),在調(diào)優(yōu)上投入的工作成本和回報(bào)不成正比。調(diào)優(yōu)需要遵循一定的原則。

2． 數(shù)據(jù)格式優(yōu)化

Hive提供了多種數(shù)據(jù)存儲(chǔ)組織格式,不同格式對(duì)程序的運(yùn)行效率也會(huì)有極大的影響。

Hive提供的格式有TEXT、SequenceFile、RCFile、ORC和Parquet等。

SequenceFile是一個(gè)二進(jìn)制key/value對(duì)結(jié)構(gòu)的平面文件,在早期的Hadoop平臺(tái)上被廣泛用于MapReduce輸出/輸出格式,以及作為數(shù)據(jù)存儲(chǔ)格式。

Parquet是一種列式數(shù)據(jù)存儲(chǔ)格式,可以兼容多種計(jì)算引擎,如MapRedcue和Spark等,對(duì)多層嵌套的數(shù)據(jù)結(jié)構(gòu)提供了良好的性能支持,是目前Hive生產(chǎn)環(huán)境中數(shù)據(jù)存儲(chǔ)的主流選擇之一。

ORC優(yōu)化是對(duì)RCFile的一種優(yōu)化,它提供了一種高效的方式來存儲(chǔ)Hive數(shù)據(jù),同時(shí)也能夠提高Hive的讀取、寫入和處理數(shù)據(jù)的性能,能夠兼容多種計(jì)算引擎。事實(shí)上,在實(shí)際的生產(chǎn)環(huán)境中,ORC已經(jīng)成為了Hive在數(shù)據(jù)存儲(chǔ)上的主流選擇之一。

我們使用同樣數(shù)據(jù)及SQL語句,只是數(shù)據(jù)存儲(chǔ)格式不同,得到如下執(zhí)行時(shí)長:

數(shù)據(jù)格式CPU時(shí)間用戶等待耗時(shí)TextFile33分171秒SequenceFile38分162秒Parquet2分22秒50秒ORC1分52秒56秒

注:CPU時(shí)間:表示運(yùn)行程序所占用服務(wù)器CPU資源的時(shí)間。
用戶等待耗時(shí):記錄的是用戶從提交作業(yè)到返回結(jié)果期間用戶等待的所有時(shí)間。

查詢TextFile類型的數(shù)據(jù)表耗時(shí)33分鐘, 查詢ORC類型的表耗時(shí)1分52秒,時(shí)間得以極大縮短,可見不同的數(shù)據(jù)存儲(chǔ)格式也能給HiveSQL性能帶來極大的影響。

3． 小文件過多優(yōu)化

小文件如果過多,對(duì) hive 來說,在進(jìn)行查詢時(shí),每個(gè)小文件都會(huì)當(dāng)成一個(gè)塊,啟動(dòng)一個(gè)Map任務(wù)來完成,而一個(gè)Map任務(wù)啟動(dòng)和初始化的時(shí)間遠(yuǎn)遠(yuǎn)大于邏輯處理的時(shí)間,就會(huì)造成很大的資源浪費(fèi)。而且,同時(shí)可執(zhí)行的Map數(shù)量是受限的。

所以我們有必要對(duì)小文件過多進(jìn)行優(yōu)化,關(guān)于小文件過多的解決的辦法,我之前專門寫了一篇文章講解,具體可查看:

解決hive小文件過多問題

4． 并行執(zhí)行優(yōu)化

Hive會(huì)將一個(gè)查詢轉(zhuǎn)化成一個(gè)或者多個(gè)階段。這樣的階段可以是MapReduce階段、抽樣階段、合并階段、limit階段�；蛘逪ive執(zhí)行過程中可能需要的其他階段。默認(rèn)情況下,Hive一次只會(huì)執(zhí)行一個(gè)階段。不過,某個(gè)特定的job可能包含眾多的階段,而這些階段可能并非完全互相依賴的,也就是說有些階段是可以并行執(zhí)行的,這樣可能使得整個(gè)job的執(zhí)行時(shí)間縮短。如果有更多的階段可以并行執(zhí)行,那么job可能就越快完成。

通過設(shè)置參數(shù)hive．exec．parallel值為true,就可以開啟并發(fā)執(zhí)行。在共享集群中,需要注意下,如果job中并行階段增多,那么集群利用率就會(huì)增加。

set hive．exec．parallel=true; //打開任務(wù)并行執(zhí)行
set hive．exec．parallel．thread．number=16; //同一個(gè)sql允許最大并行度,默認(rèn)為8。

當(dāng)然得是在系統(tǒng)資源比較空閑的時(shí)候才有優(yōu)勢(shì),否則沒資源,并行也起不來。

5． JVM優(yōu)化

JVM重用是Hadoop調(diào)優(yōu)參數(shù)的內(nèi)容,其對(duì)Hive的性能具有非常大的影響,特別是對(duì)于很難避免小文件的場(chǎng)景或task特別多的場(chǎng)景,這類場(chǎng)景大多數(shù)執(zhí)行時(shí)間都很短。

Hadoop的默認(rèn)配置通常是使用派生JVM來執(zhí)行map和Reduce任務(wù)的。這時(shí)JVM的啟動(dòng)過程可能會(huì)造成相當(dāng)大的開銷,尤其是執(zhí)行的job包含有成百上千task任務(wù)的情況。JVM重用可以使得JVM實(shí)例在同一個(gè)job中重新使用N次。N的值可以在Hadoop的mapred-site．xml文件中進(jìn)行配置。通常在10-20之間,具體多少需要根據(jù)具體業(yè)務(wù)場(chǎng)景測(cè)試得出。

我們也可以在hive中設(shè)置

set  mapred．job．reuse．jvm．num．tasks=10; //這個(gè)設(shè)置來設(shè)置我們的jvm重用

這個(gè)功能的缺點(diǎn)是,開啟JVM重用將一直占用使用到的task插槽,以便進(jìn)行重用,直到任務(wù)完成后才能釋放。如果某個(gè)“不平衡的”job中有某幾個(gè)reduce task執(zhí)行的時(shí)間要比其他Reduce task消耗的時(shí)間多的多的話,那么保留的插槽就會(huì)一直空閑著卻無法被其他的job使用,直到所有的task都結(jié)束了才會(huì)釋放。

6． 推測(cè)執(zhí)行優(yōu)化

在分布式集群環(huán)境下,因?yàn)槌绦駼ug(包括Hadoop本身的bug),負(fù)載不均衡或者資源分布不均等原因,會(huì)造成同一個(gè)作業(yè)的多個(gè)任務(wù)之間運(yùn)行速度不一致,有些任務(wù)的運(yùn)行速度可能明顯慢于其他任務(wù)(比如一個(gè)作業(yè)的某個(gè)任務(wù)進(jìn)度只有50%,而其他所有任務(wù)已經(jīng)運(yùn)行完畢),則這些任務(wù)會(huì)拖慢作業(yè)的整體執(zhí)行進(jìn)度。為了避免這種情況發(fā)生,Hadoop采用了推測(cè)執(zhí)行(Speculative Execution)機(jī)制,它根據(jù)一定的法則推測(cè)出“拖后腿”的任務(wù),并為這樣的任務(wù)啟動(dòng)一個(gè)備份任務(wù),讓該任務(wù)與原始任務(wù)同時(shí)處理同一份數(shù)據(jù),并最終選用最先成功運(yùn)行完成任務(wù)的計(jì)算結(jié)果作為最終結(jié)果。

設(shè)置開啟推測(cè)執(zhí)行參數(shù):Hadoop的mapred-site．xml文件中進(jìn)行配置:

hive本身也提供了配置項(xiàng)來控制reduce-side的推測(cè)執(zhí)行:

set hive．mapred．reduce．tasks．speculative．execution=true

關(guān)于調(diào)優(yōu)這些推測(cè)執(zhí)行變量,還很難給一個(gè)具體的建議。如果用戶對(duì)于運(yùn)行時(shí)的偏差非常敏感的話,那么可以將這些功能關(guān)閉掉。如果用戶因?yàn)檩斎霐?shù)據(jù)量很大而需要執(zhí)行長時(shí)間的map或者Reduce task的話,那么啟動(dòng)推測(cè)執(zhí)行造成的浪費(fèi)是非常巨大大。

最后

代碼優(yōu)化原則:

理透需求原則,這是優(yōu)化的根本;把握數(shù)據(jù)全鏈路原則,這是優(yōu)化的脈絡(luò);堅(jiān)持代碼的簡(jiǎn)潔原則,這讓優(yōu)化更加簡(jiǎn)單;沒有瓶頸時(shí)談?wù)搩?yōu)化,這是自尋煩惱。十一、Hive大廠面試真題1． hive內(nèi)部表和外部表的區(qū)別

未被external修飾的是內(nèi)部表,被external修飾的為外部表。

本文首發(fā)于公眾號(hào)【五分鐘學(xué)大數(shù)據(jù)】,關(guān)注公眾號(hào),獲取最新大數(shù)據(jù)技術(shù)文章

區(qū)別:

內(nèi)部表數(shù)據(jù)由Hive自身管理,外部表數(shù)據(jù)由HDFS管理;

內(nèi)部表數(shù)據(jù)存儲(chǔ)的位置是hive．metastore．warehouse．dir(默認(rèn):/user/hive/warehouse),外部表數(shù)據(jù)的存儲(chǔ)位置由自己制定(如果沒有LOCATION,Hive將在HDFS上的/user/hive/warehouse文件夾下以外部表的表名創(chuàng)建一個(gè)文件夾,并將屬于這個(gè)表的數(shù)據(jù)存放在這里);

刪除內(nèi)部表會(huì)直接刪除元數(shù)據(jù)(metadata)及存儲(chǔ)數(shù)據(jù);刪除外部表僅僅會(huì)刪除元數(shù)據(jù),HDFS上的文件并不會(huì)被刪除。

本文首發(fā)于公眾號(hào)【五分鐘學(xué)大數(shù)據(jù)】

2． Hive有索引嗎

Hive支持索引(3．0版本之前),但是Hive的索引與關(guān)系型數(shù)據(jù)庫中的索引并不相同,比如,Hive不支持主鍵或者外鍵。并且Hive索引提供的功能很有限,效率也并不高,因此Hive索引很少使用。

索引適用的場(chǎng)景:

適用于不更新的靜態(tài)字段。以免總是重建索引數(shù)據(jù)。每次建立、更新數(shù)據(jù)后,都要重建索引以構(gòu)建索引表。

Hive索引的機(jī)制如下:

hive在指定列上建立索引,會(huì)產(chǎn)生一張索引表(Hive的一張物理表),里面的字段包括:索引列的值、該值對(duì)應(yīng)的HDFS文件路徑、該值在文件中的偏移量。

Hive 0．8版本后引入bitmap索引處理器,這個(gè)處理器適用于去重后,值較少的列(例如,某字段的取值只可能是幾個(gè)枚舉值)因?yàn)樗饕�是用空間換時(shí)間,索引列的取值過多會(huì)導(dǎo)致建立bitmap索引表過大。

注意:Hive中每次有數(shù)據(jù)時(shí)需要及時(shí)更新索引,相當(dāng)于重建一個(gè)新表,否則會(huì)影響數(shù)據(jù)查詢的效率和準(zhǔn)確性,Hive官方文檔已經(jīng)明確表示Hive的索引不推薦被使用,在新版本的Hive中已經(jīng)被廢棄了。

擴(kuò)展:Hive是在0．7版本之后支持索引的,在0．8版本后引入bitmap索引處理器,在3．0版本開始移除索引的功能,取而代之的是2．3版本開始的物化視圖,自動(dòng)重寫的物化視圖替代了索引的功能。

3． 運(yùn)維如何對(duì)hive進(jìn)行調(diào)度

將hive的sql定義在腳本當(dāng)中;

使用azkaban或者oozie進(jìn)行任務(wù)的調(diào)度;

監(jiān)控任務(wù)調(diào)度頁面。

4． ORC、Parquet等列式存儲(chǔ)的優(yōu)點(diǎn)

ORC和Parquet都是高性能的存儲(chǔ)方式,這兩種存儲(chǔ)格式總會(huì)帶來存儲(chǔ)和性能上的提升。

Parquet:

Parquet支持嵌套的數(shù)據(jù)模型,類似于Protocol Buffers,每一個(gè)數(shù)據(jù)模型的schema包含多個(gè)字段,每一個(gè)字段有三個(gè)屬性:重復(fù)次數(shù)、數(shù)據(jù)類型和字段名。
重復(fù)次數(shù)可以是以下三種:required(只出現(xiàn)1次),repeated(出現(xiàn)0次或多次),optional(出現(xiàn)0次或1次)。每一個(gè)字段的數(shù)據(jù)類型可以分成兩種:group(復(fù)雜類型)和primitive(基本類型)。

Parquet中沒有Map、Array這樣的復(fù)雜數(shù)據(jù)結(jié)構(gòu),但是可以通過repeated和group組合來實(shí)現(xiàn)的。

由于Parquet支持的數(shù)據(jù)模型比較松散,可能一條記錄中存在比較深的嵌套關(guān)系,如果為每一條記錄都維護(hù)一個(gè)類似的樹狀結(jié)可能會(huì)占用較大的存儲(chǔ)空間,因此Dremel論文中提出了一種高效的對(duì)于嵌套數(shù)據(jù)格式的壓縮算法:Striping/Assembly算法。通過Striping/Assembly算法,parquet可以使用較少的存儲(chǔ)空間表示復(fù)雜的嵌套格式,并且通常Repetition level和Definition level都是較小的整數(shù)值,可以通過RLE算法對(duì)其進(jìn)行壓縮,進(jìn)一步降低存儲(chǔ)空間。

Parquet文件是以二進(jìn)制方式存儲(chǔ)的,是不可以直接讀取和修改的,Parquet文件是自解析的,文件中包括該文件的數(shù)據(jù)和元數(shù)據(jù)。

ORC:

ORC文件是自描述的,它的元數(shù)據(jù)使用Protocol Buffers序列化,并且文件中的數(shù)據(jù)盡可能的壓縮以降低存儲(chǔ)空間的消耗。

和Parquet類似,ORC文件也是以二進(jìn)制方式存儲(chǔ)的,所以是不可以直接讀取,ORC文件也是自解析的,它包含許多的元數(shù)據(jù),這些元數(shù)據(jù)都是同構(gòu)ProtoBuffer進(jìn)行序列化的。

ORC會(huì)盡可能合并多個(gè)離散的區(qū)間盡可能的減少I/O次數(shù)。

ORC中使用了更加精確的索引信息,使得在讀取數(shù)據(jù)時(shí)可以指定從任意一行開始讀取,更細(xì)粒度的統(tǒng)計(jì)信息使得讀取ORC文件跳過整個(gè)row group,ORC默認(rèn)會(huì)對(duì)任何一塊數(shù)據(jù)和索引信息使用ZLIB壓縮,因此ORC文件占用的存儲(chǔ)空間也更小。

在新版本的ORC中也加入了對(duì)Bloom Filter的支持,它可以進(jìn)一步提升謂詞下推的效率,在Hive 1．2．0版本以后也加入了對(duì)此的支持。

5． 數(shù)據(jù)建模用的哪些模型?1． 星型模型

星形模式

星形模式(Star Schema)是最常用的維度建模方式。星型模式是以事實(shí)表為中心,所有的維度表直接連接在事實(shí)表上,像星星一樣。星形模式的維度建模由一個(gè)事實(shí)表和一組維表成,且具有以下特點(diǎn):

a． 維表只和事實(shí)表關(guān)聯(lián),維表之間沒有關(guān)聯(lián);

b． 每個(gè)維表主鍵為單列,且該主鍵放置在事實(shí)表中,作為兩邊連接的外鍵;

c． 以事實(shí)表為核心,維表圍繞核心呈星形分布。

2． 雪花模型

雪花模式

雪花模式(Snowflake Schema)是對(duì)星形模式的擴(kuò)展。雪花模式的維度表可以擁有其他維度表的,雖然這種模型相比星型更規(guī)范一些,但是由于這種模型不太容易理解,維護(hù)成本比較高,而且性能方面需要關(guān)聯(lián)多層維表,性能比星型模型要低。

3． 星座模型

星座模型

星座模式是星型模式延伸而來,星型模式是基于一張事實(shí)表的,而星座模式是基于多張事實(shí)表的,而且共享維度信息。前面介紹的兩種維度建模方法都是多維表對(duì)應(yīng)單事實(shí)表,但在很多時(shí)候維度空間內(nèi)的事實(shí)表不止一個(gè),而一個(gè)維表也可能被多個(gè)事實(shí)表用到。在業(yè)務(wù)發(fā)展后期,絕大部分維度建模都采用的是星座模式。

數(shù)倉建模詳細(xì)介紹可查看:通俗易懂?dāng)?shù)倉建模

6． 為什么要對(duì)數(shù)據(jù)倉庫分層?

用空間換時(shí)間,通過大量的預(yù)處理來提升應(yīng)用系統(tǒng)的用戶體驗(yàn)(效率),因此數(shù)據(jù)倉庫會(huì)存在大量冗余的數(shù)據(jù)。

如果不分層的話,如果源業(yè)務(wù)系統(tǒng)的業(yè)務(wù)規(guī)則發(fā)生變化將會(huì)影響整個(gè)數(shù)據(jù)清洗過程,工作量巨大。

通過數(shù)據(jù)分層管理可以簡(jiǎn)化數(shù)據(jù)清洗的過程,因?yàn)榘言瓉硪徊降墓ぷ鞣值搅硕鄠�(gè)步驟去完成,相當(dāng)于把一個(gè)復(fù)雜的工作拆成了多個(gè)簡(jiǎn)單的工作,把一個(gè)大的黑盒變成了一個(gè)白盒,每一層的處理邏輯都相對(duì)簡(jiǎn)單和容易理解,這樣我們比較容易保證每一個(gè)步驟的正確性,當(dāng)數(shù)據(jù)發(fā)生錯(cuò)誤的時(shí)候,往往我們只需要局部調(diào)整某個(gè)步驟即可。

數(shù)據(jù)倉庫詳細(xì)介紹可查看:萬字詳解整個(gè)數(shù)據(jù)倉庫建設(shè)體系

7． 使用過Hive解析JSON串嗎

Hive處理json數(shù)據(jù)總體來說有兩個(gè)方向的路走:

將json以字符串的方式整個(gè)入Hive表,然后通過使用UDF函數(shù)解析已經(jīng)導(dǎo)入到hive中的數(shù)據(jù),比如使用LATERAL VIEW json_tuple的方法,獲取所需要的列名。

在導(dǎo)入之前將json拆成各個(gè)字段,導(dǎo)入Hive表的數(shù)據(jù)是已經(jīng)解析過的。這將需要使用第三方的SerDe。

詳細(xì)介紹可查看:Hive解析Json數(shù)組超全講解

8． sort by 和 order by 的區(qū)別

order by 會(huì)對(duì)輸入做全局排序,因此只有一個(gè)reducer(多個(gè)reducer無法保證全局有序)只有一個(gè)reducer,會(huì)導(dǎo)致當(dāng)輸入規(guī)模較大時(shí),需要較長的計(jì)算時(shí)間。

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

9． 數(shù)據(jù)傾斜怎么解決

數(shù)據(jù)傾斜問題主要有以下幾種:

空值引發(fā)的數(shù)據(jù)傾斜

不同數(shù)據(jù)類型引發(fā)的數(shù)據(jù)傾斜

不可拆分大文件引發(fā)的數(shù)據(jù)傾斜

數(shù)據(jù)膨脹引發(fā)的數(shù)據(jù)傾斜

表連接時(shí)引發(fā)的數(shù)據(jù)傾斜

確實(shí)無法減少數(shù)據(jù)量引發(fā)的數(shù)據(jù)傾斜

以上傾斜問題的具體解決方案可查看:Hive千億級(jí)數(shù)據(jù)傾斜解決方案

注意:對(duì)于 left join 或者 right join 來說,不會(huì)對(duì)關(guān)聯(lián)的字段自動(dòng)去除null值,對(duì)于 inner join 來說,會(huì)對(duì)關(guān)聯(lián)的字段自動(dòng)去除null值。

小伙伴們?cè)陂喿x時(shí)注意下,在上面的文章(Hive千億級(jí)數(shù)據(jù)傾斜解決方案)中,有一處sql出現(xiàn)了上述問題(舉例的時(shí)候原本是想使用left join的,結(jié)果手誤寫成了join)。此問題由公眾號(hào)讀者發(fā)現(xiàn),感謝這位讀者指正。

10． Hive 小文件過多怎么解決1． 使用 hive 自帶的 concatenate 命令,自動(dòng)合并小文件

使用方法:

#對(duì)于非分區(qū)表
alter table A concatenate;
#對(duì)于分區(qū)表
alter table B partition(day=20201224) concatenate;

注意:
1、concatenate 命令只支持 RCFILE 和 ORC 文件類型。
2、使用concatenate命令合并小文件時(shí)不能指定合并后的文件數(shù)量,但可以多次執(zhí)行該命令。
3、當(dāng)多次使用concatenate后文件數(shù)量不在變化,這個(gè)跟參數(shù) mapreduce．input．fileinputformat．split．minsize=256mb 的設(shè)置有關(guān),可設(shè)定每個(gè)文件的最小size。

2． 調(diào)整參數(shù)減少M(fèi)ap數(shù)量

設(shè)置map輸入合并小文件的相關(guān)參數(shù)(執(zhí)行Map前進(jìn)行小文件合并):

在mapper中將多個(gè)文件合成一個(gè)split作為輸入(CombineHiveInputFormat底層是Hadoop的CombineFileInputFormat方法):

set hive．input．format=org．a(chǎn)pache．hadoop．hive．ql．io．CombineHiveInputFormat; -- 默認(rèn)

每個(gè)Map最大輸入大小(這個(gè)值決定了合并后文件的數(shù)量):

set mapred．max．split．size=256000000;   -- 256M

一個(gè)節(jié)點(diǎn)上split的至少大小(這個(gè)值決定了多個(gè)DataNode上的文件是否需要合并):

set mapred．min．split．size．per．node=100000000;  -- 100M

一個(gè)交換機(jī)下split的至少大小(這個(gè)值決定了多個(gè)交換機(jī)上的文件是否需要合并):

set mapred．min．split．size．per．rack=100000000;  -- 100M
3． 減少Reduce的數(shù)量

reduce 的個(gè)數(shù)決定了輸出的文件的個(gè)數(shù),所以可以調(diào)整reduce的個(gè)數(shù)控制hive表的文件數(shù)量。

hive中的分區(qū)函數(shù) distribute by 正好是控制MR中partition分區(qū)的,可以通過設(shè)置reduce的數(shù)量,結(jié)合分區(qū)函數(shù)讓數(shù)據(jù)均衡的進(jìn)入每個(gè)reduce即可:

#設(shè)置reduce的數(shù)量有兩種方式,第一種是直接設(shè)置reduce個(gè)數(shù)
set mapreduce．job．reduces=10;
#第二種是設(shè)置每個(gè)reduce的大小,Hive會(huì)根據(jù)數(shù)據(jù)總大小猜測(cè)確定一個(gè)reduce個(gè)數(shù)
set hive．exec．reducers．bytes．per．reducer=5120000000; -- 默認(rèn)是1G,設(shè)置為5G
#執(zhí)行以下語句,將數(shù)據(jù)均衡的分配到reduce中
set mapreduce．job．reduces=10;
insert overwrite table A partition(dt)
select * from B
distribute by rand();

對(duì)于上述語句解釋:如設(shè)置reduce數(shù)量為10,使用 rand(), 隨機(jī)生成一個(gè)數(shù) x % 10 ,這樣數(shù)據(jù)就會(huì)隨機(jī)進(jìn)入 reduce 中,防止出現(xiàn)有的文件過大或過小。

4． 使用hadoop的archive將小文件歸檔

Hadoop Archive簡(jiǎn)稱HAR,是一個(gè)高效地將小文件放入HDFS塊中的文件存檔工具,它能夠?qū)⒍鄠�(gè)小文件打包成一個(gè)HAR文件,這樣在減少namenode內(nèi)存使用的同時(shí),仍然允許對(duì)文件進(jìn)行透明的訪問。

#用來控制歸檔是否可用
set hive．a(chǎn)rchive．enabled=true;
#通知Hive在創(chuàng)建歸檔時(shí)是否可以設(shè)置父目錄
set hive．a(chǎn)rchive．har．parentdir．settable=true;
#控制需要?dú)w檔文件的大小
set har．partfile．size=1099511627776;
使用以下命令進(jìn)行歸檔:
ALTER TABLE A ARCHIVE PARTITION(dt='2021-05-07', hr='12');
對(duì)已歸檔的分區(qū)恢復(fù)為原文件:
ALTER TABLE A UNARCHIVE PARTITION(dt='2021-05-07', hr='12');

注意:
歸檔的分區(qū)可以查看不能 insert overwrite,必須先 unarchive

Hive 小文件問題具體可查看:解決hive小文件過多問題

11． Hive優(yōu)化有哪些1． 數(shù)據(jù)存儲(chǔ)及壓縮:

針對(duì)hive中表的存儲(chǔ)格式通常有orc和parquet,壓縮格式一般使用snappy。相比與textfile格式表,orc占有更少的存儲(chǔ)。因?yàn)閔ive底層使用MR計(jì)算架構(gòu),數(shù)據(jù)流是hdfs到磁盤再到hdfs,而且會(huì)有很多次,所以使用orc數(shù)據(jù)格式和snappy壓縮策略可以降低IO讀寫,還能降低網(wǎng)絡(luò)傳輸量,這樣在一定程度上可以節(jié)省存儲(chǔ),還能提升hql任務(wù)執(zhí)行效率;

2． 通過調(diào)參優(yōu)化:

并行執(zhí)行,調(diào)節(jié)parallel參數(shù);

調(diào)節(jié)jvm參數(shù),重用jvm;

設(shè)置map、reduce的參數(shù);開啟strict mode模式;

關(guān)閉推測(cè)執(zhí)行設(shè)置。

3． 有效地減小數(shù)據(jù)集將大表拆分成子表;結(jié)合使用外部表和分區(qū)表。4． SQL優(yōu)化

大表對(duì)大表:盡量減少數(shù)據(jù)集,可以通過分區(qū)表,避免掃描全表或者全字段;

大表對(duì)小表:設(shè)置自動(dòng)識(shí)別小表,將小表放入內(nèi)存中去執(zhí)行。

Hive優(yōu)化詳細(xì)剖析可查看:Hive企業(yè)級(jí)性能優(yōu)化

附:九個(gè)最易出錯(cuò)的SQL講解

閱讀本節(jié)小建議:本文適合細(xì)嚼慢咽,不要一目十行,不然會(huì)錯(cuò)過很多有價(jià)值的細(xì)節(jié)。

在進(jìn)行數(shù)倉搭建和數(shù)據(jù)分析時(shí)最常用的就是 sql,其語法簡(jiǎn)潔明了,易于理解,目前大數(shù)據(jù)領(lǐng)域的幾大主流框架全部都支持sql語法,包括 hive,spark,flink等,所以sql在大數(shù)據(jù)領(lǐng)域有著不可替代的作用,需要我們重點(diǎn)掌握。

在使用sql時(shí)如果不熟悉或不仔細(xì),那么在進(jìn)行查詢分析時(shí)極容易出錯(cuò),接下來我們就來看下幾個(gè)容易出錯(cuò)的sql語句及使用注意事項(xiàng)。

1． decimal

hive 除了支持 int,double,string等常用類型,也支持 decimal 類型,用于在數(shù)據(jù)庫中存儲(chǔ)精確的數(shù)值,常用在表示金額的字段上

注意事項(xiàng):

如:decimal(11,2) 代表最多有11位數(shù)字,其中后2位是小數(shù),整數(shù)部分是9位;
如果整數(shù)部分超過9位,則這個(gè)字段就會(huì)變成null,如果整數(shù)部分不超過9位,則原字段顯示;
如果小數(shù)部分不足2位,則后面用0補(bǔ)齊兩位,如果小數(shù)部分超過兩位,則超出部分四舍五入;
也可直接寫 decimal,后面不指定位數(shù),默認(rèn)是 decimal(10,0) 整數(shù)10位,沒有小數(shù)

2． location表創(chuàng)建的時(shí)候可以用 location 指定一個(gè)文件或者文件夾
create  table stu(id int ,name string)  location '/user/stu2';

注意事項(xiàng):

創(chuàng)建表時(shí)使用location,當(dāng)指定文件夾時(shí),hive會(huì)加載文件夾下的所有文件,當(dāng)表中無分區(qū)時(shí),這個(gè)文件夾下不能再有文件夾,否則報(bào)錯(cuò)。
當(dāng)表是分區(qū)表時(shí),比如 partitioned by (day string), 則這個(gè)文件夾下的每一個(gè)文件夾就是一個(gè)分區(qū),且文件夾名為 day=20201123這種格式,然后使用:msck  repair   table  score; 修復(fù)表結(jié)構(gòu),成功之后即可看到數(shù)據(jù)已經(jīng)全部加載到表當(dāng)中去了

3． load data 和 load data local從hdfs上加載文件
load data inpath '/hivedatas/techer．csv' into table techer;
從本地系統(tǒng)加載文件
load data local inpath '/user/test/techer．csv' into table techer;

注意事項(xiàng):

使用 load data local 表示從本地文件系統(tǒng)加載,文件會(huì)拷貝到hdfs上使用 load data 表示從hdfs文件系統(tǒng)加載,文件會(huì)直接移動(dòng)到hive相關(guān)目錄下,注意不是拷貝過去,因?yàn)閔ive認(rèn)為hdfs文件已經(jīng)有3副本了,沒必要再次拷貝了如果表是分區(qū)表,load 時(shí)不指定分區(qū)會(huì)報(bào)錯(cuò)如果加載相同文件名的文件,會(huì)被自動(dòng)重命名4． drop 和 truncate刪除表操作
drop table score1;
清空表操作
truncate table score2;

注意事項(xiàng):

如果 hdfs 開啟了回收站,drop 刪除的表數(shù)據(jù)是可以從回收站恢復(fù)的,表結(jié)構(gòu)恢復(fù)不了,需要自己重新創(chuàng)建;truncate 清空的表是不進(jìn)回收站的,所以無法恢復(fù)truncate清空的表。
所以 truncate 一定慎用,一旦清空除物理恢復(fù)外將無力回天

5． join 連接INNER JOIN 內(nèi)連接:只有進(jìn)行連接的兩個(gè)表中都存在與連接條件相匹配的數(shù)據(jù)才會(huì)被保留下來
select * from techer t [inner] join course c on t．t_id = c．t_id; -- inner 可省略
LEFT OUTER JOIN 左外連接:左邊所有數(shù)據(jù)會(huì)被返回,右邊符合條件的被返回
select * from techer t left join course c on t．t_id = c．t_id; -- outer可省略
RIGHT OUTER JOIN 右外連接:右邊所有數(shù)據(jù)會(huì)被返回,左邊符合條件的被返回、
select * from techer t right join course c on t．t_id = c．t_id;
FULL OUTER JOIN 滿外(全外)連接: 將會(huì)返回所有表中符合條件的所有記錄。如果任一表的指定字段沒有符合條件的值的話,那么就使用NULL值替代。
SELECT * FROM techer t FULL JOIN course c ON t．t_id = c．t_id ;

注意事項(xiàng):

hive2版本已經(jīng)支持不等值連接,就是 join on條件后面可以使用大于小于符號(hào);并且也支持 join on 條件后跟or (早前版本 on 后只支持 = 和 and,不支持 > < 和 or)如hive執(zhí)行引擎使用MapReduce,一個(gè)join就會(huì)啟動(dòng)一個(gè)job,一條sql語句中如有多個(gè)join,則會(huì)啟動(dòng)多個(gè)job

注意:表之間用逗號(hào)(,)連接和 inner join 是一樣的,例:

select tableA．id, tableB．name from tableA , tableB where tableA．id=tableB．id;  
和  
select tableA．id, tableB．name from tableA join tableB on tableA．id=tableB．id;  

它們的執(zhí)行效率沒有區(qū)別,只是書寫方式不同,用逗號(hào)是sql 89標(biāo)準(zhǔn),join 是sql 92標(biāo)準(zhǔn)。用逗號(hào)連接后面過濾條件用 where ,用 join 連接后面過濾條件是 on。

6． left semi join為什么把這個(gè)單獨(dú)拿出來說,因?yàn)樗�和其他�?join 語句不太一樣,
這個(gè)語句的作用和 in/exists 作用是一樣的,是 in/exists 更高效的實(shí)現(xiàn)
SELECT A．* FROM A where id in (select id from B)
SELECT A．* FROM A left semi join B ON A．id=B．id
上述兩個(gè) sql 語句執(zhí)行結(jié)果完全一樣,只不過第二個(gè)執(zhí)行效率高

注意事項(xiàng):

left semi join 的限制是:join 子句中右邊的表只能在 on 子句中設(shè)置過濾條件,在 where 子句、select 子句或其他地方過濾都不行。left semi join 中 on 后面的過濾條件只能是等于號(hào),不能是其他的。left semi join 是只傳遞表的 join key 給 map 階段,因此left semi join 中最后 select 的結(jié)果只許出現(xiàn)左表。因?yàn)?left semi join 是 in(keySet) 的關(guān)系,遇到右表重復(fù)記錄,左表會(huì)跳過7． 聚合函數(shù)中 null 值hive支持 count(),max(),min(),sum(),avg() 等常用的聚合函數(shù)

注意事項(xiàng):

聚合操作時(shí)要注意 null 值:

count(*) 包含 null 值,統(tǒng)計(jì)所有行數(shù);
count(id) 不包含id為 null 的值;
min 求最小值是不包含 null,除非所有值都是 null;
avg 求平均值也是不包含 null。

以上需要特別注意,null 值最容易導(dǎo)致算出錯(cuò)誤的結(jié)果

8． 運(yùn)算符中 null 值hive 中支持常用的算術(shù)運(yùn)算符(+,-,*,/)  
比較運(yùn)算符(>, <, =)
邏輯運(yùn)算符(in, not in)
以上運(yùn)算符計(jì)算時(shí)要特別注意 null 值

注意事項(xiàng):

每行中的列字段相加或相減,如果含有 null 值,則結(jié)果為 null
例:有一張商品表(product)idpricedis_amount1100202120null

各字段含義: id (商品id)、price (價(jià)格)、dis_amount (優(yōu)惠金額)

我想算每個(gè)商品優(yōu)惠后實(shí)際的價(jià)格,sql如下:

select id, price - dis_amount as real_amount from product;

得到結(jié)果如下:

idreal_amount1802null

id=2的商品價(jià)格為 null,結(jié)果是錯(cuò)誤的。

我們可以對(duì) null 值進(jìn)行處理,sql如下:

select id, price - coalesce(dis_amount,0) as real_amount from product;
使用 coalesce 函數(shù)進(jìn)行 null 值處理下,得到的結(jié)果就是準(zhǔn)確的
coalesce 函數(shù)是返回第一個(gè)不為空的值
如上sql:如果dis_amount不為空,則返回dis_amount,如果為空,則返回0

小于是不包含 null 值,如 id < 10;是不包含 id 為 null 值的。

not in 是不包含 null 值的,如 city not in ('北京','上海'),這個(gè)條件得出的結(jié)果是 city 中不包含 北京,上海和 null 的城市。

9． and 和 or

在sql語句的過濾條件或運(yùn)算中,如果有多個(gè)條件或多個(gè)運(yùn)算,我們都會(huì)考慮優(yōu)先級(jí),如乘除優(yōu)先級(jí)高于加減,乘除或者加減它們之間優(yōu)先級(jí)平等,誰在前就先算誰。那 and 和 or 呢,看似 and 和 or 優(yōu)先級(jí)平等,誰在前先算誰,但是,and 的優(yōu)先級(jí)高于 or。

注意事項(xiàng):

例:
還是一張商品表(product)

idclassifyprice1電器702電器1303電器804家具1505家具606食品120

我想要統(tǒng)計(jì)下電器或者家具這兩類中價(jià)格大于100的商品,sql如下:

select * from product where classify = '電器' or classify = '家具' and price>100

得到結(jié)果

idclassifyprice1電器702電器1303電器804家具150

結(jié)果是錯(cuò)誤的,把所有的電器類型都查詢出來了,原因就是 and 優(yōu)先級(jí)高于 or,上面的sql語句實(shí)際執(zhí)行的是,先找出 classify = '家具' and price>100 的,然后在找出 classify = '電器' 的

正確的 sql 就是加個(gè)括號(hào),先計(jì)算括號(hào)里面的:

select * from product where (classify = '電器' or classify = '家具') and price>100
最后

第一時(shí)間獲取最新大數(shù)據(jù)技術(shù),盡在本公眾號(hào):五分鐘學(xué)大數(shù)據(jù)