目录
一、hive基础命令
1.hive建表高阶语句：
2.创建临时表
3.表操作
4.hive分区
5.视图view
二、hive高级查询
1.查询进阶
2.关联查询
3.MAPJOIN
4.UNION
5.LOAD传数据
6.INSERT
7.使用insert语句将数据插入/导出到文件
8.order by
9.SORT BY/DISTRIBUTE BY
10.CLUSTER BY
11.GROUP BY
12.having
13.高级聚合--GROUPING SETS
一、hive基础命令
前提：SELECT用于映射符合指定查询条件的行
Hive SELECT是数据库标准SQL的子集
1.hive建表高阶语句：
(1)CTAS-->AS SELECT建表
create table ctas_employee as select * from employee;
CTAS不能创建partition,external,bucket
（2）CTE建表
注这里连接的r1和r3查询的字段要相同
create table cte_employee as
with
r1 as (select name from r2 where name='Ann'),
r2 as (select name from employee where info.sex='male'),
r3 as (select name form employee where info.age='20)
select * from r1 union all select * from r3;
(3)LIKE-->只复制表结构，表数据不复制
create table employee_like like employee;
2.创建临时表
（1）临时表是应用程序自动管理在复杂查询期间生成的中间数据的方法.
表只对当前session有效，session退出后自动删除 表空间位于/tmp/hive- <user_name>(安全考虑)
（2）如果创建的临时表表名已存在，实际用的是临时表
（3）建立临时表
create temporary table 表名（字段名 类型..）
create temporary table 表名 as select xxx from 表名
create temporary table 表名 like 表名
注：如何确定一个表是否为临时表？
答;可以show create table 表名，查看建表语句，如果建表有temporary关键字，则为临时表
3.表操作
（1）删除表
删除表：drop table if exists employee [with perge](wirh perge彻底删除表)
清空表数据：truncate table employee;
（2）修改表（alter 针对元数据）
修改表名：alter table 旧表名 rename to 新表名；
修改表注释：alter table 表名 set tblpropertites('comment'='新内容');
修改类型分隔符：alter table 表名 set serdeproperties（'field.delim'='$'）;
修正文件格式：alter table 表名 set fileformat rcfile；
修改字段名和属性：alter table 表名 change 旧字段名 新字段名 类型；
添加新的列：alter table 表名 add columns(字段名 类型)；
替文中所有列：alter table 表名 replace columns(字段名1 类型，字段名2 类型)；
4.hive分区
（1）分区功能
分区列的值将表划分为segments(文件夹)
查询时使用“分区”列和常规列类似
查询时Hive自动过滤掉不用于提高性能的分区
（2）分区的种类
静态分区和动态分区
（3）创建分区表：
CREATE TABLE employee_partitioned2( name string,
work_place ARRAY<string>,
sex_age STRUCT<sex:string,age:int>,
skills_score MAP<string,int>,
depart_title MAP<STRING,ARRAY<STRING>> )
PARTITIONED BY (year INT, month INT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
LINES TERMINATED BY '\n'；
（4）静态分区表操作
查看表中的分区：
show partitions 表名
添加分区：
alter table employee_partition add partition(year=2020,month=1)
partition(year=2020,month=2) partition(year=2020,month=3)..
删除分区：
alter table employee_partition drop partition(year=2020,month=1),
alter table employee_partition drop partiton(year=2020,month=1);
(5)动态分区
使用动态分区前需设定属性
set hive.exec.dynamic.partition=true;//退出数据库依然开启
set hive.exec.dynamic.partition.mode=nonstrict;
（6）向分区表中插入数据
注：不建议用insert into values的方法插入，尤其是分区表中含有map,array数据的时候
静态表：此时的分区是自己插入时建立
insert into employee partition(year=2015,month=1) select name,array('NJ') as address,named_struct('sex','man','age',22) as info,map('phython',99) as score,map('sales','leader') as jobs from employee_hr;
动态表：
插入前，执行命令：
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
insert into table employee_partition partiton(year,month)
select name,array('Toronto') as work_place,named_struct("sex","male",:"age",30)as sex_age,
map("python",90)as skill_score,map("r&d",array("developer")) as depart-title,year(start_date)as,month(start_date) as month
from employee_hr;
也可以使用：
load data inpath 'hdfs路径' into table 表名 partition(year=''2020,month='1')
（6）分桶
特点：
分桶对应于HDFS中的文件
更高的查询处理效率使抽样（sampling）
更高效 根据“桶列”的哈希函数将数据进行分桶
分桶只有动态分桶
分桶前执行命令：set hive.enforce.bucketing=true
在建表语句的row format delimited 前面添加
clustered by (字段名（一般为数值型）) into 4（分桶的数量）buckets
(7)分桶抽样
随机抽样基于整行数据
select * from 表名 tablesample(bucket 3 out of 32 on rand())s
select * from hr_bucket tablesample(bucket 3 out of 32 on rand());
随机抽样基于指定列（使用分桶列更高效）
select * from 表名 tablesample(bucket 3 out of 32 on 根据分桶的字段名)s
select * from hr_bucket tablesample(bucket 2 out of 4 on id)s;
详解：
假设当前分桶表，一共分了z桶！
x: 代表从当前的第几桶开始抽样
0<x<=y
y: z/y 代表一共抽多少桶！
y必须是z的因子或倍数！
怎么抽：从第x桶开始抽，当y<=z每间隔y桶抽一桶，直到抽满 z/y桶
随机抽样基于block size
select * from 表名 tablesample(10 percent)s;
select * from hr_bucket tablesample(10 percent);
select * from 表名 tablesample(1M)s；
select * from hr_bucket tablesample(1M);
select * from 表名 tablesample (10 rows)s;
select * from hr_bucket tablesample(1 rows)s;
5.视图view
（1）视图概述
通过隐藏子查询、连接和函数来简化查询的逻辑结构
虚拟表，从真实表中选取数据
只保存定义，不存储数据
如果删除或更改基础表，则查询视图将失败
视图是只读的，不能插入或装载数据
（2）Hive视图操作
创建视图：create view 视图名 as select xxx from 表名；
查找视图：show tables;(SHOW VIEWS 在 hive v2.2.0之后)
删除视图：drop view 视图名
更改视图属性：alter view 视图名 set tblproperties('comment'='新内容')；
注：如何知道查询的是表还是视图
1.可以尝试插入或者删除基础表，不可以则为视图
2.show create tables 表名；
（3）hive侧视图
OUTER关键字：即使output为空也会生成结果
select name,work_place,loc from employee lateral view outer
explode(split(line,',')) a as loc;
支持多层级
select name,wps,skill,score from employee
lateral view explode(work_place)work_place single as wps
lateral view explode(skills_score)sks as skill,score;
实例：
select
name,
wps,
skill,
score
from test1.employee
lateral view explode(work_place) workplace as wps
lateral view explode(skills_score) sks as skill,score
二、hive高级查询
1.查询进阶
列匹配正则表达式
需要提前执行命令： set hive.support.quoted.identifiers=none;
select `.*字段名.*` from 表名；
虚拟列（Virtual Columns）
INPUT__FILE__NAME：Mapper Task的输入文件名称//两个连续下划线
BLOCK__OFFSET__INSIDE__FILE：当前全局文件位置
2.关联查询
与mysql大致相同
内连接：INNER JOIN
外连接：OUTER JOIN RIGHT JOIN, LEFT JOIN, FULL OUTER JOIN
交叉连接：CROSS JOIN
也叫笛卡尔积
将两个表中的每条数据都互相匹配
隐式连接：Implicit JOIN
即两表先连没有用on做条件，而是用where
select * from emp e join emp_id ei where e.id=ei.id;
SEMIJOIN
相当于内连
SELECT e.empno,e.ename,e.job FROM emp e WHERE e.deptno IN (
SELECTdeptno FROM dept)
可以变为：
SELECT e.empno,e.ename,e.job FROM emp e LEFT SEMI JOIN dept d ON (e.deptno =d.deptno);
3.MAPJOIN
(1)大多小表连接大表
使用前需要执行命令：
set hive.auto.convert.join=true;
运行时会自动将连接转换成MAPJOIN
基本格式：
select /*+ mapjion(小表名称)*/字段名 from 表名 left join
大表名称 on 小表.values=大表.values;
实例：
select /*+ mapjoin(e_p)*/name from e_p left join
e_i on e_p.name=e_i.name;
(2)MAPJOIN操作不支持
在UNION ALL, LATERAL VIEW, GROUP BY/JOIN/SORT BY/CLUSTER BY/DISTRIBUTE BY等操作后面
在UNION, JOIN 以及其他 MAPJOIN之前
4.UNION
所有子集数据必须具有相同的名称,类型和顺序
UNION ALL:合并后保留重复项
UNION:合并后删除重复项
特殊实例
select 1 union all select 2;
(虽然两个数据不同，但是默认列名相同，属性也相同，所以不会报错)
5.LOAD传数据
原始数据被移动到目标表/分区，不再存在于原始位置
load data [local]inpath'hdfs路径或者[local地址]' [overwrite]into table 表名 [partition]
6.INSERT
（1）insert overwrite（覆盖）/into(追加) [table] 表名 select xx from 表名
（2）Hive支持从同一个表进行多次插入
（3）insert into 表名 values
注意：1.数据插入必须与指定列数相同
2.insert 不支持 insert overwrite table 表名 select 'hello',但支持
insert into table 表名 select 'hello';
（4）多插入
from 表3
insert overwrite table 表1 select *
insert overwrite table 表2 select *;
也可以指定值插入：
insert into table 表1（字段名）select 字段名 from 表名
(5)插入到分区
from 表名
insert overwrite table 表名 partition(year,month)
select 字段1,字段2....,'2018','09';
5.数据交换（导入数据/导出数据）-->IMPORT/EXPORT
导出数据：
export table 表名 to 'hdfs路径'
导出分区表数据并对数据进行分区
export table 表名 partition(分区内容) to 'hdfs路径'
导入数据：
import table 表名 from 'hdfs路径'
向分区表中导入数据，并分区
注：表必须是空表，也可以是未创建的表（此时的路径应是导出表数据的路径，
因为在导表数据时，建表的语句也同样导出，所以可以直接使用）
import table 表名 partition(分区内容) to 'hdfs路径'
7.使用insert语句将数据插入/导出到文件
同时导出：
from 表名
insert overwrite local directory '本地路径' select *
insert overwrite directory 'hdfs路径' select *
以指定格式插入
insert overwrite directory 'hdfs路径'
row format delimited fields terminated by ','
select * from 表名；
8.order by
类似于sql
只使用一个Reducer执行全局数据排序
速度慢,应提前做好数据过滤
支持使用CASE WHEN或表达式
支持按位置编号排序
set hive.groupby.orderby.position.alias=true;
select * from offers order by case when offerid = 1 then 1 else 0 end;
select * from offers order by 1;（表示以第一列升序排列的方式）
9.SORT BY/DISTRIBUTE BY
（1）SORT BY对每个Reducer中的数据进行排序
当Reducer数量设置为1时，等于ORDER BY
排序列必须出现在SELECT column列表中
（2）DISTRIBUTE BY类似于标准SQL中的GROUP BY（一般用作集群中）
注：不可以使用聚合函数
确保具有匹配列值的行被分区到相同的Reducer
不会对每个Reducer的输出进行排序
通常使用在SORT BY语句之前
实例：
SELECT department_id , name, employee_id, evaluation_score
FROM employee_hr
DISTRIBUTE BY department_id SORT BY evaluation_score DESC;
10.CLUSTER BY
（1）CLUSTER BY = DISTRIBUTE BY + SORT BY
不支持ASC|DESC
排序列必须出现在SELECT column列表中
为了充分利用所有的Reducer来执行全局排序，可以先使用CLUSTER BY，然后使用ORDER BY
实例：
SELECT name, employee_id FROM employee_hr CLUSTER BY name;
11.GROUP BY
Hive基本内置聚合函数与GROUP BY一起使用
如果没有指定GROUP BY子句，则默认聚合整个表
除聚合函数外，所选的其他列也必须包含在GROUP BY中
GROUP BY支持使用CASE WHEN或表达式
支持按位置编号分组
set hive.groupby.orderby.position.alias=true;
实例：
select category, max(offervalue) from offers group by category;-- group by使用表达式 select if(category > 4000, 'GOOD', 'BAD') as newcat,max(offervalue) from offers group by category if(category > 4000, 'GOOD', 'BAD');
select if(info.sex>20,'human','youth')a from emp_id_test
group by if(info.sex>20,'human','youth');
注：if（参数1，参数2，参数3）如果第一个参数为true，则返回第二个参数，否则返回第三个参数
12.having
HAVING：对GROUP BY聚合结果的条件过滤
实例：
select sex_age.age from employee group by sex_age.age having count(*) <= 1;
用来代替子查询
问：having 和 where有什么不同?
having是对分组后的数据进行筛选
where是在分组前对数据进行筛选
注意： select的列必须包含在GROUP BY中
13.高级聚合--GROUPING SETS
实现对同一数据集进行多重GROUP BY操作
本质是多个GROUP BY进行UNION ALL操作
GROUPING SETS语句 | 等价于GROUP BY语句 |
SELECT a , b, SUM( c ) FROM tab1 GROUP BY a, b GROUPING SETS ( (a, b), a, b, ( ) ) | SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b UNION SELECT a, null, SUM( c ) FROM tab1 GROUP BY a, null UNION SELECT null, b, SUM( c ) FROM tab1 GROUP BY null, b UNION SELECT null, null, SUM( c ) FROM tab1 |
SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b GROUPING SETS ( ( a,b ), a) | SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b UNION SELECT a, null, SUM( c ) FROM tab1 GROUP BY a |
SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b GROUPING SETS ( (a,b) ) | SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b |
SELECT a,b , SUM(c) FROM tab1 GROUP BY a, b GROUPING SETS ( a,b ) | SELECT a, null, SUM( c ) FROM tab1 GROUP BY a UNION SELECT null, b, SUM( c ) FROM tab1 GROUP BY b |
GROUP BY WITH CUBE|ROLLUP
CUBE：对分组列进行所有可能组合的聚合
ROLLUP：计算维度层次级别上的聚合
ROLLUP/CUBE语句 | 等价于GROUP BY语句 |
SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b, c WITH CUBE | SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b, c GROUPING SETS ( (a, b, c), (a, b), (b, c), (a, c), (a), (b), (c), ( )) |
SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b, c WITH ROLLUP | SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b, c GROUPING SETS ( (a, b, c), (a, b), (a), ( )) |
