Hive調(diào)優(yōu)技巧有哪些

Apache Hive是建立在Apache Hadoop之上的數(shù)據(jù)倉(cāng)庫(kù)軟件項(xiàng)目，用于提供數(shù)據(jù)查詢(xún)和分析。Hive是Hadoop在HDFS上的SQL接口，它提供了類(lèi)似于SQL的接口來(lái)查詢(xún)存儲(chǔ)在與Hadoop集成的各種數(shù)據(jù)庫(kù)和文件系統(tǒng)中的數(shù)據(jù)?？梢哉f(shuō)從事數(shù)據(jù)開(kāi)發(fā)工作，無(wú)論是在平時(shí)的工作中，還是在面試中，Hive具有舉足輕重的地位，尤其是Hive的性能調(diào)優(yōu)方面，不僅能夠在工作中提升效率而且還可以在面試中脫穎而出。

1.多次INSERT單次掃描表

默認(rèn)情況下，Hive會(huì)執(zhí)行多次表掃描。因此，如果要在某張hive表中執(zhí)行多個(gè)操作，建議使用一次掃描并使用該掃描來(lái)執(zhí)行多個(gè)操作。

比如將一張表的數(shù)據(jù)多次查詢(xún)出來(lái)裝載到另外一張表中。如下面的示例，表my_table是一個(gè)分區(qū)表，分區(qū)字段為dt，如果需要在表中查詢(xún)2個(gè)特定的分區(qū)日期數(shù)據(jù)，并將記錄裝載到2個(gè)不同的表中。

INSERT INTO temp_table_20201115 SELECT * FROM my_table WHERE dt ='2020-11-15';
INSERT INTO temp_table_20201116 SELECT * FROM my_table WHERE dt ='2020-11-16';

在以上查詢(xún)中，Hive將掃描表2次，為了避免這種情況，我們可以使用下面的方式：

FROM my_table
INSERT INTO temp_table_20201115 SELECT * WHERE dt ='2020-11-15'
INSERT INTO temp_table_20201116 SELECT * WHERE dt ='2020-11-16'

這樣可以確保只對(duì)my_table表執(zhí)行一次掃描，從而可以大大減少執(zhí)行的時(shí)間和資源。

2.分區(qū)表

對(duì)于一張比較大的表，將其設(shè)計(jì)成分區(qū)表可以提升查詢(xún)的性能，對(duì)于一個(gè)特定分區(qū)的查詢(xún)，只會(huì)加載對(duì)應(yīng)分區(qū)路徑的文件數(shù)據(jù)，因此，當(dāng)用戶使用特定分區(qū)列值執(zhí)行選擇查詢(xún)時(shí)，將僅針對(duì)該特定分區(qū)執(zhí)行查詢(xún)，由于將針對(duì)較少的數(shù)據(jù)量進(jìn)行掃描，所以可以提供更好的性能。值得注意的是，分區(qū)字段的選擇是影響查詢(xún)性能的重要因素，盡量避免層級(jí)較深的分區(qū)，這樣會(huì)造成太多的子文件夾。

現(xiàn)在問(wèn)題來(lái)了，該使用哪些列進(jìn)行分區(qū)呢？一條基本的法則是：選擇低基數(shù)屬性作為“分區(qū)鍵”，比如“地區(qū)”或“日期”等。

一些常見(jiàn)的分區(qū)字段可以是：

• 日期或者時(shí)間

比如year、month、day或者h(yuǎn)our，當(dāng)表中存在時(shí)間或者日期字段時(shí)，可以使用些字段。

• 地理位置

比如國(guó)家、省份、城市等

• 業(yè)務(wù)邏輯

比如部門(mén)、銷(xiāo)售區(qū)域、客戶等等

CREATE TABLE table_name (
    col1 data_type,
    col2 data_type)
PARTITIONED BY (partition1 data_type, partition2 data_type,….);

3.分桶表

通常，當(dāng)很難在列上創(chuàng)建分區(qū)時(shí)，我們會(huì)使用分桶，比如某個(gè)經(jīng)常被篩選的字段，如果將其作為分區(qū)字段，會(huì)造成大量的分區(qū)。在Hive中，會(huì)對(duì)分桶字段進(jìn)行哈希，從而提供了中額外的數(shù)據(jù)結(jié)構(gòu)，進(jìn)行提升查詢(xún)效率。

與分區(qū)表類(lèi)似，分桶表的組織方式是將HDFS上的文件分割成多個(gè)文件。分桶可以加快數(shù)據(jù)采樣，也可以提升join的性能(join的字段是分桶字段)，因?yàn)榉滞翱梢源_保某個(gè)key對(duì)應(yīng)的數(shù)據(jù)在一個(gè)特定的桶內(nèi)(文件)，所以巧妙地選擇分桶字段可以大幅度提升join的性能。通常情況下，分桶字段可以選擇經(jīng)常用在過(guò)濾操作或者join操作的字段。

