查询语句基本语法:
sql查询的基本结构:
select * 要查询的列
from tablename 要查询的表
join on 连接的表
where 查询条件
group by 分组查询
order by 字段排序
sort by 结果排序
limit 限制结果数
union/union all 合并表
hql的执行顺序:
from
on
join
where
group by
having
select
distinct
order by
limit
---举例
explain
select
sid,sname
from student
group by sid,sname
limit 2;
查询注意事项:
1.尽量不要使用子查询,尽量不要使用in,not in
2.查询尽量避免join连接查询
3.查询永远是小表驱动大表
join语法:
1.表与表的关系:
表连接分成几种类型:
内连接(inner join)
外连接(outer join)
左外连接(left join)
右外连接(right join)
全外连接(full join)
最常用的查询:left join和join(其实就是inner join)
四种处理方法:
只返回两张表匹配的记录,这种叫做内连接(inner join)
返回匹配的记录,以及表A多余的记录,这叫做左外连接(left join)
返回匹配的记录,以及表B多余的记录,这叫做右外连接(right join)
返回匹配的记录,以及A表和B表各自多余的记录这叫做全外连接(full join)
Hive专有的hoin特点:
left semi join
在hive中,有一种专有的join操作,left semi join,我们称之为半开连接,它是left join的一种优化,只能查询左表信息,主要用于解决hive中左边的数据是否存在的问题,相当于exists的用法
子查询:
hive对子查询支持不是很友好,特别是“=”问题较多
inner join和outer join的区别:
分区字段对outer join中的on条件无效,对inner join中的on条件有效
有inner join 但是没有full inner join
有full outer join 但是没有outer join
所有的join连接只支持等值连接(= 和 and),不支持!=,<,>,>=,<=,or
map-side join
如果所用表中有小表,将会把小表缓存在内存中,然后在map端进行连接查找,hive在map段查找时会减少整体查询量,从内存中读取缓存的小表数据,效率快,还省去大量数据传输和shuffle耗时
表示式别名:
对有些表达式而言,查询的字段名如果比较难以理解,可以给表达式起一个别名,使用as将复杂表达式命名为一个容易懂的别名
select
d.dname
length(d.dname) as nameLength
from dept d;
查询子句:
1.where语句的特点:where后面不能跟聚合函数
2.group by:分组,通常和聚合函数搭配使用,查询的字段要么出现在聚合函数中,要么出现在group by后面
3.having:是对分组后的结果集进行过滤
4.Limit:限制从结果集中取数据的条数,一般用于分页
5.排序:
5.1order by:全局排序,引发全表扫描,reduce数量一个,不建议使用
5.2sort by:默认分区内排序,当reduceTask的数量是1是,效果和order by一样,一般和distribute by搭配使用
5.3distribute by:用来确定用哪个字段来分区,一般写在sort by的前面
5.4cluster by:兼有distribute by以及sort by的升序功能(排序只能是升序排序(默认),不能指定排序规则为asc或者desc)
区别:
1.distribute by:根据by后面的字段和reduceTask个数,决定maptask的输出去往哪个reducetask,默认使用查询的第一列的hash值来决定maptask的输出去往哪个reducetask,如果reducetask个数为1,那就没有任何体现
2.sort by:局部排序,只能保证单个reducetask有顺序
3.order by:全局排序,保证所有reducer中的数据都是有序的
如果reducetask的数量只有一个,那二者都差不多,两者通常和desc,asc搭配使用,默认使用asc
4order by的缺点 :
由于是全局排序,所以所有的数据都会通过一个reducer进行处理,当数据结果比较大时,一个reducer进行处理非常影响性能
注意:当开始mr严格模式的时候,order by必须要设置limit子句,要限制全局查询,否则会报错
合并结果集:
union:将多个结果集合并,去重,排序(按照第一个字段排序,如果进行了分桶,会按照分桶内进行排序)
union all:将多个结果集合并,不去重,不排序
单个union语句不支持:orderby,clusterby,distributeby,sortby,limit
单个union语句字段的个数要求相同,字段的顺序要求相同
复杂数据类型:
类型 | 描述 | 字面量示例 |
---|---|---|
array | 有序的同类型集合 | array(1,2) |
map | key-value,key是原始类型,vlaue可以是任意类型 | map(‘a’:1,‘b’:2) |
struct | 字段集合,类型可以不同 | struct(‘1’,1,1.0) |
union | 在有限取值范围内的一个值 | create_union(1,‘a’,63) |
定义示例:
array :column array<基本数据类型>,下标从0开始,查询时如果越界,不会报错而是用null替代map :column map<string,string>struct :column struct
create table if not exists arrays(id int,name string,score array<double>)row format delimited fields terminated by '\t'collection items terminated by ','; //插入的数据:1 zhangsan 78,68,54,592 lisi 96,74,84,35//查询的数据:select * from array;select name,score[1] from array where size(score) > 1;
列转行:将一列数据转换成多行
#原始数据:zhangsan 90,87,51,64#转换后的数据zhangsan 90zhangsan 87zhangsan 63zhangsan 76
行转列:把多行数据转化成一行数据
#原始数据zhangsan 90zhangsan 87zhangsan 98zhangsan 64#转化后数据zhangsan 90,87,98,64
--------------------explode----列转行(调用函数explode)select explode(tags) tags from t1 ----------这种只能查询经爆炸函数处理之后的值,并且explode(tags) 后面跟的是列名select name,id from t1 lateral view explode(tags) tags as id; --------这种方式处理之后,就可以表中任意列的值--行转列(调用函数collect_set(去重),collect_list(不去重))select collect_set(temp.name) as name,collect_set(temp.id) as idfrom(select name,id from t1 lateral view explode(tags) tags as id)tempgroup by temp.name;
map示例
数据:zhangsan chinese:90,math:87,english:63,nature:76lisi chinese:60,math:30,english:78,nature:0wangwu chinese:89,math:25,english:81,nature:9
建表:create table if not exists map1(name string,score map<string,int>)row format delimited fields terminated by ','collection item terminated by ','map keys teminated by ':';
查询:查询数学大于35分的学生的英语和自然成绩select m.name,m.score['english'],m.score['nature']from map1 mwhere m.score['math']>35;
----------map----------建表create table if not exists student(name string,score map<string,int>)row format delimitedfields terminated by '\t'collection items terminated by ','map keys terminated by ':'lines terminated by '\n';------------map的简单使用:查询数学大于35分的学生的英语和自然成绩selectname,score['english'],score['nature']from studentwhere score['math']>35;map的列转行select explode(score) as (s_class,s_score) from student; --------这种只能查询经爆炸函数处理之后的值,并且explode(score)后面跟的是列名select name,s_class,s_scorefrom student lateral view explode(score) score as s_class,s_score; ---------这种方式处理之后,就可以表中任意列的值map的行转列create table map_temp(name string,score1 int,score2 int,score3 int)row format delimitedfields terminated by ',';select name,map('chinese',score1,'math',score2,'english',score3)from map_temp;
struct
建表:create table if not exists str(name string,addr struct <province:string,city:string,xian:string,doorid:int >)row format delimited fields terminated by '\t'collection items terminated by ',';查询数据:select name,addr.province,addr.city,addr.xian,addr.doorid from str;
复杂数据类型综合:
create table if not existscreate table if not exists info(id int,name string,belong array<string>,tax map<string,double>,addr struct <province:string,city:string,road:string>)row format delimitedfields terminated by ' 'collection items terminated by ','map keys terminated by ':'lines terminated by '\n';
嵌套数据类型:
写数据时每个数据之间使用:
\001------control+V和control+A
\002------control+V和control+B
…
建表时直接使用\001,\002,,…
系统内置函数:
1.函数查看show functions -----查看hive中的所有函数desc functions fname -----查看某个函数的使用方法2.日期函数select current_date ------2021-07-10select datediff('2018-06-18','2018-11-21') -------------- -156select current_timestramp() -----2021-07-10 16:49:20.821select date_format(current_date,'yyyy-MM-dd HH:mm:ss') -------------2021-07-10 00:00:003.字符串函数select lower('ABC')select upper('abc')select length('abc')select concat('A','B','C') --字符串拼接select concat_ws('-','a','b','c') --字符串拼接,按照指定符号拼接select substr('2021-07-10',0,2) --切割字符串4.类型转换函数select cast('123' as int)+1;5.数学函数select round(42.3) ---四舍五入select ceil(42.3) ---向上取整 select floor(42.3) ---向下取整6.判断为空函数select nvl(expr1,expr2) #将查询结果为null值转换成指定值
窗口函数:
窗口函数,是分析函数的一种,用于解决复杂报表统计需求的功能强大的函数,窗口函数用于计算基于组的某种聚合值,他和聚合函数的不同之处是:对于每个组返回多行,而聚合函数对于每个组只返回一行,开窗函数可以指定分析函数工作的数据窗口的大小,这个数据窗口大小随着行的变化而变化使用结果:通过窗口函数(over),可以让明细查询和统计查询同时执行,对于左边的明细数据通过over函数单独开一个窗口进行右边的统计运算
#查询在2018年1⽉份购买过的顾客购买明细(多个结果)及总人数(只有一个结果)select *,count(*) over()from t_orderwhere substr(orderdate,0,7)='2018-01';t_order.name t_order.orderdate t_order.cost count_window_0tony 2018-01-02 15 6tony 2018-01-04 29 6saml 2018-01-05 46 6tony 2018-01-07 50 6saml 2018-01-08 55 6saml 2018-01-01 10 6
在over开窗函数中可以添加的组件:
partition by:在over窗口中进行分区,对每一列进行分区统计,窗口的大小就是分区的大小
select name,orderdate,cost,sum(cost) over (partition by month(orderdate)) from t_order;name orderdate cost sum_window_0saml 2018-01-01 10 205saml 2018-01-08 55 205tony 2018-01-07 50 205saml 2018-01-05 46 205tony 2018-01-04 29 205tony 2018-01-02 15 205saml 2018-02-03 23 23mart 2018-04-13 94 341saml 2018-04-06 42 341mart 2018-04-11 75 341mart 2018-04-09 68 341mart 2018-04-08 62 341neil 2018-05-10 12 12neil 2018-06-12 80 80
order by:会让输入的数据强制排序
#查看顾客的购买明细(多个结果)及月总购买总额(只有一个结果)
select name,orderdate,cost,
sum(cost) over (partition by month(orderdate) order by orderdate)
from t_order;
name orderdate cost sum_window_0
saml 2018-01-01 10 10
tony 2018-01-02 15 25
tony 2018-01-04 29 54
saml 2018-01-05 46 100
tony 2018-01-07 50 150
saml 2018-01-08 55 205
saml 2018-02-03 23 23
saml 2018-04-06 42 42
mart 2018-04-08 62 104
mart 2018-04-09 68 172
mart 2018-04-11 75 247
mart 2018-04-13 94 341
neil 2018-05-10 12 12
neil 2018-06-12 80 80
window:对窗口进行更细粒度的划分
常用参数:
preceding:向前
following:向后
current row:当前行
unbounded:无限制
组合:
unbounded preceding:前面起点
unbounded following:后面的重点
结构:
rows between 起点 and 终点
使用示例:
#求每名用户每天的购买明细和截至当日累计购买数量
select name,orderdate,cost,
sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row)
from t_order;
name orderdate cost sum_window_0
mart 2018-04-08 62 62
mart 2018-04-09 68 130
mart 2018-04-11 75 205
mart 2018-04-13 94 299
neil 2018-05-10 12 12
neil 2018-06-12 80 92
saml 2018-01-01 10 10
saml 2018-01-05 46 56
saml 2018-01-08 55 111
saml 2018-02-03 23 134
saml 2018-04-06 42 176
tony 2018-01-02 15 15
tony 2018-01-04 29 44
tony 2018-01-07 50 94
序列函数:
ntile:用于将分组数据按照顺序进行切割分成n片,返回当前切片值,如果切片不均匀,默认增加第一个切片的分布
select name,orderdate,cost,
ntile(3) over(partition by name)
from t_order;
name orderdate cost ntile_window_0
mart 2018-04-13 94 1
mart 2018-04-08 62 1
mart 2018-04-09 68 2
mart 2018-04-11 75 3
neil 2018-06-12 80 1
neil 2018-05-10 12 2
saml 2018-02-03 23 1
saml 2018-04-06 42 1
saml 2018-01-05 46 2
saml 2018-01-08 55 2
saml 2018-01-01 10 3
tony 2018-01-02 15 1
tony 2018-01-04 29 2
tony 2018-01-07 50 3
lag函数和lead函数:
语法:
lag(exp_str,offset,defValue) over(partition by ... order by ...)
lead(exp_str,offset,defValue) ver(partition by ... order by ...)
exp_str:要查询的字段名
offset:偏移量,即上N行
default:默认值,如果当前表的前(后)N行已经超过了表的范围,那么就取这个默认值,如果没有指定,返回null
select name,orderdate,cost,
lag(orderdate,1,'1990-01-01') over(partition by name order by orderdate) as time1
from t_order;
name orderdate cost time1
mart 2018-04-08 62 1990-01-01
mart 2018-04-09 68 2018-04-08
mart 2018-04-11 75 2018-04-09
mart 2018-04-13 94 2018-04-11
neil 2018-05-10 12 1990-01-01
neil 2018-06-12 80 2018-05-10
saml 2018-01-01 10 1990-01-01
saml 2018-01-05 46 2018-01-01
saml 2018-01-08 55 2018-01-05
saml 2018-02-03 23 2018-01-08
saml 2018-04-06 42 2018-02-03
tony 2018-01-02 15 1990-01-01
tony 2018-01-04 29 2018-01-02
tony 2018-01-07 50 2018-01-04
first_value和last_value函数:
first_value:取分组内排序后的,截止到当前行的第一个值
last_value:取分组内排序后,截止到当前行,最后一个值
排名函数:
使用场景 :
1.能做分组排名
2.能做自增的主键
row_number():没有并列,相同名词依次按照顺序排列1234
rank():有并列,相同名次空位1134
dense_rank():有并列,相同名次不空位1123
自定义函数
自定义函数的含义:
hive的内置函数满足不了所有的业务需求
hive提供了很多的模块可以自定义的功能,比如:自定义函数,serde,输入输出格式
自定义函数的分类:
1.UDF:用户自定义函数。user defined function,一对一的输入输出(最常用的udf)
2.UDTF:用户自定义表生成函数。user defined table-generate function,一对多的输入输出,lateral view explode
3.UDAF:用户自定义聚合函数。user defined aggregate function 多对一的输入输出count sum max
自定义函数实现
定义UDF函数需要注意下面几点:
1.继承org.apache.hadoop.hive.ql.exec.UDF
2.重写evaluate(),这个方法不是由接口定义的,因为它可接受的参数的个数,数据类型都是不确定的,hive会检查UDF,看能否找到和函数调用相匹配的evaluate()方法
3.evaluate()方法允许重载
自定义函数的案例:
package com.qf.hive.UDF;
import org.apache.hadoop.hive.ql.exec.UDF;
import org.datanucleus.util.StringUtils;
public class FirstUDF extends UDF {
public String evaluate(String string){
//关于默认输出是null还是“”,这个需要看具体的需求定义,这里默认定义为null
String result = null;
//1.检查输入参数
if (!StringUtils.isEmpty(string)){
result = string.toUpperCase();
}
return result;
}
//调试自定义函数
public static void main(String[] args){
System.out.println(new FirstUDF().evaluate("helloworld"));
}
}
函数的加载方式
1.命令加载
add jar /root/上传至linux的jar包名
create temporary function len_of_str as '类的全限定名'
2.启动参数加载
2.1在hive的conf目录下编写文件hive-init
2.2将下述内容添加到hive-site文件中
add jar /root/上传至linux的jar包名;
add temporary function len_of_str as '类的全限定名'
2.3启动hive时指定hive的启动方式:
hive -i /usr/local/hive/conf/hive-init
注意:上述两种方式都是对本session有效,是临时函数
3.配置文件加载
3.1将udf的jar包上传到/usr/local/hive/lib/中
3.2在hive的bin目录下创建一个配置文件:.hiverc
3.3在文件中添加如下内容:
add jar /usr/loca/hive/lib/udf.jar;
create temporary function len_of_str as '类的全限定名';
3.4启动hive即可使用
序列化和反序列化:
1.序列化(serialize):将对象转化成字节序列的过程
2.反序列化(deserialize):将字节码恢复成对象的过程
序列化的作用:
1.对象的持久化保存;将对象转换成字节码后保存文件
2.对象数据的传输;
serialize/deserialize合起来的缩写叫做serde,serde允许hive读取表中的数据,并将其以任何自定义格式写回HDFS,任何人都可以为自己的数据格式编写自己的serde
常用的serder:
csv,tsv,json serder,regexp serder等
csv:逗号分隔值
tsv:tab分隔值
json:json格式的数据
regexp:数据需要符合正则表达式
CSVSerde:在创建表的时候,如果数据是csv格式,那么不用手工去指定列分隔符,直接用CSVSerde即可
create table if not exists csv1(
uid int,
uname string,
age int
)
row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
stored as textfile;