hive操作指令

1、建表

songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long 
guan_beijing

create table if not exists test(
name string,
friends array<string>,
children map<string, int>,
address struct<street:string, city:string> )
row format delimited fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by '\n';

row format delimited fields terminated by ‘,’ – 列分隔符
collection items terminated by ‘_’ --MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
map keys terminated by ‘:’ – MAP 中的 key 与 value 的分隔符
lines terminated by ‘\n’; – 行分隔符

2、导数据

load data local inpath '/opt/module/hive/datas/test.txt' into table test;

3、创建数据库

create database if not exists db_hive;

4、显示

show databases;

5、切换数据库

use db_hive;

6、根据查询结果创建表（查询的结果会添加到新创建的表中）

create table if not exists student2 as select id, name from student;

7、根据已经存在的表结构创建表

create table if not exists student3 like student;

8、创建外部表

表是外部表，所以 Hive 并非认为其完全拥有这份数据。删除该表并不会删除掉这
份数据，不过描述表的元数据信息会被删除掉。

create external table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';

9、修改内部表 student2 为外部表

alter table student2 set tblproperties('EXTERNAL'='TRUE');

FALSE外改内

10、增列

alter table dept add columns(deptdesc string);

11、更新列

alter table dept change column deptdesc desc string；

12、替换列

alter table dept replace columns(deptno string, dname
string, loc string);

13、删除表

drop table dept;

14、插入数据

1.单表

 insert into table student_par 
values(1,'wangwu'),(2,'zhaoliu');

2.基本模式插入（根据单张表查询结果）

 insert overwrite table student_par
 select id, name from student where month='201709';

3.多表（多分区）插入模式（根据多张表查询结果）

from student
 insert overwrite table student partition(month='201707')
 select id, name where month='201709'
 insert overwrite table student partition(month='201706')
 select id, name where month='201709';

15、查询数据

 select * from student5;

16、 Import 数据到指定 Hive 表中

 import table student2
from '/user/hive/warehouse/export/student';

注意：先用 export 导出后，再将数据导入。

17、数据导出

1. Insert 导出

将查询的结果导出到本地

 insert overwrite local directory 
'/opt/module/hive/data/export/student'
select * from student;

将查询的结果格式化导出到本地

insert overwrite local directory 
'/opt/module/hive/data/export/student1'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from student;

将查询的结果导出到 HDFS 上(没有 local)

insert overwrite directory '/user/atguigu/student2'
 ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
 select * from student;

2. Hadoop 命令导出到本地

 dfs -get /user/hive/warehouse/student/student.txt
/opt/module/data/export/student3.txt;

3. Hive Shell 命令导出

bin/hive -e 'select * from default.student;' >
/opt/module/hive/data/export/student4.txt;

4. Export 导出到 HDFS 上

export 和 import 主要用于两个 Hadoop 平台集群之间 Hive 表迁移。

 export table default.student 
to '/user/hive/warehouse/export/student';

5. 清除表中数据（Truncate）

truncate table student;

18、基本查询（Select…From）

1. 列别名

重命名一个列
便于计算
紧跟列名，也可以在列名和别名之间加入关键字‘AS’
案例实操

select ename AS name, deptno dn from emp;

2. 常用函数

求总行数（count）
最大值（max）
最小值（min）
总和（sum）
平均值（avg）

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

3. Limit 语句

典型的查询会返回多行数据。LIMIT 子句用于限制返回的行数。

select * from emp limit 5;

4. Where 语句

使用 WHERE 子句，将不满足条件的行过滤掉
WHERE 子句紧随 FROM 子句

 select * from emp where sal >1000;

注意：where 子句中不能使用字段别名

5. 比较运算符（Between/In/ Is Null）

 select * from emp where sal =5000;

等于 5000

 select * from emp where sal between 500 and 1000;

500 到 1000

select * from emp where comm is null;

comm 为空

select * from emp where sal IN (1500, 5000);

1500 或 5000

6. Like 和 RLike

A开头

select * from emp where ename LIKE 'A%';

第二个为A

select * from emp where ename LIKE '_A%';

带有A

 select * from emp where ename RLIKE '[A]';

7. 逻辑运算符（And/Or/Not）

 select * from emp where sal>1000 and deptno=30;
 select * from emp where sal>1000 or deptno=30;
 select * from emp where deptno not IN(30, 20);

19、分组

1. Group By 语句

