Hive高级操作-查询操作

合集 - 数据仓库技术与应用(11)

1.掌握Hive数据存储模型2024-06-282.了解Hive基础2024-06-283.MYSQL语法：左连接、右连接、内连接、全外连接2024-03-124.Hive数据库及表操作2024-06-28

5.Hive高级操作-查询操作2024-06-28

6.Hive数据库及表操作2024-06-287.Hive高级操作-排序操作2024-06-288.Hive高级操作-窗口函数2024-06-289.Hive的内置函数（数学、字符串、日期、条件、转换、行转列）2024-06-2810.视图和索引2024-06-2811.Hive 元数据2024-06-28

收起

查询操作

---

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;