Hive学习三

Hive学习三

标签（空格分隔）： Hive

---

一， load方式加载数据，注意分区表加载数据的特殊性

1， 创建普通表并加载数据

create table if not exists emp(empno int,ename string,job string,mgr int,hiredate string,sal double,comm double,deptno int) row format delimited fields terminated by '\t' stored as textfile;

load data local inpath '/opt/datas/emp.txt' into table emp ；//加载本地数据并查询数据

2， 创建分区表：

CREATE TABLE IF NOT EXISTS emp_day(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int) partitioned by (day string) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE ;

load data local inpath '/opt/datas/emp.txt' into table emp_day partition(day='0312');//加载数据并查询

二，将hive查询结果保存到表中和本地

1， 保存查询结果到另一个hive表中

insert into emp_other select * from emp;

2， 导出结果到本地文件中

insert overwrite local directory '/opt/datas/emp_export0312' 
       ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' select * from emp;

3， 导出结果到hdfs系统

insert overwrite directory 'emp_export0312' select * from emp;

三,常见查询练习，如group by having join sort by order by等

1，  select empno,ename,deptno from emp where deptno=30;

2，  select empno,ename,deptno from emp limit 3;

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

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

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

6,select * from emp order by empno desc;