hive进阶笔记以及遇到的问题

hive进阶
简介
一、本章目标
1、hive的数据导入
1）load命令
2）Sqoop组件
2、hive的数据查询HQL
3、hive的java客户端和自定义函数
二、学习必备基础
1、hive的体系结构和基本操作
2、java编程
3、linux基本操作
2-1使用load语句进行数据导入

• 使用load语句
  语法：
  LOAD DATA ［LOCAL］ INPATH 'filepath' [OVERWRITE]
  INTO tablename ［PATHITION (partcol1=val1,partcil2 …)]
  例:
  将test1.txt数据导入t1
  load data local inpath '/root/text1.txt' into table t1;
  如果分隔符设置的不一致则会出现全部为null
• 一次导入多个文件
  将／root/data目录下的所有的text数据文件导入到t1中，并且覆盖原来的数据
  load data local inpath '/root/data' overwrite into table t1;
• 将HDFS中，／input/text1.txt 导入t3
  load data inpath '/input/text1.txt' overwrite into table t1;
  2-2使用Sqoop实现数据的导入
  1、首先下载tar包，解压
  2、添加两个环境变量
  export HADOOP_COMMON_HOME=$HADOOP_HOME/
  export HADOOP_MAPRED_HOME=$HADOOP_HOME/
  使用sqoop导入Oracle数据导入到HDFS中
  ./sqoop import –connect jdbc:oracle:thin:@172.26.253.3:3306:orcl --username scottt --password tiger --table emp --columns 'empno,ename,job,sal,deptno' -m 1 --target-dir '/sqoop/emp'

./sqoop import --connect jdbc:mysql://172.26.253.3:3306/wmy --username root --password platform --table test --columns 'sid,sname,age' -m 1 --target-dir '/sqoop/test'

sqoop import --connect jdbc:mysql://192.168.1.10:3306/itcast --username root --password 123  --table trade_detail --columns 'id, account, income, expenses'

使用sqoop导入Oracle数据导入到Hive中
./sqoop import --hive-import --connect jdbc:mysql://172.26.253.3:3306/wmy --username root --password platform --table test --columns 'sid,sname,age' -m 1

使用sqoop导入Oracle数据导入到hive中，并指定表名
./sqoop import --hive-import --connect jdbc:mysql://172.26.253.3:3306/wmy --username root --password platform --table test --columns 'sid,sname,age' -m 1 –hive-table testh
使用sqoop导入Oracle数据导入到hive中，并使用where条件
./sqoop import --hive-import --connect jdbc:mysql://172.26.253.3:3306/wmy --username root --password platform --table test --columns 'sid,sname,age' -m 1 –hive-table testh --where 'sid=1'
使用sqoop导入Oracle数据导入到hive中，并使用查询语句
./sqoop import --hive-import --connect jdbc:mysql://172.26.253.3:3306/wmy --username root --password platform -m 1 --query 'select *from test where age<20 and $CONDITIONS' --target-dir '/sqoop/test1' --hive-table testh
使用sqoop将hive的数据导出到Oracle中
./sqoop export --connect jdbc:mysql://172.26.253.3:3306/wmy --username root --password platform -m 1 --table myemp --export-dir *****
hive的数据查询
。简单查询
。过滤和排序］
。hive的函数
查询的语法：
SELECT ［ALL｜DISTINCT］select_expr,select_expr,...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[CLUSTER BY col_list
[DISTRIBUTE BY col_list] [SORY BY col_list]
[ORDER BY col_list] ]
[LIMIT number]

