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

文章图片

student
【关于Hive数据查询的18种方】
文章图片

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

hive (hypers)> select name from student; OK name Rose Jack Jimmy Tom Jerry

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

hive (hypers)> select t.name from student t; OK t.name Rose Jack Jimmy Tom Jerry

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

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; OK a.nameb.coursename RoseC语言 JackJava Jimmy高等数学 Tom离散数学 JerryC++

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

hive (hypers)> select t.* from student t; OK t.stuid t.namet.sext.age 15317408Rose121 15317412Jack020 15317432Jimmy121 15317423Tom120 15317478Jerry019 15317467Alice020

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

hive (hypers)> select * from student limit 1; OK student.stuidstudent.namestudent.sexstudent.age 15317408Rose121

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

hive (hypers)> select * from student order by age desc; OK student.stuidstudent.namestudent.sexstudent.age 15317432Jimmy121 15317408Rose121 15317467Alice020 15317423Tom120 15317412Jack020 15317478Jerry019 Time taken: 10.631 seconds, Fetched: 5 row(s) hive (hypers)> select * from student order by age; OK student.stuidstudent.namestudent.sexstudent.age 15317478Jerry019 15317467Alice020 15317423Tom120 15317412Jack020 15317432Jimmy121 15317408Rose121

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

hive (hypers)> SELECT stuid, >name, >age, >sex, >CASE >WHEN sex = '1' THEN '男' >WHEN sex = '0' THEN '女' >ELSE '未知' >END >FROM student; OK stuidnameagesex_c4 15317408Rose211男 15317412Jack200女 15317432Jimmy211男 15317423Tom201男 15317478Jerry190女 15317478Alice200女

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

hive (hypers)> select * from student where age = 21; OK student.stuidstudent.namestudent.sexstudent.age 15317408Rose121 15317432Jimmy121

文章图片

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

hive (hypers)> select sex,avg(age) from student group by sex; OK sex_c1 019.666666666666668 120.666666666666668

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

hive (hypers)> select sex,avg(age) from student group by sex having avg(age) > 20; OK sex_c1 120.666666666666668

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

hive (hypers)> select t1.name,t2.coursename from student t1 join course t2 on t1.stuid = t2.stuid; OK t1.name t2.coursename RoseC语言 JackJava Jimmy高等数学 Tom离散数学 JerryC++

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; OK t1.name t2.coursename RoseC语言 JackJava Jimmy高等数学 Tom离散数学 JerryC++ AliceNULL

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; OK t1.name t2.coursename RoseC语言 JackJava Jimmy高等数学 Tom离散数学 JerryC++ NULL大数据应用开发

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

hive (hypers)> select t1.name,t2.coursename from student t1 FULLouter join course t2 on t1.stuid = t2.stuid; OK t1.name t2.coursename RoseC语言 JackJava Tom离散数学 Jimmy高等数学 NULL大数据应用开发 AliceNULL JerryC++

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; OK t1.name Rose Jack Jimmy Tom Jerry

注意：| 在 LEFT SEMI JOIN 语句中，SELECT 和 WHERE 子句中不能引用右表中的字段。|
10、笛卡尔积 JOIN 语句
笛卡尔积 JOIN 语句 表示左表的行数乘以右表的行数等于结果集的大小，如下所示：

hive (hypers)> select * from student join course; OK student.stuidstudent.namestudent.sexstudent.agecourse.stuidcourse.coursenamecourse.score 15317408Rose12115317408C语言50 15317412Jack02015317408C语言50 15317432Jimmy12115317408C语言50 15317423Tom12015317408C语言50 15317478Jerry01915317408C语言50 15317467Alice02015317408C语言50 15317408Rose12115317412Java60 15317412Jack02015317412Java60 15317432Jimmy12115317412Java60 15317423Tom12015317412Java60 15317478Jerry01915317412Java60 15317467Alice02015317412Java60 15317408Rose12115317432高等数学70 15317412Jack02015317432高等数学70 15317432Jimmy12115317432高等数学70 15317423Tom12015317432高等数学70 15317478Jerry01915317432高等数学70 15317467Alice02015317432高等数学70 15317408Rose12115317423离散数学80 15317412Jack02015317423离散数学80 15317432Jimmy12115317423离散数学80 15317423Tom12015317423离散数学80 15317478Jerry01915317423离散数学80 15317467Alice02015317423离散数学80 15317408Rose12115317478C++90 15317412Jack02015317478C++90 15317432Jimmy12115317478C++90 15317423Tom12015317478C++90 15317478Jerry01915317478C++90 15317467Alice02015317478C++90 15317408Rose12115317463大数据应用开发100 15317412Jack02015317463大数据应用开发100 15317432Jimmy12115317463大数据应用开发100 15317423Tom12015317463大数据应用开发100 15317478Jerry01915317463大数据应用开发100 15317467Alice02015317463大数据应用开发100

