Hive学习笔记

1 数据仓库基础与Apache Hive入门

（1）数据仓库

①数据仓库概念：数据仓库,是一个用于存储、分析、报告的数据系统。数据仓库的目的是构建面向分析的集成化数据环境，为企业提供决策支持。数据仓库本身并不“生产”任何数据，其数据来源于不同外部系统；同时数据仓库自身也不需要“消费”任何的数据

②OLAP和OLTP
OLTP：操作型处理，主要目标是做数据处理，它是针对具体业务在数据库联机的日常操作，通常对少数记录进行查询、修改。用户较为关心操作的响应时间、数据的安全性、完整性和并发支持的用户数等问题。传统的关系型数据库系统作为数据管理的主要手段，主要用于操作型处理
OLAP:分析型处理，叫联机分析处理OLAP（On-Line Analytical Processing），主要目标是做数据分析。一般针对某些主题的历史数据进行复杂的多维分析，支持管理决策。数据仓库是OLAP系统的一个典型示例，主要用于数据分析，二者对比如下：

③数据集市：数据仓库是面向整个集团组织的数据，数据集市是面向单个部门使用的。可以认为数据集市是数据仓库的子集，也有人把数据集市叫做小型数据仓库。数据集市通常只涉及一个主题领域，例如市场营销或销售。因为它们较小且更具体，所以它们通常更易于管理和维护，并具有更灵活的结构

⑤数据仓库分层架构：数据分层每个企业根据自己的业务需求可以分成不同的层次**，但是最基础的分层思想，理论上数据分为三个层，操作型数据层（ODS）、数据仓库层(DW)和数据应用层(DA)。**

以下是阿里巴巴数据仓库的分层结构：

（2）Hive入门

①什么是Hive

Apache Hive是一款建立在Hadoop之上的开源数据仓库系统，可以将存储在Hadoop文件中的结构化、半结构化数据文件映射为一张数据库表，基于表提供了一种类似SQL的查询模型，称为Hive查询语言（HQL），用于访问和分析存储在Hadoop文件中的大型数据集。Hive核心是将HQL转换为MapReduce程序，然后将程序提交到Hadoop群集执行，这样就避免了直接写MapReduce程序，以下是Hive的架构图：



用户写完sql之后，hive需要针对sql进行语法校验，并且根据记录的元数据信息解读sql背后的含义，制定执行计划。并且把执行计划转换成MapReduce程序来执行，把执行的结果封装返回给用户。

②Hive数据模型：

（1）Table (表)：Table 是 Hive 中最基本的数据组织单元，类似于关系型数据库中的表。它由行和列组成，具有预定义的模式。存储在 HDFS 上的数据文件集合，支持结构化和半结构化数据，可以通过 SQL-like 查询语言(HiveQL)访问，分为内部表和外部表。具体如下图所示：

（2）Partition (分区)：分区是表的一种水平划分方式，根据分区列的值将表数据分割到不同的目录中。这样提高查询效率(分区裁剪)，每个分区对应HDFS上的一个子目录，常用按日期、地区等维度分区，分区列是虚拟列，不存储在数据文件中。具体图片如下所示：

（3）分桶（Bucket）：分桶是在数据加载时根据某列的哈希值进行数据划分，将数据均匀地分散到指定数量的桶中。分桶可以在表中创建固定数量的桶，数据会被分发到这些桶中。当查询时，Hive 可以通过桶的映射关系迅速定位到特定的桶，从而提高数据查询的效率。如果一个表按照用户ID进行了100个分桶，当你执行查询时，Hive会根据用户ID的哈希值定位到具体的桶，只需在少量桶中查找数据，而不是整个表。

③Hive 安装部署

（1）元数据相关信息：
Metadata：Metadata即元数据。元数据包含用Hive创建的database、table、表的位置、类型、属性，字段顺序类型等元信息。元数据存储在关系型数据库中。如hive内置的Derby、或者第三方如MySQL等。
Metastore：Metastore即元数据服务。Metastore服务的作用是管理metadata元数据，对外暴露服务地址，让各种客户端通过连接metastore服务，由metastore再去连接MySQL数据库来存取元数据。有了metastore服务，就可以有多个客户端同时连接，而且这些客户端不需要知道MySQL数据库的用户名和密码，只需要连接metastore 服务即可。某种程度上也保证了hive元数据的安全。

（2）metastore三种配置方式

2 HiveSQL 数据定义语言

（1）HiveSQL建表基础

（2）Hive数据类型详解

Hive数据类型指的是表中列的字段类型；
整体分为两类：原生数据类型（primitive data type）和复杂数据类型（complex data type）。
原生数据类型包括：数值类型、时间日期类型、字符串类型、杂项数据类型；

PS：数据之间有隐式转换和显示转换例如：CAST（'100'as INT）会将100字符串转换为100整数值

复杂数据类型包括：array数组、map映射、struct结构、union联合体

（3）Hive读写文件机制

