hive怎麼處理過濾掉滿足多個多個條件的記錄_Hive基礎面試題總結

 點選關注上方“知了小巷”，

設為“置頂或星标”，第一時間送達幹貨。

1. 描述一下Hive動态分區和分桶使用場景和使用方法

1. 分區

按照資料表的某列或某些列分為多個分區，分區從形式上可以了解為檔案夾，比如我們要收集某個大型網站的日志資料，一個網站每天的日志資料存在同一張表上，由于每天會生成大量的日志，導緻資料表的内容巨大，在查詢時進行全表掃描耗費的資源非常多。那其實這個情況下，我們可以按照日期對資料表進行分區，不同日期的資料存放在不同的分區，在查詢時隻要指定分區字段的值就可以直接從該分區查找。分區是以字段的形式在表結構中存在，通過describe table指令可以檢視到字段存在，但是該字段不存放實際的資料内容，僅僅是分區的辨別。

• (1) 靜态分區

• (2) 動态分區

• (3) 靜态分區和動态分區的差別

靜态分區與動态分區的主要差別在于靜态分區是手動指定，而動态分區是通過資料來進行判斷。詳細來說：

靜态分區：

• 靜态分區是在編譯期間指定的指定分區名。
• 支援load和insert兩種插入方式。
• 适用于分區數少，分區名可以明确的資料。

動态分區：

• 根據分區字段的實際值，動态進行分區
• 是在SQL執行的時候進行分區
• 需要先将動态分區設定打開

• 隻能用insert方式
• 通過普通表選出的字段包含分區字段，分區字段放置在最後，多個分區字段按照分區順序放置。

1. 分桶

分桶是相對分區進行耕細粒度的劃分。分桶将整個資料内容按照某列屬性值得到hash值進行分區，如果按照name屬性分為3個桶，就是對name屬性值的hash值對3取模，按照取模結果對資料分桶。如取模結果為0的資料記錄存放到一個檔案，取模為1的資料存放到一個檔案，取模為2的資料存放到一個檔案。

對于每一個表(table)或者分區，Hive可以進一步組織成桶，也就是說桶是更為細粒度的資料範圍劃分。Hive也是針對某一列進行桶的組織。Hive采用對列值進行哈希，然後除以桶的個數求餘的方式決定該條記錄存放在哪個桶當中。把表(或者分區)組織成桶(Bucket)有兩個理由：

• (1) 獲得更高的查詢處理效率

  桶為表加上了額外的結構，Hive在處理有些查詢時能夠利用這個結構。具體而言，連接配接兩個在(包含連接配接列的)相同列上劃分了桶的表，可以使用Map端連接配接(Map-Side join)高效的實作。比如join操作，對于join操作的兩個表有一個相同的列，如果對這兩個表都進行了桶操作。那麼将儲存相同列值的桶進行join操作就可以，可以大大減少join的資料量。

• (2) 使取樣(samping)更高效

  在處理大規模資料集時，在開發和修改查詢的階段，如果能在資料集的一小部分資料上試運作查詢，會帶來很多友善。

2. Hive是怎麼內建HBase的？

1. 首先我們需要将HBase的用戶端jar拷貝入Hive lib目錄下。
2. 修改hive/conf下的hive-site.xml配置檔案，在最後添加如下屬性：

1. 啟動Hive，建立管理表hbase_table_1，指定資料存儲在HBase表中。 類似下面的方式：

1. 往Hive表hbase_table_1表中插入資料。

CDH版本不需要額外操作，相同CDH版本下的軟體已經做過整合。重點是HBaseStorageHandler。

3. Hive查詢的時候on和where有什麼差別？

左右關聯時：

• 條件不為主表條件時，放在on和where後面一樣。
• 條件為主表條件時，放在on後面，結果為主表全量，放在where後面為主表條件篩選過後的全量。

sql1： 如果是left join在on上寫主表a的條件不會生效，全表掃描。sql2： 如果是left join在on上寫副表b的條件會生效，但是語義與寫到where條件不同。sql3： 如果是inner join在on上寫主表a、副表b的條件都會生效。sql4： 建議寫SQL，大部分的語義都是先過濾資料然後再join，是以在不了解join on+ 條件的情況下，條件盡量别寫在on後，直接寫到where後就OK了。

