HQL数据查询（DQL）语言

HQL数据查询（DQL）语言

大数据资源一连更新中。。。

一、HQL根本查询（回顾）

1、回顾MySQL五子句

1. select [*/all/distinct] from 数据表 where子句 group by子句 having子句 order by子句 limit子句;
3. ① where子句，根据条件筛选源表中数据
4. ② group by子句，分组子句，按照某个字段进行分组
5. ③ having子句，针对分组后的数据在进行数据筛选
6. ④ order by子句，排序子句，order by 字段 asc，order by 字段 desc
7. ⑤ limit子句，限制查询数量，limit 3，也被称为分页查询子句，limit offset,number;
9. 100条数据，分页，每页显示10数据，问第5页的SQL语句如何去编写？
10. select * from 数据表 limit 40,10;

复制代码

2、Hive七子句（自界说）

1. ① cte子句
2. with 临时表名 as(
3.         select [*/all/distinct] from 数据表 ② where子句 ③ group by子句 ④ having子句 ⑤ order by子句 ⑥ [cluster by 字段 | distribute by 字段 | sort by 字段] ⑦ limit子句;
4. )
5. select * from 临时表名;
7. ① cte子句 => 类似之前讲过的子查询
8. ② where子句
9. ③ group by子句
10. ④ having子句
11. ⑤ order by子句
12. ⑥ 排序子句cluster by子句/distribute by子句/sort by子句 => 可以针对组内进行排序操作
13. ⑦ limit子句

复制代码

☆ 案例：美国Covid-19新冠疫情数据

下面来准备一下语法测试环境，在附件资料中有一份数据文件《us-covid19-counties.dat》，内里记载了2021-01-28美国各个县累计新冠确诊病例数和累计殒命病例数。
在Hive中创建表，加载该文件到表中：

1. --step1:创建普通表t_usa_covid19
2. drop table t_usa_covid19;
3. CREATE TABLE t_usa_covid19(
4.        count_date string,
5.        county string,
6.        state string,
7.        fips int,
8.        cases int,
9.        deaths int
10. )
11. row format delimited fields terminated by ",";
12. --将源数据load加载到t_usa_covid19表对应的路径下
13. load data local inpath '/root/us-covid19-counties.dat' into table t_usa_covid19;
15. --step2:创建一张分区表 基于count_date日期,state州进行分区
16. CREATE TABLE t_usa_covid19_p(
17.      county string,
18.      fips int,
19.      cases int,
20.      deaths int
21. ) partitioned by(count_date string,state string)
22. row format delimited fields terminated by ",";
24. --step3:使用动态分区插入将数据导入t_usa_covid19_p中
25. set hive.exec.dynamic.partition.mode = nonstrict;
27. insert into table t_usa_covid19_p partition (count_date,state)
28. select county,fips,cases,deaths,count_date,state from t_usa_covid19;

复制代码

案例：

1. -- 查询当前数据库
2. select current_database(); --省去from关键字
4. -- 切换数据库
5. use db_itheima;
7. -- 查询使用函数
8. select count(county) from t_usa_covid19_p;
9. select count(1) from t_usa_covid19_p;
10. 3425
11. -- 问题：count(列名)和count(1)有何区别?
12. -- 答：两者都可以实现对数据表中的记录进行统计，在数据量相同的情况下，count(1)速度要略快于count(列名)，另外一点不同主要在于字段的值，如果字段的值为null，则count会忽略统计此行，而count(1)无论此字段是否为空，都会将其统计为1，所以这就是两者的区别。
14. -- 返回所有匹配的行
15. select state from t_usa_covid19_p;
16. -- 相当于
17. select all state from t_usa_covid19_p;
19. -- 返回所有匹配的行 去除重复的结果
20. select distinct state from t_usa_covid19_p;
21. -- 多个字段distinct 整体去重
22. select distinct county,state from t_usa_covid19_p;  -- 把country + state当做一个整体去重
24. -- where查询
25. select * from t_usa_covid19_p where state = "California" and deaths > 1000;
26. select * from t_usa_covid19_p where 1 > 2;  -- 1 > 2 返回false
27. select * from t_usa_covid19_p where 1 = 1;  -- 1 = 1 返回true
29. -- where条件中使用函数 找出州名字母超过10个
30. select * from t_usa_covid19_p where length(state) >10 ;
32. --WHERE子句支持子查询
33. SELECT *
34. FROM A
35. WHERE A.a IN (SELECT foo FROM B);
37. --where条件中不能使用聚合函数
38. --报错 SemanticException:Not yet supported place for UDAF 'sum'
39. select state,sum(deaths)
40. from t_usa_covid19_p where sum(deaths) >100 group by state;

复制代码

那么为什么不能在where子句中使用聚合函数呢？
由于聚合函数要使用它的条件是结果集已经确定。而where子句还处于“确定”结果集的过程中，因而不能使用聚合函数。
3、分区查询与分区裁剪

通常，SELECT查询将扫描整个表（所谓的全表扫描）。如果使用PARTITIONED BY子句创建的分区表，则在查询时可以指定分区查询，镌汰全表扫描，也叫做分区裁剪。
   如果select语句没有采取分区裁剪，默认照旧使用全表扫描！
  所谓分区裁剪指的是：对分区表举行查询时，会查抄WHERE子句或JOIN中的ON子句中是否存在对分区字段的过滤，如果存在，则仅访问查询符合条件的分区，即裁剪掉没须要访问的分区。
