Hive提高查詢效率的八條軍規(guī)

1、01開啟FetchTask一個(gè)簡(jiǎn)單的查詢語句，是指一個(gè)沒有函數(shù)、排序等功能的語句，當(dāng)開啟一個(gè)Fetch Task功能，就執(zhí)行一個(gè)簡(jiǎn)單的查詢語句不會(huì)生成MapRreduce作業(yè)，而是直接使用FetchTask，從hdfs文件系統(tǒng)中進(jìn)行查詢輸出數(shù)據(jù)，從而提高效率。設(shè)置的方式：Hive.fetch.task.conversion默認(rèn)為minimal修改配置文件hive-site.xmlhive.fetch.task.conversionmoreSomeselectqueriescanbeconvertedtosingleFETCHtaskminimizinglatency.Currentlytheq

2、ueryshouldbesinglesourcednothavinganysubqueryandshouldnothaveanyaggregationsordistincts(whichincurrsRS),lateralviewsandjoins.1.minimal:SELECTSTAR,FILTERonpartitioncolumns,LIMITonly2.more:SELECT,FILTER,LIMITonly(+TABLESAMPLE,virtualcolumns)或者當(dāng)前session修改hivesethive.fetch.task.conversion=more;執(zhí)行SELECTi

3、d,moneyFROMmlimit10;不走mr02合并中間表一個(gè)日志文件中，每一行記錄，會(huì)有很多很多字段，四五十個(gè)字段很正常。實(shí)際分析中，常常使用少數(shù)幾個(gè)字段將原始的表中數(shù)據(jù)，依據(jù)業(yè)務(wù)需求提取出要分析的字段，數(shù)據(jù)放入到對(duì)應(yīng)的業(yè)務(wù)表（子表）中，實(shí)際的業(yè)務(wù)針對(duì)業(yè)務(wù)表進(jìn)行分析。在實(shí)際中，我們會(huì)發(fā)現(xiàn)，有些業(yè)務(wù)處理，會(huì)有共同數(shù)據(jù)集用戶表、訂單表、商品表，三個(gè)表需要進(jìn)行join的操作，join 會(huì)產(chǎn)生一個(gè)結(jié)果集，會(huì)有很多的業(yè)務(wù)是針對(duì)此jion結(jié)果集進(jìn)行分析。優(yōu)化：將眾多的業(yè)務(wù)中相同的中間結(jié)果集，抽取到一個(gè)Hive中的表中去。03合理使用分區(qū)表外部表、分區(qū)表，結(jié)合使用，采用多級(jí)分區(qū)。數(shù)據(jù)采用存儲(chǔ)格式（te

4、xtfile、orcfile、parquet）或者數(shù)據(jù)壓縮(snappy)。明細(xì)數(shù)據(jù)我們一般采用按天分區(qū)，對(duì)于特別大的表，可以采用子分區(qū)，每個(gè)分區(qū)其實(shí)對(duì)應(yīng)到HDFS上就是一個(gè)目錄。數(shù)據(jù)存儲(chǔ)方式我們可以采用parquet列式存儲(chǔ)，同時(shí)具有很好的壓縮性能；同時(shí)可以減少大量的表掃描和反序列化的時(shí)間。在OLAP查詢場(chǎng)景下，我們選擇需要的列信息進(jìn)行查詢，而不是直接select * 查詢所有字段。04jvm重用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來

5、執(zhí)行map和reduce任務(wù)的，這是jvm的啟動(dòng)過程可能會(huì)造成相當(dāng)大的開銷，尤其是執(zhí)行的job包含有成千上萬個(gè)task任務(wù)的情況。JVM重用可以使得JVM實(shí)例在同一個(gè)JOB中重新使用N次，N的值可以在Hadoop的mapre-site.xml文件中進(jìn)行設(shè)置。mapred.job.reuse.jvm.num.tasks 1也可在hive的執(zhí)行設(shè)置：setmapred.job.reuse.jvm.num.tasks = 10;JVM的一個(gè)缺點(diǎn)是，開啟JVM重用將會(huì)一直占用使用到的task插槽，以便進(jìn)行重用，直到任務(wù)完成后才能釋放。如果某個(gè)“不平衡“的job中有幾個(gè)reduce task 執(zhí)行的時(shí)間

6、要比其他reduce task消耗的時(shí)間多得多的話，那么保留的插槽就會(huì)一直空閑著卻無法被其他的job使用，直到所有的task都結(jié)束了才會(huì)釋放。05年Speculative execution（推測(cè)執(zhí)行）所謂的推測(cè)執(zhí)行，就是當(dāng)所有task都開始運(yùn)行之后，Job Tracker會(huì)統(tǒng)計(jì)所有任務(wù)的平均進(jìn)度，如果某個(gè)task所在的task node機(jī)器配置比較低或者CPU load很高（原因很多），導(dǎo)致任務(wù)執(zhí)行比總體任務(wù)的平均執(zhí)行要慢，此時(shí)Job Tracker會(huì)啟動(dòng)一個(gè)新的任務(wù)（duplicate task），原有任務(wù)和新任務(wù)哪個(gè)先執(zhí)行完就把另外一個(gè)kill掉。推測(cè)執(zhí)行需要設(shè)置Job的兩個(gè)參數(shù)：map

