目录
访问HIVE的三种方式
启动Hive命令,Ctrl C 退出客户端,执行测试语句,与sql一致
需要在运行命令前运行以下命令:
set hive.support.concurrency=false;
Hive数据存放在HDFS中,默认路径:/user/hive/warehouse/test 使用 MapReduce 进行计算。
DDL
查询数据库:
show databases;
显示数据库信息:
desc database db_hive;
显示数据库详细信息,extended
desc database extended db_hive;
避免要创建的数据库已经存在错误,增加 if not exists 判断。
create database if not exists db_hive;
切换当前数据库
use db_hive;
修改数据库
create table test6(id int,name string) row format delimited fields terminated by ',' ; -- 指定分隔符为 ,号
内部表和外部表的区别:
1、创建表的时候,内部表直接存储在默认的hdfs路径,不带数据;外部表需要自己指定路径,带数据
2、删除表的时候,内部表会将数据和元数据全部删除,外部表只删除元数据(metastore的元数据),数据(hdfs中的表数据)不删除
查询表的类型
desc formatted student2;
修改内部表 student2 为外部表
alter table student2 set tblproperties('EXTERNAL'='TRUE');
修改外部表 student2 为内部表
alter table student2 set tblproperties('EXTERNAL'='FALSE');
修改删除表
添加列
alter table dept add columns(deptdesc string);
更新列
alter table dept change column deptdesc desc string;
替换列
alter table dept replace columns(deptno string, dname string, loc string);
删除表
drop table table_name;
修改列属性时string不可以改其他类型,只能从低往高修改
只修改属性不修改列名也需要加属性,加原先属性即可
replace是把表中所有的列全部替换,replace 属性比原表多则自动会用null补全,比原表少则不显示,所以由replace可以完成表的增、删、改,
DML 数据操作
向表中装载数据
load
insert overwrite table 表名 select id, name from student where age='18'(查询语句);
查询语句中创建表
create table if not exists student3 as select id, name from student;
创建表时通过 Location 指定加载数据路径
create external table if not exists student5( id int, name string ) row format delimited fields terminated by '\t' location '/student; //将HDFS 根目录下的数据插入表中
Import 数据到指定 Hive 表中
import table student2 from '/user/hive/warehouse/export/student';
inport 写进表中的数据必须是export出的
用import导入有以下要求:
1、导入路径必须使export导出的路径。
2、写入的表要么不存在,要么表中没有数据
数据导出
Insert 导出
将查询的结果格式化导出到本地
hive (default)> insert overwrite local directory
'/opt/module/hive/data/export/student'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from student;
将local去掉及导出到HDFS
Hadoop 命令导出到本地
dfs -get /user/hive/warehouse/student/student.txt
/opt/module/data/export/student3.txt;
Hive Shell 命令导出
基本语法:(hive -f/-e 执行语句或者脚本 > file)
基本语法:(hive -f/-e 执行语句或者脚本 > file)
[wyc@hadoop102 hive]$ bin/hive -e 'select * from default.student5;' >> student1.txt
导出到一个空文件,过程中不显示表中信息,但导出的数据包含列信息,不实用
export table default.student to '/user/hive/warehouse/export/student';
(库名.表名) (HDFS路径)
export 和 import 主要用于两个 Hadoop 平台集群之间 Hive 表迁移。
truncate table student;
常用函数
1)求总行数(count)
select count(*) cnt from emp;
2)求工资的最大值(max)
select max(sal) max_sal from emp;
3)求工资的最小值(min)
select min(sal) min_sal from emp;
4)求工资的总和(sum)
select sum(sal) sum_sal from emp;
5)求工资的平均值(avg)
select avg(sal) avg_sal from emp;
6)LIMIT 子句用于限制返回的行数。
select * from emp limit 5;
Like 和 RLike
查找名字中第二个字母为 A 的员工信息
select * from emp where ename LIKE '_A%';
查找名字中带有 A 的员工信息
select * from emp where ename RLIKE '[A]';
分组
Join
两张表共有的字段作为连接
内连接(两张共有的部分):
select e.empno, e.ename, d.deptno from emp e join dept d
on e.deptno = d.deptno;
左外连接(包含join左边全部表的数据,和两张表共有的部分)
select e.empno, e.ename, d.deptno from emp e left join dept d
on e.deptno = d.deptno;
右外连接(包含join右边全部表的数据,和两张表共有的部分)
满外连接:将会返回所有表中符合 WHERE 语句条件的所有记录。如果任一表的指定字
段没有符合条件的值的话,那么就使用 NULL 值替代
select e.empno, e.ename, d.deptno from emp e full join dept d
on e.deptno = d.deptno;
多表连接查询(用多个join on)
>SELECT e.ename, d.dname, l.loc_name FROM emp e
JOIN dept d ON d.deptno = e.deptno
JOIN location l ON d.loc = l.loc;
排序
全局排序(Order By)
查询员工信息按工资升序排列
select * from emp order by sal;
多个列排序
select ename, deptno, sal from emp order by deptno, sal;
每个 Reduce 内部排序(Sort By)
Sort By:对于大规模的数据集 order by 的效率非常低。在很多情况下,并不需要全局排
序,此时可以使用 sort by。
Sort by 为每个 reducer 产生一个排序文件。每个 Reducer 内部进行排序,对全局结果集
来说不是排序,单独使用sort by它的分区是随机的
设置 reduce 个数
set mapreduce.job.reduces=3;
查看设置 reduce 个数
set mapreduce.job.reduces;
将根据部门编号降序查看员工信息导入到文件中(按照部门编号降序排序)
insert overwrite local directory
'/opt/module/data/sortby-result'
select * from emp sort by deptno desc;
insert overwrite local directory
'/opt/module/data/distribute-result' select * from emp distribute by
deptno sort by empno desc;
分区表和分桶表
加载数据到分区:
load data local inpath '/opt/module/data/dept2.txt' into table dept_par2 partition(day='2020-10-27',hour='12');
增加分区
alter table dept_partition add partition(day='2020-10-28');
增加多个分区用空格
删除单个分区
hive (default)> alter table dept_partition drop partition
(day='2020-10-29');
同时删除多个分区(用逗号)
alter table dept_partition drop partition (day='20200404'), partition(day='20200405');
查看分区表有多少分区
show partitions dept_partition;
查看分区表结构
desc formatted dept_partition;
执行修复命令(自动创建分区)
hive> msck repair table dept_partition2;
HDFS创建目录后并上传数据后手动添加分区
hadoop fs -mkdir /user/hive/warehouse/dept_par/day=2020-10-28
hadoop fs -put dept1.txt /user/hive/warehouse/dept_par/day=2020-10-28
alter table dept_partition2 add partition(day='201709',hour='14');
Hdfs创建目录后 load 数据到分区
load data local inpath '/opt/module/data/dept2.txt' into table dept_par2 partition(day='2020-10-27',hour='12');
insert into table dept_no_par partition(deptno)select dname,loc,deptno from dept;
insert into table dept_no_par select dname,loc,deptno from dept; 可以不加分区字段
表中字段比查询字段少,默认查询最后一个字段作为分区信息