① where子句
② join子句
③ on子句

1. -- 找出来自加州，累计死亡人数大于1000的县 state字段就是分区字段 进行分区裁剪 避免全表扫描
2. select * from t_usa_covid19_p where state ="California" and deaths > 1000;
4. -- 多分区裁剪
5. select * from t_usa_covid19_p where count_date = "2021-01-28" and state ="California" and deaths > 1000;

复制代码

4、group by分组

GROUP BY 语句用于团结聚合函数，根据一个或多个列对结果集举行分组。必要留意的是，出现在GROUP BY中select_expr的字段：==要么是GROUP BY分组的字段；要么是被聚合函数应用的字段。==缘故原由很简单，克制出现一个字段多个值的歧义。
分组字段出现select_expr中，肯定没有歧义，由于就是基于该字段分组的，同一组中必雷同；
被聚合函数应用的字段，也没歧义，由于聚合函数的本质就是多进一出，终极返回一个结果。

如上图所示，基于category举行分组，雷同颜色的分在同一组中。
在select_expr中，如果出现category字段，则没有标题，由于同一组中category值一样，但是返回day就有标题了，day的结果不一样。
Hive => 把SQL语句翻译成底层的MR步伐
SQL语句 => Hive（表明器、编译器、优化器、实行器）=> MR步伐
扩展：group by工作原理（相识）

口试题：请简述你在Hive中遇到过哪些标题？Hive有哪些优化本领？
数据倾斜概念：简单来说就是一个大的使命会分别为多个MapTask，在Map阶段会实行分区、排序、规约操纵，但是如果某个分区的数据较多（凌驾了其他全部分区），在Reduce阶段拉取数据时，会导致某个Reduce长时间无法完成。Reduce无法完成也会导致终极的使命无法完成，我们把这种环境就称之为数据倾斜！
Map
​ 男 Reduce（男） 99% 长时间无法完成
Map
​ 女 Reduce（女） 1% 很快完成
Map
   在group by分组是最容易出现数据倾斜标题的
  答：① 分区、分桶 ② 开启Map端聚合（Map把数据分别为一个一个MapTask使命，Reduce通常是举行数据归并，默认环境下，全部的分组聚合操纵都是发生在Reduce这一端，如果数据量比力大的环境下，就会导致耗时较长大概使命长时间无法完成）
   Map端聚合 => 还可以办理未来数据倾斜的标题
  Map聚合 => 优化本领
默认设置了set hive.map.aggr=true，会在mapper端先group by一次，末了把结果merge起来，为了镌汰reducer处置惩罚的数据量。

1. select rank, isonline, count(1) as value from city group by rank, isonline;

复制代码

我们都知道Hive底层跑的是MR步伐，相称于把SQL语句翻译成MR举行盘算。
假设city数据表空间巨细（HDFS占用空间） = 256MB

Map阶段：
MapTask使命，读取源数据，得到k1，v1（k1行号，v1相称于数据表中的一行数据）
调用map()方法，举行数据拆解，由k1，v1 得到 k2，v2（k2分组rank+isonline，v2行数，默以为1）
经太过区、排序、规约，得到分好区、排好序、规好约的文件
Reduce阶段：
Reduce端，主动拉取属于本身分区的数据，把数据举行分组聚合操纵，雷同k2的数据汇总在一起，得到k3和v3，k3就是rank + isonline，v3每个组总行数。
5、having子句

在SQL中增长HAVING子句缘故原由是，WHERE关键字无法与聚合函数一起使用。
① where子句 ② group by分组 ③ having子句
HAVING子句可以让我们筛选分组后的各组数据,而且可以在Having中使用聚合函数，由于此时where，group by已经实行竣事，结果集已经确定。

1. -- 统计死亡病例数大于10000的州
2. select state,sum(deaths) as cnts
3. from t_usa_covid19_p
4. where count_date = "2021-01-28"
5. group by state
6. having cnts > 10000;

复制代码

having与where的区别:
□ having是在分组后对数据举行过滤
□ where是在分组前对数据举行过滤
□ having反面可以使用聚合函数 having cnts > 10000;
□ where反面不可以使用聚合 where发生的时间，数据还没有举行聚合呢！
6、limit子句

LIMIT子句可用于束缚SELECT语句返回的行数。
LIMIT担当一个或两个数字参数，这两个参数都必须好坏负整数常量。
第一个参数指定要返回的第一行的偏移量（从 Hive 2.0.0开始），第二个参数指定要返回的最大行数。当给出单个参数时，它代表最大行数，而且偏移量默以为0。

1. select * from t_usa_covid19_p
2. where count_date = "2021-01-28"
3.   and state ="California"
4. limit 5;

复制代码

二、HQL排序查询与高级查询

1、SORT/ORDER/CLUSTER BY/DISTRIBUTE BY

[口试题]：请分析四个by排序查询有何区别？

order by：全局排序，只有一个Reduce，数量比力大，性能最差
cluster by：按照同一个字段举行分区（分散为多个Reduce），区内有序（只能是升序分列）
distribute by + sort by：distribute by就是按照某个字段分区，sort by可以按照某个字段排序（既可以升序也可以降序）
☆ ORDER BY