7、red.map.tasks.speculative.execution=truemapred.reduce.tasks.speculative.execution=true06合理設(shè)置reduce個(gè)數(shù)reduce個(gè)數(shù)參數(shù)1：hive.exec.reducers.bytes.per.reducer=256000000/每個(gè)reduce任務(wù)處理的數(shù)據(jù)量參數(shù)2：hive.exec.reducers.max=1009/每個(gè)任務(wù)最大的reduce數(shù)目計(jì)算公式：reducer個(gè)數(shù)=min(參數(shù)2,總輸入數(shù)據(jù)量/參數(shù)1)set mapred.reduce.tasks =N：每個(gè)任務(wù)默認(rèn)的reduce數(shù)目。典

8、型為0.99* reduce槽數(shù)，hive默認(rèn)為-1，即自動(dòng)確定reduce數(shù)目。reduce個(gè)數(shù)并不是越多越好同map一樣，啟動(dòng)和初始化reduce也會(huì)消耗時(shí)間和資源；另外，有多少個(gè)reduce，就會(huì)有多少個(gè)輸出文件，如果生成了很多個(gè)小文件，那么如果這些小文件作為下一個(gè)任務(wù)的輸入，則也會(huì)出現(xiàn)小文件過多的問題。小文件過多會(huì)非常影響查詢效率，文件越多造成的IO就越多，同時(shí)還會(huì)增加元數(shù)據(jù)（namenode）的壓力。在生產(chǎn)環(huán)境中，一定要避免小文件問題，如果核查發(fā)現(xiàn)，及時(shí)合并文件！07開啟并行執(zhí)行并行執(zhí)行，意思是同步執(zhí)行hive的多個(gè)階段，hive在執(zhí)行過程，將一個(gè)查詢轉(zhuǎn)化成一個(gè)或者多個(gè)階段。某個(gè)特定

9、的job可能包含眾多的階段，而這些階段可能并非完全相互依賴的，也就是說可以并行執(zhí)行的，這樣可能使得整個(gè)job的執(zhí)行時(shí)間縮短hive.exec.parallel.thread.number8/job并行執(zhí)行的數(shù)目，一個(gè)SQL語句可能有很多mapreduce任務(wù)，限制hive.exec.parallelfalsehive執(zhí)行開啟：sethive.exec.parallel=true08優(yōu)化sqlwhere條件優(yōu)化優(yōu)化前（關(guān)系數(shù)據(jù)庫(kù)不用考慮會(huì)自動(dòng)優(yōu)化）：selectm.cid,u.idfromordermjoincustomeruon(m.cid=u.id)wherem.dt=20180808;優(yōu)化

10、后(where條件在map端執(zhí)行而不是在reduce端執(zhí)行）：selectm.cid,u.idfrom（select*fromorderwheredt=20180818）mjoincustomeruon(m.cid=u.id);union優(yōu)化盡量不要使用union （union 去掉重復(fù)的記錄）而是使用 union all 然后在用group by 去重count distinct優(yōu)化不要使用count (distinct cloumn) ,使用子查詢。selectcount(1)from(selectidfromtablenamegroupbyid)tmp;用in 來代替join如果需要根據(jù)

11、一個(gè)表的字段來約束另為一個(gè)表，盡量用in來代替join 。selectid,namefromtb1ajointb2bon(a.id=b.id);selectid,namefromtb1whereidin(selectidfromtb2);in 要比join 快消滅子查詢內(nèi)的 group by 、 COUNT(DISTINCT)，MAX，MIN?？梢詼p少job的數(shù)量。join 優(yōu)化：Common/shuffle/Reduce JOIN：連接發(fā)生的階段，發(fā)生在reduce 階段，適用于大表連接大表(默認(rèn)的方式)Map join ：連接發(fā)生在map階段，適用于小表連接大表 大表的數(shù)據(jù)從文件中讀??；小表的數(shù)據(jù)存放在內(nèi)存中（hive中已經(jīng)自動(dòng)進(jìn)行了優(yōu)化，自動(dòng)判斷小表，然后進(jìn)行緩存）。sethive.auto.convert.join=true;SMB join：Sort -Merge -Bucket Join 對(duì)大表連接大表的優(yōu)化，用桶表的概念來進(jìn)行優(yōu)化。在一個(gè)桶內(nèi)發(fā)送生笛卡爾積連接（需要是兩個(gè)桶表進(jìn)行join）se