[大数据]【大数据】Hadoop 体系（四）

Hadoop

五、Apache Hive DML语句与函数使用

1. Hive SQL DML语法之加载数据

1.1 Hive SQL-DML-Load加载数据

1.1.1 回顾

• 在Hive中建表成功之后，就会在HDFS上创建一个与之对应的文件夹，且文件夹名字就是表名；
• 文件夹父路径是由参数hive.metastore.warehouse.dir控制，默认值是/user/hive/warehouse；
  
• 不管路径在哪里，只有把数据文件移动到对应的表文件夹下面，Hive才能映射解析成功;
• 最原始暴力的方式就是使用hadoop fs –put|-mv等方式直接将数据移动到表文件夹下；
• 但是，Hive官方推荐使用Load命令将数据加载到表中。

1.1.2 Load语法功能

• Load英文单词的含义为：加载、装载；
• 所谓加载是指：将数据文件移动到与Hive表对应的位置，移动时是纯复制、移动操作。
• 纯复制、移动指在数据load加载到表中时，Hive不会对表中的数据内容进行任何转换，任何操作。

1.1.3 Load语法规则

• LOAD DATA [LOCAL] INPATH ‘filepath’ [OVERWRITE] INTO TABLE tablename;

1.1.4 语法规则之filepath

• filepath表示待移动数据的路径。可以指向文件（在这种情况下，Hive将文件移动到表中），也可以指向目录（在这种情况下，Hive将把该目录中的所有文件移动到表中）。
• filepath文件路径支持下面三种形式，要结合LOCAL关键字一起考虑：
  • 相对路径，例如：project/data1
  • 绝对路径，例如：/user/hive/project/data1
  • 具有schema的完整URI，例如：hdfs://namenode:9000/user/hive/project/data1

1.1.5 语法规则之LOCAL

• 指定LOCAL， 将在本地文件系统中查找文件路径。
  • 若指定相对路径，将相对于用户的当前工作目录进行解释；
  • 用户也可以为本地文件指定完整的URI-例如：file:///user/hive/project/data1。
• 没有指定LOCAL关键字。
  • 如果filepath指向的是一个完整的URI，会直接使用这个URI；
  • 如果没有指定schema，Hive会使用在hadoop配置文件中参数fs.default.name指定的（不出意外，都是HDFS）。

1.1.6 LOCAL本地是哪里？

• 如果对HiveServer2服务运行此命令
  • 本地文件系统指的是Hiveserver2服务所在机器的本地Linux文件系统，不是Hive客户端所在的本地文件系统。
    

1.1.7 练习 Load Data From Local FS or HDFS

--step1:建表
--建表student_local 用于演示从本地加载数据
create table student_local(num int,name string,sex string,age int,dept string) row format delimited fields terminated by ',';
--建表student_HDFS 用于演示从HDFS加载数据
create external table student_HDFS(num int,name string,sex string,age int,dept string) row format delimited fields terminated by ',';

--建议使用beeline客户端 可以显示出加载过程日志信息
--step2:加载数据
-- 从本地加载数据 数据位于HS2（node1）本地文件系统 本质是hadoop fs -put上传操作
LOAD DATA LOCAL INPATH '/root/hivedata/students.txt' INTO TABLE student_local;
--从HDFS加载数据 数据位于HDFS文件系统根目录下 本质是hadoop fs -mv 移动操作
--先把数据上传到HDFS上 hadoop fs -put /root/hivedata/students.txt /
LOAD DATA INPATH '/students.txt' INTO TABLE student_HDFS;

• 加载数据日志信息
  

1.2 Hive SQL-DML-Insert插入数据

1.2.1 Insert语法功能

• Hive官方推荐加载数据的方式：清洗数据成为结构化文件，再使用Load语法加载数据到表中。这样的效率更高。
• 也可以使用insert语法把数据插入到指定的表中，最常用的配合是把查询返回的结果插入到另一张表中。

1.2.2 insert+select

• insert+select表示：将后面查询返回的结果作为内容插入到指定表中。

  • 需要保证查询结果列的数目和需要插入数据表格的列数目一致。
  • 如果查询出来的数据类型和插入表格对应的列数据类型不一致，将会进行转换，但是不能保证转换一定成功，转换失败的数据将会为NULL。

• INSERT INTO TABLE tablename select_statement1 FROM from_statement;

--step1:创建一张源表student
drop table if exists student;
create table student(num int,name string,sex string,age int,dept string)
row format delimited
fields terminated by ',';
--加载数据
load data local inpath '/root/hivedata/students.txt' into table student;