我們可以使用set.hive.enforce.bucketing = true啟用分桶設(shè)置。

當(dāng)使用分桶表時(shí)，最好將bucketmapjoin標(biāo)志設(shè)置為true，具體配置參數(shù)為：

SET hive.optimize.bucketmapjoin = true

CREATE TABLE table_name 
PARTITIONED BY (partition1 data_type, partition2 data_type,….) CLUSTERED BY (column_name1, column_name2, …) 
SORTED BY (column_name [ASC|DESC], …)] 
INTO num_buckets BUCKETS;

4.對(duì)中間數(shù)據(jù)啟用壓縮

復(fù)雜的Hive查詢(xún)通常會(huì)轉(zhuǎn)換為一系列多階段的MapReduce作業(yè)，并且這些作業(yè)將由Hive引擎鏈接起來(lái)以完成整個(gè)查詢(xún)。因此，此處的“中間輸出”是指上一個(gè)MapReduce作業(yè)的輸出，它將用作下一個(gè)MapReduce作業(yè)的輸入數(shù)據(jù)。

壓縮可以顯著減少中間數(shù)據(jù)量，從而在內(nèi)部減少了Map和Reduce之間的數(shù)據(jù)傳輸量。

我們可以使用以下屬性在中間輸出上啟用壓縮。

set hive.exec.compress.intermediate=true;
set hive.intermediate.compression.codec=org.apache.hadoop.io.compress.SnappyCodec;
set hive.intermediate.compression.type=BLOCK;

為了將最終輸出到HDFS的數(shù)據(jù)進(jìn)行壓縮，可以使用以下屬性：

set hive.exec.compress.output=true;

下面是一些可以使用的壓縮編解碼器

org.apache.hadoop.io.compress.DefaultCodec
org.apache.hadoop.io.compress.GzipCodec
org.apache.hadoop.io.compress.BZip2Codec
com.hadoop.compression.lzo.LzopCodec
org.apache.hadoop.io.compress.Lz4Codec
org.apache.hadoop.io.compress.SnappyCodec

5.Map端JOIN

map端join適用于當(dāng)一張表很小(可以存在內(nèi)存中)的情況，即可以將小表加載至內(nèi)存。Hive從0.7開(kāi)始支持自動(dòng)轉(zhuǎn)為map端join，具體配置如下：

SET hive.auto.convert.join=true; --  hivev0.11.0之后默認(rèn)true
SET hive.mapjoin.smalltable.filesize=600000000; -- 默認(rèn) 25m
SET hive.auto.convert.join.noconditionaltask=true; -- 默認(rèn)true，所以不需要指定map join hint
SET hive.auto.convert.join.noconditionaltask.size=10000000; -- 控制加載到內(nèi)存的表的大小

一旦開(kāi)啟map端join配置，Hive會(huì)自動(dòng)檢查小表是否大于hive.mapjoin.smalltable.filesize配置的大小，如果大于則轉(zhuǎn)為普通的join，如果小于則轉(zhuǎn)為map端join。

關(guān)于map端join的原理，如下圖所示：

首先，Task A(客戶端本地執(zhí)行的task)負(fù)責(zé)讀取小表a，并將其轉(zhuǎn)成一個(gè)HashTable的數(shù)據(jù)結(jié)構(gòu)，寫(xiě)入到本地文件，之后將其加載至分布式緩存。

然后，Task B任務(wù)會(huì)啟動(dòng)map任務(wù)讀取大表b，在Map階段，根據(jù)每條記錄與分布式緩存中的a表對(duì)應(yīng)的hashtable關(guān)聯(lián)，并輸出結(jié)果

注意：map端join沒(méi)有reduce任務(wù)，所以map直接輸出結(jié)果，即有多少個(gè)map任務(wù)就會(huì)產(chǎn)生多少個(gè)結(jié)果文件。

6.向量化

Hive中的向量化查詢(xún)執(zhí)行大大減少了典型查詢(xún)操作（如掃描，過(guò)濾器，聚合和連接）的CPU使用率。

標(biāo)準(zhǔn)查詢(xún)執(zhí)行系統(tǒng)一次處理一行，在處理下一行之前，單行數(shù)據(jù)會(huì)被查詢(xún)中的所有運(yùn)算符進(jìn)行處理，導(dǎo)致CPU使用效率非常低。在向量化查詢(xún)執(zhí)行中，數(shù)據(jù)行被批處理在一起（默認(rèn)=> 1024行），表示為一組列向量。

