hive建表設(shè)計(jì)層面

1.使用分區(qū)表優(yōu)化

分區(qū)表 是在某一個(gè)或者幾個(gè)維度上對(duì)數(shù)據(jù)進(jìn)行分類存儲(chǔ)，一個(gè)分區(qū)對(duì)應(yīng)一個(gè)目錄。如果篩選條件里有分
區(qū)字段，那么 Hive 只需要遍歷對(duì)應(yīng)分區(qū)目錄下的文件即可，不需要遍歷全局?jǐn)?shù)據(jù)，使得處理的數(shù)據(jù)量
大大減少，從而提高查詢效率。

也就是說(shuō)：當(dāng)一個(gè) Hive 表的查詢大多數(shù)情況下，會(huì)根據(jù)某一個(gè)字段進(jìn)行篩選時(shí)，那么非常適合創(chuàng)建為
分區(qū)表，該字段即為分區(qū)字段。

2.使用分桶表優(yōu)化

跟分區(qū)的概念很相似，都是把數(shù)據(jù)分成多個(gè)不同的類別，區(qū)別就是規(guī)則不一樣！

1、分區(qū)：按照字段值來(lái)進(jìn)行：一個(gè)分區(qū)，就只是包含這個(gè)這一個(gè)值的所有記錄
不是當(dāng)前分區(qū)的數(shù)據(jù)一定不在當(dāng)前分區(qū)
當(dāng)前分區(qū)也只會(huì)包含當(dāng)前這個(gè)分區(qū)值的數(shù)據(jù)

2、分桶：默認(rèn)規(guī)則：Hash散列
一個(gè)分桶中會(huì)有多個(gè)不同的值
如果一個(gè)分桶中，包含了某個(gè)值，這個(gè)值的所有記錄，必然都在這個(gè)分桶

Hive Bucket，分桶，是指將數(shù)據(jù)以指定列的值為 key 進(jìn)行 hash，hash 到指定數(shù)目的桶中，這樣做的
目的和分區(qū)表類似，使得篩選時(shí)不用全局遍歷所有的數(shù)據(jù)，只需要遍歷所在桶就可以了。這樣也可以支
持高效采樣。

3.選擇合適的文件存儲(chǔ)格式

Apache Hive支持 Apache Hadoop 中使用的幾種熟悉的文件格式，比如 TextFile、SequenceFile、RCFile、Avro、ORC、ParquetFile等。

存儲(chǔ)格式一般需要根據(jù)業(yè)務(wù)進(jìn)行選擇，在我們的實(shí)操中，絕大多數(shù)表都采用TextFile與Parquet兩種存儲(chǔ)
格式之一。 TextFile是最簡(jiǎn)單的存儲(chǔ)格式，它是純文本記錄，也是Hive的默認(rèn)格式。雖然它的磁盤開銷
比較大，查詢效率也低，但它更多地是作為跳板來(lái)使用。RCFile、ORC、Parquet等格式的表都不能由
文件直接導(dǎo)入數(shù)據(jù)，必須由TextFile來(lái)做中轉(zhuǎn)。 Parquet和ORC都是Apache旗下的開源列式存儲(chǔ)格式。
列式存儲(chǔ)比起傳統(tǒng)的行式存儲(chǔ)更適合批量OLAP查詢，并且也支持更好的壓縮和編碼。

創(chuàng)建表時(shí)，特別是寬表，盡量使用 ORC、ParquetFile 這些列式存儲(chǔ)格式，因?yàn)榱惺酱鎯?chǔ)的表，每一
列的數(shù)據(jù)在物理上是存儲(chǔ)在一起的，Hive查詢時(shí)會(huì)只遍歷需要列數(shù)據(jù)，大大減少處理的數(shù)據(jù)量。

第一種：TextFile

1、存儲(chǔ)方式：行存儲(chǔ)。默認(rèn)格式，如果建表時(shí)不指定默認(rèn)為此格式。，
2、每一行都是一條記錄，每行都以換行符"\n"結(jié)尾。數(shù)據(jù)不做壓縮時(shí)，磁盤會(huì)開銷比較大，數(shù)據(jù)解析開銷也 比較大。
3、可結(jié)合Gzip、Bzip2等壓縮方式一起使用（系統(tǒng)會(huì)自動(dòng)檢查，查詢時(shí)會(huì)自動(dòng)解壓）,推薦選用可切分的壓 縮算法。

第二種：Sequence File