全局数据有序
Hive SQL中的ORDER BY语法雷同于SQL语言中的ORDER BY语法。会对输出的结果举行全局排序，因此底层使用MapReduce引擎实行的时间，只会有一个reducetask实行。也因此，如果输出的行数太大，会导致必要很长的时间才气完玉成局排序。
默认排序序次为升序（ASC），也可以指定为DESC降序。
在Hive 2.1.0和更高版本中，支持在“ order by”子句中为每个列指定null范例结果排序序次。ASC序次的默认空排序序次为NULLS FIRST，而DESC序次的默认空排序序次为NULLS LAST。
   在Hive中，默认会以为NULL空值为最小值

1. ---order by
2. --根据字段进行排序
3. select * from t_usa_covid19_p
4. where count_date = "2021-01-28"
5. and state ="California"
6. order by deaths; --默认asc null first
8. select * from t_usa_covid19_p
9. where count_date = "2021-01-28"
10. and state ="California"
11. order by deaths desc; --指定desc null last
13. --强烈建议将LIMIT与ORDER BY一起使用。避免数据集行数过大
14. --当hive.mapred.mode设置为strict严格模式时，使用不带LIMIT的ORDER BY时会引发异常。
15. select * from t_usa_covid19_p
16. where count_date = "2021-01-28"
17.   and state ="California"
18. order by deaths desc
19. limit 3;

复制代码

  Hive中如果盘算的数量较大，造成内存溢出 => return code 1
  ☆ CLUSTER BY

Hive SQL中的CLUSTER BY语法可以指定根据反面的字段将数据分区，每个区内再根据这个字段asc正序排序（不答应指定排序规则），概况起来就是：根据同一个字段，分区排序。
cluster by 字段名称，重要做了两件事：① 按照这个字段分区 ② 针对每个区中的数据按照这个字段举行升序排序（记取，只能做到升序排序）
   留意：使用了cluster by必须人为指定分组的数量 => Reduce数量
  分组的规则hash散列。hash(col_name) % reduce task nums
sno门生编号
1 % 3 = 1
2 % 3 = 2
3 % 3 = 0
假设只必要分为2个组 => 2个reduce
hash(1) % 2 = 1
hash(2) % 2 = 0
hash(3) % 2 = 1
分为几个区取决于reduce task的个数。下面在Hive beeline客户端中针对student表举行演示。

1. --cluster by
2. select * from student;
3. --不指定reduce task个数
4. --日志显示：Number of reduce tasks not specified. Estimated from input data size: 1
5. select * from student cluster by sno;
7. --手动设置reduce task个数
8. set mapreduce.job.reduces = 2;
9. select * from student cluster by sno;

复制代码

默认环境下，reduce task的个数由Hive在编译期间本身决定。

设置set mapreduce.job.reduces = 2;

实行结果如下：分为两个部分，每个部分内正序排序。
95001 % 2 = 1
95002 % 2 = 0

如果说，现在想法如下：把门生表数据根据性别分为两个部分，每个分组内根据年岁的倒序排序。你会发现CLUSTER BY无法完成了。而order by更不能在这里使用，由于它是全局排序，一旦使用order by，编译期间就会逼迫把reduce task个数设置为1。无法满意分组的需求。
☆ DISTRIBUTE BY + SORT BY

如果说CLUSTER BY的功能是分组排序（同一个字段），那么DISTRIBUTE BY + SORT BY就相称于把cluster by的功能一分为二：DISTRIBUTE BY负责分区，SORT BY负责分组内排序，而且可以是差别的字段。如果DISTRIBUTE BY +SORT BY的字段一样，可以得出下列结论：
CLUSTER BY=DISTRIBUTE BY +SORT BY（字段一样）

1. --手动设置reduce task个数
2. set mapreduce.job.reduces = 2;
4. --案例：把学生表数据根据性别分为两个部分，每个分组内根据年龄的倒序排序。
5. select * from student distribute by sex sort by sage desc;

复制代码

1. --下面两个语句执行结果一样
2. select * from student distribute by sno sort by sno;
3. select * from student cluster by sno;

复制代码

☆ 小结

 order by会对输入做全局排序，因此只有一个reducer，会导致当输入规模较大时，必要较长的盘算时间。
 sort by不是全局排序，其在数据进入reducer前完成排序。因此，如果用sort by举行排序，而且设置mapred.reduce.tasks>1，则sort by只包管每个reducer的输出有序，不包管全局有序。
 distribute by(字段)根据指定字段将数据分到差别的reducer，分发算法是hash散列。
 cluster by(字段) 除了具有distribute by的功能外，还会对该字段举行排序。
如果distribute和sort的字段是同一个时，此时，cluster by = distribute by + sort by

2、Union团结查询

   留意：字段同等，重要代表字段的数量要同一，否则语法会报错！
  UNION用于未来自多个SELECT语句的结果归并为一个结果集。语法如下：

1. select_statement UNION [ALL | DISTINCT] select_statement UNION [ALL | DISTINCT] select_statement ...

复制代码

使用DISTINCT关键字与只使用UNION默认值结果一样，都会删除重复行。
使用ALL关键字，不会删除重复行，结果集包罗全部SELECT语句的匹配行（包罗重复行）。
1.2.0之前的Hive版本仅支持UNION ALL，在这种环境下不会消除重复的行。
每个select_statement返回的列的数量和名称必须雷同。
MySQL有一个概念：分库/分表 => 10万条数据，table1 2万 table2 2万 table3 2万 …
在MySQL中优化中，有如许一个概念：分库分表
把数据分散在多个数据库与多个数据表中，减轻单表压力

