【Python大数据笔记_day08_hive查询】

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基本查询

基础查询格式: 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)*yy: 本次查询记录数          

数据准备:

-- 创建订单表
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';
-- 加载数据(因为是普通内部表可以直接上传文件到hfds表路径下)

数仓分层思想:

-- 数仓分层: 本质就是分库分表
-- 构建源数据层
create database xls_ods;
-- 构建数数据仓库
create database xls_dw;
-- 构建数据应用层
create database xls_da;
​
-- 转换应用场景
-- 注意: 在大数据分析中转换完后为了以后方便使用一般存储起来
create table xls_dw.dw_orders as
selectorderid,orderno,shopid,userid,orderstatus,goodsmoney,delivermoney,totalmoney,realtotalmoney,casewhen payType=0 then '未知'when payType=1 then '支付宝'when payType=2 then '微信'when payType=3 then '现金'when payType=4 then '其他'end as payType,payType,username,useraddress,userphone,createtime,paytime,totalpayfee
from orders;
​
-- 修改字段类型
alter table dw_orders change orderstatus orderstatus string;
alter table dw_orders change ispay ispay string;
-- 修改后重新修改了,需要覆盖数据
insert overwrite table xls_dw.dw_orders
selectorderid,orderno,shopid,userid,casewhen orderstatus=-3 then '用户拒收'when orderstatus=-2 then '未付款的订单'when orderstatus=-1 then '用户取消'when orderstatus=0 then '待发货'when orderstatus=1 then '配送中'when orderstatus=2 then '用户确认收货'endas orderstatus,goodsmoney,delivermoney,totalmoney,realtotalmoney,casewhen payType=0 then '未知'when payType=1 then '支付宝'when payType=2 then '微信'when payType=3 then '现金'when payType=4 then '其他'end as payType,
​casewhen isPay=0 then '未支付'when isPay=1 then '已支付'end as isPay,username,useraddress,userphone,createtime,paytime,totalpayfee
from orders;

课堂练习:

-- 基础查询格式: select distinct 字段名 from 表名;
--              注意: *代表所有字段  distinct去重  as给表或者字段起别名且可以省略
-- 指定字段查询
select userName,userPhone from orders where userName='邓力夫';
-- 指定字段并且取别名查询
select distinct userName name,userPhone phone from orders where userName='邓力夫';
-- 当然也可以给表起别名(目前单表即使起了也没有多大意义)
select o.userName ,o.userPhone  from orders as o ;
-- 查询支付类型要求去重
select distinct payType from orders;
​
​
-- 2.演示where条件查询
-- 查询广东省订单
drop table if exists da_gd_orders;
create table da_gd_orders as
select * from orders where userAddress like '广东省%';
​
​
-- 3.演示聚合查询
-- 查询广东省数据量
select count(*) from orders where userAddress like '广东省%';
​
-- 4.演示分组查询
-- 注意: select后的字段要么在groupby后出现要么在聚合函数内出现,否则报错
-- 统计已支付和未支付各自多少人
select isPay,count(*) cnt from orders group by isPay;
​
-- 5.演示条件查询,聚合查询,分组查询综合练习
-- 在已支付订单中,统计每个用户最高的一笔消费金额
select userId, username, max(realTotalMoney)
from orders
where isPay = 1
group by userId, username;
-- 统计每个用户的平均消费金额
select userId, username, avg(realTotalMoney)
from orders
where isPay = 1
group by userId, username;
-- 统计每个用户的平均消费金额并且筛选大于10000的
select userId, username, avg(realTotalMoney) as avg_money
from orders
where isPay = 1
group by userId, username
having avg_money > 10000;
-- 统计每个用户的平均消费金额并且筛选大于10000的,平均值要求保留2位小数
select userId, username,round(avg(realTotalMoney),2)
from orders
where isPay = 1
group by userId, username
having round(avg(realTotalMoney),2) > 10000;
​
-- 6.演示排序查询
-- asc默认升序  desc 降序
-- 查询广东省订单,要求按照总价降序排序
select * from orders where userAddress like '广东省%' order by realTotalMoney desc;
​
-- 7.演示分页查询
-- limit x,y  注意: x和y都是整数,x是从0开始起始索引,y是查询的条数
-- 查询广东省订单总价最高的前5个订单
select * from orders where userAddress like '广东省%' order by realTotalMoney desc limit 5;

类sql多表查询

交叉连接格式: select 字段名 from 左表 cross join 右表;注意: 交叉连接产生的结果叫笛卡尔积,此种方式慎用!!!
​
内连接格式: select 字段名 from 左表 inner join 右表 on 左右表关联条件;特点: 相当于只取两个表的交集
​
左外连接格式:  select 字段名 from 左表 left outer join 右表 on 左右表关联条件;特点: 以左表为主,左表数据全部保留,右表只保留和左表有交集的部分
​
右外连接格式:  select 字段名 from 左表 right outer join 右表 on 左右表关联条件;特点: 以右表为主,右表数据全部保留,左表只保留和右表有交集的部分
​
自连接: 本质是一个特殊的内外连接,最大特点就是左右表是同一个表应用场景: 比较局限,场景1: 存储省市县三级数据的区域表   场景2: 存储上下级信息的员工表
​
子查询: 本质是一个select语句作为另外一个select语句的一部分(表或者条件)注意: 子查询作为表使用的话必须取别名