on中的條件是在join時執行，where中的條件，是在join結束後對結果集進行執行過濾。

4. Hive裡面的left join時怎麼執行的？

不考慮where條件下，left join會把左表所有資料查詢出來，on及其後面的條件僅僅會影響右表的資料(符合就顯示，不符合全部為null)；

在join階段，where子句的條件都不會被使用，僅在join階段完成以後，where子句條件才會被使用，它将從比對階段産生的資料中檢索過濾。

是以左連接配接關注的是左邊的主表資料，不應該把on後面的從表中的條件加到where後，這樣會影響原有主表中的資料。where後面：是先連接配接生成臨時查詢結果，然後再篩選。on後面：先根據條件過濾篩選，再連接配接生成臨時查詢結果。

對于條件在on加個and還是用子查詢，查詢結果是一模一樣的，至于如何使用。需要分情況， 用子查詢的話會多一個MapTask，但是如果利用這個子查詢能過濾很多資料的話，用子查詢還是比較建議的，因為不會加載太多的資料到記憶體中，如果過濾資料不多的情況下，建議使用on後面加and條件。

5. Hive内部表、外部表、分區表

1. 内部表、管理表(MANAGE TABLE)

• 與資料庫中的Table在概念上是類似的。
• 每一個Table在Hive中都有一個相應的目錄存儲資料。
• 所有的Table資料(不包括External Table)都儲存在這個目錄中。
• 删除表時，中繼資料與資料都會被删除

1. 外部表

• 指向已經在HDFS中存在的資料，可以建立Partition。
• 它和内部表在中繼資料的組織上是相同的，而實際資料的存儲則有較大的差異。
• 外部表隻有一個過程，加載資料和建立表同時完成，并不會移動到資料庫目錄中，隻是與外部資料建立一個連接配接。當删除一個外部表時，僅删除連接配接和中繼資料。

1. 分區表

• Partition對應于資料庫的Partition列的聚集索引
• 在Hive中，表中的一個Partition對應于表下的一個目錄，所有的Partition的資料都存儲在對應的目錄中。

6. Hive和MySQL的差別，大資料為什麼不用MySQL做大規模資料存儲和資料處理？

Hive是基于Hadoop建構的一套資料倉庫分析系統，它提供了豐富的SQL查詢方式來分析存儲在Hadoop分布式檔案系統中的資料：可以将結構化資料檔案映射為一張資料庫表，并提供完整的SQL查詢功能；可以将SQL語句轉換為MapReduce任務運作，通過自己的SQL查詢分析需要的内容，這套SQL簡稱Hive SQL，使得不熟悉MapReduce開發人員可以友善地利用SQL語言-查詢、彙總和分析資料。而MapReduce開發人員可以把自己寫的Mapper和Reducer作為插件來支援Hive做更複雜的資料分析。

它與關系型資料庫的SQL略有不同，但支援了絕大多數的語句如DDL、DML以及常見的聚合函數、連接配接查詢、條件查詢。它還提供了一系列的工具進行資料提取轉化加載，用來存儲、查詢和分析存儲在Hadoop中的大規模資料集，并支援UDF(User-Defined Function)、UDAF(User-Defined AggregateFunction)和UDTF(User-Defined Table-Generating Function)，也可以實作對map和reduce函數的定制，為資料操作提供了良好的伸縮性和可擴充性。

Hive不适合用于聯機事務處理(OLTP)，也不提供實時查詢功能。它最适合應用在基于大量不可變資料的批處理作業。Hive的特點包括：可伸縮(在Hadoop的叢集上動态添加裝置)、可擴充、容錯、輸入格式的松散耦合。

Hive建構在基于靜态批處理的Hadoop之上，Hadoop通常都有較高的延遲并且在作業送出和排程的時候需要大量的開銷。是以，Hive并不能在大規模資料集上實作低延遲快速的查詢。例如，Hive在幾百MB的資料集上執行查詢一般有分鐘級的時間延遲。

