Hive 查询的 18 种方式

作者：编程江湖

2021 年 12 月 20 日
本文字数：14458 字
阅读完需：约 47 分钟

我们本期内容大部分 HQL 操作都需要依赖如下两张表，具体的数据内容如下：

course

student

1、SELECT 查询语句

SELECT 查询语句比较简单，后面跟要查询的字段，如下所示：

hive (hypers)> select name from student;OKnameRoseJackJimmyTomJerry

复制代码

可以为查询语句中的列和表加上别名，如下所示：

hive (hypers)> select t.name from student t;OKt.nameRoseJackJimmyTomJerry

复制代码

可以使用如下语句进行嵌套查询：

hive (hypers)> select a.name, b.coursename             > from (select stuid, name from student) a             >          join (select stuid, coursename from course) b on a.stuid = b.stuid;
OKa.name  b.coursenameRose    C语言Jack    JavaJimmy   高等数学Tom     离散数学Jerry   C++

复制代码

可以使用正则表达式指定查询的列，如下所示：

hive (hypers)> select t.* from student t;OKt.stuid t.name  t.sex   t.age15317408        Rose    1       2115317412        Jack    0       2015317432        Jimmy   1       2115317423        Tom     1       2015317478        Jerry   0       1915317467        Alice   0       20

复制代码

可以使用 LIMIT 限制查询的结果条数，如下所示：

hive (hypers)> select * from student limit 1;OKstudent.stuid   student.name    student.sex     student.age15317408        Rose    1       21

复制代码

可以使用ORDER BY语句对结果进行排序，升序我们可以不在排序的字段后加上ASC(默认)，但是倒序需要指定DESC，如下所示：

hive (hypers)> select * from student order by age desc;OKstudent.stuid   student.name    student.sex     student.age15317432        Jimmy   1       2115317408        Rose    1       2115317467        Alice   0       2015317423        Tom     1       2015317412        Jack    0       2015317478        Jerry   0       19Time taken: 10.631 seconds, Fetched: 5 row(s)
hive (hypers)> select * from student order by age;OKstudent.stuid   student.name    student.sex     student.age15317478        Jerry   0       1915317467        Alice   0       2015317423        Tom     1       2015317412        Jack    0       2015317432        Jimmy   1       2115317408        Rose    1       21

复制代码

我们还可以使用CASE...WHEN...THEN语句对某一列的值进行处理，如下所示：

hive (hypers)> SELECT stuid,             >        name,             >        age,             >        sex,             >        CASE             >            WHEN sex = '1' THEN '男'             >            WHEN sex = '0' THEN '女'             >            ELSE '未知'             >            END             >        FROM student;OKstuid   name    age     sex     _c415317408        Rose    21      1       男15317412        Jack    20      0       女15317432        Jimmy   21      1       男15317423        Tom     20      1       男15317478        Jerry   19      0       女15317478        Alice   20      0       女

复制代码

2、WHERE 条件语句

WHERE 条件语句主要是对查询进行条件限制，如下所示：

hive (hypers)> select * from student where age = 21;OKstudent.stuid   student.name    student.sex     student.age15317408        Rose    1       2115317432        Jimmy   1       21

复制代码

3、GROUP BY 语句

GROUP BY语句主要是对查询的数据进行分组，通常会和聚合函数一起使用，如下所示：

hive (hypers)> select sex,avg(age) from student group by sex;OKsex     _c10       19.6666666666666681       20.666666666666668

复制代码

4、HAVING 语句

HAVING语句主要用来对GROUP BY语句的结果进行条件限制，如下所示：

hive (hypers)> select sex,avg(age) from student group by sex having avg(age) > 20;OKsex     _c11       20.666666666666668

复制代码

5、INNER JOIN 语句

在 INNER JOIN 语句中，只有进行连接的两个表中都存在与连接条件相匹配的数据时才会被显示在结果数据中，如下所示：

hive (hypers)> select t1.name,t2.coursename from student t1 join course t2 on t1.stuid = t2.stuid;OKt1.name t2.coursenameRose    C语言Jack    JavaJimmy   高等数学Tom     离散数学Jerry   C++