数据准备:

-- 创建用户表
CREATE TABLE users (userId int,loginName string,loginSecret int,loginPwd string,userSex tinyint,userName string,trueName string,brithday date,userPhoto string,userQQ string,userPhone string,userScore int,userTotalScore int,userFrom tinyint,userMoney double,lockMoney double,createTime timestamp,payPwd string,rechargeMoney double
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
-- 加载数据
load data inpath '/source/itheima_users.txt' into table users;
-- 验证数据
select * from users limit 1;

练习:

-- 交叉连接格式: select 字段名 from 左表 cross join 右表;
--          注意: 交叉连接产生的结果叫笛卡尔积,此种方式慎用!!!
select * from users cross join orders;
-- 内连接格式: select 字段名 from 左表 inner join 右表 on 左右表关联条件;
--          特点: 相当于只取两个表的交集
select * from users u inner join orders o on u.userId=o.userId ;
-- 左外连接格式:  select 字段名 from 左表 left outer join 右表 on 左右表关联条件;
--          特点: 以左表为主,左表数据全部保留,右表只保留和左表有交集的部分
select * from users u left outer join orders o on u.userId=o.userId ;
-- 右外连接格式:  select 字段名 from 左表 right outer join 右表 on 左右表关联条件;
--          特点: 以右表为主,右表数据全部保留,左表只保留和右表有交集的部分
select * from users u right outer join orders o on u.userId=o.userId ;
​
-- 自连接: 本质是一个特殊的内外连接,最大特点就是左右表是同一个表
--          应用场景: 比较局限,场景1: 存储省市县三级数据的区域表   场景2: 存储上下级信息的员工表
-- 可以运行下基础班的areas.sql脚本,做以下练习
-- 方式1: 建议
select xian.title
from(select * from areas  where title = '北京市' and pid is not null) city
joinareas xian
on city.id = xian.pid;
-- 方式2:
select xian.title
fromareas city
joinareas xian
on city.id = xian.pid
where city.title = '北京市' and city.pid is not null;
-- 子查询: 本质是一个select语句作为另外一个select语句的一部分(表或者条件)
--          注意: 子查询作为表使用的话必须取别名
;
select title
from areas
where pid = (select id from areas where title = '北京市' and pid is not null);

hive其他join操作

在Hive中除了支持cross join（交叉连接，也叫做笛卡尔积）,inner join（内连接）、left outer join（左外连接）、right outer join（右外连接）还支持full outer join（全外连接）、left semi join（左半开连接）

全外连接: 左表 full [outer] join 右表 on 条件
​
左半开连接: 左表 left semi join 右表 on 条件

-- hive不同于mysql的join操作
-- 全外连接(左表 full outer join 右表 on 条件) 大白话就是左外和右外结果合并同时去重
select * from users u full outer join orders o on u.userId = o.userId;
​
-- 左半开连接(左表 left semi join 右表 on 条件) 大白话就是内连接的一半
select * from users u left semi join orders o on u.userId = o.userId;

hive其他排序操作

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或者值大于桶数量

-- 演示4个by区别
-- 创建表
create table students(id int,name string,gender string,age int,cls string
)row format delimited
fields terminated by ',';
-- 加载数据
load data inpath '/source/students.txt' into table students;
-- 验证数据
select * from students limit 1;
​
​
-- 查询reduces的数量
set mapreduce.job.reduces; -- -1代表根据任务实时改变
-- 1.cluster by 字段名 查询的时候分桶且排序
-- 注意: 如果是1个reduces那么cluster by全局升序排序
select * from students cluster by id;
-- 修改reduces数量为3
set mapreduce.job.reduces=3;
-- 再次使用cluster by查询,查看效果
-- 效果: 如果多个reduces那么cluster by桶内局部排序
select * from students cluster by age;
​
​
-- 2.distribute by + sort by
-- 设置reduces的数量为-1
set mapreduce.job.reduces = -1;
-- 默认1个ruduces数量,使用distribute by + sort by查询观察结果
-- 注意: 如果是1个ruduces那么distribute by + sort by全局排序
select * from students distribute by name sort by age desc;
-- 修改reduces数量
set mapreduce.job.reduces = 2;
-- 再次distribute by + sort by查询
-- 效果: 如果多个redueces,那么distribute by 分reduces数量个桶,sort by桶内局部排序
select * from students distribute by name sort by age desc;
​
​
-- 3.order by
-- 注意: order by 永远都是全局排序,不受reduces数量影响,每次只用1个reduces
select * from students order by age desc;

抽样查询

TABLESAMPLE抽样好处: 尽可能实现随机抽样,并且不走MR查询效率相对较快
​
基于随机分桶抽样格式: SELECT 字段名 FROM tbl TABLESAMPLE(BUCKET x OUT OF y ON(字段名 | rand()))y:表示将表数据随机划分成y份（y个桶）x:表示从y里面随机抽取x份数据作为取样| : 或者字段名: 表示随机的依据基于某个列的值,每次按相关规则取样结果都是一致rand(): 表示随机的依据基于整行,每次取样结果不同

-- 随机抽样函数 tablesample
-- 参考字段分桶抽样,快且随机
select * from orders tablesample ( bucket 1 out of 10 on orderid);
-- 参考rand()随机数,快且真正达到随机
select * from orders tablesample ( bucket 1 out of 10 on rand());
​
​
-- 快速取前面部分数据 : 快但没有随机
-- 前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 );
​
-- 随机取100条: 随机但是不快
select * from orders distribute by rand() sort by rand() limit 100;

