1 數據連接

內連接：保留左表和右表連接成功的數據信息,連接未成功則不保留該數據

select * from hive_day03.orders as o join users u on o.userid = u.userid;

左連接：保留左表中全部的數據記錄,與右表中與左表連接成功的記錄

select * from hive_day03.users u right join orders o on o.userid = u.userid;

右連接：保留右表中全部的數據記錄,與左表中與右表連接成功的記錄

select * from hive_day03.orders o left join users u on o.userid = u.userid;

左半連接：執行內連接,保留左右兩個表匹配成功的數據記錄, 但是僅返回左表的數據信息

左半連接，由于獲取的數據量較小，在內存中占用的空間就小，數據移動速度快，計算效率會有所提高；相比于內連接,效率一定會提高,數據量越大提升越明顯

-- 左半連接:執行內連接保留左邊 select * from hive_day03.orders o left semi join users u on o.userid = u.userid;

全連接：左表和右表中的數據全部保留,未連接成功的部分補充null值即可

select * from hive_day03.orders o full join users u on o.userid = u.userid;

交叉連接：笛卡爾積, 左表的數據依次與右表的每一條數據進行匹配無論是否成功都保留,不足位置補充null

select * from hive_day03.orders o cross join users u on o.userid = u.userid;

2 hive數據查詢

桶內查詢

hive中的排序方式有兩種:

全局排序: order by 將所有的數據查詢出來,從頭到尾的排序
桶內排序: cluster by / sort by 將數據按桶進行提取,以桶為單位,在桶的內部進行排序

思考: 全局排序效率高,還是桶內排序效率高?

桶內排序效率高, 在現實開發中我們一般避免全局排序,如果非要全局排序,一般會增加limit關鍵字限制范圍

查詢時分桶排序

注意:一定先修改分桶數量,后續操作才能有分桶效果set mapreduce.job.reduces = 3;

查詢時分桶排序,其實是 在查詢過程中根據規則重新計算一遍分桶,將數據放入不同的虛擬桶中,對于原數據無影響
select 觸發的任何指令都不會修改原來的數據內容
讀取分桶表數據時僅需要一個reduce任務, 因為桶已經分好,不需要重新計算
分桶排序時,需要多個reduce任務,因為需要重新計算有多少個reduce任務就分為多少個桶

-- 創建表時進行桶內排序規則的指定 create table hive_day03.students_sort(s_id int,s_name string,gender string,age int,course string )clustered by (s_id) sorted by (s_id) into 3 buckets row format delimited fields terminated by ','; -- 表中加載數據 load data local inpath '/root/hive_data/students.txt' into table hive_day03.students_sort; -- 查看表數據 select * from hive_day03.students_sort; -- 查詢時分桶并排序，不一定非要查詢分桶表 -- 此時沒有設定reduce task 的數量，進行分桶排序后，進行分桶排序后，只能按照一個桶進排序 -- select * from hive_day03.students cluster by s_id; -- 如果要指定分桶的數量，則需要修改reducetask數量 -- mapreduce.job.reduce 參數默認值為-1，根據情況自行判斷reducetask數量 -- 當值為-1時，向分桶表中插入數據，會自動開啟與分桶數量相同的reductask -- 當值為-1時，按照分桶規則讀取數據時，會始終開啟1個reductask -- 修改reduce任務數量可以按照該數量進行分桶 set mapreduce.job.reduce=3; select * from hive_day03.students cluster by s_id; select * from hive_day03.students_sort cluster by s_id;-- cluster by 可以進行分桶也可以進行排序，但是分桶和排序必須是同一個字段，且該字段排序僅可以升序 -- 如果想要根據不同的字段進行分桶排序，或者降序排序，該如何？ -- distribute by 分桶 + sort by 排序 select * from hive_day03.students distribute by s_id sort by age desc ;

基本查詢

Hive中使用基本查詢SELECT、WHERE、GROUP BY、聚合函數、HAVING、JOIN和普通的SQL語句幾乎沒有區別
hive的JOIN查詢和排序查詢相比普通sql新增了部分功能

RLIKE 正則匹配

什么是正則表達式
正則表達式就是一種規則的集合。按照一定的規則去判斷字符串是否符合該規則的表達式

RLIKE的作用
可以基于正則表達式，對數據內容進行匹配

使用like時匹配效率低且不靈活，一般都會使用rlike通過正則表達式進行匹配