复制代码

6、 LEFT OUTER JOIN 语句

LEFT OUTER JOIN语句表示左外连接，左外连接查询数据会包含左表中的全部记录，而右表中不符合条件的结果将以NULL的形式出现，如下所示：

hive (hypers)> select t1.name,t2.coursename from student t1 left outer join course t2 on t1.stuid = t2.stuid;OKt1.name t2.coursenameRose    C语言Jack    JavaJimmy   高等数学Tom     离散数学Jerry   C++Alice   NULL

复制代码

7、RIGHT OUTER JOIN 语句

RIGHT OUTER JOIN表示右外连接，右外连接查询数据会包含右表中的全部记录，而左表中不符合条件的结果将以 NULL 的形式出现，如下所示：

hive (hypers)> select t1.name,t2.coursename from student t1 right outer join course t2 on t1.stuid = t2.stuid;OKt1.name t2.coursenameRose    C语言Jack    JavaJimmy   高等数学Tom     离散数学Jerry   C++NULL    大数据应用开发

复制代码

8、FULL OUTER JOIN 语句

FULL OUTER JOIN语句表示全外连接，结果数据会包含左表和右表的全部数据，不符合条件的用 NULL 表示，如下所示：

hive (hypers)> select t1.name,t2.coursename from student t1 FULL  outer join course t2 on t1.stuid = t2.stuid;OKt1.name t2.coursenameRose    C语言Jack    JavaTom     离散数学Jimmy   高等数学NULL    大数据应用开发Alice   NULLJerry   C++

复制代码

9、 LEFT SEMI JOIN 语句

LEFT SEMI JOIN语句表示左半连接，其结果数据对应右表满足 ON 语句中的条件，如下所示：

hive (hypers)> select t1.name from student t1 LEFT SEMI JOIN course t2 on t1.stuid = t2.stuid;OKt1.nameRoseJackJimmyTomJerry

复制代码

注意：| 在 LEFT SEMI JOIN 语句中，SELECT 和 WHERE 子句中不能引用右表中的字段。|

10、笛卡尔积 JOIN 语句

笛卡尔积 JOIN 语句表示左表的行数乘以右表的行数等于结果集的大小，如下所示：

hive (hypers)> select * from student join course;OKstudent.stuid   student.name    student.sex     student.age     course.stuid    course.coursename     course.score15317408        Rose    1       21      15317408        C语言   5015317412        Jack    0       20      15317408        C语言   5015317432        Jimmy   1       21      15317408        C语言   5015317423        Tom     1       20      15317408        C语言   5015317478        Jerry   0       19      15317408        C语言   5015317467        Alice   0       20      15317408        C语言   5015317408        Rose    1       21      15317412        Java    6015317412        Jack    0       20      15317412        Java    6015317432        Jimmy   1       21      15317412        Java    6015317423        Tom     1       20      15317412        Java    6015317478        Jerry   0       19      15317412        Java    6015317467        Alice   0       20      15317412        Java    6015317408        Rose    1       21      15317432        高等数学        7015317412        Jack    0       20      15317432        高等数学        7015317432        Jimmy   1       21      15317432        高等数学        7015317423        Tom     1       20      15317432        高等数学        7015317478        Jerry   0       19      15317432        高等数学        7015317467        Alice   0       20      15317432        高等数学        7015317408        Rose    1       21      15317423        离散数学        8015317412        Jack    0       20      15317423        离散数学        8015317432        Jimmy   1       21      15317423        离散数学        8015317423        Tom     1       20      15317423        离散数学        8015317478        Jerry   0       19      15317423        离散数学        8015317467        Alice   0       20      15317423        离散数学        8015317408        Rose    1       21      15317478        C++     9015317412        Jack    0       20      15317478        C++     9015317432        Jimmy   1       21      15317478        C++     9015317423        Tom     1       20      15317478        C++     9015317478        Jerry   0       19      15317478        C++     9015317467        Alice   0       20      15317478        C++     9015317408        Rose    1       21      15317463        大数据应用开发  10015317412        Jack    0       20      15317463        大数据应用开发  10015317432        Jimmy   1       21      15317463        大数据应用开发  10015317423        Tom     1       20      15317463        大数据应用开发  10015317478        Jerry   0       19      15317463        大数据应用开发  10015317467        Alice   0       20      15317463        大数据应用开发  100

