Hive操作
Hive操作
1 创建库
数据库在 HDFS 上的默认存储路径是/user/hive/warehouse/*.db
(1) 创建库：create database if not exists xxx;
(2) 创建指定HDFS上位置数据库：create database xxx location ‘/xxx’;
2 修改库
用户可以使用alter database命令为某个数据库的dbproperties设置键值对属性值，来描述数据库的属性信息
(1) alter命令：alter database db_hive set dbproperties(‘creat time’=’20170830’);
(2) 查看修改结果：desc database extended db_hive;
3 查询库
(1) 显示数据库：show databases;
(2) 过滤显示查询的数据库：show databases like ‘db_hive*’;
(3) 显示数据库信息：desc database db_hive;
(4) 显示数据库详细信息extended：desc database extended db_hive;
(5) 切换当前库：use db_hive;
4 删除库
(1) 删除空数据库：drop database db_hive1;
(2) 数据库不为空，强制删：drop database db_hive1 cascade；
5 创建表
创表格式：Create [external] table [if not exists] table_namecomment/partitioned by/clustered by/sorted by/row format/stored as/location/
Create table：指定表名
external：外部表
comment：为表和列添加注释
partitioned by：创建分区表
clustered by：创建分桶表
sorted by：不常用
stored as：指定存储文件类型
localtion：指定表在hdfs上的存储位置
like：允许用户复制现有的表结构，但是不复制数据
(1) 创建内部表：create table xxx;
create table if not exists student2(
id int, name string
)
row format delimited fields terminated by'\t'
stored as textfile
location '/user/hive/warehouse/student2';
(2) 创建外部表：create external table xxx;
(3) 查询表的类型：desc formatted xxx;
(4) 查看创建的表：show tables;
(5) 查询表结构：desc xxx;
6 修改表
(1) 重命名表：alter table table_name rename to new_table_name；
hive (default)> alter table dept_partition2 rename todept_partition3;
(2) 更新列信息
ALTER TABLEtable_name CHANGE [COLUMN] col_old_name col_new_name
column_type[COMMENT col_comment] [FIRST|AFTER column_name]
(3) 增加和替换列信息
ALTER TABLEtable_name ADD|REPLACE COLUMNS (col_name data_type
[COMMENTcol_comment], ...)
注：add是代表新增加字段，字段位置在所有列后面(patition列前)，replace则是表示替换表中所有字段
(4) 增加列
hive (default)> alter table dept_partition add columns(deptdescstring);
(5) 查询表结构
hive>desc dept_partition;
(6) 更新列
hive (default)> alter table dept_partition change column deptdesc descint;
(7) 替换列
hive (default)> alter table dept_partition replace columns(deptnostring, dname string, loc string);
7 删除表
hive (default)> drop table dept_partition;
内部表删数据和结构，外部表只是删了结构，表再创建回来，数据还在，要到存储位置删才能完全删除。
8 删除表中数据
注意：Truncate只能删除管理表，不能删除外部表中数据
hive (default)>truncate table student;
9 分区表partition
(1) 创建分区表：
create tabledept_partition(deptno int, dname string, loc string)
partitioned by (month string)row format delimited fields terminated by '\t';
(2) 数据导入：
hive (default)> load data local inpath'/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201709');hive (default)> load data local inpath '/opt/module/datas/dept.txt' intotabledefault.dept_partition partition(month='201708');hive (default)> load data local inpath '/opt/module/datas/dept.txt' intotabledefault.dept_partition partition(month='201707');
(3) 单查询：
hive (default)> select * from dept_partition wheremonth='201709';
(4) 多查询：
hive (default)> select * from dept_partition where month='201709'unionselect * from dept_partition where month='201708'unionselect * from dept_partition where month='201707';
(5) 增加单个分区：
hive (default)> alter table dept_partition add partition(month='201706') ;
(6) 增加多个分区：
hive (default)> alter table dept_partition add partition(month='201705')partition(month='201704');
(7) 删除单个分区：
hive (default)> alter table dept_partition drop partition (month='201704');
(8) 删除多个分区：
hive (default)> alter table dept_partition drop partition (month='201705'), partition(month='201706');
(9) 查看分区表有多少个区：
hive>show partitions dept_partition;
(10)查看分区表表结构：
hive>desc formatted dept_partition;
10 二级分区表
(1) 创建二级分区表
create tabledept_partition2(deptno int, dname string, loc string)
partitioned by (month string, day string)row format delimited fields terminated by '\t';
(2) 加载数据
hive (default)> load data local inpath'/opt/module/datas/dept.txt' into table
default.dept_partition2 partition(month='201709', day='13');
(3) 查询分区数据
hive (default)> select * from dept_partition2 wheremonth='201709' and day='13';
11 bucket
(1) 打开bucket功能：
set hive.enforce.bucketing = true；
(2) 创建表：
create external table rating_table_b
(userId INT,
movieId STRING,
rating STRING
)
clustered by (userId) into 32 buckets;
#按userid分库，userid和32取模进入kucket
创建完之后，用命desc formatted rating_table_b令查看
(3) 导入数据
from rating_table
insert overwrite table rating_table_b
select userid, movieid, rating;
在现有的表查数据，查到的数据导入rating_table_b中
数据导入之后查看数据：select * from rating_table_b limit 10;
这时HDFS上也有对应的数据目录，可以看到是32个对应目录
[root@master ~]# hadoop fs -get/hive/warehouse/rating_table_b/000018_0
这时按userid分库的，数据多少有些倾斜的
[root@master ~]# hadoop fs -cat/hive/warehouse/rating_table_b/000012_0 | less
。。。。。。
396^A420^A4.0
396^A412^A2.0
396^A410^A3.0
396^A405^A2.0
396^A382^A3.0
取模计算验证
>>>396%32
12
结果是12，所以这些数据在第12个bucket上正确
(4) 采样
select * from rating_table_btablesample(bucket 3 out of 16 on userid) limit 10;
注：tablesample是抽样语句，语法：TABLESAMPLE(BUCKETx OUT OF y) 。y 必须是table总bucket数的倍数或者因子。hive 根据 y 的大小，决定抽样的比例。例如，table总共分了4份，当y=2时，抽取(4/2=)2个 bucket 的数据，当y=8时，抽取(4/8=)1/2个 bucket的数据。x表示从哪个bucket开始抽取。例如，table总 bucket 数为 4， tablesample(bucket4 out of 4)，表示总共抽取（4/4=）1个 bucket的数据，抽取第 4 个 bucket的数据。注意：x的值必须小于等于y的值
12 数据导入
12.1 Load data
hive>load data [local] inpath '/xxx/xxx.txt' [overwrite]into table table_name [partition (partcol1=val1,…)];
load data：表示加载数据
local：有就是表示从本地加载数据到hive表，否则就是从hdfs加载到hive表
inpath：要加载数据的路径
overwrite：有就是表示覆盖已有数据，没有就是表示追加数据
into table：要加载到哪张表
table_name：具体要加载的表
partition：表示上传到指定分区
12.2 创建表时通过localtion指定加载数据路径
(1) 数据上传到hdfs
hive (default)> dfs -put /opt/module/datas/student.txt/user/hive/warehouse/student5;
(2) 创建表，并指定hdfs上的位置
hive (default)> create table if not exists student5(id int, name string)row format delimited fields terminated by '\t'location '/user/hive/warehouse/student5';
(3) 查询数据
hive (default)> select * from student5;
12.3 import数据到指定hive表中
先用export导出后，再将数据导入
hive (default)> import table student2partition(month='201709') from /user/hive/warehouse/export/student';
13 数据导出
13.1 insert导出
(1) 将查询结果导出到本地
hive(default)> insert overwritelocal directory '/opt/module/datas/export/student'select * from student;
(2) 将查询结果格式化导出到本地
hive(default)> insert overwritelocal directory '/opt/module/datas/export/student1'ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'COLLECTION ITEMS TERMINATED BY '\n'select * from student;
(3) 将查询结果导出到HDFS(没有local)
hive(default)> insert overwritedirectory '/user/atguigu/hive/warehouse/student2'ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'COLLECTION ITEMS TERMINATED BY '\n'select * from student;
13.2 Hadoop命令导出
hive (default)> dfs -get /user/hive/warehouse/student/month=201709/000000_0/opt/module/datas/export/student3.txt;
13.3 Hive Shell命令导出
基本语法：（hive -f/-e 执行语句或者脚本 > file）
[atguigu@hadoop hive]$ bin/hive -e 'select * fromdefault.student;' >/opt/module/datas/export/student4.txt;
13.4 export导出到HDFS上
hive (default)> export tabledefault.student to '/user/hive/warehouse/export/student';
14 查询
14.1 基本查询
(1) 全表查询：select * from xxx;
(2) 特定列查询：select empno,ename from emp;
14.2 常用函数
(1) 求总行数（count）hive (default)> select count(*) cntfrom emp;(2) 求工资的最大值（max）hive (default)> select max(sal)max_sal from emp;(3) 求工资的最小值（min）hive (default)> select min(sal)min_sal from emp;(4) 求工资的总和（sum）hive (default)> select sum(sal)sum_sal from emp;(5) 求工资的平均值（avg）hive (default)> select avg(sal)avg_sal from emp;
14.3 limit语句
典型的查查心会返回多行数据。limit子句用于限制返回的行数
hive (default)> select* from emp limit 5; 查询前5行
14.4 where语句
(1) 使用where子句，将不满足条件的行过滤掉
(2) where子句紧随from子句
查询出薪水大于1000 的所有员工
hive (default)> select* from emp where sal >1000;
14.4.1比较运算符(Between/In/Is Null)
案例实操（1）查询出薪水等于 5000 的所有员工hive (default)> select * from emp where sal =5000;（2）查询工资在 500 到 1000 的员工信息hive (default)> select * from empwhere sal between 500 and 1000;（3）查询 comm 为空的所有员工信息hive (default)> select * from empwhere comm is null;（4）查询工资是 1500 和 5000 的员工信息hive (default)> select * from empwhere sal IN (1500, 5000);
14.4.2 like和rlike
1）使用 LIKE 运算选择类似的值2）选择条件可以包含字符或数字:% 代表零个或多个字符(任意个字符)。_ 代表一个字符。3） RLIKE 子句是 Hive 中这个功能的一个扩展，其可以通过Java 的正则表达式这个更强大的语言来指定匹配条件。4）案例实操（1）查找以 2 开头薪水的员工信息hive (default)> select * from empwhere sal LIKE '2%';（2）查找第二个数值为 2 的薪水的员工信息hive (default)> select * from empwhere sal LIKE '_2%';（3）查找薪水中含有 2 的员工信息hive (default)> select * from empwhere sal RLIKE '[2]';
14.4.3逻辑运算符
逻辑运算符：and、or、not
(1) 查询薪水大于1000，部门是30
hive (default)> select * from emp wheresal>1000 and deptno=30;
(2) 查询薪水大于1000，或者部门是30
hive (default)> select * from emp wheresal>1000 or deptno=30;
(3) 查询除了20 部门和30 部门以外的员工信息
hive (default)> select * from emp wheredeptno not IN(30, 20);
15分组
15.1 Group by
Group by语句通常会跟聚合函数一起使用按照一个或者多个队列结果进行分组，然后对每个分组进行聚合操作
(1)计算emp表每个部门的平均工资
hive (default)> selectt.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;
(2)计算 emp 每个部门中每个岗位的最高薪水
hive (default)> selectt.deptno, t.job, max(t.sal) max_sal from emp t group by t.deptno,t.job;
15.2 Having语句
having与where不同点
(1) where针对表中的列发挥作用，查询数据；havind针对查询结果中的列发挥作用，筛选数据；
(2) where后面不能写分组函数，而having后面可以使用分组函数;
(3) having只用于group by分组统计语句
(4) 求每个部门的平均薪水大于2000 的部门
求每个部门的平均工资
hive (default)> selectdeptno, avg(sal) from emp group by deptno;
求每个部门的平均薪水大于 2000 的部门
hive (default)> selectdeptno, avg(sal) avg_sal from emp groupby deptno having avg_sal > 2000;
16 join语句
16.1 等值join
hive支持通常的sql join语句，但是支持等值连接，不支持非等值连接.
根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门编号；
hive (default)> selecte.empno, e.ename, d.deptno, d.dname from emp e join dept d on
e.deptno = d.deptno;
16.2 表的别名
(1) 好处
- 使用别名可以简化查询
- 使用表名前缀可以提高执行效率
(2) 案例实操
合并员工表和部门表
hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;
16.3 内连接
只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来
16.4 左外连接
左外连接：left join
join操作符左边表中符合where子句的所有记录将会被返回
hive (default)> selecte.empno, e.ename, d.deptno from emp e leftjoin dept d on e.deptno = d.deptno;
16.5 右外连接
右外连接：right join
join操作符右边表中符合where子句的所有记录将会被返回
hive (default)> selecte.empno, e.ename, d.deptno from emp e rightjoin dept d on e.deptno = d.deptno;
16.7 满外连接
满外连接：full join
将会返回所有表中符合where语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。
hive (default)> selecte.empno, e.ename, d.deptno from emp e fulljoin dept d on e.deptno = d.deptno;
16.8 多表连接
连接n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件
hive (default)> selecte.ename,d.deptno,l.loc_name from emp e join dept d on d.deptno = e.deptno joinlocation l on d.loc = l.loc;
大多数情况下，hive会对每对join连接对象启动一个mapreduce任务。本例中会首先启动一个mapreduce job对表e和表d进行连接操作，然后再启动一个mapreduce job将第一个mapreduce job的输出和表l进行连接操作
注意：为什么不是表d和表l先进行连接操作？这是因为hive总是按照从左到右的顺序执行的。
16.9 笛卡尔积
(1) 笛卡尔积会在下面条件下产生：
- 省略连接条件
- 连接条件无效
- 所有表中的行互相连接
(2) 案例实操
hive (default)> selectempno, deptno from emp, dept;
FAILED: SemanticExceptionColumn deptno Found in more than One Tables/Subqueries
16.10 连接谓词中支持or
hive (default)> selecte.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno or e.ename=d.ename; 错误的
17 排序
17.1 全局排序(orderby)
Order by：全局排序，一个mapreduce
(1) 使用order by子句排序
a.Asc(ascend)：升序(不添加，默认也是升序)
b.Desc(descend)：降序
(2) Order by子句在select语句的结尾
(3) 案例
a.查询员工信息工资升序排列
select * from emp order by sal;
b.查询员工信息工资降序排列
select * from emp order by sal desc;
17.2 按照别名排序
按照员工薪水的2倍排序
hive (default)> selectename, sal*2 twosal from emp order bytwosal;
17.3 多个列排序
按照部门和工资升序排序hive (default)> select ename,deptno, sal from emp order bydeptno, sal ;
17.4 每个mapreduce内部排序(sort by)
Sort By：每个 MapReduce 内部进行排序，对全局结果集来说不是排序。1）设置 reduce 个数hive (default)> setmapreduce.job.reduces=3;2）查看设置 reduce 个数hive (default)> setmapreduce.job.reduces;3）根据部门编号降序查看员工信息hive (default)> select * from emp sort by empno desc;4）将查询结果导入到文件中（按照部门编号降序排序）hive (default)> insert overwritelocal directory '/opt/module/datas/sortby-result' select *from emp sort by deptno desc;
17.5 分区排序(distributeby)
Distribute By：类似 MR 中 partition，进行分区，结合 sort by 使用。注意， Hive 要求 DISTRIBUTE BY 语句要写在 SORT BY 语句之前。对于 distribute by 进行测试，一定要分配多reduce 进行处理，否则无法看到 distribute by的效果。
先按照部门编号分区，再按照员工编号降序排序
hive (default)> setmapreduce.job.reduces=3;hive (default)> insert overwrite local directory'/opt/module/datas/distribute-result' select * from emp distribute by deptno sort by empno desc;
17.6 Cluster by
当 distribute by 和 sorts by 字段相同时，可以使用 cluster by 方式。cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。但是排序只能是倒序排序， 不能指定排序规则为 ASC 或者 DESC。1）以下两种写法等价select * from emp cluster by deptno;select * from emp distribute by deptno sort by deptno;注意：按照部门编号分区，不一定就是固定死的数值，可以是 20 号和 30 号部门分到一个分区里面去。
18 压缩
压缩分3部分：第一是输入数据压缩；第二是map输出压缩；第三是reduce输出压缩。
18.1 开启Map输出压缩
开启map输出阶段压缩可以减少job中map和reduce task间数据传输量。
案例实操：1）开启 hive 中间传输数据压缩功能hive (default)>sethive.exec.compress.intermediate=true;2）开启 mapreduce 中 map 输出压缩功能hive (default)>setmapreduce.map.output.compress=true;3）设置 mapreduce 中 map 输出数据的压缩方式hive (default)>setmapreduce.map.output.compress.codec= org.apache.hadoop.io.compress.SnappyCodec;4）执行查询语句hive (default)> select count(ename)name from emp;
18.2 开启reduce输出阶段压缩
当hive将输出写入到表中时，输出内容同样可以进行压缩。属性hive.exec.compress.output控制着这个功能。用户可能需要保持默认设置文件中的默认值false，这样默认的输出就是非压缩的纯文本文件了。用户可以通过在查询语句或执行脚本中设置这个值为true，来开启输出压缩功能。
案例实操：1）开启 hive 最终输出数据压缩功能
hive (default)>sethive.exec.compress.output=true;2）开启 mapreduce 最终输出数据压缩hive (default)>setmapreduce.output.fileoutputformat.compress=true;3）设置 mapreduce 最终数据输出压缩方式hive (default)> setmapreduce.output.fileoutputformat.compress.codec =org.apache.hadoop.io.compress.SnappyCodec;4）设置 mapreduce 最终数据输出压缩为块压缩hive (default)> setmapreduce.output.fileoutputformat.compress.type=BLOCK;5）测试一下输出结果是否是压缩文件hive (default)> insert overwritelocal directory '/opt/module/datas/distribute-result' select * from emp distribute by deptno sort by empno desc;
19 存储
Hive支持的存储数据的格式主要有textfile、sequencefile、orc、parquet；
Textfiel和sequencefile的存储格式都是基于行存储的；
Orc和parquet是基于列式存储的。