一. 数据库
1. 查询数据库列表
show databases ;
2. 使用指定的数据库
use default;
3. 查看数据库的描述信息
desc database extended db_hive_03 ;
二. 表
1. 查询表列表
show tables ;
2. 查询表的描述信息:
desc student ; desc extended student ; desc formatted student ;
3. 创建表
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 ;
4. 创建一张表并复制一个表的结构和数据
create table if not exists default.dept_cats as select * from dept ;
5. 使用另一张表的结构创建一张新表
create table if not exists default.dept_like like default.dept ;
6. 清空表：
truncate table dept_cats ;
7.删除表
drop table if exists dept_like_rename ;
8. 修改表名
alter table dept_like rename to dept_like_rename ;
9.查询表
select * from student ; select id from student ;
三. 功能函数:
1. 显示功能函数列表
show functions ;
2. 查看功能函数的描述信息
desc function upper ;
3. 查询功能函数的扩展信息
desc function extended upper ;
4. 测试功能函数
select id ,upper(name) uname from db_hive.student ;
四. 进阶：
1. 创建一个外部表，并指定导入文件的位置和字段分割符：
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';
2. 创建分区表：
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' ;
3. 向分区表中导入数据：
load data local inpath '/usr/local/app/hive_test/emp.txt' into table default.emp_partition partition (month='201805',day='31') ;
4. 查看分区表列表：
show partitions emp_partition ;
5. 查询分区表中的数据：
select * from emp_partition where month = '201509' and day = '13' ;
6. 加载数据到hive：
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指定加载
7. hive到文件：
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
8. 将查询结果导出到本地文件中：
insert overwrite directory '/hive_test/export_emp.txt' select * from emp; select * from emp ; select t.empno, t.ename, t.deptno from emp t ;
五. 进阶查询：
1. = >= <= between and
select * from emp limit 5 ; select t.empno, t.ename, t.deptno from emp t where t.sal between 800 and 1500 ;
2. is null / is not null /in /not in
select t.empno, t.ename, t.deptno from emp t where comm is null ;
3. max/min/count/sum/avg
select count(*) cnt from emp ; select max(sal) max_sal from emp ; select sum(sal) from emp ; select avg(sal) from emp ;
4. group by /having 分组
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 ;
5. >>>having
* 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;
6. join 两个表进行连接
##等值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 ;
六. 客户端配置与启停
1. 关闭CLI客户端命令:
exit #退出hive命令,使用exit,不要直接用ctrl+c,否则进程还在,只是窗口关闭了而已.
2. 在启动hive时设置配置属性信息
$ bin/hive --hiveconf <property=value>
3. 查看当前所有的配置信息
hive > set ; hive (db_hive)> set system:user.name ; system:user.name=beifeng
4. 查看帮助
[beifeng@hadoop-senior hive-0.13.1]$ bin/hive -help
5. 执行sql语句
* bin/hive -e <quoted-query-string> eg: bin/hive -e "select * from db_hive.student ;"
6. 执行指定的文件
* 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
7. 在hive cli命令窗口中如何查看hdfs文件系统
hive (default)> dfs -ls / ;
8. 在hive cli命令窗口中如何查看本地文件系统
hive (default)> !ls /opt/datas ；
