Hive不依赖于HDFS hive的sql语句

一、HIVE SQL 语法

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  FROM tablename
  [WHERE where_condition] --where条件语句
  [GROUP BY col_list]  --group by 分组语句
  [ORDER BY col_list]  --order by 排序语句 
  [CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list] ]
 [LIMIT number]

大家都知道hive底层跑的是mapreduce程序，在执行hql语句时，mapreduce会解析hql产生执行逻辑，生成结果。

 

MapReduce引擎执行计划

Map阶段：

1、执行from加载，进行表的查找与加载。

2、执行where过滤，进行条件过滤与筛选。

3、执行select查询，进行输出项的筛选。

4、执行group by分组，描述了分组后需要计算的函数。

5、map端文件合并，map端本地溢出写文件的合并操作，每个map最终形成一个临时文件。然后按列映射到对应的Reduce阶段：

Reduce阶段：

1、group by：对map端发送过来的数据进行分组并进行计算。

2、select：最后过滤列用于输出结果。

3、limit排序后进行结果输出到HDFS文件。

注意，以上顺序不是绝对的，会根据语句的不同，有所调整。

可以通过执行计划查看大概顺序

explain sql 语句
如：explain select * from tablename 即可查看执行计划

二、Hive Join

Hive只支持等连接、外连接。Hive不支持非相等的Join条件，不过我们可以通过其他方式实现，如left outer join，因为它很难在map/reduce job实现这样的条件。建表对Join操作进行试验，建表语句和数据如下：