是以，Hive并不适合那些需要高實時性的應用，例如，聯機事務處理(OLTP)。Hive查詢操作過程嚴格遵守Hadoop MapReduce的作業執行模型，Hive将使用者的HiveQL語句通過解釋器轉換為MapReduce作業送出到Hadoop叢集上，Hadoop監控作業執行過程，然後傳回作業執行結果給使用者。Hive并非為聯機事務處理而設計，Hive并不提供實時的查詢和基于行級的資料更新操作。Hive的最佳使用場景是大資料集的批處理作業，例如，網絡日志分析。

總結：

• 查詢語言不同，Hive是hql語言，MySQL是SQL語句
• 資料存儲位置不通，Hive是把資料存儲在HDFS上，而MySQL資料是存儲在自己的系統中
• 資料格式，Hive資料格式可以使用者自定義，MySQL有自己的系統定義格式
• 延遲性，Hive延遲較高，而MySQL延遲較低
• 資料規模，Hive存儲的資料量超級大，而MySQL隻是存儲一些少量的業務資料
• 底層執行原理：Hive底層預設使用的是MapReduce，而MySQL是Executor執行器

7. 簡述Hive調優

【Hive企業級調優】

1. 設定本地模式
2. 并行執行
3. JVM重用
4. 嚴格模式
5. 合理設定map和reduce的數量
6. Fetch抓取
7. 防止資料傾斜參數的開啟，會生成兩個MR job
8. explain執行計劃，通過執行計劃來調節SQL語句

8. Hive資料傾斜原因和處理

1. 産生原因

• (1) K分布不均勻
• (2) 業務資料本身的特性
• (3) 建表時考慮不周
• (4) 某些SQL語句本身就有資料傾斜，例如：大表join小表：其中小表key比較集中，分發到某一個或幾個Reduce上的資料遠高于平均值。大表join大表：但是分桶的判斷字段0值或空值過多，這些空值都由一個Reduce處理，非常慢。group by：group by次元過小，某個值的數量過多，處理這個值的Reduce非常耗時。Count Distinct：某特殊值過多，處理此特殊值的Reduce耗時。

1. 解決方案

參數調節

Map端部分聚合，相當于Combiner。

有資料傾斜的時候進行負載均衡，當選項設定為true，生成的查詢計劃會有兩個MRJob。第一個MRJob中，Map的輸出結果會随機分不到Reduce中，每個Reduce做部分聚合操作，并輸出結果，這樣處理的結果是相同的Group By Key有可能被分發到不同的Reduce中，進而達到負載均衡的目的；第二個MRJob再根據預處理的資料結果按照Group By Key分布到Reduce中(這個過程可以保證相同的Group By Key被分不到同一個Reduce中)，最後完成最終的聚合操作。

SQL調整

• 如何join：關于驅動表的選取，選用join key分布最均勻的表作為驅動表，做好列裁剪和filter操作，以達到兩表做join的時候，資料量相對變小的效果。
• 大小表join：使用map join讓小的次元表(1000條以下的記錄條數)先進記憶體。在Map端完成Reduce操作。
• 大表join大表：把空值的Key變成一個字元串加上随機數，把傾斜的資料分到不同的Reduce上，由于null值關聯不上，處理後并不影響最終結果。
• count distinct大量相同特殊值，将值為空的情況單獨處理，如果是計算count distinct，可以不用處理，直接過濾，在最後結果中加1。如果還有其他計算，需要進行group by，可以先将值為空的記錄單獨處理，再和其他計算結果進行union all。
• group by次元過小：采用sum() group by的方式來替換count(distinct)完成計算。
• 特殊情況預處理：在業務邏輯優化效果不大的情況下，有些時候是可以将傾斜的資料單獨拿出來處理。最後union回去。

9. Hive的自定義函數實作了什麼接口什麼方法？

Hive自定義函數包括三種：UDF UDAF UDTF。

1. UDF：一進一出，繼承了UDF類，并重寫evaluate方法。
2. UDAF：聚合函數，多進一出。如count max min
3. UDTF：一進多出，如lateral view explore

10. HiveSQL如何查詢A表中B表不存的資料？

11. 如何控制Hive中Mapper和Reducer的個數？