例：
查询所有员工的所有信息：
select ＊ from emp；
查询员工信息：员工号，姓名，月薪
select empno，ename，sal from emp；
查询员工信息：员工号，姓名，月薪，年薪
select empno，ename，sal ，sal＊12 from emp；
查询员工信息：员工号，姓名，月薪，年薪，奖金，年收入
select empno，ename，sal ，sal＊12，comm，sal＊12+nvl（comm，0） from emp；
查询奖金为null的员工：
select ＊ from emp where comm=null；错误
select ＊ from emp where comm is(not) null；正确
使用distinct来去掉重复记录
select distinct deptno，job from emp；
简单查询的Fetch Task功能(执行简单的功能，没有函数没有排序的时候不会生成mapreduce作业，而直接使用fetch task直接从hdfs中查询输出，这样简单查询的效率会更高）
配置方式
。set hive.fetch.task.conversion=more;
. hive --hiveconf hive.fetch.task.conversion=more(启动时进行配置）
. 修改hive-site.xml文件
在属性里加上配置的属性

<configuration>
<property>
<name>hive.fetch.task.conversion</name>
<value>more</value>
</property>
在查询中使用过滤
查询的语法：
SELECT ［ALL｜DISTINCT］select_expr,select_expr,...
FROM table_reference
[WHERE where_condition]（过滤）
[GROUP BY col_list]
[CLUSTER BY col_list
[DISTRIBUTE BY col_list] [SORY BY col_list]
[ORDER BY col_list] ]
[LIMIT number]
（ DISTRIBUTE BY 指定多分发器，（partitioner），多Reducer可用）
--查询10号部门的员工
select ＊ from emp where deptno=10;
--查询名叫king的员工
select ＊ from emp where ename='king';(字符串加“'”，区分大小写)。
--查询部门号是10，薪水小于2000的员工（and）（或or）
select ＊ from emp where deptn=10 and sal<2000;
查看执行计划：
explain select ＊ from emp where deptn=10 and sal<2000;
---模糊查询：查询名字以s打头的员工
select ＊ from emp where ename like 's%';（表示一个字符。%任意字符）
---模糊查询：查询名字中含有下划线的员工
select ＊ from emp where ename like '%%';（查询出所有的记录，因为下划线代表任意一个字符）
使用转译字符;
select ＊ from emp where ename like '%\_%';
3-3在查询中使用排序
orderby语句排序最后结果
--查询员工信息，并且按照月薪排序（默认是升序）；
select from emp order by sal;
select from emp order by sal desc ;(降序）
orderby 后边可以跟：列名，表达式，别名，序号；
使用序号要进行修改
set hive.groupby.orderby.position.alias=true;
select empno ,ename,sal,sal12 from emp order by 4;
--查询员工信息，按照奖金排序------》null的排序
select from emp order by comm;（升序null在最前边）。
select * from emp order by comm desc ; （null在最后）。
hive的函数
4-1 数学函数
hive的内嵌函数

。数学函数
--round
--ceil
--floor
--四舍五入round
select round(45.987 ,2);对45.987进行四舍五入保留两位小数；
select round(45.987 ,-2);
---向上取整ceil
select ceil(45.987 )结果46
---向下取整floor
select floor(45.987)结果45
4-2字符函数
。字符函数
--lower转为小写
--upper转为大写
select lower('Hello World),upper('Hello World);
--length求字符串的长度(字符数）
select length('hello world),length('你好');
11 2(4个字节，两个字符）
--concat添加字符串
select cconcat('hello','world');
--substr求字符串的字串
.substr(a,b);从a中，第b位开始取，取右边的所有的字符
select substr('hello world',3);--llo world
.substr(a,b,c);从a中，第b位开始取，取右边的c个字符
select substr('hello world',3，4);--llo 结果，有个空格

--trim去掉前后的空格
--lpad左填充
--rpad右填充
select lpad('abcd',10,''),rpad('abcd',10,'');
扩展到10 位，用＊进行填充
4-3收集函数和转换函数
。收集函数
--size
size (map(<key,value>,<key,value>.....);
select size(map(1.'top',2,'df');--2

。转换函数
--cast数据格式转换
select cast(1 as bigint);--1
select cast(1 as float);--1.0
select cast ('2015-04-10' as date);将字符串转换为date
4-4日期函数
。日期函数
--to_date取出一个字符串中日期的部分
select to_date('2015-04-10 11:23:22');-->2015-04-10
--year取出日期中的年
--month取出月
--day取出日
select year('2015-04-10 11:23:22'),month('2015-04-10 11:23:22'),day('2015-04-10 11:23:22');-->2015 04 10
--weekofyear返回一个日期在一年中是第几个星期
select weekofyear('2015-04-10 11:23:22');-->15
--datediff两个日期相减返回日期相差的天数
select datediff('2015-04-10 11:23:22','2014-04-10 11:23:22');--->365
--date_add在一个日期的基础上加上多少天
--date_sub在一个日期上减去多少天
–-----后天和前天
select date_add('2015-04-10 11:23:22',2),date_sub('2015-04-10 11:23:22',2);
----》2015-04-12 2015-04-08
4-5条件函数
。条件函数
--coalesce：从左到右返回第一个不是null的值
select comm,sal,coalesce(comm,sal) from emp;
--->结果：
NULL 800.00 800.00
300.00 500.00 500.00

---case…when…：条件表达式
CASE a WHEN b THEN c ［WHEN d THEN e］＊［ELSE f］ END;
–----按照员工的职位涨工资，总裁张1000，经理800，其他400
select ename,job,sal,
case job when 'PRESIDENT' then sal+1000
when 'MANAGER' thensal+800
else sal+400
end
from emp;(格式方便看，执行时放到一行）
---》结果
smith CLERK 800.00 1200.00
JONES MANAGER 2890.00 3690.00
KING PRISIDENT 5000.00 6000.00
4-6聚合函数和表生成函数
。表生成函数
--explode
把一个map集合或者是一个数组中的每个元素单独生成一个行
select explode(map(1,'dfgd',2,'dfg',3,'weww'));

。聚合函数
--count
--sum
--min
--max
--avg
--查询员工的人数，总额，最大值，最小值，平均值
select count(*),sum(sal),min(sal),max(sal),avg(sal);
结果:
OK
1 dfgd
2 dfg
3 weww

hive的表连接
。等值连接
。不等值连接
。外连接
。自连接
三张表部门表
desc dept
deptno double
dname string
loc string
员工信息表
desc emp；
empno double
ename string
job string
mgr double／／老板的员工号
hiredate string
sal double
comm double
deptno double
员工薪水级别
desc salgrade;
grade double
losal double
hisal double
5-1等值和不等值连接
.等值连接：
--查询员工信息：员工号，姓名，月薪，部门名称
select e.empno,e.ename,e.sal,d.dname
from emp e,dept d
where e.deptno=d.deptno;
.不等值连接
--查询员工信息，员工号，姓名，月薪，工资级别
select e.empno,e.name,e.sal,s.grade
from emp e,salgrade s
where e.sal between losal and hisal;
5-2外连接
。外连接
--按部门统计员工人数：部门号，部门名称，人数
select d.detpno,d.dname,count(e.empno)
from emp e,deptd
where e.deptno=d.deptno
group by d.deptno,d.name;
所有没有出现在聚合函数的列都要出现在groupby的后边
有缺陷，员工表里边可能没有出现全部的部门等值链接的=等不起来
这里要用到外链接
。通过外连接可以将对于连接条件不成立的记录仍然包含在最后的结果中
--右外连接（当连接条件不成立的时候，连接条件左边的表依然可以出现在结果中）
--左外连接（当连接条件不成立的时候，链接条件左边的表依然可以出现在结果中）
---正确的方法：
select d.detpno,d.dname,count(e.empno)
from emp e,deptd
where emp e right join dept d
on (e.deptno=d.deptno)
group by d.deptno,d.name;
5-3自连接
。自连接
--查询员工的姓名和员工的老板的姓名
.自连接的核心：通过表的别名将同一张表视为多张表
select e.ename,b,ename
from emp e,emp b
where e.mgr=b.empno;
hive中的子查询
。hive只支持：from和where子句中的子查询
--某个部门的员工姓名
select e.name from emp e where e.deptno in (select d.deptno from dept d where d.name='SALES' or d.name='ACCOUNTING');
。注意问题：
--语法中的括号
--合理的书写风格
--hive只支持：from和where子句中的子查询
--主查询和子查询可以不是同一张表，只要子查询返回的结果主查询可以使用就可以
--子查询中的空值问题（如果子查询的返回值中有空值，不可以使用not in但是可以使用in）
--查询不是老板的员工
select from emp e where e.empno not in (select mgr from emp);
有空值不行，修改：
select from emp e where e.empno not in (select mgr from emp where mgr is not null);
第7章 hive的客户端操作
7-1hive的JDBC客户端操作上
。启动hive远程服务
-#hive --service hiveserver2 –hiveconf hive.server2.thrift.port=10000开启远程
编写一个工具类连接hive，访问hive的资源。见windows的workspacehiveJDNC，这个整了三天，中了几次hadoop和hive最后发现都不是这上边问题，想偷懒搞几
个jar进去结果发现不行，最后还是把所有的jar都放进去了，那个hadoop的commonjar在share文件夹里边。不在lib里。最后用的hive的server不行还要用hiveserver2.结果还有log4j的警告，查了半天结果就是忽略不管他了。还有就是在建表的时候报了个什么错，好像是没有权限，我就给hadoop的用户付了个权限最后也成功。
访问方式
。JDBC
。步骤

。Thrift Client
7-2hive的JDBC客户端操作下

7-3hive的thrift客户端操作
在程序中没有hiveclient用的thrifthive.client代替的，可是还是有问题，用jdbc可以连接，但是用这个到execute就运行不动了
第8章开发hive的自定义函数
8-1hive中的自定义函数简介
--hive自定义函数的实现细节
。hive的自定义函数（UDF）：User Defined Function
。可以直接用于select语句，对查询结果做格式化吃力后，在输出内容
。自定义UDF需要继承org.apache.hadoop.hive.ql.UDF
。需要实现evaluate函数，evaluate函数支持重载
--hive自定义函数的部署运行
。把程序打包放到目标机器上去
。进入hive客户端，添加jar包
---- hive>add jar /root/training/udfjar/udf/udf_test.jar(上传jar的路径）
。创建临时函数：
--hive>CREATE TEMPORARY FUNCTION <函数名>
AS 'java类名';
–-hive自定义函数的使用
.select <函数名> from table;
.销毁创建的临时函数
--hive >DROP TEMPORARY FUNCTION <函数名>;

8-2hive中的自定义函数案例
--拼加两个字符串
首先编写java程序打包上传，。
。add jar /home/hadoop/study/udf/ConcatString.har;
.CREATE TEMPORARY FUNCTION myconcat
AS 'demo.udf.ConcatString';
.select myconcat('hello','world');
结果：hello**world
第9章课程总结
hive数据仓库用来支持OLAP（联机分析处理）的应用 ，hive数据仓库建构在hadoop集群之上，数据存在hdfs文件系统中，hive中执行的操作会装换成mapreduce作业进行执行，hive支持类似SQL的语言HQL，hive采用元数据对表进行管理，元数据有三种存放模式：嵌入模式，远程模式，本地模式；hive提供了强大的编程接口，：jdbc和客户端，hive最强大的功能在于可以使用变成接口创建自定义函数来完成复杂的业务逻辑