复制代码

注意：| 如果将 Hive 的属性 hive.mapred.mode 设置为 strict，则会阻止执行笛卡尔积查询。 |

11、map-side JOIN 语句

map-site JOIN 语句会在 Map 阶段将小表读到内存，直接在 Map 端进行 JOIN，这种连接需要在查询语句中显式申明，如下所示：

SELECT /* + MapJOIN(t1) */ s1.stuid,s2.stuid  from student s1 JOIN student s2 ON s1.stuid = s2.stuid;

复制代码

可以通过设置 Hive 的属性 hive.auto.convert.join=true自动开启 map-side JOIN；也可以设置 Hive 的属性 hive.mapjoin.smalltable.filesize定义表的大小，默认为 25 000 000 B。

12、多表 JOIN 语句

Hive 支持多张表进行连接，语句如下所示：

hive (hypers)> SELECT *               FROM test1 t1               JOIN test2 t2 ON t1.id = t2.id               JOIN test3 t3 ON t2.id = t3.id

复制代码

每个 JOIN 都会启动一个 MapReduce 作业。第一个 MapReduce 作业连接 test1 表和 test2 表，第二个 MapReduce 作业连接第一个 MapReduce 作业的输出结果和 test3 表。

13、ORDER BY 和 SORT BY 语句

Hive 中的 ORDER BY语句和SQL语句一样，可以实现对结果集的排序，如下所示：

hive (hypers)> select * from student order by age asc,stuId desc;OKstudent.stuid   student.name    student.sex     student.age15317478        Jerry   0       1915317467        Alice   0       2015317423        Tom     1       2015317412        Jack    0       2015317432        Jimmy   1       2115317408        Rose    1       21Time taken: 11.929 seconds, Fetched: 6 row(s)

复制代码

上述语句表示按照 age 字段升序，stuId 字段降序排序。

如果 Hive 表中的数据非常多，使用 ORDER BY排序可能会导致执行的时间过长，此时可以设置 Hive 的属性 hive.mapred.mode为 strict，则排序语句后面必须加上 LIMIT限制查询的结果条数，以避免数据量太多造成的执行时间过长的问题，如下所示：

hive (hypers)> SET hive.mapred.mode = strict;hive (hypers)> select * from student order by age asc,stuId desc limit 100;OKstudent.stuid   student.name    student.sex     student.age15317478        Jerry   0       1915317467        Alice   0       2015317423        Tom     1       2015317412        Jack    0       2015317432        Jimmy   1       2115317408        Rose    1       21Time taken: 9.378 seconds, Fetched: 6 row(s)

复制代码

SORT BY语句会在每个 Reduce 中对数据进行排序，可以保证每个 Reduce 输出的数据是有序的（全局不一定有序），并可以提高全局排序的性能，如下所示：

hive (hypers)> select * from student sort by age asc,stuId desc limit 100;OKstudent.stuid   student.name    student.sex     student.age15317478        Jerry   0       1915317467        Alice   0       2015317423        Tom     1       2015317412        Jack    0       2015317432        Jimmy   1       2115317408        Rose    1       21

复制代码

上述语句会在每个 Reduce 中对 age 字段进行升序排序，同时对create_time字段进行降序排序。如果 Reduce 个数为 1，则ORDER BY和SORT BY语句的查询结果相同；如果 Reduce 个数大于 1，则SORT BY输出的结果为局部有序。

14、 DISTRIBUTE BY 和 SORT BY 语句

DISTRIBUTE语句结合SORT BY语句可以实现在第一列数据相同时，能够按照第二列数据进行排序，如下所示：

