|
查询语句基本语法：
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 ',';
列转行：将一列数据转换成多行
行转列：把多行数据转化成一行数据
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;
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
窗口函数：
窗口函数，是分析函数的一种，用于解决复杂报表统计需求的功能强大的函数，窗口函数用于计算基于组的某种聚合值，他和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行，开窗函数可以指定分析函数工作的数据窗口的大小，这个数据窗口大小随着行的变化而变化使用结果：通过窗口函数（over），可以让明细查询和统计查询同时执行，对于左边的明细数据通过over函数单独开一个窗口进行右边的统计运算
在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){
String result = null;
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;
|