--step2：创建一张目标表 只有两个字段
create table student_from_insert(sno int,sname string);
--使用insert+select插入数据到新表中
insert into table student_from_insert select num,name from student;

select *
from student_insert1;

2. Hive SQL DML语法之查询数据

2.1 Hive SQL select语法介绍

2.1.1 Select语法树

• 从哪里查询取决于FROM关键字后面的table_reference，这是我们写查询SQL的首先要确定的事即你查询谁？
• 表名和列名不区分大小写。
• SELECT [ALL | DISTINCT] select_expr, select_expr, …
  • FROM table_reference
  • [WHERE where_condition]
  • [GROUP BY col_list]
  • [ORDER BY col_list]
  • [LIMIT [offset,] rows];

2.1.2 练习 美国Covid-19新冠数据之select查询

• 数据文件《us-covid19-counties.dat》，里面记录了2021-01-28美国各个县累计新冠确诊病例数和累计死亡病例数。
• 数据环境准备

--创建表t_usa_covid19
drop table if exists t_usa_covid19;
CREATE TABLE t_usa_covid19(
count_date string,
county string,
state string,
fips int,
cases int,
deaths int)
row format delimited fields terminated by ",";
--将源数据load加载到t_usa_covid19表对应的路径下
load data local inpath '/root/hivedata/us-covid19-counties.dat' into table t_usa_covid19;

• select_expr表示检索查询返回的列，必须至少有一个select_expr。

--1、select_expr
--查询所有字段或者指定字段
select * from t_usa_covid19;
select county, cases, deaths from t_usa_covid19;
--查询当前数据库
select current_database(); --省去from关键字

• ALL 、DISTINCT
  • 用于指定查询返回结果中重复的行如何处理。
  • 如果没有给出这些选项，则默认值为ALL（返回所有匹配的行）。
  • DISTINCT指定从结果集中删除重复的行。

--2、ALL DISTINCT
--返回所有匹配的行
select state from t_usa_covid19;
--相当于
select all state from t_usa_covid19;
--返回所有匹配的行 去除重复的结果
select distinct state from t_usa_covid19;
--多个字段distinct 整体去重
select distinct county,state from t_usa_covid19;

• WHERE
  • WHERE后面是一个布尔表达式（结果要么为true，要么为false），用于查询过滤，当布尔表达式为true时，返回select后面expr表达式的结果，否则返回空。
  • 在WHERE表达式中，可以使用Hive支持的任何函数和运算符，但聚合函数除外。

--3、WHERE CAUSE
select * from t_usa_covid19 where 1 > 2; -- 1 > 2 返回false
select * from t_usa_covid19 where 1 = 1; -- 1 = 1 返回true
--找出来自于California州的疫情数据
select * from t_usa_covid19 where state = "California";
--where条件中使用函数 找出州名字母长度超过10位的有哪些
select * from t_usa_covid19 where length(state) >10 ;
--注意：where条件中不能使用聚合函数
-- --报错 SemanticException:Not yet supported place for UDAF ‘sum'
--聚合函数要使用它的前提是结果集已经确定。
--而where子句还处于“确定”结果集的过程中，因而不能使用聚合函数。
select state,sum(deaths) from t_usa_covid19 where sum(deaths) >100 group by state;
--可以使用Having实现
select state,sum(deaths) from t_usa_covid19 group by state having sum(deaths) > 100;

• 聚合操作
  • SQL中拥有很多可用于计数和计算的内建函数，其使用的语法是：SELECT function(列) FROM 表。
  • 这里我们要介绍的叫做聚合（Aggregate）操作函数，如：Count、Sum、Max、Min、Avg等函数。
  • 聚合函数的最大特点是不管原始数据有多少行记录，经过聚合操作只返回一条数据，这一条数据就是聚合的结果。
    
  • 常见的聚合操作函数
    • AVG(column) 返回某列的平均值
    • COUNT(column) 返回某列的行数（不包括 NULL 值）
    • COUNT(*) 返回被选行数
    • MAX(column) 返回某列的最高值
    • MIN(column) 返回某列的最低值
    • SUM(column) 返回某列的总和

--4、聚合操作
--统计美国总共有多少个县county
select count(county) from t_usa_covid19;
--统计美国加州有多少个县
select count(county) from t_usa_covid19 where state = "California";
--统计德州总死亡病例数
select sum(deaths) from t_usa_covid19 where state = "Texas";
--统计出美国最高确诊病例数是哪个县
select max(cases) from t_usa_covid19;

• GROUP BY 概念
  • GROUP BY语句用于结合聚合函数，根据一个或多个列对结果集进行分组；
  • 如果没有group by语法，则表中的所有行数据当成一组。
    

