編號函式 自定義函式 集合型別 表的優化 資料傾斜

T D Z發表於2020-12-04
函式型別優化

一.編號函式

1. row_number()  --僅僅定義組內的資料進行編號   1 2 3 4

孫悟空	語文	87
孫悟空	數學	95
娜娜	英語	84
宋宋	語文	64
孫悟空	英語	68
宋宋	英語	84
婷婷	語文	65
娜娜	語文	94
宋宋	數學	86
婷婷	數學	85
娜娜	數學	56
婷婷	英語	78

select
*,
row_number() over(partition by subject order by score desc) --分組倒序 row_number:編號
from
tb_sub

+--------------+-----------------+---------------+----------------------+
| tb_sub.name  | tb_sub.subject  | tb_sub.score  | row_number_window_0  |
+--------------+-----------------+---------------+----------------------+
| 孫悟空          | 數學              | 95.0          | 1                    |
| 宋宋           | 數學              | 86.0          | 2                    |
| 婷婷           | 數學              | 85.0          | 3                    |
| 娜娜           | 數學              | 56.0          | 4                    |
| 宋宋           | 英語              | 84.0          | 1                    |
| 娜娜           | 英語              | 84.0          | 2                    |
| 婷婷           | 英語              | 78.0          | 3                    |
| 孫悟空          | 英語              | 68.0          | 4                    |
| 娜娜           | 語文              | 94.0          | 1                    |
| 孫悟空          | 語文              | 87.0          | 2                    |
| 婷婷           | 語文              | 65.0          | 3                    |
| 宋宋           | 語文              | 64.0          | 4                    |
+--------------+-----------------+---------------+----------------------+

2. rank()  --編號的時候 排序欄位會參與編號相同的排序欄位 標號一致, 總標號不變

select
*,
rank() over(partition by subject order by score desc) --分組倒序 rank() 分數相同並列排名
from                                                    --總編號不變
tb_sub

+--------------+-----------------+---------------+----------------+
| tb_sub.name  | tb_sub.subject  | tb_sub.score  | rank_window_0  |
+--------------+-----------------+---------------+----------------+
| 孫悟空          | 數學              | 95.0          | 1              |
| 宋宋           | 數學              | 86.0          | 2              |
| 婷婷           | 數學              | 85.0          | 3              |
| 娜娜           | 數學              | 56.0          | 4              |
| 宋宋           | 英語              | 84.0          | 1              |
| 娜娜           | 英語              | 84.0          | 1              |
| 婷婷           | 英語              | 78.0          | 3              |
| 孫悟空          | 英語              | 68.0          | 4              |
| 娜娜           | 語文              | 94.0          | 1              |
| 孫悟空          | 語文              | 87.0          | 2              |
| 婷婷           | 語文              | 65.0          | 3              |
| 宋宋           | 語文              | 64.0          | 4              |
+--------------+-----------------+---------------+----------------+

3. dense_rank()  --編號的時候 排序欄位會參與編號相同的排序欄位標號一致 , 總標號有可能變小

select
*,
dense_rank() over(partition by subject order by score desc) --分組倒序 
from                       --dense-rank() 成績相同並列排名,第3人實為第2名,總編號可能會改變
tb_sub

+--------------+-----------------+---------------+----------------------+
| tb_sub.name  | tb_sub.subject  | tb_sub.score  | dense_rank_window_0  |
+--------------+-----------------+---------------+----------------------+
| 孫悟空          | 數學              | 95.0          | 1                    |
| 宋宋           | 數學              | 86.0          | 2                    |
| 婷婷           | 數學              | 85.0          | 3                    |
| 娜娜           | 數學              | 56.0          | 4                    |
| 宋宋           | 英語              | 84.0          | 1                    |
| 娜娜           | 英語              | 84.0          | 1                    |
| 婷婷           | 英語              | 78.0          | 2                    |
| 孫悟空          | 英語              | 68.0          | 3                    |
| 娜娜           | 語文              | 94.0          | 1                    |
| 孫悟空          | 語文              | 87.0          | 2                    |
| 婷婷           | 語文              | 65.0          | 3                    |
| 宋宋           | 語文              | 64.0          | 4                    |
+--------------+-----------------+---------------+----------------------+

二. 自定義函式

程式設計步驟:

1. 建立maven工程

2. 匯入依賴

 

