Hive分区表操作

分区表操作：提高数据查询效率

创建分区表：

CREATE EXTERNAL TABLE *(...) PARTITIONED BY(name type...);

//分区内容直接作为列

静态导入：按照用户指定的分区值导入数据

eg1:分区存储下列信息

1001,Lucy,US,COO

1002,Mary,US,GM

1003,马云,CH,CEO

1004,马化腾,CH,COO

1005,王健林,CH,CTO

1006,雷军,CH,CTO

1007,Jack,UK,AVP

1008,Jackson,UK,AVP

1009,Bill,US,GM

1010,任正非,CH,CEO

1011,Bob,FR,HRD

1012,Leo,FR,AVP

1013,Ken,UK,COO

1014,Neil,US,GM

insert overwrite table emp2 partition(country='CH',work="CEO") select id,name from emp1 where country='CH' and work="CEO";

动态导入：

hive默认关闭了动态导入，在导入之前要设置开启动态导入

set hive.exec.dynamic.partition.mode=nonstrict

insert overwrite table emp2 partition(country,work) select * from emp1;

查看表中的分区： 

分区->文件夹  文件夹名称就是分区的值

show partitions table;

删除分区：

alter table emp2 drop partition(country=CH,work=CTO);

增加分区：

alter table emp2 add partition(country=CH,work=AVP)

表的分区列是在创建表的时候指定好的，创建表后不能修改分区列，只能修改分区值

删除表/清空表

drop table if exists table_name;

truncate table table_name;（先删除，再创建）

表重命名：

alter table table_name rename new_table;

修改列名、类型、列位置：

将test表中col1 修改new_col1数据类型type

（如果选择FIRST，会将new_col1放在列首，如果选择AFTER col2 ，会将new_col1放在col2之后）

alter table test change col new_col_name type [first |after col2]   

增加列：

alter table table_name add col_name dataType;

删除/替换列

alter table table_name replace columns (col1 type);

加载数据：

load data local（如果没有指定local，使用HDFS的数据）INPATH '数据路径' OVERWRITE INTO TABLE table_name。

OVERWRITE关键字，会将目标文件中之前存在的数据先删除掉，然后再导入。

如果没有指定OVERWRITE，仅仅会把新数据添加到目标文件夹中而不会删除原来数据。

在往分区表中导入数据的时候，如果没有指定的分区，会先创建分区。

数据查询：

desc emp:

id                  int                                     

name                string                                  

level               string                                  

super               int                                     

intime              date                                    

salary              double                                  

deptid              int   

desc dept:

deptid              int                                     

deptname            string                                  

loc                 string   

查询实例：

1、查询至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数首先在emp表中查询每个部门的人数：

首先查询每个部门的人数-->emp表

select deptid,count(*) from emp group by deptId; 

deptId count(*)

集成：

select dept.*,z.c  from dept,(select deptid,count(*) c from emp group by deptid) z where z.deptid=dept.deptid;

2、列出所有员工的姓名及其直接上级的姓名：（自表关联）

select e1.name,e2.name  from emp e1, emp e2 where e1.id = e2.super ;

使用左连接显示董事长信息：

select e1.name,e2.name from emp e1 left outer joinemp e2 one1.id = e2.super;

3、列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称：

首先找出受雇日期早于直接上级的员工编号、员工名称

select * from dept,(select e1.id d_id,e1.name ,e1.deptid from emp e1,emp e2 where e1.id = e2.super and e1.time <e2.time;) e3where e3.d_id = dept.id; 

4、列出最低薪金大于15000的各种工作及从事此工作的员工人数：

select level,count(*) from emp group by level having min(salary) > 15000;  

(注：where 后不能用聚合函数，having 可以)

搜索公众号 编译未来 关注我哟