1. --union
2. --使用DISTINCT关键字与使用UNION默认值效果一样，都会删除重复行。
3. select num,name from student_local
4. UNION
5. select num,name from student_hdfs;
6. --和上面一样
7. select num,name from student_local
8. UNION DISTINCT
9. select num,name from student_hdfs;
11. --使用ALL关键字会保留重复行。
12. select num,name from student_local
13. UNION ALL
14. select num,name from student_hdfs;
16. --如果要将ORDER BY，SORT BY，CLUSTER BY，DISTRIBUTE BY或LIMIT应用于单个SELECT
17. --请将子句放在括住SELECT的括号内
18. SELECT sno,sname FROM (select sno,sname from student_local LIMIT 2) subq1
19. UNION
20. SELECT sno,sname FROM (select sno,sname from student_hdfs LIMIT 3) subq2
22. --如果要将ORDER BY，SORT BY，CLUSTER BY，DISTRIBUTE BY或LIMIT子句应用于整个UNION结果
23. --请将ORDER BY，SORT BY，CLUSTER BY，DISTRIBUTE BY或LIMIT放在最后一个之后。
24. select sno,sname from student_local
25. UNION
26. select sno,sname from student_hdfs
27. order by sno desc;

复制代码

3、Common Table Expressions（CTE）

CTE也可以明白为子查询的一种，只不外CTE语句中的子查询要先实行。
CTE/窗口函数
MySQL8.0以及Hive中存在！
   MySQL8.0以后增长了两个比力突出功能：① 窗口函数 ② CTE表达式
  CTE先容
==公用表达式（CTE）==是一个暂时结果集，该结果集是从WITH子句中指定的简单查询派生而来的，该查询紧接在SELECT或INSERT关键字之前。 => 子查询
CTE仅在单个语句的实行范围内界说。一个或多个CTE可以在Hive SELECT，INSERT， CREATE TABLE AS SELECT或CREATE VIEW AS SELECT语句中使用。
根本语法：

1. with 临时表名称 as (
2.         select 查询语句
3. )
4. select * from 临时表名称 where xxx;
6. 等价于子查询
8. select * from (select * from 数据表) as 临时表 where xxx;
10. PS：
11. CTE与子查询虽然都是子查询操作的一种，但是相对而言，CTE表达式性能更高，先把select查询结果写入到临时表，二次、三次甚至多次查询都可以反复使用已经创建好的临时表，不需要重复计算。
12. select * from (select * from 数据表) as 临时表 where xxx; 子查询有所不同，每次在使用到子查询()内的查询结果时，()中的select需要重复计算，所以性能相对于CTE要略低一些。

复制代码

案例：每个国家顾客评价最高的市肆地点都会（topN标题）
​ rank
中国 广州 6 2
中国 深圳 9 1
法国 巴黎 7 1
法国 波尔多 7 1
德国 慕尼黑 3 1
日本 东京 8 1
俄罗斯 莫斯科 7 1
   topN标题以后起首就要想到使用CTE表达式
    求每个班级中结果最高的前3名，求每个部分薪资最高的谁职员工信息…
    分组 + 排序 + 排名（只要最高的，只要第1名）=> CTE表达式中，然后select筛选！！！
  with语句 + 窗口函数（partition by order by + 排名函数 => dense_rank()/rank()）

1. WITH ranking AS (
2.   SELECT
3.     country,
4.     city,
5.     dense_rank() OVER(PARTITION BY country ORDER BY rating DESC) AS `rank`
6.   FROM store
7. )
8. SELECT
9.   country,
10.   city
11. FROM ranking
12. WHERE `rank` = 1;
14. 注意：并列且不连续用rank()，并列且连续用dense_rank()，具体使用哪种，根据业务实现

复制代码

① 通过在CTE中使用窗口函数来获取每个国家/地区按评分的市肆排名
② 在外部查询中直接查询每个国家分数最高的店肆地点都会
三、Hive JOIN毗连查询（重点）

在Hive中，当下版本3.1.2统共支持6种join语法。分别是：
inner join（内毗连）、left join（左外毗连）、right join（右外毗连）、full outer join（全外毗连）、left semi join（左半开毗连）、cross join（交织毗连，也叫做笛卡尔积）。
1、数据集准备

为了更好的练习、学习把握Hive中的join语法，下面我们去创建3张表而且加载数据到表中。
表1：employee 员工表；
表2：employee_address 员工住址信息表；
表3：employee_connection 员工接洽方式表；

1. --table1: 员工表
2. CREATE TABLE employee(
3.    id int,
4.    name string,
5.    deg string,
6.    salary int,
7.    dept string
8. ) row format delimited
9. fields terminated by ',';
11. --table2:员工住址信息表
12. CREATE TABLE employee_address (
13.     id int,
14.     hno string,
15.     street string,
16.     city string
17. ) row format delimited
18. fields terminated by ',';
20. --table3:员工联系方式表
21. CREATE TABLE employee_connection (
22.     id int,
23.     phno string,
24.     email string
25. ) row format delimited
26. fields terminated by ',';
28. --加载数据到表中
29. load data local inpath '/root/hivedata/employee.txt' into table employee;
30. load data local inpath '/root/hivedata/employee_address.txt' into table employee_address;
31. load data local inpath '/root/hivedata/employee_connection.txt' into table employee_connection;

复制代码

2、Hive inner join（简写为join）

内毗连 是最常见的一种毗连，它也被称为平常毗连，而关系模子提出者E.FCodd（埃德加•科德）最早称之为天然毗连。此中inner可以省略。inner join == join 等价于早期的毗连语法。
根本语法：