hive (hypers)> select * from student distribute by sex sort by age,stuId;OKstudent.stuid   student.name    student.sex     student.age15317478        Jerry   0       1915317412        Jack    0       2015317423        Tom     1       2015317467        Alice   0       2015317408        Rose    1       2115317432        Jimmy   1       21

复制代码

DISTRIBUTE BY语句能够保证 sex 相同的数据进入同一个 Reduce 函数，我们本期内容大部分 HQL 操作都需要依赖如下两张表，具体的数据内容如下：

course

student

1、SELECT 查询语句

SELECT 查询语句比较简单，后面跟要查询的字段，如下所示：

hive (hypers)> select name from student;OKnameRoseJackJimmyTomJerry

复制代码

可以为查询语句中的列和表加上别名，如下所示：

hive (hypers)> select t.name from student t;OKt.nameRoseJackJimmyTomJerry

复制代码

可以使用如下语句进行嵌套查询：

hive (hypers)> select a.name, b.coursename             > from (select stuid, name from student) a             >          join (select stuid, coursename from course) b on a.stuid = b.stuid;
OKa.name  b.coursenameRose    C语言Jack    JavaJimmy   高等数学Tom     离散数学Jerry   C++

复制代码

可以使用正则表达式指定查询的列，如下所示：

hive (hypers)> select t.* from student t;OKt.stuid t.name  t.sex   t.age15317408        Rose    1       2115317412        Jack    0       2015317432        Jimmy   1       2115317423        Tom     1       2015317478        Jerry   0       1915317467        Alice   0       20

复制代码

可以使用 LIMIT 限制查询的结果条数，如下所示：

hive (hypers)> select * from student limit 1;OKstudent.stuid   student.name    student.sex     student.age15317408        Rose    1       21

复制代码

可以使用ORDER BY语句对结果进行排序，升序我们可以不在排序的字段后加上ASC(默认)，但是倒序需要指定DESC，如下所示：

hive (hypers)> select * from student order by age desc;OKstudent.stuid   student.name    student.sex     student.age15317432        Jimmy   1       2115317408        Rose    1       2115317467        Alice   0       2015317423        Tom     1       2015317412        Jack    0       2015317478        Jerry   0       19Time taken: 10.631 seconds, Fetched: 5 row(s)
hive (hypers)> select * from student order by age;OKstudent.stuid   student.name    student.sex     student.age15317478        Jerry   0       1915317467        Alice   0       2015317423        Tom     1       2015317412        Jack    0       2015317432        Jimmy   1       2115317408        Rose    1       21

复制代码

我们还可以使用CASE...WHEN...THEN语句对某一列的值进行处理，如下所示：

hive (hypers)> SELECT stuid,             >        name,             >        age,             >        sex,             >        CASE             >            WHEN sex = '1' THEN '男'             >            WHEN sex = '0' THEN '女'             >            ELSE '未知'             >            END             >        FROM student;OKstuid   name    age     sex     _c415317408        Rose    21      1       男15317412        Jack    20      0       女15317432        Jimmy   21      1       男15317423        Tom     20      1       男15317478        Jerry   19      0       女15317478        Alice   20      0       女

复制代码

2、WHERE 条件语句

WHERE 条件语句主要是对查询进行条件限制，如下所示：

hive (hypers)> select * from student where age = 21;OKstudent.stuid   student.name    student.sex     student.age15317408        Rose    1       2115317432        Jimmy   1       21

复制代码

3、GROUP BY 语句

GROUP BY语句主要是对查询的数据进行分组，通常会和聚合函数一起使用，如下所示：

hive (hypers)> select sex,avg(age) from student group by sex;OKsex     _c10       19.6666666666666681       20.666666666666668

复制代码

4、HAVING 语句

HAVING语句主要用来对GROUP BY语句的结果进行条件限制，如下所示：

hive (hypers)> select sex,avg(age) from student group by sex having avg(age) > 20;OKsex     _c11       20.666666666666668

复制代码

5、INNER JOIN 语句

