企业需要做网站吗,深圳装修公司前十强,杭州优化排名哪家好,免费wordpress平台思维导图 基本查询
基本语法
SELECT [ALL | DISTINCT] 字段名, 字段名, ...
FROM 表名 [inner | left outer | right outer | full outer | left semi JOIN 表名 ON 关联条件 ]
[WHERE 非聚合条件]
[GROUP BY 分组字段名]
[HAVING 聚合条件]
[ORDER BY 排序字段名 asc | desc…思维导图 基本查询
基本语法
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 pidp1; # union all 拼接过程中不会去重.
select * from product
union all
select * from product where pidp1; hive有别于mysql的排序 cluster by关键字 distribute bysort 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 bysort by就相当于把cluster by的功能一分为二
distribute by和sort by可以跟不同的字段
DISTRIBUTE BY负责分
SORT BY负责分组内排序
如果DISTRIBUTE BY SORT BY的后面字段一样可以得出下列结论
CLUSTER BYDISTRIBUTE 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表达式 -- 可以理解成把子查询语句从主查询语句中抽取出来起别名,这样的话主查询语句就比较清晰了
withman 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.rowoffsettrue 才可使用
SET hive.exec.rowoffsettrue;
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.rowoffsettrue 才可使用
2. 虚拟列的作用 查看行级别的数据详细参数 可以用于WHERE、GROUP BY等各类统计计算中 可以协助进行错误排查工作
