一文詳解Hive知識體系
2． explain dependency的用法
explain dependency用于描述一段SQL需要的數(shù)據(jù)來源,輸出是一個json格式的數(shù)據(jù),里面包含以下兩個部分的內(nèi)容:
input_partitions:描述一段SQL依賴的數(shù)據(jù)來源表分區(qū),里面存儲的是分區(qū)名的列表,如果整段SQL包含的所有表都是非分區(qū)表,則顯示為空。
input_tables:描述一段SQL依賴的數(shù)據(jù)來源表,里面存儲的是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的使用場景有兩個:
場景一:快速排除�？焖倥懦�?yàn)樽x取不到相應(yīng)分區(qū)的數(shù)據(jù)而導(dǎo)致任務(wù)數(shù)據(jù)輸出異常。例如,在一個以天分區(qū)的任務(wù)中,上游任務(wù)因?yàn)樯a(chǎn)過程不可控因素出現(xiàn)異�；蛘呖张�,導(dǎo)致下游任務(wù)引發(fā)異常。通過這種方式,可以快速查看SQL讀取的分區(qū)是否出現(xiàn)異常。
場景二:理清表的輸入,幫助理解程序的運(yùn)行,特別是有助于理解有多重子查詢,多表連接的依賴輸入。
下面通過兩個案例來看explain dependency的實(shí)際運(yùn)用:
案例一:識別看似等價的代碼
對于剛接觸SQL的程序員,很容易將
select * from a inner join b on a．no=b．no and a．f>1 and a．f<3;
等價于
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í)上述的兩個SQL并不等價,代碼1在內(nèi)連接(inner join)中的連接條件(on)中加入非等值的過濾條件后,并沒有將內(nèi)連接的左右兩個表按照過濾條件進(jìn)行過濾,內(nèi)連接在執(zhí)行時會多讀取part=0的分區(qū)數(shù)據(jù)。而在代碼2中,會過濾掉不符合條件的分區(qū)。
案例二:識別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;
以上兩個代碼的數(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個分區(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個分區(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個分區(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"}]}
可以看到,對左外連接在連接條件中加入非等值過濾的條件,如果過濾條件是作用于右表(b表)有起到過濾的效果,則右表只要掃描兩個分區(qū)即可,但是左表(a表)會進(jìn)行全表掃描。如果過濾條件是針對左表,則完全沒有起到過濾的作用,那么兩個表將進(jìn)行全表掃描。這時的情況就如同全外連接一樣都需要對兩個數(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,操作是查詢;
觀察上面的信息我們還會看到AUTHORIZATION_FAILURES信息,提示對當(dāng)前的輸入沒有查詢權(quán)限,但如果運(yùn)行上面的SQL的話也能夠正常運(yùn)行。為什么會出現(xiàn)這種情況?Hive在默認(rèn)不配置權(quán)限管理的情況下不進(jìn)行權(quán)限驗(yàn)證,所有的用戶在Hive里面都是超級管理員,即使不對特定的用戶進(jìn)行賦權(quán),也能夠正常查詢。
最后
通過上面對explain的介紹,可以發(fā)現(xiàn)explain中有很多值得我們?nèi)パ芯康膬?nèi)容,讀懂 explain 的執(zhí)行計劃有利于我們優(yōu)化Hive SQL,同時也能提升我們對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è),總共有五個組件:
UI:用戶界面�？煽醋魑覀兲峤籗QL語句的命令行界面。
DRIVER:驅(qū)動程序。接收查詢的組件。該組件實(shí)現(xiàn)了會話句柄的概念。
COMPILER:編譯器。負(fù)責(zé)將 SQL 轉(zhuǎn)化為平臺可執(zhí)行的執(zhí)行計劃。對不同的查詢塊和查詢表達(dá)式進(jìn)行語義分析,并最終借助表和從 metastore 查找的分區(qū)元數(shù)據(jù)來生成執(zhí)行計劃。
METASTORE:元數(shù)據(jù)庫。存儲 Hive 中各種表和分區(qū)的所有結(jié)構(gòu)信息。
EXECUTION ENGINE:執(zhí)行引擎。負(fù)責(zé)提交 COMPILER 階段編譯好的執(zhí)行計劃到不同的平臺上。
上圖的基本流程是:
步驟1:UI 調(diào)用 DRIVER 的接口;
步驟2:DRIVER 為查詢創(chuàng)建會話句柄,并將查詢發(fā)送到 COMPILER(編譯器)生成執(zhí)行計劃;
步驟3和4:編譯器從元數(shù)據(jù)存儲中獲取本次查詢所需要的元數(shù)據(jù),該元數(shù)據(jù)用于對查詢樹中的表達(dá)式進(jìn)行類型檢查,以及基于查詢謂詞修建分區(qū);
步驟5:編譯器生成的計劃是分階段的DAG,每個階段要么是 map/reduce 作業(yè),要么是一個元數(shù)據(jù)或者HDFS上的操作。將生成的計劃發(fā)給 DRIVER。
如果是 map/reduce 作業(yè),該計劃包括 map operator trees 和一個 reduce operator tree,執(zhí)行引擎將會把這些作業(yè)發(fā)送給 MapReduce :
步驟6、6．1、6．2和6．3:執(zhí)行引擎將這些階段提交給適當(dāng)?shù)慕M件。在每個 task(mapper/reducer) 中,從HDFS文件中讀取與表或中間輸出相關(guān)聯(lián)的數(shù)據(jù),并通過相關(guān)算子樹傳遞這些數(shù)據(jù)。最終這些數(shù)據(jù)通過序列化器寫入到一個臨時HDFS文件中(如果不需要 reduce 階段,則在 map 中操作)。臨時文件用于向計劃中后面的 map/reduce 階段提供數(shù)據(jù)。
步驟7、8和9:最終的臨時文件將移動到表的位置,確保不讀取臟數(shù)據(jù)(文件重命名在HDFS中是原子操作)。對于用戶的查詢,臨時文件的內(nèi)容由執(zhí)行引擎直接從HDFS讀取,然后通過Driver發(fā)送到UI。
Hive SQL 編譯成 MapReduce 過程
編譯 SQL 的任務(wù)是在上節(jié)中介紹的 COMPILER(編譯器組件)中完成的。Hive將SQL轉(zhuǎn)化為MapReduce任務(wù),整個編譯過程分為六個階段:
Hive SQL編譯過程詞法、語法解析: Antlr 定義 SQL 的語法規(guī)則,完成 SQL 詞法,語法解析,將 SQL 轉(zhuǎn)化為抽象語法樹 AST Tree;
Antlr是一種語言識別的工具,可以用來構(gòu)造領(lǐng)域語言。使用Antlr構(gòu)造特定的語言只需要編寫一個語法文件,定義詞法和語法替換規(guī)則即可,Antlr完成了詞法分析、語法分析、語義分析、中間代碼生成的過程。
語義解析: 遍歷 AST Tree,抽象出查詢的基本組成單元 QueryBlock;
生成邏輯執(zhí)行計劃: 遍歷 QueryBlock,翻譯為執(zhí)行操作樹 OperatorTree;
優(yōu)化邏輯執(zhí)行計劃: 邏輯層優(yōu)化器進(jìn)行 OperatorTree 變換,合并 Operator,達(dá)到減少 MapReduce Job,減少數(shù)據(jù)傳輸及 shuffle 數(shù)據(jù)量;
生成物理執(zhí)行計劃: 遍歷 OperatorTree,翻譯為 MapReduce 任務(wù);
優(yōu)化物理執(zhí)行計劃: 物理層優(yōu)化器進(jìn)行 MapReduce 任務(wù)的變換,生成最終的執(zhí)行計劃。
下面對這六個階段詳細(xì)解析:
為便于理解,我們拿一個簡單的查詢語句進(jìn)行展示,對5月23號的地區(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最基本的組成單元,包括三個部分:輸入源,計算過程,輸出。簡單來講一個QueryBlock就是一個子查詢。
QueryBlock的生成過程為一個遞歸過程,先序遍歷 AST Tree ,遇到不同的 Token 節(jié)點(diǎn)(理解為特殊標(biāo)記),保存到相應(yīng)的屬性中。
階段三:生成邏輯執(zhí)行計劃
遍歷QueryBlock,翻譯為執(zhí)行操作樹OperatorTree:
Hive最終生成的MapReduce任務(wù),Map階段和Reduce階段均由OperatorTree組成。
基本的操作符包括:
TableScanOperatorSelectOperatorFilterOperatorJoinOperatorGroupByOperatorReduceSinkOperator`
Operator在Map Reduce階段之間的數(shù)據(jù)傳遞都是一個流式的過程。每一個Operator對一行數(shù)據(jù)完成操作后之后將數(shù)據(jù)傳遞給childOperator計算。
由于Join/GroupBy/OrderBy均需要在Reduce階段完成,所以在生成相應(yīng)操作的Operator之前都會先生成一個ReduceSinkOperator,將字段組合并序列化為Reduce Key/value, Partition Key。
階段四:優(yōu)化邏輯執(zhí)行計劃
Hive中的邏輯查詢優(yōu)化可以大致分為以下幾類:
投影修剪推導(dǎo)傳遞謂詞謂詞下推將Select-Select,Filter-Filter合并為單個操作多路 Join查詢重寫以適應(yīng)某些列值的Join傾斜
階段五:生成物理執(zhí)行計劃
生成物理執(zhí)行計劃即是將邏輯執(zhí)行計劃生成的OperatorTree轉(zhuǎn)化為MapReduce Job的過程,主要分為下面幾個階段:
對輸出表生成MoveTask從OperatorTree的其中一個根節(jié)點(diǎn)向下深度優(yōu)先遍歷ReduceSinkOperator標(biāo)示Map/Reduce的界限,多個Job間的界限遍歷其他根節(jié)點(diǎn),遇過碰到JoinOperator合并MapReduceTask生成StatTask更新元數(shù)據(jù)剪斷Map與Reduce間的Operator的關(guān)系
階段六:優(yōu)化物理執(zhí)行計劃
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ù)用戶提示決定最后流哪個表刪除不必要的 ReduceSinkOperators對于帶有Limit子句的查詢,減少需要為該表掃描的文件數(shù)對于帶有Limit子句的查詢,通過限制 ReduceSinkOperator 生成的內(nèi)容來限制來自 mapper 的輸出減少用戶提交的SQL查詢所需的Tez作業(yè)數(shù)量如果是簡單的提取查詢,避免使用MapReduce作業(yè)對于帶有聚合的簡單獲取查詢,執(zhí)行不帶 MapReduce 任務(wù)的聚合重寫 Group By 查詢使用索引表代替原來的表當(dāng)表掃描之上的謂詞是相等謂詞且謂詞中的列具有索引時,使用索引掃描
經(jīng)過以上六個階段,SQL 就被解析映射成了集群上的 MapReduce 任務(wù)。
SQL編譯成MapReduce具體原理
在階段五-生成物理執(zhí)行計劃,即遍歷 OperatorTree,翻譯為 MapReduce 任務(wù),這個過程具體是怎么轉(zhuǎn)化的呢
我們接下來舉幾個常用 SQL 語句轉(zhuǎn)化為 MapReduce 的具體步驟:
Join的實(shí)現(xiàn)原理
以下面這個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)原理
以下面這個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)原理
以下面這個SQL為例,講解 distinct 的實(shí)現(xiàn):
select dealid, count(distinct uid) num from order group by dealid;
當(dāng)只有一個distinct字段時,如果不考慮Map階段的Hash GroupBy,只需要將GroupBy字段和Distinct字段組合為map輸出key,利用mapreduce的排序,同時將GroupBy字段作為reduce的key,在reduce階段保存LastKey即可完成去重:
MapReduce Distinct的實(shí)現(xiàn)九、Hive千億級數(shù)據(jù)傾斜數(shù)據(jù)傾斜問題剖析
數(shù)據(jù)傾斜是分布式系統(tǒng)不可避免的問題,任何分布式系統(tǒng)都有幾率發(fā)生數(shù)據(jù)傾斜,但有些小伙伴在平時工作中感知不是很明顯,這里要注意本篇文章的標(biāo)題—“千億級數(shù)據(jù)”,為什么說千億級,因?yàn)槿绻粋�任務(wù)的數(shù)據(jù)量只有幾百萬,它即使發(fā)生了數(shù)據(jù)傾斜,所有數(shù)據(jù)都跑到一臺機(jī)器去執(zhí)行,對于幾百萬的數(shù)據(jù)量,一臺機(jī)器執(zhí)行起來還是毫無壓力的,這時數(shù)據(jù)傾斜對我們感知不大,只有數(shù)據(jù)達(dá)到一個量級時,一臺機(jī)器應(yīng)付不了這么多的數(shù)據(jù),這時如果發(fā)生數(shù)據(jù)傾斜,那么最后就很難算出結(jié)果。
所以就需要我們對數(shù)據(jù)傾斜的問題進(jìn)行優(yōu)化,盡量避免或減輕數(shù)據(jù)傾斜帶來的影響。
在解決數(shù)據(jù)傾斜問題之前,還要再提一句:沒有瓶頸時談?wù)搩?yōu)化,都是自尋煩惱。
大家想想,在map和reduce兩個階段中,最容易出現(xiàn)數(shù)據(jù)傾斜的就是reduce階段,因?yàn)閙ap到reduce會經(jīng)過shuffle階段,在shuffle中默認(rèn)會按照key進(jìn)行hash,如果相同的key過多,那么hash的結(jié)果就是大量相同的key進(jìn)入到同一個reduce中,導(dǎo)致數(shù)據(jù)傾斜。
那么有沒有可能在map階段就發(fā)生數(shù)據(jù)傾斜呢,是有這種可能的。
一個任務(wù)中,數(shù)據(jù)文件在進(jìn)入map階段之前會進(jìn)行切分,默認(rèn)是128M一個數(shù)據(jù)塊,但是如果當(dāng)對文件使用GZIP壓縮等不支持文件分割操作的壓縮方式時,MR任務(wù)讀取壓縮后的文件時,是對它切分不了的,該壓縮文件只會被一個任務(wù)所讀取,如果有一個超大的不可切分的壓縮文件被一個map讀取時,就會發(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ù)參與到計算作業(yè)中,表中有大量的null值,如果表之間進(jìn)行join操作,就會有shuffle產(chǎn)生,這樣所有的null值都會被分配到一個reduce中,必然產(chǎn)生數(shù)據(jù)傾斜。
之前有小伙伴問,如果A、B兩表join操作,假如A表中需要join的字段為null,但是B表中需要join的字段不為null,這兩個字段根本就join不上啊,為什么還會放到一個reduce中呢?
這里我們需要明確一個概念,數(shù)據(jù)放到同一個reduce中的原因不是因?yàn)樽侄文懿荒躩oin上,而是因?yàn)閟huffle階段的hash操作,只要key的hash結(jié)果是一樣的,它們就會被拉到同一個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時的hash結(jié)果是一樣的,那么我們可以給null值隨機(jī)賦值,這樣它們的hash結(jié)果就不一樣,就會進(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ù)傾斜
對于兩個表join,表a中需要join的字段key為int,表b中key字段既有string類型也有int類型。當(dāng)按照key進(jìn)行兩個表的join操作時,默認(rèn)的Hash操作會按int型的id來進(jìn)行分配,這樣所有的string類型都被分配成同一個id,結(jié)果就是所有的string類型的字段進(jìn)入到一個reduce中,引發(fā)數(shù)據(jù)傾斜。
解決方案:
如果key字段既有string類型也有int類型,默認(rèn)的hash就都會按int類型來分配,那我們直接把int類型都轉(zhuǎn)為string就好了,這樣key字段都為string,hash時就按照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)儲,這時候往往會對數(shù)據(jù)進(jìn)行壓縮;當(dāng)對文件使用GZIP壓縮等不支持文件分割操作的壓縮方式,在日后有作業(yè)涉及讀取壓縮后的文件時,該壓縮文件只會被一個任務(wù)所讀取。如果該壓縮文件很大,則處理該文件的Map需要花費(fèi)的時間會遠(yuǎn)多于讀取普通文件的Map時間,該Map任務(wù)會成為作業(yè)運(yùn)行的瓶頸。這種情況也就是Map讀取文件的數(shù)據(jù)傾斜。
解決方案:
這種數(shù)據(jù)傾斜問題沒有什么好的解決方案,只能將使用GZIP壓縮等不支持文件分割的文件轉(zhuǎn)為bzip和zip等支持文件分割的壓縮方式。
所以,我們在對文件進(jìn)行壓縮時,為避免因不可拆分大文件而引發(fā)數(shù)據(jù)讀取的傾斜,在數(shù)據(jù)壓縮的時候可以采用bzip2和Zip等支持文件分割的壓縮算法。
4． 數(shù)據(jù)膨脹引發(fā)的數(shù)據(jù)傾斜
在多維聚合計算時,如果進(jìn)行分組聚合的字段過多,如下:
select a,b,c,count(1)from log group by a,b,c with rollup;
注:對于最后的with rollup關(guān)鍵字不知道大家用過沒,with rollup是用來在分組統(tǒng)計數(shù)據(jù)的基礎(chǔ)上再進(jìn)行統(tǒng)計匯總,即用來得到group by的匯總信息。
如果上面的log表的數(shù)據(jù)量很大,并且Map端的聚合不能很好地起到數(shù)據(jù)壓縮的情況下,會導(dǎo)致Map端產(chǎn)出的數(shù)據(jù)急速膨脹,這種情況容易導(dǎo)致作業(yè)內(nèi)存溢出的異常。如果log表含有數(shù)據(jù)傾斜key,會加劇Shuffle過程的數(shù)據(jù)傾斜。
解決方案:
可以拆分上面的sql,將with rollup拆分成如下幾個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)在是對3個字段進(jìn)行分組聚合,那如果是5個或者10個字段呢,那么需要拆解的SQL語句會更多。
在Hive中可以通過參數(shù) hive．new．job．grouping．set．cardinality 配置的方式自動控制作業(yè)的拆解,該參數(shù)默認(rèn)值是30。表示針對grouping sets/rollups/cubes這類多維聚合的操作,如果最后拆解的鍵組合大于該值,會啟用新的任務(wù)去處理大于該值之外的組合。如果在處理數(shù)據(jù)時,某個分組聚合的列有較大的傾斜,可以適當(dāng)調(diào)小該值。
5． 表連接時引發(fā)的數(shù)據(jù)傾斜
兩表進(jìn)行普通的repartition join時,如果表連接的鍵存在傾斜,那么在 Shuffle 階段必然會引起數(shù)據(jù)傾斜。
解決方案:
通常做法是將傾斜的數(shù)據(jù)存到分布式緩存中,分發(fā)到各個 Map任務(wù)所在節(jié)點(diǎn)。在Map階段完成join操作,即MapJoin,這避免了 Shuffle,從而避免了數(shù)據(jù)傾斜。
MapJoin是Hive的一種優(yōu)化操作,其適用于小表JOIN大表的場景,由于表的JOIN操作是在Map端且在內(nèi)存進(jìn)行的,所以其并不需要啟動Reduce任務(wù)也就不需要經(jīng)過shuffle階段,從而能在一定程度上節(jié)省資源提高JOIN效率。
在Hive 0．11版本之前,如果想在Map階段完成join操作,必須使用MAPJOIN來標(biāo)記顯示地啟動該優(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;
如果想將多個表放到Map端內(nèi)存中,只需在mapjoin()中寫多個表名稱即可,用逗號分隔,如將a表和c表放到Map端內(nèi)存中,則 +mapjoin(a,c) 。
在Hive 0．11版本及之后,Hive默認(rèn)啟動該優(yōu)化,也就是不在需要顯示的使用MAPJOIN標(biāo)記,其會在必要的時候觸發(fā)該優(yōu)化操作將普通JOIN轉(zhuǎn)換成MapJoin,可以通過以下兩個屬性來設(shè)置該優(yōu)化的觸發(fā)時機(jī):
hive．a(chǎn)uto．convert．join=true 默認(rèn)值為true,自動開啟MAPJOIN優(yōu)化。
hive．mapjoin．smalltable．filesize=2500000 默認(rèn)值為2500000(25M),通過配置該屬性來確定使用該優(yōu)化的表的大小,如果表的大小小于此值就會被加載進(jìn)內(nèi)存中。
注意:使用默認(rèn)啟動該優(yōu)化的方式如果出現(xiàn)莫名其妙的BUG(比如MAPJOIN并不起作用),就將以下兩個屬性置為fase手動使用MAPJOIN標(biāo)記來啟動該優(yōu)化:
hive．a(chǎn)uto．convert．join=false (關(guān)閉自動MAPJOIN轉(zhuǎn)換操作)
hive．ignore．mapjoin．hint=false (不忽略MAPJOIN標(biāo)記)
再提一句:將表放到Map端內(nèi)存時,如果節(jié)點(diǎn)的內(nèi)存很大,但還是出現(xiàn)內(nèi)存溢出的情況,我們可以通過這個參數(shù) mapreduce．map．memory．mb 調(diào)節(jié)Map端內(nèi)存的大小。
6． 確實(shí)無法減少數(shù)據(jù)量引發(fā)的數(shù)據(jù)傾斜
在一些操作中,我們沒有辦法減少數(shù)據(jù)量,如在使用 collect_list 函數(shù)時:
select s_age,collect_list(s_score) list_score
from student
group by s_age
collect_list:將分組中的某列轉(zhuǎn)為一個數(shù)組返回。
在上述sql中,s_age有數(shù)據(jù)傾斜,但如果數(shù)據(jù)量大到一定的數(shù)量,會導(dǎo)致處理傾斜的Reduce任務(wù)產(chǎn)生內(nèi)存溢出的異常。
collect_list輸出一個數(shù)組,中間結(jié)果會放到內(nèi)存中,所以如果collect_list聚合太多數(shù)據(jù),會導(dǎo)致內(nèi)存溢出。
有小伙伴說這是 group by 分組引起的數(shù)據(jù)傾斜,可以開啟hive．groupby．skewindata參數(shù)來優(yōu)化。我們接下來分析下:
開啟該配置會將作業(yè)拆解成兩個作業(yè),第一個作業(yè)會盡可能將Map的數(shù)據(jù)平均分配到Reduce階段,并在這個階段實(shí)現(xiàn)數(shù)據(jù)的預(yù)聚合,以減少第二個作業(yè)處理的數(shù)據(jù)量;第二個作業(yè)在第一個作業(yè)處理的數(shù)據(jù)基礎(chǔ)上進(jìn)行結(jié)果的聚合。
hive．groupby．skewindata的核心作用在于生成的第一個作業(yè)能夠有效減少數(shù)量。但是對于collect_list這類要求全量操作所有數(shù)據(jù)的中間結(jié)果的函數(shù)來說,明顯起不到作用,反而因?yàn)橐胄碌淖鳂I(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這個配置。
總結(jié)
通過上面的內(nèi)容我們發(fā)現(xiàn),shuffle階段堪稱性能的殺手,為什么這么說,一方面shuffle階段是最容易引起數(shù)據(jù)傾斜的;另一方面shuffle的過程中會產(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 ,但是對內(nèi)存有要求,對GC 有壓力;Buffer 設(shè)置得小,可能不占用那么多內(nèi)存, 但是可能頻繁的磁盤I/O 、頻繁的網(wǎng)絡(luò)I/O 。十、Hive企業(yè)級性能優(yōu)化Hive性能問題排查的方式
當(dāng)我們發(fā)現(xiàn)一條SQL語句執(zhí)行時間過長或者不合理時,我們就要考慮對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í)行計劃。如Oracle數(shù)據(jù)庫,它有多種類型的執(zhí)行計劃,通過多種執(zhí)行計劃的配合使用,可以看到根據(jù)統(tǒng)計信息推演的執(zhí)行計劃,即Oracle推斷出來的未真正運(yùn)行的執(zhí)行計劃;還可以看到實(shí)際執(zhí)行任務(wù)的執(zhí)行計劃;能夠觀察到從數(shù)據(jù)讀取到最終呈現(xiàn)的主要過程和中間的量化數(shù)據(jù)�？梢哉f,在Oracle開發(fā)領(lǐng)域,掌握合適的環(huán)節(jié),選用不同的執(zhí)行計劃,SQL調(diào)優(yōu)就不是一件難事。
Hive中也有執(zhí)行計劃,但是Hive的執(zhí)行計劃都是預(yù)測的,這點(diǎn)不像Oracle和SQL Server有真實(shí)的計劃,可以看到每個階段的處理數(shù)據(jù)、消耗的資源和處理的時間等量化數(shù)據(jù)。Hive提供的執(zhí)行計劃沒有這些數(shù)據(jù),這意味著雖然Hive的使用者知道整個SQL的執(zhí)行邏輯,但是各階段耗用的資源狀況和整個SQL的執(zhí)行瓶頸在哪里是不清楚的。
想要知道HiveSQL所有階段的運(yùn)行信息,可以查看YARN提供的日志。查看日志的鏈接,可以在每個作業(yè)執(zhí)行后,在控制臺打印的信息中找到。如下圖所示:
Hive提供的執(zhí)行計劃目前可以查看的信息有以下幾種:
查看執(zhí)行計劃的基本信息,即explain;查看執(zhí)行計劃的擴(kuò)展信息,即explain extended;查看SQL數(shù)據(jù)輸入依賴的信息,即explain dependency;查看SQL操作相關(guān)權(quán)限的信息,即explain authorization;查看SQL的向量化描述信息,即explain vectorization。
在查詢語句的SQL前面加上關(guān)鍵字explain是查看執(zhí)行計劃的基本方法。用explain打開的執(zhí)行計劃包含以下兩部分:
作業(yè)的依賴關(guān)系圖,即STAGE DEPENDENCIES;每個作業(yè)的詳細(xì)信息,即STAGE PLANS。
Hive中的explain執(zhí)行計劃詳解可看我之前寫的這篇文章:
Hive底層原理:explain執(zhí)行計劃詳解
注:使用explain查看執(zhí)行計劃是Hive性能調(diào)優(yōu)中非常重要的一種方式,請務(wù)必掌握!
總結(jié):Hive對SQL語句性能問題排查的方式:
使用explain查看執(zhí)行計劃;查看YARN提供的日志。Hive性能調(diào)優(yōu)的方式
為什么都說性能優(yōu)化這項(xiàng)工作是比較難的,因?yàn)橐豁?xiàng)技術(shù)的優(yōu)化,必然是一項(xiàng)綜合性的工作,它是多門技術(shù)的結(jié)合。我們?nèi)绻痪窒抻谝环N技術(shù),那么肯定做不好優(yōu)化的。
下面將從多個完全不同的角度來介紹Hive優(yōu)化的多樣性,我們先來一起感受下。
1． SQL語句優(yōu)化
SQL語句優(yōu)化涉及到的內(nèi)容太多,因篇幅有限,不能一一介紹到,所以就拿幾個典型舉例,讓大家學(xué)到這種思想,以后遇到類似調(diào)優(yōu)問題可以往這幾個方面多思考下。
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;
我們簡單分析上面的SQl語句,就是將每個年齡的最大和最小的生日獲取出來放到同一張表中,union all 前后的兩個語句都是對同一張表按照s_age進(jìn)行分組,然后分別取最大值和最小值。對同一張表相同的字段進(jìn)行兩次分組,這造成了極大浪費(fèi),我們能不能改造下呢,當(dāng)然是可以的,為大家介紹一個語法:from ．．． insert into ．．． ,這個語法將from前置,作用就是使用一張表,可以進(jìn)行多次插入操作:
--開啟動態(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就可以對stu_ori表的s_age字段分組一次而進(jìn)行兩次不同的插入操作。
這個例子告訴我們一定要多了解SQL語句,如果我們不知道這種語法,一定不會想到這種方式的。
2． distinct
先看一個SQL,去重計數(shù):
select count(1)
from(
select s_age
from stu
group by s_age
) b;
這是簡單統(tǒng)計年齡的枚舉值個數(shù),為什么不用distinct?
select count(distinct s_age)
from stu;
有人說因?yàn)樵跀?shù)據(jù)量特別大的情況下使用第一種方式能夠有效避免Reduce端的數(shù)據(jù)傾斜,但是事實(shí)如此嗎?
我們先不管數(shù)據(jù)量特別大這個問題,就當(dāng)前的業(yè)務(wù)和環(huán)境下使用distinct一定會比上面那種子查詢的方式效率高。原因有以下幾點(diǎn):
上面進(jìn)行去重的字段是年齡字段,要知道年齡的枚舉值是非常有限的,就算計算1歲到100歲之間的年齡,s_age的最大枚舉值才是100,如果轉(zhuǎn)化成MapReduce來解釋的話,在Map階段,每個Map會對s_age去重。由于s_age枚舉值有限,因而每個Map得到的s_age也有限,最終得到reduce的數(shù)據(jù)量也就是map數(shù)量*s_age枚舉值的個數(shù)。
distinct的命令會在內(nèi)存中構(gòu)建一個hashtable,查找去重的時間復(fù)雜度是O(1);group by在不同版本間變動比較大,有的版本會用構(gòu)建hashtable的形式去重,有的版本會通過排序的方式, 排序最優(yōu)時間復(fù)雜度無法到O(1)。另外,第一種方式(group by)去重會轉(zhuǎn)化為兩個任務(wù),會消耗更多的磁盤網(wǎng)絡(luò)I/O資源。
最新的Hive 3．0中新增了 count(distinct ) 優(yōu)化,通過配置 hive．optimize．countdistinct,即使真的出現(xiàn)數(shù)據(jù)傾斜也可以自動優(yōu)化,自動改變SQL執(zhí)行的邏輯。
第二種方式(distinct)比第一種方式(group by)代碼簡潔,表達(dá)的意思簡單明了,如果沒有特殊的問題,代碼簡潔就是優(yōu)!
這個例子告訴我們,有時候我們不要過度優(yōu)化,調(diào)優(yōu)講究適時調(diào)優(yōu),過早進(jìn)行調(diào)優(yōu)有可能做的是無用功甚至產(chǎn)生負(fù)效應(yīng),在調(diào)優(yōu)上投入的工作成本和回報不成正比。調(diào)優(yōu)需要遵循一定的原則。
2． 數(shù)據(jù)格式優(yōu)化
Hive提供了多種數(shù)據(jù)存儲組織格式,不同格式對程序的運(yùn)行效率也會有極大的影響。
Hive提供的格式有TEXT、SequenceFile、RCFile、ORC和Parquet等。
SequenceFile是一個二進(jìn)制key/value對結(jié)構(gòu)的平面文件,在早期的Hadoop平臺上被廣泛用于MapReduce輸出/輸出格式,以及作為數(shù)據(jù)存儲格式。
Parquet是一種列式數(shù)據(jù)存儲格式,可以兼容多種計算引擎,如MapRedcue和Spark等,對多層嵌套的數(shù)據(jù)結(jié)構(gòu)提供了良好的性能支持,是目前Hive生產(chǎn)環(huán)境中數(shù)據(jù)存儲的主流選擇之一。
ORC優(yōu)化是對RCFile的一種優(yōu)化,它提供了一種高效的方式來存儲Hive數(shù)據(jù),同時也能夠提高Hive的讀取、寫入和處理數(shù)據(jù)的性能,能夠兼容多種計算引擎。事實(shí)上,在實(shí)際的生產(chǎn)環(huán)境中,ORC已經(jīng)成為了Hive在數(shù)據(jù)存儲上的主流選擇之一。
我們使用同樣數(shù)據(jù)及SQL語句,只是數(shù)據(jù)存儲格式不同,得到如下執(zhí)行時長:
數(shù)據(jù)格式CPU時間用戶等待耗時TextFile33分171秒SequenceFile38分162秒Parquet2分22秒50秒ORC1分52秒56秒
注:CPU時間:表示運(yùn)行程序所占用服務(wù)器CPU資源的時間。
用戶等待耗時:記錄的是用戶從提交作業(yè)到返回結(jié)果期間用戶等待的所有時間。
查詢TextFile類型的數(shù)據(jù)表耗時33分鐘, 查詢ORC類型的表耗時1分52秒,時間得以極大縮短,可見不同的數(shù)據(jù)存儲格式也能給HiveSQL性能帶來極大的影響。
3． 小文件過多優(yōu)化
小文件如果過多,對 hive 來說,在進(jìn)行查詢時,每個小文件都會當(dāng)成一個塊,啟動一個Map任務(wù)來完成,而一個Map任務(wù)啟動和初始化的時間遠(yuǎn)遠(yuǎn)大于邏輯處理的時間,就會造成很大的資源浪費(fèi)。而且,同時可執(zhí)行的Map數(shù)量是受限的。
所以我們有必要對小文件過多進(jìn)行優(yōu)化,關(guān)于小文件過多的解決的辦法,我之前專門寫了一篇文章講解,具體可查看:
解決hive小文件過多問題
4． 并行執(zhí)行優(yōu)化
Hive會將一個查詢轉(zhuǎn)化成一個或者多個階段。這樣的階段可以是MapReduce階段、抽樣階段、合并階段、limit階段。或者Hive執(zhí)行過程中可能需要的其他階段。默認(rèn)情況下,Hive一次只會執(zhí)行一個階段。不過,某個特定的job可能包含眾多的階段,而這些階段可能并非完全互相依賴的,也就是說有些階段是可以并行執(zhí)行的,這樣可能使得整個job的執(zhí)行時間縮短。如果有更多的階段可以并行執(zhí)行,那么job可能就越快完成。
通過設(shè)置參數(shù)hive．exec．parallel值為true,就可以開啟并發(fā)執(zhí)行。在共享集群中,需要注意下,如果job中并行階段增多,那么集群利用率就會增加。
set hive．exec．parallel=true; //打開任務(wù)并行執(zhí)行
set hive．exec．parallel．thread．number=16; //同一個sql允許最大并行度,默認(rèn)為8。
當(dāng)然得是在系統(tǒng)資源比較空閑的時候才有優(yōu)勢,否則沒資源,并行也起不來。
5． JVM優(yōu)化
JVM重用是Hadoop調(diào)優(yōu)參數(shù)的內(nèi)容,其對Hive的性能具有非常大的影響,特別是對于很難避免小文件的場景或task特別多的場景,這類場景大多數(shù)執(zhí)行時間都很短。
Hadoop的默認(rèn)配置通常是使用派生JVM來執(zhí)行map和Reduce任務(wù)的。這時JVM的啟動過程可能會造成相當(dāng)大的開銷,尤其是執(zhí)行的job包含有成百上千task任務(wù)的情況。JVM重用可以使得JVM實(shí)例在同一個job中重新使用N次。N的值可以在Hadoop的mapred-site．xml文件中進(jìn)行配置。通常在10-20之間,具體多少需要根據(jù)具體業(yè)務(wù)場景測試得出。
我們也可以在hive中設(shè)置
set mapred．job．reuse．jvm．num．tasks=10; //這個設(shè)置來設(shè)置我們的jvm重用
這個功能的缺點(diǎn)是,開啟JVM重用將一直占用使用到的task插槽,以便進(jìn)行重用,直到任務(wù)完成后才能釋放。如果某個“不平衡的”job中有某幾個reduce task執(zhí)行的時間要比其他Reduce task消耗的時間多的多的話,那么保留的插槽就會一直空閑著卻無法被其他的job使用,直到所有的task都結(jié)束了才會釋放。
6． 推測執(zhí)行優(yōu)化
在分布式集群環(huán)境下,因?yàn)槌绦駼ug(包括Hadoop本身的bug),負(fù)載不均衡或者資源分布不均等原因,會造成同一個作業(yè)的多個任務(wù)之間運(yùn)行速度不一致,有些任務(wù)的運(yùn)行速度可能明顯慢于其他任務(wù)(比如一個作業(yè)的某個任務(wù)進(jìn)度只有50%,而其他所有任務(wù)已經(jīng)運(yùn)行完畢),則這些任務(wù)會拖慢作業(yè)的整體執(zhí)行進(jìn)度。為了避免這種情況發(fā)生,Hadoop采用了推測執(zhí)行(Speculative Execution)機(jī)制,它根據(jù)一定的法則推測出“拖后腿”的任務(wù),并為這樣的任務(wù)啟動一個備份任務(wù),讓該任務(wù)與原始任務(wù)同時處理同一份數(shù)據(jù),并最終選用最先成功運(yùn)行完成任務(wù)的計算結(jié)果作為最終結(jié)果。
設(shè)置開啟推測執(zhí)行參數(shù):Hadoop的mapred-site．xml文件中進(jìn)行配置:
hive本身也提供了配置項(xiàng)來控制reduce-side的推測執(zhí)行:
set hive．mapred．reduce．tasks．speculative．execution=true
關(guān)于調(diào)優(yōu)這些推測執(zhí)行變量,還很難給一個具體的建議。如果用戶對于運(yùn)行時的偏差非常敏感的話,那么可以將這些功能關(guān)閉掉。如果用戶因?yàn)檩斎霐?shù)據(jù)量很大而需要執(zhí)行長時間的map或者Reduce task的話,那么啟動推測執(zhí)行造成的浪費(fèi)是非常巨大大。
最后
代碼優(yōu)化原則:
理透需求原則,這是優(yōu)化的根本;把握數(shù)據(jù)全鏈路原則,這是優(yōu)化的脈絡(luò);堅持代碼的簡潔原則,這讓優(yōu)化更加簡單;沒有瓶頸時談?wù)搩?yōu)化,這是自尋煩惱。十一、Hive大廠面試真題1． hive內(nèi)部表和外部表的區(qū)別
未被external修飾的是內(nèi)部表,被external修飾的為外部表。
本文首發(fā)于公眾號【五分鐘學(xué)大數(shù)據(jù)】,關(guān)注公眾號,獲取最新大數(shù)據(jù)技術(shù)文章
區(qū)別:
內(nèi)部表數(shù)據(jù)由Hive自身管理,外部表數(shù)據(jù)由HDFS管理;
內(nèi)部表數(shù)據(jù)存儲的位置是hive．metastore．warehouse．dir(默認(rèn):/user/hive/warehouse),外部表數(shù)據(jù)的存儲位置由自己制定(如果沒有LOCATION,Hive將在HDFS上的/user/hive/warehouse文件夾下以外部表的表名創(chuàng)建一個文件夾,并將屬于這個表的數(shù)據(jù)存放在這里);
刪除內(nèi)部表會直接刪除元數(shù)據(jù)(metadata)及存儲數(shù)據(jù);刪除外部表僅僅會刪除元數(shù)據(jù),HDFS上的文件并不會被刪除。
本文首發(fā)于公眾號【五分鐘學(xué)大數(shù)據(jù)】
2． Hive有索引嗎
Hive支持索引(3．0版本之前),但是Hive的索引與關(guān)系型數(shù)據(jù)庫中的索引并不相同,比如,Hive不支持主鍵或者外鍵。并且Hive索引提供的功能很有限,效率也并不高,因此Hive索引很少使用。
索引適用的場景:
適用于不更新的靜態(tài)字段。以免總是重建索引數(shù)據(jù)。每次建立、更新數(shù)據(jù)后,都要重建索引以構(gòu)建索引表。
Hive索引的機(jī)制如下:
hive在指定列上建立索引,會產(chǎn)生一張索引表(Hive的一張物理表),里面的字段包括:索引列的值、該值對應(yīng)的HDFS文件路徑、該值在文件中的偏移量。
Hive 0．8版本后引入bitmap索引處理器,這個處理器適用于去重后,值較少的列(例如,某字段的取值只可能是幾個枚舉值)因?yàn)樗饕怯每臻g換時間,索引列的取值過多會導(dǎo)致建立bitmap索引表過大。
注意:Hive中每次有數(shù)據(jù)時需要及時更新索引,相當(dāng)于重建一個新表,否則會影響數(shù)據(jù)查詢的效率和準(zhǔn)確性,Hive官方文檔已經(jīng)明確表示Hive的索引不推薦被使用,在新版本的Hive中已經(jīng)被廢棄了。
擴(kuò)展:Hive是在0．7版本之后支持索引的,在0．8版本后引入bitmap索引處理器,在3．0版本開始移除索引的功能,取而代之的是2．3版本開始的物化視圖,自動重寫的物化視圖替代了索引的功能。
3． 運(yùn)維如何對hive進(jìn)行調(diào)度
將hive的sql定義在腳本當(dāng)中;
使用azkaban或者oozie進(jìn)行任務(wù)的調(diào)度;
監(jiān)控任務(wù)調(diào)度頁面。
4． ORC、Parquet等列式存儲的優(yōu)點(diǎn)
ORC和Parquet都是高性能的存儲方式,這兩種存儲格式總會帶來存儲和性能上的提升。
Parquet:
Parquet支持嵌套的數(shù)據(jù)模型,類似于Protocol Buffers,每一個數(shù)據(jù)模型的schema包含多個字段,每一個字段有三個屬性:重復(fù)次數(shù)、數(shù)據(jù)類型和字段名。
重復(fù)次數(shù)可以是以下三種:required(只出現(xiàn)1次),repeated(出現(xiàn)0次或多次),optional(出現(xiàn)0次或1次)。每一個字段的數(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ù)一個類似的樹狀結(jié)可能會占用較大的存儲空間,因此Dremel論文中提出了一種高效的對于嵌套數(shù)據(jù)格式的壓縮算法:Striping/Assembly算法。通過Striping/Assembly算法,parquet可以使用較少的存儲空間表示復(fù)雜的嵌套格式,并且通常Repetition level和Definition level都是較小的整數(shù)值,可以通過RLE算法對其進(jìn)行壓縮,進(jìn)一步降低存儲空間。
Parquet文件是以二進(jìn)制方式存儲的,是不可以直接讀取和修改的,Parquet文件是自解析的,文件中包括該文件的數(shù)據(jù)和元數(shù)據(jù)。
ORC:
ORC文件是自描述的,它的元數(shù)據(jù)使用Protocol Buffers序列化,并且文件中的數(shù)據(jù)盡可能的壓縮以降低存儲空間的消耗。
和Parquet類似,ORC文件也是以二進(jìn)制方式存儲的,所以是不可以直接讀取,ORC文件也是自解析的,它包含許多的元數(shù)據(jù),這些元數(shù)據(jù)都是同構(gòu)ProtoBuffer進(jìn)行序列化的。
ORC會盡可能合并多個離散的區(qū)間盡可能的減少I/O次數(shù)。
ORC中使用了更加精確的索引信息,使得在讀取數(shù)據(jù)時可以指定從任意一行開始讀取,更細(xì)粒度的統(tǒng)計信息使得讀取ORC文件跳過整個row group,ORC默認(rèn)會對任何一塊數(shù)據(jù)和索引信息使用ZLIB壓縮,因此ORC文件占用的存儲空間也更小。
在新版本的ORC中也加入了對Bloom Filter的支持,它可以進(jìn)一步提升謂詞下推的效率,在Hive 1．2．0版本以后也加入了對此的支持。
5． 數(shù)據(jù)建模用的哪些模型?1． 星型模型
星形模式
星形模式(Star Schema)是最常用的維度建模方式。星型模式是以事實(shí)表為中心,所有的維度表直接連接在事實(shí)表上,像星星一樣。星形模式的維度建模由一個事實(shí)表和一組維表成,且具有以下特點(diǎn):
a． 維表只和事實(shí)表關(guān)聯(lián),維表之間沒有關(guān)聯(lián);
b． 每個維表主鍵為單列,且該主鍵放置在事實(shí)表中,作為兩邊連接的外鍵;
c． 以事實(shí)表為核心,維表圍繞核心呈星形分布。
2． 雪花模型
雪花模式
雪花模式(Snowflake Schema)是對星形模式的擴(kuò)展。雪花模式的維度表可以擁有其他維度表的,雖然這種模型相比星型更規(guī)范一些,但是由于這種模型不太容易理解,維護(hù)成本比較高,而且性能方面需要關(guān)聯(lián)多層維表,性能比星型模型要低。
3． 星座模型
星座模型
星座模式是星型模式延伸而來,星型模式是基于一張事實(shí)表的,而星座模式是基于多張事實(shí)表的,而且共享維度信息。前面介紹的兩種維度建模方法都是多維表對應(yīng)單事實(shí)表,但在很多時候維度空間內(nèi)的事實(shí)表不止一個,而一個維表也可能被多個事實(shí)表用到。在業(yè)務(wù)發(fā)展后期,絕大部分維度建模都采用的是星座模式。
數(shù)倉建模詳細(xì)介紹可查看:通俗易懂?dāng)?shù)倉建模
6． 為什么要對數(shù)據(jù)倉庫分層?
用空間換時間,通過大量的預(yù)處理來提升應(yīng)用系統(tǒng)的用戶體驗(yàn)(效率),因此數(shù)據(jù)倉庫會存在大量冗余的數(shù)據(jù)。
如果不分層的話,如果源業(yè)務(wù)系統(tǒng)的業(yè)務(wù)規(guī)則發(fā)生變化將會影響整個數(shù)據(jù)清洗過程,工作量巨大。
通過數(shù)據(jù)分層管理可以簡化數(shù)據(jù)清洗的過程,因?yàn)榘言瓉硪徊降墓ぷ鞣值搅硕鄠�步驟去完成,相當(dāng)于把一個復(fù)雜的工作拆成了多個簡單的工作,把一個大的黑盒變成了一個白盒,每一層的處理邏輯都相對簡單和容易理解,這樣我們比較容易保證每一個步驟的正確性,當(dāng)數(shù)據(jù)發(fā)生錯誤的時候,往往我們只需要局部調(diào)整某個步驟即可。
數(shù)據(jù)倉庫詳細(xì)介紹可查看:萬字詳解整個數(shù)據(jù)倉庫建設(shè)體系
7． 使用過Hive解析JSON串嗎
Hive處理json數(shù)據(jù)總體來說有兩個方向的路走:
將json以字符串的方式整個入Hive表,然后通過使用UDF函數(shù)解析已經(jīng)導(dǎo)入到hive中的數(shù)據(jù),比如使用LATERAL VIEW json_tuple的方法,獲取所需要的列名。
在導(dǎo)入之前將json拆成各個字段,導(dǎo)入Hive表的數(shù)據(jù)是已經(jīng)解析過的。這將需要使用第三方的SerDe。
詳細(xì)介紹可查看:Hive解析Json數(shù)組超全講解
8． sort by 和 order by 的區(qū)別
order by 會對輸入做全局排序,因此只有一個reducer(多個reducer無法保證全局有序)只有一個reducer,會導(dǎo)致當(dāng)輸入規(guī)模較大時,需要較長的計算時間。
sort by不是全局排序,其在數(shù)據(jù)進(jìn)入reducer前完成排序．因此,如果用sort by進(jìn)行排序,并且設(shè)置mapred．reduce．tasks>1, 則sort by只保證每個reducer的輸出有序,不保證全局有序。
9． 數(shù)據(jù)傾斜怎么解決
數(shù)據(jù)傾斜問題主要有以下幾種:
空值引發(fā)的數(shù)據(jù)傾斜
不同數(shù)據(jù)類型引發(fā)的數(shù)據(jù)傾斜
不可拆分大文件引發(fā)的數(shù)據(jù)傾斜
數(shù)據(jù)膨脹引發(fā)的數(shù)據(jù)傾斜
表連接時引發(fā)的數(shù)據(jù)傾斜
確實(shí)無法減少數(shù)據(jù)量引發(fā)的數(shù)據(jù)傾斜
以上傾斜問題的具體解決方案可查看:Hive千億級數(shù)據(jù)傾斜解決方案
注意:對于 left join 或者 right join 來說,不會對關(guān)聯(lián)的字段自動去除null值,對于 inner join 來說,會對關(guān)聯(lián)的字段自動去除null值。
小伙伴們在閱讀時注意下,在上面的文章(Hive千億級數(shù)據(jù)傾斜解決方案)中,有一處sql出現(xiàn)了上述問題(舉例的時候原本是想使用left join的,結(jié)果手誤寫成了join)。此問題由公眾號讀者發(fā)現(xiàn),感謝這位讀者指正。
10． Hive 小文件過多怎么解決1． 使用 hive 自帶的 concatenate 命令,自動合并小文件
使用方法:
#對于非分區(qū)表
alter table A concatenate;
#對于分區(qū)表
alter table B partition(day=20201224) concatenate;
注意:
1、concatenate 命令只支持 RCFILE 和 ORC 文件類型。
2、使用concatenate命令合并小文件時不能指定合并后的文件數(shù)量,但可以多次執(zhí)行該命令。
3、當(dāng)多次使用concatenate后文件數(shù)量不在變化,這個跟參數(shù) mapreduce．input．fileinputformat．split．minsize=256mb 的設(shè)置有關(guān),可設(shè)定每個文件的最小size。
2． 調(diào)整參數(shù)減少M(fèi)ap數(shù)量
設(shè)置map輸入合并小文件的相關(guān)參數(shù)(執(zhí)行Map前進(jìn)行小文件合并):
在mapper中將多個文件合成一個split作為輸入(CombineHiveInputFormat底層是Hadoop的CombineFileInputFormat方法):
set hive．input．format=org．a(chǎn)pache．hadoop．hive．ql．io．CombineHiveInputFormat; -- 默認(rèn)
每個Map最大輸入大小(這個值決定了合并后文件的數(shù)量):
set mapred．max．split．size=256000000; -- 256M
一個節(jié)點(diǎn)上split的至少大小(這個值決定了多個DataNode上的文件是否需要合并):
set mapred．min．split．size．per．node=100000000; -- 100M
一個交換機(jī)下split的至少大小(這個值決定了多個交換機(jī)上的文件是否需要合并):
set mapred．min．split．size．per．rack=100000000; -- 100M
3． 減少Reduce的數(shù)量
reduce 的個數(shù)決定了輸出的文件的個數(shù),所以可以調(diào)整reduce的個數(shù)控制hive表的文件數(shù)量。
hive中的分區(qū)函數(shù) distribute by 正好是控制MR中partition分區(qū)的,可以通過設(shè)置reduce的數(shù)量,結(jié)合分區(qū)函數(shù)讓數(shù)據(jù)均衡的進(jìn)入每個reduce即可:
#設(shè)置reduce的數(shù)量有兩種方式,第一種是直接設(shè)置reduce個數(shù)
set mapreduce．job．reduces=10;
#第二種是設(shè)置每個reduce的大小,Hive會根據(jù)數(shù)據(jù)總大小猜測確定一個reduce個數(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();
對于上述語句解釋:如設(shè)置reduce數(shù)量為10,使用 rand(), 隨機(jī)生成一個數(shù) x % 10 ,這樣數(shù)據(jù)就會隨機(jī)進(jìn)入 reduce 中,防止出現(xiàn)有的文件過大或過小。
4． 使用hadoop的archive將小文件歸檔
Hadoop Archive簡稱HAR,是一個高效地將小文件放入HDFS塊中的文件存檔工具,它能夠?qū)⒍鄠�小文件打包成一個HAR文件,這樣在減少namenode內(nèi)存使用的同時,仍然允許對文件進(jìn)行透明的訪問。
#用來控制歸檔是否可用
set hive．a(chǎn)rchive．enabled=true;
#通知Hive在創(chuàng)建歸檔時是否可以設(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');
對已歸檔的分區(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ù)存儲及壓縮:
針對hive中表的存儲格式通常有orc和parquet,壓縮格式一般使用snappy。相比與textfile格式表,orc占有更少的存儲。因?yàn)閔ive底層使用MR計算架構(gòu),數(shù)據(jù)流是hdfs到磁盤再到hdfs,而且會有很多次,所以使用orc數(shù)據(jù)格式和snappy壓縮策略可以降低IO讀寫,還能降低網(wǎng)絡(luò)傳輸量,這樣在一定程度上可以節(jié)省存儲,還能提升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)閉推測執(zhí)行設(shè)置。
3． 有效地減小數(shù)據(jù)集將大表拆分成子表;結(jié)合使用外部表和分區(qū)表。4． SQL優(yōu)化
大表對大表:盡量減少數(shù)據(jù)集,可以通過分區(qū)表,避免掃描全表或者全字段;
大表對小表:設(shè)置自動識別小表,將小表放入內(nèi)存中去執(zhí)行。
Hive優(yōu)化詳細(xì)剖析可查看:Hive企業(yè)級性能優(yōu)化
附:九個最易出錯的SQL講解
閱讀本節(jié)小建議:本文適合細(xì)嚼慢咽,不要一目十行,不然會錯過很多有價值的細(xì)節(jié)。
在進(jìn)行數(shù)倉搭建和數(shù)據(jù)分析時最常用的就是 sql,其語法簡潔明了,易于理解,目前大數(shù)據(jù)領(lǐng)域的幾大主流框架全部都支持sql語法,包括 hive,spark,flink等,所以sql在大數(shù)據(jù)領(lǐng)域有著不可替代的作用,需要我們重點(diǎn)掌握。
在使用sql時如果不熟悉或不仔細(xì),那么在進(jìn)行查詢分析時極容易出錯,接下來我們就來看下幾個容易出錯的sql語句及使用注意事項(xiàng)。
1． decimal
hive 除了支持 int,double,string等常用類型,也支持 decimal 類型,用于在數(shù)據(jù)庫中存儲精確的數(shù)值,常用在表示金額的字段上
注意事項(xiàng):
如:decimal(11,2) 代表最多有11位數(shù)字,其中后2位是小數(shù),整數(shù)部分是9位;
如果整數(shù)部分超過9位,則這個字段就會變成null,如果整數(shù)部分不超過9位,則原字段顯示;
如果小數(shù)部分不足2位,則后面用0補(bǔ)齊兩位,如果小數(shù)部分超過兩位,則超出部分四舍五入;
也可直接寫 decimal,后面不指定位數(shù),默認(rèn)是 decimal(10,0) 整數(shù)10位,沒有小數(shù)
2． location表創(chuàng)建的時候可以用 location 指定一個文件或者文件夾
create table stu(id int ,name string) location '/user/stu2';
注意事項(xiàng):
創(chuàng)建表時使用location,當(dāng)指定文件夾時,hive會加載文件夾下的所有文件,當(dāng)表中無分區(qū)時,這個文件夾下不能再有文件夾,否則報錯。
當(dāng)表是分區(qū)表時,比如 partitioned by (day string), 則這個文件夾下的每一個文件夾就是一個分區(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)加載,文件會拷貝到hdfs上使用 load data 表示從hdfs文件系統(tǒng)加載,文件會直接移動到hive相關(guān)目錄下,注意不是拷貝過去,因?yàn)閔ive認(rèn)為hdfs文件已經(jīng)有3副本了,沒必要再次拷貝了如果表是分區(qū)表,load 時不指定分區(qū)會報錯如果加載相同文件名的文件,會被自動重命名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)行連接的兩個表中都存在與連接條件相匹配的數(shù)據(jù)才會被保留下來
select * from techer t [inner] join course c on t．t_id = c．t_id; -- inner 可省略
LEFT OUTER JOIN 左外連接:左邊所有數(shù)據(jù)會被返回,右邊符合條件的被返回
select * from techer t left join course c on t．t_id = c．t_id; -- outer可省略
RIGHT OUTER JOIN 右外連接:右邊所有數(shù)據(jù)會被返回,左邊符合條件的被返回、
select * from techer t right join course c on t．t_id = c．t_id;
FULL OUTER JOIN 滿外(全外)連接: 將會返回所有表中符合條件的所有記錄。如果任一表的指定字段沒有符合條件的值的話,那么就使用NULL值替代。
SELECT * FROM techer t FULL JOIN course c ON t．t_id = c．t_id ;
注意事項(xiàng):
hive2版本已經(jīng)支持不等值連接,就是 join on條件后面可以使用大于小于符號;并且也支持 join on 條件后跟or (早前版本 on 后只支持 = 和 and,不支持 > < 和 or)如hive執(zhí)行引擎使用MapReduce,一個join就會啟動一個job,一條sql語句中如有多個join,則會啟動多個job
注意:表之間用逗號(,)連接和 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ū)別,只是書寫方式不同,用逗號是sql 89標(biāo)準(zhǔn),join 是sql 92標(biāo)準(zhǔn)。用逗號連接后面過濾條件用 where ,用 join 連接后面過濾條件是 on。
6． left semi join為什么把這個單獨(dú)拿出來說,因?yàn)樗推渌?join 語句不太一樣,
這個語句的作用和 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
上述兩個 sql 語句執(zhí)行結(jié)果完全一樣,只不過第二個執(zhí)行效率高
注意事項(xiàng):
left semi join 的限制是:join 子句中右邊的表只能在 on 子句中設(shè)置過濾條件,在 where 子句、select 子句或其他地方過濾都不行。left semi join 中 on 后面的過濾條件只能是等于號,不能是其他的。left semi join 是只傳遞表的 join key 給 map 階段,因此left semi join 中最后 select 的結(jié)果只許出現(xiàn)左表。因?yàn)?left semi join 是 in(keySet) 的關(guān)系,遇到右表重復(fù)記錄,左表會跳過7． 聚合函數(shù)中 null 值hive支持 count(),max(),min(),sum(),avg() 等常用的聚合函數(shù)
注意事項(xiàng):
聚合操作時要注意 null 值:
count(*) 包含 null 值,統(tǒng)計所有行數(shù);
count(id) 不包含id為 null 的值;
min 求最小值是不包含 null,除非所有值都是 null;
avg 求平均值也是不包含 null。
以上需要特別注意,null 值最容易導(dǎo)致算出錯誤的結(jié)果
8． 運(yùn)算符中 null 值hive 中支持常用的算術(shù)運(yùn)算符(+,-,*,/)
比較運(yùn)算符(>, <, =)
邏輯運(yùn)算符(in, not in)
以上運(yùn)算符計算時要特別注意 null 值
注意事項(xiàng):
每行中的列字段相加或相減,如果含有 null 值,則結(jié)果為 null
例:有一張商品表(product)idpricedis_amount1100202120null
各字段含義: id (商品id)、price (價格)、dis_amount (優(yōu)惠金額)
我想算每個商品優(yōu)惠后實(shí)際的價格,sql如下:
select id, price - dis_amount as real_amount from product;
得到結(jié)果如下:
idreal_amount1802null
id=2的商品價格為 null,結(jié)果是錯誤的。
我們可以對 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ù)是返回第一個不為空的值
如上sql:如果dis_amount不為空,則返回dis_amount,如果為空,則返回0
小于是不包含 null 值,如 id < 10;是不包含 id 為 null 值的。
not in 是不包含 null 值的,如 city not in ('北京','上海'),這個條件得出的結(jié)果是 city 中不包含 北京,上海和 null 的城市。
9． and 和 or
在sql語句的過濾條件或運(yùn)算中,如果有多個條件或多個運(yùn)算,我們都會考慮優(yōu)先級,如乘除優(yōu)先級高于加減,乘除或者加減它們之間優(yōu)先級平等,誰在前就先算誰。那 and 和 or 呢,看似 and 和 or 優(yōu)先級平等,誰在前先算誰,但是,and 的優(yōu)先級高于 or。
注意事項(xiàng):
例:
還是一張商品表(product)
idclassifyprice1電器702電器1303電器804家具1505家具606食品120
我想要統(tǒng)計下電器或者家具這兩類中價格大于100的商品,sql如下:
select * from product where classify = '電器' or classify = '家具' and price>100
得到結(jié)果
idclassifyprice1電器702電器1303電器804家具150
結(jié)果是錯誤的,把所有的電器類型都查詢出來了,原因就是 and 優(yōu)先級高于 or,上面的sql語句實(shí)際執(zhí)行的是,先找出 classify = '家具' and price>100 的,然后在找出 classify = '電器' 的
正確的 sql 就是加個括號,先計算括號里面的:
select * from product where (classify = '電器' or classify = '家具') and price>100
最后
第一時間獲取最新大數(shù)據(jù)技術(shù),盡在本公眾號:五分鐘學(xué)大數(shù)據(jù)
分享
請輸入評論內(nèi)容...
請輸入評論/評論長度6~500個字
最新活動更多
推薦專題
-
10 月之暗面，絕地反擊
- 1 UALink規(guī)范發(fā)布：挑戰(zhàn)英偉達(dá)AI統(tǒng)治的開始
- 2 北電數(shù)智主辦酒仙橋論壇，探索AI產(chǎn)業(yè)發(fā)展新路徑
- 3 “AI寒武紀(jì)”爆發(fā)至今，五類新物種登上歷史舞臺
- 4 降薪、加班、裁員三重暴擊，“AI四小龍”已折戟兩家
- 5 國產(chǎn)智駕迎戰(zhàn)特斯拉FSD，AI含量差幾何？
- 6 光計算迎來商業(yè)化突破，但落地仍需時間
- 7 東陽光：2024年扭虧、一季度凈利大增，液冷疊加具身智能打開成長空間
- 8 地平線自動駕駛方案解讀
- 9 封殺AI“照騙”，“淘寶們”終于不忍了？
- 10 優(yōu)必選：營收大增主靠小件，虧損繼續(xù)又逢關(guān)稅，能否乘機(jī)器人東風(fēng)翻身？
