Hive实战：分科汇总求月考平均分

文章目录

一、实战概述

• 在这个实战中，我们使用了Hive框架来处理学生的月考成绩数据。首先，我们准备了三个文本文件，分别包含了语文、数学和英语的月考成绩数据。这些数据被上传到HDFS的指定目录。
• 接着，我们启动了Hive Metastore服务，并通过Hive客户端连接到Hive。在Hive中，我们创建了一个分区表student_score，用于存储学生的成绩数据，其中分区字段为科目（subject）。
• 然后，我们按照科目将数据加载到分区表中，分别加载了语文、数学和英语的成绩数据。通过这样的分区方式，我们能够更方便地对数据进行查询和分析。
• 最后，我们使用Hive的SQL语句进行统计，计算每个学生在三个科目上的月考平均分。使用了AVG函数和ROUND函数来得到每个学生的平均分，并保留一位小数。这样，我们得到了每个学生在语文、数学和英语三个科目上的月考平均分的统计结果。
• 整个实战过程涉及了Hive的表创建、分区管理、数据加载和SQL查询等操作，展示了Hive在大数据处理中的灵活性和便捷性。通过这次实战，我们能够更好地理解和掌握Hive框架在数据分析和查询中的应用。

二、提出任务

• 语文月考成绩 - chinese.txt

1 张晓云 892 张晓云 733 张晓云 674 张晓云 705 张晓云 796 张晓云 877 张晓云 998 张晓云 839 张晓云 9710 张晓云 9211 张晓云 6712 张晓云 861 王东林 492 王东林 833 王东林 674 王东林 495 王东林 936 王东林 877 王东林 658 王东林 929 王东林 6010 王东林 9411 王东林 8112 王东林 901 李宏宇 772 李宏宇 663 李宏宇 894 李宏宇 875 李宏宇 966 李宏宇 797 李宏宇 878 李宏宇 969 李宏宇 6910 李宏宇 8711 李宏宇 9612 李宏宇 79

• 数学月考成绩 - math.txt

1 张晓云 792 张晓云 833 张晓云 774 张晓云 905 张晓云 896 张晓云 677 张晓云 898 张晓云 939 张晓云 9010 张晓云 8211 张晓云 7712 张晓云 961 王东林 782 王东林 943 王东林 764 王东林 705 王东林 906 王东林 837 王东林 858 王东林 829 王东林 8410 王东林 7811 王东林 9912 王东林 931 李宏宇 862 李宏宇 813 李宏宇 764 李宏宇 935 李宏宇 886 李宏宇 827 李宏宇 818 李宏宇 939 李宏宇 8610 李宏宇 9011 李宏宇 6712 李宏宇 88

• 英语月考成绩 - english.txt

1 张晓云 782 张晓云 833 张晓云 924 张晓云 665 张晓云 826 张晓云 897 张晓云 798 张晓云 689 张晓云 9610 张晓云 9111 张晓云 8712 张晓云 821 王东林 692 王东林 863 王东林 734 王东林 995 王东林 676 王东林 957 王东林 748 王东林 929 王东林 7610 王东林 8811 王东林 9212 王东林 561 李宏宇 882 李宏宇 783 李宏宇 924 李宏宇 785 李宏宇 896 李宏宇 767 李宏宇 928 李宏宇 759 李宏宇 8810 李宏宇 9211 李宏宇 9712 李宏宇 85

• 利用Hive框架，统计每个同学各科月考平均分

三、完成任务

（一）准备数据

1、在虚拟机上创建文本文件

• 在master虚拟机上创建chinese.txt文件
• 创建math.txt文件
• 创建english.txt文件

2、上传文件到HDFS指定目录

• 创建/subjectavg/input目录，执行命令：hdfs dfs -mkdir -p /subjectavg/input
• 将文本文件chinese.txt、math.txt与english.txt，上传到HDFS的/subjectavg/input目录

（二）实现步骤

1、启动Hive Metastore服务

• 执行命令：hive --service metastore &，在后台启动metastore服务

2、启动Hive客户端

• 执行命令：hive，看到命令提示符hive>

3、创建分区的学生成绩表