1. select * from 数据表A inner join 数据表B on A表.字段 = B表.字段;
2. 简写为
3. select * from 数据表A join 数据表B on A表.字段 = B表.字段;

复制代码

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

1. --1、inner join
2. select e.id,e.name,e_a.city,e_a.street
3. from employee e inner join employee_address e_a
4. on e.id =e_a.id;
5. --等价于 inner join=join
6. select e.id,e.name,e_a.city,e_a.street
7. from employee e join employee_address e_a
8. on e.id =e_a.id;
10. --等价于 隐式连接表示法
11. select e.id,e.name,e_a.city,e_a.street
12. from employee e , employee_address e_a
13. where e.id =e_a.id;

复制代码

运行结果：

3、Hive left join

left join中文叫做是左外毗连(Left Outer Jion)大概左毗连，此中outer可以省略，left outer join是早期的写法。
left join的核心就在于left左。左指的是join关键字左边的表，简称左表。
平常表明：join时以左表的全部数据为准，右边与之关联；左表数据全部返回，右表关联上的表现返回，关联不上的表现null返回。

1. --2、left join
2. select e.id,e.name,e_conn.phno,e_conn.email
3. from employee e left join employee_connection e_conn
4. on e.id =e_conn.id;
6. --等价于 left outer join
7. select e.id,e.name,e_conn.phno,e_conn.email
8. from employee e left outer join  employee_connection e_conn
9. on e.id =e_conn.id;

复制代码

4、Hive right join

right join中文叫做是右外毗连(Right Outer Jion)大概右毗连，此中outer可以省略。
right join的核心就在于Right右。右指的是join关键字右边的表，简称右表。
平常表明：join时以右表的全部数据为准，左边与之关联；右表数据全部返回，左表关联上的表现返回，关联不上的表现null返回。
很显着，right join和left join之间很相似，重点在于以哪边为准，也就是一个方向的标题。

1. --3、right join
2. select e.id,e.name,e_conn.phno,e_conn.email
3. from employee e right join employee_connection e_conn
4. on e.id =e_conn.id;
6. --等价于 right outer join
7. select e.id,e.name,e_conn.phno,e_conn.email
8. from employee e right outer join employee_connection e_conn
9. on e.id =e_conn.id;

复制代码

运行结果：

5、Hive full outer join

full outer join 等价 full join,中文叫做全外毗连大概全毗连。
包罗左、右两个表的全部行，不管别的一边的表中是否存在与它们匹配的行在功能上，它等价于对这两个数据聚集分别举行左外毗连和右外毗连，然后再使用消去重复行的操纵将上述两个结果集归并为一个结果集。

1. --4、full outer join
2. select e.id,e.name,e_a.city,e_a.street
3. from employee e full outer join employee_address e_a
4. on e.id =e_a.id;
5. --等价于
6. select e.id,e.name,e_a.city,e_a.street
7. from employee e full join employee_address e_a
8. on e.id =e_a.id;

复制代码

运行结果：

6、Hive left semi join

左半开毗连（LEFT SEMI JOIN）会返回左边表的记载，条件是其记载对于右边的表满意ON语句中的判断条件。
   从结果上来看有点像inner join之后只返回左表的结果。

1. --5、left semi join
2. select *
3. from employee e left semi join employee_address e_addr
4. on e.id =e_addr.id;
6. --相当于 inner join 只不过效率高一些
7. select e.*
8. from employee e inner join employee_address e_addr
9. on e.id =e_addr.id;

复制代码

7、Hive cross join

交织毗连cross join，将会返回被毗连的两个表的笛卡尔积，返回结果的行数便是两个表行数的乘积。对于大表来说，cross join慎用。
   cross join本身没有任何意义，但是其是全部毗连查询的根本！
  根本语法：

1. select * from A表 cross join B表;
3. 返回结果：
4. 字段数：A表字段 + B表字段
5. 记录数：A表记录数 * B表记录数（笛卡尔积）

复制代码

在SQL尺度中界说的cross join就是无条件的inner join。返回两个表的笛卡尔积,无需指定关联键。
在HiveSQL语法中，cross join 反面可以跟where子句举行过滤，大概on条件过滤。

1. --6、cross join
2. --下列A、B、C 执行结果相同，但是效率不一样：
3. --A:
4. select a.*,b.* from employee a,employee_address b where a.id=b.id;
6. --B:
7. select * from employee a cross join employee_address b on a.id=b.id;
8. select * from employee a cross join employee_address b where a.id=b.id;
10. --C:
11. select * from employee a inner join employee_address b on a.id=b.id;
13. --一般不建议使用B，因为如果有WHERE子句的话，往往会先进行笛卡尔积返回数据然后才根据WHERE条件从中选择。
14. --因此，如果两个表太大，将会非常非常慢，不建议使用。

复制代码

扩展：Hive JOIN底层原理

Hive => Hive Join => MR => MapReduce实行原理：
① Map阶段：构建（key(tag),value）,key这里反面的数字是tag，反面在reduce阶段用来区分来自于谁人表的数据，对key求hashcode设为hivekey；
Map阶段 => 得到k1，v1 => （key(tag), value）=> key毗连字段（uid，其他字段结果）=> tag标记数据表，未来4个字段到底谁放在前面谁放在反面。想个办法，给每个表添加一个标记，前面的就是0，反面的就是1
② Shuffle机制：如果key在差别呆板上，会通过网络传输把hivekey雷同的数据搜集到一台呆板；
③ Reduce阶段：把tag=1的内容，都加到tag=0的反面，归并输出。

