Hive实战之航空数据分析

接着上Hive 大数据开发（一套打通）继续写

PS：最好是手敲代码一遍，有些代码可能是中文输入法打出来的，因为SQL代码块显示不出来Hive语法，导致看不出来到底是什么地方出现错误。所以盲目的直接复制粘贴会出现报错信息

用到的flights文件链接：

链接：https://pan.baidu.com/s/16WwGFjk22kBDPHX534-IcA?pwd=gch1
提取码：gch1
--来自百度网盘超级会员V5的分享

9.1需求描述

利用Hive实现航空数据分析的全流程数据管理。

9.2、业务实现

9.2.1、创建数据表

创建数据表的语句功能强大，形式多样，基本语法和常用选项如下：

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [<数据库名>.]<表名>
  [(<列名1> <数据类型1>, <列名2> <数据类型2>, ...)]
  [PARTITIONED BY (<分区列名1> <数据类型1>, <分区列名2> <数据类型2>, ...)]
  [ROW FORMAT <行格式>] 
  [STORED AS <文件存储格式>]
  [LOCATION <HDFS存储路径>]
  [AS <查询语句>];

使用 CREATE TABLE [IF NOT EXISTS] <表名> 语句创建数据表 flights 用于存储航班数据集，其中

IF NOT EXISTS 关键词表示仅在该数据表不存在时才创建；
列carrier、tailnum、origin和dest为字符串类型（string），其他列都是整数类型（int），数据类型会在后续章节详细介绍。

USE default; -- 切换数据库

CREATE TABLE IF NOT EXISTS fligths（
year int,
month int,
day int,
dep_time int,
dep_delay int,
arr_time int,
arr_delay int,
cancelled int,
carrier string,
tailnum string,
flight int,
origin sting,
dest string,
air_time int,
distance int,
hour int,
min int
）;

9.2.2、描述数据表

使用 DESCRIBE [FORMATTED] <表名> 语句描述数据表flights 。

DESCIBE FORMATTED flight;

9.2.3、列出数据表

使用 SHOW TABLES [LIKE '<正则表达式>'] 语句列出所有（满足正则表达式的）数据表。正则表达式中，星号 * 指代一个或多个字母，竖线 | 表示或。

SHOW TABLES;

9.2.4、删除数据表

使用 DROP TABLE [IF EXISTS] <表名> 语句删除表 flights ，其中

IF EXISTS 关键词表示仅在该表存在时才删除。

删除表时会同时删除表的元数据和数据。事实上，数据会移动到HDFS上的 .Trash/Current 文件夹中（思考一下内外表）。

DROP TABLE IF NOT EXISTS flights;

9.2.5、从查询结果创建数据表

使用 CREATE TABLE [IF NOT EXISTS] <表名> AS <查询语句> 语句从查询结果创建数据表 flights_top5 ，查询结果为简单选取 flights 表中的所有数据。

CREATE TABLE IF NOT EXISITS flights_top2 AS
  SELECT * FROM flights  LIMIT 5;

9.2.6、拷贝表结构

使用 CREATE TABLE [IF NOT EXISTS] <新表名> LIKE <原表名> 语句拷贝 flights 表结构创建数据表 flightscp，并不拷贝数据。

CREATE TABLE IF NOT EXISTS flight_cp LIKE flights;

9.2.7、从本地文件系统导入数据表

使用 CREATE TABLE 语句创建数据表 flights 用于存储航班数据集。

DROP TABLE IF EXISTS flights;

CREATE TABLE IF NOT EXISTS flights（
year int,
month int, 
day int, 
dep_time int ,
dep_delay int, 
arr_time int, 
arr_delay int, 
cancelled int, 
carrier string, 
tailnum string,
flight int, 
origin string, 
dest string,
air_time int, 
distance int, 
hour int, 
min int
）
ROW FORMAT DELIMITED FIELDS TERMINNATED BY ','
SORED AS TEXTFILE;

使用 LOAD DATA LOCAL INPATH '<数据文件路径>' INTO TABLE <表名> 语句将本地文件系统中的数据文件 /opt/data/flights/flights14.csv 导入表 flights ，其中

LOCAL 关键词表示从本地文件系统导入。

LOAD DATA LOCAL INPATH '/绝对路径/flights14.csv/'
INTO TABLE flights;
Loading data to table default.flights
TABLE defult.flights stats:[numFiles=1,totalSize=16150465]
-- 跑完上面的代码后

SELECT * FROM flights LIMIT 5;

9.2.8、从HDFS导入数据表

重新创建数据表flights。

DROP TABLE IF EXISTS flights;

CREATE TABLE IF NOT EXISTS flights（
year int,
month int, 
day int, 
dep_time int ,
dep_delay int, 
arr_time int, 
arr_delay int, 
cancelled int, 
carrier string, 
tailnum string,
flight int, 
origin string, 
dest string,
air_time int, 
distance int, 
hour int, 
min int
）
ROW FORMAT DELIMITED FIELDS TERMINNATED BY ','
SORED AS TEXTFILE;