//我们创建三个表用来测试
CREATE TABLE testA(
    id INT COMMENT 'id',
    name STRING COMMENT '姓名',
    age INT COMMENT '年龄'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

CREATE TABLE testB(
    id INT COMMENT 'id',
    name STRING COMMENT '姓名',
    age INT COMMENT '年龄'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

CREATE TABLE testC(
    id INT COMMENT 'id',
    name STRING COMMENT '姓名',
    age INT COMMENT '年龄'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

INSERT INTO TABLE testA VALUES (1,"唐三",20);
INSERT INTO TABLE testA VALUES (2,"石昊",21);
INSERT INTO TABLE testA VALUES (5,"林动",18);
INSERT INTO TABLE testA VALUES (6,"萧炎",17);
INSERT INTO TABLE testB VALUES (1,"周元",19);
INSERT INTO TABLE testB VALUES (3,"李明",22);
INSERT INTO TABLE testB VALUES (4,"叶修",23);
INSERT INTO TABLE testB VALUES (5,"吴邪",17);
INSERT INTO TABLE testC VALUES (1,"孙无",24);
INSERT INTO TABLE testC VALUES (2,"沙沙",25);
INSERT INTO TABLE testC VALUES (6,"唐四",21);
INSERT INTO TABLE testC VALUES (7,"叶凡",20);

 

2.1、两表的inner join

SELECT
    a.id id,
    a.name aname,
    a.age aage,
    --b.id bid,
    b.name bname,
    b.age bage 
FROM
    testa a 
INNER JOIN
    testb b  
ON a.id = b.id

结果：
1   唐三        20      周元    19
5   林动        18      吴邪    17

 

2.2、外连接：left join 和 right join

--left join
SELECT
    a.id id,
    a.name aname,
    a.age aage,
    b.name bname,
    b.age bage 
FROM
    testa a 
LEFT JOIN
    testb b  
ON a.id = b.id

结果：
1   唐三      20     周元     19
2   石昊      21     NULL    NULL
5   林动      18     吴邪     17
6   萧炎      17     NULL    NULL

--right join
SELECT
    a.id id,
    a.name aname,
    a.age aage,
    b.name bname,
    b.age bage 
FROM
    testa a 
RIGHT JOIN
    testb b  
ON a.id = b.id

结果：
1   唐三      20      周元    19
NULL NULL   NULL      李明    22
NULL NULL   NULL      叶修    23
5   林动      18      吴邪    17

 

2.3、 实现非等值连接

查询testa有，testb没有，用 left join + is null

查询testa没有，testb有，用 right join + is null

--查询testa有，testb没有
SELECT
    a.id aid,
    a.name aname,
    a.age aage,
    b.id bid,
    b.name bname,
    b.age bage 
FROM
    testa a 
LEFT JOIN
    testb b  
ON 
    a.id = b.id
WHERE   
    b.id IS NULL 
结果：
2   石昊  21  NULL    NULL    NULL
6   萧炎  17  NULL    NULL    NULL
--查询testa没有，testb有。同理，如上把b.id改为a.id即可。

注意下面的Sql和上面的Sql结果是不一样的：
SELECT
    a.id aid,
    a.name aname,
    a.age aage,
    b.id bid,
    b.name bname,
    b.age bage 
FROM
    testa a 
LEFT JOIN
    testb b  
ON 
    a.id = b.id
AND    
    b.id IS NULL 
结果：
1   唐三  20  NULL    NULL    NULL
2   石昊  21  NULL    NULL    NULL
5   林动  18  NULL    NULL    NULL
6   萧炎  17  NULL    NULL    NULL

 

2.4、多表join

--笛卡尔积的SQL
select 
    a.id id,
    a.name aname,
    a.age aage,
    b.name bname,
    b.age bage 
from testa a
inner join testb b;

简单应用：使用join计算新增用户

--计算新增用户
select count(1) from
(select uid from user_table where dt='20200506' group by uid) a
right join
(select uid from user_table where dt='20200507' group by uid) b
on b.uid=a.uid
where a.uid is null;

 

2.5、使用join时要避免的查询操作

--笛卡尔积的SQL
select 
    a.id id,
    a.name aname,
    a.age aage,
    b.name bname,
    b.age bage 
from testa a
inner join testb b;

设置 set hive.mapred.mode=strict 这个参数，可以限制以下情况：

1）限制执行可能形成笛卡尔积的SQL；

2）partition表使用时不加分区；

3）order by全局排序的时候不加limit的情况；

取消限制：set hive.mapred.mode=nonstrict

 

2.6、full outer join

full outer join意思是包括两个表join的结果。左边有、右边NULL，右边有、左边NULL。其结果等价于left join union right join

select 
    a.id id,
    a.name aname,
    a.age aage,
    b.name bname,
    b.age bage 
from testa a
full outer join testb b
on a.id = b.id

结果：
1   唐三    20    周元  19
6   萧炎    17    NULL  NULL  
NULL NULL  NULL   李明  22
5   林动    18    吴邪  17
NULL NULL  NULL   叶修  23
2   石昊    21    NULL NULL

使用union 实现 full outer join效果

select 
    a.id id,
    a.name aname,
    a.age aage,
    b.name bname,
    b.age bage 
from testa a
LEFT JOIN  testb b
on a.id = b.id
UNION 
select 
    a.id id,
    a.name aname,
    a.age aage,
    b.name bname,
    b.age bage 
from testa a
RIGHT  JOIN  testb b
on a.id = b.id

 

2.7、map端的join操作

当大表 join 小表时，小表没有运行Map，两个表连在一起也没有用Reduce，直接跳过，将小表数据刷入内存，默认是自动mapjoin。

hive 默认是开启 MapJoin/*+ MAPJOIN(c) */

//将小表刷入内存中，默认是true
set hive.auto.convert.join=true
set hive.ignore.mapjoin.hint=true
 //刷入内存表的大小(字节)，根据自己的数据集加大
set hive.mapjoin.smalltable.filesize=2000000

 

不开启MapJoin,可以按照如下设置

set hive.auto.convert.join=false;
set hive.ignore.mapjoin.hint=false;

 

在设置成false 或 true时，可以手动的 /*+ MAPJOIN(c) **/

select /*+ MAPJOIN(c) */ * from user_table_a a
inner join user_table_b b
on a.id=b.id
where a.dt='20200507'

 

注意：map join 类似于 MapReduce中的semijoin。

当把set hive.auto.convert.join设置成false时，可以手动的加 /*+ MAPJOIN(c) ***/外连接不允许用mapjoin ，内连接可以用mapjoin / *+ MAPJOIN(c) */把c放到内存。