uid, uname, uid, wid
uid wid
1（R1） uid = 1 101
1（R1） uid = 1 102
2（R2） uid = 2 103
8、Hive JOIN使用留意事项（相识）

总体来说，随着Hive的版本发展，join语法的功能也愈加丰富。当下我们课程使用的是3.1.2版本，有以下几点必要留意：
a) 答应使用复杂的联接表达式

1. SELECT a.* FROM a JOIN b ON (a.id = b.id)
2. SELECT a.* FROM a JOIN b ON (a.id = b.id AND a.department = b.department)
3. SELECT a.* FROM a LEFT OUTER JOIN b ON (a.id <> b.id)

复制代码

b) 同一查询中可以毗连2个以上的表

1. SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

复制代码

c) 如果每个表在联接子句中使用雷同的列，则Hive将多个表上的联接转换为单个MR作业
A join B join C
A join B => 1个MR，结果 join C => 1个MR

1. SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)--由于联接中仅涉及b的key1列，因此被转换为1个MR作业来实行SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)
2. --会转换为两个MR作业，由于在第一个毗连条件中使用了b中的key1列，而在第二个毗连条件中使用了b中的key2列。第一个map / reduce作业将a与b联接在一起，然后将结果与c联接到第二个map / reduce作业中。

复制代码

d) join时的末了一个表会通过reducer流式传输，并在此中缓冲之前的其他表，因此，将大表放置在末了有助于镌汰reducer阶段缓存数据所必要的内存
select *from A表 join B表 on A.id = B.id;
A表 大表
B表 小表
Hive底层：小表缓冲在内存，大表拆分多少个小表，然后在举行关联。
   Hive优化有如许一条：大表和小表举行毗连，则小表放在前面，大表放在反面！
  大表(100GB) join 小表（10GB => 128MB/128MB），得到一个大表，再次和小表 join，相称于大表全程到场了！

1. SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)--由于联接中仅涉及b的key1列，因此被转换为1个MR作业来实行，而且表a和b的键的特定值的值被缓冲在reducer的内存中。然后，对于从c中检索的每一行，将使用缓冲的行来盘算联接。SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)
2. --盘算涉及两个MR作业。此中的第一个将a与b毗连起来，并缓冲a的值，同时在reducer中流式传输b的值。在第二个MR作业中，将缓冲第一个毗连的结果，同时将c的值通过reducer流式传输。

复制代码

e) 在join的时间，可以通过语法STREAMTABLE提示指定要流式传输的表。如果省略STREAMTABLE提示，则Hive将流式传输最右边的表。
/*+ STREAMTABLE(a) */ 逼迫按照某个方案实行，默认传输末了一个，但是给了此参数，则逼迫将此表作为流式传输的表。逼迫大表

1. SELECT /*+ STREAMTABLE(a) */ a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)
2. --a,b,c三个表都在一个MR作业中联接，并且表b和c的键的特定值的值被缓冲在reducer的内存中。然后，对于从a中检索到的每一行，将使用缓冲的行来计算联接。如果省略STREAMTABLE提示，则Hive将流式传输最右边的表。

复制代码

f) join在WHERE条件之进步行。=> 现有join然后才有where
g) 如果除一个要毗连的表之外的全部表都很小，则可以将其作为仅map作业实行

1. SELECT /*+ MAPJOIN(b) */ a.key, a.value FROM a JOIN b ON a.key = b.key
2. --不需要reducer。对于A的每个Mapper，B都会被完全读取。限制是不能执行FULL / RIGHT OUTER JOIN b。

复制代码

另有一些其他干系的使用留意事项，可以参考官方
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Joins
三、Hive参数设置

1、设置属性概述

Hive作为一款复杂的数据堆栈软件，除了一些默认的属性举动之外，还支持用户设置属性举行修改，使得在某些场景下满意用户的需求。
作为用户我们必要把握两件事：
一是：Hive有哪些属性支持修改，修改了有什么功能；
二是：Hive支持哪种方式举行修改，修改是暂时收效照旧永世收效的。
Hive设置属性的规范列表是在HiveConf.Java类中管理的，因此请参考该HiveConf.java文件，以获取Hive当前使用的发行版中可用的设置属性的完备列表。从Hive 0.14.0开始，会从HiveConf.java类中直接天生设置模板文件hive-default.xml.template，它是当前版本设置及其默认值的可靠泉源。
具体的设置参数大全可以参考Hive官网设置参数，在页面使用ctrl+f举行搜索。
https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties

2、修改设置属性方式

设置属性的方式一共有两种环境：永世设置 + 暂时设置
记取：
文件型设置 => 永世设置
下令型设置 => 暂时设置（会话级别）
方式1：hive-site.xml设置文件（永世设置）

在$HIVE_HOME/conf路径下，可以添加一个hive-site.xml文件，把必要界说修改的设置属性添加进去，这个设置文件会影响到这个Hive安装包的任何一种服务启动、客户端使用方式，可以明白为是Hive的全局设置。
比如我们指定使用MySQL作为Hive元数据的存储介质，那么就必要把Hive毗连MySQL的干系属性设置在hive-site.xml文件中，如许不管是本地模式照旧远程模式启动，不管客户端本地毗连照旧远程毗连，都将访问同一个元数据存储介质，各人使用的元数据都是同等的。