①序列化和反序列化

②Hive读写文件流程

PS：这里的读入和Hadoop是一样的

（4）Hive数据存储路径

可以通过location语法来更改数据在HDFS上的存储路径，使得建表加载数据更加灵活方便。
语法：LOCATION '<hdfs_location>'

（5）几个案例

--ddl create table
create table t_archer(
    id int comment "ID",
    name string comment "英雄名称",
    hp_max int comment "最大生命",
    mp_max int comment "最大法力",
    attack_max int comment "最高物攻",
    defense_max int comment "最大物防",
    attack_range string comment "攻击范围",
    role_main string comment "主要定位",
    role_assist string comment "次要定位"
) comment "王者荣耀射手信息"
row format delimited fields terminated by "\t";   //指定分隔符，建表成功之后，在Hive的默认存储路径下就生成了表对应的文件夹

hadoop fs -put archer.txt  /user/hive/warehouse/itheima.db/t_archer
//把archer.txt文件上传到对应的表文件夹下。

select * from t_archer;
//可见数据和HiveSQL的表之间映射已经成功，里面已经有语句了

复杂数据类型案例
create table t_hot_hero_skin_price(
    id int,
    name string,
    win_rate int,
    skin_price map<string,int>
)
row format delimited                   //文件里面一行就是一个row数据
fields terminated by ','               //一行数据中的属性用‘，’隔开
collection items terminated by '-'    //集合之间的元素用‘-’隔开
map keys terminated by ':' ;          //map kv分隔符 

hadoop fs -put hot_hero_skin_price.txt /user/hive/warehouse/itheima.db/t_hot_hero_skin_price
//把对应文件放入表的文件夹当中

默认分隔符案例：
create table t_team_ace_player(
    id int,
    team_name string,
    ace_player_name string
);
hadoop fs -put team_ace_player.txt /user/hive/warehouse/itheima.db/t_team_ace_player
select * from t_team_ace_player;

（6）Hive SQL DDL建表高阶语法

①Hive内、外部表
1 内部表：被Hive拥有和管理的托管表。默认情况下创建的表就是内部表，Hive拥有该表的结构和文件。换句话说，Hive完全管理表（元数据和数据）的生命周期，类似于RDBMS中的表。当您删除内部表时，它会删除数据以及表的元数据
2 外部表：外部表（External table）中的数据不是Hive拥有或管理的，只管理表元数据的生命周期。要创建一个外部表，需要使用EXTERNAL语法关键字。删除外部表只会删除元数据，而不会删除实际数据。在Hive外部仍然可以访问实际数据。

②Hive分区表：Hive里面一个文件夹就是一张表，当Hive表对应的数据量大、文件多时，为了避免查询时全表扫描数据，Hive支持根据用户指定的字段进行分区（例如指定“角色”中的“射手”），分区的字段可以是日期、地域、种类等具有标识意义的字段。

create table t_all_hero(
    id int,
    name string,
    hp_max int,
    mp_max int,
    attack_max int,
    defense_max int,
    attack_range string,
    role_main string,
    role_assist string
)
row format delimited
fields terminated by "\t";
//先建立一张统一的表

create table t_all_hero_part(
       id int,
       name string,
       hp_max int,
       mp_max int,
       attack_max int,
       defense_max int,
       attack_range string,
       role_main string,
       role_assist string
) partitioned by (role string)
row format delimited
fields terminated by "\t";
//针对《王者荣耀》英雄数据，重新创建一张分区表t_all_hero_part，以role角色作为分区字段。

load data inpath '/user/hive/warehouse/itheima.db/t_all_hero/archer.txt' into table t_all_hero_part partition(role='sheshou');
load data inpath '/user/hive/warehouse/itheima.db/t_all_hero/assassin.txt' into table t_all_hero_part partition(role='cike');
load data inpath '/user/hive/warehouse/itheima.db/t_all_hero/mage.txt' into table t_all_hero_part partition(role='fashi');
load data inpath '/user/hive/warehouse/itheima.db/t_all_hero/support.txt' into table t_all_hero_part partition(role='fuzhu');
load data inpath '/user/hive/warehouse/itheima.db/t_all_hero/tank.txt' into table t_all_hero_part partition(role='tanke');
load data inpath '/user/hive/warehouse/itheima.db/t_all_hero/warrior.txt' into table t_all_hero_part partition(role='zhanshi');
使用Load语句加载文件进去对应的表，Local参数用于指定待加载的数据是位于本地文件系统还是HDFS文件系统，
有Local的话就是在本地系统，没有的话就是在HDFS系统.这是静态加载。


insert into table t_all_hero_part partition(role) 
select *,"fashi" 
from t_all_hero 
where role_main="fashi";
使用insert+select语句执行动态分区插入

非分区表 全表扫描过滤查询
select count(*) from t_all_hero where role_main="archer" and hp_max >6000;
结果：Time taken: 39.519 seconds, Fetched: 1 row(s)