--5、GROUP BY
--根据state州进行分组 统计每个州有多少个县county
select count(county) from t_usa_covid19 where count_date = "2021-01-28" group by state;
--想看一下统计的结果是属于哪一个州的
select state,count(county) from t_usa_covid19 where count_date = "2021-01-28" group by state;
--再想看一下每个县的死亡病例数，我们猜想很简单呀 把deaths字段加上返回 真实情况如何呢？
select state,count(county),deaths from t_usa_covid19 where count_date = "2021-01-28" group by state;
--很尴尬 sql报错了org.apache.hadoop.hive.ql.parse.SemanticException:Line 1:27 Expression not in GROUP BY key 'deaths'
--为什么会报错？？group by的语法限制
--结论：出现在GROUP BY中select_expr的字段：要么是GROUP BY分组的字段；要么是被聚合函数应用的字段。
--deaths不是分组字段 报错
--state是分组字段 可以直接出现在select_expr中
--被聚合函数应用
select state,count(county),sum(deaths) from t_usa_covid19 where count_date = "2021-01-28" group by state;

• GROUP BY语法限制
  • 出现在GROUP BY中select_expr的字段：要么是GROUP BY分组的字段；要么是被聚合函数应用的字段。
  • 原因：避免出现一个字段多个值的歧义。
    • 分组字段出现select_expr中，一定没有歧义，因为就是基于该字段分组的，同一组中必相同；
    • 被聚合函数应用的字段，也没歧义，因为聚合函数的本质就是多进一出，最终返回一个结果。
      
• HAVING
  • 在SQL中增加HAVING子句原因是，WHERE关键字无法与聚合函数一起使用。
  • HAVING子句可以让我们筛选分组后的各组数据,并且可以在Having中使用聚合函数，因为此时where，group by已经执行结束，结果集已经确定。

--6、having
--统计2021-01-28死亡病例数大于10000的州
select state,sum(deaths) from t_usa_covid19 where count_date = "2021-01-28" and sum(deaths) >10000 group by state;
--where语句中不能使用聚合函数 语法报错
--先where分组前过滤，再进行group by分组， 分组后每个分组结果集确定 再使用having过滤
select state,sum(deaths) from t_usa_covid19 where count_date = "2021-01-28" group by state having sum(deaths) > 10000;
--这样写更好 即在group by的时候聚合函数已经作用得出结果 having直接引用结果过滤 不需要再单独计算一次了
select state,sum(deaths) as cnts from t_usa_covid19 where count_date = "2021-01-28" group by state having cnts> 10000;

• HAVING与WHERE区别
  • having是在分组后对数据进行过滤
  • where是在分组前对数据进行过滤
  • having后面可以使用聚合函数
  • where后面不可以使用聚合函数
• ORDER BY
  • ORDER BY 语句用于根据指定的列对结果集进行排序。
  • ORDER BY 语句默认按照升序（ASC）对记录进行排序。如果您希望按照降序对记录进行排序，可以使用DESC关键字

--7、order by
--根据确诊病例数升序排序 查询返回结果
select * from t_usa_covid19 order by cases;
--不写排序规则 默认就是asc升序
select * from t_usa_covid19 order by cases asc;
--根据死亡病例数倒序排序 查询返回加州每个县的结果
select * from t_usa_covid19 where state = "California" order by cases desc;

• LIMIT
  • LIMIT用于限制SELECT语句返回的行数。
  • LIMIT接受一个或两个数字参数，这两个参数都必须是非负整数常量。
  • 第一个参数指定要返回的第一行的偏移量（从 Hive 2.0.0开始），第二个参数指定要返回的最大行数。当给出单个参数时，它代表最大行数，并且偏移量默认为0。

--8、limit
--没有限制返回2021.1.28 加州的所有记录
select * from t_usa_covid19 where count_date = "2021-01-28" and state ="California";
--返回结果集的前5条
select * from t_usa_covid19 where count_date = "2021-01-28" and state ="California" limit 5;
--返回结果集从第1行开始 共3行
select * from t_usa_covid19 where count_date = "2021-01-28" and state ="California" limit 2,3;
--注意 第一个参数偏移量是从0开始的

• 执行顺序
  • 在查询过程中执行顺序：from > where > group（含聚合）> having > order > select；
    • 聚合语句(sum,min,max,avg,count)要比having子句优先执行
    • where子句在查询过程中执行优先级别优先于聚合语句(sum,min,max,avg,count)

--执行顺序
select state,sum(deaths) as cnts from t_usa_covid19
where count_date = "2021-01-28"
group by state
having cnts> 10000
limit 2;