18-Hive DML&分区表&内部函数

select

聚合函数

max/min/count/sum/avg  多进一出

分组函数  group by

求每个部门的平均工资

select deptno, avg(salary) from ruozedata_emp group by deptno;

Expression not in GROUP BY key 'ename'

select中出现的字段，如果没有出现在组函数/聚合函数中，必须出现在group by里面

求每个部门(deptno)、工作岗位(job)的最高工资(salary)

求每个部门的平均工资大于2000的部门

select deptno, avg(salary)  from ruozedata_emp group by deptno where avg(salary)>2000;

where是需要写在group by之前

where和having的执行之在什么位置？

case when then

select ename,salary,

case

when salary>1 and salary<=1000 then 'lower'

when salary>1000 and salary<=2000 then 'middle'

when salary>2000 and salary<=4000 then 'high'

else 'highest'

end

from ruozedata_emp;

join

1      ruoze

2      j

3      k

1      30

2      29

4      21

create table a(

id int, name string

) row format delimited fields terminated by '\t';

create table b(

id int, age int

) row format delimited fields terminated by '\t';

load data local inpath '/home/hadoop/data/join_a.txt' overwrite into table a;

inner join = join

1      ruoze  30

2      j      29

outer join : left right full

1      ruoze  30

2      j      29

3      k      NULL

分区表  partition

who when what

click_log

click_log_yyyyMMdd

hive hdfs + partition <== where partition

==> reduce io

分区表存在的意义

静态分区

create table order_partition(

ordernumber string,

eventtime string

)

partitioned by (event_month string)

row format delimited fields terminated by '\t';

LOAD DATA LOCAL INPATH '/home/hadoop/data/order.txt'

OVERWRITE INTO TABLE order_partition

PARTITION(event_month='2014-05');

alter table PARTITIONS convert to character set latin1;

alter table PARTITION_KEYS convert to character set latin1;

ALTER TABLE order_partition ADD IF NOT EXISTS

PARTITION (event_month='2014-07') ;

create table order_4_partition(

ordernumber string,

eventtime string

)

row format delimited fields terminated by '\t';

load data local inpath '/home/hadoop/data/order.txt' overwrite into table order_4_partition;

insert overwrite table order_partition

partition(event_month='2014-08')

select * from order_4_partition;

以上讲解的是静态分区中的单级分区

多级分区

create table order_mulit_partition(

ordernumber string,

eventtime string

)

partitioned by (event_month string,event_day string)

row format delimited fields terminated by '\t';

LOAD DATA LOCAL INPATH '/home/hadoop/data/order.txt'

OVERWRITE INTO TABLE order_mulit_partition

PARTITION(event_month='2014-05', event_day='01');

动态分区

create table ruozedata_static_emp

(empno int, ename string, job string, mgr int, hiredate string, salary double, comm double)

PARTITIONED by(deptno string)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '\t' ;

create table ruozedata_dynamic_emp

(empno int, ename string, job string, mgr int, hiredate string, salary double, comm double)

PARTITIONED by(deptno string)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '\t' ;

动态分区明确要求：分区字段写在select的最后面

insert into table ruozedata_dynamic_emp partition(deptno)

select empno,ename,job,mgr,hiredate,salary,comm,deptno from ruozedata_emp ;

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

这是hive中常用的设置key=value的方式

语法格式：

set key=value; 设置

set key;      取值

function

build-in

show functions