新启动一个终端，使用hdfs dfs -put命令将数据文件flights14上传到HDFS。

hdfs dfs -put /opt/data/flights/flights14.csv

使用 LOAD DATA INPATH '<数据文件路径>' INTO TABLE <表名> 语句将HDFS中的数据文件 /user/root/flights14.csv 导入表 flights 。

LOAD DATA INPATH '/绝对路径/flights14.csv' INTO TABLE flights;

Loading data to table default.flights
Table default.flights stats: [numFiles=1, totalSize=16150465]

-- 该语句仅仅是将数据文件在HDFS上从原路径移动到Hive表对应的路径下。

SELECT * FROM flights limit 5;

9.2.9、导入并覆盖

由于表 flights 中已经有数据，如果不使用OVERWRITE关键词则会在原有数据基础上添加新导入数据。

LOAD DATA LOCAL INPATH '/绝对路径/flights14.csv' INTO TABLE flights;
Loading data to data default.flights
Table default.flights stats:[numFiles=2,totalSize=32300]

使用 dfs -ls 命令显示表 flights 对应文件夹的统计信息。

dfs -ls /user/hive/warehouse/flights;

-- 运行代码
Found 2 items
-rwxr-xr-x   1 root supergroup   16150465 2020-08-04 06:37 /user/hive/warehouse/flights/flights14.csv
-rwxr-xr-x   1 root supergroup   16150465 2020-08-04 06:40 /user/hive/warehouse/flights/flights14_copy_1.csv

可以看出，该表对应的数据文件有2个，且文件大小完全一样。

使用 OVERWRITE 关键词导入并覆盖，将仅保留新导入数据。

LOAD DATA LOCAL INPATH '/opt/data/flights/flights14.csv' OVERWRITE INTO TABLE flights;

Loading data to table default.flights
Table default.flights stats: [numFiles=1, numRows=0, totalSize=16150465, rawDataSize=0]

使用 dfs -ls 命令显示表flights对应文件夹的统计信息。

dfs -ls /user/hive/warehouse/flights; 

-- 运行代码
Found 1 items
-rwxr-xr-x   1 root supergroup   16150465 2020-08-04 06:42 /user/hive/warehouse/flights/flights14.csv

可以看出，该表对应的数据文件仅有1个。

.2.10、导入分区表

使用 CREATE TABLE ... PARTITIONED BY ... 语句创建分区表 flights ，其中分区列为 load_date 。

DROP TABLE IF EXISTS flights;

CREATE TABLE IF NOT EXISTS flights（
year int,
month int, 
day int, 
dep_time int ,
dep_delay int, 
arr_time int, 
arr_delay int, 
cancelled int, 
carrier string, 
tailnum string,
flight int, 
origin string, 
dest string,
air_time int, 
distance int, 
hour int, 
min int
）
ROW FORMAT DELIMITED FIELDS TERMINNATED BY ','
SORED AS TEXTFILE;

数据导入时，使用 PARTITION 关键词指定导入的分区。

LOAD DATA LOCAL INPATH '/opt/data/flights/flights14.csv' INTO TABLE flights PARTITION (load_date='2018-10-12');

数据插入语句的基本语法和常用选项如下：

INSERT INTO|OVERWRITE TABLE <表名>

[PARTITION (<分区列名1>=<分区列值1>, <分区列名2>=<分区列值2>, ...)] <查询语句>;

9.2.11、基本插入

使用 CREATE TABLE 语句创建数据表 flights 用于存储航班数据集。

DROP TABLE IF EXISTS flights;

CREATE TABLE IF NOT EXISTS flights（
year int,
month int, 
day int, 
dep_time int ,
dep_delay int, 
arr_time int, 
arr_delay int, 
cancelled int, 
carrier string, 
tailnum string,
flight int, 
origin string, 
dest string,
air_time int, 
distance int, 
hour int, 
min int
）

使用 INSERT INTO TABLE <表名> <查询语句> 语句将查询结果插入表 flights ，查询结果为简单选取 flights 表中的所有数据。

INSERT INTO TABLE flights
SELECT * FROM flights；

9.2.12、插入分区表

使用 CREATE TABLE ... PARTITIONED BY ... 语句创建分区表 flights ，其中分区列为 year 和 month 。

DROP TABLE IF EXISTS flights;

CREATE TABLE IF NOT EXISTS flights（
year int,
month int, 
day int, 
dep_time int ,
dep_delay int, 
arr_time int, 
arr_delay int, 
cancelled int, 
carrier string, 
tailnum string,
flight int, 
origin string, 
dest string,
air_time int, 
distance int, 
hour int, 
min int
）
PARTITIONED BY (year int,month int);

数据插入时，使用 PARTITION 关键词指定导入的分区。

INSERT OVERWRITE TABLE flights PARTITION(year =2014,month=1)
SELECT day,
dept_time,
dept_delay,
arr_time,
arr_delay,
cancelled,
carrier, 
tailnum,
flight, 
origin, 
dest, 
air_time, 
distance, 
hour, 
min   FROM flights
WHRER year = 2014 AND month=1;

