Hive进阶，

和通数据库htsjk.Com2019-10-11 02:49 来源:未知阅读:7634 评论 329 热度5

标签：

Hive进阶，

一、Hive数据的导入
1.使用Load语句执行数据导入

语法：

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE]
INTO TABLE tablename [PARTITION (partcol1=val1,partcol2=val2...)]

local—数据文件在操作系统(Linux)上，若数据文件在HDFS上则省略；overwrite—覆盖原表数据

#将student01.txt数据导入t3 load data local inpath '/root/data/student01.txt' into table t3;
#将/root/data下的所有数据文件导入t3表中，并且覆盖原来的数据
load data local inpath '/root/data/' overwrite into table t3;

#将HDFS中，/input/student01.txt 导入到t3 load data inpath '/input/student01.txt' overwrite into table t3;
#将数据导入分区表 load data local inpath '/root/data/data1.txt' into table partition_table partition (gender='M');
2.使用Sqoop实现数据导入

安装sqoop组件

#下载解压sqoop安装包 tar -zxvf sqoop-1.4.5.bin_hadoop-0.23.tar.gz #配置环境变量 export HADOOP_COMMON_HOME=~/training/hadoop-2.4.1/ export HADOOP_MAPRED_HOME=~/training/hadoop-2.4.1/

使用Sqoop导数据

#Sqoop基于JDBC，需上传JDBC驱动jar包到sqoop内部的lib下

# Oracle —> HDFS
#1521是默认端口；orcl是所选数据库；--属性；-m 线程数   ./sqoop import --connect jdbc:oracle:thin:@192.168.56.101:1521:orcl --username scott --password tiger --table emp --columns 'empno,ename,job,sal,deptno' -m 1 --target-dir '/sqoop/emp'
# Oracle —> Hive
  ./sqoop import --connect jdbc:oracle:thin:@192.168.56.101:1521:orcl --username scott --password tiger --table emp -m 1 --columns 'empno,ename,job,sal,deptno'

# Oracle —> Hive，并指定表名
  ./sqoop import --connect jdbc:oracle:thin:@192.168.56.101:1521:orcl --username scott --password tiger --table emp -m 1 --columns 'empno,ename,job,sal,deptno' --hive-table emp1
# Oracle —> Hive，并使用where条件
  ./sqoop import --connect jdbc:oracle:thin:@192.168.56.101:1521:orcl --username scott --password tiger --table emp -m 1 --columns 'empno,ename,job,sal,deptno' --hive-table emp2 --where 'deptno=10'

# Oracle —> Hive，并使用查询语句
#必须在query里写$CONDITIONS;同时还需指明保存的hdfs路径   ./sqoop import --connect jdbc:oracle:thin:@192.168.56.101:1521:orcl --username scott --password tiger -m 1 --query 'select * from emp where sal<2000 and $conditions' --target-dir '/sqoop/emp5' --hive-table emp5
# Hive —> Oracle #首先在Oracle中创建MYEMP且列的数目和类型要与hdfs中的文件保持一致
  ./sqoop export --connect jdbc:oracle:thin:@192.168.56.101:1521:orcl --username scott --password tiger -m 1 --table MYEMP --export-dir '/hdfspath'

二、Hive的数据查询 1.简单查询和fetch task

查询的语法：

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] [SORT BY col_list] | [ORDER BY col_list] ] [LIMIT number] #DISTRIBUTE BY 指定分发器(Partitioner)，多Reducer可用

简单查询：

#查询所有员工的所有信息 select * from emp;
#查询员工信息：员工号、姓名、月薪 select empno,ename,sal from emp;
#查询员工信息：员工号、姓名、月薪、年薪
select empno,ename,sal,sal*12 from emp;
#查询员工信息：员工号、姓名、月薪、年薪、奖金、年收入
#Hive内置函数 nvl(col，0) 将null转换成 0 select empno,ename,sal,sal*12,comm,sal*12+nvl(comm,0) from emp;
#查询奖金为null的员工 select * from emp where comm is null;
#使用distinct来去掉重复记录 select distinct depno from emp;