在 INNER JOIN 语句中，只有进行连接的两个表中都存在与连接条件相匹配的数据时才会被显示在结果数据中，如下所示：

hive (hypers)> select t1.name,t2.coursename from student t1 join course t2 on t1.stuid = t2.stuid;OKt1.name t2.coursenameRose    C语言Jack    JavaJimmy   高等数学Tom     离散数学Jerry   C++

复制代码

6、 LEFT OUTER JOIN 语句

LEFT OUTER JOIN语句表示左外连接，左外连接查询数据会包含左表中的全部记录，而右表中不符合条件的结果将以NULL的形式出现，如下所示：

hive (hypers)> select t1.name,t2.coursename from student t1 left outer join course t2 on t1.stuid = t2.stuid;OKt1.name t2.coursenameRose    C语言Jack    JavaJimmy   高等数学Tom     离散数学Jerry   C++Alice   NULL

复制代码

7、RIGHT OUTER JOIN 语句

RIGHT OUTER JOIN表示右外连接，右外连接查询数据会包含右表中的全部记录，而左表中不符合条件的结果将以 NULL 的形式出现，如下所示：

hive (hypers)> select t1.name,t2.coursename from student t1 right outer join course t2 on t1.stuid = t2.stuid;OKt1.name t2.coursenameRose    C语言Jack    JavaJimmy   高等数学Tom     离散数学Jerry   C++NULL    大数据应用开发

复制代码

8、FULL OUTER JOIN 语句

FULL OUTER JOIN语句表示全外连接，结果数据会包含左表和右表的全部数据，不符合条件的用 NULL 表示，如下所示：

hive (hypers)> select t1.name,t2.coursename from student t1 FULL  outer join course t2 on t1.stuid = t2.stuid;OKt1.name t2.coursenameRose    C语言Jack    JavaTom     离散数学Jimmy   高等数学NULL    大数据应用开发Alice   NULLJerry   C++

复制代码

9、 LEFT SEMI JOIN 语句

LEFT SEMI JOIN语句表示左半连接，其结果数据对应右表满足 ON 语句中的条件，如下所示：

hive (hypers)> select t1.name from student t1 LEFT SEMI JOIN course t2 on t1.stuid = t2.stuid;OKt1.nameRoseJackJimmyTomJerry

复制代码

注意：| 在 LEFT SEMI JOIN 语句中，SELECT 和 WHERE 子句中不能引用右表中的字段。|

10、笛卡尔积 JOIN 语句

笛卡尔积 JOIN 语句表示左表的行数乘以右表的行数等于结果集的大小，如下所示：

hive (hypers)> select * from student join course;OKstudent.stuid   student.name    student.sex     student.age     course.stuid    course.coursename     course.score15317408        Rose    1       21      15317408        C语言   5015317412        Jack    0       20      15317408        C语言   5015317432        Jimmy   1       21      15317408        C语言   5015317423        Tom     1       20      15317408        C语言   5015317478        Jerry   0       19      15317408        C语言   5015317467        Alice   0       20      15317408        C语言   5015317408        Rose    1       21      15317412        Java    6015317412        Jack    0       20      15317412        Java    6015317432        Jimmy   1       21      15317412        Java    6015317423        Tom     1       20      15317412        Java    6015317478        Jerry   0       19      15317412        Java    6015317467        Alice   0       20      15317412        Java    6015317408        Rose    1       21      15317432        高等数学        7015317412        Jack    0       20      15317432        高等数学        7015317432        Jimmy   1       21      15317432        高等数学        7015317423        Tom     1       20      15317432        高等数学        7015317478        Jerry   0       19      15317432        高等数学        7015317467        Alice   0       20      15317432        高等数学        7015317408        Rose    1       21      15317423        离散数学        8015317412        Jack    0       20      15317423        离散数学        8015317432        Jimmy   1       21      15317423        离散数学        8015317423        Tom     1       20      15317423        离散数学        8015317478        Jerry   0       19      15317423        离散数学        8015317467        Alice   0       20      15317423        离散数学        8015317408        Rose    1       21      15317478        C++     9015317412        Jack    0       20      15317478        C++     9015317432        Jimmy   1       21      15317478        C++     9015317423        Tom     1       20      15317478        C++     9015317478        Jerry   0       19      15317478        C++     9015317467        Alice   0       20      15317478        C++     9015317408        Rose    1       21      15317463        大数据应用开发  10015317412        Jack    0       20      15317463        大数据应用开发  10015317432        Jimmy   1       21      15317463        大数据应用开发  10015317423        Tom     1       20      15317463        大数据应用开发  10015317478        Jerry   0       19      15317463        大数据应用开发  10015317467        Alice   0       20      15317463        大数据应用开发  100