分区表 先基于分区过滤 再查询
select count(*) from t_all_hero_part where role="sheshou" and hp_max >6000;
结果：Time taken: 37.294 seconds, Fetched: 1 row(s)
可见后者更快，而且越大的表越明显

HiveSQL支持多重分区（分区当中还有分区）
create table t_all_hero_part(
       id int,
       name string,
       hp_max int,
       mp_max int,
       attack_max int,
       defense_max int,
       attack_range string,
       role_main string,
       role_assist string
) partitioned by (role_main string,name string)
单分区表，按省份分区
create table t_user_province (id int, name string,age int) partitioned by (province string);
双分区表，按省份和市分区
create table t_user_province_city (id int, name string,age int) partitioned by (province string, city string);
三分区表，按省份、市、县分区
create table t_user_province_city_county (id int, name string,age int) partitioned by (province string, city string,county string);
--多分区表的数据插入和查询使用
load data local inpath 'data1.txt' into table t_user_province partition(province='shanghai');
load data local inpath 'data2.txt' into table t_user_province_city_county partition(province='zhejiang',city='hangzhou',county='xiaoshan');

分区以后的区别如下：只不过多了一个分区字段。实际上分区表在底层管理数据的方式发生了改变。这里直接去HDFS查看区别。不同分区对应着不同的文件夹，同一分区的数据存储在同一个文件夹下。查询过滤的时候只需要根据分区值找到对应的文件夹，扫描本文件夹下本分区下的文件即可，避免全表数据扫描。

③Hive Bucketed Tables 分桶表：分桶表也叫做桶表，叫法源自建表语法中bucket单词，是一种用于优化查询而设计的表类型。**分桶表对应的数据文件在底层会被分解为若干个部分，通俗来说就是被拆分成若干个独立的小文件。**在分桶时，要指定根据哪个字段将数据分为几桶（几个部分）。


句子如下：

--根据state州分为5桶 每个桶内根据cases确诊病例数倒序排序
CREATE TABLE itheima.t_usa_covid19_bucket_sort(
     count_date string,
     county string,
     state string,
     fips int,
     cases int,
     deaths int
)
CLUSTERED BY(state)
sorted by (cases desc) INTO 5 BUCKETS;

-step2:把源数据加载到普通hive表中
CREATE TABLE itheima.t_usa_covid19(
	count_date string,
	county string,
	state string,
	fips int,
	cases int,
	deaths int
)
	row format delimited 
	filed terminated by ",";

动态插入数据：insert into t_usa_covid19_bucket_sort select * from t_usa_covid19;

--基于分桶字段state查询来自于New York州的数据
--不再需要进行全表扫描过滤
--根据分桶的规则hash_function(New York) mod 5计算出分桶编号
--查询指定分桶里面的数据 就可以找出结果  此时是分桶扫描而不是全表扫描
select * from t_usa_covid19_bucket where state="New York";

可见该表底层被分成了5份数据且使用好处如下：




④Hive Transactional Tables事务表：在Hive中创建一张具备事务功能的表，并尝试进行增删改操作，以前只有查询的作用，以下是局限性：
如果不做任何配置修改，直接针对Hive中已有的表进行Update、Delete、Insert操作，可以发现，只有insert语句可以执行，Update和Delete操作会报错。Insert插入操作能够成功的原因在于，底层是直接把数据写在一个新的文件中的。Hive的文件是存储在HDFS上的，而HDFS上又不支持对文件的任意修改。用HDFS文件作为原始数据（基础数据），用delta保存事务操作的记录增量数据；当访问Hive数据时，根据HDFS原始文件和delta增量文件做合并，查询最新的数据

--1、开启事务配置（可以使用set设置当前session生效 也可以配置在hive-site.xml中）
set hive.support.concurrency = true; --Hive是否支持并发
set hive.enforce.bucketing = true; --从Hive2.0开始不再需要  是否开启分桶功能
set hive.exec.dynamic.partition.mode = nonstrict; --动态分区模式  非严格
set hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; --
set hive.compactor.initiator.on = true; --是否在Metastore实例上运行启动线程和清理线程
set hive.compactor.worker.threads = 1; --在此metastore实例上运行多少个压缩程序工作线程。

2 创建一个可用于执行事务的表
create table trans_student(
	id int,
	name String,
	age int
)
clustered by (id) into 2 buckets 
stored as orc 
TBLPROPERTIES('transactional'='true');

3 此情况下insert update drop都可执行
insert into trans_student values(1,"allen",18);
update trans_student set name="Michael Jackson" where age=18;
delete from trans_student where id =1;

⑤HiveSQL的视图

--hive中有一张真实的基础表t_usa_covid19select *from itheima.t_usa_covid19;
--创建视图
create view v_usa_covid19 
as select count_date, county,state,deaths 
from t_usa_covid19 
limit 5;

create view v_usa_covid19_from_view 
as select * from v_usa_covid19 
limit 2;

