hive数据查询语法
思维导图
基本查询
基本语法
SELECT [ALL | DISTINCT] 字段名, 字段名, ...
FROM 表名 [inner | left outer | right outer | full outer | left semi JOIN 表名 ON 关联条件 ]
[WHERE 非聚合条件]
[GROUP BY 分组字段名]
[HAVING 聚合条件]
[ORDER BY 排序字段名 asc | desc]
[CLUSTER BY 字段名 | [DISTRIBUTE BY 字段名 SORT BY 字段名]]
[LIMIT x,y]整体上和普通SQL差不多，部分有区别，如：CLUSTER BY、DISTRIBUTE BY、SORT BY等
基础查询格式: select distinct 字段名 from 表名;
注意: *代表所有字段 distinct去重 as给表或者字段起别名
条件查询格式: select distinct 字段名 from 表名 where 条件;
比较运算符: > < >= <= != <>
逻辑运算符: and or not
模糊查询: %代表任意0个或者多个字符 _代表任意1个字符
空判断: 为空is null 不为空is not null
范围查询: x到y的连续范围:between x and y x或者y或者z类的非连续范围: in(x,y,z)
排序查询格式: select distinct 字段名 from 表名 [where 条件] order by 排序字段名 asc|desc ; asc : 升序 默认升序
desc: 降序
聚合查询格式: select 聚合函数(字段名) from 表名;
聚合函数: 又叫分组函数或者统计函数
聚合函数: count() sum() avg() max() min()
分组查询格式: select 分组字段名,聚合函数(字段名) from 表名 [where 非聚合条件] group by 分组字段名 [having 聚合条件];
注意: 当分组查询的时候,select后的字段名要么在groupby后出现过,要么放在聚合函数内,否则报错
where和having区别?
区别1: 书写顺序不同,where在group by关键字前,having在group by关键字后
区别2: 执行顺序不同,where在分组之前过滤数据,having在分组之后过滤数据
区别3: 筛选数据不同,where只能在分组之前过滤非聚合数据,having在分组之后主要过滤聚合数据
区别4: 操作对象不同,where底层操作伪表,having底层操作运算区
分页查询格式: select 字段名 from 表名 [ order by 排序字段名 asc|desc] limit x,y;
x: 起始索引 默认从0开始,如果x为0可以省略 计算格式: x=(页数-1)*y
y: 本次查询记录数
数据准备
准备数据：订单表
CREATE TABLE orders (
orderId bigint COMMENT '订单id',
orderNo string COMMENT '订单编号',
shopId bigint COMMENT '门店id',
userId bigint COMMENT '用户id',
orderStatus tinyint COMMENT '订单状态 -3:用户拒收 -2:未付款的订单 -1：用户取消 0:待发货 1:配送中 2:用户确认收货',
goodsMoney double COMMENT '商品金额',
deliverMoney double COMMENT '运费',
totalMoney double COMMENT '订单金额（包括运费）',
realTotalMoney double COMMENT '实际订单金额（折扣后金额）',
payType tinyint COMMENT '支付方式,0:未知;1:支付宝，2：微信;3、现金；4、其他',
isPay tinyint COMMENT '是否支付 0:未支付 1:已支付',
userName string COMMENT '收件人姓名',
userAddress string COMMENT '收件人地址',
userPhone string COMMENT '收件人电话',
createTime timestamp COMMENT '下单时间',
payTime timestamp COMMENT '支付时间',
totalPayFee int COMMENT '总支付金额'
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
LOAD DATA LOCAL INPATH '/home/hadoop/orders.txt' INTO TABLE orders;基本查询
这是一张订单销售表，我们基于此表，做一下简单的Hive基本查询
SELECT 基础查询
-- 查询所有
SELECT * FROM orders;
-- 查询单列
SELECT orderid, totalmoney, username, useraddress, paytime FROM orders;
-- 查询数据量
SELECT COUNT(orderid) FROM orders;
-- 过滤广东省订单
SELECT * FROM orders WHERE useraddress LIKE '%广东%';
-- 找出广东省单笔营业额最大的订单
SELECT * FROM orders WHERE useraddress like '%广东%' ORDER BY totalmoney DESC LIMIT 1;SELECT 分组、聚合查询
-- 统计未支付、已支付各自的人数
SELECT ispay, COUNT(*) AS cnt FROM orders GROUP BY ispay;
-- 在已付款订单中，统计每个用户最高的一笔消费金额
SELECT userid, MAX(totalmoney) AS max_money FROM orders WHERE ispay = 1 GROUP BY userid;
-- 统计每个用户的平均订单消费额
SELECT userid, AVG(totalmoney) FROM orders GROUP BY userid;
-- 统计每个用户的平均订单消费额，过滤大于10000的数据
SELECT userid, AVG(totalmoney) AS avg_money FROM orders GROUP BY userid HAVING avg_money > 10000;hive 多表join查询
演示join查询
1. 准备数据源, 建表, 添加表数据.
table1: 商品表
-- table1: 商品表
CREATE TABLE product(
pid string,
pname string,
category_id string
) row format delimited
fields terminated by ',';表中数据：
table2: 分类表
-- table2: 分类表
CREATE TABLE category (
cid string,
cname string
) row format delimited
fields terminated by ',';表中数据：
多表查询 cross join------交叉连接--------------
select * from product cross join category;
多表查询 inner join------内连接--------------
-- 多表查询 inner join------内连接--------------
select * from product inner join category on product.category_id = category.cid;
多表查询 left join on------左外连接--------------
-- 多表查询 left join on------左外连接--------------
select * from product left join category on product.category_id = category.cid;
多表查询 right join on------右外连接--------------
-- 多表查询 right join on------右外连接--------------
select * from product right join category on product.category_id = category.cid;
多表查询 full join on------全外连接--------------
-- todo 多表查询 full join on------全外连接--------------
select * from product full join category on product.category_id = category.cid;
多表查询 union -----联合查询--------------
-- 多表查询 union -----联合查询--------------
select * from product left join category on product.category_id = category.cid
union
select * from product right join category on product.category_id = category.cid;
多表查询 left semi join------左半连接--------------
-- 多表查询 left semi join------左半连接--------------
select * from product left semi join category on product.category_id = category.cid;
hive有别于mysql的join
满外连接, full outer join
左半连接, left semi join
多表查询 full join on------全外连接--------------
-- todo 多表查询 full join on------全外连接--------------
select * from product full join category on product.category_id = category.cid;
多表查询 left semi join------左半连接--------------
-- 多表查询 left semi join------左半连接--------------
select * from product left semi join category on product.category_id = category.cid;
union查询
union 是上下拼接的连接查询: 要求上下字段和类型要保持一种.
主要实现功能: 把两个select查询结果上下拼接起来.
# union 拼接过程中会去除重复.
select * from product
union
select * from product where pid='p1';
# union all 拼接过程中不会去重.
select * from product
union all
select * from product where pid='p1';
hive有别于mysql的排序
cluster by关键字
distribute by+sort by关键字
set mapreduce.job.reduces: 查看当前设置的reduce数量 默认结果是-1,代表自动匹配reduce数量和桶数量一致
set mapreduce.job.reduces = 数量 : -- 修改reduces数量
cluster by 字段名: 分桶且正序排序 弊端: 分和排序是同一个字段,相对不灵活
distribute by 字段名 sort by 字段名: distribute by负责分,sort by负责排序, 相对比较灵活
order by 字段名: 只能全局排序
注意: cluster by 和 distribute by 字段名 sort by 字段名 受当前设置的reduces数量影响,但是设置的reduces数量对order by无影响,因为orderby就是全局排序,就是一个reduce
建表的时候指定分桶字段和排序字段: clustered by (字段名) sorted by (字段名) into 桶数量 buckets
注意: 如果建表的时候设置了桶数量,那么reduces建议设置值-1或者值大于桶数量
Hive SQL中的cluster by语法可以指定根据后面的字段将数据分桶，桶内再根据这个字段正序排序， 概括起来就是：根据同一个字段，分且排序。
-- 创建基础表主要用于给分桶表准备数据
create table students
(
id int,
name string,
gender string,
age int,
class string
)
row format delimited
fields terminated by ',';
设置reduce数量,注意:此种方式num默认是-1,代表自动匹配reduce数量和桶数量一致.
这里设置reduce数量其实可以确定查询分桶个数.
set mapreduce.job.reduces = num;
本示例设了三个桶
set mapreduce.job.reduces = 3;-- 创建基础表后,上传students.txt文件
-- 查询数据,观察结果
select * from students cluster by id;
Hive SQL中的distribute by+sort by就相当于把cluster by的功能一分为二：
distribute by和sort by可以跟不同的字段
DISTRIBUTE BY负责分
SORT BY负责分组内排序
如果DISTRIBUTE BY +SORT BY的后面字段一样，可以得出下列结论：
CLUSTER BY=DISTRIBUTE BY +SORT BY（字段一样）
-- 直接基于基础表查询,观察结果
select * from students distribute by id sort by gender;
总结
Hive中使用基本查询SELECT、WHERE、GROUP BY、聚合函数、HAVING、JOIN和普通的SQL语句几乎没有区别
hive的JOIN查询和排序查询相比普通sql新增了部分功能
抽样查询
TABLESAMPLE抽样好处: 尽可能实现随机抽样,并且不走MR,查询效率相对较快
基于随机分桶抽样格式: SELECT 字段名 FROM tbl TABLESAMPLE(BUCKET x OUT OF y ON(字段名 | rand()))
y:决定将表数据随机划分成多少份
x:决定从第几份数据开始采样
| : 或者
字段名: 表示随机的依据基于某个列的值,每次按相关规则取样结果都是一致
rand(): 表示随机的依据基于整行,每次取样结果不同
为什么需要抽样表数据
对表进行随机抽样是非常有必要的。
大数据体系下，在真正的企业环境中，很容易出现很大的表，比如体积达到TB级别。
对这种表一个简单的SELECT * 都会非常的慢，哪怕LIMIT 10想要看10条数据，也会走MapReduce流程 这个时间等待是不合适的。
Hive提供的快速抽样的语法，可以快速从大表中随机抽取一些数据供用户查看。
TABLESAMPLE函数
进行随机抽样，本质上就是用TABLESAMPLE函数
基于随机分桶抽样：
SELECT ... FROM tbl TABLESAMPLE(BUCKET x OUT OF y ON(colname | rand()))
y表示将表数据随机划分成y份（y个桶）
x表示从分好的桶中获取第几个桶的数据.(x的值不能大于y)
colname表示随机的依据基于某个列的值
rand()表示随机的依据基于整行
示例：
SELECT id,name,gender from students TABLESAMPLE(BUCKET 1 OUT OF 10 ON id);
SELECT id,name,gender from students TABLESAMPLE(BUCKET 1 OUT OF 10 ON rand());
注意：
使用colname作为随机依据，则其它条件不变下，每次抽样结果一致
使用rand()作为随机依据，每次抽样结果都不同
TABLESAMPLE函数其他用法
-- 快速取前面部分数据 : 快但没有随机
-- 前100条
select * from orders tablesample ( 100 rows );
-- 前10%数据
select * from orders tablesample ( 10 percent );
-- 取1k或者1m的数据
select * from orders tablesample (16k);
select * from orders tablesample (167k);
select * from orders tablesample (1m);总结
1. 为什么需要抽样？
大数据体系下，表内容一般偏大，小操作也要很久
所以如果想要简单看看数据，可以通过抽样快速查看
2. TABLESAMPLE函数的使用
桶抽样方式，TABLESAMPLE(BUCKET x OUT OF y ON(colname | rand()))，推荐，完全随机，速度略慢块抽样，使用分桶表可以加速
RLIKE 正则匹配
sql模糊查询关键字: like 任意0个或者多个: % 任意1个: _
正则模糊查询关键字: rlike 任意0个或者多个: .* 任意1个: . 正则语法还有很多......
正则表达式
正则表达式是一种规则集合，通过特定的规则字符描述，来判断字符串是否符合规则。
RLIKE
Hive中提供RLIKE关键字，可以供用户使用正则和数据进行匹配。
我们以上一节中使用的订单表为例，来简单使用一下RLIKE正则匹配。
查找广东省的数据
SELECT * FROM orders WHERE useraddress RLIKE '.*广东.*';
查找用户地址是：xx省 xx市 xx区的数据
SELECT * FROM orders WHERE useraddress RLIKE '..省 ..市 ..区';
查找用户姓为张、王、邓
SELECT * FROM orders WHERE username RLIKE '[张王邓]\\S+';
查找手机号符合：188****0*** 规则
SELECT * FROM orders WHERE userphone RLIKEE '188\\S{4}0\\S{3}';
总结
1. 什么是正则表达式
正则表达式就是一种规则的集合。通过特定的规则字符来匹配字符串是否满足规则的描述。
2. RLIKE的作用
可以基于正则表达式，对数据内容进行匹配
CTE表达式[补充]
CTE: 公用表表达式（CTE）是一个在查询中定义的临时命名结果集将在from子句中使用它。
注意: 每个CTE仅被定义一次（但在其作用域内可以被引用任意次）,仅适用于当前运行的sql语句
语法如下:
with 临时结果集的别名1 as (子查询语句),
临时结果集的别名2 as (子查询语句)
...
select 字段名 from (子查询语句);根据实际使用次数可以决定用"CTE表达式"还是"视图"还是"建表": with 临时结果名 as (select语句) > create view 视图名 as select语句 > create table 表名 as select语句
-- with: CTE表达式
-- 可以理解成把子查询语句从主查询语句中抽取出来起别名,这样的话主查询语句就比较清晰了
with
man as (select name,gender from students where gender = '男' ),
woman as (select name,gender from students where gender = '女')
select * from man
union
select * from woman;
内置虚拟列
Virtual Columns虚拟列
虚拟列是Hive内置的可以在查询语句中使用的特殊标记，可以查询数据本身的详细参数。
Hive目前可用3个虚拟列：
INPUT__FILE__NAME，显示数据行所在的具体文件
BLOCK__OFFSET__INSIDE__FILE，显示数据行所在文件的偏移量 ROW__OFFSET__INSIDE__BLOCK，显示数据所在HDFS块的偏移量
此虚拟列需要设置：SET hive.exec.rowoffset=true 才可使用
SET hive.exec.rowoffset=true;
select * ,INPUT__FILE__NAME,BLOCK__OFFSET__INSIDE__FILE,ROW__OFFSET__INSIDE__BLOCK from students;
按照位置分组
-- 按照位置分组
select INPUT__FILE__NAME ,count(*) from students group by INPUT__FILE__NAME;
按照偏移量查询
select *,BLOCK__OFFSET__INSIDE__FILE from students where BLOCK__OFFSET__INSIDE__FILE > 100;
总结
1. 什么是虚拟列，有哪些虚拟列？
虚拟列是Hive内置的可以在查询语句中使用的特殊标记，可以查询数据本身的详细参数。 INPUT__FILE__NAME，显示数据行所在的具体文件 BLOCK__OFFSET__INSIDE__FILE，显示数据行所在文件的偏移量 ROW__OFFSET__INSIDE__BLOCK，显示数据所在HDFS块的偏移量
此虚拟列需要设置：SET hive.exec.rowoffset=true 才可使用
2. 虚拟列的作用
查看行级别的数据详细参数
可以用于WHERE、GROUP BY等各类统计计算中
可以协助进行错误排查工作
原文地址：https://blog.csdn.net/m0_63845988/article/details/143578056
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！