GROUP BY 语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然
后对每个组执行聚合操作

select t.deptno, avg(t.sal) avg_sal from emp t group by 
t.deptno;

计算 emp 表每个部门的平均工资

select t.deptno, t.job, max(t.sal) max_sal from emp t 
group by t.deptno, t.job;

计算 emp 每个部门中每个岗位的最高薪水

2. Having 语句

where 后面不能写分组函数，而 having 后面可以使用分组函数。
having 只用于 group by 分组统计语句。

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

平均

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

平均大于 2000

20、Join

1. 等值Join

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

根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称

2. 表的别名

select e.empno, e.ename, d.deptno from emp e join dept d 
on e.deptno = d.deptno;

合并员工表和部门表

3. 内连接

内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。

 select e.empno, e.ename, d.deptno from emp e join dept d 
on e.deptno = d.deptno;

4. 外连接

左外连接：JOIN 操作符左边表中符合 WHERE 子句的所有记录将会被返回。

select e.empno, e.ename, d.deptno from emp e left join 
dept d on e.deptno = d.deptno;

右外连接：JOIN 操作符右边表中符合 WHERE 子句的所有记录将会被返回。

select e.empno, e.ename, d.deptno from emp e right join 
dept d on e.deptno = d.deptno;

满外连接：将会返回所有表中符合 WHERE 语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用 NULL 值替代。

select e.empno, e.ename, d.deptno from emp e full join 
dept d on e.deptno = d.deptno;

5. 多表连接

create table if not exists location(
loc int,
loc_name string
)
row format delimited fields terminated by '\t';

load data local inpath '/opt/module/datas/location.txt' 
into table location;

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;

大多数情况下，Hive 会对每对 JOIN 连接对象启动一个 MapReduce 任务。本例中会首先启动一个 MapReduce job 对表 e 和表 d 进行连接操作，然后会再启动一个 MapReduce job 将第一个 MapReduce job 的输出和表 l;进行连接操作。
注意：为什么不是表 d 和表 l 先进行连接操作呢？这是因为 Hive 总是按照从左到右的顺序执行的。
优化：当对 3 个或者更多表进行 join 连接时，如果每个 on 子句都使用相同的连接键的话，那么只会产生一个 MapReduce job。

21、笛卡尔积

笛卡尔积会在下面条件下产生：
（1）省略连接条件
（2）连接条件无效
（3）所有表中的所有行互相连接

select empno, dname from emp, dept;

22、排序

1. 全局排序（Order By）

Order By：全局排序，只有一个 Reducer
ASC（ascend）: 升序（默认）
DESC（descend）: 降序

select * from emp order by sal;

select * from emp order by sal desc;

2. 按照别名排序

 select ename, sal*2 twosal from emp order by twosal;

3. 多个列排序

select ename, deptno, sal from emp order by deptno, sal;

4. 每个 Reduce 内部排序（Sort By）

Sort By：对于大规模的数据集 order by 的效率非常低。在很多情况下，并不需要全局排序，此时可以使用 sort by。
Sort by 为每个 reducer 产生一个排序文件。每个 Reducer 内部进行排序，对全局结果集来说不是排序。

set mapreduce.job.reduces=3;

设置reduce个数

select * from emp sort by deptno desc;

根据部门编号降序查看员工信息

insert overwrite local directory 
'/opt/module/data/sortby-result'
select * from emp sort by deptno desc;

将查询结果导入到文件中（按照部门编号降序排序）

5. 分区（Distribute By）

Distribute By： 在有些情况下，我们需要控制某个特定行应该到哪个 reducer，通常是为了进行后续的聚集操作。distribute by 子句可以做这件事。distribute by 类似 MR 中 partition（自定义分区），进行分区，结合 sort by 使用。对于 distribute by 进行测试，一定要分配多 reduce 进行处理，否则无法看到 distribute by 的效果。

insert overwrite local directory 
'/opt/module/data/distribute-result' select * from emp 
distribute by deptno sort by empno desc;

先按照部门编号分区，再按照员工编号降序排序。

6. Cluster By

当 distribute by 和 sorts by 字段相同时，可以使用 cluster by 方式。
cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。但是排序只能是升序排序，不能指定排序规则为 ASC 或者 DESC。

select * from emp cluster by deptno;
select * from emp distribute by deptno sort by deptno;

23、分区表

1. 创建分区表语法

