基本查询
创建部门表
create table if not exists dept(deptno int,dname string,loc int)row format delimited fields terminated by '';创建员工表
create table if not exists emp(empno int,ename string,job string,mgr int,hiredate string, sal double, comm double,deptno int)row format delimited fields terminated by '';导入数据
hive (default)> load data local inpath '/opt/module/datas/dept.txt' into tabledept;hive (default)> load data local inpath '/opt/module/datas/emp.txt' into table emp;1．全表查询
hive (default)> select * from emp;2．选择特定列查询
hive (default)> select empno, ename from emp;注意：
(1)SQL 语言大小写不敏感。
(2)SQL 可以写在一行或者多行。
(3)关键字不能被缩写也不能分行。
(4)各子句一般要分行写。
(5)使用缩进提高语句的可读性。
Hive，对我们平常书写SQL很多地方是相同的，例如分组，排序，左连接，右连接等，不再介绍了，现在介绍他们不一样的地方。
Like和RLike
1)使用LIKE运算选择类似的值
2)选择条件可以包含字符或数字:
% 代表零个或多个字符(任意个字符)。
_ 代表一个字符。
3)RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。
4)案例实操
(1)查找以2开头薪水的员工信息
hive (default)> select * from emp where sal LIKE '2%';(2)查找第二个数值为2的薪水的员工信息
hive (default)> select * from emp where sal LIKE '_2%';(3)查找薪水中含有2的员工信息
hive (default)> select * from emp where sal RLIKE '[2]';连接谓词中不支持or
hive join目前不支持在on子句中使用谓词or
#错误的hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno= d.deptno or e.ename=d.ename;排序
全局排序(Order By)
Order By：全局排序，只有一个Reducer
hive (default)> select ename, sal*2 twosal from emp order by twosal;1．使用 ORDER BY 子句排序
ASC(ascend): 升序(默认)
DESC(descend): 降序
Sort By：对于大规模的数据集order by的效率非常低。在很多情况下，并不需要全局排序，此时可以使用sort by。
Sort by为每个reducer产生一个排序文件。每个Reducer内部进行排序，对全局结果集来说不是排序。
1．设置reduce个数
hive (default)> set mapreduce.job.reduces=3;2．查看设置reduce个数
hive (default)> set mapreduce.job.reduces;3．根据部门编号降序查看员工信息
hive (default)> select * from emp sort by deptno desc;4．将查询结果导入到文件中(按照部门编号降序排序)
hive (default)> insert overwrite local directory '/opt/module/datas/sortby-result' select * from emp sort by deptno desc;分区排序(Distribute By)
Distribute By： 在有些情况下，我们需要控制某个特定行应该到哪个reducer，通常是为了进行后续的聚集操作。distribute by 子句可以做这件事。distribute by类似MR中partition(自定义分区)，进行分区，结合sort by使用。
对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。
案例实操：
(1)先按照部门编号分区，再按照员工编号降序排序。
hive (default)> set mapreduce.job.reduces=3;hive (default)> insert overwrite local directory '/opt/module/datas/distribute-result'select * from emp distribute by deptno sort by empno desc;注意：
1． distribute by的分区规则是根据分区字段的hash码与reduce的个数进行模除后，余数相同的分到一个区。
2． Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。
Cluster By
当distribute by和sorts by字段相同时，可以使用cluster by方式。
cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。
1)以下两种写法等价
hive (default)> select * from emp cluster by deptno;hive (default)> select * from emp distribute by deptno sort by deptno;注意：按照部门编号分区，不一定就是固定死的数值，可以是20号和30号部门分到一个分区里面去。
分桶及抽样查询
分桶表数据存储
分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区。对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围划分。
分桶是将数据集分解成更容易管理的若干部分的另一个技术。
分区针对的是数据的存储路径；分桶针对的是数据文件。
1．先创建分桶表，通过直接导入数据文件的方式
(1)数据准备
student.txt
(2)创建分桶表
create table stu_buck(id int, name string)clustered by(id) into 4 bucketsrow format delimited fields terminated by '';(3)查看表结构
hive (default)> desc formatted stu_buck;Num Buckets: 4(4)导入数据到分桶表中
hive (default)> load data local inpath '/opt/module/datas/student.txt' into table stu_buck;(5)查看创建的分桶表中是否分成4个桶
发现并没有分成4个桶。是什么原因呢？
2．创建分桶表时，数据通过子查询的方式导入
(1)先建一个普通的stu表
create table stu(id int, name string)row format delimited fields terminated by '';(2)向普通的stu表中导入数据
load data local inpath '/opt/module/datas/student.txt' into table stu;(3)清空stu_buck表中数据
truncate table stu_buck;select * from stu_buck;(4)导入数据到分桶表，通过子查询的方式
insert into table stu_buckselect id, name from stu;(5)发现还是只有一个分桶
(6)需要设置一个属性
hive (default)> set hive.enforce.bucketing=true;hive (default)> set mapreduce.job.reduces=-1;hive (default)> insert into table stu_buckselect id, name from stu;
(7)查询分桶的数据
hive (default)> select * from stu_buck;OKstu_buck.id stu_buck.name1004 ss41008 ss81012 ss121016 ss161001 ss11005 ss51009 ss91013 ss131002 ss21006 ss61010 ss101014 ss141003 ss31007 ss71011 ss111015 ss15分桶规则：
根据结果可知：Hive的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中
分桶抽样查询
对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive可以通过对表进行抽样来满足这个需求。
查询表stu_buck中的数据。
hive (default)> select * from stu_buck tablesample(bucket 1 out of 4 on id);注：tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y) 。
y必须是table中bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。例如，table总共分了4份，当y=2时，抽取(4/2=)2个bucket的数据，当y=8时，抽取(4/8=)1/2个bucket的数据。
x表示从哪个bucket开始抽取，如果需要取多个分区，以后的分区号为当前分区号加上y。例如，table总bucket数为4，tablesample(bucket 1 out of 2)，表示总共抽取(4/2=)2个bucket的数据，抽取第1(x)个和第3(x+y)个bucket的数据。
注意：x的值必须小于等于y的值，否则
FAILED: SemanticException [Error 10061]: Numerator should not be bigger than denominator in sample clause for table stu_buckHive的窗口函数
1．相关函数说明
OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。
CURRENT ROW：当前行
n PRECEDING：往前n行数据
n FOLLOWING：往后n行数据
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点
LAG(col,n,default_val)：往前第n行数据
LEAD(col,n, default_val)：往后第n行数据
NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。
2．数据准备：name，orderdate，cost
jack,2017-01-01,10tony,2017-01-02,15jack,2017-02-03,23tony,2017-01-04,29jack,2017-01-05,46jack,2017-04-06,42tony,2017-01-07,50jack,2017-01-08,55mart,2017-04-08,62mart,2017-04-09,68neil,2017-05-10,12mart,2017-04-11,75neil,2017-06-12,80mart,2017-04-13,943．需求
(1)查询在2017年4月份购买过的顾客及总人数
(2)查询顾客的购买明细及月购买总额
(3)上述的场景, 将每个顾客的cost按照日期进行累加
(4)查询每个顾客上次的购买时间
(5)查询前20%时间的订单信息
4．创建本地business.txt，导入数据
vi business.txt5．创建hive表并导入数据
create table business(name string, orderdate string,cost int) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';load data local inpath "/opt/module/datas/business.txt" into table business;6．按需求查询数据
(1)查询在2017年4月份购买过的顾客及总人数
select name,count(*) over () from business where substring(orderdate,1,7) = '2017-04' group by name;(2)查询顾客的购买明细及月购买总额
select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) from business;(3)上述的场景, 将每个顾客的cost按照日期进行累加
select name,orderdate,cost, sum(cost) over() as sample1,--所有行相加 sum(cost) over(partition by name) as sample2,--按name分组，组内数据相加 sum(cost) over(partition by name order by orderdate) as sample3,--按name分组，组内数据累加 sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as sample4 ,--和sample3一样,由起点到当前行的聚合 sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5, --当前行和前面一行做聚合 sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行 sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行 from business;rows必须跟在Order by 子句之后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量
(4)查看顾客上次的购买时间
select name,orderdate,cost, lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) as time1, lag(orderdate,2) over (partition by name order by orderdate) as time2 from business;(5)查询前20%时间的订单信息
select * from ( select name,orderdate,cost, ntile(5) over(order by orderdate) sorted from business) twhere sorted = 1;Rank
1．函数说明
RANK() 排序相同时会重复，总数不会变
DENSE_RANK() 排序相同时会重复，总数会减少
ROW_NUMBER() 会根据顺序计算
2．数据准备
3．需求
计算每门学科成绩排名。
4．创建本地score.txt，导入数据
vi score.txt5．创建hive表并导入数据
create table score(name string,subject string, score int) row format delimited fields terminated by "";load data local inpath '/opt/module/datas/score.txt' into table score;6．按需求查询数据
select name,subject,score,rank() over(partition by subject order by score desc) rp,dense_rank() over(partition by subject order by score desc) drp,row_number() over(partition by subject order by score desc) rmpfrom score;name subject score rp drp rmp孙悟空 数学 95 1 1 1宋宋 数学 86 2 2 2婷婷 数学 85 3 3 3大海 数学 56 4 4 4宋宋 英语 84 1 1 1大海 英语 84 1 1 2婷婷 英语 78 3 2 3孙悟空 英语 68 4 3 4大海 语文 94 1 1 1孙悟空 语文 87 2 2 2婷婷 语文 65 3 3 3宋宋 语文 64 4 4 4系统内置函数
1．查看系统自带的函数
hive> show functions;2．显示自带的函数的用法
hive> desc function upper;3．详细显示自带的函数的用法
hive> desc function extended upper;自定义函数
1)Hive 自带了一些函数，比如：max/min等，但是数量有限，自己可以通过自定义UDF来方便的扩展。
2)当Hive提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数(UDF：user-defined function)。
3)根据用户自定义函数类别分为以下三种：
(1)UDF(User-Defined-Function)
一进一出
(2)UDAF(User-Defined Aggregation Function)
聚集函数，多进一出
类似于：count/max/min
(3)UDTF(User-Defined Table-Generating Functions)
一进多出
如lateral view explore()
4)官方文档地址
https://cwiki.apache.org/confluence/display/Hive/HivePlugins5)编程步骤：
(1)继承org.apache.hadoop.hive.ql.exec.UDF
(2)需要实现evaluate函数；evaluate函数支持重载；
(3)在hive的命令行窗口创建函数
a)添加jar
add jar linux_jar_pathb)创建function
create [temporary] function [dbname.]function_name AS class_name;(4)在hive的命令行窗口删除函数
Drop [temporary] function [if exists] [dbname.]function_name;6)注意事项
(1)UDF必须要有返回类型，可以返回null，但是返回类型不能为void；
自定义UDF函数
1．创建一个Maven工程Hive
2．导入依赖
org.apache.hivehive-exec1.2.13．创建一个类
public class Lower extends UDF {public String evaluate (final String s) {if (s == null) {return null;}return s.toLowerCase();}}4．打成jar包上传到服务器/opt/module/jars/udf.jar
5．将jar包添加到hive的classpath
hive (default)> add jar /opt/module/datas/udf.jar;6．创建临时函数与开发好的java class关联
hive (default)> create temporary function mylower as "com.test.hive.Lower";7．即可在hql中使用自定义的函数
hive (default)> select ename, mylower(ename) lowername from emp;
