hadoop13--hive导入导出, 基本查询,分组, 排序

hive高级应用

数据导入

Load 加载数据

load data local inpath '/data/dept.txt' into table dept_partion;

对于以上的加载数据的语法，可以从本地加载数据，也可以从HDFS加载数据。

• Local ： 代表从本地加载数据
• HDFS: 把local 去掉代表从HDFS上加载数据

1. 把准备上传的数据，上传到HDFS 目录中

	dfs -put /data/dept.txt /data ;

2. 执行

load data inpath '/data/dept.txt' into table dept_partion;

加载HDFS上的数据的时候 ，HDFS上的数据，会进行剪切，所以原目录中的文件不存在
而在本地加载的时候，数据是进行复制的
对于以上的语法，存在如下的关键字：
Overwrite : 表示覆盖已有的数据，不加上该关键字则表示对已有的数据进行追加

不使用overwrite关键字的时候会对已有数据进行追加，接下使用overwrite关键字观察使用之后的结果

load data inpath '/data/dept.txt' overwrite into table dept_partion;

结果发现，把之前的数据全部覆盖为本次加载的数据

Insert 插入数据

通过insert 语句也可以向表中导入数据，该语法如下

insert into dept_partion values('200','beijing');

As select 通过查询语句创建表 （常用）
根据查询结果创建表 ， 查询到的结果，会在加载到新创建的表中

create  table dept_name as select dname from dept_partion;

以上的表的创建方式，经常会在清洗数据的时候使用

Location 指定加载数据的位置

 create EXTERNAL table student4(id int,name string) 
 row format delimited fields terminated by '\t'
 stored as textfile 
 location '/user/hive/warehouse/student';

对于以上的语法，在创建表的时候，指定数据加载在HDFS上位置

数据导出

insert导出

将查询的结果导出到本地

insert overwrite local directory '/data/dept2.txt' select * from dept_partion;

将查询结果按照指定分隔符进行导出

insert overwrite local directory '/data/dept2.txt' 
 row format delimited fields terminated by '\t' select * from dept_partion;

将查询结果导出到HDFS 上 去掉关键字local

insert overwrite directory '/data/dept2.txt' 
 row format delimited fields terminated by '\t' select * from dept_partion;

使用HDFS命令导出数据文件到本地

所谓hive中的数据，其实就是保存在HDFS上的数据，直接使用hdfs命令也可以从集群中下载想要的数据到linux本地文件系统中

dfs -get /data/dept2.txt/000000_0 /data/dept3.txt

通过hive 交互命令方式完成

bin/hive -e 'select * from dept_partion' > /data/dept4.txt

通过hive 中shell 命令实现数据的导出

基本查询

基础查询

1. 首先准备，需要练习的数据，创建emp表

  create table emp(
				empno int,
				ename string,
				job string,
				mgr int ,
				edate string ,
				sal double ,
				deptno int) 
row format delimited fields terminated by '\t';

3. 加载emp 数据到创建好的emp中

load data local inpath '/data/emp.txt' into table emp;

对于查询来说，存在全表查询，以及按照指定字段进行查询：

全表查询为

select * from emp;

按照指定字段查询，员工名称

select ename from emp;

为列起别名 可以使用as 关键字

select ename as myname from emp;

常用的函数

1. count 求总行数

select count(*) as n from emp;

2. sum 总和

select sum(sal) as sal from emp;

3. avg 平均数

select avg(sal) as sal from emp;

4. max 求最大值

select max(sal) as sal from emp;

5. min 求最小值

select min(sal) as sal from emp;

可以通过 show functions; 查看hive中所有的函数

limit

返回限制的行数

select * from emp limit 2;

where 语句

Where 语句hql 起到了一个过滤的作用，条件筛选

select sal from emp where ename='SMITH';

以上的语句为一个条件的过滤，同时也可以增加多个条件的过滤

查询员工信息，工资为800 部门编号为空的员工

select * from emp where sal=800.00 and deptno is null;

查询工资的范围800-1500之间的员工信息

select * from emp where sal between 800 and 1500 ;

查询工资为800 和 1500 员工信息

select * from emp where sal in(800,1500);

like 和 RLike (hive中扩展的功能)

like的使用

与like 配合使用的符号 % 表示任意字符
使用like 可以进行模糊查询，范例查询以S开头的员工姓名

select * from emp where ename like 'S%';

RLike的使用

可以通过java中的正则表达式，来进行匹配

select * from emp where sal RLIKE '[2]';

分组

GROUP BY 语句

Group by 可以结果进行分组，之后在对分组之后的数据进行操作

范例： 查询每个部门的平均工资

select deptno,avg(sal) from emp group by deptno;

Having 语句

• Having 与where的区别？
  由于where之后不使用分组，所以说对于分组之后的条件筛选，可以使用having
  根据以上的案例，继续扩充，要求查询出每个部门的平均工资，并且工资大于1000的部门

select deptno,avg(sal) as avg_sal from emp group by deptno having avg_sal >1000;

Join (左连接，右连接，内连接, 全连接)

• 左连接： 以关键字join左边的表为主，可以查询出主表中的全部内容，右边查询出来和连接字段匹配的内容，不匹配的输出为null
• 右连接： 和上面类似
• 内连接： 只有join的字段完全匹配才可以进行显示
  对于连接的操作，只能是两张表以上可以进行连接操作，准备数据emp 和dept
  范例： 内连接查询 部门与员工表的信息 按照deptno进行join

select e.empno,e.ename,d.deptno,d.dname from emp e join dept d on e.deptno=d.deptno;

左连接范例：

select e.empno,e.ename,d.deptno,d.dname from emp e left join dept d on e.deptno=d.deptno;

右连接范例：

select e.empno,e.ename,d.deptno,d.dname from emp e right join dept d on e.deptno=d.deptno;

• 全连接： full join 所有符合条件的数值都会被显示，不符合的用nulll来显示
  范例： 全连接范例

select e.empno,e.ename,d.deptno,d.dname from emp e full join dept d on e.deptno=d.deptno;

排序

order by 全局排序

对于全局排序来说可以使用order by 关键字 可以使用 desc 降序 asc 升序
范例： 查询员工信息并且按照工资进行降序排列

select * from emp order by sal desc ;

Desc 是按照降序排序，默认为asc 升序

分组之后排序
范例：

select deptno,avg(sal) as avg_sal from emp group by deptno order by avg_sal;

Mapreduce的内部排序(sort by )

对每个mapredue进行内部排序，不是全局排序

1. 设置redue的个数

set mapreduce.job.reduces=3;

2. 查看设置的reduce 个数

set mapreduce.job.reduces;

3. 按照部门编号进行排序查询

select * from emp sort by deptno;

4. 导出到本地

insert overwrite local  directory '/data/emp' select * from emp sort by deptno;

分区排序(distribute by )

distribute by : 类似mapreduce中的partition , 结合sort by使用
范例：按照部门编号，进行分区。然后在按照员工编号进行排序
在使用distribute by 必须先设置reduce的个数

insert overwrite local  directory '/data/emp2' select * from emp distribute by deptno sort by empno desc;