注意：| 如果将 Hive 的属性hive.mapred.mode 设置为 strict，则会阻止执行笛卡尔积查询。 |
11、map-side JOIN语句
map-site JOIN语句会在Map阶段将小表读到内存，直接在 Map 端 进行JOIN，这种连接需要在查询语句中显式申明，如下所示：

SELECT /* + MapJOIN(t1) */ s1.stuid,s2.stuidfrom 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; OK student.stuidstudent.namestudent.sexstudent.age 15317478Jerry019 15317467Alice020 15317423Tom120 15317412Jack020 15317432Jimmy121 15317408Rose121

Time 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; OK student.stuidstudent.namestudent.sexstudent.age 15317478Jerry019 15317467Alice020 15317423Tom120 15317412Jack020 15317432Jimmy121 15317408Rose121 Time 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; OK student.stuidstudent.namestudent.sexstudent.age 15317478Jerry019 15317467Alice020 15317423Tom120 15317412Jack020 15317432Jimmy121 15317408Rose121

上述语句会在每个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; OK student.stuidstudent.namestudent.sexstudent.age 15317478Jerry019 15317412Jack020 15317423Tom120 15317467Alice020 15317408Rose121 15317432Jimmy121

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; OK student.stuidstudent.namestudent.sexstudent.age 15317478Jerry019 15317467Alice020 15317423Tom120 15317412Jack020 15317432Jimmy121 15317408Rose121

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

hive (hypers)> select * from student where cast(stuId AS INT) >= 15317450; OK student.stuidstudent.namestudent.sexstudent.age 15317478Jerry019 15317467Alice020

上述语句表示将 stuId 转化为 INT 类型。
17、分桶抽样
Hive支持分桶抽样查询，如下所示：

hive (hypers)> SELECT * FROM student TABLESAMPLE (BUCKET 2 OUT OF 6 ON stuid); OK student.stuidstudent.namestudent.sexstudent.age 15317467Alice020

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

hive (hypers)> SELECT * FROM student TABLESAMPLE (BUCKET 2 OUT OF 6 ON RAND()); OK student.stuidstudent.namestudent.sexstudent.age 15317478Jerry019 Time taken: 0.04 seconds, Fetched: 1 row(s)

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

hive.enforce.bucketing true

也可以在Hive命令行设置，如下所示：
hive (default)> SET hive.enforce.bucketing = true;
创建表时指定分桶，并插入 student 表中的 id列数据，如下所示：

hive (hypers)> CREATE TABLE test_bucket(id INT) CLUSTERED BY (id) INTO 3 BUCKETS ; OK Time taken: 0.086 seconds hive (hypers)> INSERT OVERWRITE TABLE test_bucket SELECT stuid FROM student; OK stuid Time 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.name FROM ( SELECT t1.id,t1.name FROM test1 t1 UNION ALL SELECT t2.id,t2.name FROM test2 t2 UNION ALL SELECT t3.id,t3.name FROM test3 t3 ) t

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

推荐阅读

• 

  什么是碱性岩
• 

  洛阳奥斯卡影院6月13号放映什么电影？洛阳奥斯卡影院今日放映表
• 

  京东“京银计划” 是什么 怎么参与？
• 

  共栅极放大电路分析,场效应管共栅极放大电路
• 

  2022年三伏天时间表？
• 

  活珠子不适合哪些人吃
• 

  m当前“最接近完美”的五款手机，别看价格贵，用三年毫无压力
• 

  上海电商有哪些 什么是电商上海，什么是电商上海人
• 

  家用茶具多少钱的合适 – 高档茶盘图片价格表
• 

  冰箱不结冰是怎么回事？原因分析与解决方法
• 

  热水器不烧水怎么办,是因为这些原因
• 

  增加注册资本后股东的股权比例
• 

  白发傅筹第几集知道身世
• 

  CPB水前肌底精华好不好？CPB水前肌底精华怎么用
• 

  兄弟T310如何清零废墨？
• 

  炒杜仲与生杜仲的区别 炒杜仲与生杜仲的不同之处
• 

  《侠客风云传前传》激活方法及特权简单介绍
• 

  取暖器品牌排名？哪个比较好的？
• 

  中风|女性中风有奇特症状？盘点易被女性忽视的11个中风症状！
• 

  减肥可以吃红枣吗

• 大数据|大数据开发技术hive篇
• Hive中order by，sort by，distribute by，cluster by的区别
• Hive 函数使用（一）（datediff,row_number,partition）
• SCD|一种基于SparkSQL的Hive数据仓库拉链表缓慢变化维（SCD2+SCD1）的示例实现
• Hive ORC和Parquet
• HiveQL执行过程
• hive-LanguageManual
• 大数据Hive系列之Hive API
• hive|使用Hive-JDBC遇到的一些问题解决