INSERT OVERWRITE TABLE flights PARTITION (year=2014, month=2)
SELECT day, dep_time, dep_delay, arr_time, arr_delay, cancelled, carrier, 
tailnum, flight, origin, dest, air_time, distance, hour, min 
FROM flights
WHERE year=2014 and month=2;

使用 SHOW PARTITIONS 语句显示分区表 flights 的分区

SHOW PARTITIONS flights;

9.2.13、动态插入分区表

以上的例子插入分区表时，插入每个分区都需要指定分区列的值，如果分区较多则会非常繁琐。动态分区只需要使用一条语句，自动根据分区列的值，插入相应的分区。

在动态插入分区表前，需要先设置2个选项：

选项 hive.exec.dynamic.partition.mode 表示动态分区模式， strict 模式中用户必须至少指定一个分区以防止用户意外的覆盖所有分区， nonstrict 模式中用户可以不指定任何一个分区，即所有分区都是动态的，默认为 strict ；
选项 hive.exec.dynamic.partition 表示是否允许动态分区， true 表示允许， false 表示不允许，默认为 true 。
SET hive.exec.dynamic.partition.mode = nonstrict;
SET hive.exec.dynamic.partition = true;

使用 INSERT ... PARTITION ... 语句插入分区表。

INSERT INTO TABLE flights PARTITION(year, month)
SELECT day, dep_time, dep_delay, arr_time, arr_delay, cancelled, carrier, 
tailnum, flight, origin, dest, air_time, distance, hour, min,
year, month
FROM flights;

使用 SHOW PARTITIONS 语句显示分区表 flights 的分区。

SHOW PARTITIONS flights;

9.2.14、数据查询分析

1、数据查询语句的基本语法和常用选项如下：

ELECT [DISTINCT] <列名表达式1> [AS <列别名1>], <列名表达式2> [AS <列别名2>], ...
FROM <表名>
[WHERE <筛选条件>]
[GROUP BY <列名表达式列表>]
[HAVING <筛选条件>]
[ORDER BY <列名表达式列表>]
[LIMIT <行数>]

2、使用 SELECT ... FROM ... LIMIT ... 语句从航班表 flights 中选取所有列，并返回前5行，其中

星号*表示选取所有列；
LIMIT语句表示保留前几行。

SELECT * FROM flights LIMIT 5;

3、使用 WHERE语句筛选航班表 flights 中出发地（列 origin ）为 'JFK' 、月份（列 month ）为6月的数据行，其中

字符串可以可以使用单引号 ' 或双引号 " ；
一个等号 = 表示比较是否相等；
运算符 and 表示多个条件“与”的关系。

SELECT * 
FROM flights
WHERE origin = 'JFK' and month = 6
LIMIT 5;

4、使用 ORDER BY 语句将返回结果按出发地（列 origin ）顺序、目的地（列 dest ）倒序排列，其中

DESC 关键词表示倒序排列， ASC 关键词表示顺序排列，默认为顺序排列，因此 ASC 可以省略。

SELECT * 
FROM flights
ORDER BY origin, dest DESC
LIMIT 5;

5、使用 SELECT 语句后跟列名，选取航班到达延误分钟数（列 arr_delay ）和出发延误分钟数（列 dep_delay ），并使用 AS 关键词将这两列重命名为 delay_arr 和 delay_dep 。

SELECT arr_delay AS delay_arr, dep_delay AS delay_dep
FROM flights
LIMIT 5;

6、使用 SELECT 语句后跟列名的表达式，新计算返回2列：平均速度（列 speed ）和总延误分钟数（列 delay ）。

SELECT distance / (air_time / 60) AS speed,
arr_delay + dep_delay AS delay
FROM flights
LIMIT 5;

7、所谓数据聚合，就是将原数据中的多行按一定的计算方法合并成一行返回，其中有两个要素：

计算方法：在Hive中通过聚合函数指定；
分组方式：即哪些原数据中的行合并成返回结果的一行，在Hive中通过 SELECT 语句中未包含在聚合函数中的列以及 GROUP BY 语句中的列指定。
调用聚合函数 count() 计算表 flights 的行数.
```
SELECT count(1)
FROM flights;
```

8、使用 GROUP BY 语句指定按出发地（列 origin ）分组，计算各出发地的行数

SELECT origin ,count(1)
FROM flights
GROUP BY origin;

9、使用 GROUP BY 语句指定多个列，计算各不同出发地（列 origin ）和目的地（列 dest ）的组合中，航空公司代码（列 carrier ）为 'AA' 的行数。

SELECT origin,dest,count(1)
FROM flights
WHERE carrier='AA'
GROUP BY origin,dest;

posted @ 2022-10-26 11:52 去揽一池星河阅读(379) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· Hive 大数据开发（一套打通）

· Hive SQL DDL建表高阶语法

· Hive SQL基本使用

· Hive基本语句及函数用法

· Hive 总结

公告

昵称：去揽一池星河
园龄： 2年10个月
粉丝： 2
关注： 0

+加关注

2025年3月

日

一

二

三

四

五

六

catch-autumn