咱们本期内容大部分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       21

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;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 函数，大数据培训在 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语句，必须应用嵌套查问 。 |