--显示当前已有的视图
show tables;
show views;

--、查看视图定义
show create table v_usa_covid19;
--删除视图
drop view v_usa_covid19_from_view;
--更改视图属性
alter view v_usa_covid19 
set TBLPROPERTIES ('comment' = 'This is a view');
--更改视图定义
alter view v_usa_covid19 as  select county,deaths from t_usa_covid19 limit 2;

以下是物化视图的句子：

CREATE MATERIALIZED VIEW mv_sales_summary
AS 
SELECT region, product, sum(amount) as total_sales
FROM sales
GROUP BY region, product;

⑥其他的HiveSQL句子

-- =============================================
-- 一、Database/Schema (数据库) DDL 操作
-- =============================================

-- 1. 创建数据库
CREATE DATABASE IF NOT EXISTS sales_db 
COMMENT 'Sales department database' 
LOCATION '/user/hive/warehouse/sales_db' 
WITH DBPROPERTIES ('creator'='John', 'date'='2023-01-01');
-- 参数说明:
--   IF NOT EXISTS: 避免重复创建报错
--   COMMENT: 数据库描述
--   LOCATION: 指定存储路径
--   DBPROPERTIES: 附加属性

-- 2. 查看数据库
SHOW DATABASES LIKE 'sales*';  -- 查看以'sales'开头的数据库
DESCRIBE DATABASE sales_db;    -- 查看数据库详细信息

-- 3. 修改数据库
ALTER DATABASE sales_db SET DBPROPERTIES ('edited-by'='Mary');  -- 修改属性

-- 4. 删除数据库
DROP DATABASE IF EXISTS sales_db CASCADE;  
-- CASCADE: 级联删除(删除数据库下所有表)


-- =============================================
-- 二、Table (表) DDL 操作
-- =============================================

-- 1. 创建表
CREATE TABLE IF NOT EXISTS employees (
    id INT COMMENT 'Employee ID',
    name STRING COMMENT 'Employee name',
    salary DECIMAL(10,2) COMMENT 'Monthly salary',
    hire_date DATE COMMENT 'Hire date'
)
COMMENT 'Employee information table'
PARTITIONED BY (department STRING, year INT)  -- 分区字段
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
STORED AS ORC
LOCATION '/user/hive/warehouse/sales_db/employees';
-- 参数说明:
--   STORED AS: 存储格式(ORC/Parquet/TextFile等)
--   ROW FORMAT: 定义行格式
--   PARTITIONED BY: 定义分区字段

-- 2. 查看表结构
DESCRIBE FORMATTED employees;  -- 查看详细表结构(包括存储信息)
SHOW CREATE TABLE employees;   -- 查看建表语句

-- 3. 修改表
ALTER TABLE employees RENAME TO staff;  -- 重命名表
ALTER TABLE employees ADD COLUMNS (age INT COMMENT 'Employee age');  -- 添加列
ALTER TABLE employees CHANGE COLUMN name full_name STRING;  -- 修改列名/类型

-- 4. 删除表
DROP TABLE IF EXISTS employees PURGE;  
-- PURGE: 直接删除(不进回收站)


-- =============================================
-- 三、Partition (分区) DDL 操作
-- =============================================

-- 1. 添加分区
ALTER TABLE employees ADD PARTITION (department='IT', year=2023) 
LOCATION '/user/hive/warehouse/sales_db/employees/dept=IT/year=2023';
-- 参数说明:
--   显式指定分区路径

-- 2. 动态分区插入(需要先设置参数)
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;

INSERT INTO TABLE employees PARTITION (department, year)
SELECT id, name, salary, hire_date, dept, yr 
FROM temp_employees;
-- 参数说明:
--   最后两列(dept, yr)会映射到分区字段(department, year)

-- 3. 查看分区
SHOW PARTITIONS employees;  -- 查看所有分区
SHOW PARTITIONS employees PARTITION(department='IT');  -- 查看特定分区

-- 4. 删除分区
ALTER TABLE employees DROP PARTITION (department='HR', year=2022);  -- 删除特定分区

-- 5. 修复分区(元数据与文件系统同步)
MSCK REPAIR TABLE employees;  -- 同步新增的分区
-- 或使用:
ALTER TABLE employees RECOVER PARTITIONS;


-- =============================================
-- 四、其他实用操作
-- =============================================

-- 1. 清空表数据(保留表结构)
TRUNCATE TABLE employees;

-- 2. 复制表结构
CREATE TABLE employees_copy LIKE employees;

-- 3. 导出表数据
EXPORT TABLE employees TO '/user/hive/export/employees_data';

-- 4. 导入表数据
IMPORT TABLE employees FROM '/user/hive/export/employees_data';

3 HiveSQL 数据操控、查询语言（DML、DQL）

(1)Load 加载数据指令:将数据文件移动到与Hive表对应的位置（和hadoop fs -put一样）
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] 
INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)];
1 filepath:待移动数据的路径。可以指向文件（在这种情况下，Hive将文件移动到表中），
  也可以指向目录（在这种情况下，Hive将把该目录中的所有文件移动到表中）。