1. 控制Hive任務中的map數

   通常情況下，作業會通過input的目錄産生一個或者多個map任務。

   主要的決定因素有：input的檔案總個數，input的檔案大小，叢集設定的檔案塊大小(目前為128MB，可以在Hive中通過set dfs.block.size;指令檢視到，該參數不能自定義修改)

   舉例：假設input目錄下有1個檔案a，大小為780MB，那麼Hadoop會将該檔案a分割成7個塊(6個128MB的塊和1個12MB的塊)，進而産生7個map數。假設input目錄下有3個文a, b, c大小分别為10M、20M、130M，那麼Hadoop會分割成4個塊(10M、20M、128M、2M)，進而産生4個map數，即，如果檔案大于塊大小(128MB)，那麼會拆分，如果小于塊大小，則把該檔案當成一個塊。

• 問題1  是不是map數越多越好？

  并不是越多越好。如果一個任務有很多小檔案(遠遠小于塊大小128MB)，則每個小檔案也會被當做一個塊，用一個map任務來完成，而一個map任務啟動和初始化的時間遠遠大于邏輯處理的時間，就會造成很大的資源浪費。而且，同時可執行的map數是受限的。

• 問題2 是不是保證每個map處理接近128MB的檔案塊，就高枕無憂？

  答案是不一定，比如有一個127MB的檔案，正常會用一個map去完成，但這個檔案隻有一個或者兩個小字段，卻有幾千萬的記錄，如果map處理的邏輯比較複雜，用一個map任務去做，肯定也比較耗時。

針對上面的兩個問題，需要分别減少map數和增加map數來解決：

• (1) 通過合并小檔案，減少map數？假設一個SQL：

該任務的inputdir /xxxx/p_data/p_data-etl/pt/pt=20200915公有194個檔案，其中很多是遠遠小于128MB的小檔案，總大小9GB，正常執行會用194個map任務。Map總共消耗的計算資源：SLOTS_MILLIS_MAPS=623020 我通過以下方法來在map執行前合并小檔案，減少map數：

在執行count語句，用了74個map任務，map消耗的計算資源：SLOTS_MILLIS_MAPS=333500，對于這個簡單的SQL任務，執行時間上可能差不多，但節省了一半的計算資源。

其中，100000000辨別100MB，CombineHiveInputFormat是用來表示在執行前對小檔案進行合并，大于檔案塊大小128MB的，按照128M來分割，小于128MB大于100MB的，按照100M來分割，把哪些小于100MB的(包括小檔案和分割大檔案剩下的)，進行合并，最終生成了74個塊。

• (2) 如何适當的增加map個數？當input的檔案都很大，任務邏輯複雜，map執行非常慢的時候，可以考慮增加map數，來使得每個map處理的資料量減少，進而提高任務的執行效率。假設有這樣一個任務：

如果表a隻有一個檔案，大小為120MB，但包含幾千萬的記錄，如果用1個map去完成這個任務，肯定是比較耗時的，這種情況下，要考慮将一個檔案合理的拆分成多個，這樣就可以用多個map任務去完成。

這樣會将a表的記錄，随機的分散到包含10個檔案的a_1表中，再用a_1表代替a表，則會用10個map任務來完成。每個map任務處理大于12MB(幾百萬記錄)的資料，效率肯定會好很多。

map數量是要根據實際情況：使得大資料量利用合适的map數，使得單個map任務處理合适的資料量。

1. 控制Hive任務中的reduce數

Hive自己如何确定reduce數：reduce個數的設定極大影響任務執行效率，不指定reduce個數的情況下，Hive會猜測确定一個reduce個數，

計算reducer數的公式很簡單N=min(參數2，總的輸入資料量/參數1)；

如果reduce的輸入(map的輸出)總大小不超過1G，那麼隻會有一個reduce任務

調整reduce個數方法一

調整hive.exec.reducers.bytes.per.reducer參數的值：

調整reduce個數方法二

reduce個數也不是越多越好

和map數量一樣，啟動和初始化reduce也會消耗時間和資源；另外，有多少個reduce，就會有多少個輸出檔案，如果生産了很多小檔案，而且這些小檔案是作為下一個任務的輸入，則也會出現小檔案過多的問題。

什麼情況下隻有一個reduce？

有時候，不管資料量有多大，也不管有沒有設定調整reduce個數的參數，任務中一直都隻有一個reduce任務；其實隻有一個reduce任務的情況，除了資料量小于hive.exec.reducers.bytes.per.reducer參數值之外，還有以下原因：

