hive04_DQL操作

Stitches發表於2024-08-08

注意點

全域性排序 OrderBy

SELECT <select_expression>, <select_expression>, ...
    FROM <table_name>
    ORDER BY <col_name> [ASC|DESC] [,col_name [ASC|DESC], ...]

Hive 中使用全域性排序時，會將所有資料交給一個 Reduce 任務進行計算，實現查詢結果的全域性排序。所以資料量大的情況下會耗費大量的時間。
Hive 適用於離線處理，執行全量計算任務時，一般不會用到全域性排序。如果涉及到全域性排序場景，需要將 Hive 處理後的資料存放到快速查詢的產品中，比如 Presto、Impala、ClickHouse 等等。
資料處理過程中的全域性排序，最好使用 UDF 轉換為區域性排序。
- 先預估資料的範圍，將資料劃分為多個批次；
- 每個批次會分發到一個 Reducer 執行任務，然後在每個 Reduce 作業中進行區域性排序。
- 一般不涉及到全域性排序，可以先透過子查詢減小查詢範圍，然後再排序。

如果是 TOPN 的情況，先用子查詢對每個 Reducer 排序，然後取前 N 個資料，最後對結果集進行全域性排序。

select t.id, t.name from 
(
    select id, name from <table_name>
    distributed by length(name) sort by length(name) desc limit 10
) t
order by length(t.user_name) desc limit 10;

區域性排序 SortBy

SELECT <select_expression>, <select_expression>, ...
    FROM <table_name>
    SORT BY <col_name> [ASC|DESC] [,col_name [ASC|DESC], ...]

區域性排序操作，Hive 會在每個 Reduce 任務中對資料進行排序，當啟動多個 Reduce 任務時，OrderBy 輸出一個檔案，SortBy 輸出多個檔案且區域性有序。

聚合操作 GroupBy、DistributeBy、ClusterBy

GroupBy：按照某些欄位的值進行分組，在底層 MapReduce 執行過程中，同一組的資料會傳送到同一個 Reduce 任務中，意味著每個 Reduce 會包含多組資料，同一組的資料會單獨進行聚合運算。

可以配置 Reducer 數量 mapred.reduce.tasks，或者配置 hive.groupby.skewindata=true 來最佳化資料傾斜問題。

select col1, [col2], count(1), sel_expr(聚合操作) from table
where condition                  -- Map端執行
group by col1 [,col2]            -- Reduce端執行
[having]                         -- Reduce端執行

DistributeBy：透過雜湊取模的方式，將列值相同的資料傳送到同一個 Reducer 任務，只是單純的分散資料，不執行其他操作。

SELECT <select_expression>, <select_expression>, ...
    FROM <table_name>
    DISTRIBUTE BY <col_list>
    [SORT BY <col_name> [ASC|DESC] [, col_name [ASC|DESC], ...] ]

DistrubuteBy 通常和 SortBy 一起使用，實現先聚合後排序。並且可以指定升序 ASC 還是降序 DESC，但 DistributeBy 必須在 SortBy 之前。

ClusterBy：把相同值的資料聚合到一起並且排序，效果等價於 distribute by col sort by col。

SELECT <select_expression>, <select_expression>, ...
    FROM <table_name>
    CLUSTER BY <col_list>

ClusterBy 沒有 DistributeBy 那麼靈活，並且不能自定義排序，當 DistributeBy 和 SortBy 列完全相同且按照升序排序時，等價於執行 ClusterBy。

Join最佳化

MySQL Join 最佳化

https://blog.csdn.net/norminv/article/details/108020102

MySQL JOIN 都是透過迴圈巢狀的方式實現，用小表驅動大表減少多次連線操作帶來的效能開銷。

left join：小表 left join 大表；
right join：大表 right join 小表；
用子查詢代替 JOIN 減少驅動表掃描行數。

舉個例子，如下用子查詢最佳化示例：

select
  o.no,s_order.no,sum(s_item.count),sum(after_sale_item.count)
  from
  buyer_order o
  left join seller_order s_order on o.id = s_order.buyer_order_id
  left join seller_order_item s_item on s_order.id = s_item.seller_order_id
  left join seller_order_after_sale after_sale on s_order.id = after_sale.seller_order_id
  left join seller_order_after_sale_item after_sale_item on after_sale.id = after_sale_item.after_sale_id
where o.add_time >='2019-05-01'
group by
  o.id,s_order.id
order by
  o.id
limit 0,10

用子查詢最佳化後：