2 LOCAL关键字：将在本地文件系统中查找文件路径。若指定相对路径，将相对于用户的当前工作目录进行解释
3 OVERWRITE关键字:则目标表（或者分区）中的已经存在的数据会被删除，然后再添加filepath指向的文件

(2)insert插入数据：
  最常见的和SQL一样：insert into trans_student (id, name, age)
                   values (1,"allen",18)
 1 insert+select插入数据：
 insert into table student_from_insert 
 select num,name from student;
 
 2 Multiple Inserts多重插入:扫描一张student表但是执行多次插入:
   from student 
   insert into table student_insert1 select num
   insert into table student_insert2 select name;

(3)Dynamic Partition Inserts:动态分区插入。在load过程中，分区值是手动指定写死的，叫做静态分
区。要求创建一张分区表，根据最后一个字段（选修专业）进行分区，同一个专业的同学分到同一个分区中，
如何实现。此时要引入动态分区：分区的值是由后续的select查询语句的结果来动态确定的。根据查询结果
自动分区（分区表现在表上就是加列，二次分区就是加两列）
--1、首先设置动态分区模式为非严格模式 默认已经开启了动态分区功能
set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = nonstrict;
--2、当前库下已有一张表student
select * from student;
--3、创建分区表 以sdept作为分区字段
create table student_partition(Sno int,Sname string,Sex string,Sage int)
partitioned by(Sdept string);
--4.动态插入分区
insert into table student_partition partition(Sdept)
select num,name,sex,age,dept from student;
其中，num,name,sex,age作为表的字段内容插入表中，dept作为分区字段值

(4)insert Directory导出数据
	INSERT OVERWRITE [LOCAL] DIRECTORY directory1
    [ROW FORMAT row_format] [STORED AS file_format] 
	SELECT ... FROM ...
    例句如下：
	insert overwrite directory "/hive_export"
    select * from student;

(5)查询语句
SELECT [ALL | DISTINCT] select_expr, select_expr, ...  -- 选择查询的列，ALL（默认）返回所有行，DISTINCT 去重  
FROM table_reference  -- 指定查询的数据来源（表或视图）  
[WHERE where_condition]  -- 可选，筛选满足条件的行  
[GROUP BY col_list]  -- 可选，按指定列分组，通常配合聚合函数使用  
[ORDER BY col_list]  -- 可选，按指定列排序（默认升序 ASC，可指定降序 DESC）  
[CLUSTER BY col_list | [DISTRIBUTE BY col_list][SORT BY col_list]] 
--CLUSTER BY col_list可选，Hive特有语法，同时完成数据分布（DISTRIBUTE BY）和排序（SORT BY）  
--[DISTRIBUTE BY col_list]可选，控制数据在Reducer间的分布方式（类似MapReduce的分区）  
-- [SORT BY col_list]] 可选，在每个Reducer内部对数据进行排序（非全局排序）  
[LIMIT [offset,] rows];  -- 可选，限制返回的行数（offset 跳过前N行，rows 返回的行数）
PS:

SELECT  employee_id,employee_name, department, salary
FROM employee_table  
WHERE department = 'Sales'  -- 筛选销售部门的员工
ORDER BY  salary DESC  -- 按工资降序排列
LIMIT 10;       -- 只返回前10条记录

SELECT employee_id,department,salary
FROM  employee_table
CLUSTER BY department;  -- 等同于DISTRIBUTE BY department SORT BY department

SELECT num,name FROM (select num,name from student_local LIMIT 2) subq1
UNION
SELECT num,name FROM (select num,name from student_hdfs LIMIT 3) subq2
--Union用于合并结果集

SELECT * FROM student_hdfs
WHERE student_hdfs.num IN (select num from student_local limit 2);
--子查询

--select语句中的CTE
with q1 as (select num,name,age from student where num = 95002)
select * from q1;
公用表表达式（CTE）是一个临时结果集：该结果集是从WITH子句中指定的简单查询派生而来的，
紧接在SELECT或INSERT关键字之前。CTE仅在单个语句的执行范围内定义

(6)Hive SQL Join连接操作（注意内连接(交集)和全外连接(并集)的区别）
-- 1. 内连接
SELECT e.emp_id, e.emp_name, d.dept_name 
FROM employees e JOIN departments d 
ON e.dept_id = d.dept_id;

-- 2. 左外连接
SELECT e.emp_id, e.emp_name, d.dept_name 
FROM employees e LEFT JOIN departments d 
ON e.dept_id = d.dept_id;

-- 3. 右外连接
SELECT e.emp_name, d.* 
FROM employees e RIGHT JOIN departments d 
ON e.dept_id = d.dept_id;

-- 4. 全外连接
SELECT e.emp_name, d.dept_name
FROM employees e FULL JOIN departments d 
ON e.dept_id = d.dept_id;

