8.22筆記

一、Hive的HQL語法重點問題

1.1 DDL

1.1.1 Hive中數(shù)據(jù)表的分類問題

Hive中數(shù)據(jù)表的分類只是用來告訴我們Hive底層在HDFS上存儲的文件的規(guī)則和規(guī)范

1.1.2 特殊的數(shù)據(jù)類型

array、map、struct

Hive數(shù)據(jù)表可以指定底層的存儲格式的分隔符問題row format

1.2 DML

Hive中默認不支持批量的刪除和更新操作

1. 增加數(shù)據(jù)
   1. 裝載數(shù)據(jù)
   2. 追加和覆蓋
2. 刪除數(shù)據(jù)（只能刪除所有數(shù)據(jù)或者某個分區(qū)的數(shù)據(jù)）–truncate
3. 數(shù)據(jù)導入和導出問題

1.3 DQL

1.3.1 查詢語法和MySQL大部分都是一致的

1. 排序問題

   1. 全局排序：DQL查詢語法轉換的MR底層只有一個reduce任務
      order by

   2. 局部排序：DQL查詢語法轉換的MR底層可以有多個reduce任務，每個reduce的輸出有序，整體沒有順序sort by

      如果要使用sort by進行局部排序，那么需要設置Hive底層的轉換的MR程序的reduce任務數(shù)大于1

      set mapreduce.job.reduces=num>1

      注意：如果我們只是使用了sort by進行排序，并且reduce的任務數(shù)大于1，那么mr程序計算的時候底層會對數(shù)據(jù)進行分區(qū)，分區(qū)數(shù)就是reduce的任務數(shù)，默認情況下,如果只使用了sort by，那么分區(qū)機制我們是無法控制

      如果我們在局部排序的時候還想控制每個分區(qū)的數(shù)據(jù)，可以在sort by之前增加上一個distribute By 字段，Distribute By和sort by結合使用的，是用于負責控制分區(qū)規(guī)則的。分區(qū)規(guī)則是根據(jù)我們指定的分區(qū)字段的hash值和分區(qū)數(shù)取余數(shù)。

      如果Distribute By和sort by的字段一樣，我們可以使用cluster by替代上述兩個操作

2. 連接查詢

   Hive支持了全外連接full join

1.4 講了三個數(shù)據(jù)庫的可視化工具

1.4.1 navicat

界面特別好看的，收費的

一般只能連接RDBMS關系型數(shù)據(jù)庫，連接底層不是基于JDBC

1.4.2 dbeaver

1.4.3 chat2db

增加了AI大模型

1.4.2~1.4.3：都是免費的，底層都是基于JDBC連接數(shù)據(jù)庫
因此這兩個軟件不僅可以連接常見的RDBMS，還可以連接大數(shù)據(jù)環(huán)境：Hive、Spark等等

二、Hive中重點問題：Hive函數(shù)的使用

Hive中提供了很多的自帶函數(shù)，自帶函數(shù)有大部分都是為了統(tǒng)計分析設計的。Hive中的函數(shù)大部分都是一個Java類

2.1 函數(shù)分為兩種

UDF：一對一函數(shù)
UDTF：一對多函數(shù)
UDAF：多對一函數(shù)

2.1.1 內(nèi)置函數(shù)

1. 如何查看系統(tǒng)自帶的內(nèi)置函數(shù)

show functions;

desc function 函數(shù)名;

desc function extended 函數(shù)名;