-- 找到 xx 省 xx 市 xx 區的數據 -- like select * from hive_day03.orders where useraddress like '__省 __市 __區'; -- rlike select * from hive_day03.orders where useraddress rlike '.{2}省 .{2}市 .{2}區';-- 找到上海市和北京市的所有訂單 -- like select * from hive_day03.orders where useraddress like '%上海%' or useraddress like '%北京%'; -- rlike select * from hive_day03.orders where useraddress rlike '.*上海|北京.*';-- 查找姓張、王、鄧的用戶 -- like select * from hive_day03.orders where username like '張%' or username like '王%' or username like '鄧%'; -- rlike 在正則匹配中如果需要使用\，一定要使用\\否則不轉義 select * from hive_day03.orders where username rlike '[張王鄧]\\S{2}';

UNION聯合

UNION關鍵字的作用是？
將多個SELECT的結果集合并成一個
多個SELECT的結果集需要架構一致，否則無法合并
自帶去重效果，如果無需去重，需要使用 UNION ALL
UNION用在何處：可以用在任何需要SELECT發揮的地方（包括子查詢、ISNERT SELECT等）

-- 將周杰輪和林均街的數據合并到一起縱向連接 5條數據 select * from hive_day03.course where s_name='周杰輪' union select * from hive_day03.course where s_name='林均街'; -- 將周杰輪和林均街的數據合并到一起縱向連接 4條數據 union會自動去重,union 等價于 union distinct

注意

union 等價于union distinct 在縱向合并時會自動去重

-- 結果4條數據 union會自動去重,union 等價于 union distinct select * from hive_day03.course where s_name='周杰輪' union select * from hive_day03.course where s_name='周杰輪'; -- 若不希望自動去重應該用 union all；結果8條數據 select * from hive_day03.course where s_name='周杰輪' union all select * from hive_day03.course where s_name='周杰輪';

union縱向合并后會根據自己的規則重排數據順序,不是上邊就在上邊下邊就在下邊

union 聯合查詢時 上下兩個表的列數必須相同

select id,c_name from hive_day03.course where s_name='周杰輪' union select * from hive_day03.course where s_name='周杰輪';

提示報錯：org.apache.hadoop.hive.ql.parse.SemanticException:Schema of both sides of union should match.
并集兩邊的模式應該匹配，上下兩個表查詢字段數量不同所以報錯。

union 聯合查詢時 上下兩張表同列字段類型必須相同

select id,c_name from hive_day03.course where s_name='周杰輪' union all select c_name,id from hive_day03.course where s_name='周杰輪';

提示報錯：org.apache.hadoop.hive.ql.parse.SemanticException:3:22 Schema of both sides of union should match: Column id is of type int on first table and type string on second table. Error encountered near token 'course'，
列id在第一個表上的類型為int，在第二個表上為string。上下兩張表同列字段類型不同所以報錯

Sampling采樣（了解）

為什么需要抽樣？
大數據體系下，表內容一般偏大，小操作也要很久
所以如果想要簡單看看數據，可以通過抽樣快速查看

TABLESAMPLE函數的使用
桶抽樣方式，TABLESAMPLE(BUCKET x OUT OF y ON(colname | rand()))，
推薦使用，完全隨機，速度略慢塊抽樣，使用分桶表可以加速
Virtual Columns 虛擬列
語法：SELECT ... FROM tbl TABLESAMPLE(BUCKET x OUT OF y ON(colname | rand()))
y 表示將表數據隨機劃分成y份（y個桶），從總共的桶里一共取出幾桶數據。eg: 一共100條數據分成10桶 y=4 我們一共獲取多少條采樣數據? 10 * 10/4 = 25條
x 表示從y里面隨機抽取x份數據作為取樣，從第幾桶數據開始采樣 。eg: 一共10桶數據 x=2 y=5 一共取 10/5 2桶數據, 次數從2 7桶
colname 表示隨機的依據基于某個列的值
rand() 表示隨機的依據基于整行

總共抽取的數據=總桶數 / y * 每個桶的數量 x
在開發中每個桶的數據量不一樣多, 所以在使用時不一定獲取的數據就是均分數據,而是一個范圍

