第8周 · 查询操作

查询操作

1.1基本查询

1.1.1查询显示所有字段

使用select语句查询显示temperature表的所有字段，具体操作如图1-1所示。

select * from temperature limit 3;

📷 图1-1显示表所有字段

1.1.2查询显示部分字段

使用select语句查询显示temperature表的部分字段，具体操作如图1-2所示。

select year,temperature from temperature limit 3;

📷 图1-2显示表部分字段

1.1.3 where条件查询

（1）使用where子句，将不满足条件的行过滤掉

（2）where子句紧随from子句

（3）案例实操

使用where语句对temperature表进行过滤，查询显示气温值小于10的记录，具体操作如图1-3所示。

select * from temperature where temperature<=10 limit 3;

📷 图1-3查询气温值小于10的记录

1.1.4 distinct去重查询

使用distinct语句对temperature表中的气温字段进行去重查询，具体操作如图1-4所示。

select distinct temperature from temperature limit 3;

📷 图1-4对气温值去重查询

1.1.5 group by分组查询

使用group by语句对temperature表按气象站ID来分组统计每个气象站的平均气温，具体操作如图1-5所示。

select id,sum(temperature)/count(*) from temperature group by id limit 3;

📷 图1-5分组统计每个气象站的平均气温

1.1.6 order by全局排序

使用order by语句对temperature表按照气温值进行全局排序，具体操作如图7-42所示。

select * from temperature order by temperature limit 3;

📷 图1-6按照气温值对表进行全局排序

备注：order by语句会对表进行全局排序，因此底层作业只运行一个reduce任务。当表的数据规模较大时，全局排序需要运行的时间比较长。

7.sort by局部排序

使用sort by语句对temperature表进行局部排序，为了便于观察局部排序效果，可以将reduce任务的并行度设置为3，同时将局部排序后的结果输出到HDFS，具体操作如图1-7所示。

set mapreduce.job.reduces=3;

INSERT OVERWRITE DIRECTORY '/weather/temperature' ROW FORMAT DELIMITED FIELDS TERMINATED by ',' select * from temperature sort by temperature;

📷 图1-7按照气温值对表进行局部排序

使用HDFS命令查看局部排序结果，可以看出每个输出文件结果局部有序，查看结果如图1-8所示。

bin/hdfs dfs -cat /weather/temperature/000000_0 | head -3

bin/hdfs dfs -cat /weather/temperature/000001_0 | head -3

bin/hdfs dfs -cat /weather/temperature/000002_0 | head -3

📷 图1-8按照气温值对表进行局部排序

8.distribute by分区查询

使用distribute by语句对temperature表中的数据按照气象站ID进行分区，具体操作如图1-9所示。

set mapreduce.job.reduces=3;

INSERT OVERWRITE DIRECTORY '/weather/temperature' ROW FORMAT DELIMITED FIELDS TERMINATED by ',' select * from temperature DISTRIBUTE BY id;

📷 图1-9按照气象站ID对表进行分区

使用HDFS命令查看分区文件，可以看出数据按照气象站ID分配到不同的分区，具体结果如图1-10所示。

bin/hdfs dfs -cat /weather/temperature/000000_0 | head -3

bin/hdfs dfs -cat /weather/temperature/000001_0 | head -3

bin/hdfs dfs -cat /weather/temperature/000002_0 | head -3

📷 图1-10数据按照气象站ID进行分区

9.cluster by分区排序

cluster by兼具distribute by和sort by功能。当distribute by和sort by指定的字段相同时，即可使用cluster by替换。使用cluster by语句对temperature表中的数据按照气象站ID进行分区和排序，具体操作如图1-11所示。

set mapreduce.job.reduces=3;

INSERT OVERWRITE DIRECTORY '/weather/temperature' ROW FORMAT DELIMITED FIELDS TERMINATED by ',' select * from temperature cluster by id;

📷 图1-11 按照气象站ID对表分区和排序

使用HDFS命令查看输出文件，可以看出数据按照气象站ID分区并排序，具体结果如图1-12所示。

bin/hdfs dfs -cat /weather/temperature/000000_0 | head -3

bin/hdfs dfs -cat /weather/temperature/000001_0 | head -3

bin/hdfs dfs -cat /weather/temperature/000002_0 | head -3

📷 图1-12数据按照气象站ID进行分区和排序

1.3 Hive表连接相关操作

和直接使用MapReduce相比，使用Hive简化了多表连接操作，极大地降低开发成本。

1.3.1等值连接

使用join子句实现temperature和station表的等值连接，具体操作如图1-13所示。

select t.id,t.year,t.temperature,s.state,s.latitude,s.longitude,s.elevation from temperature t join station s on (t.id==s.id) limit 3;

📷 图1-13表的等值连接

备注：表的等值连接是内连接的子集。

1.3.2内连接

（1）解释

内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。

（2）示例

使用inner join子句实现temperature和station表的内连接，具体操作如图1-14所示。

select t.id,t.year,t.temperature,s.state,s.latitude,s.longitude,s.elevation from temperature t inner join station s on t.id==s.id limit 3;

📷 图1-14表的内连接

备注：与等值连接相比，表的内连接的条件可以相等，也可以不相等。

1.3.3左连接

（1）解释

左连接：join操作符左边表中符合where子句的所有记录将会被返回。

（2）示例

使用left join子句实现temperature和station表的左连接，具体操作如图1-15所示。

select t.id,t.year,t.temperature,s.state,s.latitude,s.longitude,s.elevation from temperature t left join station s on t.id==s.id limit 3;

📷 图1-15表的左连接

备注：使用左连接会显示左表temperature的所有数据，如果右表station通过外键与左表temperature有匹配数据就显示对应字段的数据，否则右表字段都显示为\N。

1.3.4右连接

（1）解释

右连接：join操作符右边表中符合where子句的所有记录将会被返回。

（2）示例

使用right join子句实现temperature和station表的右连接，具体操作如图1-16所示。

select t.id,t.year,t.temperature,s.state,s.latitude,s.longitude,s.elevation from temperature t right join station s on t.id==s.id limit 3;

📷 图1-16表的右连接

备注：使用右连接会显示右表station的所有数据，如果左表temperature通过外键与右表station有匹配数据就显示对应字段的数据，否则右表字段都显示为\N。

6.4.5全连接

（1）解释

满外连接：将会返回所有表中符合where语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用null值替代。

（2）示例

使用full join子句实现temperature和station表的全连接，具体操作如图1-17所示。

select t.id,t.year,t.temperature,s.state,s.latitude,s.longitude,s.elevation from temperature t full join station s on t.id==s.id limit 3;

📷 图1-17表的全连接

备注：使用全连接会显示temperature和station表的所有数据，两个表通过id键进行关联，如果temperature表无匹配的数据则对应字段显示为\N，反之，station表对应的字段显示为\N。

II