• 执行语句：CREATE TABLE student_score ( id INT, name STRING, score INT ) PARTITIONED BY (subject STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ';

4、按分区加载数据

• 加载chinese.txt到chinese分区
• 加载math.txt到math分区
• 加载english.txt到english分区
• 在Hadoop WebUI里查看分区表对应的分区目录
• 数据文件，比如chinese.txt就在对应分区目录里

5、查看分区表全部记录

• 执行语句：SELECT * FROM student_score;

1       张晓云  89      chinese
2       张晓云  73      chinese
3       张晓云  67      chinese
4       张晓云  70      chinese
5       张晓云  79      chinese
6       张晓云  87      chinese
7       张晓云  99      chinese
8       张晓云  83      chinese
9       张晓云  97      chinese
10      张晓云  92      chinese
11      张晓云  67      chinese
12      张晓云  86      chinese
1       王东林  49      chinese
2       王东林  83      chinese
3       王东林  67      chinese
4       王东林  49      chinese
5       王东林  93      chinese
6       王东林  87      chinese
7       王东林  65      chinese
8       王东林  92      chinese
9       王东林  60      chinese
10      王东林  94      chinese
11      王东林  81      chinese
12      王东林  90      chinese
1       李宏宇  77      chinese
2       李宏宇  66      chinese
3       李宏宇  89      chinese
4       李宏宇  87      chinese
5       李宏宇  96      chinese
6       李宏宇  79      chinese
7       李宏宇  87      chinese
8       李宏宇  96      chinese
9       李宏宇  69      chinese
10      李宏宇  87      chinese
11      李宏宇  96      chinese
12      李宏宇  79      chinese
1       张晓云  78      english
2       张晓云  83      english
3       张晓云  92      english
4       张晓云  66      english
5       张晓云  82      english
6       张晓云  89      english
7       张晓云  79      english
8       张晓云  68      english
9       张晓云  96      english
10      张晓云  91      english
11      张晓云  87      english
12      张晓云  82      english
1       王东林  69      english
2       王东林  86      english
3       王东林  73      english
4       王东林  99      english
5       王东林  67      english
6       王东林  95      english
7       王东林  74      english
8       王东林  92      english
9       王东林  76      english
10      王东林  88      english
11      王东林  92      english
12      王东林  56      english
1       李宏宇  88      english
2       李宏宇  78      english
3       李宏宇  92      english
4       李宏宇  78      english
5       李宏宇  89      english
6       李宏宇  76      english
7       李宏宇  92      english
8       李宏宇  75      english
9       李宏宇  88      english
10      李宏宇  92      english
11      李宏宇  97      english
12      李宏宇  85      english
1       张晓云  79      math
2       张晓云  83      math
3       张晓云  77      math
4       张晓云  90      math
5       张晓云  89      math
6       张晓云  67      math
7       张晓云  89      math
8       张晓云  93      math
9       张晓云  90      math
10      张晓云  82      math
11      张晓云  77      math
12      张晓云  96      math
1       王东林  78      math
2       王东林  94      math
3       王东林  76      math
4       王东林  70      math
5       王东林  90      math
6       王东林  83      math
7       王东林  85      math
8       王东林  82      math
9       王东林  84      math
10      王东林  78      math
11      王东林  99      math
12      王东林  93      math
1       李宏宇  86      math
2       李宏宇  81      math
3       李宏宇  76      math
4       李宏宇  93      math
5       李宏宇  88      math
6       李宏宇  82      math
7       李宏宇  81      math
8       李宏宇  93      math
9       李宏宇  86      math
10      李宏宇  90      math
11      李宏宇  67      math
12      李宏宇  88      math

6、按姓名和科目分组汇总平均分

• 执行语句：SELECT name, subject, ROUND(AVG(score), 1) AS average_score FROM student_score GROUP BY name, subject;
• 每个学生三科平均分倒是统计出进来了，但是格式我们并不满意，我们希望每个学生的三科平均分不是分占三行，而是在一行里显示三科平均分。

7、按姓名统计每个学生三科月考平均分

• 执行语句：SELECT name, ROUND(AVG(CASE WHEN subject = 'chinese' THEN score ELSE NULL END), 1) AS chinese_avg, ROUND(AVG(CASE WHEN subject = 'math' THEN score ELSE NULL END), 1) AS math_avg, ROUND(AVG(CASE WHEN subject = 'english' THEN score ELSE NULL END), 1) AS english_avg FROM student_score GROUP BY name;
• 这个SQL查询语句用于从一个名为student_score的表中检索学生的平均分数，并按学生姓名进行分组。以下是查询的解释说明：

1. SELECT子句- name: 选择学生的姓名。- ROUND(AVG(CASE WHEN subject = 'chinese' THEN score ELSE NULL END), 1) AS chinese_avg: 计算学生在"chinese"科目的平均分数，并将结果四舍五入到一位小数。使用CASE语句，只有当科目是"chinese"时才考虑该分数，否则将其视为NULL。- ROUND(AVG(CASE WHEN subject = 'math' THEN score ELSE NULL END), 1) AS math_avg: 计算学生在"math"科目的平均分数，同样将结果四舍五入到一位小数。- ROUND(AVG(CASE WHEN subject = 'english' THEN score ELSE NULL END), 1) AS english_avg: 计算学生在"english"科目的平均分数，同样将结果四舍五入到一位小数。
2. FROM子句- student_score: 表示数据来自名为student_score的表。
3. GROUP BY子句- GROUP BY name: 将结果按学生姓名进行分组，以便计算每个学生在不同科目上的平均分数。

• 因此，这个查询将返回一个结果集，其中包含每个学生的姓名以及他们在"chinese"、"math"和"english"科目上的平均分数，四舍五入到一位小数。

四、实战总结

• 本次实战运用Hive处理学生月考成绩数据，通过创建分区表、加载各科目成绩至对应分区，并使用SQL语句计算平均分，展示了Hive在大数据管理、分析查询及统计计算上的灵活性与便捷性。