1、一種Hadoop API提供的二進(jìn)制文件，使用方便、可分割、個(gè)壓縮的特點(diǎn)。
2、支持三種壓縮選擇：NONE、RECORD、BLOCK。RECORD壓縮率低，一般建議使用BLOCK壓縮。

第三種：RC File

1、存儲(chǔ)方式：數(shù)據(jù)按行分塊，每塊按照列存儲(chǔ) 。
A、首先，將數(shù)據(jù)按行分塊，保證同一個(gè)record在一個(gè)塊上，避免讀一個(gè)記錄需要讀取多個(gè)block。
B、其次，塊數(shù)據(jù)列式存儲(chǔ)，有利于數(shù)據(jù)壓縮和快速的列存取。
2、相對(duì)來(lái)說(shuō)，RCFile對(duì)于提升任務(wù)執(zhí)行性能提升不大，但是能節(jié)省一些存儲(chǔ)空間。可以使用升級(jí)版的ORC格 式。

第四種：ORC File

1、存儲(chǔ)方式：數(shù)據(jù)按行分塊，每塊按照列存儲(chǔ)
2、Hive提供的新格式，屬于RCFile的升級(jí)版，性能有大幅度提升，而且數(shù)據(jù)可以壓縮存儲(chǔ)，壓縮快，快速 列存取。 3、ORC
File會(huì)基于列創(chuàng)建索引，當(dāng)查詢的時(shí)候會(huì)很快。

第五種：Parquet File

1、存儲(chǔ)方式：列式存儲(chǔ)。
2、Parquet對(duì)于大型查詢的類型是高效的。對(duì)于掃描特定表格中的特定列查詢，Parquet特別有用。
Parquet一般使用Snappy、Gzip壓縮。默認(rèn)Snappy。
3、Parquet支持Impala 查詢引擎。
4、表的文件存儲(chǔ)格式盡量采用Parquet或ORC，不僅降低存儲(chǔ)量，還優(yōu)化了查詢，壓縮，表關(guān)聯(lián)等性能。

4.選擇合適的壓縮格式

Hive 語(yǔ)句最終是轉(zhuǎn)化為 MapReduce 程序來(lái)執(zhí)行的，而 MapReduce 的性能瓶頸在與 網(wǎng)絡(luò)IO 和 磁盤
IO，要解決性能瓶頸，最主要的是 減少數(shù)據(jù)量，對(duì)數(shù)據(jù)進(jìn)行壓縮是個(gè)好方式。壓縮雖然是減少了數(shù)據(jù)
量，但是壓縮過(guò)程要消耗 CPU，但是在 Hadoop 中，往往性能瓶頸不在于 CPU，CPU 壓力并不大，所
以壓縮充分利用了比較空閑的 CPU。

map階段輸出數(shù)據(jù)壓縮 ，在這個(gè)階段，優(yōu)先選擇一個(gè)低CPU開銷的算法。

set hive.exec.compress.intermediate=true set mapred.map.output.compression.codec= org.apache.hadoop.io.compress.SnappyCodec set mapred.map.output.compression.codec=com.hadoop.compression.lzo.LzoCodec;

對(duì)最終輸出結(jié)果壓縮

set hive.exec.compress.output=true set mapred.output.compression.codec=org.apache.hadoop.io.compress.SnappyCodec

##當(dāng)然，也可以在hive建表時(shí)指定表的文件格式和壓縮編碼
結(jié)論，一般選擇orcfile/parquet + snappy 方式

sql語(yǔ)法和運(yùn)行參數(shù)層面

1.列裁剪

列裁剪就是在查詢時(shí)只讀取需要的列，分區(qū)裁剪就是只讀取需要的分區(qū)。當(dāng)列很多或者數(shù)據(jù)量很大時(shí)，
如果 select * 或者不指定分區(qū)，全列掃描和全表掃描效率都很低。
Hive 在讀數(shù)據(jù)的時(shí)候，可以只讀取查詢中所需要用到的列，而忽略其他的列。這樣做可以節(jié)省讀取開
銷：中間表存儲(chǔ)開銷和數(shù)據(jù)整合開銷。

set hive.optimize.cp = true; ## 列裁剪，取數(shù)只取查詢中需要用到的列，默認(rèn)是true

2.謂詞下推

將 SQL 語(yǔ)句中的 where 謂詞邏輯都盡可能提前執(zhí)行，減少下游處理的數(shù)據(jù)量。對(duì)應(yīng)邏輯優(yōu)化器是
PredicatePushDown。

set hive.optimize.ppd=true;

