從hdfs獲取hive結果太慢，因為maponly任務太多，執行hiveSQL查詢時如何合并查詢結果

hive查詢在“/tmp/hive/hive”的折疊中產生了太多的結果檔案，接近4W個任務。但是運行結果的總數只有100多個所以不知道有沒有辦法合并后的結果查詢，減少結果檔案數量，提高拉取結果的效率？

這是查詢的解釋

 ---------------------------------------------------- -- 
|                      Explain                       |
 ---------------------------------------------------- -- 
| STAGE DEPENDENCIES:                                |
|   Stage-1 is a root stage                          |
|   Stage-0 depends on stages: Stage-1               |
|                                                    |
| STAGE PLANS:                                       |
|   Stage: Stage-1                                   |
|     Map Reduce                                     |
|       Map Operator Tree:                           |
|           TableScan                                |
|             alias: kafka_program_log             |
|             filterExpr: ((msg like '%disk loss%') and (ds > '2022-05-01')) (type: boolean) |
|             Statistics: Num rows: 36938084350 Data size: 11081425337136 Basic stats: PARTIAL Column stats: PARTIAL |
|             Filter Operator                        |
|               predicate: (msg like '%disk loss%') (type: boolean) |
|               Statistics: Num rows: 18469042175 Data size: 5540712668568 Basic stats: COMPLETE Column stats: PARTIAL |
|               Select Operator                      |
|                 expressions: server (type: string), msg (type: string), ts (type: string), ds (type: string), h (type: string) |
|                 outputColumnNames: _col0, _col1, _col2, _col3, _col4 |
|                 Statistics: Num rows: 18469042175 Data size: 5540712668568 Basic stats: COMPLETE Column stats: PARTIAL |
|                 File Output Operator               |
|                   compressed: false                |
|                   Statistics: Num rows: 18469042175 Data size: 5540712668568 Basic stats: COMPLETE Column stats: PARTIAL |
|                   table:                           |
|                       input format: org.apache.hadoop.mapred.TextInputFormat |
|                       output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat |
|                       serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe |
|                                                    |
|   Stage: Stage-0                                   |
|     Fetch Operator                                 |
|       limit: -1                                    |
|       Processor Tree:                              |
|         ListSink                                   |
|                                                    |
 ---------------------------------------------------- -- 

uj5u.com熱心網友回復：

設定 mapred.max.split.size=2560000000;
增加單個map處理的檔案大小，從而減少map的數量

uj5u.com熱心網友回復：

1. 使用 ORC/Parquet 重新創建表，您將獲得更好的性能。這是加快速度的第一要務。
2. 您正在使用 like 運算子，這意味著掃描所有資料。您可能需要考慮，重寫它以改用 join/where 子句。這將運行得更快。這是一個示例，說明您可以做些什么來使事情變得更好。

    with words as --short cut for readable sub-query
    (
      select 
        log.msg 
      from 
        kafka_program_log log 
      lateral view EXPLODE(split(msg, ' ')) words as word  -- for each word in msg, make a row assumes ' disk loss ' is what is in the msg
      where 
        word in ('disk', 'loss' ) -- filter the words to the ones we care about.
      and 
        ds > '2022-05-01' -- filter dates to the ones we care about.
      group by 
        log.msg -- gather the msgs together
      having 
        count(word) >= 2  -- only pull back msg that have at least two words we are interested in.
    ) -- end sub-query
      select 
        * 
      from kafka_program_log log
      inner join 
        words.msg = log.msg  // This join should really reduce the data we examine
      where 
        msg like "%disk loss%" -- like is fine now to make sure it's exactly what we're looking for.

標籤：Hadoop 蜂巢 直线

上一篇：文本檔案到鏈表C

下一篇：將多個STDIN輸入從powershell腳本傳遞到perl腳本