首先需要了解的是MapReduce的编写与操作需要操作人员具有JAVA的编程能力，而Hive存在的目的是用类SQL的语句，提供MapReduce操作。相较于直接编写MapReduce更加方面高效。

SQL中DDL语法的作用

数据定义语言（Data Definition Language, DDL），是SQL语言中对数据库内部的对象结构进行创建，删除，修改等的操作语言，这些数据库对象包括databases、table等。
DDL核心语法由CREATE、ALTER与DROP三个所组成。DDL并不涉及表内部数据的操作。
Hive SQL（HQL）与标准SQL的语法大同小异，基本相通；
基于Hive的设计、使用特点，HQL中create语法（尤其create table）将是学习掌握Hive DDL语法的重中之重。见表是否成功直接影响数据文件是否成功映射成功，进而影响后续是否可以基于SQL分析数据。通俗来说，没有表，表没有数据，Hive就没东西可分析。

数据库database

在Hive中，默认的数据库叫做default，存储数据位置位于HDFS的/user/hive/warehouse下
用户自己创建的数据库存储位置是/user/hive/warehouse/database_name.db下

create database用于创建新的数据库

COMMENT：数据库的注释说明语句

LOCATION：指定数据库在HDFS存储位置，默认/user/hive/warehouse/dbname.db

WITH DBPROPERTIES：用于指定一些数据库的属性配置

use database

选择特定的数据库

切换当前绘画使用数据库进行操作

drop database

删除数据库

默认行为是RESTRICT，这意味着尽在数据库为空才删除它。

要删除带有表的数据库（不为空的数据库），我们可以使用CASCADE。

表Table

一个数据库通常包含一个或多个表。每个表由一个名字表示（例如“客户”或者“订单”）。
表包含带有数据库的记录（行）。

建表语法树（基础）

create table [db_name].table_name (col_name data_type [COMMENT col_comment],...) [COMMENT table_comment] [ROW FORMAT DELIMITED...];

注意事项

蓝色字体是建表语法的关键字，用于指定某些功能。	CREATE TABLE [IF NOT EXISTS] [db_name.]table_name
[ ]中括号的语法表示可选。	(col_name data_type [COMMENT col_comment], ... )
建表语句中的语法顺序要和语法树中顺序保持一致。	[COMMENT table_comment]
最低限度必须包括的语法为：	[ROW FORMAT DELIMITED …];

数据类型

Hive数据类型指的是表中列的字段类型；
整体分为两类：原生数据类型（primitive data type）和复杂数据类型（complex data type）。
最常用的数据类型是字符串string和数字类型int。

分隔符指定语法

ROW FORMAT DELIMITED语法用于指定字段之间等相关的分隔符，这样Hive才能正确的读取分析数据。
或者说只有分隔符指定正确，解析数据成功，我们才能在表中看到数据。
LazySimpleSerDe是Hive默认的，包含4中子语法，分别用于指定字段之间、集合元素之间、map映射kv之间、换行的分割符号。
在建表的时候可以根据数据的特点灵活使用

Hive默认分隔符

Hive建表时如果没有row format语法指定分隔符，则采用默认分隔符；
默认的分隔符是'\001'，是一种特殊的字符，使用的是ASCII编码的值。

DML语句与函数使用

Load语法功能

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

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename;

最终目的都是将数据上传至对应的hdfs目录下，所以实现方法并不拘泥于load，直接使用hadoop fs -put，或者从web页面都可以实现

语法规则之filepath

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

相对路径，例如：project/data1
绝对路径，例如：/user/hive/project/data1
具有schema的完整URI，例如：hdfs://namenode:9000/user/hive/project/data1

语法规则之LOCAL

指定local，将在本地文件系统中查找文件路径。

若指定相对路径，将相对于用户的目前工作目录进行解释；

用户也可以为本地文件指定完整的URI-例如：file://user/hive/project/data1。

没有指定LOCAL关系字。

如果filepath指向的是一个完整的URI，则会直接使用这个URI；

如果没有指定schema，Hive会使用在hadoop配置文件中阐述fs.default.name指定的（正常情况都是HDFS）。

LOCAL本地是哪里？

如果对HiveServer2服务运行此命令

本地文件系统指的是Hiveserver2服务所在的服务器本地Linux系统，而不是Hive客户端所在的文件系统。

Insert语法功能

Hive官方推荐加载数据的方式：

清洗数据成为结构化文件，再使用Load语法加载数据到表中。这样的效率更高。

也可以使用insert语法把数据插入到指定的表中，最常见的配合是把查询返回的结果插入到另一张表中。

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;

Hive SQL DCL语法（查询语句）

select语法树

从哪里查询取决于FROM关键字后面的table_reference，这是我们写查询语句时首先要确定的
表名和列名部分大小写

案例：美国Covid-19新冠数据之select查询

准备一下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表示检索查询返回的列，必须至少有一个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;

特殊条件（空值判断、between、in）

聚合操作

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

常见的聚合操作函数