复制代码

注意：| 如果将 Hive 的属性 hive.mapred.mode 设置为 strict，则会阻止执行笛卡尔积查询。 |

11、map-side JOIN 语句

map-site JOIN 语句会在 Map 阶段将小表读到内存，直接在 Map 端进行 JOIN，这种连接需要在查询语句中显式申明，如下所示：

SELECT /* + MapJOIN(t1) */ s1.stuid,s2.stuid  from student s1 JOIN student s2 ON s1.stuid = s2.stuid;

复制代码

12、多表 JOIN 语句

Hive 支持多张表进行连接，语句如下所示：

hive (hypers)> SELECT *               FROM test1 t1               JOIN test2 t2 ON t1.id = t2.id               JOIN test3 t3 ON t2.id = t3.id

复制代码

每个 JOIN 都会启动一个 MapReduce 作业。第一个 MapReduce 作业连接 test1 表和 test2 表，第二个 MapReduce 作业连接第一个 MapReduce 作业的输出结果和 test3 表。

13、ORDER BY 和 SORT BY 语句

Hive 中的 ORDER BY语句和SQL语句一样，可以实现对结果集的排序，如下所示：

hive (hypers)> select * from student order by age asc,stuId desc;OKstudent.stuid   student.name    student.sex     student.age15317478        Jerry   0       1915317467        Alice   0       2015317423        Tom     1       2015317412        Jack    0       2015317432        Jimmy   1       2115317408        Rose    1       21Time taken: 11.929 seconds, Fetched: 6 row(s)

复制代码

上述语句表示按照 age 字段升序，stuId 字段降序排序。

hive (hypers)> SET hive.mapred.mode = strict;hive (hypers)> select * from student order by age asc,stuId desc limit 100;OKstudent.stuid   student.name    student.sex     student.age15317478        Jerry   0       1915317467        Alice   0       2015317423        Tom     1       2015317412        Jack    0       2015317432        Jimmy   1       2115317408        Rose    1       21Time taken: 9.378 seconds, Fetched: 6 row(s)

复制代码

SORT BY语句会在每个 Reduce 中对数据进行排序，可以保证每个 Reduce 输出的数据是有序的（全局不一定有序），并可以提高全局排序的性能，如下所示：

hive (hypers)> select * from student sort by age asc,stuId desc limit 100;OKstudent.stuid   student.name    student.sex     student.age15317478        Jerry   0       1915317467        Alice   0       2015317423        Tom     1       2015317412        Jack    0       2015317432        Jimmy   1       2115317408        Rose    1       21

复制代码

14、 DISTRIBUTE BY 和 SORT BY 语句

DISTRIBUTE语句结合SORT BY语句可以实现在第一列数据相同时，能够按照第二列数据进行排序，如下所示：

hive (hypers)> select * from student distribute by sex sort by age,stuId;OKstudent.stuid   student.name    student.sex     student.age15317478        Jerry   0       1915317412        Jack    0       2015317423        Tom     1       2015317467        Alice   0       2015317408        Rose    1       2115317432        Jimmy   1       21

复制代码

DISTRIBUTE BY语句能够保证 sex 相同的数据进入同一个 Reduce 函数，大数据培训在 Reduce 中再按照 age 和 stuId 排序即可实现在第一列数据相同时，按照第二列数据排序。

15、CLUSTER BY 语句