<dependencies>

      <!-- https://mvnrepository.com/artifact/org.apache.hive/hive-exec -->

      <dependency>

          <groupId>org.apache.hive</groupId>

          <artifactId>hive-exec</artifactId>

          <version>1.2.1</version>

      </dependency>

</dependencies>

 

3. 編寫 類 繼承 UDF 類

4. 重寫方法 evaluate 支援過載

package cn.doit.com;

import org.apache.hadoop.hive.ql.exec.UDF;

public class Lower extends UDF {
    public String evaluate (final String s){
        if (s == null){
            return null;
        }
        return s.toLowerCase();
    }
}

5. 打包上傳到HDFS

hdfs dfs -put udf.jar /

6. 在hive的命令列視窗建立函式

1)add jar /udf.jar
2)reate function sayHello as 'cn.doit.com.Lower' using jar 'hdfs://linux01:8020/udf.jar'

6. 注意: UDF必須要有返回型別，可以返回null，但是返回型別不能為void；

三. 集合資料型別

struct    --類似於java物件 pojo類 
array     --array中的資料為相同型別，例如，假如array A中元素['a','b','c']，則A[1]的值為'b' 
map       --鍵值對

--資料
--string   array<string>    map<string ,int>   struct<street:string, city:string>
benben,fengjie_furong,xiaoben:18_daben:19,hui long guan_beijing
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing

--建表
create  table  tb_user(
name  string ,
friends  array<string> ,
children map<string ,int> ,
 <street:string , city:string>
)
row format  delimited fields terminated by  ',' 
collection items terminated by  '_'   --一個欄位專案分隔符
map  keys terminated by  ':'   --k,v分割符
lines terminated by  '\n';     --換行符切割
load  data local  inpath "/doit19/collection/" into  table tb_user ;

陣列 
--friends[index]

select friends[0] as a , friends[1] as b  from tb_user;  --取值   as a別名,可不寫
+----------+---------+
|    a     |    b    |
+----------+---------+
| fengjie  | furong  |
| caicai   | susu    |
+----------+---------+
--長度 size[arr]
select size(friends) from tb_user;   
select friends[if(2>size(friends) , 0 , 2)] from tb_user;  --防止角標越界 引數二:索引0 引數三:索引2
+------+
| _c0  |
+------+
| 2    |
| 2    |
+------+                    
+----------+
|   _c0    |
+----------+
| fengjie  |
| caicai   |
+----------+

map集合
map(k1,v1,k2,v2);
map_keys(map);
map_values(map);
size(map);

select children['xiaoben'] from tb_user; --根據K獲取map的V值
select map('k1','v1','k2','v2');   --建立map集合 kv要成對,偶數
select map_keys(children) from tb_user; --獲取集合中所有的key
select map_values(children) from tb_user;  --獲取集合中所有的value
select size(children) from tb_user;  --獲取map集合的長度
select explode(children) from tb_user;  --炸裂開map集合
+----------------+--------+
|      key       | value  |
+----------------+--------+
| xiaoben        | 18     |
| daben          | 19     |
| xiao yang      | 18     |
| xiaoxiao yang  | 19     |
+----------------+--------+

struct結構體

select address.street , address.city from tb_user;   --結構體獲取屬性
+----------------+----------+
|     street     |   city   |
+----------------+----------+
| hui long guan  | beijing  |
| chao yang      | beijing  |
+----------------+----------+

四. 功能擴充套件使用反射呼叫java類的方法

反射 reflect 函式 

reflect(calss(類名) , methodName(方法名) , args...(引數) )

1.編寫java程式

2.打包

3.上傳到linux系統

4.add jar /test.jar   將jar包新增到 lib的目錄下

5.查詢

select reflect( 'cn.doit.demo.Test1' , 'test1' , 'hello' , 23);

6. 企業級表的優化

1) 使用分割槽表和分桶表

2) 對SQL語句的優化  count(1)比count(*)效率高  select 欄位

3) 使用特殊的儲存方式   列式儲存 格式 ORC parquet(常用)

4) 小表 join 大表 [MR 程式中的map端join]

5) 開啟 map 端聚合

6) 避免count(distinct col)

7) 避免笛卡爾積

8) 適當的調整 maptask 的個數和 reducetask的個數

9) 測試資料在本地執行 

set mapreduce.framework.name=local

 

 

相關文章