2. Hive中常見的一些內(nèi)置函數(shù)的用法

   1. 數(shù)學函數(shù):UDF

      函數(shù)名	說明
      abs(x)	返回x的絕對值
      ceil(x)	向上取整，返回比x大的正整數(shù)中最小的那一個
      floor(x)	向下取整
      mod(a,b)	a%b
      pow(a,b)	a^b
      round(x,[n])	四舍五入 如果不傳遞n 代表小數(shù)點不保留，如果n>=1 代表小數(shù)點后保留n位
      sqrt(x)	根號x

   2. 字符串函數(shù)

      函數(shù)名	說明
      concat	直接拼接，拼接需要傳遞多個參數(shù)，會把多個參數(shù)拼接起來，如果有一個參數(shù)為null值，那么結果直接為null
      concat_ws	可以拼接的分隔符，傳遞的第一個參數(shù)是一個分隔符，如果拼接了null值，null值不計算
      lpad |rpad(str,x,pad)	在str左/右邊以指定的pad字符填充字符串到指定的x長度
      ltrim|rtrim|trim(str)	去除空格
      length(x)	返回字符串的長度
      replace(str,str1,replacestr)	將字符串中指定字符串替換成為另外一個字符串
      everse(str)	字符串反轉
      split(str,x):array	字符串切割
      substr|substring(str,n,[num])，截取字符串

   3. 日期

      函數(shù)名	說明
      current_date()	返回當前的日期 年月日
      current_timestamp()	返回當前時間
      date_format(date,“格式”)	格式化時間的
      datediff(date,date1)	返回這兩個時間的差值（天）

   4. 條件判斷函數(shù)

      函數(shù)名
      if
      case when then [when then] … else end

   5. 特殊函數(shù)

      1. 和數(shù)組、集合操作有關的函數(shù)

         函數(shù)內(nèi)部需要傳遞一個數(shù)組，或者返回值是一個數(shù)組類型的函數(shù)

         函數(shù)名	說明
         split(str,spea):array
         collect_set(列名)：array	將一列中的所有行的數(shù)據(jù)封裝為一個數(shù)組 列轉行 不允許重復
         	將一列中的所有行的數(shù)據(jù)封裝為一個數(shù)組 列轉行 允許重復
         array(ele…):array
         map(key,value,key,value,key,value…):map
         concat_ws(spe,array(string))：String	將一個數(shù)組中的所有字符串以指定的分隔符拼接得到一個全新的字符串
         explode（array,map集合）	多行多列的數(shù)據(jù) 炸裂函數(shù) 行轉列的函數(shù) 如果傳遞的是array,那么結果是一列多行 如果傳遞的是map集合，那么結果就是兩列多行

         collect_set(列名)：array、collect_list(列名)：array

         列轉行函數(shù)
         將一列的多行數(shù)據(jù)轉換成為一行數(shù)據(jù)
         UTAF

      2. 和字符串有關的特殊函數(shù)：（字符串必須得是URL）

         1. URL的概念

            URL是叫做統(tǒng)一資源定位符，是用來表示互聯(lián)網(wǎng)或者主機上的唯一的一個資源的
            URL整體上主要有如下幾部分組成的：
            協(xié)議：http/https、ftp、file、ssh
            host：主機名、域名、ip地址
            port：端口號
            path：資源路徑
            queryParam：參數(shù) ?key=value&key=value…

            例子：

            http://192.168.35.101:9870/index.html?name=zs&age=20

            https://www.baidu.com/search/a?key=valueURL

            中，如果沒有寫端口號，那么都是有默認端口，http:80 https:443 ssh:22

         2. parse_url（urlstr,"特殊字符"）：string一次只能提取URL的一個成分

         3. parse_url_tuple(urlstr,"特殊字符"...)：每一個成分當作一列單獨展示，函數(shù)可以將一個數(shù)據(jù)轉換成為一行多列的數(shù)據(jù)函數(shù)多了一個特殊字符：QUERY:key

            parse_url_tuple(urlstr,"特殊字符"...) as (列名...)

            2、3：

            hive提供用來專門用來解析URL的函數(shù)：從URL中提取URL組成成分
            特殊字符代表的是URL的組成成分，特殊字符有如下幾種：
            HOST：提取URL中的主機名、IP地址
            PATH,：提取URL中資源路徑
            QUERY, 提取URL中的所有請求參數(shù)
            PROTOCOL, 提起URL中的請求協(xié)議
            AUTHORITY,
            FILE,
            USERINFO,
            REF,

      3. 側視圖

         1. 側視圖Lateral View專門用來和UDTF函數(shù)結合使用，用來生成一個虛擬表格，然后這個虛擬表格一行數(shù)據(jù)會生成一個，虛擬表格是動態(tài)的，一行數(shù)據(jù)會生成一個虛擬表格，生成的虛擬表格和當前行做一個笛卡爾乘積，得到一些我們普通SQL無法實現(xiàn)的功能
         2. 側視圖使用場景：一個表格中，某一行的某一列是一個多字段組成的數(shù)據(jù)，我們想把多字段的列拆分開和當前行結合得到一個多行的結果。

      4. 開窗函數(shù) overselect子語句中

         1. 開窗函數(shù)指的是在查詢表數(shù)據(jù)時，將表按照指定的規(guī)則拆分成為多個虛擬窗口（并沒有真實的拆分、類似于分組），然后可以在窗口中得到一些只有分組之后才能得到一些信息，然后將信息和原始數(shù)據(jù)結合起來，實現(xiàn)在同一個查詢中既可以得到基礎字段，還可以得到聚合字段。既需要普通字段還需要一些聚合信息的時候，開窗函數(shù)就是最完美的選擇。

         2. 語法：

            函數(shù)(參數(shù)) over(partition by 列名 order by 字段) as 別名-列名

         3. 可以和窗口函數(shù)結合使用的主要有三種類型的函數(shù)

            1. first_value(col)|last_value(col) over(partition by 列名 order by 字段) as 別名-列名

            2. 聚合函數(shù)

               sum/avg/count/max/min over(partition by 列名) as 列別名

            3. 排名函數(shù)

               row_number()/rank()/dense_rank() over(partition by 列名 order by 字段) as 列別名

               排名函數(shù)的作用就是對數(shù)據(jù)開窗之后，查詢到某一行數(shù)據(jù)之后，看一下這行數(shù)據(jù)在所屬窗口的排名-位置，然后根據(jù)位置打上一個序號，序號從1開始

               函數(shù)名	說明
               row_number()	序號是從1開始依次遞增，如果兩行數(shù)據(jù)排名一致，也會依次編號
               rank()	序號是從1開始依次遞增，如果兩行數(shù)據(jù)排名一致，兩行編號一樣的 跳排名
               dense_rank()	序號是從1開始依次遞增，如果兩行數(shù)據(jù)排名一致，也會依次編號,不會跳排名

            4. 使用場景：求不同組中排名topN的數(shù)據(jù)信息