-------------------------------Sampling采樣---------------------------- -- 數據準備 分五個桶 create table hive_day03.students_buck_5(s_id int,s_name string,gender string,age int,course string )clustered by (s_id) sorted by (s_id) into 5 buckets row format delimited fields terminated by ','; -- 表中加載數據 load data local inpath '/root/hive_data/students.txt' into table hive_day03.students_buck_5; -- 查看表數據 select * from hive_day03.students_buck_5; -- 分桶采樣，根據指定字段采樣 select * from hive_day03.students_buck_5 tablesample ( bucket 1 out of 5 on s_id); select * from hive_day03.students_buck_5 tablesample ( bucket 1 out of 2 on s_id); select * from hive_day03.students_buck_5 tablesample ( bucket 2 out of 5 on gender); -- 隨機采樣,推薦使用,隨機采樣數據代表性更強 select * from hive_day03.students_buck_5 tablesample ( bucket 1 out of 2 on rand());

virtual columns 虛擬列（

這里是引用

了解）
實際上不存在,但是hive可以幫我們映射出來的列,就是虛擬列

select *,INPUT__FILE__NAME, -- 顯示映射該數據記錄的文件所在位置BLOCK__OFFSET__INSIDE__FILE -- 顯示行偏移量 from hive_day03.course; -- 如果要使用 ROW__OFFSET__INSIDE__BLOCK 必須先開啟一下配置 用的極少 set hive.exec.rowoffset = true; SELECT * ,INPUT__FILE__NAME, --顯示映射該數據記錄的文件所在位置BLOCK__OFFSET__INSIDE__FILE,--顯示行偏移量ROW__OFFSET__INSIDE__BLOCK -- 顯示塊偏移量,但是如果僅有一個塊,則都是0 FROM hive_day03.course;

3 HIVE函數

Hive的函數共計有上百種，挑選練習一些常用的，更多詳細的函數使用可以參閱：官方文檔(https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-MathematicalFunctions)

什么是函數?

將一些特定的功能封裝起來,當我們想要使用該功能的時候,通過特定的字符組合集合調用這種方式就是函數。eg: sum 代表的是求和功能, count 代表的是計數功能

Hive的函數分為兩大類：內置函數（Built-in Functions）、用戶定義函數（User-Defined Functions）：

3.1 用戶自定義函數（User-Defined Functions）

自己定義封裝的功能就是自定義函數