3.合并小文件

在執(zhí)行 MapReduce 程序的時(shí)候，一般情況是一個(gè)文件的一個(gè)數(shù)據(jù)分塊需要一個(gè) mapTask 來(lái)處理。但
是如果數(shù)據(jù)源是大量的小文件，這樣就會(huì)啟動(dòng)大量的 mapTask 任務(wù)，這樣會(huì)浪費(fèi)大量資源。可以將輸
入的小文件進(jìn)行合并，從而減少 mapTask 任務(wù)數(shù)量。

4.合理控制map task和reduce task的數(shù)量

這個(gè)優(yōu)化措施，但凡能用就用！ 大表 join 小表 小表滿足需求： 小表數(shù)據(jù)小于控制條件時(shí)
MapJoin 是將 join 雙方比較小的表直接分發(fā)到各個(gè) map 進(jìn)程的內(nèi)存中，在 map 進(jìn)程中進(jìn)行 join 操
作，這樣就不用進(jìn)行 reduce 步驟，從而提高了速度。只有 join 操作才能啟用 MapJoin。

5.join 數(shù)據(jù)傾斜優(yōu)化

在編寫 Join 查詢語(yǔ)句時(shí)，如果確定是由于 join 出現(xiàn)的數(shù)據(jù)傾斜，那么請(qǐng)做如下設(shè)置：

join的鍵對(duì)應(yīng)的記錄條數(shù)超過(guò)這個(gè)值則會(huì)進(jìn)行分拆，值根據(jù)具體數(shù)據(jù)量設(shè)置

set hive.skewjoin.key=100000;

如果是join過(guò)程出現(xiàn)傾斜應(yīng)該設(shè)置為true

set hive.optimize.skewjoin=false;

如果開啟了，在 Join 過(guò)程中 Hive 會(huì)將計(jì)數(shù)超過(guò)閾值 hive.skewjoin.key（默認(rèn)100000）的傾斜 key 對(duì)
應(yīng)的行臨時(shí)寫進(jìn)文件中，然后再啟動(dòng)另一個(gè) job 做 map join 生成結(jié)果。
通過(guò) hive.skewjoin.mapjoin.map.tasks 參數(shù)還可以控制第二個(gè) job 的 mapper 數(shù)量，默認(rèn)
10000。

6.CBO優(yōu)化

join的時(shí)候表的順序的關(guān)系：前面的表都會(huì)被加載到內(nèi)存中。后面的表進(jìn)行磁盤掃描。

select a.*, b.*, c.* from a join b on a.id = b.id join c on a.id = c.id;

Hive 自 0.14.0 開始，加入了一項(xiàng) “Cost based Optimizer” 來(lái)對(duì) HQL 執(zhí)行計(jì)劃進(jìn)行優(yōu)化，這個(gè)功能通
過(guò) “hive.cbo.enable” 來(lái)開啟。在 Hive 1.1.0 之后，這個(gè) feature 是默認(rèn)開啟的，它可以 自動(dòng)優(yōu)化 HQL
中多個(gè) Join 的順序，并選擇合適的 Join 算法。

CBO，成本優(yōu)化器，代價(jià)最小的執(zhí)行計(jì)劃就是最好的執(zhí)行計(jì)劃。傳統(tǒng)的數(shù)據(jù)庫(kù)，成本優(yōu)化器做出最優(yōu)化
的執(zhí)行計(jì)劃是依據(jù)統(tǒng)計(jì)信息來(lái)計(jì)算的。Hive 的成本優(yōu)化器也一樣。
Hive 在提供最終執(zhí)行前，優(yōu)化每個(gè)查詢的執(zhí)行邏輯和物理執(zhí)行計(jì)劃。這些優(yōu)化工作是交給底層來(lái)完成
的。根據(jù)查詢成本執(zhí)行進(jìn)一步的優(yōu)化，從而產(chǎn)生潛在的不同決策：如何排序連接，執(zhí)行哪種類型的連
接，并行度等等。
要使用基于成本的優(yōu)化（也稱為CBO），請(qǐng)?jiān)诓樵冮_始設(shè)置以下參數(shù)：

set hive.cbo.enable=true; set hive.compute.query.using.stats=true; set hive.stats.fetch.column.stats=true; set hive.stats.fetch.partition.stats=true;

hive架構(gòu)層面

1.啟用本地抓取

