Hive高階操作-查詢操作

查詢操作

---

1.select語句

SELECT [ALL | DISTINCT] select_expr, select_expr, ... 
FROM table_reference 
[WHERE where_condition] 
[GROUP BY col_list] 
[HAVING having_condition] 
[CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list]] 
[LIMIT number];

1.1where子句

必須是布林表示式，過濾結果集

select * from customers where customer_state = 'NY'
and customer_city='New York';

hive常用表示式

A [not] LIKE B
'abc' like 'a*'
'abc' like 'a%'
'abc' like 'a__'

運算子	操作	描述
A = B	所有基本型別	如果表達A等於表達B，結果TRUE ，否則FALSE。
A != B	所有基本型別	如果A不等於表示式B表達返回TRUE ，否則FALSE。
A < B	所有基本型別	TRUE，如果表示式A小於表示式B，否則FALSE。
A <= B	所有基本型別	TRUE，如果表示式A小於或等於表示式B，否則FALSE。
A > B	所有基本型別	TRUE，如果表示式A大於表示式B，否則FALSE。
A >= B	所有基本型別	TRUE，如果表示式A大於或等於表示式B，否則FALSE。
A IS NULL	所有型別	TRUE，如果表示式的計算結果為NULL，否則FALSE。
A IS NOT NULL	所有型別	FALSE，如果表示式A的計算結果為NULL，否則TRUE。
A LIKE B	字串	TRUE，如果字串模式A匹配到B，否則FALSE。
A RLIKE B	字串	NULL，如果A或B為NULL；TRUE，如果A任何子字串匹配Java正規表示式B；否則FALSE。
A REGEXP B	字串	等同於RLIKE。

1.2 ALL、DISTINCT子句

兩個子句都表示是否返回重複行，預設是all。

all	預設項，返回所有匹配行
DISTINCT	查詢會返回所有不重複的行

1.3. limit子句

limit子句用於限制select語句返回的行數。

select p_id,p_name from products limit 5;

1.4 公共表示式（CTE）

表示一個臨時的結果集，只在CTE語句範圍內才可使用該臨時表(即一句語句結束，;之前)，用關鍵字with 開頭

with t1 as (
select concat(customer_fname,'-',customer_lname)as fullname from customers
)select * from t1;

1.5 巢狀查詢

又稱子查詢，用於FROM 子句後。

• 子查詢必須給定名稱，因為FROM子句中每個表必須有表明
• 列有唯一名稱且可在外部查詢引用
• 可使用union和join操作

SELECT *FROM (
select concat(name,age)as a from customers)t1
limit 5;

其中t1是表名，必須要給巢狀的子查詢給定名稱，原本是表則不需要。

1.6 列匹配正規表示式

HQL支援使用正規表示式指定列名稱，符合的列名將被作為結果集的一列。需要提前設定set hive.support.quoted.identufiers=None;,使hive將反引號解釋為正規表示式。

select `regex_expr` from table;

1.7 虛擬列

並非真正存在的列，用於相關驗證。常用如下：

input__file__name：該行資料包含於哪個檔案中

block__offset__inside__file:包含檔案內的塊內偏移量

2.關聯查詢（join）

指多表進行聯合查詢，主要透過join語句實現

2.1 內連線

返回兩張表的交集

select * from a inner join b on a.id=b.id;
# 逗號的連表方式就是內連線
select * from A , B where A.id = B.id 

2.2 外連線

2.2.1 左外連線

是以左表為基礎，根據ON後給出的兩表的條件將兩表連線起來。

結果會將左表所有的查詢資訊列出，而右表只列出ON後條件與左表滿足的部分。左連線全稱為左外連線，是外連線的一種。

# left join
select * from A left join B on  A.id = B.id 
# left outer join
select * from A left outer join B on  A.id = B.id 

2.2.2 右外連線

是以右表為基礎，根據ON後給出的兩表的條件將兩表連線起來。結果會將右表所有的查詢資訊列出，而左表只列出ON後條件與右表滿足的部分。右連線全稱為右外連線，是外連線的一種。

# right join
select * from A right join B on  A.id = B.id 
# right outer join
select * from A right outer join B on  A.id = B.id 

2.3 交叉連線

笛卡爾乘積，兩表相乘。關鍵字是cross join

3.聯合查詢

union語句用於合併兩個或多個select語句的結果集

• union all用的比較多，是直接連線，取到得是所有值
• union 是取唯一值，記錄沒有重複

4.技能實訓

根據零售商店查詢所有的訂單明細，並儲存為新的表orders_details。

create table order_details as
select order_items.order_item_order_id,
orders.order_date,
customers.customer_lname,
customers.customer_city,
products.product_name,
categories.category_name,
order_items.order_item_quantity,
order_items.order_item_product_price 
from 
orders join customers on orders.order_customer_id=customers.customer_id
join order_items on orders.order_id=order_items.order_item_order_id 
join products on order_items.order_item_product_id=products.product_id
join categories on products.product_category_id=categories.category_id

limit 10;