如果 DISTRIBUTE BY 和 SORT BY语句中的列完全相同，并且都是按照升序排序，则可以使用CLUSTER BY语句代替DISTRIBUTE BY和SORT BY语句，如下所示：

select * from student distribute by age sort by age;

复制代码

上面的语句等价于：

hive (hypers)> select * from student cluster by age;OKstudent.stuid   student.name    student.sex     student.age15317478        Jerry   0       1915317467        Alice   0       2015317423        Tom     1       2015317412        Jack    0       2015317432        Jimmy   1       2115317408        Rose    1       21

复制代码

16、类型转换

类型转换可以使用 cast(value As TYPE)语法，如下所示：

hive (hypers)> select * from student where cast(stuId AS INT) >= 15317450;OKstudent.stuid   student.name    student.sex     student.age15317478        Jerry   0       1915317467        Alice   0       20

复制代码

上述语句表示将 stuId 转化为 INT 类型。

17、分桶抽样

Hive 支持分桶抽样查询，如下所示：

hive (hypers)> SELECT * FROM student TABLESAMPLE (BUCKET 2 OUT OF 6 ON stuid);OKstudent.stuid   student.name    student.sex     student.age15317467        Alice   0       20

复制代码

上述语句表示查询时分 6 个桶，取第 2 个桶，分桶的依据是将 id 值的哈希值除以桶数 6 的余数。也可以采用随机抽样的方式，如下所示：

hive (hypers)> SELECT * FROM student TABLESAMPLE (BUCKET 2 OUT OF 6 ON RAND());OKstudent.stuid   student.name    student.sex     student.age15317478        Jerry   0       19Time taken: 0.04 seconds, Fetched: 1 row(s)

复制代码

可以在创建表时指定分桶，需要提前将 Hive 的 hive.enforce.bucketing属性设置为 true。该属性可以在 hive-site.xml文件中配置，如下所示：

<property>        <name>hive.enforce.bucketing</name>        <value>true</value></property>

复制代码

也可以在 Hive 命令行设置，如下所示：

hive (default)> SET hive.enforce.bucketing = true;

复制代码

创建表时指定分桶，并插入 student 表中的 id 列数据，如下所示：

hive (hypers)> CREATE TABLE test_bucket(id INT) CLUSTERED BY (id) INTO 3 BUCKETS ;OKTime taken: 0.086 secondshive (hypers)> INSERT OVERWRITE TABLE test_bucket SELECT stuid FROM student;OKstuidTime taken: 24.261 seconds

复制代码

上述语句首先创建一个 test_bucket表，并将 test_bucket 表划分为 3 个桶，然后将 student 表中的 id 列数据插入 test_bucket表中。插入的数据会被保存在 3 个文件中，每个桶一个文件，保存在 test_bucket表路径下。

18、 UNION ALL 语句

Hive 支持 UNION ALL查询，其主要用于多表数据合并的场景。使用 UNION ALL语句要求各表查询出的字段类型必须完全匹配，如下所示：

SELECT t.id,t.nameFROM (SELECT t1.id,t1.name FROM test1 t1UNION ALLSELECT t2.id,t2.name FROM test2 t2UNION ALLSELECT t3.id,t3.name FROM test3 t3) t

复制代码

注意：| 在 Hive 中使用 UNION ALL语句，必须使用嵌套查询。 |在 Reduce 中再按照 age 和 stuId 排序即可实现在第一列数据相同时，按照第二列数据排序。

15、CLUSTER BY 语句

如果 DISTRIBUTE BY 和 SORT BY语句中的列完全相同，并且都是按照升序排序，则可以使用CLUSTER BY语句代替DISTRIBUTE BY和SORT BY语句，如下所示：

select * from student distribute by age sort by age;

复制代码

上面的语句等价于：

hive (hypers)> select * from student cluster by age;OKstudent.stuid   student.name    student.sex     student.age15317478        Jerry   0       1915317467        Alice   0       2015317423        Tom     1       2015317412        Jack    0       2015317432        Jimmy   1       2115317408        Rose    1       21

复制代码

16、类型转换

