Hive SQL DML语法

上篇导入文件的方式比较暴力，那么怎么做比较合适呢？Hive官方推荐使用Load命令将数据加载到表中。

Hive SQL-DML-Load加载数据 ：

Load语法规则：
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename;
语法规则之filepath ：

• filepath表示待移动数据的路径。可以指向文件（在这种情况下， Hive将文件移动到表中），也可以指向目录（在这种情况下， Hive将把该目录中的所有文件移动到表中）。
• filepath文件路径支持下面三种形式，要结合LOCAL关键字一起考虑：

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

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

Step1:建表 ，导入SQL 05_Apache Hive DML语句与函数使用.sql

--建表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 ',';

Step2:load加载数据 students.txt

-- 从本地加载数据 数据位于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 /
-- 相当于加载HDFS文件系统的数据， / 表示从根目录加载，那首先要上传，当加载完成后，这个文件会移动指定的表里面
LOAD DATA INPATH '/students.txt' INTO TABLE student_HDFS

Hive SQL-DML-Insert插入数据 ：

Insert语法功能 ：
Hive官方推荐加载数据的方式： 清洗数据成为结构化文件，再使用Load语法加载数据到表中。这样的效率更高。
Insert+Select：也可以使用insert语法把数据插入到指定的表中，最常用的配合是把查询返回的结果插入到另一张表中。
（1）需要保证查询结果列的数目和需要插入数据表格的列数目一致。
（2）如果查询出来的数据类型和插入表格对应的列数据类型不一致，将会进行转换，但是不能保证转换一定成功，转换失败的数据将会为NULL。

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

Hive SQL DML语法之查询数据 ：

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];

案例 美国Covid-19新冠数据之select查询 ：
（1）数据环境准备：us-covid19-counties.txt

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 *  from t_usa_covid19;

select county, cases, deaths from t_usa_covid19;
--查询常数返回 此时返回的结果和表中字段无关
select 1 from t_usa_covid19;
--查询当前数据库
select current_database(); --省去from关键字

--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;

（2） ALL 、 DISTINCT
用于指定查询返回结果中重复的行如何处理。
1. 如果没有给出这些选项，则默认值为ALL（返回所有匹配的行） 。
2. 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;
   a      BBB
   c      BBB
   a      BBB
   d      CCC
   a      WWW
-- 结果，是两个都重复才会进行去重
   a      BBB
   c      BBB
   d      CCC

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

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[LIMIT [offset,] rows];

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;

（4）聚合操作 ：
SQL中拥有很多可用于计数和计算的内建函数，其使用的语法是： SELECT function(列) FROM 表。
这里我们要介绍的叫做聚合（Aggregate）操作函数，如： Count、 Sum、 Max、 Min、 Avg等函数。
聚合函数的最大特点是不管原始数据有多少行记录，经过聚合操作只返回一条数据，这一条数据就是聚合的结果。

常见的聚合函数：

--统计美国总共有多少个县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;

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

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[LIMIT [offset,] rows];

group by 不能使用select查询group by 之外的原因：比如根据category分组，查询到每个不同类型的个数，blue 3个，Green 3个，Red 3个，那么 如果显示value呢？是显示第一行的value，还是第二行的value，这个就会存在歧义，所以不能使用group by和聚合函数之外的字段；如果group by 两个字段呢？那就相当于distinct两个字段，需要同时满足两个查询条件

GROUP BY使用 ： 使用 Group BY 意思是按照某个字段进行分组，select查询时也只能是这个字段，但是可以用聚合函数查询其他语句

--根据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语法限制 ：https://segmentfault.com/a/1190000006821331
出现在GROUP BY中select_expr的字段： 要么是GROUP BY分组的字段； 要么是被聚合函数应用的字段。
原因： 避免出现一个字段多个值的歧义。
（1）分组字段出现select_expr中，一定没有歧义，因为就是基于该字段分组的，同一组中必相同；
（2）被聚合函数应用的字段，也没歧义，因为聚合函数的本质就是多进一出，最终返回一个结果
原因：如果返回的不是group by的字段，那多条整合成一条之后，显示哪一条呢？group by 类似于统计

（6） HAVING ：
在SQL中增加HAVING子句原因是， WHERE关键字无法与聚合函数一起使用。
HAVING子句可以让我们筛选分组后的各组数据,并且可以在Having中使用聚合函数，因为此时where， group by已经执行结束，结果集已经确定。