-- 5. 交叉连接（直接返回笛卡尔积）
SELECT e.emp_name, p.project_name 
FROM employees e CROSS JOIN projects p;

-- 6. 半连接
SELECT e.* FROM employees e
 LEFT SEMI JOIN departments d 
 ON e.dept_id = d.dept_id 
 WHERE d.budget > 100000;

Group By和Clustered By的区别

4 Hive内置运算符和Hive函数

（1）Hive内置运算符

1 关系运算符
 等值比较: = 、==        不等值比较: <> 、!=         小于比较: <
 小于等于比较: <=        大于比较: >                大于等于比较: >=
 空值判断: IS NULL      非空判断: IS NOT NULL       LIKE比较: LIKE
 JAVA的LIKE操作: RLIKE  REGEXP操作: REGEXP
 
2 算术运算符
加法操作: +               减法操作: -            乘法操作: *
除法操作: /               取整操作: div          取余操作: %
位与操作: &               位或操作: |            位异或操作: ^
位取反操作: ~

3 逻辑运算符
与操作: A AND B                 或操作: A OR B         非操作: NOT A 、!A
在:A IN (val1, val2, ...)      
不在:A NOT IN (val1, val2, ...)
逻辑是否存在: [NOT] EXISTS (subquery)

（2）Hive 内置函数（Built-in Functions）

1 字符串函数
SELECT length('Hello World') AS length_example;  -- 返回字符串的长度，结果为11
SELECT reverse('Hive') AS reverse_example;  -- 返回字符串的反转结果，结果为'eviH'
SELECT concat('Hello', ' ', 'World') AS concat_example;  -- 连接多个字符串，结果为'Hello World'
SELECT concat_ws('-', '2023', '08', '01') AS concat_ws_example;  -- 用分隔符连接字符串，结果为'2023-08-01'
SELECT substr('Hello World', 7, 5) AS substr_example;  -- 从第7个字符开始截取5个字符，结果为'World'
SELECT substring('Hello World', 7) AS substring_example;  -- 从第7个字符开始截取到末尾，结果为'World'
SELECT upper('hive') AS upper_example;  -- 转换为大写，结果为'HIVE'
SELECT ucase('sql') AS ucase_example;  -- 转换为大写，结果为'SQL'
SELECT lower('HIVE') AS lower_example;  -- 转换为小写，结果为'hive'
SELECT lcase('SQL') AS lcase_example;  -- 转换为小写，结果为'sql'
SELECT trim('  hello  ') AS trim_example;  -- 去除字符串两端的空格，结果为'hello'
SELECT ltrim('  hello') AS ltrim_example;  -- 去除字符串左边的空格，结果为'hello'
SELECT rtrim('hello  ') AS rtrim_example;  -- 去除字符串右边的空格，结果为'hello'
SELECT regexp_replace('100-200', '(\\d+)', 'num') AS regexp_replace_example;  -- 用正则替换字符串，结果为'num-num'
SELECT regexp_extract('2023-08-01', '^(\\d{4})', 1) AS regexp_extract_example;  -- 提取匹配正则的部分，结果为'2023'
SELECT parse_url('http://example.com/path?query=1', 'HOST') AS parse_url_example;  -- 解析URL的指定部分，结果为'example.com'
SELECT get_json_object('{"name":"John", "age":30}', '$.name') AS get_json_object_example;  -- 从JSON字符串中提取指定字段，结果为'John'
SELECT concat('Hello', space(2), 'World') AS space_example;  -- 生成指定数量的空格字符串，结果为'Hello  World'
SELECT repeat('ha', 3) AS repeat_example;  -- 重复字符串指定次数，结果为'hahaha'
SELECT ascii('A') AS ascii_example;  -- 返回字符串第一个字符的ASCII码，结果为65
SELECT lpad('5', 3, '0') AS lpad_example;  -- 用指定字符左补足到指定长度，结果为'005'
SELECT rpad('5', 3, '0') AS rpad_example;  -- 用指定字符右补足到指定长度，结果为'500'
SELECT split('a,b,c,d', ',') AS split_example;  -- 按分隔符分割字符串，返回数组，结果为["a","b","c","d"]
SELECT find_in_set('b', 'a,b,c,d') AS find_in_set_example;  -- 在逗号分隔的字符串中查找元素位置，结果为2