1. <configuration>
2.     <!-- 存储元数据mysql相关配置 -->
3.     <property>
4.         <name>javax.jdo.option.ConnectionURL</name>
5.         <value> jdbc:mysql://node1:3306/hive?createDatabaseIfNotExist=true&amp;useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8</value>
6.     </property>
8.     <property>
9.         <name>javax.jdo.option.ConnectionDriverName</name>
10.         <value>com.mysql.jdbc.Driver</value>
11.     </property>
13.     <property>
14.         <name>javax.jdo.option.ConnectionUserName</name>
15.         <value>root</value>
16.     </property>
18.     <property>
19.         <name>javax.jdo.option.ConnectionPassword</name>
20.         <value>hadoop</value>
21.     </property>
22.    
23.     <property>
24.         <name>官网查看到的参数</name>
25.         <value>设置为你期望的参数值</value>
26.     </property>
27. </configuration>
29. 注意：配置完成后，一定要重启Hive，否则配置无法生效！！！

复制代码

方式2：set下令（暂时设置）

在Hive CLI或Beeline中使用set下令为set下令之后的全部SQL语句设置设置参数，这个也是会话级别的。
这种方式也是用户一样平常开辟中使用最多的一种设置参数方式。由于Hive提倡一种：谁必要、谁设置、谁使用的一种头脑，克制你的属性修改影响其他用户的修改。
获取已有设置：

1. set hive.exec.dynamic.partition;

复制代码

设置Hive属性：

1. #启用hive动态分区，需要在hive会话中设置两个参数：
2. set hive.exec.dynamic.partition=true;
3. set hive.exec.dynamic.partition.mode=nonstrict;

复制代码

3、小结

设置方式的优先级序次：set参数>hive-site.xml设置文件。
一样平常的开辟使用中，如果不是核心的必要全局修改的参数属性，发起各人使用set下令举行设置。
别的，Hive也会读入Hadoop的设置，由于Hive是作为Hadoop的客户端启动的，Hive的设置会覆盖Hadoop的设置。
四、Hive内置运算符（把握几个重点）

运算符就是一样平常生存中的加减乘除如许一些操纵。
1、Hive内置运算符

随着Hive版本的不绝发展，在Hive SQL中支持的、内置的运算符也越来越多。可以使用下面的下令检察当下支持的运算符和函数，而且检察其具体的使用方式。
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
也可以使用课程附件中的中文版本运算符函数分析文档举行检察。

1. --显示所有的函数和运算符
2. show functions;
3. --查看运算符或者函数的使用说明
4. describe function +;
5. --使用extended 可以查看更加详细的使用说明
6. describe function extended +;

复制代码

2、取整操纵: div

在Hive中不但支持+、-、*、/操纵，其还支持整除操纵（取整操纵）

1. --取整操作: div  给出将A除以B所得的整数部分。例如17 div 3得出5。
2. select 17 div 3;

复制代码

3、regexp正则匹配(regexp与rlike等价，支持正则)

1. select * from t_student where name rlike '^[\\u4e00-\\u9fa5]{3}$';  -- 匹配姓名为3个字的同学信息

复制代码

五、Hive函数入门（当做手册）

1、函数概述

犹如RDBMS中尺度SQL语法一样，Hive SQL也内建了不少函数，满意于用户在差别场合下的数据分析需求，进步开辟SQL数据分析的服从。
可以使用show functions检察当下版本支持的函数，而且可以通过===describe function extended funcname==来检察函数的使用方式和方法。

2、函数分类概述

Hive的函数很多，除了本身内置所支持的函数之外，还支持用户本身界说开辟函数。
针对==内置的函数==，可以根据函数的应用范例举行归纳分类，比如：数值范例函数、日期范例函数、字符串范例函数、聚集函数、条件函数等；
针对==用户自界说函数==，可以根据函数的输入输出行数举行分类，比如：UDF、UDAF、UDTF。

UDF（User-Defined-Function）平常函数，一进一出
UDAF（User-Defined Aggregation Function）聚合函数，多进一出
UDTF（User-Defined Table-Generating Functions）表天生函数，一进多出
怎样判断一个函数属于UDF、UDAF照旧UDTF呢？
答：describe function extended 函数名称
3、内置函数分类

所谓的**内置函数（buildin）**指的是Hive开辟实现好，直接可以使用的函数,也叫做内建函数。
官方文档地点：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
内置函数根据应用归类团体可以分为以下8大种范例，我们将对此中告急的，使用频率高的函数使用举行具体解说。
举行具体解说。
☆ String Functions 字符串函数

1. --字符串长度函数：length(str | binary)
2. select length("angelababy");
4. --字符串连接函数：concat(str1, str2, ... strN)
5. select concat("angela","baby");
7. --字符串截取函数：substr(str, pos[, len]) 或者  substring(str, pos[, len])
8. select substr("angelababy",-2); --pos是从1开始的索引，如果为负数则倒着数
9. select substr("angelababy",2,2);
11. --正则表达式替换函数：regexp_replace(str, regexp, rep)
12. select regexp_replace('100-200', '(\\d+)', 'num');
14. --分割字符串函数: split(str, regex)
15. select split('apache hive', '\\s+');

复制代码

☆ Date Functions 日期函数