注意：分区字段不能是表中已经存在的数据，可以将分区字段看作表的伪列。

create table dept_partition(
deptno int, dname string, loc string
)
partitioned by (day string)
row format delimited fields terminated by '\t';

2. 加载分区

注意：分区表加载数据时，必须指定分区

load data local inpath 
'/opt/module/hive/datas/dept_20200401.log' into table dept_partition 
partition(day='20200401');

3. 查询分区表中数据

select * from dept_partition where day='20200401';

单表

select * from dept_partition where day='20200401'
union
select * from dept_partition where day='20200402'
union
select * from dept_partition where day='20200403';

select * from dept_partition where day='20200401' 
or day='20200402' or day='20200403';

多分区联合查询

4. 增加分区

alter table dept_partition add partition(day='20200404');

alter table dept_partition add partition(day='20200405') 
partition(day='20200406');

5. 删除分区

alter table dept_partition drop partition 
(day='20200406');

alter table dept_partition drop partition 
(day='20200404'), partition(day='20200405');

6. 查看分区表有多少分区

show partitions dept_partition;

7. 查看分区表结构

desc formatted dept_partition;

24、二级分区

1. 创建二级分区表

create table dept_partition2(
 deptno int, dname string, loc string
 )
 partitioned by (day string, hour string)
 row format delimited fields terminated by '\t';

2. 加载和查询

load data local inpath 
'/opt/module/hive/datas/dept_20200401.log' into table
dept_partition2 partition(day='20200401', hour='12');

select * from dept_partition2 where day='20200401' and 
hour='12';

3. 把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式

方式一：上传数据后修复

dfs -mkdir -p
/user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=13;
dfs -put /opt/module/datas/dept_20200401.log 
/user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=13;

msck repair table dept_partition2;

方式二：上传数据后添加分区

alter table dept_partition2 add 
partition(day='201709',hour='14');

方式三：创建文件夹后 load 数据到分区

load data local inpath 
'/opt/module/hive/datas/dept_20200401.log' into table
dept_partition2 partition(day='20200401',hour='15');

25、动态分区调整

关系型数据库中，对分区表 Insert 数据时候，数据库自动会根据分区字段的值，将数据插入到相应的分区中，Hive 中也提供了类似的机制，即动态分区(Dynamic Partition)，只不过，使用 Hive 的动态分区，需要进行相应的配置。

create table dept_partition_dy(id int, name string) 
partitioned by (loc int) row format delimited fields terminated by '\t';

set hive.exec.dynamic.partition.mode = nonstrict;
hive (default)> insert into table dept_partition_dy partition(loc) select 
deptno, dname, loc from dept;

将 dept 表中的数据按照地区（loc 字段），插入到目标表 dept_partition 的相应分区中
注意：在每个执行 MR 的节点上，最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即 day 字段有 365 个值，那么该参数就需要设置成大于 365，如果使用默认值 100，则会报错。

hive.exec.max.dynamic.partitions.pernode=100

26、分桶表

分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区。对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围划分。
分桶是将数据集分解成更容易管理的若干部分的另一个技术。
分区针对的是数据的存储路径；分桶针对的是数据文件。

1. 创建分桶表

建表

create table stu_buck(id int, name string)
clustered by(id) 
into 4 buckets
row format delimited fields terminated by '\t';

查看结构

desc formatted stu_buck;

导入数据到分桶表中，load 的方式

load data inpath '/student.txt' into table stu_buck;

Hive 的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方
式决定该条记录存放在哪个桶当中

2. 分桶表操作需要注意的事项

（1）reduce 的个数设置为-1,让 Job 自行决定需要用多少个 reduce 或者将 reduce 的个数设置为大于等于分桶表的桶数
（2）从 hdfs 中 load 数据到分桶表中，避免本地文件找不到问题
（3）不要使用本地模式

3. insert 方式将数据导入分桶表

insert into table stu_buck select * from student_insert;

27、抽样查询

对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive 可以通过对表进行抽样来满足这个需求。
语法: TABLESAMPLE(BUCKET x OUT OF y)
查询表 stu_buck 中的数据。

select * from stu_buck tablesample(bucket 1 out of 4 on 
id);

注意：x 的值必须小于等于 y 的值，否则

FAILED: SemanticException [Error 10061]: Numerator should not be bigger 
than denominator in sample clause for table stu_buck

28、系统内置函数