select
  o.id,o.no,s_order.no,
  (select sum(sot.count) from seller_order so
    left join seller_order_item sot on so.id = sot.seller_order_id
        where so.id =s_order.id ),
  (select sum(osat.count) from seller_order_after_sale osa
    left join seller_order_after_sale_item osat on osa.id = osat.after_sale_id
        where osa.seller_order_id = s_order.id )
  from
  buyer_order o
  left join seller_order s_order on o.id = s_order.buyer_order_id
where o.addTime >='2019-05-01'
order by
  o.id
limit 0,10

透過子查詢減少了 left join 次數，從而減少驅動表的資料量；
減少了 groupby 的使用，方案一中先分組再取後 10 條；方案二先取後 10 條再執行聚合操作，效率更高。

SteamTable

Hive 執行 Join 操作時，預設會將前面的表直接載入進快取，後一張表進行 stream 處理，即 shuffle 操作。這樣可以減少 shuffle 過程，因為直接載入到快取中的表，只需要等待後面 stream表的資料，不需要進行 shuffle。

使用時透過宣告 /*+ STREAMTABLE(xxx) */ 來定義 stream 表：

SELECT /*+ STREAMTABLE(a) */ a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

MapJoin

MapJoin 即將小表直接載入到 Map 作業中，減少 shuffle 開銷。

SELECT /*+ MAPJOIN(b) */ a.key, a.value FROM a JOIN b ON a.key=b.key

資料傾斜

資料傾斜主要表現在，執行 map/reduce 時 reduce 大部分節點執行完畢，但是有一個或幾個 reduce 節點執行很慢，導致程式整體處理時間很長；

資料傾斜發生原因：

join：使用 join 關鍵字處理的問題；
- 小表驅動大表，但是 key 比較集中導致分發到某個 Reduce 上的資料遠高於平均值；
- 大表驅動大表，但是分桶判斷欄位空值過多，空值由一個 Reduce 處理；
group by：先分派後聚合，某個 Reduce 處理耗時很長；
count distinct：特殊值過多。

解決方案：

引數調節

Map 端部分聚合：配置 hive.map.aggr=true；
資料傾斜時進行負載均衡：配置 hive.groupby.skewindata=true；它生成的查詢計劃有兩個 MR Job，一個 MR Job 會將 Map 的結果隨機分不到 Reduce 中；另外一個 MR Job 則根據預處理結果按照 Key 值相同分佈到同一個 Reduce 中，最後完成聚合操作。

SQL調節

大小表Join：MapJoin 讓小表先進記憶體，在 Map 端完成 Reduce 操作，減少 shuffle；
大表大表Join：將空值變成字串加上隨機數，將傾斜資料分散到不同 Reduce，避免零值/空值分佈到同一個 Reduce 導致傾斜；

使用者自定義函式

Hive 除了支援內建函式外，還允許使用者自定義函式來擴充函式的功能；
UDF 對每一行資料處理，輸出一行資料；
UDAF 對多行資料處理，最終輸出一行資料，一般用於聚合操作；
UDTF 對一行資料處理，輸出多個結果，比如將一行字串按照某個字元拆分後進行儲存，表的行數會增加。

建立函式：

-- 臨時建立
ADD JARS[S] <local_hdfs_path>;
CREATE TEMPORARY FUNCTION <function_name> AS <class_name>;
DROP TEMPORARY FUNCTION <function_name>;

--- 永久建立
CREATE PERMANENT FUNCTION <function_name> AS <class_name> [USING JAR|FILE <file_uri>];
DROP PERMANENT FUNCTION <function_name>;

UDF

實現方式有兩種：繼承UDF、繼承 GenericUDF，其中 GenericUDF 處理起來更加靈活。

繼承 GenericUDF 的步驟：

initialize 方法，檢查輸入資料並初始化；
evaluate 方法，執行資料處理過程，返回最終結果；
getDisplayString 方法，定義 explain 的返回內容。

@org.apache.hadoop.hive.ql.exec.Description(name = "AvgScore",
        extended = "示例：select AvgScore(score) from src;",
        value = "_FUNC_(col)-對Map型別儲存的學生成績進行平均值計算")
public class AvgScore extends GenericUDF {
    
    // 檢查輸入資料，初始化輸入資料
    @Override
    public ObjectInspector initialize(ObjectInspector[] objectInspectors) throws UDFArgumentException {…}
    
    // 資料處理，返回最終結果
    @Override
    public Object evaluate(DeferredObject[] deferredObjects) throws HiveException {…}
    
