Hive查詢、Hive Join和Hive集合操作

• Hive查詢
• • SELECT基礎
  • CTE和嵌套查詢
  • 進階語句
• Hive Join
• • 關聯查詢
  • MapJoin
• Hive集合操作(UNION)

Hive查詢

SELECT基礎

• SELECT用于映射符合指定查詢條件的行

• Hive SELECT是資料庫标準SQL的子集

  使用方法類似于MySQL

#查詢顧客表中地區為“NY”所在城市為'New York'的使用者
select * from customers where customer_state="NY" and customer_city="New York";
#查詢訂單表中共有多少不同顧客下過訂單
select count(distinct order_customer_id) from orders;
#查詢商品表中前5個商品
select * from products limit 5;
           

CTE和嵌套查詢

• CTE（Common Table Expression）

create table jobs_details as 
with 
tmp as (select jobs from employee_partition where info.gender='Male'),
t2 as (select jobs from employee_partition where info.gender='Female')
select tmp.jobs male_job,t2.jobs female_job from tmp,t2;
           

• 嵌套查詢

  嵌套查詢示例

進階語句

• 列比對正規表達式

#使用列比對正規表達式需要進行設定
set  hive.support.quoted.identifiers = none;
#列比對正規表達式查詢
select `a.*` from employee_partition;
select `.*n.*` from employee_partition;
           

• 虛拟列（Virtual Columns）

  兩個連續下劃線，用于資料驗證：

  input__file__name：Mapper Task的輸入檔案名稱

  block__offset__inside__file：目前全局檔案位置

Hive Join

關聯查詢

• 指對多表進行聯合查詢
• JOIN用于将兩個或多個表中的行組合在一起查詢

• 類似于SQL JOIN，但是Hive僅支援等值連接配接

  内連接配接：inner join

  外連接配接：outer join（right join, left join, full outer join）

  交叉連接配接：cross join

  隐式連接配接：Implicit join

• join發生在where子句之前

select name,address[0],info.age from employee_partition e join a_p a on e.add=a.test;

select concat(customer_fname,' ',customer_lname) name from customers left join orders on customer_id=order_customer_id where order_id is null;
           

MapJoin

• MapJoin操作在Map端完成

  小表關聯大表

  可進行不等值連接配接

• 開啟join操作

set hive.auto.convert.join=true（預設值）
           

運作時自動将連接配接轉換為MapJoin

-MapJoin操作不支援：

在union all, lateral view, group by/join/sort by /cluster by/distribute by等操作後面

在union, join以及其他 MapJoin之前

select /*+mapjoin(customers)*/ customer_fname from customers 
left join orders on customer_id=order_customer_id 
where order_id is null;
           

Hive集合操作(UNION)

• 所有子集資料必須具有相同的名稱和類型

  union all：合并後保留重複項

  union：合并後删除重複項（v1.2之後）

• 可以在頂層查詢中使用（0.13.0之後）
• ORDER BY, SORT BY, CLUSTER BY, DISTRIBUTE BY 和LIMIT适用于合并後的整個結果

• 集合其他操作可以使用JOIN/OUTER JOIN來實作

  差集、交集

select key from
(select key from src1 order by key kimit 10) sub
union all 
select key from src2
order by ke limit 10