Fetch Task功能：执行简单(无函数运算)查询，不会开启MapReduce作业

#配置方式 -set hive.fetch.task.conversion=more; -hive --hiveconf hive.fetch.task.conversion=more #以上两种只在当前会话有效，若退出连接重新登入hive的命令行，则默认开启MapReduce作业 -修改hive-site.xml文件 #永久开启Fetch Task功能
2.在查询中使用过滤 #查询10号部门的员工
select * from emp where deptno=10;

#查询名叫KING的员工
select * from emp where ename='KING';
#查询10号部门，月薪小于2000的员工
select * from emp where deptno=10 and sal<2000;
#模糊查询：查询名字以S打头的员工。'%'代表任意字符串;'_'代表任意的一个字符
select empno,ename,sal from emp where ename like 'S%';
#模糊查询：查询名字含有'_'下划线的员工。取下划线本身的含义，需要转义字符 \\_
select empno,ename,sal from emp where ename like '%\\_%';

3.在查询中使用排序
#查询员工信息：员工号姓名月薪按照月薪排序
#order by 后面跟：列，表达式，别名，序号;desc 降序
select empno,ename,sal from emp order by sal desc;#列名，降序 select empno,ename,sal,sal*12 annsal from emp order by sal*12;#表达式
select empno,ename,sal,sal*12 annsal from emp order by annsal;#别名
#使用序号排序需修改参数 set hive.groupby.orderby.position.alias=true; select empno,ename,sal,sal*12 annsal from emp order by 4;#序号

#数据中含有 null 的排序：升序在最前面，降序在最后面实际查询中，习惯将null转换成0，排在最后面
三、Hive的内置函数
1.Hive数学函数
#四舍五入,round(a,b) b--（2保留两位小数，1保留一位小数，0保留到个位，-1保留到十位，-2保留到百位） select round(45.926,2),round(45.926,1),round(45.926,0),round(45.926,-1),round(45.926,-2);

#向上取整
select ceil(45.9);

#向下取整
select floor(45.9);
2.Hive字符函数
- lower 将字符串转成小写 - upper 将字符串转成大写 select lower('Hello World'),upper('Hello World'); #hello world HELLO WORLD
- length 字符串的长度，字符数非字节数 select length('Hello World'),length('你好')； #11 2
- concat 拼接字符串 select concat('Hello',' World'); #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,'*'); #******abcd abcd******
3.收集函数和转换函数

收集函数

- size 收集map函数的长度 size(map(<key,value>,<key,value>)) select size(map(<1,'Tom'>,<2,'Mary'>)); #2

转换函数

- cast 数据类型的转换 select cast(1 as bigint); #1 select cast(1 as float);    #1.0 select cast('2015-04-10' as date); #2015-04-10
4.Hive日期函数
- to_date 取出字符串中日期的部分 select to_date('2015-04-23 11:23:11'); #2015-04-23
- year 取出日期中的年 - month 取出日期中的月 - day 取出日期中的日 select year('2015-04-23 11:23:11'),month('2015-04-23 11:23:11'),day('2015-04-23 11:23:11'); #2015 4 23
- weekofyear 返回一个日期在一年中是第几个星期 select weekofyear('2015-04-23 11:23:11'); #17
- datediff 返回两个日期相差的天数 select datediff('2015-04-23 11:23:11','2014-04-23 11:23:11'); #365
- data_add 在一个日期上加多少天 - date_sub 在一个日期上减多少天 select date_add('2015-04-23 11:23:11',2),date_sub('2015-04-23 11:23:11',2);    #2015-04-25 2015-04-21
5.Hive条件函数
- coalesce:从左到右返回第一个不为null的值 select comm,sal,coalesce(comm,sal) from emp; #NULL 800.0 800.0 300.0 1600.0 300.0
- 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 'CEO' then sal+1000
when 'Manager' then sal+800 else sal+400 end from emp; # SMITH clerk 800.0 1200.0 ALLEN salesman 1600.0 2000.0 CZONE CEO 5000.0 6000.0 SCOTT Manager 3200.0 4000.0
6.聚合函数
- count：统计个数 - sum：求和 - min：求最小值 - max：求最大值 - avg：求平均值    select count(*),sum(sal),max(sal),min(sal),avg(sal) from emp;
7.表生成函数
- explode：将一个map集合或数组中的每个元素单独生成一个行
   select explode(map(1,'Tom',2'Mary',3,'Mike'));
