【大数据进阶第三阶段之Hive学习笔记】Hive安装-CSDN博客
【大数据进阶第三阶段之Hive学习笔记】Hive常用命令和属性配置-CSDN博客
【大数据进阶第三阶段之Hive学习笔记】Hive基础入门-CSDN博客
【大数据进阶第三阶段之Hive学习笔记】Hive查询、函数、性能优化-CSDN博客
————————————————
1、查询
查询语句语法：
[WITH CommonTableExpression (, CommonTableExpression)*] (Note: Only available
starting with Hive 0.13.0)
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT number]
1.1 基本查询（Select…From）
1.1.1 全表和特定列查询
select * from emp;
select empno, ename from emp;
注意：
（1）SQL 语言大小写不敏感。
（2）SQL 可以写在一行或者多行
（3）关键字不能被缩写也不能分行
（4）各子句一般要分行写。
（5）使用缩进提高语句的可读性。
1.1.2 列别名
1．重命名一个列
2．便于计算
3．紧跟列名，可以在列名和别名之间加入关键字‘AS’
select ename AS name, deptno dn from emp;
1.1.3 算术运算符
1.1.4 常用函数
1．求总数（count）
2．求最大值（max）
3．求最小值（min）
4．求总和（sum）
5．求平均值（avg）
select count(*) cnt from emp;
1.1.5 Limit语句
典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。
select * from emp limit 5;
1.2 Where语句
1．使用WHERE子句，将不满足条件的行过滤掉
2．WHERE子句紧随FROM子句
案例：查询出薪水大于1000的所有员工
select * from emp where sal >1000;
1.2.1 比较运算符（Between/In/ Is Null）
下面表中描述了谓词操作符，这些操作符同样可以用于JOIN…ON和HAVING语句中。
1.2.2 Like和RLike
1）使用LIKE运算选择类似的值
2）选择条件可以包含字符或数字:
% 代表零个或多个字符(任意个字符)。
_ 代表一个字符。
3）RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。
案例：
查找以2开头薪水的员工信息
select * from emp where sal LIKE '2%';
查找第二个数值为2的薪水的员工信息
select * from emp where sal LIKE '_2%';
查找薪水中含有2的员工信息
select * from emp where sal RLIKE '[2]';
1.2.3 逻辑运算符（And/Or/Not）
操作符 含义
AND 逻辑并
OR 逻辑或
NOT 逻辑否
案例：查询薪水大于1000，部门是30
select * from emp where sal>1000 and deptno=30;
1.3 分组
1.3.1 Group By语句
GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。
案例：计算emp表每个部门的平均工资
select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;
1.3.2 Having语句
1．having与where不同点
（1）where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据。
（2）where后面不能写分组函数，而having后面可以使用分组函数。
（3）having只用于group by分组统计语句。
案例：求每个部门的平均薪水大于2000的部门
hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;
1.4 Join语句
1.4.1 等值Join
Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持非等值连接。
select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno;
1.4.2 表的别名
1．好处
（1）使用别名可以简化查询。
（2）使用表名前缀可以提高执行效率。
select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;
1.4.3 内连接
内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。
select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;
1.4.4 左外连接
左外连接：JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。
select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;
1.4.5右外连接
右外连接：JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。
select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;
1.4.6 满外连接
满外连接：将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。
select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;
1.4.7 笛卡尔积
1．笛卡尔集会在下面条件下产生
（1）省略连接条件
（2）连接条件无效
（3）所有表中的所有行互相连接
1.4.8 连接谓词中不支持or
select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno or e.ename=d.ename; 错误的
1.5 排序
1.5.1 全局排序（Order By）
Order By：全局排序，一个Reducer
1．使用 ORDER BY 子句排序
ASC（ascend）: 升序（默认）
DESC（descend）: 降序
2．ORDER BY 子句在SELECT语句的结尾
3．案例实操 ：查询员工信息按工资升序排列
select * from emp order by sal;
1.5.2 每个MapReduce内部排序（Sort By）
Sort By：每个Reducer内部进行排序，对全局结果集来说不是排序。丛林
1．设置reduce个数
set mapreduce.job.reduces=3;
2．查看设置reduce个数
set mapreduce.job.reduces;
3．根据部门编号降序查看员工信息
select *from dept_partition sort by deptno;
对于全局结果来说并没有排序，只是对每个reduce的结果进行了排序。
1.5.3 分区排序（Distribute By）
Distribute By：类似MR中partition，进行分区，结合sort by使用。
注意，Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。
对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。
select *from dept_partition distribute by deptno sort by month;
insert overwrite local directory '/opt/datas/dept3' select *from dept_partition distribute by deptno sort by month;
1.5.4 Cluster By
当distribute by和sorts by字段相同时，可以使用cluster by方式。
cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。
以下两种方法等价：
select *from dept_partition distribute by deptno sort by deptno;
select *from dept_partition cluster by deptno;
注意：按照部门编号分区，不一定就是固定死的数值，可以是20号和30号部门分到一个分区里面去。
1.6 分桶（buckets）及抽样查询
1.6.1 分桶表数据存储
分区针对的是数据的存储路径；分桶针对的是数据文件。
分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区，特别是之前所提到过的要确定合适的划分大小这个疑虑。
分桶是将数据集分解成更容易管理的若干部分的另一个技术。
设置分捅属性
set hive.enforce.bucketing=true;
创建分桶表
create table stu_buck(id int, name string)
clustered by(id)
into 4 buckets
row format delimited fields terminated by '\t';
导入数据到分桶表，通过子查询的方式
insert into table stu_buck select id, name from stu;
分捅表只能通过insert插入数据，load读取数据是无效的。
1.6.2 分桶抽样查询
对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive可以通过对表进行抽样来满足这个需求。
select * from customers_buck1 tablesample(bucket 1 out of 4 on customer_fname);
注：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_buck
表总共有：
分捅相当于预览了部分数据。
1.7 其他常用查询函数
1.7.1 空字段赋值
1.函数说明
NVL：给值为NULL的数据赋值，它的格式是NVL( string1, replace_with)。它的功能是如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值，如果两个参数都为NULL ，则返回NULL。
c表数据：
select c1,nvl(c2,1) from c;
1.7.2 CASE WHEN
数据准备
2．需求
求出不同部门男女各多少人。结果如下：
A 2 1
B 1 2
3．按需求查询数据
select dept_id,
sum(case when sex='男' then 1 else 0 end) man,
sum(case when sex='女' then 1 else 0 end) woman
from emp_sex group by dept_id;
1.7.3 行转列
1．相关函数说明
CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;
CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。
2．数据准备
3．需求
把星座和血型一样的人归类到一起。结果如下：
射手座,A 大海|凤姐
白羊座,A 孙悟空|猪八戒
白羊座,B 宋宋
4．按需求查询数据
select
t1.base,
concat_ws('|', collect_set(t1.name)) name
from
(select
name,
concat(constellation, ",", blood_type) base
from
person_info) t1
group by
t1.base;
1.7.4 列转行
1．函数说明
EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。
LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。
2．数据准备
3．需求
将电影分类中的数组数据展开。结果如下：
《疑犯追踪》 悬疑
《疑犯追踪》 动作
《疑犯追踪》 科幻
《疑犯追踪》 剧情
《Lie to me》 悬疑
《Lie to me》 警匪
《Lie to me》 动作
《Lie to me》 心理
《Lie to me》 剧情
《战狼2》 战争
《战狼2》 动作
《战狼2》 灾难
4．按需求查询数据
select movie,category_name from movie_info
lateral view explode(category) table_tmp as category_name;
1.7.5 窗口函数
1．相关函数说明
OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化
CURRENT ROW：当前行
n PRECEDING：往前n行数据
n FOLLOWING：往后n行数据
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点
LAG(col,n)：往前第n行数据
LEAD(col,n)：往后第n行数据
NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。
2．数据准备：name，orderdate，cost
jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94
3．需求
（1）查询在2017年4月份购买过的顾客及总人数
（2）查询顾客的购买明细及月购买总额
（3）上述的场景,要将cost按照日期进行累加
（4）查询顾客上次的购买时间
（5）查询前20%时间的订单信息
4．按需求查询数据
（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;
（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;
1.7.6 Rank
1．函数说明
RANK() 排序相同时会重复，总数不会变
DENSE_RANK() 排序相同时会重复，总数会减少
ROW_NUMBER() 会根据顺序计算
2．数据准备
3．需求
计算每门学科成绩排名。
4．按需求查询数据
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) rmp
from score;
2、函数
2.1Hive函数分类
- 从输入输出角度分类
标准函数：一行数据中的一列或多列为输入，结果为单一值
聚合函数：多行的零列到多列为输入，结果为单一值
表生成函数：零个或多个输入，结果为多列或多行
- 从实现方式分类
内置函数
自定义函数:
UDF：自定义标准函数
UDAF：自定义聚合函数
UDTF：自定义表生成函数
2.2内置函数
Hive提供大量内置函数供开发者使用
- 标准函数
- 字符函数
- 类型转换函数
- 数学函数
- 日期函数
- 集合函数
- 条件函数
- 聚合函数
- 表生成函数
2.2.1字符函数
2.2.2 类型转换函数和数学函数
2.2.3 日期函数
7.2.4 集合函数
7.2.5 条件函数
2.2.6 聚合函数和表生成函数
- 聚合函数
count、sum、max、min、avg、var_samp等
- 表生成函数：输出可以作为表使用
2.3 自定义UDF函数
Hive UDF开发流程
- 继承UDF类或GenericUDF类
- 重写evaluate()方法并实现函数逻辑
- 编译打包为jar文件
- 复制到正确的HDFS路径
- 使用jar创建临时/永久函数
- 调用函数
1．创建一个Maven工程Hive
2．导入依赖
org.apache.hadoop hadoop-common
2.6.0 org.apache.hive hive-exec
1.2.1 3．创建一个类
package cn.kgc.kb09;
import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;
public class TestUDF extends UDF {
public Text evaluate(Text str){
if(null==str){
return null;
}
return new Text(str.toString().toUpperCase());
}
public static void main(String[] args) {
TestUDF tu=new TestUDF();
Text rst = tu.evaluate(new Text());
System.out.println(rst);
}
}
方法一(创建临时函数)：
4．打成jar包上传到服务器/opt/testudf.jar
5．将jar包添加到hive的classpath（linux路径）
add jar /opt/testudf.jar;
6．创建临时函数与开发好的java class关联
create temporary function mylower as "cn.kgc.kb09.TestUDF";
方法二（创建永久函数）：
4.在linux命令行使用hdfs命令把jar包上传到hdfs的路径
hdfs dfs -put 路径
create function 函数名 as ‘方法的全路径’using as ‘jar包的hdfs路径’
7．即可在hql中使用自定义的函数
select ename, mylower(ename) lowername from emp;
注意:
报错在linux执行
报错
java.sql.SQLException: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.FunctionTask
at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:294)
at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:291)
at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:291)
at org.apache.zeppelin.jdbc.JDBCInterpreter.executeSql(JDBCInterpreter.java:736)
at org.apache.zeppelin.jdbc.JDBCInterpreter.interpret(JDBCInterpreter.java:819)
at org.apache.zeppelin.interpreter.LazyOpenInterpreter.interpret(LazyOpenInterpreter.java:103)
at org.apache.zeppelin.interpreter.remote.RemoteInterpreterServer$InterpretJob.jobRun(RemoteInterpreterServer.java:632)
at org.apache.zeppelin.scheduler.Job.run(Job.java:188)
at org.apache.zeppelin.scheduler.ParallelScheduler$JobRunner.run(ParallelScheduler.java:162)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
执行命令，不行就退出hive，再次执行
zip -d testUdf.jar 'META-INF/.SF' 'META-INF/.RSA' 'META-INF/*SF'
临时函数可以垮库运行，永久函数需要库名.函数名
3、性能优化
3.1 Hive事务
1.事务（Transaction ）指一组单元化操作，这些操作要么都执行，要么都不执行
ACID特性：
- Atomicity：原子性
- Consistency：一致性
- Isolation：隔离性
- Durability：持久性
2.Hive事务的特点和局限
V0.14版本开始支持行级事务
- 支持INSERT、DELETE、UPDATE(v2.2.0开始支持Merge)
- 文件格式只支持ORC
局限
- 表必须是bucketed表
- 需要消耗额外的时间、资源和空间
- 不支持开始、提交、回滚、桶或分区列上的更新
- 锁可以为共享锁或排它锁(串联的而不是并发)
- 不允许从一个非ACID连接读写ACID表
- 使用较少
3. Hive事务的开启和设置
- 通过Hive命令行方式设置,当前session有效
- 通过配置文件设置，全局有效
- 通过UI工具（如Ambari)设置
-- 通过命令行方式开启事务
set hive.support.concurrency = true;
set hive.enforce.bucketing = true;
set hive.exec.dynamic.partition.mode = nonstrict;
set hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
set hive.compactor.initiator.on = true;
set hive.compactor.worker.threads = 1;
-- 通过配置文件hive-site.xml
hive.support.concurrency true hive.txn.manager org.apache.hadoop.hive.ql.lockmgr.DbTxnManager 3.2 Hive PLSQL
Hive PLSQL：Hive存储过程（v2.0之后）
- 支持SparkSQL和Impala
- 兼容Oracle、DB2、MySQL、TSQL标准
- 使将现有的过程迁移到Hive变得简单和高效
- 使编写UDF不需要Java技能
- 它的性能比Java UDF稍微慢一些
- 功能较新
在Hive2 bin目录下运行./hplsql
./hplsql -f plsql_demo.pl
RETURNS STRING
BEGIN RETURN 'Hello, ' || text || '!';
END;
Print hello(' word')
CREATE PROCEDURE getCount()
BEGIN DECLARE cnt INT = 0;
SELECT COUNT(*) INTO cnt FROM employee;
PRINT 'Users cnt: ' || cnt;
END;
call getCount();
3.3 Hive性能调优工具
1.EXPLAIN
2.ANALYZE
- ANALYZE：分析表数据，用于执行计划选择的参考
收集表的统计信息，如行数、最大值等
使用时调用该信息加速查询
- 语法
ANALYZE TABLE employee COMPUTE STATISTICS;
ANALYZE TABLE employee_partitioned
PARTITION(year=2014, month=12) COMPUTE STATISTICS;
ANALYZE TABLE employee_id COMPUTE STATISTICS
FOR COLUMNS employee_id;
3.4 Hive优化设计
- 使用分区表、桶表
- 使用索引
- 使用适当的文件格式，如orc, avro, parquet
- 使用适当的压缩格式，如snappy
- 考虑数据本地化 - 增加一些副本
- 避免小文件
- 使用Tez引擎代替MapReduce
- 使用Hive LLAP(在内存中读取缓存)
- 考虑在不需要时关闭并发
3.5 Job优化
1.本地模式运行
Hive支持将作业自动转换为本地模式运行
当要处理的数据很小时，完全分布式模式的启动时间比作业处理时间要长
-- 通过以下设置开启本地模式
SET hive.exec.mode.local.auto=true; --default false
SET hive.exec.mode.local.auto.inputbytes.max=50000000;
SET hive.exec.mode.local.auto.input.files.max=5; --default 4
Job必须满足以下条件才能在本地模式下运行
Job总输入大小小于 hive.exec.mode.local.auto. inputbytes.max
map任务总数小于 hive.exec.mode.local.auto. input.files.max
所需的Reduce任务总数为1或0
2.JVM重用（JVM Reuse）
通过JVM重用减少JVM启动的消耗
默认每个Map或Reduce启动一个新的JVM
Map或Reduce运行时间很短时，JVM启动过程占很大开销
通过共享JVM来重用JVM，以串行方式运行MapReduce Job
适用于同一个Job中的Map或Reduce任务
对于不同Job的任务，总是在独立的JVM中运行
-- 通过以下设置开启JVM重用
set mapred.job.reuse.jvm.num.tasks = 5; -- 默认值为1
3.并行执行
并行执行可提高集群利用率
Hive查询通常被转换成许多按默认顺序执行的阶段
这些阶段并不总是相互依赖的
它们可以并行运行以节省总体作业运行时间
如果集群的利用率已经很高，并行执行帮助不大
-- 通过以下设置开启并行执行
SET hive.exec.parallel=true; -- default false
SET hive.exec.parallel.thread.number=16; -- default 8,定义并行运行的最大数量
3.6 查询优化
自动启动Map端Join
防止数据倾斜
set hive.optimize.skewjoin=true;
启用CBO(Cost based Optimizer)
set hive.cbo.enable=true;
set hive.compute.query.using.stats=true;
set hive.stats.fetch.column.stats=true;
set hive.stats.fetch.partition.stats=true;
启动Vectorization（矢量化）
set hive.vectorized.execution.enabled = true;
set hive.vectorized.execution.reduce.enabled = true;
使用CTE、临时表、窗口函数等正确的编码约定
3.7 压缩算法
- 减少传输数据量，会极大提升MapReduce性能
- 采用数据压缩是减少数据量的很好的方式
- 常用压缩方法对比
- 语法
- ANALYZE：分析表数据，用于执行计划选择的参考
猜你喜欢
网友评论
- 搜索
- 最新文章
- 热门文章