本來，UDF 、UDAF、UDTF3個標準是針對用戶自定義函數分類的；但是，現在可以將這個分類標準擴大到hive中所有的函數，包括內置函數和自定義函數；
1、UDF（User-Defined-Function）普通函數: 一進一出 輸入一行數據輸出一行數據
eg: split、hash、round、rand、split
2、UDAF (User-Defined Aggregation Function）聚合函數: 多進一出 輸入多行輸出一行
eg: count、sum、max、min、avg、lead、lag
3、UDTF（User-Defined Table-Generating Functions）高階函數也稱表生成函數: 一進多出 輸入一行輸出多行。eg: explode、join_touple…

最開始UDF,UDAF,UDTF是對于自定義函數的分類規則,后來逐步推廣到所有的函數中

3.2 內置函數（Built-in Functions）

系統定義好的,我們可以直接通過sql指令調用的功能就是內置函數
eg：查看函數的使用方法

-- 查看所有的內置函數 show functions ; -- 查看函數的使用方法 -- 詳細查詢 desc function extended round;

字符串相關函數

-- 1.conncat(字符串1,字符串2...)連接多個字符串 select concat('hello','world!'); select concat('hello ','world!!!','And ','hive')-- 2.concat_ws()連接多個字符串并使用指定分隔符連接 -- 查詢函數使用方法 desc function extended concat_ws; -- 嘗試使用 select concat_ws('&&' ,'hive','hello','hhh');-- 3.length查詢字符串的長度 select length('hive'); select char_length('hello world'); select character_length('hello world');-- 4.upper和lower大小寫轉換 select upper('hello world'); select lower('HELLO world');-- 5.trim去除字符串左右兩側的空白 select ' hello '; select length(' hello '); select trim(' hello '); select length(trim(' hello '));-- 6.split字符串拆分,結果是一個數組類型數據 -- 格式:split(被拆分的字符串,分隔符) select split('hello,world,!!!,and,hive',','); select split('hello,world,!!!,and,hive',',')[2];-- 7.substr獲取字符串中的一部分信息 -- 格式:subste(被截取的字符串,開始截取的索引位置,截取的數據長度) select substr('海燕呀,你可長點心吧',0,3); -- 注意,此函數截取數據時索引查詢從1開始計數 select substr('海燕呀,你可長點心吧',7,3); --長點心 -- 完全等價 select substring('海燕呀,你可長點心吧',7,3); --長點心-- 8.regexp_replace 正則替換 D非數值;d數值 select regexp_replace('100-200','\\d+','num');-- 9.解析URL desc function extended parse_url;select parse_url('http://node1:9870/explorer.html#/', 'HOST'); select parse_url('http://node1:9870/explorer.html#/', 'PATH'); select parse_url('https://mp.csdn.net/mp_blog/manage/article?spm=3001.5298', 'QUERY'); select parse_url('https://mp.csdn.net/mp_blog/manage/article?username=zhangsan&age=10', 'QUERY', 'username');-- 10.get_json_object解析json數據 -- json數據學名:JAVASCRIP對象簡譜 -- 主要負責數據流傳輸工作,一般開發中前后的交互或者服務器間的交互使用最多的就是JSON數據 -- json 數據就類似于 array 和 map 的嵌套 /* json_str:{"name": "小明","age": 18,"hobby": ["eat","drink","play"],"score": {"Chinese": 18,"Math": 21,"English": 33} }*/ -- 獲取學員姓名 select get_json_object('{"name": "小明","age": 18,"hobby": ["eat","drink","play"],"score": {"Chinese": 18,"Math": 21,"English": 33} }', '$.name');-- 獲取該學員的第二個興趣愛好 select get_json_object('{"name": "小明","age": 18,"hobby": ["eat","drink","play"],"score": {"Chinese": 18,"Math": 21,"English": 33} }', '$.hobby[1]');-- 獲取 學員的英語成績 select get_json_object('{"name": "小明","age": 18,"hobby": ["eat","drink","play"],"score": {"Chinese": 18,"Math": 21,"English": 33} }', '$.score.English');

json格式轉換器：https://www.bejson.com/

時間函數

-- 1.查看當前日期 select current_date(); --獲取當前日期 select `current_timestamp`(); --獲取當前時間 -- 2.to_date 將數據轉換為日期字符串 格式需要遵循yyyy-mm-dd select to_date('2022年5月1日'); --數據格式不正確會返回null select to_date('2022-05-01'); select to_date('2022-05-01 19:59:59'); select to_date('2022-05-01 23');-- 3.獲取時間類型數據的一部分維度 select year('2022-05-01 19:59:59'); select quarter('2022-05-01 19:59:59');--季度 select month('2022-05-01 19:59:59'); select weekofyear('2022-05-01 19:59:59'); --一年的第幾周 select `dayofweek`('2022-05-01 19:59:59'); --計算周幾,周日是第一天 select day ('2022-05-01 19:59:59'); -- 當月第幾天 select hour ('2022-05-01 19:59:59'); select minute ('2022-05-01 19:59:59'); select second ('2022-05-01 19:59:59');-- 4.datediff獲取兩個日期間相差幾天 -- 時間差=前面日期-后面日期 select datediff('2022-05-01 19:59:59',`current_timestamp`());-- 5.時間偏移 -- 指定日期加一天 select date_add(`current_date`(),1); select date_add(`current_date`(),-1); -- 指定日期減一天 select date_sub(`current_date`(),1); select date_sub(`current_date`(),-1);-- 6.將時間轉換為時間戳 -- 將時間轉換為時間戳 -- 時間戳:從1970.1.1 0點到現在的毫秒值 select `current_timestamp`(); select unix_timestamp(); --當前時間戳 select unix_timestamp('2022-05-01 19:59:59'); --獲取指定時間的時間戳-- 7.將時間戳轉換為時間類型數據 select from_unixtime(1651435199);-- 8.時間類型格式化 select date_format('2022-05-01 19:59:59','yyyy年MM月dd日'); select date_format('2022-05-01 19:59:59','yyyy年MM月dd日 HH時mm分ss秒'); select date_format(`current_timestamp`(),'yyyy年MM月dd日 HH時mm分ss秒');

數學函數

-- 1.保留N位小鼠,并四舍五入 select round(3.1415926,3); --3.142 select round(3.14,3); --3.140 select round(2.57,5); --2.57000 -- 2.rand()獲取從0到1的隨機數 select rand(); -- rand()中填寫固定值,則每次獲取的數據都是相同,這個數字被稱為隨機數種子 select rand(10000);

總結

以上是生活随笔為你收集整理的Hive数据连接与函数（2）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。