要使用向量化查詢(xún)執(zhí)行，必須以O(shè)RC格式（CDH 5）存儲(chǔ)數(shù)據(jù)，并設(shè)置以下變量。

SET hive.vectorized.execution.enabled=true

在CDH 6中默認(rèn)啟用Hive查詢(xún)向量化，啟用查詢(xún)向量化后，還可以設(shè)置其他屬性來(lái)調(diào)整查詢(xún)向量化的方式，具體可以參考cloudera官網(wǎng)。

7.謂詞下推

默認(rèn)生成的執(zhí)行計(jì)劃會(huì)在可見(jiàn)的位置執(zhí)行過(guò)濾器，但在某些情況下，某些過(guò)濾器表達(dá)式可以被推到更接近首次看到此特定數(shù)據(jù)的運(yùn)算符的位置。

比如下面的查詢(xún)：

select
    a.*,
    b.* 
from 
    a join b on (a.col1 = b.col1)
where a.col1 > 15 and b.col2 > 16

如果沒(méi)有謂詞下推，則在完成JOIN處理之后將執(zhí)行過(guò)濾條件**(a.col1> 15和b.col2> 16)**。因此，在這種情況下，JOIN將首先發(fā)生，并且可能產(chǎn)生更多的行，然后在進(jìn)行過(guò)濾操作。

使用謂詞下推，這兩個(gè)謂詞**(a.col1> 15和b.col2> 16)**將在JOIN之前被處理，因此它可能會(huì)從a和b中過(guò)濾掉連接中較早處理的大部分?jǐn)?shù)據(jù)行，因此，建議啟用謂詞下推。

通過(guò)將hive.optimize.ppd設(shè)置為true可以啟用謂詞下推。

SET hive.optimize.ppd=true

8.輸入格式選擇

Hive支持TEXTFILE, SEQUENCEFILE, AVRO, RCFILE, ORC,以及PARQUET文件格式，可以通過(guò)兩種方式指定表的文件格式：

• CREATE TABLE … STORE AS :即在建表時(shí)指定文件格式，默認(rèn)是TEXTFILE
• ALTER TABLE … [PARTITION partition_spec] SET FILEFORMAT :修改具體表的文件格式

如果未指定文件存儲(chǔ)格式，則默認(rèn)使用的是參數(shù)hive.default.fileformat設(shè)定的格式。

如果數(shù)據(jù)存儲(chǔ)在小于塊大小的小文件中，則可以使用SEQUENCE文件格式。如果要以減少存儲(chǔ)空間并提高性能的優(yōu)化方式存儲(chǔ)數(shù)據(jù)，則可以使用ORC文件格式，而當(dāng)列中嵌套的數(shù)據(jù)過(guò)多時(shí)，Parquet格式會(huì)很有用。因此，需要根據(jù)擁有的數(shù)據(jù)確定輸入文件格式。

9.啟動(dòng)嚴(yán)格模式

如果要查詢(xún)分區(qū)的Hive表，但不提供分區(qū)謂詞（分區(qū)列條件），則在這種情況下，將針對(duì)該表的所有分區(qū)發(fā)出查詢(xún)，這可能會(huì)非常耗時(shí)且占用資源。因此，我們將下面的屬性定義為strict，以指示在分區(qū)表上未提供分區(qū)謂詞的情況下編譯器將引發(fā)錯(cuò)誤。

SET hive.partition.pruning=strict

10.基于成本的優(yōu)化

Hive在提交最終執(zhí)行之前會(huì)優(yōu)化每個(gè)查詢(xún)的邏輯和物理執(zhí)行計(jì)劃?；诔杀镜膬?yōu)化會(huì)根據(jù)查詢(xún)成本進(jìn)行進(jìn)一步的優(yōu)化，從而可能產(chǎn)生不同的決策：比如如何決定JOIN的順序，執(zhí)行哪種類(lèi)型的JOIN以及并行度等。

可以通過(guò)設(shè)置以下參數(shù)來(lái)啟用基于成本的優(yōu)化。

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;

可以使用統(tǒng)計(jì)信息來(lái)優(yōu)化查詢(xún)以提高性能?；诔杀镜膬?yōu)化器（CBO）還使用統(tǒng)計(jì)信息來(lái)比較查詢(xún)計(jì)劃并選擇最佳計(jì)劃。通過(guò)查看統(tǒng)計(jì)信息而不是運(yùn)行查詢(xún)，效率會(huì)很高。

收集表的列統(tǒng)計(jì)信息：

ANALYZE TABLE mytable COMPUTE STATISTICS FOR COLUMNS;

查看my_db數(shù)據(jù)庫(kù)中my_table中my_id列的列統(tǒng)計(jì)信息：

DESCRIBE FORMATTED my_db.my_table my_id