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