2 日期函数
SELECT current_date() AS current_date_example;  -- 返回当前日期，格式为'YYYY-MM-DD'
SELECT current_timestamp() AS current_timestamp_example;  -- 返回当前时间戳，包含日期和时间
SELECT from_unixtime(1690848000) AS from_unixtime_example1;  -- 将UNIX时间戳转换为日期时
SELECT from_unixtime(1690848000, 'yyyy/MM/dd') AS from_unixtime_example2;  -- 按指定格式转换
SELECT unix_timestamp() AS unix_timestamp_example1;  -- 获取当前时间的UNIX时间戳(秒级)
SELECT unix_timestamp('2023-08-01 00:00:00') AS unix_timestamp_example2;  -- 将日期字符串转为UNIX时间戳
SELECT unix_timestamp('01/08/2023', 'dd/MM/yyyy') AS unix_timestamp_example3;  -- 按指定格式解析日期并转为时间戳
SELECT to_date('2023-08-01 12:34:56') AS to_date_example;  -- 从时间戳中提取日期部分，结果为'2023-08-01'
SELECT year('2023-08-01') AS year_example;  -- 提取年份，结果为2023
SELECT month('2023-08-01') AS month_example;  -- 提取月份，结果为8
SELECT day('2023-08-01') AS day_example;  -- 提取日，结果为1
SELECT hour('2023-08-01 12:34:56') AS hour_example;  -- 提取小时，结果为12
SELECT minute('2023-08-01 12:34:56') AS minute_example;  -- 提取分钟，结果为34
SELECT second('2023-08-01 12:34:56') AS second_example;  -- 提取秒，结果为56
SELECT weekofyear('2023-08-01') AS weekofyear_example;  -- 返回日期在一年中的周数，结果为31
SELECT datediff('2023-08-10', '2023-08-01') AS datediff_example;  -- 计算两个日期相差的天数，结果为9
SELECT date_add('2023-08-01', 7) AS date_add_example;  -- 日期增加指定天数，结果为'2023-08-08'
SELECT date_sub('2023-08-01', 7) AS date_sub_example;  -- 日期减少指定天数，结果为'2023-07-25'

3 HiveSQL 常用数学函数示例集
SELECT round(3.1415926) AS round_example1;  -- 四舍五入取整，结果为3
SELECT round(3.1415926, 3) AS round_example2;  -- 四舍五入保留3位小数，结果为3.142
SELECT floor(3.7) AS floor_example;  -- 向下取整，结果为3
SELECT ceil(3.2) AS ceil_example;  -- 向上取整，结果为4
SELECT ceiling(3.2) AS ceiling_example;  -- ceil的别名，功能相同
SELECT rand() AS rand_example1;  -- 返回0到1之间的随机数
SELECT rand(100) AS rand_example2;  -- 使用种子值100生成随机数
SELECT bin(10) AS bin_example;  -- 将十进制数转为二进制字符串，结果为'1010'
SELECT conv('1010', 2, 10) AS conv_example1;  -- 将二进制'1010'转为十进制，结果为'10'
SELECT conv('A', 16, 2) AS conv_example2;  -- 将十六进制'A'转为二进制，结果为'1010'
SELECT abs(-10.5) AS abs_example;  -- 返回绝对值，结果为10.5

4 HiveSQL 集合函数
-- 1. 集合元素size函数: size(Map<K.V>) size(Array<T>)
SELECT size(array(1, 2, 3)) AS array_size_example;  -- 返回数组元素个数，结果为3
SELECT size(map('a', 1, 'b', 2)) AS map_size_example;  -- 返回map键值对个数，结果为2

-- 2. 取map集合keys函数: map_keys(Map<K.V>)
SELECT map_keys(map('name', 'John', 'age', 30)) AS map_keys_example;  -- 返回map的所有key组成的数组，结果为["name","age"]

-- 3. 取map集合values函数: map_values(Map<K.V>)
SELECT map_values(map('name', 'John', 'age', 30)) AS map_values_example;  -- 返回map的所有value组成的数组，结果为["John",30]

-- 4. 判断数组是否包含指定元素: array_contains(Array<T>, value)
SELECT array_contains(array(1, 2, 3), 2) AS array_contains_example1;  -- 判断数组是否包含元素2，结果为true
SELECT array_contains(array('a', 'b', 'c'), 'd') AS array_contains_example2;  -- 判断数组是否包含元素d，结果为false

-- 5. 数组排序函数: sort_array(Array<T>)
SELECT sort_array(array(3, 1, 2)) AS sort_array_example1;  -- 对数组进行升序排序，结果为[1,2,3]
SELECT sort_array(array('c', 'a', 'b')) AS sort_array_example2;  -- 对字符串数组排序，结果为["a","b","c"]
SELECT sort_array(array(3, 1, 2), false) AS sort_array_example3;  -- 对数组进行降序排序，结果为[3,2,1]

5 HiveSQL 条件判断与空值处理函数
-- 1. if条件判断函数: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
SELECT if(1=1, 'true', 'false') AS if_example1;  -- 条件为真返回第一个值，结果为'true'
SELECT if(1=2, 'true', 'false') AS if_example2;  -- 条件为假返回第二个值，结果为'false'

-- 2. 空判断函数: isnull(a)
SELECT isnull(NULL) AS isnull_example1;  -- 判断是否为NULL，结果为true
SELECT isnull('value') AS isnull_example2;  -- 判断是否为NULL，结果为false