Hive 的某些 SQL 語(yǔ)句需要轉(zhuǎn)換成 MapReduce 的操作，某些 SQL 語(yǔ)句就不需要轉(zhuǎn)換成 MapReduce 操
作，但是同學(xué)們需要注意，理論上來(lái)說(shuō)，所有的 SQL 語(yǔ)句都需要轉(zhuǎn)換成 MapReduce 操作，只不過(guò)
Hive 在轉(zhuǎn)換 SQL 語(yǔ)句的過(guò)程中會(huì)做部分優(yōu)化，使某些簡(jiǎn)單的操作不再需要轉(zhuǎn)換成 MapReduce，例
如：

1、只是 select * 的時(shí)候
2、where 條件針對(duì)分區(qū)字段進(jìn)行篩選過(guò)濾時(shí)
3、帶有 limit 分支語(yǔ)句時(shí)

Hive 從 HDFS 中讀取數(shù)據(jù)，有兩種方式：啟用MapReduce讀取 和 直接抓取。
直接抓取數(shù)據(jù)比 MapReduce 方式讀取數(shù)據(jù)要快的多，但是只有少數(shù)操作可以使用直接抓取方式。
可以通過(guò) hive.fetch.task.conversion 參數(shù)來(lái)配置在什么情況下采用直接抓取方式：

minimal：只有 select * 、在分區(qū)字段上 where 過(guò)濾、有 limit 這三種場(chǎng)景下才啟用直接抓取方式。 more：在
select、where 篩選、limit 時(shí)，都啟用直接抓取方式。

2.本地執(zhí)行優(yōu)化

Hive 在集群上查詢時(shí)，默認(rèn)是在集群上多臺(tái)機(jī)器上運(yùn)行，需要多個(gè)機(jī)器進(jìn)行協(xié)調(diào)運(yùn)行，這種方式很好
的解決了大數(shù)據(jù)量的查詢問(wèn)題。但是在 Hive 查詢處理的數(shù)據(jù)量比較小的時(shí)候，其實(shí)沒(méi)有必要啟動(dòng)分布
式模式去執(zhí)行，因?yàn)橐苑植际椒绞綀?zhí)行設(shè)計(jì)到跨網(wǎng)絡(luò)傳輸、多節(jié)點(diǎn)協(xié)調(diào)等，并且消耗資源。對(duì)于小數(shù)據(jù)
集，可以通過(guò)本地模式，在單臺(tái)機(jī)器上處理所有任務(wù)，執(zhí)行時(shí)間明顯被縮短。

啟動(dòng)本地模式涉及到三個(gè)參數(shù)：

--打開hive自動(dòng)判斷是否啟動(dòng)本地模式的開關(guān) set hive.exec.mode.local.auto=true; -- map任務(wù)數(shù)最大值，不啟用本地模式的task最大個(gè)數(shù) set hive.exec.mode.local.auto.input.files.max=4; -- map輸入文件最大大小，不啟動(dòng)本地模式的最大輸入文件大小 set hive.exec.mode.local.auto.inputbytes.max=134217728;

3.JVM重用

Hive 語(yǔ)句最終會(huì)轉(zhuǎn)換為一系列的 MapReduce 任務(wù)，每一個(gè)MapReduce 任務(wù)是由一系列的 MapTask
和 ReduceTask 組成的，默認(rèn)情況下，MapReduce 中一個(gè) MapTask 或者 ReduceTask 就會(huì)啟動(dòng)一個(gè)
JVM 進(jìn)程，一個(gè) Task 執(zhí)行完畢后，JVM 進(jìn)程就會(huì)退出。這樣如果任務(wù)花費(fèi)時(shí)間很短，又要多次啟動(dòng)
JVM 的情況下，JVM 的啟動(dòng)時(shí)間會(huì)變成一個(gè)比較大的消耗，這時(shí)，可以通過(guò)重用 JVM 來(lái)解決。

set mapred.job.reuse.jvm.num.tasks=5;

JVM也是有缺點(diǎn)的，開啟JVM重用會(huì)一直占用使用到的 task 的插槽，以便進(jìn)行重用，直到任務(wù)完成后才
會(huì)釋放。如果某個(gè) 不平衡的job 中有幾個(gè) reduce task 執(zhí)行的時(shí)間要比其他的 reduce task 消耗的時(shí)間
要多得多的話，那么保留的插槽就會(huì)一直空閑卻無(wú)法被其他的 job 使用，直到所有的 task 都結(jié)束了才
會(huì)釋放。
根據(jù)經(jīng)驗(yàn)，一般來(lái)說(shuō)可以使用一個(gè) cpu core 啟動(dòng)一個(gè) JVM，假如服務(wù)器有 16 個(gè) cpu core ，但是這個(gè)
節(jié)點(diǎn)，可能會(huì)啟動(dòng) 32 個(gè)mapTask，完全可以考慮：啟動(dòng)一個(gè)JVM，執(zhí)行兩個(gè)Task。