AVG(colmu)	返回某列的平均值
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语法，则表中的所有行数据当成一组。

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接受一个或两个数字阐述，这两个参数都必须是飞赴整数常量。
第一个参数指定要返回的第一行的偏移量（从Hive2.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)

结合下面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;

Hive SQL Join关联查询

背景

根据数据库的三范式设计要求和日常工作习惯来说，我们通常不会设计一张达标把所有类型的数据都放在一起，而是不同类型的数据设计不同的表存储。
比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其他信息（比如姓名、所属公司等）的字段。
在这种情况下，有时需要基于多张表查询才能得到最终的结果；
join语法的出现是用于根据两个或多个表中的列之间的关系，从这些表中共同组合查询数据。

Hive主要的Join方式

Hive join语法规则

在Hive中，使用最多，最重要的两种join分别是：
inner join（内连接）、left join （左连接）
table_reference：是join查询中使用的表名。
table_factor：与table_referene相同，是联接查询中使用的表名。
join_condition：join查询关联的条件，如果在两个以上的表上需要连接，则使用AND关联字。

join查询数据环境准备

为了更好的练习、学习掌握Hive中的join语法，下面我们去创建3张表并且加载数据到表中。

表1：employee员工表；

表2：employee_address员工住址信息表；

表3：employee_connection员工联系方式表；

--table1: 员工表
CREATE TABLE employee(
  id int,
  name string,
  deg string,
  salary int,
  dept string
) row format delimited
fields terminated by ',';

--table2:员工家庭住址信息表
CREATE TABLE employee_address (
  id int,
  hno string,
  street string,
  city string
) row format delimited
fields terminated by ',';

--table3:员工联系方式信息表
CREATE TABLE employee_connection (
  id int,
  phno string,
  email string
) row format delimited
fields terminated by ',';

--加载数据到表中
load data local inpath '/root/hivedata/employee.txt' into table employee;
load data local inpath '/root/hivedata/employee_address.txt' into table employee_address;
load data local inpath '/root/hivedata/employee_connection.txt' into table employee_connection;

inner join 内连接

内连接是最常见的一种连接，它也成为普通连接，其中inner可以省略：inner join == join;
只有进行连接的两个表都存在与连接条件匹配的数据才会被留下来。

--1、inner join
select e.id,e.name,e_a.city,e_a.street
from employee e inner join employee_address e_a
on e.id =e_a.id;
--等价于 inner join=join
select e.id,e.name,e_a.city,e_a.street
from employee e join employee_address e_a
on e.id =e_a.id;
--等价于 隐式连接表示法
select e.id,e.name,e_a.city,e_a.street
from employee e , employee_address e_a
where e.id =e_a.id;

left join左连接

left join中文叫做左外连接（left Outer Join）或者左连接，其中outer可以省略，left outer join是早期的称呼。
left join的核心就在于left左。左值得是join关键字左边的表，简称左表。
通俗解释：join时以坐标的全部数据为准，右表与之关联；左表数据全部返回，右表关联上的显示返回，关联不上的显示null返回。

--2、left join
select e.id,e.name,e_conn.phno,e_conn.email
from employee e left join employee_connection e_conn
on e.id =e_conn.id;

--等价于 left outer join
select e.id,e.name,e_conn.phno,e_conn.email
from employee e left outer join employee_connection e_conn
on e.id =e_conn.id;

Hive常用函数入门

概述

Hive内建里不少函数，用于满足用户不同使用需求，提高SQL编写效率：

使用show functions查看当下可用的所有函数；
通过describe funciton extended funcname来查兰函数的使用方式

分类标准

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

用户定义函数UDF分类标准

根据函数输入输出的行数：

UDF（User-Defined-Function）普通函数，一进一出

UDAF（User-Defined Aggregation Function）聚合函数，多进一出

UDTF（User-Defined Table-Generating Function）表生成函数，一进多出

UDF分类标准扩大化

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

因为不管是什么类型的函数，一定满足于输入输出的要求，那么从输入几行和输出几行上来划分没有任何问题。

千万不要被UD（User-Defined）这两个字母所迷惑，造成事业的狭隘。

比如Hive官方文档中，针对聚合函数的标准就是内置的UDAF类型

概述

内置函数（build-in）指的是Hive开发实现好，直接可以使用的函数，也叫做内建函数。
官方文档地址：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
内置函数根据应用归类整体可以分为8大种类，我们将对其中重要的，使用频率高的函数使用进行详细讲解。8大重函数：日期函数、字符串函数、数字函数、数据脱敏函数、类型转换函数、集合函数、条件函数、其他杂项函数

String Functions字符串函数

字符串长度函数：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', ' ');

Data Functions日期函数

----------- Date Functions 日期函数 -----------------

--获取当前日期: 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);

Mathematical Functions 数学函数

----Mathematical Functions 数学函数-------------

--取整函数: 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);

Conditional Functions条件函数

主要用于条件判断、逻辑判断转换这样的场合

-----Conditional Functions 条件函数------------------

--使用之前课程创建好的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;

Share

Hive数仓基本操作