类型转换可以使用 cast(value As TYPE)语法，如下所示：

hive (hypers)> select * from student where cast(stuId AS INT) >= 15317450;OKstudent.stuid   student.name    student.sex     student.age15317478        Jerry   0       1915317467        Alice   0       20

复制代码

上述语句表示将 stuId 转化为 INT 类型。

17、分桶抽样

Hive 支持分桶抽样查询，如下所示：

hive (hypers)> SELECT * FROM student TABLESAMPLE (BUCKET 2 OUT OF 6 ON stuid);OKstudent.stuid   student.name    student.sex     student.age15317467        Alice   0       20

复制代码

上述语句表示查询时分 6 个桶，取第 2 个桶，分桶的依据是将 id 值的哈希值除以桶数 6 的余数。也可以采用随机抽样的方式，如下所示：

hive (hypers)> SELECT * FROM student TABLESAMPLE (BUCKET 2 OUT OF 6 ON RAND());OKstudent.stuid   student.name    student.sex     student.age15317478        Jerry   0       19Time taken: 0.04 seconds, Fetched: 1 row(s)

复制代码

可以在创建表时指定分桶，需要提前将 Hive 的 hive.enforce.bucketing属性设置为 true。该属性可以在 hive-site.xml文件中配置，如下所示：

<property>        <name>hive.enforce.bucketing</name>        <value>true</value></property>

复制代码

也可以在 Hive 命令行设置，如下所示：

hive (default)> SET hive.enforce.bucketing = true;

复制代码

创建表时指定分桶，并插入 student 表中的 id 列数据，如下所示：

hive (hypers)> CREATE TABLE test_bucket(id INT) CLUSTERED BY (id) INTO 3 BUCKETS ;OKTime taken: 0.086 secondshive (hypers)> INSERT OVERWRITE TABLE test_bucket SELECT stuid FROM student;OKstuidTime taken: 24.261 seconds

复制代码

18、 UNION ALL 语句

Hive 支持 UNION ALL查询，其主要用于多表数据合并的场景。使用 UNION ALL语句要求各表查询出的字段类型必须完全匹配，如下所示：

SELECT t.id,t.nameFROM (SELECT t1.id,t1.name FROM test1 t1UNION ALLSELECT t2.id,t2.name FROM test2 t2UNION ALLSELECT t3.id,t3.name FROM test3 t3) t

复制代码

注意：| 在 Hive 中使用 UNION ALL语句，必须使用嵌套查询。 |

发布于: 3 小时前阅读数: 6

编程江湖

关注

IT技术分享 2021.11.23 加入

还未添加个人简介

发布

暂无评论

创作场景

Hive 查询的 18 种方式

1、SELECT 查询语句

2、WHERE 条件语句

3、GROUP BY 语句

4、HAVING 语句

5、INNER JOIN 语句

6、 LEFT OUTER JOIN 语句

7、RIGHT OUTER JOIN 语句

8、FULL OUTER JOIN 语句

9、 LEFT SEMI JOIN 语句

10、笛卡尔积 JOIN 语句

11、map-side JOIN 语句

12、多表 JOIN 语句

13、ORDER BY 和 SORT BY 语句

14、 DISTRIBUTE BY 和 SORT BY 语句

1、SELECT 查询语句

2、WHERE 条件语句

3、GROUP BY 语句

4、HAVING 语句

5、INNER JOIN 语句

6、 LEFT OUTER JOIN 语句

7、RIGHT OUTER JOIN 语句

8、FULL OUTER JOIN 语句

9、 LEFT SEMI JOIN 语句

10、笛卡尔积 JOIN 语句

11、map-side JOIN 语句

12、多表 JOIN 语句

13、ORDER BY 和 SORT BY 语句

14、 DISTRIBUTE BY 和 SORT BY 语句

15、CLUSTER BY 语句

16、类型转换

17、分桶抽样

18、 UNION ALL 语句

15、CLUSTER BY 语句

16、类型转换

17、分桶抽样

18、 UNION ALL 语句

编程江湖

评论