4.并行執(zhí)行

有的查詢語(yǔ)句，Hive 會(huì)將其轉(zhuǎn)化為一個(gè)或多個(gè)階段，包括：MapReduce 階段、抽樣階段、合并階段、
limit 階段等。默認(rèn)情況下，一次只執(zhí)行一個(gè)階段。但是，如果某些階段不是互相依賴，是可以并行執(zhí)行
的。多階段并行是比較耗系統(tǒng)資源的。
一個(gè) Hive SQL 語(yǔ)句可能會(huì)轉(zhuǎn)為多個(gè) MapReduce Job，每一個(gè) job 就是一個(gè) stage，這些 Job 順序執(zhí)
行，這個(gè)在 cli 的運(yùn)行日志中也可以看到。但是有時(shí)候這些任務(wù)之間并不是是相互依賴的，如果集群資
源允許的話，可以讓多個(gè)并不相互依賴 stage 并發(fā)執(zhí)行，這樣就節(jié)約了時(shí)間，提高了執(zhí)行速度，但是如
果集群資源匱乏時(shí)，啟用并行化反倒是會(huì)導(dǎo)致各個(gè) Job 相互搶占資源而導(dǎo)致整體執(zhí)行性能的下降。啟用
并行化：

-- 可以開啟并發(fā)執(zhí)行。 set hive.exec.parallel=true; --同一個(gè)sql允許最大并行度，默認(rèn)為8。 set hive.exec.parallel.thread.number=16;

sql優(yōu)化

1 對(duì)于這個(gè)調(diào)優(yōu)，有待再次驗(yàn)證目前我驗(yàn)證了4千萬(wàn) join 8千萬(wàn)發(fā)現(xiàn)不管是left join還是inner join，執(zhí)行計(jì)劃差不多，job數(shù)，maptask，reducetask數(shù)都一樣，執(zhí)行時(shí)長(zhǎng)也差不多可能都已經(jīng)自動(dòng)優(yōu)化了(謂詞下推)

select count(1) from test1 t1 inner join test2 t2 on t1.uuid = t2.uuid and t2.date_id = ‘2020-10-23’ where t1.date_id = ‘2020-10-23’；select count(1) from (select * from test1 where date_id = ‘2020-10-23’) t1 inner join test2 t2 on t1.uuid = t2.uuid and t2.date_id = ‘2020-10-23’

;

2 .union優(yōu)化

盡量不要使用union （union 去掉重復(fù)的記錄）而是使用 union all 然后再用group by 去重

3 .count distinct優(yōu)化

數(shù)據(jù)量小的時(shí)候無(wú)所謂，數(shù)據(jù)量大的情況下，由于COUNT DISTINCT操作需要用一個(gè)Reduce Task來(lái)完成，這一個(gè)Reduce需要處理的數(shù)據(jù)量太大，就會(huì)導(dǎo)致整個(gè)Job很難完成，一般COUNT DISTINCT使用先GROUP BY再COUNT的方式替換：
不要使用count (distinct cloumn) ,使用子查詢

select count(1) from (select id from tablename group by id) tmp;

雖然會(huì)多用一個(gè)Job來(lái)完成，但在數(shù)據(jù)量大的情況下，這個(gè)絕對(duì)是值得的。

4.用in 來(lái)代替join

如果需要根據(jù)一個(gè)表的字段來(lái)約束另為一個(gè)表，盡量用in來(lái)代替join . in 要比join 快

select a.Id ,name from tb1 a join tb2 b on(a.id = b.id); select Id ,name from tb1 where id in(select id from tb2);

5. Left semi join優(yōu)化in/exists

(a表和b表通過(guò)user_id關(guān)聯(lián))
a表數(shù)據(jù)

select * from wedw_dw.t_user;

b表數(shù)據(jù)

select * from wedw_dw.t_order;

l

--left semi join Select * from wedw_dw.t_user t1 left semi join wedw_dw.t_order t2 on t1.user_id = t2.user_id;

如圖所示：只能展示a表的字段，因?yàn)閘eft semi join 只傳遞表的 join key 給 map 階段

總結(jié)

以上是生活随笔為你收集整理的Hive优化语句的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。