    // 函式執行 HQL Explain 展示的字串內容
    @Override
    public String getDisplayString(String[] strings) {…}
}

然後將函式打包為 jar 上傳到伺服器對應路徑 /xxxx/xxx/xxx.jar；
將 jar 包新增到 hive 的 classpath：add jar /xxxx/xxx/xxx.jar；
建立臨時函式與開發好的 java class 關聯：create temporary function func_name as "xxxx.xxx.xxx.MyUDF"；
hql 中使用臨時函式：select func_name(col) from src;；

UDAF

實現方式：繼承 UDAF、AbstractGenericUDAFResolver，其中 AbstractGenericUDAFResolver 更加靈活。

使用 AbstractGenericUDAFResolver 的步驟：

繼承 AbstractAggregationBuffer，來儲存中間結果；
繼承 GenericUDAFEvaluator，實現 UDAF 處理流程；
繼承 AbstractGenericUDAFResolver，註冊 UDAF。

// UDAF 註冊函式
public class FieldLength extends AbstractGenericUDAFResolver {
    @Override
    public GenericUDAFEvaluator getEvaluator(GenericUDAFParameterInfo info) throws SemanticException {
        return super.getEvaluator(info);
    }
}


// 儲存中間結果
class FieldLengthAggregationBuffer extends GenericUDAFEvaluator.AbstractAggregationBuffer {
    private Integer value = 0;

    public Integer getValue() {
        return value;
    }

    public void setValue(Integer value) {
        this.value = value;
    }

    @Override
    public int estimate() {
        return JavaDataModel.PRIMITIVES1;
    }

    public void add(int addVal) {
        synchronized (value) {
            value += addVal;
        }
    }
}


// 資料處理函式
class FieldLengthUDAFEvaluator extends GenericUDAFEvaluator {
    // 輸入
    private PrimitiveObjectInspector inputOI;
    // 輸出
    private ObjectInspector outputOI;
    // 前一個階段輸出
    private PrimitiveObjectInspector integerOI;

    /**
     * 資料校驗、資料初始化
     * 由於 UDAF 會執行 Map、Reduce 兩個階段任務，所以根據 Mode.xxx 區分具體階段
     * @param m
     * @param parameters
     * @return
     * @throws HiveException
     */
    @Override
    public ObjectInspector init(Mode m, ObjectInspector[] parameters) throws HiveException {
        super.init(m, parameters);
        // Map 階段，輸入為原始資料
        if (Mode.PARTIAL1.equals(m) || Mode.COMPLETE.equals(m)) {
            inputOI = (PrimitiveObjectInspector) parameters[0];
        } else {
            // combiner、redeuce 階段基於前一個階段的返回值作為輸入
            integerOI = (PrimitiveObjectInspector) parameters[0];
        }
        // 指定輸出型別
        outputOI = ObjectInspectorFactory.getReflectionObjectInspector(
                Integer.class,
                ObjectInspectorFactory.ObjectInspectorOptions.JAVA
        );
        return outputOI;
    }

    /**
     * 獲取中間存放結果物件
     * @return
     * @throws HiveException
     */
    @Override
    public AggregationBuffer getNewAggregationBuffer() throws HiveException {
        return new FieldLengthAggregationBuffer();
    }

    /**
     * 重置中間結果
     * @param aggregationBuffer
     * @throws HiveException
     */
    @Override
    public void reset(AggregationBuffer aggregationBuffer) throws HiveException {
        ((FieldLengthAggregationBuffer)aggregationBuffer).setValue(0);
    }

    /**
     * Map 階段
     * @param aggregationBuffer
     * @param objects
     * @throws HiveException
     */
    @Override
    public void iterate(AggregationBuffer aggregationBuffer, Object[] objects) throws HiveException {
        if (objects == null || objects.length < 1) {
            return;
        }
        Object javaobj = inputOI.getPrimitiveJavaObject(objects[0]);
        ((FieldLengthAggregationBuffer)aggregationBuffer).add(String.valueOf(javaobj).length());
    }

    /**
     * 返回 Map、Combiner 階段結果
     * @param aggregationBuffer
     * @return
     * @throws HiveException
     */
    @Override
    public Object terminatePartial(AggregationBuffer aggregationBuffer) throws HiveException {
        return terminate(aggregationBuffer);
    }