--统计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区别 ：
（1）having是在分组后对数据进行过滤
（2）where是在分组前对数据进行过滤
（3）having后面可以使用聚合函数
（4）where后面不可以使用聚合函数

（7） ORDER BY ：
ORDER BY 语句用于根据指定的列对结果集进行排序。
ORDER BY 语句默认按照升序（ASC） 对记录进行排序。如果您希望按照降序对记录进行排序，可以使用DESC关键字

--根据确诊病例数升序排序 查询返回结果
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;

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

--没有限制返回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)
结合下面SQL感受 ：

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

Join操作和MYSQL类似，不写了
inner join 内连接 ：
内连接是最常见的一种连接，它也被称为普通连接，其中inner可以省略： inner join == join ；
只有进行连接的两个表中都存在与连接条件相匹配的数据才会被留下来。
left join 左连接 ：
left join中文叫做是左外连接(Left Outer Join)或者左连接，其中outer可以省略， left outer join是早期的写法。
left join的核心就在于left左。左指的是join关键字左边的表，简称左表。
通俗解释： join时以左表的全部数据为准，右边与之关联；左表数据全部返回，右表关联上的显示返回，关联不上的显示null返回

Hive 函数概述及分类标准 ：

Hive内建了不少函数， 用于满足用户不同使用需求， 提高SQL编写效率：
使用 show functions 查看当下可用的所有函数；
通过 describe function extended funcname 来查看函数的使用方式。

Hive的函数分为两大类： 内置函数（Built-in Functions）、 用户定义函数UDF（User-Defined Functions）：
内置函数可分为： 数值类型函数、日期类型函数、字符串类型函数、集合函数、条件函数等；
用户定义函数根据输入输出的行数可分为3类： UDF、 UDAF、 UDTF

根据函数输入输出的行数：
UDF（User-Defined-Function）普通函数，一进一出
UDAF（User-Defined Aggregation Function）聚合函数，多进一出
UDTF（User-Defined Table-Generating Functions）表生成函数，一进多出

UDF分类标准扩大化 :
UDF分类标准本来针对的是用户自己编写开发实现的函数。 UDF分类标准可以扩大到Hive的所有函数中：包括内置函数和用户自定义函数。

Hive 常用的内置函数 ：

字符串长度函数： length
字符串反转函数： reverse
字符串连接函数： concat
带分隔符字符串连接函数： concat_ws
字符串截取函数： substr,substring
------------String Functions 字符串函数------------
select length("itcast");
select reverse("itcast");
select concat("angela","baby");
--带分隔符字符串连接函数： concat_ws(separator, [string | array(string)]+)
select concat_ws('.', 'www', array('itcast', 'cn'));
--字符串截取函数： substr(str, pos[, len]) 或者 substring(str, pos[, len])
select substr("angelababy",-2); --pos是从1开始的索引，如果为负数则倒着数
select substr("angelababy",2,2);
--分割字符串函数: split(str, regex)
select split('apache hive', ' ');

--获取当前日期: current_date
select current_date();
--获取当前UNIX时间戳函数: unix_timestamp
select unix_timestamp();
--日期转UNIX时间戳函数: unix_timestamp
select unix_timestamp("2011-12-07 13:01:03");
--指定格式日期转UNIX时间戳函数: unix_timestamp
select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss');
--UNIX时间戳转日期函数: from_unixtime
select from_unixtime(1618238391);
select from_unixtime(0, 'yyyy-MM-dd HH:mm:ss');
--日期比较函数: datediff 日期格式要求'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'
select datediff('2012-12-08','2012-05-09');
--日期增加函数: date_add
select date_add('2012-02-28',10);
--日期减少函数: date_sub
select date_sub('2012-01-1',10);

--取整函数: round 返回double类型的整数值部分 （遵循四舍五入）
select round(3.1415926);
--指定精度取整函数: round(double a, int d) 返回指定精度d的double类型
select round(3.1415926,4);
--取随机数函数: rand 每次执行都不一样 返回一个0到1范围内的随机数
select rand();
--指定种子取随机数函数: rand(int seed) 得到一个稳定的随机数序列
select rand(3)

--使用之前课程创建好的student表数据
select * from student limit 3;
--if条件判断: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
select if(1=2,100,200);
select if(sex ='男','M','W') from student limit 3;
--空值转换函数: nvl(T value, T default_value)
select nvl("allen","itcast");
select nvl(null,"itcast");
--条件转换函数: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end;
select case sex when '男' then 'male' else 'female' end from student limit 3;