Hadoop-Hive常用基础HQL语句

show databases ;

use default;

desc database extended db_hive_03 ;

show tables ;

desc student ;
desc extended student ;
desc formatted student ;

create table student(
id int, 
name string) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
load data local inpath '/opt/datas/student.txt'into table student ;

create table if not exists default.dept_cats as select * from dept ;

create table if not exists default.dept_like like default.dept ;

truncate table dept_cats ;

drop table if exists dept_like_rename ;

alter table dept_like rename to dept_like_rename ;

select * from student ;
select id from student ;

show functions ;

desc function upper ;

desc function extended upper ;

select id ,upper(name) uname from db_hive.student ;

create EXTERNAL table IF NOT EXISTS default.emp_ext2(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
location '/user/hive/warehouse/emp_ext2';

create EXTERNAL table IF NOT EXISTS default.emp_partition(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)
partitioned by (month string,day string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ;

load data local inpath '/usr/local/app/hive_test/emp.txt' into table default.emp_partition partition (month='201805',day='31') ;

show partitions emp_partition ;

select * from emp_partition where month = '201509' and day = '13' ;

1）加载本地文件到hive表
load data local inpath '/opt/datas/emp.txt' into table default.emp ;
2）加载hdfs文件到hive中
load data inpath '/user/beifeng/hive/datas/emp.txt' overwrite into table default.emp ;
3）加载数据覆盖表中已有的数据
load data inpath '/user/beifeng/hive/datas/emp.txt' into table default.emp ;
4）创建表是通过insert加载
create table default.emp_ci like emp ;
insert into table default.emp_ci select * from default.emp ;
5）创建表的时候通过location指定加载

insert overwrite local directory '/opt/datas/hive_exp_emp'
select * from default.emp ;
insert overwrite local directory '/opt/datas/hive_exp_emp2'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' COLLECTION ITEMS TERMINATED BY '\n'
select * from default.emp ;
bin/hive -e "select * from default.emp ;" > /opt/datas/exp_res.txt

insert overwrite directory '/hive_test/export_emp.txt' select * from emp;
select * from emp ;
select t.empno, t.ename, t.deptno from emp t ;

select * from emp limit 5 ;
select t.empno, t.ename, t.deptno from emp t where  t.sal between 800 and 1500 ;

select t.empno, t.ename, t.deptno from emp t where comm is null ;

select count(*) cnt from emp ;
select max(sal) max_sal from emp ;
select sum(sal) from emp ;
select avg(sal) from emp ;

emp表
* 每个部门的平均工资
select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno ;
* 每个部门中每个岗位的做高薪水
select t.deptno, t.job, max(t.sal) avg_sal from emp t group by t.deptno, job ;

* where 是针对单条记录进行筛选
* having 是针对分组结果进行筛选

求每个部门的平均薪水大于2000的部门
select deptno, avg(sal) from emp group by deptno ;
select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;

##等值jion  join ... on
select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno ;
##左连接  left join
select e.empno, e.ename, d.deptno, d.dname  from emp e left join dept d on e.deptno = d.deptno ;
##右连接  right join
select e.empno, e.ename, e.deptno, d.dname  from emp e right join dept d on e.deptno = d.deptno ;
##全连接  full join
select e.empno, e.ename, e.deptno, d.dname  from emp e full join dept d on e.deptno = d.deptno ;

exit
#退出hive命令,使用exit,不要直接用ctrl+c,否则进程还在,只是窗口关闭了而已.

$ bin/hive --hiveconf <property=value>

hive > set ;
hive (db_hive)> set system:user.name ;
system:user.name=beifeng

[beifeng@hadoop-senior hive-0.13.1]$ bin/hive -help

* bin/hive -e <quoted-query-string>
eg:
bin/hive -e "select * from db_hive.student ;"

* bin/hive -f <filename>
eg:
$ touch hivef.sql
select * from db_hive.student ;
$ bin/hive -f /opt/datas/hivef.sql 
#将执行结果输入到指定的文件中
$ bin/hive -f /opt/datas/hivef.sql > /opt/datas/hivef-res.txt

hive (default)> dfs -ls / ;

hive (default)> !ls /opt/datas ；