#1 Tom 2 Mary 3 Mike
四、Hive的表连接
#把表连接操作转换成MapReduce作业提交到Hadoop上运行 1.等值连接： - 查询员工信息：员工号，姓名，月薪，部门名称 select e.empno,e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno;
2.不等值连接(between 下限 and 上限) - 查询员工信息：员工号，姓名，月薪，工资级别
select e.empno,e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;
3.外连接 - 按部门统计员工人数：部门号，部门名称，人数 select d.deptno,d.name,count(e.empno) from emp e,dept d
where e.deptno=d.deptno group by d.deptno,d.name; #所有不在聚合函数中的列都要group by

通过外连接可以将对于连接条件不成立的记录任然包含在最后的结果中。

- 左外连接：当连接条件不成立的时候，连接条件左边的表依然包含在结果中 - 右外连接：当连接条件不成立的时候，连接条件右边的表依然包含在结果中 select d.deptno,d.name,count(e.empno) from emp e right outer join dept d
on (e.deptno=d.deptno) group by d.deptno,d.name;
4.自连接 - 查询员工的姓名和员工的老板姓名
select e.ename,b.ename from emp e,emp b
where e.mgr=b.empno;

自连接的核心：通过表的别名将同一张表视为多张

五、Hive的子查询 #查询SALES、ACCOUNTING部门的员工姓名 select e.ename from emp e where e.deptno in (select d.deptno from dept d where d.dname='SALES' or d.dname='ACCOUNTING');

注意的问题：

- 语法中的括号 - 合理的书写风格 - Hive只支持from和where子句中的子查询 - 主查询和子查询可以不是同一张表，只要主查询可以使用子查询返回的结果 - 子查询中的空值问题 #查询自己不是领导的员工 select * from emp e where e.empno not in (select e1.mgr from emp e1 where e1.mgr is not null);

六、Hive的客户端操作

启动Hive远程服务

- #hive --service hiveserver
1.Hive的JDBC客户端操作

步骤：

获取链接—>创建运行环境—>执行HQL—>处理结果—>释放资源

添加依赖：

<dependency> <groupId>org.apache.hadoop</groupId> <artifactId>hadoop-hdfs</artifactId> <version>2.6.4</version> </dependency> <dependency> <groupId>org.apache.hadoop</groupId> <artifactId>hadoop-common</artifactId> <version>2.6.4</version> </dependency> <dependency> <groupId>org.apache.hive</groupId> <artifactId>hive-exec</artifactId> <version>1.1.0</version> <exclusions> <exclusion> <artifactId> pentaho-aggdesigner-algorithm </artifactId> <groupId>org.pentaho</groupId> </exclusion> </exclusions> </dependency> <dependency> <groupId>org.apache.hive</groupId> <artifactId>hive-jdbc</artifactId> <version>1.1.0</version> </dependency>

2.Thrift Client
七、开发Hive的自定义函数 1.Hive的自定义函数（UDF）：User Defined Function
2.可以直接应用于select语句，对查询结果做格式化处理后，再输出内容
3.Hive自定义函数的实现细节

自定义UDF必须要继承 org.apache.hadoop.hive.ql.UDF
需要实现evaluate函数，evaluate函数支持重载

4.Hive自定义函数的部署运行

把程序打包（jar）放到目标机器（Linux的Hive服务器）上去
进入hive客户端，添加jar包：

- hive>add jar /root/training/udfjar/udf_test.jar

创建临时函数：用来指向上层的jar包

- hive>CREATE TEMPORARY FUNCTION <函数名> AS 'Java类名';

销毁临时函数：

- hive>DROP TEMPORARY FUNCTION <函数名>;

5.Hive自定义函数的使用

select <函数名> from table;

6.创建自定义函数

拼接两个字符串