1. --获取当前UNIX时间戳函数: unix_timestamp，时间戳就是从格林制时间1970年-1月-1日 00:00:00到当前时间秒数
2. select unix_timestamp();
4. --UNIX时间戳转日期函数: from_unixtime
5. select from_unixtime(1618238391);
6. select from_unixtime(0, 'yyyy-MM-dd HH:mm:ss');
8. --日期转UNIX时间戳函数: unix_timestamp
9. select unix_timestamp("2011-12-07 13:01:03");
11. --日期转年函数: year
12. select year('2009-07-30 04:17:52');
14. --日期转月函数: month
15. select month('2009-07-30 04:17:52');
17. --日期比较函数: datediff  日期格式要求'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'
18. select datediff('2012-12-08','2012-05-09');
20. --日期增加函数: date_add
21. select date_add('2012-02-28',10);
23. --日期减少函数: date_sub
24. select date_sub('2012-01-1',10);

复制代码

☆ Mathematical Functions 数学函数

1. --取整函数: round  返回double类型的整数值部分 （遵循四舍五入）
2. select round(3.1415926);
3. --保留2位小数
4. select round(3.1415926,2);
6. --向上取整函数: ceil
7. select ceil(3.1415926);
9. --向下取整函数: floor
10. select floor(3.1415926);
11. select floor(-3.1415926);
13. --取随机数函数: rand 每次执行都不一样 返回一个0到1范围内的随机数，包含0但是不包含1
14. select rand();

复制代码

☆ Collection Functions 聚集函数

array：数组
map：字典

1. --取map集合keys函数: map_keys(Map<K.V>)
2. select map_keys(`map`("id",10086,"name","zhangsan","age",18));
4. --取map集合values函数: map_values(Map<K.V>)
5. select map_values(`map`("id",10086,"name","zhangsan","age",18));
7. --数组排序函数:sort_array(Array<T>)
8. select sort_array(`array`(12,2,32));

复制代码

  MySQL/Hive中，如果有关键字出现，可以使用``反撇号举行阔起来。
  ☆ Conditional Functions 条件函数

1. --空值判断函数: isnull( a )
2. select isnull("allen");
3. select isnull(null);
5. --空值转换函数: nvl(T value, T default_value)
6. select nvl("allen","itcast");
7. select nvl(null,"itcast");
9. --返回第一个不为null的参数: coalesce( a, b, c )
10. select coalesce( null, null, 'itheima' );

复制代码

☆ Data Masking Functions 数据脱敏函数（相识）

脱敏：把一个敏感数据隐蔽

1. --对前n个进行脱敏替换
2. select mask_first_n("abc123DEF",4);
4. --mask_last_n(string str[, int n])
5. select mask_last_n("abc123DEF",4);

复制代码

☆ Misc Functions 其他杂项函数（相识）

1. --取哈希值函数:hash
2. select hash("allen");
4. --MD5加密: md5(string/binary)
5. select md5("allen");

复制代码

  扩展一下：这么多函数，如果不太记得某个函数作用大概有哪些参数了，应该怎么办呢？
  答：可以通过describe function extended 函数名称;
  4、用户自界说函数分类

固然说Hive内置了很多函数，但是不见得肯定可以满意于用户各种各样的分析需求场景。为了办理这个标题，Hive推出来用户自界说函数功能，让用户实现本身渴望实现的功能函数。
用户自界说函数简称UDF，源自于英文user-defined function。自界说函数统共有3类，是根据函数输入输出的行数来区分的，分别是：
UDF（User-Defined-Function）平常函数，一进一出
UDAF（User-Defined Aggregation Function）聚合函数，多进一出
UDTF（User-Defined Table-Generating Functions）表天生函数，一进多出
UDF分类尺度扩大化
   固然说UDF叫做用户自界说函数，其分类尺度重要针对的是用户编写开辟的函数。
    但是这套UDF分类尺度可以扩大到Hive的全部函数中：包罗内置函数和自界说函数。由于不管是什么范例的行数，肯定满意于输入输出的要求，那么从输入几行和输出几行上来分别没有任何毛病。万万不要被UD（User-Defined）这两个字母所迷惑，照成视野的局促。
  比如Hive官方文档中，针对聚合函数的尺度就是内置的UDAF范例。

☆ UDF 平常函数

UDF函数通常把它叫做平常函数，最大的特点是一进一出，也就是输入一行输出一行。比如round如许的取整函数，吸收一行数据，输出的照旧一行数据。

☆ UDAF聚合函数

UDAF函数通常把它叫做聚合函数，A所代表的单词就是Aggregation聚合的意思。最大的特点是多进一出，也就是输入多行输出一行。比如count、sum如许的函数。

1. •count:统计检索到的总行数。
2. •sum:求和
3. •avg:求平均
4. •min:最小值
5. •max:最大值
6. •数据收集函数（去重）: collect_set(col)
7. •数据收集函数（不去重）: collect_list(col)

复制代码

1. select sex from student;
3. select collect_set(sex) from student;
4. select collect_list(sex) from student;

复制代码

☆ UDTF 表天生函数

UDTF函数通常把它叫做表天生函数，T所代表的单词是Table-Generating表天生的意思。最大的特点是一进多出，也就是输入一行输出多行。
之以是叫做表天生函数，缘故原由在于这范例的函数作用返回的结果雷同于表（多行数据嘛），同时，UDTF函数也是我们打仗比力少的函数，生疏。比如explode函数。

在内置函数中，有一个比力有争议，容易堕落的函数 => split()函数
split函数属于UDF、UDAF、UDTF函数呢？
答：split属于UDF的，由于数组也算一个元素
   如有错误与不敷请指出，关注DPT一起进步吧！！！