背 景
HiveQL语法和MySQL的语法很像，当然Hive本身作为大数据的数据仓库，本身对数据的修改和删除并不铭感，虽然Hive支持通过ORC为存储文件实现数据的删除和修改，但是这个功能相对比较鸡肋，这里也不做HiveQL的UPDATE和DELETE介绍，重点聊聊Hive DML语言的SELECT和新增数据的INSERT及加载数据LOAD；
如果你精通MySQL，你会觉得HiveQL很友好，如果你对SQL的基本操作不是很了解，建议你去看看SQL的基础知识数据库原理PPT课件;
基本的select操作
Hive 中 select 操作的语法如下：
SELECT [All | DISTINCT] select_expr, select_expr,
FROM table reference
(WHERE where_condition]
(GROUP BY col list [HAVING condition]]
[ CLUSTER BY col list
I (DISTRIBUTE BY col_list] [SORT BY! ORDER BY col_list]
[LIMIT number]
接下来，聊聊这些关键字的用法；
SELECT后面使用关键字ALL和DISTINCT选项区分对重复记录的处理 。 默认是 ALL ，表示查询所有记录，可以省略不写， DISTINCT 表示去掉重复的记录 ,这个基础只要略懂SQL的基本都应该知道吧，这里就不过多阐述了；FROM跟传统SQL的类似，后面跟着需要操作的源表；WHERE条件：类似于传统 SQL 的where条件，支持AND 、 OR 、 BETWEEN 、 IN 、NOT IN等常规操作 。ORDER BY与SORT BY的不同：ORDER BY指全局排序,因为Hive最终转化为MapReduce运行，如果使用ORDER BY最终都是要再起一个Reduce任务将所有的结果全局排序一次，而SORT BY只在本机做排序 ，即只是单独每个reduce本身的结果排序，全局文件不一定有序。LIMIT：可以限制查询的记录数，如只取table1的前5行；
SELECT * FROM table1 LJMIT 5 ;
LIMIT 也可以实现 Top查询，比如下面的查询语句可以查询销售记录最多的 5 个销售代表：
SET mapred.reduce.tasks = 1
SELECT * FROM test SORT BY amount DESC LIMIT 5;
Hive加载数据到表内
加载数据前需要先新建表结构，关于HiveQL建表需要的艺术性，可以参考下博客Hive从入门到放弃——HiveQL表级别DDL设计的艺术性（五）;
这里建立学生表ods_rs_basic_tbd_student和班级表ods_rs_basic_tbd_class，具体语HiveQL句如下；
-- 切换到hive的dw数据库
use dw;
-- 创建学生表
CREATE TABLE `ods_rs_basic_tbd_student`(
`sno` int COMMENT '学号',
`sname` string COMMENT '姓名',
`ssex` string COMMENT '性别',
`sage` string COMMENT '年龄',
`classid` string COMMENT '首字母拼音简称')
COMMENT 'ods学生表'
PARTITIONED BY (
`event_week` int,
`event_day` string,
`event_hour` string)
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
'/hive/warehouse/ods/rs/basic/ods_rs_basic_tbd_student'
TBLPROPERTIES ('parquet.compression'='snappy');
-- 创建班级表
CREATE TABLE `ods_rs_basic_tbd_class`(
`classid` int COMMENT '班号',
`classname` string COMMENT '班级名称'
)
COMMENT 'ods班级表'
PARTITIONED BY (
`event_week` int,
`event_day` string,
`event_hour` string)
row format delimited fields terminated by ','
lines terminated by '\n'
LOCATION'/hive/warehouse/ods/rs/basic/ods_rs_basic_tbd_class'
;
Hive加载数据到表内通用的做法有4种，大类上只有两种，即load 和insert，具体如下：
insert overwrite table：数据本身存在表里面或者自己造出来的，利用select出来字段，覆盖（清掉原目标表记录，写入新的）到目标表的数据;insert into table：数据本身存在表里面或者自己造出来的，利用select出来字段，追加（保留原目标表记录，插入在原记录后面）到目标表的数据;load ... overwrite into table ...：一种是存在数据文件在hdfs上(一般建议存在hdfs上)或者hive机器的local内，采用load将数据从该文件load到表内覆盖（清掉原目标表记录，写入新的）原来目标表的数据；load ... into table ...：一种是存在数据文件在hdfs上(一般建议存在hdfs上)或者hive机器的local内，采用load将数据从该文件load到表内追加（保留原目标表记录，插入在原记录后面）原来目标表的数据；
insert操作
insert 两种方式加载数据到表内的操作HiveQL语句如下；
-- 覆盖原表记录，插入新数据
insert overwrite table ods_rs_basic_tbd_student partition(event_week='25',event_day='20200618',event_hour='00')
select 1,'小明','男',15,6
union all
select 2,'小红','女',13,5
union all
select 3,'小丽','女',14,7
union all
select 4,'小华','男',17,1
union all
select 5,'小蓝','男',15,2
;
-- 追加原表记录，插入新数据
insert into table ods_rs_basic_tbd_student partition(event_week='25',event_day='20200618',event_hour='00')
select 6,'大林','男',14,3
union all
select 7,'大姝','女',13,5
union all
select 8,'大瑶','女',14,7
union all
select 9,'大发','男',17,1
union all
select 10,'大佬','男',15,4
;
-- 查看下表内数据
select * from ods_rs_basic_tbd_student where event_day='20200618';
查询结果如图1，红框内是insert overwrite table加载的数据，绿框内的是insert into table 加载的数据；
load操作
先准备两个txt文件ods_rs_basic_tbd_class和ods_rs_basic_tbd_class1，具体内容如下，ods_rs_basic_tbd_class文件如下；
3,初一(1)班
4,初一(4)班
6,初一(5)班
7,初一(6)班
8,初一(7)班
9,初一(8)班
10,初一(9)班
11,初一(10)班
ods_rs_basic_tbd_class内容如下；
12,初二(1)班
13,初三(1)班
将两个文件上传 到hdfs的/source目录下，shell指令如下；
hadoop fs -put ods_rs_basic_tbd_class /source
hadoop fs -put ods_rs_basic_tbd_class1 /source
然后分别使用load ... overwrite into table ...和load ... into table ...将数据加载到表内，具体操作Hive-SQL语句如下；
-- load ... overwrite into table ... 样例
load data inpath '/source/ods_rs_basic_tbd_class' overwrite into table ods_rs_basic_tbd_class partition(event_week='25',event_day='20200618',event_hour='00');
-- load ... into table ... 样例
load data inpath '/source/ods_rs_basic_tbd_class1' into table ods_rs_basic_tbd_class partition(event_week='25',event_day='20200618',event_hour='00');
-- 预览load加载数据到hive表内的结果
select * from ods_rs_basic_tbd_class where event_day='20200618';
load加载数据结果预览，如图2,红框内是load ... overwrite into table ...加载的数据，绿框内的是load ... into table ... 加载的数据；
jion操作
Hive-2.3.5版本，已经是相对较稳定的版本了，Hive的join语句基本可以完全等同于MySQL的join操作了，常用的join有：
innser join(内连接 inner可省略 )left join(左连接)right join(右连接，有些人会觉得这个连接没必要存在，因为只要把表的顺序反一下即可用左连接代替))full join(全连接)cross join(笛卡尔积)left semi join(2.2.0版本后就没有地位了)
注意：默认写个join指的就是inner join(内连接)，左连接，右连接，全连接都是外连接，所以又可以写成left outer join(左外连接),right outer join(右外连接),full outer join(全外连接) ，但是我都比较懒，通常都省略outer关键字，大家知道一下这点即可；
Hive-SQL的常用连接和MySQL相似，接下来我们逐个来聊聊这些连接；
innser join
innser join的逻辑是返回是两个表的交集，如图3，即结果即在table1，也在table2上的，当然同时也要满足各自的筛选条件，这里假设table1为ods_rs_basic_tbd_student，table2为ods_rs_basic_tbd_class，则两个表的inner join语句如下;
select s.sno as `学号`
,s.sname as `姓名`
,s.ssex as `性别`
,s.sage as `年龄`
,c.classid as `班号`
,c.classname as `班级名称`
,s.event_week as `第几周`
,s.event_day as `日期`
,s.event_hour as `小时`
from ods_rs_basic_tbd_student s
inner join ods_rs_basic_tbd_class c
on s.classid=c.classid
and c.event_day='20200618'
where s.event_day='20200618';
如图4结果是利用inner join查询出第25周20200618日00时所有分配到了班级的学生。
left join
left join的逻辑是以左表为主体，两个表关联后，返回左表（table1）所有的行，如果右表（table2）中没有匹配，则结果为 NULL。如图5，这里假设table1为ods_rs_basic_tbd_student，table2为ods_rs_basic_tbd_class，则两个表的left join语句如下;
select s.sno as `学号`
,s.sname as `姓名`
,s.ssex as `性别`
,s.sage as `年龄`
,c.classid as `班号`
,c.classname as `班级名称`
,s.event_week as `第几周`
,s.event_day as `日期`
,s.event_hour as `小时`
from ods_rs_basic_tbd_student s
left join ods_rs_basic_tbd_class c
on s.classid=c.classid
and c.event_day='20200618'
where s.event_day='20200618'
-- and c.classid is not null --此条件若放开，则该情形效果等同inner join
-- and c.classid is null -- 此条件若放开，则只会返回未分配到班级的学生
;
如图4结果是利用left join查询出第25周20200618日00时所有学生的班级情况，其中学号2，4，5，7，9的学生的班级为null，说明在第25周20200618日00时这些学生的原始班级撤销了，需要分配新班级；
注意：可以细细品味下以上left join 的Hive-SQL语句的注释部分，加深left join的使用场景和印象。
right join
right join的逻辑是以右表为主体，两个表关联后，返回右表（table2）所有的行，如果左表（table1）中没有匹配，则结果为 NULL，如图7，假设table1为ods_rs_basic_tbd_student，table2为ods_rs_basic_tbd_class，则两个表的right join语句如下;
注意：right join和left join的逻辑刚好是相反的，在不影响性能优化的情况下，right join完全可以被left join代替，因为只需要把table1和table2的位置调换下，那么原来right join的逻辑就变成了left join的逻辑，所以有些偏激的规范内，干脆就不允许开发人员使用right join。
select s.sno as `学号`
,s.sname as `姓名`
,s.ssex as `性别`
,s.sage as `年龄`
,c.classid as `班号`
,c.classname as `班级名称`
,c.event_week as `第几周`
,c.event_day as `日期`
,c.event_hour as `小时`
from ods_rs_basic_tbd_student s
right join ods_rs_basic_tbd_class c
on s.classid=c.classid
and c.event_day='20200618'
where c.event_day='20200618'
-- and s.sno is not null --此条件若放开，则该情形效果等同inner join
-- and s.sno is null -- 此条件若放开，则只会返回没有学生的班级
;
如图8结果是利用right join查询出第25周20200618日00时所有班级的学生情况，其中班号8，9，10，11，12，13班级的学生为null，说明在第25周20200618日00时这些班级的原始学生撤销了，可以分配新的学生进来；
注意：对比right join 的Hive-SQL和left join 的Hive-SQL的区别，掌握两者真正的实现效果区别；可以细细品味下以上right join 的Hive-SQL语句的注释部分，加深right join的使用场景和印象。
full join
full join关键字返回左表（table1）和右表（table2）中所有的行,即两个表的并集。如果 table1 表中的行在 table2 中没有匹配或者 table2 表中的行在 table1 表中没有匹配，也会列出这些行，逻辑图谱如图9，假设table1为ods_rs_basic_tbd_student，table2为ods_rs_basic_tbd_class，则两个表的full join语句如下;
select s.sno as `学号`
,s.sname as `姓名`
,s.ssex as `性别`
,s.sage as `年龄`
,c.classid as `班号`
,c.classname as `班级名称`
,c.event_week as `第几周`
,c.event_day as `日期`
,c.event_hour as `小时`
from ods_rs_basic_tbd_student s
full join ods_rs_basic_tbd_class c
on s.classid=c.classid
and c.event_day='20200618'
and s.event_day='20200618'
-- where c.classid is not null and s.sno is not null --此条件若放开，则该情形效果等同inner join
-- where s.sno is not null --此条件若放开，则该情形效果等同left join
-- where c.classid is not null --此条件若放开，则该情形效果等同right join
;
如图10,结果是利用full join查询出第25周20200618日00时所有班级的学生情况，其实就是left join和right join的并集结果，在实际运用中full join的场景不多；
注意：对比right join 的Hive-SQL和left join 的Hive-SQL及full join 的Hive-SQL的区别，掌握两者真正的实现效果区别；可以细细品味下以上full join 的Hive-SQL语句的注释部分，加深full join的使用场景和印象。
cross join
所谓cross join其实就是数学里面的笛卡尔积，就是两组数据之间的依次组合得到的结果，如果table1有m条记录 ，table2有n条记录，那么cross join在没有任何过滤条件的情况下，得到的结果是m*n条记录；
cross join的一个重大用途之一就是生成大量数据，其中最经典cross join例子之一就是扑克牌的生成，一副扑克（去掉大小王），他是怎么来的呢？其实很简单，他就是由两个table笛卡尔积得到的，这里假设table1为ods_rs_basic_tbd_colorstyle(扑克牌花色)，table1的值域[黑桃♠，梅花♣，红心❤，方块♦]；table2为ods_rs_basic_tbd_numstyle(扑克牌数字)，table2的值域[A,1,2,3,4,5,6,7,8,9,10,J,Q,K]；所以只要两个表之间cross join（笛卡尔积）就可得到一副扑克牌，具体实现代码如下；
-- 创建扑克牌花色表
CREATE TABLE `ods_rs_basic_tbd_colorstyle`
(
`colorstyle` string COMMENT '扑克牌花色'
)
COMMENT 'ods扑克牌花色表'
PARTITIONED BY (
`event_week` int,
`event_day` string,
`event_hour` string)
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
'hdfs://dw-test-cluster/hive/warehouse/ods/rs/basic/ods_rs_basic_tbd_colorstyle'
TBLPROPERTIES ('parquet.compression'='snappy')
;
-- 写入扑克牌花色到花色表
insert overwrite table ods_rs_basic_tbd_colorstyle partition(event_week='25',event_day='20200618',event_hour='00')
select '黑桃♠'
union all
select '梅花♣'
union all
select '红心❤'
union all
select '方块♦'
;
-- 预览扑克牌花色表
select * from ods_rs_basic_tbd_colorstyle where event_day='20200618';
-- 创建扑克牌花色表
CREATE TABLE `ods_rs_basic_tbd_numstyle`
(
`numstyle` string COMMENT '扑克牌数字'
)
COMMENT 'ods扑克牌数字表'
PARTITIONED BY (
`event_week` int,
`event_day` string,
`event_hour` string)
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
'hdfs://dw-test-cluster/hive/warehouse/ods/rs/basic/ods_rs_basic_tbd_numstyle'
TBLPROPERTIES ('parquet.compression'='snappy')
;
-- 写入扑克牌花色到花色表
insert overwrite table ods_rs_basic_tbd_numstyle partition(event_week='25',event_day='20200618',event_hour='00')
select 'A'
union all
select '2'
union all
select '3'
union all
select '4'
union all
select '5'
union all
select '6'
union all
select '7'
union all
select '8'
union all
select '9'
union all
select '10'
union all
select 'J'
union all
select 'Q'
union all
select 'K'
;
-- 预览扑克牌数字表
select * from ods_rs_basic_tbd_numstyle where event_day='20200618';
set hive.mapred.mode=nonstrict;
-- 查看得到扑克牌的结果
select cs.colorstyle as `扑克花色`
,ns.numstyle as `扑克数字`
,concat(cs.colorstyle,ns.numstyle) as `扑克牌`
from ods_rs_basic_tbd_colorstyle cs
cross join ods_rs_basic_tbd_numstyle ns
on 1=1
where cs.event_day='20200618' and ns.event_day='20200618'
;
cross join(笛卡尔积)查询结果如图11；
left semi join
left semi join（左半连接）是in/exists 子查询的一种更高效的实现;其限制是： join 子句中右边的表只能在on 子句中设置过滤条件，在where 子句、select 子句或其他地
方过滤都不行，说白了就是你不需要最后select出右表的字段，右表只是单纯的左表的过滤条件而已；其实，刚刚也说了，他可以被in/exists代替，废话不多说，进入实战演示环节，以下是实战sql；
-- 将b表存在的classid在a表中找出来，注意b表只能作为过滤条件，不能select b表字段
select a.*
from dw.ods_rs_basic_tbd_student a
left semi join dw.ods_rs_basic_tbd_class b
on (a.classid = b.classid)
;
-- 等价于in的实现
select a.*
from dw.ods_rs_basic_tbd_student a
where a.classid in
(select b.classid
from dw.ods_rs_basic_tbd_class b);
-- 等价于exists的实现
select a.*
from dw.ods_rs_basic_tbd_student a
where exists (select 1 from dw.ods_rs_basic_tbd_class b where a.classid = b.classid);
left semi join结果如图12所示；
注意： left semi join与内连接外连接的区别就在于，最后的select结果只能是左表的字段，右表只是单纯的起到过滤条件的作用，如果select出来b表的字段，那么是会报错的，如图13；
union 和union all
union 和union all的实现效果和MySQL一样，union返回两个查询集合去重的结果，而union all返回的是两个查询集合的所有的结果，不去重，具体Hive-SQL语句如下；
-- union sql
select 'A'
union
select 'A'
;
-- union all sql
select 'A'
union all
select 'A'
;
union结果如图14
union all结果如图15
938
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
