乐趣区

关于hive:关于Hive数据查询的18种方

jiezi

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

course

student

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.name  b.coursename
Rose    C 语言
Jack    Java
Jimmy   高等数学
Tom     离散数学
Jerry   C++

能够应用正则表达式指定查问的列，如下所示：

hive (hypers)> select t.* from student t;
OK
t.stuid t.name  t.sex   t.age
15317408        Rose    1       21
15317412        Jack    0       20
15317432        Jimmy   1       21
15317423        Tom     1       20
15317478        Jerry   0       19
15317467        Alice   0       20

能够应用 LIMIT 限度查问的后果条数，如下所示：


hive (hypers)> select * from student limit 1;
OK
student.stuid   student.name    student.sex     student.age
15317408        Rose    1       21

能够应用 ORDER BY 语句对后果进行排序，升序咱们能够不在排序的字段后加上 ASC(默认)，然而倒序须要指定 DESC，如下所示：


hive (hypers)> select * from student order by age desc;
OK
student.stuid   student.name    student.sex     student.age
15317432        Jimmy   1       21
15317408        Rose    1       21
15317467        Alice   0       20
15317423        Tom     1       20
15317412        Jack    0       20
15317478        Jerry   0       19
Time taken: 10.631 seconds, Fetched: 5 row(s)
 
hive (hypers)> select * from student order by age;
OK
student.stuid   student.name    student.sex     student.age
15317478        Jerry   0       19
15317467        Alice   0       20
15317423        Tom     1       20
15317412        Jack    0       20
15317432        Jimmy   1       21
15317408        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;
OK
stuid   name    age     sex     _c4
15317408        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;
OK
student.stuid   student.name    student.sex     student.age
15317408        Rose    1       21
15317432        Jimmy   1       21

3、GROUP BY 语句

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


hive (hypers)> select sex,avg(age) from student group by sex;
OK
sex     _c1
0       19.666666666666668
1       20.666666666666668

4、HAVING 语句

HAVING 语句次要用来对 GROUP BY 语句的后果进行条件限度，如下所示：

hive (hypers)> select sex,avg(age) from student group by sex having avg(age) > 20;
OK
sex     _c1
1       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;
OK
t1.name t2.coursename
Rose    C 语言
Jack    Java
Jimmy   高等数学
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;
OK
t1.name t2.coursename
Rose    C 语言
Jack    Java
Jimmy   高等数学
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;
OK
t1.name t2.coursename
Rose    C 语言
Jack    Java
Jimmy   高等数学
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;
OK
t1.name t2.coursename
Rose    C 语言
Jack    Java
Tom     离散数学
Jimmy   高等数学
NULL    大数据利用开发
Alice   NULL
Jerry   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;
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.stuid   student.name    student.sex     student.age     course.stuid    course.coursename     course.score
15317408        Rose    1       21      15317408        C 语言   50
15317412        Jack    0       20      15317408        C 语言   50
15317432        Jimmy   1       21      15317408        C 语言   50
15317423        Tom     1       20      15317408        C 语言   50
15317478        Jerry   0       19      15317408        C 语言   50
15317467        Alice   0       20      15317408        C 语言   50
15317408        Rose    1       21      15317412        Java    60
15317412        Jack    0       20      15317412        Java    60
15317432        Jimmy   1       21      15317412        Java    60
15317423        Tom     1       20      15317412        Java    60
15317478        Jerry   0       19      15317412        Java    60
15317467        Alice   0       20      15317412        Java    60
15317408        Rose    1       21      15317432        高等数学        70
15317412        Jack    0       20      15317432        高等数学        70
15317432        Jimmy   1       21      15317432        高等数学        70
15317423        Tom     1       20      15317432        高等数学        70
15317478        Jerry   0       19      15317432        高等数学        70
15317467        Alice   0       20      15317432        高等数学        70
15317408        Rose    1       21      15317423        离散数学        80
15317412        Jack    0       20      15317423        离散数学        80
15317432        Jimmy   1       21      15317423        离散数学        80
15317423        Tom     1       20      15317423        离散数学        80
15317478        Jerry   0       19      15317423        离散数学        80
15317467        Alice   0       20      15317423        离散数学        80
15317408        Rose    1       21      15317478        C++     90
15317412        Jack    0       20      15317478        C++     90
15317432        Jimmy   1       21      15317478        C++     90
15317423        Tom     1       20      15317478        C++     90
15317478        Jerry   0       19      15317478        C++     90
15317467        Alice   0       20      15317478        C++     90
15317408        Rose    1       21      15317463        大数据利用开发  100
15317412        Jack    0       20      15317463        大数据利用开发  100
15317432        Jimmy   1       21      15317463        大数据利用开发  100
15317423        Tom     1       20      15317463        大数据利用开发  100
15317478        Jerry   0       19      15317463        大数据利用开发  100
15317467        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;
OK
student.stuid   student.name    student.sex     student.age
15317478        Jerry   0       19
15317467        Alice   0       20
15317423        Tom     1       20
15317412        Jack    0       20
15317432        Jimmy   1       21
15317408        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;
OK
student.stuid   student.name    student.sex     student.age
15317478        Jerry   0       19
15317467        Alice   0       20
15317423        Tom     1       20
15317412        Jack    0       20
15317432        Jimmy   1       21
15317408        Rose    1       21
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.stuid   student.name    student.sex     student.age
15317478        Jerry   0       19
15317467        Alice   0       20
15317423        Tom     1       20
15317412        Jack    0       20
15317432        Jimmy   1       21
15317408        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;
OK
student.stuid   student.name    student.sex     student.age
15317478        Jerry   0       19
15317412        Jack    0       20
15317423        Tom     1       20
15317467        Alice   0       20
15317408        Rose    1       21
15317432        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;
OK
student.stuid   student.name    student.sex     student.age
15317478        Jerry   0       19
15317467        Alice   0       20
15317423        Tom     1       20
15317412        Jack    0       20
15317432        Jimmy   1       21
15317408        Rose    1       21

16、类型转换

类型转换能够应用 cast(value As TYPE) 语法，如下所示：

hive (hypers)> select * from student where cast(stuId AS INT) >= 15317450;
OK
student.stuid   student.name    student.sex     student.age
15317478        Jerry   0       19
15317467        Alice   0       20

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

17、分桶抽样

Hive 反对分桶抽样查问，如下所示：

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

上述语句示意查问时候 6 个桶，取第 2 个桶，分桶的根据是将 id 值的哈希值除以桶数 6 的余数。也能够采纳随机抽样的形式，如下所示：

hive (hypers)> SELECT * FROM student TABLESAMPLE (BUCKET 2 OUT OF 6 ON RAND());
OK
student.stuid   student.name    student.sex     student.age
15317478        Jerry   0       19
Time 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 ;
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 语句，必须应用嵌套查问。|

退出移动版