正则模糊查询

sql模糊查询关键字: like      任意0个或者多个:  %     任意1个: _    
​
正则模糊查询关键字: rlike     任意0个或者多个: .*     任意1个: .     正则语法还有很多...

-- 正则表达式查询
-- 1.查询广东省数据
-- sql模糊查询
select * from orders where userAddress like '广东省%';
-- 正则模糊查询
select * from orders where userAddress rlike '广东省.*';
​
-- 2. 查询满足'xx省 xx市 xx区'格式的信息
-- sql模糊查询
select * from orders where userAddress like '__省 __市 __区';
-- 正则模糊查询
select * from orders where userAddress rlike '..省 ..市 ..区';
​
-- 3.查询所有姓张王邓的用户信息
-- sql模糊查询
select * from orders where username like '张%' or username like '王%' or username like '邓%' ;
-- 正则模糊查询
select * from orders where username rlike '[张王邓].*';
select * from orders where username rlike "[张王邓].+";
​
-- 4.查找所有188开头的手机号
-- sql模糊查询
select * from orders where userPhone like '188________' ;
-- 正则模糊查询
select * from orders where userPhone rlike '188........' ;
select * from orders where userPhone rlike '188.{8}' ;
select * from orders where userPhone rlike '188\\*{4}[0-9]{4}' ;
select * from orders where userPhone rlike '188\\*{4}\\d{4}' ;

union联合查询

union联合查询:  就是把两个select语句结果合并成一个临时结果集,整体可以用于其他sql操作
​
union [distinct]: 去重,只是省略了distinct
union all : 不去重

-- 插入数据
insert into product values('p1','联想','c1'),('p2','小米','c2'),('p3','华为',null);
-- 创建分类表
create table category(cid varchar(100),cname varchar(100)
);
-- 插入数据
insert into category values('c1','电脑'),('c2','手机'),('c3','服饰');
​
-- 1.如果在mysql中,使用union实现全外连接
-- 使用union关键字,自动去重
-- 左外 union 右外
select pid,pname,p.cid,cname from product p left join category c on p.cid = c.cid
union
select pid,pname,c.cid,cname from product p right join category c on p.cid = c.cid;
​
​
-- 注意: 如果不想去重使用 union all
-- 左外 union all 右外
select pid,pname,p.cid,cname from product p left join category c on p.cid = c.cid
union  all
select pid,pname,c.cid,cname from product p right join category c on p.cid = c.cid;
​
-- 2.在hive中使用full outer join实现全外连接
select pid,pname,c.cid,cname from product p full join category c on p.cid = c.cid;

CTE表达式

CTE: 公用表表达式（CTE）是一个在查询中定义的临时命名结果集将在from子句中使用它。
注意: 每个CTE仅被定义一次（但在其作用域内可以被引用任意次）,仅适用于当前运行的sql语句
语法如下:with 临时结果集的别名1  as (子查询语句),临时结果集的别名2  as (子查询语句)...select 字段名 from (表别名);

内置虚拟列

虚拟列是Hive内置的可以在查询语句中使

用的特殊标记，可以查询数据本身的详细参数。
Hive目前可用3个虚拟列：
INPUT__FILE__NAME:显示数据行所在的具体文件
BLOCK__OFFSET__INSIDE__FILE:显示数据行所在文件的偏移量
ROW__OFFSET__INSIDE__BLOCK:显示数据所在HDFS块的偏移量   注意: 此虚拟列需要设置：SET hive.exec.rowoffset=true 才可使用

-- 演示内置虚拟列
-- 打开ROW__OFFSET__INSIDE__BLOCK
SET hive.exec.rowoffset=true;
-- 演示查询
SELECT *, INPUT__FILE__NAME, BLOCK__OFFSET__INSIDE__FILE, ROW__OFFSET__INSIDE__BLOCK FROM students_bucket;