• (1) 沒有group by的聚合計算

  比如直接寫成

• (2) order by 全局排序
• (3) 有笛卡爾積 這些操作隻能找辦法來變通和避免，沒有其他好的辦法，Hadoop不得不用一個reduce去完成；同樣，在設定reduce個數的時候需要考慮兩個原則：大資料量利用合适的reduce數，單個reduce任務處理合适的資料量。

12. Hive中的Order, Sort, Cluster, and Distribute By

1. order byHive中的order by與傳統的SQL語言中的order by作用是一樣的，會對查詢的結果做一次全局排序，是以，在Hive中使用order by，所有資料都會到同一個reducer進行處理。大量資料的時候，這是一個非常耗時的操作。如果制定了參數hive.mapred.mode=strict(預設值是nonstrict)，這時就必須指定limit來限制輸出條數，因為所有資料都會在同一個Reducer端進行處理，資料量大的情況下可能出不來結果，是以在strict模式下必須指定輸出的條數。

2. sort by

   Hive中指定了sort by，那麼在每個Reducer端都會做排序，也就是說保證了局部有序(每個reducer出來的資料都是有序的，但是不能保證所有的資料是有序的，除非隻有一個reducer)，好處是：執行了局部排序之後可以為接下去的全局排序提高不少的效率(再做一次歸并)。

3. distribute by和sort by一起使用

   distribute by是控制map的輸出在reducer是如何劃分的，比如有一張店鋪表store，mid是所屬商戶，money是商戶的盈利，name是店鋪名字。HiveSQL語句：

所有mid相同的資料會被發送到同一個reducer去處理，因為指定了distribute by mid，統計出每個商戶中各個商店的盈利的排序(排序是全局有序的，相同商戶是在同一個reducer處理)。distribute by要在sort by之前。

1. cluster by

   cluster by的功能就是distribute by和sort by相結合，功能是一樣的。

被cluster by指定的列隻能是降序，不能指定asc和desc。

13. Hive中的split、coalesce以及collect list函數的用法？

1. split将字元串轉化為數組，即split('a,b,c,d') ==> ["a","b","c","d"]
2. coalesce(T v1, T v2, ...)傳回參數中的第一個非空值：如果所有值都為null，則傳回null。如果某個預設值為null，想要傳回的不是null，而是0或者其他值，可以使用coalesce函數：

1. collect_list将分組中的某列轉換為一個數組傳回，不去重(列轉行函數)。

1. collect_set也是将分組中的某列轉為一個數組傳回，但是去重。

14. 描述一下資料庫中的null，null在Hive底層是如何存儲的？

1. null與任何值運算的結果都是null，可以使用is null、is not null函數指定其值為null情況下的取值。
2. null在Hive底層預設使用'\N'來存儲，可以通過alter table test set SERDEPROPERTIES('serialization.null.format'='a')來修改。
3. 查詢出t1表中存在但是t2表中不存在的id相對應的t1表的所有字段。

15. Hive中有哪些儲存中繼資料的方式及其特點

• 内嵌模式，資料庫derby，也是基于Java、JDBC和SQL标準
• 本地模式，MySQL資料庫，資料存儲模式可以自己設定，持久化好，檢視友善。
• 遠端模式，用于非Java用戶端通路中繼資料庫，在服務端啟動MetaStoreServer，用戶端利用Thrift協定通過MetaStoreServer通路中繼資料庫。将MetaStore分離出來獨立成一個Hive服務(可以部署多個)，可以避免認證資訊洩露等。

16. 生産環境中為什麼建議使用外部表？

1. 因為外部表不會加載資料到Hive，減少資料傳輸、資料還能共享。
2. Hive不會修改資料，是以無需擔心資料的損壞。
3. 删除表時，隻删除表結構、不删除資料。

往期推薦：

MapReduce和YARN基礎面試題總結

HDFS基礎面試題總結

建設資料中台到底有什麼⽤？

資料中台從哪⾥來，要到哪⾥去？

資料中台在⽹易電商業務的最佳實踐

知了小巷

長按識别二維碼，一鍵關注