2.2 用戶自定義函數(shù)

用戶自定義函數(shù)就是我們覺得hive內(nèi)置函數(shù)不滿足我們的需求，我們可以自定義函數(shù)實現(xiàn)我們想要的功能

2.2.1 Hive自定義函數(shù)的步驟

（Hive底層也都是Java，自定義函數(shù)也是編寫Java代碼的）

1. 創(chuàng)建一個Java項目

2. 引入編程依賴

   1. 創(chuàng)建lib目錄，自己找jar包放到lib目錄下，然后lib目錄add as library hive的安裝目錄的lib目錄下
   2. 使用maven然后根據(jù)gav坐標引入依賴

3. 編寫對應的函數(shù)類：UDF、UDTF、UDAF
   大部分自定義都是UDF和UDTF函數(shù)

4. 將編寫好的Java代碼打成jar包

5. 將jar包上傳到HDFS上

6. 通過create function …從jar包以全限定類名的方式創(chuàng)建函數(shù)

7. 注意：自定義的函數(shù)創(chuàng)建的時候有兩種創(chuàng)建方式
   create [temporary] function function_name as “全限定類名” using jar “jar包在hdfs上的路徑”

   1. 臨時函數(shù)：只對本次會話有效
      只可以通過show functions查看，元數(shù)據(jù)庫不會記錄
   2. 永久函數(shù)：永久生效
      無法通過show functions查看,但是可以通過Hive的元數(shù)據(jù)庫的FUNS表中查看

2.2.2 自定義UDF函數(shù)

2.2.3 自定義UDTF函數(shù)

2.2.4 刪除自定義函數(shù)

drop function 函數(shù)名