    /**
     * Reduce 階段
     * @param aggregationBuffer
     * @param o
     * @throws HiveException
     */
    @Override
    public void merge(AggregationBuffer aggregationBuffer, Object o) throws HiveException {
        ((FieldLengthAggregationBuffer) agg).add((Integer)integerOI.getPrimitiveJavaObject(partial));
    }

    /**
     * 返回最終結果
     * @param aggregationBuffer
     * @return
     * @throws HiveException
     */
    @Override
    public Object terminate(AggregationBuffer aggregationBuffer) throws HiveException {
        return ((FieldLengthAggregationBuffer)aggregationBuffer).getValue();
    }
}

UDTF

繼承 GenericUDTF 類，並重寫 initialize、process、close 方法：

initialize：初始化返回值型別；
process：具體資料處理過程；
close：清理收尾工作；
forward：傳遞輸出給收集器；

public class JsonParser extends GenericUDTF {
    private PrimitiveObjectInspector stringOI = null;

    // 輸入資料解析，初始化
    @Override
    public StructObjectInspector initialize(ObjectInspector[] argOIs) throws UDFArgumentException {
        if (argOIs.length != 1) {
            throw new UDFArgumentException("take only one argument");
        }
        // 輸入必須為 PRIMITIVE 型別，且具體型別必須為 String
        if (argOIs[0].getCategory() != ObjectInspector.Category.PRIMITIVE &&
                ((PrimitiveObjectInspector)argOIs[0]).getPrimitiveCategory() != PrimitiveObjectInspector.PrimitiveCategory.STRING) {
            throw new UDFArgumentException("take only one string argument");
        }
        // 初始化輸入
        stringOI = (PrimitiveObjectInspector) argOIs[0];
        // 定義輸出型別
        List<String> fieldNames = new ArrayList<String>();
        List<ObjectInspector> fieldOIs = new ArrayList<ObjectInspector>();
        fieldNames.add("name");
        fieldNames.add("value");
        fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
        fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
        return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames, fieldOIs);
    }

    private ArrayList<Object[]> parseInputRecord(String feature) {
        ArrayList<Object[]> resultList = null;
        //......
        //.....
        return resultList;
    }

    @Override
    public void process(Object[] objects) throws HiveException {
        final String feature = stringOI.getPrimitiveJavaObject(objects[0]).toString();
        ArrayList<Object[]> results = parseInputRecord(feature);
        Iterator<Object[]> it = results.iterator();
        while (it.hasNext()) {
            Object[] strs = it.next();
            // 結果 Key-Value 傳遞給收集器
            forward(strs);
        }
    }

    @Override
    public void close() throws HiveException {

    }
}

JQ操作標籤--樣式操作、位置操作、尺寸、文字操作、獲取值操作、屬性操作、文件處理、事件
2024-06-27
事件
sqlite相關--------adb shell操作，工具操作，程式操作
2013-11-22
SQLite
Go 操作 Redis 的基本操作
2019-09-19
GoRedis
常用操作 / 資料庫操作
2017-03-01
資料庫
原子操作 vs 非原子操作
2014-01-09
操作
2024-04-21
坐下坐下，基本操作（ZooKeeper 操作篇）
2021-02-04
JavaScript騷操作之操作符
2018-12-16
JavaScript
Java檔案操作讀寫操作
2016-07-10
Java
Hive高階操作-查詢操作
2024-06-28
Hive
使用java操作ranger，hdfs ranger授權操作，hive ranger授權操作
2020-10-03
JavaRangerHive
Go 語言操作 MySQL 之 CURD 操作
2020-07-01
GoMySql
Oracle並行操作——並行DML操作
2016-09-19
Oracle並行
js操作
2021-09-09
JS
gorm 操作
2021-07-28
GoORM
firewalld操作
2021-08-31
DOM 操作
2018-07-28
docker操作
2020-11-13
Docker
Jedis操作
2020-10-26
Git 操作
2020-11-04
Git
BOM操作
2022-02-09
mongoDB操作
2018-07-07
MongoDB
DOM操作
2018-05-07
npm 操作
2017-12-26
NPM
SourceTree 操作
2017-12-31
mysql操作
2014-04-26
MySql
操作欄
2014-05-26
操作格子
2014-11-23
快捷操作
2013-10-24
fileStream操作
2015-06-06
maven 操作
2015-08-09
Maven
奇怪++操作
2015-07-18
IO操作
2008-10-20
操作cookie
2008-09-12
Cookie
rman操作
2009-02-25
Sqlldr操作
2009-02-25
SQL
組操作
2011-07-08
XML操作
2011-04-19
XML