1. 空字段赋值

NVL：给值为 NULL 的数据赋值，它的格式是 NVL( value，default_value)。它的功能是如果 value 为NULL，则 NVL 函数返回 default_value 的值，否则返回 value 的值，如果两个参数都为 NULL ，则返回 NULL

select comm,nvl(comm, -1) from emp;

如果员工的 comm 为 NULL，则用-1 代替

select comm, nvl(comm,mgr) from emp;

如果员工的 comm 为 NULL，则用领导 id 代替

2. CASE WHEN THEN ELSE END

求出不同部门男女各多少人。

select
dept_id,
sum(case sex when '男' then 1 else 0 end) male_count,
sum(case sex when '女' then 1 else 0 end) female_count
from emp_sex
group by dept_id;

3. 行转列

CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字
符串;
CONCAT_WS(separator, str1, str2,…)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
注意: CONCAT_WS must be “string or array”
COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生 Array 类型字段。

SELECT
t1.c_b,
CONCAT_WS("|",collect_set(t1.name))
FROM (
SELECT
NAME,
CONCAT_WS(',',constellation,blood_type) c_b
FROM person_info
)t1
GROUP BY t1.c_b

把星座和血型一样的人归类到一起。

4. 列转行

EXPLODE(col)：将 hive 一列中复杂的 Array 或者 Map 结构拆分成多行。
LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：用于和 split, explode 等 UDTF 一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

SELECT
movie,
category_name
FROM
movie_info
lateral VIEW
explode(split(category,",")) movie_info_tmp AS category_name;

将电影分类中的数组数据展开。

5. 窗口函数（开窗函数）

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。
CURRENT ROW：当前行
n PRECEDING：往前 n 行数据
n FOLLOWING：往后 n 行数据
UNBOUNDED：起点，
UNBOUNDED PRECEDING 表示从前面的起点，
UNBOUNDED FOLLOWING 表示到后面的终点
LAG(col,n,default_val)：往前第 n 行数据
LEAD(col,n, default_val)：往后第 n 行数据
NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从 1 开始，对于每一行，NTILE 返回此行所属的组的编号。注意：n 必须为 int 类型。

jack,2017-01-01,10

create table business(
name string,
orderdate string,
cost int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
load data local inpath "/opt/module/data/business.txt" into table 
business;

select name,count(*) over () 
from business
where substring(orderdate,1,7) = '2017-04'
group by name;

查询在 2017 年 4 月份购买过的顾客及总人数

select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) 
from business;

查询顾客的购买明细及月购买总额

select name,orderdate,cost,
sum(cost) over() as sample1,--所有行相加
sum(cost) over(partition by name) as sample2,--按 name 分组，组内数据相加
sum(cost) over(partition by name order by orderdate) as sample3,--按 name
分组，组内数据累加
sum(cost) over(partition by name order by orderdate rows between 
UNBOUNDED PRECEDING and current row ) as sample4 ,--和 sample3 一样,由起点到
当前行的聚合
sum(cost) over(partition by name order by orderdate rows between 1 
PRECEDING and current row) as sample5, --当前行和前面一行做聚合
sum(cost) over(partition by name order by orderdate rows between 1 
PRECEDING AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行
sum(cost) over(partition by name order by orderdate rows between current 
row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行
from business;

将每个顾客的 cost 按照日期进行累加

select name,orderdate,cost,
lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) 
as time1, lag(orderdate,2) over (partition by name order by orderdate) as 
time2 
from business;

查看顾客上次的购买时间

select * from (
 select name,orderdate,cost, ntile(5) over(order by orderdate) sorted
 from business
) t
where sorted = 1;

查询前 20%时间的订单信息

6. Rank

RANK() 排序相同时会重复，总数不会变
DENSE_RANK() 排序相同时会重复，总数会减少
ROW_NUMBER() 会根据顺序计算

create table score(
name string,
subject string, 
score int) 
row format delimited fields terminated by "\t";
load data local inpath '/opt/module/data/score.txt' into table score;

select name,
subject,
score,
rank() over(partition by subject order by score desc) rp,
dense_rank() over(partition by subject order by score desc) drp,
row_number() over(partition by subject order by score desc) rmp
from score;

name subject score rp drp rmp
宋宋 英语 84 1 1 1
大海 英语 84 1 1 2
婷婷 英语 78 3 2 3
孙悟空 英语 68 4 3 4