【注意】用戶自定義函數(shù)有一個特別重要的問題，自定義函數(shù)和數(shù)據(jù)庫綁定的。只能在創(chuàng)建函數(shù)的數(shù)據(jù)庫使用函數(shù)。如果要在其他數(shù)據(jù)庫下使用，需要在其他數(shù)據(jù)庫下把函數(shù)重新創(chuàng)建一遍即可。

三、相關代碼

-- 查看hive自帶的所有函數(shù)
show functions;
-- 查看某個函數(shù)的用法
desc function abs;
-- 查看某個函數(shù)的詳細用法
desc function extended parse_url;select sqrt(8);create table demo(name string
);
insert into demo values("zs"),("ls"),("ww");
select * from demo;
select concat_ws("-",collect_set(name)) from demo;select concat_ws("-",name) from demo;
select concat_ws("-","zs","ls",null);select rpad("zs",10,"-");select ltrim("    z   s     ");
select rtrim("    z   s     ");
select trim("    z    s     ");select replace("2022-10-11","-","/");
select reverse("zs");
select split("zs-ls-ww","-");select substring("zs is a good boy",9);select current_timestamp();
select date_format("2022-10-11 20:00:00","HH")
select datediff(current_date(),"2000-10-11");select if(1>2,"zs","ls");
select CASE 10when 10 then "zs"when 20 then "ls"else "ww"ENDselect CASE when 1>2 then "zs"when 1<2 then "ls"else "ww"ENDselect explode(array(1,2,3,4,5));
select map("name","zs","age","20","sex","man");
select explode(map("name","zs","age","20","sex","man"));select parse_url("http://www.baidu.com:80/search/a?name=zs&age=30","QUERY");
select parse_url_tuple("http://www.baidu.com:80/search/a?name=zs&age=30","QUERY","PATH","HOST","QUERY:sex") 
as (query,path,host,sex);-- 側視圖的使用
create table test(name string,age int,hobby array<string>
);insert into test values("zs",20,array("play","study")),("ls",30,array("sleep","study"));
select * from test;select name,age,hobby,temp.hb from test
lateral view explode(hobby) temp as hb;-- 開窗函數(shù)的使用
create table student(student_name string,student_age int,student_sex string
);insert into student values("zs",20,"man"),("ls",20,"woman"),("ww",20,"man"),("ml",20,"woman"),("zsf",20,"man");select * from student;-- 查詢不同性別的總人數(shù)
select student_sex,count(1) from student group by student_sex;
-- 查詢表中所有的學生信息，并且每個學生信息后面需要跟上這個學生所屬性別組的總人數(shù)
select student_name,student_age,student_sex,row_number() over(partition by student_sex order by student_name desc) as sex_count
from student;-- 排名函數(shù)的使用場景
create table employees(employees_id int,employees_name string,employees_dept int,employees_salary double
);insert into employees values(1,"zs",1,2000.0),(2,"ls",1,1800.0),(3,"ww",1,1700.0),(4,"ml",1,2000.0),(5,"zsf",1,1900.0),(6,"zwj",2,3000.0),(7,"qf",2,2500.0),(8,"cl",2,2500.0),(9,"jmsw",2,2000.0);select * from employees;
-- 獲取每個部門薪資排名前二的員工信息
-- 部門分組 薪資降序排序  排名窗口函數(shù)  給每一行數(shù)據(jù)打上一個序號
select * from(select *,DENSE_RANK() over(partition by employees_dept order by employees_salary desc) as salary_rankfrom employees
) as b
where salary_rank <=2;