-- 3. 非空判断函数: isnotnull(a)
SELECT isnotnull(NULL) AS isnotnull_example1;  -- 判断是否不为NULL，结果为false
SELECT isnotnull('value') AS isnotnull_example2;  -- 判断是否不为NULL，结果为true

-- 4. 空值转换函数: nvl(T value, T default_value)
SELECT nvl(NULL, 'default') AS nvl_example1;  -- 值为NULL时返回默认值，结果为'default'
SELECT nvl('not null', 'default') AS nvl_example2;  -- 值不为NULL时返回原值，结果为'not null'

-- 5. 非空查找函数: COALESCE(T v1, T v2, ...)
SELECT coalesce(NULL, NULL, 'first non-null', 'last') AS coalesce_example1;  -- 返回第一个非NULL值，结果为'first non-null'
SELECT coalesce('first', 'second') AS coalesce_example2;  -- 返回第一个非NULL值，结果为'first'

-- 6. 条件转换函数: CASE WHEN THEN ELSE END
SELECT 
  CASE 
    WHEN 1=1 THEN 'case1'
    WHEN 2=2 THEN 'case2'
    ELSE 'else'
  END AS case_example1;  -- 返回第一个满足条件的值，结果为'case1'

SELECT 
  CASE 2
    WHEN 1 THEN 'one'
    WHEN 2 THEN 'two'
    ELSE 'other'
  END AS case_example2;  -- 匹配值返回对应结果，结果为'two'

-- 7. nullif(a, b): 如果a = b则返回NULL，否则返回a
SELECT nullif(1, 1) AS nullif_example1;  -- 两值相等返回NULL，结果为NULL
SELECT nullif(1, 2) AS nullif_example2;  -- 两值不等返回第一个值，结果为1

-- 8. assert_true: 如果条件不为真则引发异常，否则返回NULL
SELECT assert_true(1=1) AS assert_true_example1;  -- 条件为真返回NULL
SELECT assert_true(1=2) AS assert_true_example2;  -- 条件为假会抛出异常

6 显式转换函数：cast(12.14 as string)

（3）Hive 用户自定义函数

接下来我自己实现一个简单的数字加密函数UDF
（1）写一个Java类

package wuyinan;

import org.apache.hadoop.hive.ql.exec.UDF;

public class Encode extends UDF {
    public String evaluate(String number){
        String result="";
        for (int i=0;i<number.length();i++){
            char c=number.charAt(i);
            c=(char)(c-'0'+'a');
            result=result+c;
        }
        return result;
    }
}

（2）将其打包成Jar包以后上传的Linux服务器然后加载到Hive的classpath

（3）在Hive里面注册成函数：create temporary function 函数名 as 'UDF类在jar包的完整路径'
例子如下：create temporary function Encode as wuyinan.Encode;

PS：Jar包里面的那个具体的类才是函数，而且因为可以重载多个evaluate方法所以函数的参数可以不一样

（4）直接使用函数

（4）Hive 函数高阶

- 1. explode函数（将数组或Map展开为多行）
SELECT explode(array('A','B','C')) AS letters; 
/* 输出：
letters
-------
A
B
C
*/

SELECT explode(map('k1','v1','k2','v2')) AS (key,val);
/* 输出：
key    val
----   ----
k1     v1
k2     v2
*/

-- 2. Lateral View（配合explode实现列转行）
WITH t AS (SELECT 'id1' AS id, array(10,20,30) AS arr)
SELECT id,num FROM t LATERAL VIEW explode(arr) v AS num;
/* 输出：
id    num
----  ----
id1    10
id1    20
id1    30
*/

-- 3. 聚合函数（分组计算）
SELECT department, 
       count(*) AS cnt,
       collect_list(name) AS names, 
       percentile(salary,0.5) AS median_sal 
FROM emp GROUP BY department;
/* 输出示例：
department    cnt    names            median_sal
----------    ---    --------------   ---------
Sales         5      [John,Mike,...]   5500
HR            3      [Amy,Lisa,...]    4800
*/

-- 4. 窗口函数（基于分区的计算）
select id,name,deg,salary,dept,
sum(salary) over(partition by dept) as total 
from employee


-- 5. 抽样函数（数据采样）
SELECT * FROM logs TABLESAMPLE(10 PERCENT); 
/* 输出：原表10%的随机样本数据 */

SELECT * FROM logs TABLESAMPLE(100 ROWS);
/* 输出：最多100行的样本数据 */

-- 6. 复杂案例：Lateral View + 聚合
WITH sales AS (
  SELECT '2023-01' AS month, 
         map('productA',100,'productB',200) AS sales_data
)
SELECT 
  month,
  product,
  amount,
  sum(amount) OVER (PARTITION BY month) AS total
FROM sales
LATERAL VIEW explode(sales_data) s AS product,amount;
/* 输出：
month     product    amount    total
-------   --------   ------    -----
2023-01   productA    100       300
2023-01   productB    200       300
*/

PS：以下是窗口函数的输出结果