package com.sxuek.udf;import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;/*** 類就是從一個字符串中找大寫字符個數(shù)的函數(shù)* UDF*/
public class FindUpperCount extends GenericUDF {/*** 初始化方法，方法是用來判斷函數(shù)參數(shù)的*   指定函數(shù)參數(shù)的個數(shù)以及函數(shù)參數(shù)的類型* @param objectInspectors   函數(shù)參數(shù)的類型和個數(shù)的一個數(shù)組* @return  方法的返回值代表的是函數(shù)執(zhí)行完成之后的返回值類型* @throws UDFArgumentException*/@Overridepublic ObjectInspector initialize(ObjectInspector[] objectInspectors) throws UDFArgumentException {/*** 1、判斷參數(shù)的類型和個數(shù)是否滿足需求*///數(shù)組的長度就是函數(shù)參數(shù)的個數(shù)int length = objectInspectors.length;if (length != 1){throw new UDFArgumentException("function only need one param");}else{//ObjectInspector是一個Hive數(shù)據(jù)類型的頂尖父類 參數(shù)的類型ObjectInspector objectInspector = objectInspectors[0];//PrimitiveObjectInspectorFactory是Hive中所有基礎數(shù)據(jù)類型的工廠類//返回函數(shù)的執(zhí)行完成之后輸出的結果類型  整數(shù)類型return PrimitiveObjectInspectorFactory.javaIntObjectInspector;}}/*** 方法就是函數(shù)實現(xiàn)的核心邏輯方法* @param deferredObjects   函數(shù)傳遞的參數(shù)* @return 返回值就是函數(shù)執(zhí)行完成之后的返回結果 返回結果必須和initialize的返回值類型保持一致* @throws HiveException*/@Overridepublic Object evaluate(DeferredObject[] deferredObjects) throws HiveException {//獲取函數(shù)傳遞的那一個參數(shù)DeferredObject deferredObject = deferredObjects[0];//get方法是獲取封裝的參數(shù)值Object o = deferredObject.get();String str = o.toString();int num = 0;for (char c : str.toCharArray()) {if (c >= 65 && c <= 90){num++;}}return num;}/*** HQL的解析SQL的輸出----沒有用處* @param strings* @return*/@Overridepublic String getDisplayString(String[] strings) {return "";}
}

package com.sxuek.udtf;import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
import org.apache.hadoop.hive.serde2.objectinspector.*;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;import java.util.ArrayList;
import java.util.List;/***  輸入?yún)?shù)有兩個：*     字符串*     分隔符*  輸出結果 一列多行的結果*     word*     zs*     ls*/
public class SplitPlus extends GenericUDTF {/*** 作用：*   1、校驗輸入的參數(shù)*   2、返回UDTF函數(shù)返回的列的個數(shù)、名字、類型* @param argOIs  當作一個數(shù)組來看，里面多個參數(shù)組成的* @return* @throws UDFArgumentException*/@Overridepublic StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {List<? extends StructField> allStructFieldRefs = argOIs.getAllStructFieldRefs();if (allStructFieldRefs.size() != 2){throw new UDFArgumentException("function need two params");}else{/*** 返回一列多行 UDTF函數(shù)也可以返回多行多列*///返回的列的名字 是一個集合 集合有幾項  代表UDTF函數(shù)返回幾列List<String> columnNames = new ArrayList<>();columnNames.add("word");//返回的列的類型 集合的個數(shù)必須和columnNames集合的個數(shù)保持一致List<ObjectInspector> columnTypes = new ArrayList<>();columnTypes.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);//構建StandardStructObjectInspector，需要兩個List集合 List<String> List<ObjectInspector>StandardStructObjectInspector standardStructObjectInspector = ObjectInspectorFactory.getStandardStructObjectInspector(columnNames,columnTypes);return standardStructObjectInspector;}}/*** UDTF函數(shù)執(zhí)行的核心邏輯*   結果的輸出需要借助forward方法* @param objects 函數(shù)的輸入?yún)?shù)* @throws HiveException*/@Overridepublic void process(Object[] objects) throws HiveException {String str = objects[0].toString();String split = objects[1].toString();String[] array = str.split(split);for (String s : array) {//一行輸出需要輸出一次  如果輸出一行數(shù)據(jù) 那么只需要調(diào)用一次forward方法即可/*** 如果一行數(shù)據(jù)有多列，可以先創(chuàng)建一個List集合，List<Object> 集合中把一行的多列值全部加加進來*/forward(s);}}/*** close用于關閉一些外部資源* @throws HiveException*/@Overridepublic void close() throws HiveException {}
}