Hive笔记

发布于：2024-04-22 ⋅ 阅读:(319) ⋅ 点赞:(0)

HIVE介绍

Hive是什么

Hive 是建立在 Hadoop 上的数据仓库基础构架。它提供了一系列的工具，可以用来进行数据提取转化加载（ETL ），这是一种可以存储、查询和分析存储在 Hadoop 中的大规模数据的机制。Hive 定义了简单的类 SQL 查询语言，称为 HQL ，它允许熟悉 SQL 的用户查询数据。同时，这个语言也允许熟悉 MapReduce 开发者的开发自定义的 mapper 和 reducer 来处理内建的 mapper 和 reducer 无法完成的复杂的分析工作。


Hive和传统数据库比较

查询语言 HiveQL SQL
数据存储位置 HDFS Raw Device or 本地FS
数据格式 用户定义 系统决定
数据更新 不支持（1.x以后版本支持） 支持
索引 新版本有，但弱 有
执行 MapReduce Executor
执行延迟 高 低
可扩展性 高 低
数据规模 大 小

  1. 查询语言。类 SQL 的查询语言 HQL。熟悉 SQL 开发的开发者可以很方便的使用 Hive 进行开发。

  2. 数据存储位置。所有 Hive 的数据都是存储在 HDFS 中的。而数据库则可以将数据保存在块设备或者本地文件系统中。

  3. 数据格式。Hive 中没有定义专门的数据格式。而在数据库中，所有数据都会按照一定的组织存储，因此，数据库加载数据的过程会比较耗时。

  4. 数据更新。Hive 对数据的改写和添加比较弱化，0.14版本之后支持，需要启动配置项。而数据库中的数据通常是需要经常进行修改的。

  5. 索引。Hive 在加载数据的过程中不会对数据进行任何处理。因此访问延迟较高。数据库可以有很高的效率，较低的延迟。由于数据的访问延迟较高，决定了 Hive 不适合在线数据查询。

  6. 执行计算。Hive 中执行是通过 MapReduce 来实现的而数据库通常有自己的执行引擎。

  7. 数据规模。由于 Hive 建立在集群上并可以利用 MapReduce 进行并行计算，因此可以支持很大规模的数据；对应的，数据库可以支持的数据规模较小。

HIVE架构

image.png

1.元数据包括：表名、表所属的数据库（默认是default）、表的拥有者、列/分区字段、表的类型（是否是外部表）、表的数据所在目录等；默认存储在自带的derby数据库中，推荐使用MySQL存储Metastore
2.使用HDFS进行存储，使用MapReduce进行计算。
3.驱动器
（1）解析器（SQL Parser）：将SQL字符串转换成抽象语法树AST，这一步一般都用第三方工具库完成，比如antlr；对AST进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误。
（2）编译器（Physical Plan）：将AST编译生成逻辑执行计划。
（3）优化器（Query Optimizer）：对逻辑执行计划进行优化。
（4）执行器（Execution）：把逻辑执行计划转换成可以运行的物理计划。对于Hive来说，就是MR/Spark。

简单来说 HIVE就是数据存储在HDFS中 计算是在MapReduce中进行，元数据是由Mysql或derby 数据库完成，而HIVE本身就是一个用于将SQL转换成MapReduce的框架

HIVE安装

hive配置.md

hive安装目录下

Hive的存储格式

  • Hive的数据存储基于Hadoop HDFS。
  • Hive没有专门的数据文件格式，常见的有以下几种：TEXTFILE、SEQUENCEFILE、AVRO、RCFILE、ORCFILE、PARQUET。

建表语句

create table student(
id String comment '编号',
name String comment '姓名',
age int comment '年龄',
gender String comment '性别',
clazz String comment '班级'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','  
;

-- 通过HDFS命令将数据提交到表所在的目录中
hdfs dfs -put ./student.txt /user/hive/warehouse/bigdata.db/student/

-- 查询数据
select * from student limit 10;

默认情况下，HIve的表是以文本文件格式存储的 TextFile
通过数据插入可以看到最终的结果大小为：246.09 MB

RCFile:

create table student_rcfile(
id String comment '编号',
name String comment '姓名',
age int comment '年龄',
gender String comment '性别',
clazz String comment '班级'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','  
STORED AS rcfile
;
-- 由于该表格式为rcfile 压缩格式，不能直接通过hdfs 的put命令上传数据 
-- 而需要使用 insert方式插入数据 

INSERT INTO TABLE student_rcfile SELECT * FROM  student;
-- 通过INSERT语句，根据当前要插入的表格式，进行判断使用对应的类进行压缩数据

通过数据插入可以看到最终的结果大小为：205.08 MB

ORCFile

create table student_orcfile(
id String comment '编号',
name String comment '姓名',
age int comment '年龄',
gender String comment '性别',
clazz String comment '班级'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','  
STORED AS orcfile
;
-- 由于该表格式为rcfile 压缩格式，不能直接通过hdfs 的put命令上传数据 
-- 而需要使用 insert方式插入数据 

INSERT INTO TABLE student_orcfile SELECT * FROM  student;
-- 通过INSERT语句，根据当前要插入的表格式，进行判断使用对应的类进行压缩数据

通过数据插入可以看到最终的结果大小为：951.58 KB
一般情况下，在实际开发过程中，对于查询的结果数据默认保存为 ORCFile 进行压缩 ,效率最高

Parquet：

create table student_parquet(
id String comment '编号',
name String comment '姓名',
age int comment '年龄',
gender String comment '性别',
clazz String comment '班级'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','  
STORED AS parquet
;
-- 由于该表格式为rcfile 压缩格式，不能直接通过hdfs 的put命令上传数据 
-- 而需要使用 insert方式插入数据 

INSERT INTO TABLE student_parquet SELECT * FROM  student;
-- 通过INSERT语句，根据当前要插入的表格式，进行判断使用对应的类进行压缩数据

通过数据插入可以看到最终的结果大小为：19.88 MB

下面我们详细的看一下Hive的常见数据格式：

  • TextFile:
    TEXTFILE 即正常的文本格式，是Hive默认文件存储格式，因为大多数情况下源数据文件都是以text文件格式保存（便于查看验数和防止乱码）。此种格式的表文件在HDFS上是明文，可用hadoop fs -cat命令查看，从HDFS上get下来后也可以直接读取。
    TEXTFILE 存储文件默认每一行就是一条记录，可以指定任意的分隔符进行字段间的分割。但这个格式无压缩，需要的存储空间很大。 虽然可以结合Gzip、Bzip2、Snappy等使用，使用这种方式，Hive不会对数据进行切分，从而无法对数据进行并行操作。一般只有与其他系统由数据交互的接口表采用TEXTFILE 格式，其他事实表和维度表都不建议使用。

  • RCFile:
    Record Columnar的缩写。是Hadoop中第一个列文件格式。 能够很好的压缩和快速的查询性能。通常写操作比较慢，比非列形式的文件格式需要更多的内存空间和计算量。 RCFile是一种行列存储相结合的存储方式。 首先，其将数据按行分块，保证同一个record在一个块上，避免读一个记录需要读取多个block。其次，块数据列式存储，有利于数据压缩和快速的列存取。

  • ORCFile:
    Hive从0.11版本开始提供了ORC的文件格式，ORC文件不仅仅是一种列式文件存储格式，最重要的是有着很高的压缩比，并且对于MapReduce来说是可切分（Split）的。因此，在Hive中使用ORC作为表的文件存储格式，不仅可以很大程度的节省HDFS存储资源，而且对数据的查询和处理性能有着非常大的提升，因为ORC较其他文件格式压缩比高，查询任务的输入数据量减少，使用的Task也就减少了。ORC能很大程度的节省存储和计算资源，但它在读写时候需要消耗额外的CPU资源来压缩和解压缩，当然这部分的CPU消耗是非常少的。

  • Parquet:
    通常我们使用关系数据库存储结构化数据，而关系数据库中使用数据模型都是扁平式的，遇到诸如List、Map和自定义Struct的时候就需要用户在应用层解析。但是在大数据环境下，通常数据的来源是服务端的埋点数据，很可能需要把程序中的某些对象内容作为输出的一部分，而每一个对象都可能是嵌套的，所以如果能够原生的支持这种数据，这样在查询的时候就不需要额外的解析便能获得想要的结果。Parquet的灵感来自于2010年Google发表的Dremel论文，文中介绍了一种支持嵌套结构的存储格式，并且使用了列式存储的方式提升查询性能。Parquet仅仅是一种存储格式，它是语言、平台无关的，并且不需要和任何一种数据处理框架绑定。这也是parquet相较于orc的仅有优势：支持嵌套结构。Parquet 没有太多其他可圈可点的地方,比如他不支持update操作(数据写成后不可修改),不支持ACID等.

  • SEQUENCEFILE:
    SequenceFile是Hadoop API 提供的一种二进制文件，它将数据以<key,value>的形式序列化到文件中。 这种二进制文件内部使用Hadoop 的标准的Writable 接口实现序列化和反序列化。它与Hadoop API中的MapFile 是互相兼容的。Hive 中的SequenceFile 继承自Hadoop API 的SequenceFile，不过它的key为空，使用value 存放实际的值， 这样是为了避免MR 在运行map 阶段的排序过程。 **SequenceFile支持三种压缩选择：NONE, RECORD, BLOCK。 Record压缩率低，一般建议使用BLOCK压缩。 SequenceFile最重要的优点就是Hadoop原生支持较好，有API，**但除此之外平平无奇，实际生产中不会使用。

  • AVRO:
    Avro是一种用于支持数据密集型的二进制文件格式。它的文件格式更为紧凑，若要读取大量数据时，Avro能够提供更好的序列化和反序列化性能。并且Avro数据文件天生是带Schema定义的，所以它不需要开发者在API 级别实现自己的Writable对象。Avro提供的机制使动态语言可以方便地处理Avro数据。最近多个Hadoop 子项目都支持Avro 数据格式，如Pig 、Hive、Flume、Sqoop和Hcatalog。

其中的TextFile、RCFile、ORC、Parquet为Hive最常用的四大存储格式
它们的 存储效率及执行速度比较如下：
    ORCFile存储文件读操作效率最高，耗时比较（ORC<Parquet<RCFile<TextFile）
    ORCFile存储文件占用空间少，压缩效率高(ORC<Parquet<RCFile<TextFile)

Hive操作客户端

常用的客户端有两个：CLI，JDBC/ODBC

  • CLI，即Shell命令行

  • JDBC/ODBC 是 Hive 的Java，与使用传统数据库JDBC的方式类似。

​ Hive 将元数据存储在数据库中(metastore)，目前只支持 mysql、derby。 Hive 中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等；由解释器、编译器、优化器完成 HQL 查询语句从词法分析、语法分析、编译、优化以及查询计划（plan）的生成。生成的查询计划存储在 HDFS 中，并在随后由 MapReduce 调用执行。

​ Hive 的数据存储在 HDFS 中，大部分的查询由 MapReduce 完成（包含 * 的查询，比如 select * from table 不会生成 MapRedcue 任务）

Hive的metastore

metastore是hive元数据的集中存放地。
metastore默认使用内嵌的derby数据库作为存储引擎
Derby引擎的缺点：一次只能打开一个会话
使用MySQL作为外置存储引擎，可以多用户同时访问`

元数据库详解见：查看mysql SDS表和TBLS表

HIVE的基本语法

库操作

创建数据库
CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];

CREATE DATABASE 表示创建数据库
IF NOT EXISTS 表示 如果不存在
COMMENT database_comment 对数据库进行注释
LOCATION 表示当前数据库的存储位置 默认存储位置 /user/hive/warehouse/
WITH DBPROPERTIES 表示数据库的配置信息 一般情况下不使用

CREATE DATABASE IF NOT EXISTS bigdata28
COMMENT 'bigadta28 learn'
LOCATION '/bigdata28';
显示数据库
show databases;

显示过滤查询的数据库

-- 显示以以"db_hive"开头的数据库名称
show databases like 'db_hive*';
查看数据库详情

显示数据库信息

desc database db_hive;

显示数据库详细信息

desc database extended db_hive;
切换当前数据库
use db_hive;
修改数据库

用户可以使用 ALTER DATABASE命令为某个数据库的 DBPROPERTIES设置键-值对属性值，来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。

hive (default)> alter database db_hive set dbproperties('create_time'='20201217');

在hive中查看修改结果

hive> desc database extended db_hive;
OK
db_name	comment	location	owner_name	owner_type	parameters
db_hive		hdfs://hadoop102:9000/warehouse/db_hive.db	dwjf321	USER	{create_time=20201217}
Time taken: 0.044 seconds, Fetched: 1 row(s)
删除数据库

删除空数据库

hive>drop database db_hive;

如果删除的数据库不存在，最好采用 if exists判断数据库是否存在

hive> drop database if exists db_hive;

如果数据库不为空，可以采用cascade命令，强制删除

hive> drop database db_hive cascade;
当前数据库下创表

在当前的bigdata28库中创建表，那么 在HDFS中的表现

create table bigdata28.student(
id String comment '编号',
name String comment '姓名',
age int comment '年龄',
gender String comment '性别',
clazz String comment '班级'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','  
;

当在该数据库中创建的表在HDFS中表现为 都在 LOCATION 指定的路径下创建表

表操作

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
  [(col_name data_type [COMMENT col_comment], ...)]
 [COMMENT table_comment]
  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
  [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
  [
   [ROW FORMAT row_format] 
   [STORED AS file_format]
   | STORED BY 'storage.handler.class.name' [ WITH SERDEPROPERTIES (...) ]  (Note:  only available starting with 0.6.0)
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]  (Note:  only available starting with 0.6.0)
  [AS select_statement]  (Note: this feature is only available starting with 0.5.0.)

CREATE TABLE [IF NOT EXISTS] table_name 如果表不存在则创建表
EXTERNAL 关键字表示为一个外部表
[(col_name data_type [COMMENT col_comment], …)] 定义表中的字段 其中col_name 表示为字段名称 data_type 字段类型（Hive独有） COMMENT 添加注释信息 需要在MySQL中修改对应元数据表设置为UTF8格式
[COMMENT table_comment] 添加表的注释信息
[ROW FORMAT row_format] 表示当前行数据的格式 比如列分隔符和行分隔符
[STORED AS file_format] 表示表的存储格式
[LOCATION hdfs_path] 可以指定表的存储位置
[TBLPROPERTIES (property_name=property_value, …)] 可以设置当前表的配置信息
[AS select_statement] select_statement表示SQL查询语句 将查询的结果作为一张表存储起来

普通表创建
CREATE  TABLE IF NOT EXISTS bigdata.tbl1 (
  name String comment '姓名',
  age int comment '年龄',
  job_year int comment '工作年限',
  course String comment '教授科目'
);

insert into table bigdata.tbl1 values ('笑哥',30,8,'大数据高级');
insert into table bigdata.tbl1 values ('laoxing',18,6,'bigdata');

通过插入数据，并查看数据内容，可以知道hive表默认的存储格式为TextFile,其分隔符为0x01

需求：将一个csv格式的数据，保存成一个HIVE表，那么如何建表

建表指定分隔符
DROP TABLE bigdata.student1000;
CREATE TABLE IF NOT EXISTS bigdata.student1000 (
  id String comment 'id',
  name String comment '姓名',
  age int comment '年龄',
  gender String comment '性别',
  clazz String comment '班级'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

-- 将表数据上传至创建该表对应的HDFS目录下
dfs -put /root/student1000.csv  /user/hive/warehouse/bigdata.db/student1000;

当创建表后，再通过文本文件添加数据到表中时，如果查询的结果，部分列出现NULL值需要排查 1.列分隔符是否出现错误 2.查看类型和数据类型是否一致 （当文本数据中的内容和列字段类型进行转换时，如果类型不匹配，那么会返回NULL）

需求：上述建表时，对于表的描述信息 包括字段类型、分隔符等出现错误，但是数据没有错误，每次删除表，都会将之前上传到HDFS中的数据删除了 。如何只删除元数据信息（表描述信息）而不删除HDFS中的数据

根据查询结果创建表（查询的结果会添加到新创建的表中）
create table if not exists student1 as select * from  student1000;
根据已经存在的表结构创建表
create table if not exists test4 like test;
EXTERNAL 外部表

CREATE EXTERNAL TABLE IF NOT EXISTS bigdata.student1000 (
  id int comment 'id',
  name int comment '姓名',
  age int comment '年龄',
  gender String comment '性别',
  clazz String comment '班级'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

-- 将表数据上传至创建该表对应的HDFS目录下
dfs -put /root/student1000.csv  /user/hive/warehouse/bigdata.db/student1000;

DROP TABLE bigdata.student1000;
CREATE EXTERNAL TABLE IF NOT EXISTS bigdata.student1000 (
  id int comment 'id',
  name String comment '姓名',
  age int comment '年龄',
  gender String comment '性别',
  clazz String comment '班级'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

外部表作用：当表使用drop语句删除时，只能删除表的元数据信息，对于HDFS中的具体数据不会发生任何变化
应用场景： 当数据从外部系统采集到数据仓库（hive）中时，可以使用外部表修饰，防止数据被误删

需求：现在在HDFS中某个目录下已经存在由具体的数据了，那么此时要根据该部分数据进行创建 表并查询数据的条数

STORED AS 用法（指定存储格式）
create table students3
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS rcfile; 
// 指定储存格式为rcfile，inputFormat:RCFileInputFormat,outputFormat:RCFileOutputFormat，
//如果不指定，默认为textfile，
//注意：除textfile以外，其他的存储格式的数据都不能直接加载，需要使用从表加载的方式。
LOCATION
dfs -mkdir -p /bigdata/student1000;
dfs -put /root/student1000.csv /bigdata/student1000;

DROP TABLE bigdata.student1000;
CREATE EXTERNAL TABLE IF NOT EXISTS bigdata.student1000 (
  id int comment 'id',
  name String comment '姓名',
  age int comment '年龄',
  gender String comment '性别',
  clazz String comment '班级'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
LOCATION '/bigdata/student1000'
;

当数据已经存在在HDFS的指定目录中时，可以使用LOCATION方式
注意：对于LOCATION的位置需要放在ROW FORMAT之后

需求：当外部表对应的数据存在问题，需要将数据删除重新导入，那么此时由于外部表删除表信息不会影响数据，那么如何解决

方式1:先将表中元数据删除，再将HDFS中的数据通过HDFS命令删除
方式2：将当前表修改成一个普通表

TBLPROPERTIES

需要给定要设置的参数名和参数值
[TBLPROPERTIES (property_name=property_value, …)]

DROP TABLE bigdata.student1000;
CREATE TABLE IF NOT EXISTS bigdata.student1000 (
  id int comment 'id',
  name String comment '姓名',
  age int comment '年龄',
  gender String comment '性别',
  clazz String comment '班级'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
LOCATION '/bigdata/student1000'
TBLPROPERTIES('EXTERNAL'='TRUE')
;

对于外部表实际上是TBLPROPERTIES中的一个参数，对于外部表可以使用set 进行修改

需求：需要将查询的结果保存到对应的一个表中

内部表与外部表相互转换

查询表的类型

hive (default)> desc formatted test3;
Table Type:         	MANAGED_TABLE

修改内部表 test3为外部表

alter table test3 set tblproperties('EXTERNAL'='TRUE');

查询表的类型

hive (default)> desc formatted test3;
Table Type:         	EXTERNAL_TABLE

修改外部表test3为内部表

alter table test3 set tblproperties('EXTERNAL'='FALSE');

查询表的类型

hive (default)> desc formatted test3;
Table Type:         	MANAGED_TABLE
AS 用法：(创建表)
CREATE TABLE bigdata.clazz_gender_cnt AS
SELECT
clazz
,gender
,count(*) as num
FROM bigdata.student1000
GROUP BY clazz,gender

注意：

  1. 新建表不允许是外部表。
  2. select后面表需要是已经存在的表，建表同时会加载数据。
  3. 会启动mapreduce任务去读取源表数据写入新表
LIKE用法（创建表）

只想建表，不需要加载数据

create table students_1 like students;
分区表

PARTITIONED BY分区

需求：在查询整个表中的数据时，对班级进行过滤，取出文科一班的所有学生

SELECT
*
FROM  bigdata.student1000 WHERE clazz = '文科一班';

对于上述的SQL语句，在执行时，会将整个student1000.csv文件加载到内存中，进行过滤了，当文件过大时，对于整个文件进行遍历取值，效率非常慢，同时，该场景在大数据分析中经常会出现

DROP TABLE bigdata.student_partition_clazz;
CREATE EXTERNAL TABLE IF NOT EXISTS bigdata.student_partition_clazz (
  id int comment 'id',
  name String comment '姓名',
  age int comment '年龄',
  gender String comment '性别'
)
PARTITIONED BY (clazz String comment '班级')
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
;
-- 上述建表语句以班级作为分区字段 
-- /bigdata/student1000该位置中有数据，但是查询结果不显示任何信息，说明分区表和普通表的数据形式不一样
-- 添加数据  

--INSERT INTO TABLE bigdata.student_partition_clazz VALUES('1500100985','申飞珍',21,'女',	'文科一班');  普通表方式插入数据并不适合 分区表

INSERT INTO TABLE bigdata.student_partition_clazz partition(clazz ='wenkeyiban') VALUES('1500100985','申飞珍',21,'女');
INSERT INTO TABLE bigdata.student_partition_clazz partition(clazz ='wenkeyiban') VALUES('1500100986','孔祥信',22,'男');
INSERT INTO TABLE bigdata.student_partition_clazz partition(clazz ='wenkeerban') VALUES('1500100987','唐伟民',23,'男');

注意：
1.分区表在HDFS中存储的数据为除分区字段外的其他数据
2.分区表中分区字段的表现为 在HDFS中表目录下会有分区目录，该目录是由分区字段名=分区值组成
3.相同分区的数据，会放在同一个分区目录下
4.一个分区对应一个分区目录

查询上述分区表时，如果使用 SELECT * FROM bigdata.student_partition_clazz 那么是会加载整个表中的数据 而如果 SELECT * FROM bigdata.student_partition_clazz WHERE clazz =‘wenkeyiban’ 那么只需要加载HDFS中表目录下 wenkeyiban 分区目录的数据即可 ，提高了查询效率

查看表

查看表的元信息：
desc test_table;   
desc extended test_table;  
desc formatted test_table;

注意：
1.desc 是查看表的字段 类型 及 字段描述
2.extended展示表的描述信息
3.formatted 对表的详细信息进行格式化展示

hive> desc formatted bigdata.student1000;
OK

Detailed Table Information

Database: bigdata
OwnerType: USER
Owner: root
CreateTime: Tue Feb 20 10:30:52 CST 2024
LastAccessTime: UNKNOWN
Retention: 0
Location: hdfs://master:9000/bigdata/student1000 查看位置
Table Type: EXTERNAL_TABLE 表类型：EXTERNAL_TABLE 外部表
Table Parameters: 表的参数
EXTERNAL TRUE 如果为False 那么当前表是非外部表

bucketing_version 2
numFiles 1
totalSize 41998
transient_lastDdlTime 1708396252

Storage Information

SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
field.delim ,
serialization.format ,

查看建表
show create table 表名
show create table bigdata.student_partition_clazz;

修改表

修改表信息

change

alter table rename

-- 修改表名称 
alter table test_table rename to new_table;

alter table student rename to student_textfile;
alter table student1000 rename to student;

-- 修改表的字段属性
alter table 表名 change column 旧字段 新字段 字段属性信息等
alter table student change column id id string;

alter table student change column clazz class string comment '新班级列';

修改分区信息
-- 查看所有分区
show partitions bigdata.student_partition_clazz;

-- 删除分区
alter table bigdata.student_partition_clazz drop partition(clazz='wenkeerban');

-- 添加分区
alter table bigdata.student_partition_clazz add partition(clazz='wenkesanban');

修该表属性
alter table bigdata.student set TBLPROPERTIES('EXTERNAL'='FALSE');

删除表
--1.drop 
drop table bigdata.student;

--2.清空表
truncate table bigdata.student_textfile

注意：Truncate只能删除管理表(内部表)，不能删除外部表中数据

分区表

创建分区表 （或多级分区）

创建多级分区就是partition by 多个字段

DROP TABLE bigdata.student_partition_clazz_gender;
CREATE EXTERNAL TABLE IF NOT EXISTS bigdata.student_partition_clazz_gender (
  id int comment 'id',
  name String comment '姓名',
  age int comment '年龄'
)
PARTITIONED BY (clazz String comment '班级',
  gender String comment '性别')
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
;

INSERT INTO TABLE bigdata.student_partition_clazz_gender partition(clazz ='wk1',gender='nv') VALUES('1500100985','申飞珍',21);

对于分区表可以设置多个分区字段，并且按照分区字段的顺序进行分层创建目录，当添加数据时，会按照数据值作为目录中的一部分，具体非分区字段列数据，存放在文件中

分区插入数据中文支持

HIVE开启分区支持中文.txt


INSERT INTO TABLE bigdata.student_partition_clazz_gender partition(clazz ='文科1班',gender='女') VALUES('1500100985','申飞珍',21);

-- 覆盖整个分区中的数据，进行重新写入
INSERT OVERWRITE TABLE bigdata.student_partition_clazz_gender partition(clazz ='文科1班',gender='女') VALUES('1500100985','申飞珍',22);

CREATE TABLE IF NOT EXISTS bigdata.student (
  id String comment 'id',
  name String comment '姓名',
  age int comment '年龄',
  gender String comment '性别',
  clazz String comment '班级'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

dfs -put /root/student1000.csv  /user/hive/warehouse/bigdata.db/student;

-put 方法上传数据

--1.准备数据 -> 非分区字段的数据 

--2.创建分区 
alter table bigdata.student_partition_clazz_gender add partition (clazz='文科六班',gender = '男');

--3.上传数据到指定分区目录中
dfs -put /root/wk6clazz.txt /user/hive/warehouse/bigdata.db/student_partition_clazz_gender/clazz=文科六班/gender=男

增加一个分区
alter table students_pt1 add partition(pt='20210904');
删除分区
alter table students_pt drop partition(pt='20210904');
查看某个表所有分区
 // 推荐这种方式（直接从元数据中获取分区信息）
show partitions students_pt;

// 不推荐
select distinct pt from students_pt;
查询某个分区的数据
// 全表扫描，不推荐，效率低
select count(*) from students_pt;

// 使用where条件进行分区裁剪，避免了全表扫描，效率高
select count(*) from students_pt where pt='20210101';

// 也可以在where条件中使用非等值判断
select count(*) from students_pt where pt<='20210112' and pt>='20210110';

强制删除分区

当前表为分区表，并且为外部表（EXTERNAL修饰的）删除分区时，只能删除分区的元数据信息，不能对分区中的数据进行删除 ，那么如何删除？

方式1.将其变成普通表再删除分区
alter table bigdata.student_partition_clazz_gender set TBLPROPERTIES(‘EXTERNAL’=‘FALSE’);

alter table bigdata.student_partition_clazz_gender drop partition(clazz=‘文科1班’,gender=‘女’);
alter table bigdata.student_partition_clazz_gender set TBLPROPERTIES(‘EXTERNAL’=‘TRUE’);

方式2：分步删除
alter table bigdata.student_partition_clazz_gender drop partition(clazz=‘wk1’,gender=‘nv’);

dfs -rm -r -f /user/hive/warehouse/bigdata.db/student_partition_clazz_gender/clazz=wk1

恢复被删除分区
alter table bigdata.student_partition_clazz_gender drop partition(clazz='文科六班',gender='男');

msck repair table  bigdata.student_partition_clazz_gender;
-- 通过该方式可以在Mysql中添加分区的元数据信息，也可以通过add partition进行手动添加

覆盖原先分区中的数据
INSERT OVERWRITE TABLE bigdata.student_partition_clazz_gender partition(clazz ='文科六班',gender='男') VALUES('1500100985','申飞珍',22);

-- INSERT OVERWRITE 对于分区表只会覆盖当前指定的分区，对于其他分区数据不会发生任何变化

增加动态分区操作

INSERT OVERWRITE TABLE bigdata.student_partition_clazz_gender partition(clazz,gender) SELECT id,name,age,clazz,gender FROM  bigdata.student;
或
INSERT INTO TABLE bigdata.student_partition_clazz_gender partition(clazz,gender) SELECT id,name,age,clazz,gender FROM  bigdata.student;
-- 当直接执行时，提示set hive.exec.dynamic.partition.mode=nonstrict 需要将当前分区模式设置为非严格模式
-- set hive.exec.dynamic.partition=true;	//开启动态分区功能（默认true，开启）

-- 添加数据时，需要从其他查询语句中进行添加，分区字段要求按照顺序，对应在SELECT查询语句的最后 

select * from bigdata.student_partition_clazz_gender where clazz = '文科一班' and gender = '女';

在Hive中使用动态分区操作有以下几个好处：

  1. 自动分区管理：使用动态分区操作可以自动将数据加载到相应的分区中，无需手动创建每个分区。这样可以减少手动管理分区的工作量，特别是在处理大量分区的情况下。
  2. 灵活性和扩展性：动态分区操作使得数据加载更加灵活和可扩展。你可以根据实际数据的分布情况，动态地创建新的分区，并将数据加载到相应的分区中。这样可以方便地处理新的分区和新的数据。
  3. 查询性能优化：通过使用动态分区操作，你可以将数据加载到特定的分区中，并在查询时只处理特定的分区数据。这样可以提高查询性能，因为Hive可以仅扫描相关分区，而不是整个表。这对于大型数据集和复杂查询非常有用。
  4. 数据组织和管理：使用动态分区操作可以更好地组织和管理数据。你可以根据数据的特征和属性，将数据加载到不同的分区中，从而更好地组织和管理数据。这可以提高数据的可读性和可维护性。

总的来说，动态分区操作在Hive中提供了更灵活、更高效的数据加载和查询方式。它可以简化分区管理工作，提高查询性能，并提供更好的数据组织和管理。


分区表作用
  1. 可以在HDFS的表目录中生成分区字段目录
  2. 当分区表被加载查询过滤时，使用WHERE条件语句对分区字段进行过滤，那么会加载指定分区中的数据，避免全表扫描
如何选取分区字段

1.对于后续的查询业务中，该表中部分字段会被经常过滤的，可以作为分区字段
2.对于字段中的信息，需要有一定的区分度
3.由于HIVE数据是存储在HDFS上，并且HDFS元数据信息不能过多，于是分区表中的分区个数不能过多 => 防止产生大量的小文件
4.一般情况下，对于分区表存储的数据会比较大，同时是以时间不断递增的，那么分区表通常使用的字段为 日期字段

分桶表

分桶实际上是对文件（数据）的进一步切分；Hive默认关闭分桶；分桶的作用：在往分桶表中插入数据的时候，会根据 clustered by 指定的字段 进行hash分组 对指定的buckets个数 进行取余，进而可以将数据分割成buckets个数个文件，以达到数据均匀分布，可以解决Map端的“数据倾斜”问题，方便我们取抽样数据，提高Map join效率；分桶字段 需要根据业务进行设定

1)、开启分桶开关

hive> set hive.enforce.bucketing=true;

创建分桶表

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
	 [(col_name data_type [COMMENT col_comment], ...)]
	[CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] --根据 给定的列进行 分桶排序 指定存入 N 个桶中

需求

-- 将bucket.txt中的数据分成4个桶进行存储
CREATE TABLE IF NOT EXISTS learn5.bucket_table(
id int,name STRING
)
CLUSTERED BY(id) INTO 4 BUCKETS
ROW FORMAT DELIMITED FIELDS TERMINATED BY ",";

-- 在1.X load data local inpath 方式导入分桶数据时，并不会产生分桶效果 -- 3.X 通过load可以产生桶
--load data local inpath "/usr/local/soft/hive-1.2.1/data/id_name.txt" into table learn5.bucket_table;


INSERT INTO TABLE learn5.bucket_table values ('1','student1'),('2','student2'),('3','student3'),('4','student4'),('5','student5'),('6','student6'),('7','student7'),('8','student8');

启用桶表

set hive.enforce.bucketing=true;
--限制对桶表进行load操作 --3.x中可以使用
set hive.strict.checks.bucketing = false;

通过查看表对应HDFS上的路径 可以看到数据分为4分，这样有什么好处？

通过查看000000_0文件数据可以看到，ID列中对4取余都为0
通过查看000001_0文件数据可以看到，ID列中对4取余都为1

这样我们后面去表中取数据，如果对id进行过滤,如下SQL语句
SELECT * FROM learn2.bucket_table WHERE id in ('4','1');
那么它的执行逻辑为： 对需要查找的 id ('4','1') 进行取余，
	如果余数为0那么就去000000_0文件中加载数据 
	如果余数为1那么就去000001_0文件中加载数据 
这样就可以避免加载不必要的数据，提高执行效率

查看桶的数量

DESC FORMATTED learn2.bucket_table; 
-- 输出信息中 Num Buckets: 4 参数查看具体对应的桶数

注意：

  • 分桶表可以和分区表搭配使用
  • 分桶表中的字段需要在表字段定义时存在，分区表一定不能存在于字段定义阶段

JDBC连接

1)、启动hiveserver2的服务

hive --service hiveserver2 &

2)、 新建maven项目并添加两个依赖

    <dependency>
        <groupId>org.apache.hadoop</groupId>
        <artifactId>hadoop-common</artifactId>
        <version>2.7.6</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.hive/hive-jdbc -->
    <dependency>
        <groupId>org.apache.hive</groupId>
        <artifactId>hive-jdbc</artifactId>
        <version>1.2.1</version>
    </dependency>

3)、 编写JDBC代码

import java.sql.*;

public class HiveJDBC {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("org.apache.hive.jdbc.HiveDriver");
        Connection conn = DriverManager.getConnection("jdbc:hive2://master:10000/test3");
        Statement stat = conn.createStatement();
        ResultSet rs = stat.executeQuery("select * from students limit 10");
        while (rs.next()) {
            int id = rs.getInt(1);
            String name = rs.getString(2);
            int age = rs.getInt(3);
            String gender = rs.getString(4);
            String clazz = rs.getString(5);
            System.out.println(id + "," + name + "," + age + "," + gender + "," + clazz);
        }
        rs.close();
        stat.close();
        conn.close();
    }
}

数据加载

load 语句加载
CREATE TABLE IF NOT EXISTS bigdata.student_load (
  id String comment 'id',
  name String comment '姓名',
  age int comment '年龄',
  gender String comment '性别',
  clazz String comment '班级'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

-- load加载本地Linux中的数据到HIVE中
load data local inpath '/root/student1000.csv' into table  bigdata.student_load;

-- 当数据存储在HDFS中 
dfs -put /root/student1000.csv /data/
-- 清空表数据
truncate table bigdata.student_load;
-- 上传数据 
load data inpath '/data/student1000.csv' into table  bigdata.student_load;

dfs -put

使用HDFS的命令进行上传数据

使用hdfs dfs -put '本地数据' 'hive表对应的HDFS目录下'

INSERT 方式
-- 插入数据到表中
INSERT INTO TABLE 表名称 VALUES (数据),(数据)...;
INSERT INTO TABLE 表名称 SELECT 查询语句;


CREATE TABLE IF NOT EXISTS bigdata.student_insert (
  id String comment 'id',
  name String comment '姓名',
  age int comment '年龄',
  gender String comment '性别',
  clazz String comment '班级'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

INSERT INTO TABLE student_insert VALUES ('1500100993','衡从蕾',21,	'女',	'理科二班'),('1500100999',	'钟绮晴',	23,	'女',	'文科五班');

-- 给定数据缺失部分字段时，可以指定插入的列
INSERT INTO TABLE student_insert (id,name,age,gender) VALUES ('1500100993','衡从蕾',21,	'女'),('1500100999',	'钟绮晴',	23,	'女');


-- 插入时，查询字段的顺序需要和表字段定义的顺序一致
INSERT INTO TABLE student_insert SELECT id,name,age,clazz,gender FROM student limit 10;
-- 插入时，可以指定插入的部分列
INSERT INTO TABLE student_insert (id,name,age,clazz,gender) SELECT id,name,age,clazz,gender FROM student limit 10;

-- 覆盖数据到表中
INSERT OVERWRITE TABLE 表名称 VALUES (数据),(数据)...;
INSERT OVERWRITE TABLE 表名称 SELECT 查询语句;

-- 对整个表进行覆盖写入
INSERT OVERWRITE TABLE student_insert VALUES ('1500100993','衡从蕾',21,	'女',	'理科二班'),('1500100999',	'钟绮晴',	23,	'女',	'文科五班');


INSERT OVERWRITE TABLE student_insert SELECT id,name,age,gender,clazz FROM student limit 10;

注意：INSERT … SELECT 方式在开发过程中最为常用
INSERT OVERWRITE = truncate table + INSERT INTO

AS SELECT
-- 建表时通过SELECT语句将查询结果创建成一个表

CREATE TABLE bigdata.student_where_age_gender AS
SELECT id,name,age,gender,clazz FROM student WHERE age > 22 and gender = '男'

当做SQL查询时，如果SQL逻辑比较复杂，会产生多个中间结果数据（子查询），此时可以将子查询的数据通过该方式保存到HIVE中

  1. 如果SQL查询结果有问题，可以通过中间生成的数据进行排查
  2. 当子查询被使用多次，那么将该数据保存后，可以进行反复使用，节约计算资源

LOCATION

在建表时，指定数据的存储位置，通常和外部表搭配使用

IMPORT TABLE 表名 FROM “路径”
IMPORT TABLE student_import FROM "/data/export/student"

IMPORT方式是对Export导出的数据进行导入操作

数据导出

INSERT OVERWRITE LOCAL DIRECTORY “路径”
-- 将SELECT的查询数据保存到本地路径中 
-- ROW FORMAT 指定分隔符 
INSERT OVERWRITE LOCAL DIRECTORY "/root/student/select" 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
SELECT 
clazz
,count(*) as num
FROM bigdata.student
group by clazz

将SELECT的查询数据保存到/root/student/select路径中

HDFS get 方法

使用get方法将表中的所有数据保存到本地

hive -e >
-- 可以直接给定一个SQL语句对数据进行查询
hive -e   

hive -e "SELECT substring(clazz,0,2),count(*) num FROM bigdata.student group by substring(clazz,0,2)"


hive -e "SELECT substring(clazz,0,2),count(*) num FROM bigdata.student group by substring(clazz,0,2)" > ./clazz_num.txt

将查询结果重定向到"./clazz_num.txt"文件中

hive -f >
-- 将需要查询的SQL语句保存到文件中
vim select.sql
-- -f进行执行

hive -f select.sql

将一个SQL脚本中的所有SQL语句进行执行

export方法

-- export是将查询的数据保存到HDFS中
export table 表名 to "HDFS路径"
export table bigdata.student TO "/data/export/student"

通过export可以将数据导出到HDFS指定路径，并且包含了数据的描述信息 表结构

数据类型

简单数据类型
基本数据类型
整型

TINYINT — 微整型，只占用1个字节，只能存储0-255的整数。
SMALLINT– 小整型，占用2个字节，存储范围–32768 到 32767。
INT– 整型，占用4个字节，存储范围-2147483648到2147483647。
BIGINT– 长整型，占用8个字节，存储范围-263到263-1。

浮点型

浮点型 FLOAT– 单精度浮点数。
DOUBLE– 双精度浮点数。

字符串

字符串型STRING– 不设定长度。

对于 Hive 的 String 类型相当于数据库的 varchar 类型，该类型是一个可变的字符串，不过它不能声明其中最多能存储多少个字符，理论上它可以存储 2GB 的字符数。

布尔型

布尔型 BOOLEAN — TRUE/FALSE

日期类型：

1，Timestamp 格式“YYYY-MM-DD HH：MM：SS.fffffffff”（9位小数位精度）
2，Date DATE值描述特定的年/月/日，格式为YYYY-MM-DD。

create table tbl_type_int(
  col_int1 TINYINT,
  col_int2 SMALLINT,
  col_int3 INT,
  col_int4 BIGINT
);

insert into table tbl_type_int values(254,32767,10,1000);
insert into table tbl_type_int values('254','32767',10,1000);

create table tbl_type_f(
  col1 Float,
  col2 DOUBLE
);
insert into table tbl_type_f values(3.14,1000.0),(3,1000);

create table tbl_type_b(
  col1 boolean
);
insert into table tbl_type_b values(TRUE),(true);
-- 大写小写都可以插入识别

create table tbl_type_d(
  col1 timestamp,
  col2 date
);
insert into table tbl_type_d values('2024-02-20 16:18:33','2024-02-20'),('2024-02-20','2024-02-20 16:18:33');
-- HIVE中对于日期和时间格式数据会进行格式化
  • 时间戳与时间字符串转换
// from_unixtime 传入一个时间戳以及pattern（yyyy-MM-dd） 
//可以将 时间戳转换成对应格式的字符串
select from_unixtime(1630915221,'yyyy年MM月dd日 HH时mm分ss秒')

// unix_timestamp 传入一个时间字符串以及pattern，
//可以将字符串按照pattern转换成时间戳
select unix_timestamp('2021年09月07日 11时00分21秒','yyyy年MM月dd日 HH时mm分ss秒');
select unix_timestamp('2021-01-14 14:24:57.200')
复杂数据类型
Array数据类型
-- Array数据类型可以在一列中存储多个数据
create table tbl_arr(
  name String ,
  score Array<int>
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
COLLECTION ITEMS TERMINATED BY ',';

-- 给定列的分隔符 以及Array中的分隔符

zhangsan	70,80,90
lisi	90,100,80

vim arr_data.txt
load data local inpath '/root/arr_data.txt' into table tbl_arr;

-- 数组定义时，实际上是有对应下标，所以取值可以使用下标进行

select score[0] from tbl_arr

-- 获取数组中的数量
select size(score) from tbl_arr
Map数据类型
-- Map数据类型是 KeyValue类型
create table tbl_map(
  name String ,
  score Map<String,int>
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
;

zhangsan	yy:70,yw:80,sx:90
lisi	yy:90,sx:100,yw:80

load data local inpath '/root/map_date.txt' into table tbl_map;

SELECT  score['yw']  FROM tbl_map;
SELECT  size(score)  FROM tbl_map;
Struct数据类型
-- STRUCT类型类似于结构体，可以包含多个有序的字段。
create table tbl_struct(
  name String ,
  score struct<course:String,num:int>
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
COLLECTION ITEMS TERMINATED BY ','
;

zhangsan	yy,70
zhangsan	yw,80
zhangsan	sx,90
lisi	yy,90
lisi	sx,100
lisi	yw,80
load data local inpath '/root/struct_data.txt' into table tbl_struct;

-- 取值
SELECT
name
,score.course as course
,score.num as num
FROM tbl_struct

类型转换


create table tbl_str(
  col String
)
insert into table tbl_str values ('11');
select col+1 from tbl_str; 
-- HIVE中对于字符串可以自动类型转换 

-- 手动转换
create table cast_tbl AS
SELECT cast(col as int) FROM tbl_str;

-- 转换时可能会出现精度丢失的现象 
insert overwrite table tbl_str values ('11.13');

cast 函数在某些场景下 非常重要，对于有些函数必须使用固定的数据类型，类型不一致会报错

简单查询
过滤操作

where 在表加载 FROM 以及JOIN 之后进行添加的
where中添加的判断条件 between、in 、not in、is NULL、IS NOT NULL、< /> / <> / = / !=
having 在表使用GROUP BY之后添加的

create table emp(
    EMPNO int
    ,ENAME string
    ,JOB string
    ,MGR int
    ,HIREDATE string
    ,SAL int
    ,BONUS string
    ,DEPTNO int
) 
row format delimited
fields terminated by ',';

load data local inpath '/root/emp.txt' into table emp;
load data local inpath '/root/dept.txt' into table dept;

需求：取出薪资范围在2000-3000之间

SELECT
ENAME
,SAL
FROM emp
WHERE SAL between 2450 and 3000

between是一个左闭右闭的区间

需求：取出部门编号在10,20的所有员工

SELECT
ENAME
,SAL
,deptno
FROM emp
WHERE deptno in (10,20)

SELECT
ENAME
,SAL
,deptno
FROM emp
WHERE deptno not in (10,20)

需求：取出所有没有年终奖的员工

SELECT
*
FROM emp
WHERE BONUS IS NULL 


SELECT
*
FROM emp
WHERE length(BONUS) = 0

对于数据判断是否为空，可以使用NULL也需要查看字符串的长度是否为0

类型转换

Hive 的原子数据类型是可以进行隐式转换的，类似于 Java 的类型转换，例如某表达式使用 INT 类型，TINYINT 会自动转换为 INT 类型，但是 Hive 不会进行反向转化，例如，某表达式使用 TINYINT 类型，INT 不会自动转换为TINYINT 类型，它会返回错误，除非使用 CAST 操作。

隐式类型转换规则如下

任何整数类型都可以隐式地转换为一个范围更广的类型，如 TINYINT 可以转换成 INT，INT 可以转换成BIGINT。

所有整数类型、FLOAT 和 STRING 类型都可以隐式地转换成 DOUBLE。

TINYINT、SMALLINT、INT 都可以转换为 FLOAT。

BOOLEAN 类型不可以转换为任何其它的类型。

可以使用CAST操作显示进行数据类型转换

例如 CAST(‘1’ AS INT)将把字符串’1’转换成整数1；如果强制类型转换失败，如执行CAST(‘X’ AS INT)，表达式返回空值 NULL。

Hive HQL使用语法

我们知道SQL语言可以分为5大类：
(1）DDL(Data Definition Language) 数据定义语言
用来定义数据库对象：数据库，表，列等。
关键字：create，drap,alter等
( 2）DML(Data Manipulation Language) 数据操作语言
用来对数据库中表的数据进行增删改。
关键字：insert,delete,update等
( 3）DQL(Data Query Language)数据查询语言
用来查询数据库表的记录（数据）。
关键字：select,where 等
( 4）DCL(Data Control Language) 数据控制语言
用来定义数据库的访问权限和安全级别，及创建用户。
关键字：GRANT，REVOKE等
(5)TCL(Transaction Control Language) 事务控制语言
T CL经常被用于快速原型开发、脚本编程、GUI和测试等方面，
关键字: commit、rollback等。
HQL-DDL
创建数据库 create database xxxxx;
查看数据库 show databases；
删除数据库 drop database tmp;
强制删除数据库：drop database tmp cascade;
查看表：SHOW TABLES；
查看表的元信息：
    desc test_table;
    describe extended test_table;
    describe formatted test_table;
查看建表语句：show create table table_XXX
重命名表：
    alter table test_table rename to new_table;
修改列数据类型：alter table lv_test change column colxx string;
增加、删除分区：
    alter table test_table add partition (pt=xxxx) 
    alter table test_table drop if exists partition(...);
HQL-DML
where  用于过滤，分区裁剪，指定条件

join  用于两表关联，left outer join ，join，mapjoin（1.2版本后默认开启）

group by  用于分组聚合，通常结合聚合函数一起使用

order by  用于全局排序，要尽量避免排序，是针对全局排序的，即对所有的reduce输出是有序的

sort by  :当有多个reduce时，只能保证单个reduce输出有序，不能保证全局有序

cluster by = distribute by + sort by

distinct 去重

Hive中order、sort、distribute、cluster by区别与联系

hive中的order by 会对查询结果集执行一个全局排序，这也就是说所有的数据都通过一个reduce进行处理的过程，对于大数据集，这个过程将消耗很大的时间来执行。

hive的sort by 也就是执行一个局部排序过程。这可以保证每个reduce的输出数据都是有序的(但并非全局有效)。这样就可以提高后面进行的全局排序的效率了。对于这两种情况，语法区别仅仅是，一个关键字是order，另一个关键字是sort。用户可以指定任意期望进行排序的字段，并可以在字段后面加上asc关键字(默认)表示升序，desc关键字是降序排序。

在使用sort by之前，需要先设置Reduce的数量>1，才会做局部排序，如果Reduce数量是1，作用与order by一样，全局排序。如：set mapred.reduce.tasks=2;

distribute by 控制 map的输出在reduer中是如何划分的，mapreduce job 中传输的所有数据都是按照键-值对的方式进行组织的，因此hive在将用户的查询语句转换成mapreduce job时，其必须在内部使用这个功能。默认情况下，MapReduce计算框架会依据map输入的键计算相应的哈希值，然后按照得到的哈希值将键-值对均匀分发到多个reducer中去，不过不幸的是，这也是意味着当我们使用sort by 时，不同reducer的输出内容会有明显的重叠，至少对于排序顺序而已只这样，即使每个reducer的输出的数据都有序的。如果我们想让同一年的数据一起处理，那么就可以使用distribute by 来保证具有相同年份的数据分发到同一个reducer中进行处理，然后使用sort by 来安装我们的期望对数据进行排序:

cluster by 除了distribute by 的功能外，还会对该字段进行排序，所以cluster by = distribute by +sort by 。

Hive HQL使用注意

count(*)、count(1) 、count(‘字段名’) 的区别

HQL 执行优先级：
from、where、 group by 、having、order by、join、select 、limit

where 条件里不支持不等式子查询，实际上是支持 in、not in、exists、not exists

hive中大小写不敏感

在hive中，数据中如果有null字符串，加载到表中的时候会变成 null （不是字符串）
如果需要判断 null，使用 某个字段名 is null 这样的方式来判断;或者使用 nvl() 函数，不能 直接 某个字段名 == null

NVL()函数是一个在SQL中常用的函数，它用于判断一个表达式是否为NULL，如果表达式为NULL，则返回指定的默认值；如果表达式不为NULL，则返回表达式本身的值。

语法如下：

NVL(expr1, default_value)
Copy

参数说明：

  • expr1: 要判断是否为NULL的表达式。
  • default_value: 如果expr1为NULL，则返回的默认值。

使用explain查看SQL执行计划

简单查询

见文档下 hive查询.md

过滤操作

where,having,on,between,in,not in,IS NULL,IS NOT NULL,< /> / <>/=/!=

where 在表加载 FROM 以及JOIN 之后进行添加的
where中添加的判断条件 between、in 、not in、is NULL、IS NOT NULL、< /> / <> / = / !=
having 在表使用GROUP BY之后添加的

create table emp(
    EMPNO int
    ,ENAME string
    ,JOB string
    ,MGR int
    ,HIREDATE string
    ,SAL int
    ,BONUS int
    ,DEPTNO int
) 
row format delimited
fields terminated by ',';

load data local inpath '/root/emp.txt' into table emp;
load data local inpath '/root/dept.txt' into table dept;

需求：取出薪资范围在2000-3000之间

SELECT
ENAME
,SAL
FROM emp
WHERE SAL between 2450 and 3000

between是一个左闭右闭的区间

需求：取出部门编号在10,20的所有员工

SELECT
ENAME
,SAL
,deptno
FROM emp
WHERE deptno in (10,20)

SELECT
ENAME
,SAL
,deptno
FROM emp
WHERE deptno not in (10,20)

需求：取出所有没有年终奖的员工

SELECT
*
FROM emp
WHERE BONUS IS NULL 


SELECT
*
FROM emp
WHERE length(BONUS) = 0

对于数据判断是否为空，可以使用NULL也需要查看字符串的长度是否为0

关联

JOIN,LEFT JOIN,RIGHT JOIN

JOIN

将emp表和dept表进行普通关联

SELECT
t1.ENAME
,t1.deptno
,t2.deptname
FROM emp t1 JOIN dept t2 ON t1.deptno = t2.deptno


-- Mysql

SELECT
t1.ENAME
,t1.deptno
,t2.deptname
FROM emp t1, dept t2 
WHERE t1.deptno = t2.deptno
-- 不推荐该写法 
-- FROM emp t1, dept t2 是将两张表做了全关联操作
-- 之后再对关联后的结果进行条件过滤

LEFT JOIN / RIGHT JOIN
SELECT
t1.ENAME
,t1.deptno
,t2.deptname
FROM emp t1 LEFT JOIN dept t2 ON t1.deptno = t2.deptno

左关联：
FROM 表1 LEFT JOIN 表2 ON 关联条件
对于表1是主表 表2 为从表，保留表1中的所有数据，表2中的数据如果关联条件为true,则保存，如果为false 则以NULL替代

SELECT
t1.ENAME
,t1.deptno
,t2.deptname
FROM emp t1 RIGHT JOIN dept t2 ON t1.deptno = t2.deptno

右关联：
FROM 表1 RIGHT JOIN 表2 ON 关联条件
对于表2是主表 表1 为从表，保留表2中的所有数据，表1中的数据如果关联条件为true,则保存，如果为false 则以NULL替代

需求：将emp表中所有的数据和dept表中所有数据都保存


-- dept表中所有关联到的数据以及未关联数据
SELECT
t1.ENAME
,t1.deptno
,t2.deptname
FROM emp t1 RIGHT JOIN dept t2 ON t1.deptno = t2.deptno
UNION ALL
SELECT
tt1.ENAME
,tt1.deptno
,tt1.deptname
FROM(
SELECT
t1.ENAME
,t1.deptno
,t2.deptname
FROM emp t1 LEFT JOIN dept t2 ON t1.deptno = t2.deptno
)tt1 WHERE tt1.deptname IS NULL

拼接

UNION / UNION ALL

SELECT
t1.ENAME
,t1.deptno
,t2.deptname
FROM emp t1 RIGHT JOIN dept t2 ON t1.deptno = t2.deptno
UNION 
SELECT
t1.ENAME
,t1.deptno
,t2.deptname
FROM emp t1 LEFT JOIN dept t2 ON t1.deptno = t2.deptno

UNION ALL 会直接拼接两个表的所有数据 相对来说效率高 但是不对数据进行去重
UNION 会对数据进行去重操作，效率低

分组 GROUP BY

需求：取出所有部门的员工数

-- count(*) 是对所有行统计行数 
SELECT
t1.deptno
,t1.deptname
,count(*) as num
FROM dept t1 LEFT JOIN emp t2 ON t1.deptno = t2.deptno
GROUP BY t1.deptno,t1.deptname

-- 对于当前的emp 添加1列值为1的表示当前人数
SELECT
t1.deptno
,t1.deptname
,sum(num) as all_num
FROM dept t1 LEFT JOIN
(SELECT *,1 as num FROM emp)  t2 ON t1.deptno = t2.deptno
GROUP BY t1.deptno,t1.deptname

-- count(字段):对当前给定字段中，计算多少不为NULL的行数 
SELECT
t1.deptno
,t1.deptname
,count(t2.empno) as num
FROM dept t1 LEFT JOIN emp t2 ON t1.deptno = t2.deptno
GROUP BY t1.deptno,t1.deptname

注意：
GROUP BY 在SELECT中只能添加 分组的字段/条件，以及聚合函数

需求：求出每个部门一年的人力成本，并过滤成本大于 5000的所有部门

SQL的处理顺序
FROM JOIN ON WHERE GROUP BY HAVING SELECT ODERBY LIMIT

SELECT
t1.deptno
,t1.deptname
,sum(t2.comsu) as all_comsu
FROM dept t1 LEFT JOIN (
SELECT
empno
,deptno
,sal * 12 + nvl(bonus,0) as comsu
FROM emp
) t2 ON t1.deptno = t2.deptno
GROUP BY t1.deptno,t1.deptname
HAVING all_comsu > 5000

去重

distinct / group by

在Hive中，GROUP BY语句用于对查询结果进行分组聚合操作。GROUP BY语句会根据指定的列对结果集进行分组，然后对每个组进行聚合操作，如COUNT()、SUM()等。

在Hive中，GROUP BY语句并不会自动去重。如果在GROUP BY语句中存在重复的值，那么这些重复的值仍然会被分到同一个组中，并进行聚合操作。

如果需要去重操作，可以结合使用DISTINCT关键字和GROUP BY语句来实现。例如，可以先使用DISTINCT关键字去除重复值，然后再使用GROUP BY进行分组聚合操作。

总结来说，在Hive中，GROUP BY语句不会自动去重，如果需要去重操作，需要额外使用DISTINCT关键字来去除重复值。

需求：求出所有员工表中的部门编号

SELECT
deptno
FROM emp 
group by deptno


SELECT
distinct deptno
FROM emp

对于HIVE执行SQL时，会调用MapReduce任务，其Reduce Task数量可以通过
set mapred.reduce.tasks=n; 设置 ，其默认值为-1 可以根据数据量以及对应的操作调整
set mapred.reduce.tasks=3;
通过该参数的调整,发现 distinct 可以使用多个Reduce进行任务计算

with as

需求：求出所有薪资大于员工平均薪资，并且小于部门最高薪资的所有员工

-- 对于该SQL语句其中子查询在过程中
with avg_tbl as (
SELECT
avg(sal) as avg_sal
FROM emp
)
,max_tbl as (
SELECT
deptno
,max(sal) as max_sal
FROM emp
GROUP BY deptno
)
SELECT
T.sal
,T.deptno
,T.empno
FROM emp T 
JOIN avg_tbl T1 ON 1=1
JOIN max_tbl T2 ON T.deptno = T2.deptno
WHERE T.sal > T1.avg_sal AND T.sal < T2.max_sal

with as 的作用：
1.可以方便的整理出SQL编写逻辑
2.将子查询数据作为一个表存放在内存当中，在后续使用中，避免对相同表数据进行重复计算

with as的格式：
with 注册的表名称1 as (
SELECT 查询语句
)
,注册的表名称2 AS (
SELECT 查询语句
[FROM 注册的表名称1] – 也可以使用之前注册过的表
)
,…
SELECT 将上述的数据进行查询

排序

需求：对于每个部门中的员工一年的收入进行从大到小排序

order by

order by

hive中的order by 会对查询结果集执行一个全局排序，这也就是说所有的数据都通过一个reduce进行处理的过程，对于大数据集，这个过程将消耗很大的时间来执行。

SELECT
empno
,deptno
,sal * 12 + nvl(bonus,0) as income
FROM emp
order by deptno,income desc

sort by
SELECT
empno
,deptno
,sal * 12 + nvl(bonus,0) as income
FROM emp
sort by deptno,income desc

hive的sort by 也就是执行一个局部排序过程。这可以保证每个reduce的输出数据都是有序的(但并非全局有效)。这样就可以提高后面进行的全局排序的效率了。对于这两种情况，语法区别仅仅是，一个关键字是order，另一个关键字是sort。用户可以指定任意期望进行排序的字段，并可以在字段后面加上asc关键字(默认)表示升序，desc关键字是降序排序。

在使用sort by之前，需要先设置Reduce的数量>1，才会做局部排序，如果Reduce数量是1，作用与order by一样，全局排序。

set mapred.reduce.tasks=4;
当Reduce数量发生变化时，此时结果不正确

INSERT OVERWRITE LOCAL DIRECTORY "/root/sort" 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
SELECT
empno
,deptno
,sal * 12 + nvl(bonus,0) as income
FROM emp
sort by deptno,income desc

通过上述将数据保存到文件中，发现每个ReduceTask生成的数据文件，都是符合 部门编号升序 收入降序的要求

distribute by + sort by
INSERT OVERWRITE LOCAL DIRECTORY "/root/distribute" 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
SELECT
empno
,deptno
,sal * 12 + nvl(bonus,0) as income
FROM emp
distribute by deptno  sort by deptno,income desc

**distribute by + sort by 可以先对数据进行分区，将distribute by给定的列作为数据划分的依据，将相同的值发送到一个Reduce中 ，之后再对相同Reduce中的所有数据 进行sort by 排序 **
**该方式可以使用多个Reduce提升效率，但是从一定意义上可以看出对于distribute by列 并没有完全的全局排序 **

distribute by 控制 map的输出在reduer中是如何划分的，mapreduce job 中传输的所有数据都是按照键-值对的方式进行组织的，因此hive在将用户的查询语句转换成mapreduce job时，其必须在内部使用这个功能。默认情况下，MapReduce计算框架会依据map输入的键计算相应的哈希值，然后按照得到的哈希值将键-值对均匀分发到多个reducer中去，不过不幸的是，这也是意味着当我们使用sort by 时，不同reducer的输出内容会有明显的重叠，至少对于排序顺序而已只这样，即使每个reducer的输出的数据都有序的。如果我们想让同一年的数据一起处理，那么就可以使用distribute by 来保证具有相同年份的数据分发到同一个reducer中进行处理，然后使用sort by 来安装我们的期望对数据进行排序:

**cluster by **
SELECT
empno
,deptno
,sal * 12 + nvl(bonus,0) as income
FROM emp
cluster by income ;

**cluster by => 等于distribute by + sort by 但是不能对数据进行 desc 操作 **
**可以从小到大使用多个Reduce 对某列进行排序 **

函数基本使用

详见 Hive函数大全.pdf

查看函数

-- 查看所有支持的函数
show functions;
-- 对于函数进行模糊查询
show functions like '*str*';
-- 查看函数的用法
desc function 'substr';
-- 查看函数的完整用法
desc function extended 'substr';

判断函数

if函数
SELECT bonus, if(bonus IS NULL,0,bonus) FROM emp;

IF函数格式：
if(判断条件,判断条件为True返回值,判断条件为False返回值)

NVL函数
SELECT bonus, NVL(bonus,0) FROM emp;

NVL函数格式：
NVL(字段,为NULL的返回值)
对当前的字段判断是否为NULL,如果为NULL，那么返回参数值，不为NULL返回自身

COALESCE函数
select COALESCE(NULL,1,NULL,4);
SELECT bonus, COALESCE(bonus,0) FROM emp;
-- 如果该行bonus为null，则返回0，否则返回原值

COALESCE函数格式：
COALESCE(列1,列2,列3…)
**作用：从给定的参数中按照顺序返回第一个不为NULL的数据 **

CASE 函数
SELECT bonus, CASE WHEN bonus IS NULL THEN 0 ELSE  bonus END FROM emp;

CASE函数：
CASE WHEN 判断条件1 THEN 判断条件1为TRUE的返回值
WHEN 判断条件2 THEN 判断条件2为TRUE的返回值
…
ELSE 上述判断条件都为FASE对应的返回值 END
CASE函数可以进行多条件判断 在实际开发过程中，会经常使用，必须要会

字符串函数

必须知道：

  1. length：返回字符串的长度。
  2. concat：将多个字符串连接在一起。
  3. substr,substring：返回字符串的子串，可以指定起始位置和长度。
  4. trim：去除字符串两端的空格。
  5. regexp_replace：使用正则表达式替换字符串中的匹配项。
  6. regexp_extract：使用正则表达式从字符串中提取匹配项。
  7. split：根据指定的分隔符将字符串拆分为数组。

了解：

  1. reverse：反转字符串。
  2. concat_ws：将多个字符串使用指定的分隔符连接在一起。
  3. upper：将字符串转换为大写。
  4. lower：将字符串转换为小写。
  5. get_json_object：从JSON格式的字符串中获取指定路径的值。

日期函数

必须知道：

  1. from_unixtime：将Unix时间戳转换为日期时间格式。
  2. unix_timestamp：将日期时间转换为Unix时间戳。
  3. to_date：从日期时间中提取日期部分。
  4. weekofyear：返回日期所在年份的周数。
  5. datediff：计算两个日期之间的天数差。
  6. date_add：给定日期添加指定天数。
  7. date_sub：给定日期减去指定天数。
  8. current_date：返回当前日期。
  9. add_months：给定日期添加指定月数。

了解：

  1. year：获取日期的年份部分。
  2. month：获取日期的月份部分。
  3. day：获取日期的天数部分。
  4. hour：获取日期的小时部分。
  5. minute：获取日期的分钟部分。
  6. second：获取日期的秒数部分。
  7. last_day：返回给定日期所在月份的最后一天。
  8. trunc：将日期截断到指定精度，如年、月、日等。

其他

** 必须掌握：**

  1. round：对数字进行四舍五入。
  2. floor：向下取整，返回不大于给定参数的最大整数。
  3. ceil：向上取整，返回不小于给定参数的最小整数。
  4. collect_set：将数据集中的唯一值收集到一个集合中。
  5. collect_list：将数据集中的值收集到一个列表中，保留顺序。

需求：对于学生数据 要求将相同班级中的所有学生ID数据放入一列中

SELECT
clazz
,collect_list(id)
FROM student
GROUP BY clazz
  1. collect_set以及collect_list 都是聚合函数，所以需要搭配GROUP BY 使用

需求：对于学生数据 要求将相同班级中的所有的年龄数据汇集到一个列中

SELECT
clazz
,collect_set(age)
FROM student
GROUP BY clazz

对于collect_set会将数据进行去重保存

行列互换

行转列

函数

case···when···then···else···end语句

collect_set()、collect_list()

函数 作用
collect_set(字段) 求出该字段的所有值（不重复，去重）
collect_list(字段) 求出该字段的所有值（存在重复）

它们都是将分组中的某列转为一个数组返回，不同的是collect_list不去重而collect_set去重。

array_contains()、if()

函数 作用
array_contains(数组，判断包含字段) 包含返回true，否则返回false
if ( boolean, true返回，false返回) 判断条件为true返回第一个条件，否则第二个
练习题

需求、数据、建表等

  • 需求：编写Hive的HQL语句求出所有flink课程成绩 大于 spark课程成绩的学生的学号

  • 数据： (序号-id，学号-sid，课程-course，分数-score)

    2,1,flink,55
3,2,spark,77
4,2,flink,88
5,3,spark,98
6,3,flink,65
7,3,hadoop,80

  • 建表、导入数据

    -- 创建表并指定字段分隔符为逗号（，）
create table student_score(id int,sid int,course string,score int) row format delimited fields terminated by ",";

-- 准备数据，放置在服务器文件系统或HDFS。此处放在服务器文件系统上（/root/yber/data/student_score_data.txt）

-- 加载数据到表
load data local inpath "/usr/local/soft/hive-1.2.1/data/student_score_data.txt" into table student_score;

SELECT DISTINCT s1.sid
FROM student_score s1
JOIN student_score s2 ON s1.sid = s2.sid
WHERE s1.course = 'flink' AND s2.course = 'spark' AND s1.score > s2.score;

需求、数据、建表等

  • 需求：

    ​ 有id为1,2,3的学生选修了课程a,b,c,d,e,f中其中几门。

    ​ 编写Hive的HQL语句来实现以下结果：表中的Yes表示选修，表中的No表示未选修

  • 元数据. (id course )

    1,a
1,b
1,c
1,e
2,a
2,c
2,d
2,f
3,a
3,b
3,c
3,e

  • 建表、导入数据

    -- 创建表并指定字段分隔符为逗号（，）
create table if not exists id_course(id int, course string) row format delimited fields terminated by ",";

-- 准备数据，放置在服务器文件系统或HDFS。此处放在服务器文件系统上（/root/yber/data/id_course_data.txt）

-- 加载数据到表
load data local inpath "/usr/local/soft/hive-1.2.1/data/id_course_data.txt" into table id_course;

结果：

id	a	b	c	d	e	f
1	Yes	Yes	Yes	Yes	No	No
2	Yes	No	Yes	Yes	NO	Yes
3	Yes	Yes	Yes	NO	YES	No

SELECT
  id,
  MAX(CASE WHEN course = 'a' THEN 'Yes' ELSE 'No' END) AS a,
  MAX(CASE WHEN course = 'b' THEN 'Yes' ELSE 'No' END) AS b,
  MAX(CASE WHEN course = 'c' THEN 'Yes' ELSE 'No' END) AS c,
  MAX(CASE WHEN course = 'd' THEN 'Yes' ELSE 'No' END) AS d,
  MAX(CASE WHEN course = 'e' THEN 'Yes' ELSE 'No' END) AS e,
  MAX(CASE WHEN course = 'f' THEN 'Yes' ELSE 'No' END) AS f
FROM id_course
GROUP BY id;

列转行

字符串函数-split

函数 用法 含义
split() split(favors,‘-’) 将favors按照-分割成多部分。

炸裂函数-explode

函数 用法 含义 读音
explode() explode(array参数) 将array的值转到多行上 explode /ɪkˈsploʊd/ 爆炸
explode(split(炸裂字段,分隔符)) 和split组合使用，将String类型转为array，然后转到多行上

虚拟视图-lateral view

  • 读音：

lateral /ˈlætərəl/ 侧面的

  • 用法：

LATERAL VIEW udtf(expression) tableAlias AS columnAlias (‘,’ columnAlias)

lateral view explode(split(favors,“-”))t2 as favor;

「t2」—是虚拟视图的名字

「favor」—是虚拟视图列别名

练习题

数据

(city,infos)

北京	朝阳区,海淀区,其他
上海	黄浦区,徐汇区,其他

查询语句

-- 创建表并指定字段分隔符为逗号（\t）
create table city_infos(city string,infos string) row format delimited fields terminated by "\t";

-- 准备数据，放置在服务器文件系统或HDFS。此处放在服务器文件系统上（/root/yber/data/lines_data.txt）

-- 加载数据到表
load data local inpath "/usr/local/soft/hive-1.2.1/data/lines_data.txt" into table city_infos;

查询结果

city	t2.info
北京	朝阳区
北京	海淀区
北京	其他
上海	黄浦区
上海	徐汇区
上海	其他

select explode(split(infos,‘,’)) as info from city_infos;

朝阳区
海淀区
其他
黄浦区
徐汇区
其他

select city,t1.info from city_infos lateral view explode(split(infos,‘,’)) t1 as info;

北京 朝阳区
北京 海淀区
北京 其他
上海 黄浦区
上海 徐汇区
上海 其他

多行转一行

对于学生数据 要求将相同班级的数据 以 ID|姓名 汇集到一个列中，并且列要求是字符串

SELECT
t1.clazz
,concat_ws(',',collect_list(concat_id_name)) as concat_res
FROM (
SELECT
clazz
,concat(id,'|',name) as concat_id_name
FROM student
)t1 GROUP BY t1.clazz

多行转一行 一般情况下，需要使用GROUP BY + 一些聚合函数来完成， 比如Max sum collect_list …

一行转多行

create table words(
  line String
)
insert into table words values('hello,world'),('hello,java'),('hello,hive');
insert into table words values('hello,world,JAVA,SCALA'),('hello,hadoop,hive');

需求：对当前line列中的单词进行统计个数

SELECT
t2.word
,count(*) as num
FROM (
SELECT 
t1.split_res[0]  as word
FROM(
SELECT
split(line,',') as split_res
FROM words
)t1

UNION ALL

SELECT
t1.split_res[1] as word
FROM(
SELECT
split(line,',') as split_res
FROM words
)t1
) t2 GROUP BY t2.word

-- 上述SQL如何进行简化 有没有其他的写法

EXPLODE函数

EXPLODE函数：
炸裂函数，将一个数组，转换成多行数据
EXPLODE函数在SELECT中只能单独存在，不能添加原表中的其他列

-- split函数返回的就是一个数组

SELECT
word
,count(*) as num
FROM (
SELECT
explode(split(line,',')) as word
FROM words
)T1  GROUP BY T1.word

需求：对于电影数据 => 肖申克的救赎,犯罪/剧情 需要将数据转换成
肖申克的救赎,犯罪
肖申克的救赎,剧情

CREATE TABLE movies (
  movie_name STRING,
  movie_type STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 

load data local inpath '/root/movie.txt'  into table movies;

SELECT
explode(split(movie_type,'/')) as types
FROM  movies

LATERAL VIEW EXPLODE
SELECT
movie_name
,tb1.types
FROM movies LATERAL VIEW EXPLODE(split(movie_type,'/'))  tb1 AS types

LATERAL VIEW 表示是一个侧写表 表名称为 tb1
EXPLODE 表示在侧写表中执行了一个炸裂函数，该列会重命名成 types

窗口函数

窗口函数在HIVE中相对较为特殊的一类函数，
该函数可以完成 数据的聚合计算、数据排名、按行进行取值
窗口函数的一般格式：
函数名() OVER()
函数名可以指定数据处理的形式，比如排名使用row_number函数
OVER中可以对窗口数据大小进行调整

需求：对学生的成绩信息和学生的基本信息进行关联，成绩信息中需要计算其总分数据，最终结果要求既包含学生基本信息也要包含其明细数据和总分数据

-- 创建学生成绩表

CREATE TABLE scores (
  id STRING,
  course_id STRING,
  score int
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','

load data local inpath '/root/score.txt' into table scores;

SELECT
T3.*
,T2.*
,T1.total_score
FROM (
SELECT
id
,sum(score) as total_score
FROM scores
GROUP BY id
)T1 
JOIN scores T2 ON T1.id = T2.id
JOIN student T3 ON T1.id = T3.id

窗口统计-count

需求： 求出所有班级的学生总数，最终结果展示需要提供学生的基本信息

SELECT
T2.*
,T1.num
FROM (
SELECT
count(*) as num
FROM student 
)T1 JOIN student T2 ON 1=1

使用窗口函数计算

SELECT
*,count(*) OVER()
FROM student

count(*) 表示对数据的统计函数
OVER() 表示开启一个窗口，在窗口中包含了当前student表中的所有数据

计算逻辑：
1.FROM 加载了当前的student表
2.执行SELECT语句 发现有 ，于是可以取出一行中的所有数据
注意：在SQL执行过程中，执行是按照行遍历当前表中的所有数据
1500100001 施笑槐 22 女 文科六班
3.发现有count() OVER() 其中OVER()是一个窗口，窗口中包含了整个student表中的数据
1500100001 施笑槐 22 女 文科六班
1500100002 吕金鹏 24 男 文科六班
1500100003 单乐蕊 22 女 理科六班
1500100004 葛德曜 24 男 理科三班
1500100005 宣谷芹 22 女 理科五班
1500100006 边昂雄 21 男 理科二班
1500100007 尚孤风 23 女 文科六班
1500100008 符半双 22 女 理科六班
1500100009 沈德昌 21 男 理科一班
1500100010 羿彦昌 23 男 理科六班
…
窗口中的数据进行count(*) 计算 ，得到数据一共1000行

  1. 将count(*) OVER() 在第一行得到的结果 1000 和其他列字段进行合并

1500100001 施笑槐 22 女 文科六班 1000

  1. 再从student中获取第2行的数据，依次根据上述的条件进行计算

1500100002 吕金鹏 24 男 文科六班 1000

窗口限制 Partition BY

需求：求出每个班级的学生总数，最终结果展示需要提供学生的基本信息

SELECT
T2.*
,T1.num
FROM (
SELECT
clazz
,count(*) as num
FROM student 
GROUP BY clazz
)T1 JOIN student T2 ON T1.clazz = T2.clazz


-- 使用窗口函数如何进行计算？
--  如何进行调整窗口大小 方式1:使用partition方式
SELECT
*,count(*) OVER(partition by clazz)
FROM student

计算逻辑：
1.FROM 加载了当前的student表
2.执行SELECT语句 发现有 ，于是可以取出一行中的所有数据
注意：在SQL执行过程中，执行是按照行遍历当前表中的所有数据
1500100001 施笑槐 22 女 文科六班
3.发现有count() OVER(partition by clazz) OVER提供了一个窗口，但是在该窗口中使用了partition by进行限制当前窗口的数据 依据clazz列取出所有班级和当前行的clazz字段相同的数据 clazz =文科六班 所以窗口中的数据为文科六班所有的数据
1500100663 竺旭彬 23 男 文科六班
1500100662 仰景明 23 男 文科六班
1500100255 国平安 21 女 文科六班
1500100658 习昂杰 22 男 文科六班
…
1500100438 宋向南 22 女 文科六班

  1. 使用count(*)对当前窗口中的数据进行计算得到结果为 104
  2. 将结果和 * 的所有列进行合并

1500100001 施笑槐 22 女 文科六班 104

  1. 按照上述的过程再依次往下执行

需求：求出每个班级学生的最高分，以及当前班级中所有学生的基本信息和成绩明细数据

with join_tbl AS (
SELECT
T1.*
,T2.*
FROM student T1 JOIN scores T2 ON T1.id = T2.id
)
, score_sum AS (
SELECT
id
,sum(score) as total_score
FROM scores
GROUP BY id
)
,max_clazz_score AS (
SELECT
clazz
,max(total_score) as max_total_score
FROM join_tbl T1 JOIN score_sum T2 ON T1.id = T2.id
GROUP BY clazz
)
SELECT
T1.*
,T2.max_total_score
FROM join_tbl T1 JOIN max_clazz_score T2 ON T1.clazz =  T2.clazz

-- 窗口函数如何编写？


SELECT
T1.*
,T2.*
,T3.sum_score
,max(T3.sum_score) OVER(PARTITION BY clazz)
FROM student T1 
JOIN scores T2 ON T1.id = T2.id
JOIN (
  SELECT
  id
  ,sum(score) as sum_score
  FROM scores
  GROUP BY id
) T3 ON T3.id = T1.id

窗口其他统计函数

需求：对于emp表数据，需要展示emp中明细数据以及当前部门月薪资总和

SELECT
*
,sum(sal) OVER(PARTITION BY deptno)  as sum_sal
,avg(sal) OVER(PARTITION BY deptno)  as avg_sal
,max(sal) OVER(PARTITION BY deptno)  as max_sal
,min(sal) OVER(PARTITION BY deptno)  as min_sal
,count(*) OVER(PARTITION BY deptno)  as num
FROM emp

窗口帧使用

需求：取出学生总分数据前一行后一行以及当前行的平均分
1500100994 310 305
1500100995 300 300
1500100996 290 295

窗口帧：通过给定的语句，对窗口中的数据进行限制其范围，其方式有两种
1.ROWS 按行限制其大小，2.RANGE 按照值进行限制其大小

Hive 提供了两种定义窗口帧的形式：ROWS 和 RANGE。两种类型都需要配置上界和下界。例如，ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 表示选择分区起始记录到当前记录的所有行；RANGE BETWEEN 100 PRECEDING AND 200 FOLLOWING 则通过 字段差值 来进行选择。如当前行的 close 字段值是 200，那么这个窗口帧的定义就会选择分区中 close 字段值落在 100 至 400 区间的记录。以下是所有可能的窗口帧定义组合。如果没有定义窗口帧，则默认为 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。
只能运用在max、min、avg、count、sum、FIRST_VALUE、LAST_VALUE这几个窗口函数上

(ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)

(ROWS | RANGE) BETWEEN CURRENT ROW AND (CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)

(ROWS | RANGE) BETWEEN [num] FOLLOWING AND (UNBOUNDED | [num]) FOLLOWING

ROWS 表示按行进行取范围
BETWEEN 表示给定范围 如果使用ROWS 那么就是给定行的范围
UNBOUNDED 表示无边界
num 表示限制其行数
PRECEDING 表示往前取数据 针对当前行取数据的方向
AND 表示 BETWEEN 范围的分割
CURRENT ROW 表示当前行
FOLLOWING 表示往后取数据

RANGE 表示按数据的值进行取范围
ORDER BY RANGE必须要和ORDER BY进行搭配使用，对ORDER BY的列值进行取窗口范围
BETWEEN 表示给定范围 如果使用RANGE 那么就是给定值的范围
num 加减某个数值
PRECEDING 表示对ORDER BY的列的值进行加 num
FOLLOWING 表示对ORDER BY的列的值进行减去num

窗口限制 - ROWS多行取值

-- 需求代码
-- 取出学生总分数据前一行后一行以及当前行的平均分 
CREATE TABLE sum_score_tbl AS
SELECT
id
,sum(score) as sum_score
FROM scores
GROUP BY id


1500100001	406
1500100002	440 <-
1500100003	359
1500100004	421
1500100005	395
1500100006	314
1500100007	418
1500100008	363
1500100009	251
1500100010	402

SELECT
id
,sum_score
,avg(sum_score) OVER(ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
FROM sum_score_tbl

需求：根据学生的总分数据，求出往前不限制行数，直到当前行的分数最大值

1500100001	406 406
1500100002	440 440 
1500100003	359 440
1500100004	421 440
1500100005	395 440
1500100006	314 440
1500100007	418 440 <-
1500100008	363 440
1500100009	251 440
1500100010	402 440


SELECT
id
,sum_score
,max(sum_score) OVER(ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM sum_score_tbl


-- 方向调换之后写法 
SELECT
id
,sum_score
,max(sum_score) OVER(ROWS BETWEEN CURRENT ROW  AND UNBOUNDED FOLLOWING)
FROM sum_score_tbl

窗口排序-ORDER BY

需求：需要对学生ID进行排序，排序后学生的总分数据，求出往前不限制行数，直到当前行的分数最大值

对于ORDER BY 可以对窗口中的数据进行排序

SELECT
id
,sum_score
,max(sum_score) OVER( ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM sum_score_tbl

需求：需要对各班级中 学生ID进行排序，排序后各班级学生的总分数据，求出往前不限制行数，直到当前行的分数最大值

SELECT
T1.id
,T1.sum_score
,T2.clazz
,max(T1.sum_score) OVER( PARTITION BY T2.clazz  ORDER BY T1.id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM sum_score_tbl T1 JOIN student T2 ON T1.id = T2.id

PARTITION BY 和ROWS 可以同时限制窗口中的数据

窗口限制-RANGE值范围

需求：求出每个学生比他多10分以及少10分的人数
窗口中成绩数据的范围
1500100001 406 [416-396]
1500100002 440 [450-430]
1500100003 359 [369-349]
1500100004 421 …
1500100010 402 [412-392]

SELECT
*
,count(*) OVER(ORDER BY sum_score RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING)
FROM sum_score_tbl T1
-- ORDER BY 指定 RANGE 中值加减的列

窗口排名

排名：
根据一定的排序规则，对数据先按顺序进行排列，之后再根据行数，从1开始对数据依次进行标记
排名在窗口函数中所包含的有 row_number rank dense_rank

需求： 要求按照班级 对各班中的总分数据从高到低进行排名

--
CREATE TABLE student_total_score AS
SELECT
T2.*
,T1.sum_score as total_score
FROM sum_score_tbl T1 JOIN student T2 ON T1.id  =  T2.id 


SELECT
T1.*
,(SELECT count(*)  FROM student_total_score T2 WHERE T1.clazz = T2.clazz AND T1.total_score  < T2.total_score ) +1
FROM student_total_score T1
ORDER BY T1.clazz,T1.total_score desc

/*
  1.先加载student_total_score表作为T1表
  2.T1.* 会取出表中的所有字段
  3.当取到第一行时
  1500100001	施笑槐	22	女	文科六班	406 
  4.再去执行 执行子查询(SELECT ，在该子查询中对数据进行过滤操作 T1.clazz为文科六班 
Hive笔记
已保存 15:42:02

Hive笔记
和 T2表中的数据进行过滤 取出T2中的 文科六班 数据，之后再对成绩过滤  T1.total_score 为 406 再过滤T2表中大于 406的分数数据 
  5.对T2表中过滤的数据计算其行数  于是行数+1就可以认为是其排名 
  
*/

1500100635	蓬怀绿	23	女	理科四班	534	1
1500100590	逄中震	24	男	理科四班	530	2
1500100939	耿智杰	23	男	理科四班	530	2
1500100501	松迎梅	22	女	理科四班	515	4
1500100853	林鸿朗	24	男	理科四班	515	4

使用窗口函数完成
ROW_NUMBER()/RANK()/DENSE_RANK()

SELECT
*
,ROW_NUMBER() OVER(PARTITION BY clazz ORDER BY total_score desc) row_pm
,RANK() OVER(PARTITION BY clazz ORDER BY total_score desc) rank_pm
,DENSE_RANK() OVER(PARTITION BY clazz ORDER BY total_score desc) dense_pm
FROM student_total_score;

1500100635	蓬怀绿	23	女	理科四班	534	1	1	1
1500100590	逄中震	24	男	理科四班	530	2	2	2
1500100939	耿智杰	23	男	理科四班	530	3	2	2
1500100501	松迎梅	22	女	理科四班	515	4	4	3
1500100853	林鸿朗	24	男	理科四班	515	5	4	3
1500100491	富香薇	23	女	理科四班	491	6	6	4
1500100959	金醉波	23	女	理科四班	491	7	6	4
1500100617	田德明	23	男	理科四班	480	8	8	5
1500100837	广沛凝	21	女	理科四班	479	9	9	6
1500100041	傅景天	24	男	理科四班	468	10	10	7

ROW_NUMBER/RANK/DENSE_RANK函数
作用：用于使用窗口函数对当前窗口中的数据进行计算排名，其使用规则必须要使用ORDER BY对数值列进行指定
区别：
三个函数的区别在于对相同值进行排名时：
ROW_NUMBER 排名 不重复 且连续的
RANK 排名 重复且不连续
DENSE_RANK 排名 重复且连续

其他排名函数

SELECT
*
,count(*) OVER(PARTITION BY clazz) clazz_num
,percent_rank() OVER(PARTITION BY clazz ORDER BY total_score desc) pm1
,cume_dist() OVER(PARTITION BY clazz ORDER BY total_score desc) rank_pm
,NTILE(3) OVER(PARTITION BY clazz ORDER BY total_score desc) group_num
FROM student_total_score 

1500100635	蓬怀绿	23	女	理科四班	534	91	0.0	0.01098901098901099	1
1500100590	逄中震	24	男	理科四班	530	91	0.011111111111111112	0.03296703296703297  1
1500100939	耿智杰	23	男	理科四班	530	91	0.011111111111111112	0.03296703296703297  1
1500100501	松迎梅	22	女	理科四班	515	91	0.03333333333333333	0.054945054945054944 1
1500100853	林鸿朗	24	男	理科四班	515	91	0.03333333333333333	0.054945054945054944 1
1500100491	富香薇	23	女	理科四班	491	91	0.05555555555555555	0.07692307692307693  1
1500100959	金醉波	23	女	理科四班	491	91	0.05555555555555555	0.07692307692307693  1
1500100617	田德明	23	男	理科四班	480	91	0.07777777777777778	0.08791208791208792  1
percent_rank：
(rank的结果-1)/(分区内数据的个数-1)

用于计算当前行数据中的数值列在整个分区中的占比位置

cume_dist：计算某个窗口或分区中某个值的累积分布。

假定升序排序，则使用以下公式确定累积分布：
小于等于当前值x的行数 / 窗口或partition分区内的总行数。其中，x 等于 order by 子句中指定的列的当前行中的值。

NTILE(n)：对分区内数据再分成n组，然后打上组号

对于某个分区中的数据 平均划分成多个部分 可以分开计算

窗口取行

需求：要求对数据按班级和成绩排序 对相同班级取出前1名和当前学生之间的分差
1500100635 蓬怀绿 23 女 理科四班 534
1500100590 逄中震 24 男 理科四班 530 4
1500100939 耿智杰 23 男 理科四班 530 0
1500100501 松迎梅 22 女 理科四班 515 15
1500100853 林鸿朗 24 男 理科四班 515 0
1500100491 富香薇 23 女 理科四班 491 24


T1
1500100635	蓬怀绿	23	女	理科四班	534   1 去除  
1500100590	逄中震	24	男	理科四班	530   2  534    4
1500100939	耿智杰	23	男	理科四班	530   3  530   0
1500100501	松迎梅	22	女	理科四班	515   4  530   15
1500100853	林鸿朗	24	男	理科四班	515   5  515
1500100491	富香薇	23	女	理科四班	491   6  515

T2

/*
  分析：
  1.由于需要对前1名和当前行进行比较，那么需要先对数据进行排名
  2.排名可以选择使用row_number进行，因为不能有重复，且排名要连续 
  3. 根据班级信息和排名进行关联 
*/

with stu_score_pm AS (
SELECT
*
,row_number() over(PARTITION BY clazz ORDER BY total_score desc) pm
FROM student_total_score  
)
SELECT
T1.*
,T2.total_score as lag_score
,T2.total_score - T1.total_score as score_diff
FROM stu_score_pm T1 JOIN  stu_score_pm T2 ON T1.clazz = T2.clazz AND 
T1.pm = T2.pm +1

lag/lead函数

SELECT
*
,lag(total_score,1) OVER(PARTITION BY clazz ORDER BY total_score DESC) as lag_score 
FROM student_total_score  

SELECT
*
,lag(total_score,2) OVER(PARTITION BY clazz ORDER BY total_score DESC) as lag_score 
FROM student_total_score  

SELECT
*
,lead(total_score,1) OVER(PARTITION BY clazz ORDER BY total_score) as lag_score 
FROM student_total_score  

SELECT
*
,lead(total_score,1,750) OVER(PARTITION BY clazz ORDER BY total_score) as lag_score 
FROM student_total_score

lag/lead
作用：取出第N行中某列的数据，其中lag向上取值 lead是向下取值
格式：
lag(取值字段，往上第n行)
lag(取值字段，往上第n行,如果没有填充默认值)
lead(取值字段，往下第n行)
lead(取值字段，往下第n行,如果没有填充默认值)

FIRST_VALUE/LAST_VALUE

SELECT
*
,FIRST_VALUE(total_score) OVER(PARTITION BY clazz ORDER BY total_score DESC) as firt_score 
,LAST_VALUE(total_score) OVER(PARTITION BY clazz ORDER BY total_score DESC) as last_score
FROM student_total_score

FIRST_VALUE：取分组内排序后，截止到当前行，第一个值
LAST_VALUE：取分组内排序后，截止到当前行，最后一个值，对于并列的排名，取最后一个

卡口流量分析

需求1：查询当月的设备及其流量总数

CREATE TABLE veh_pass (
sbbh STRING,
pass_dt STRING,
num int
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

SELECT
substr(pass_dt,1,7)
,sbbh
,sum(num) as sum_num
FROM veh_pass
group by substr(pass_dt,1,7),sbbh

需求2：查询所有流量明细及所有设备月流量总额

WITH sum_num_tbl AS (
SELECT
substr(pass_dt,1,7) as mon
,sbbh
,sum(num) as sum_num
FROM veh_pass
group by substr(pass_dt,1,7),sbbh
)
SELECT
T2.*
,T1.sum_num
FROM sum_num_tbl T1 JOIN veh_pass T2 ON T1.mon =  substr(T2.pass_dt,1,7) AND T1.sbbh = T2.sbbh


SELECT
*
,sum(num) OVER(PARTITION BY substr(pass_dt,1,7),sbbh)
FROM veh_pass

需求3：按设备编号、日期顺序展示明细

1）从第一天开始到当前天数 对流量进行累加
/*
    1.对于多行数据处理，并且有明细信息展示，所以需要使用窗口函数
    2.对于窗口使用OVER,并且OVER中需要对数据进行限制
        2.1 不同设备需要分开计算  -> PARTITION 
        2.2 由于每一行代表一天数据，从第一天到当前天就是第一行到当前行 -> ROWS
    3.需要按照设备、日期顺序进行排序  -> ORDER BY 
*/

SELECT
*
,SUM(num) OVER(PARTITION BY sbbh ORDER BY sbbh,pass_dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
FROM veh_pass



SELECT
*
,SUM(num) OVER(PARTITION BY sbbh ORDER BY sbbh,pass_dt) 
FROM veh_pass


SELECT
*
,SUM(num) OVER(PARTITION BY sbbh) 
FROM veh_pass

SELECT
*
,count(*) OVER(PARTITION BY sbbh ORDER BY sbbh,pass_dt) 
FROM veh_pass

对于统计函数，如果在OVER窗口中，对窗口数据进行排序使用ORDER BY，那么会产生累计求和或其他统计结果

对于count(*)和 OVER窗口中使用ORDER BY 那么会产生和ROW_NUMBER() 一样的效果


2）昨天与当前天流量累加
/*
  分析：
    昨天是当前行的上一行  -> rows 
    昨天是当前行日期减去1天 -> 使用 range 但是对于日期数据range范围取不到 
    451000000000071117	2023-06-15	
    451000000000071117	2023-06-16
    那么如果使用 range 就需要将所有的日期转换成一个数值 10，如何操作？

    由于日期是某个开始日期不断累加天数得到，于是日期可以转换成对于一个开始日期间隔多少天数 
*/

SELECT
T1.*
,SUM(num) OVER(PARTITION BY sbbh ORDER BY flag_day RANGE BETWEEN 1 PRECEDING AND CURRENT ROW) AS sum_num
FROM (
SELECT
*
,datediff(pass_dt,'2023-01-01') as flag_day
FROM veh_pass
) T1

3）当前天数的前一天与后一天流量累加
SELECT
T1.*
,SUM(num) OVER(PARTITION BY sbbh ORDER BY flag_day RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) - T1.num AS sum_num
FROM (
SELECT
*
,datediff(pass_dt,'2023-01-01') as flag_day
FROM veh_pass
) T1

4）当前天与下一天的累加和
SELECT
T1.*
,SUM(num) OVER(PARTITION BY sbbh ORDER BY flag_day RANGE BETWEEN CURRENT ROW AND 1 FOLLOWING) AS sum_num
FROM (
SELECT
*
,datediff(pass_dt,'2023-01-01') as flag_day
FROM veh_pass
) T1

5）当前天数与之后所有天流量累加和
SELECT
*
,SUM(num) OVER(PARTITION BY sbbh ORDER BY sbbh,pass_dt ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) 
FROM veh_pass

需求4：查询每个设备编号上次有数据日期和下一次有数据日期

/*
分析：
    1.每个设备编号 需要按设备进行区分
    2.上次有数据日期和下一次有数据日期是指当前行的上一行和下一行 
*/

451000000000071117	2023-06-16	 
451000000000071117	2023-07-14	 <- 2023-06-16  2023-07-16 
451000000000071117	2023-07-16 

SELECT
*
,LAG(pass_dt,1) OVER(PARTITION BY sbbh ORDER BY pass_dt) as lag_dt
,LEAD(pass_dt,1) OVER(PARTITION BY sbbh ORDER BY pass_dt) as lead_dt
FROM veh_pass

连续交易问题

在电商、物流和银行 可能经常会遇到这样的需求：
** 1.统计用户连续交易的总额、**

连续交易：
对于某个用户，在每一天都发生交易，并且日期是连续的

/*
  分析：
    1.由于连续交易需要对每天数据进行判断，那么此时需要将每一天数据进行汇总 
    2.当得到用户每一天交易额，那么就可以进行判断天数是否连续  
    3.
*/

-- 每个用户在每一天的交易额
SELECT
id
,datestr
,sum(amount) as day_amount
FROM deal_tb
GROUP BY id,datestr

-- 如果将下一行数据提取到上一行，那么可以通过日期相差天数 知道数据是否为连续
-- 但是对于数据进行统计分析时，对连续天数进行分组，无法进行确定 
1	2019-02-08	12461.55 2019-02-09  1
1	2019-02-09	1052.99 2019-02-10  1
1	2019-02-10	85.69  2019-02-12  2
1	2019-02-12	769.85 2019-02-13 1
1	2019-02-13	943.86 2019-02-14 1
1	2019-02-14	538.42 2019-02-15 1
1	2019-02-15	369.76 
1	2019-02-16	369.76
1	2019-02-18	795.15
1	2019-02-19	715.65

连续在数据上如何体现的？
1,2,3,4,5,6 -> 是连续的数值
2019-02-13 2019-02-14 2019-02-15 2019-02-16 -> 是连续的数值
对于日期也可以转换成某个开始日期加上 相隔的天数
2019-02-12 开始日期
1,2,3,4

不连续：

2019-02-13 2019-02-14 2019-02-16 2019-02-17 2019-02-18
2019-02-12 开始日期
1,2,4,5,6
如何知道其是不连续的，在数据上如何体现
拿连续的数值 1,2,3,4,5,6 和 1,2,4,5,6 进行按位置比较
1,2,3,4,5
1,2,4,5,6
0,0,1,1,1
通过将要判断的数据和连续的数据进行相减比较，就可以知道那些数据是连续的，那些是不连续的

/*
  分析：
    1.由于连续交易需要对每天数据进行判断，那么此时需要将每一天数据进行汇总 
    2.当得到用户每一天交易额，那么就可以进行判断天数是否连续  
    3.将日期减去某个开始日期，得到相隔的天数 datediff
    4.再按位置获取一个连续的数值数据 1,2,3,4...  row_number
    5.再拿连续日期和天数进行相减 比较其差别 
    
*/
CREATE TABLE amount_continue_tbl (
  id  STRING 
,datestr STRING
,day_amount double
,diff_day int
,rn int
,continue_flag int
)



WITH sum_tbl AS (
SELECT
id
,datestr
,sum(amount) as day_amount
FROM deal_tb
GROUP BY id,datestr
)
INSERT INTO TABLE amount_continue_tbl 
SELECT
T1.*
,diff_day - rn continue_flag
FROM (
SELECT
*
,datediff(datestr,'2019-02-07') diff_day
,row_number() over(PARTITION BY id ORDER BY datestr) as rn
FROM sum_tbl
) T1

-- continue_flag 如果相同，表示为连续的 

SELECT
continue_flag
,sum(day_amount)
FROM amount_continue_tbl
GROUP BY continue_flag

对于WITH AS 不能使用 CREATE TABLE 将查询结果创建成一张表


2.连续交易天数、
3.连续交易开始和结束时间、

SELECT
id
,min(datestr) as start_date
,max(datestr) as end_date
,sum(day_amount) as sum_day_amount
,count(*) as continue_time
FROM amount_continue_tbl
GROUP BY id,continue_flag

4.无交易间隔天数等

1	2019-02-08	2019-02-10	13600.23	3  2019-02-12 
1	2019-02-12	2019-02-16	2991.6500000000005	5

SELECT
T1.*
,LEAD(start_date,1,current_date) OVER(PARTITION BY id ORDER BY id,start_date) next_day
,datediff(LEAD(start_date,1,current_date) OVER(PARTITION BY id ORDER BY id,start_date),T1.end_date)
FROM (
SELECT
id
,min(datestr) as start_date
,max(datestr) as end_date
,sum(day_amount) as sum_day_amount
,count(*) as continue_time
FROM amount_continue_tbl
GROUP BY id,continue_flag
) T1

设备异常

1.无数据设备统计

**设备是指道路上拍摄违法数据的摄像头， 摄像头分布较多，出现问题的原因有很多，现场人员如果要进行排查，所花费时间较多，效果不明显 需求要求根据数据大致可以判断出那些摄像头会存在由一些问题，当数据在相隔一段时间内没有数据，那么可以认为该设备在某段时间有问题 **

**1.对于每个设备情况不太一样，比如有些设备两天没有数据是正常的，但是对于其他设备两天没有数据是不正常的 对于每个设备需要分类型判断 **
**2.对于每个设备需要计算其吗没有数据的时间差，根据时间差长短判断是否存在问题 **


create table base_vio_sbbh(
sbbh STRING COMMENT "设备编号",
wfsj STRING COMMENT "违法时间"
);

insert into table base_vio_sbbh (sbbh,wfsj) values
('451000000000071113','2020-04-13'),  
('451000000000071113','2020-04-22'),  
('451000000000071113','2020-05-13'),  
('451000000000071113','2020-05-18'),  
('451000000000071113','2020-06-14'),  
('451000000000071113','2020-06-15'),  
('451000000000071113','2020-06-16'),  
('451000000000071113','2020-06-18'),  
('451000000000071113','2020-06-23'),  
('451000000000071113','2020-06-28'),   
('451000000000071113','2020-06-29'),   
('451000000000071113','2020-07-02'),   
('451000000000071113','2020-07-07'),   
('451000000000071113','2020-07-09'),   
('451000000000071113','2020-07-22'),   
('451000000000071113','2020-07-26'),   
('451000000000071113','2020-07-28'),   
('451000000000071113','2020-07-30'),   
('451000000000071113','2020-08-01'),   
('451000000000071113','2020-08-02'),   
('451000000000071113','2020-08-03'),   
('451000000000071113','2020-08-05'),   
('451000000000071113','2020-08-06'),   
('451000000000071113','2020-08-08'),   
('451000000000071113','2020-08-09'),   
('451000000000071113','2020-08-11'),   
('451000000000071113','2020-08-12'),   
('451000000000071113','2020-08-13'),   
('451000000000071113','2020-08-14'),   
('451000000000071113','2020-08-19'),   
('451000000000071113','2020-08-20'),   
('451000000000071113','2020-08-21'),   
('451000000000071113','2020-08-28'),   
('451000000000071113','2020-09-02'),   
('451000000000071113','2020-09-03'),   
('451000000000071113','2020-09-05'),   
('451000000000071113','2020-09-11'),   
('451000000000071113','2020-09-24'),   
('451000000000071113','2020-09-27'),   
('451000000000071113','2020-09-28'),   
('451000000000071113','2020-09-30'),   
('451000000000071113','2020-10-01'),   
('451000000000071113','2020-10-02'),
('451000000000072222','2020-04-13'),  
('451000000000072222','2020-04-22'),  
('451000000000072222','2020-05-13'),  
('451000000000072222','2020-05-18'),  
('451000000000072222','2020-06-14'),  
('451000000000072222','2020-06-15'),  
('451000000000072222','2020-06-16'),  
('451000000000072222','2020-06-18'),  
('451000000000072222','2020-06-23'),  
('451000000000072222','2020-06-29'),   
('451000000000072222','2020-06-29'),   
('451000000000072222','2020-07-02'),   
('451000000000072222','2020-07-07'),   
('451000000000072222','2020-07-09'),   
('451000000000072222','2020-07-22'),   
('451000000000072222','2020-07-26'),   
('451000000000072222','2020-07-29'),   
('451000000000072222','2020-07-30'),   
('451000000000072222','2020-08-01'),   
('451000000000072222','2020-08-02'),   
('451000000000072222','2020-08-03'),   
('451000000000072222','2020-08-05'),   
('451000000000072222','2020-08-06'),   
('451000000000072222','2020-08-08'),   
('451000000000072222','2020-08-09'),   
('451000000000072222','2020-08-14'),   
('451000000000072222','2020-08-12'),   
('451000000000072222','2020-08-13'),   
('451000000000072222','2020-08-14'),   
('451000000000072222','2020-08-19'),   
('451000000000072222','2020-08-20'),   
('451000000000072222','2020-08-21'),   
('451000000000072222','2020-08-29'),   
('451000000000072222','2020-09-02'),   
('451000000000072222','2020-09-03'),   
('451000000000072222','2020-09-05'),   
('451000000000072222','2020-09-14'),   
('451000000000072222','2020-09-24'),   
('451000000000072222','2020-09-27'),   
('451000000000072222','2020-09-29'),   
('451000000000072222','2020-09-30'),   
('451000000000072222','2020-10-01'),   
('451000000000072222','2020-10-02')

-- 该数据每一条都代表一天是否有违法数据

SELECT
sbbh
,wfsj
,LEAD(wfsj,1,CURRENT_DATE) OVER(PARTITION BY sbbh ORDER BY wfsj) as next_date
,datediff(LEAD(wfsj,1,CURRENT_DATE) OVER(PARTITION BY sbbh ORDER BY wfsj),wfsj) 
FROM base_vio_sbbh

对于计算后的时间差，其间隔的天数时间不确定，无法进行有效界定其时间界限
于是需要对数据按照某种方式对每个设备划定一个时间界限 大于该界限的认为数据是有问题的

可以使用箱线图算法方式解决该问题
**箱线图的作用是可以根据一系列无序且不需要符合正态分布的数据判断其数据是否存在由异常值 **
**箱线图通过上下边界值来过滤异常值，当数据小于下边缘值或大于上边缘值时为异常数据 **
**上下边界值 计算需要通过 上下四分位值进行计算 **
**上下四分位值定义是：将所有的散列数据，依次排序，取 3/4位置的数据为上四分位值 Q3 ，1/4位置的数据为下四份位值 Q1 于是可以得到数据跨度 IQR=(Q3-Q1) **
**下边界值 = Q1-1.5IQR =Q1-1.5(Q3-Q1) (其中1.5为专家值) **
**上边界值 = Q3+1.5IQR =Q1+1.5(Q3-Q1) (其中1.5为专家值) **

现有如下数据：
2,1,3,4,7,8,9,5,10,20,-6
箱线图计算：
1.对数据进行排序
-6,1,2,3,4,5,7,8,9,10,20
中位数： 5
上四分位值：9
下四分位值：2
IQR=(Q3-Q1) = 7
下边界值 = Q1-1.5IQR = 2 - 10.5 = -8.5
**上边界值 = Q3+1.5IQR = **9+10.5 = 19.5
当专家值修改为1时，可以将-6和20全部取出

CREATE TABLE sbbh_wf_sjc AS
SELECT
sbbh
,wfsj
,LEAD(wfsj,1,CURRENT_DATE) OVER(PARTITION BY sbbh ORDER BY wfsj) as next_date
,datediff(LEAD(wfsj,1,CURRENT_DATE) OVER(PARTITION BY sbbh ORDER BY wfsj),wfsj) day_diff
FROM base_vio_sbbh
order by sbbh,day_diff


/*
  分析：
    1.对数据进行排序操作
    2.计算当前每个数据的条数 用于获取上下四分位下标
    3.根据条数据获取 3/4 和 1/4 下标位置
    4.根据位置获取对应的数据 
    5.计算IQR及上下边界值
    6.将异常数据进行过滤 
*/

CREATE TABLE index_tbl AS 
SELECT
sbbh
,count(*) as index_cnt
,floor(count(*)/4) as q1_index
,floor(count(*)/4 * 3) as q3_index
FROM base_vio_sbbh
GROUP BY sbbh

-- 当得到上述的下标位置时，需要根据下标位置获取对应位置的值
451000000000072222	43	10	32

CREATE TABLE sbbh_wf_sjc_pm AS 
SELECT
*
,row_number() over(PARTITION BY sbbh ORDER BY day_diff) as pm
FROM sbbh_wf_sjc 

/*
SELECT
T1.sbbh
,T1.wfsj
,T1.day_diff AS q1_value
FROM sbbh_wf_sjc_pm T1 JOIN index_tbl T2 ON T1.sbbh = T2.sbbh AND T2.q1_index = T1.pm
UNION ALL
SELECT
T1.sbbh
,T1.wfsj
,T1.day_diff AS q3_value
FROM sbbh_wf_sjc_pm T1 JOIN index_tbl T2 ON T1.sbbh = T2.sbbh AND T2.q3_index = T1.pm
-- 该方式拼接数据不太好对 Q3和Q1的值进行计算 
*/
451000000000071113	2020-08-12	1   0
451000000000071113	2020-07-02	0   5

451000000000071113 1 5 4  -5  11

451000000000072222	2020-08-02	1   0
451000000000072222	2020-07-02	0   5

451000000000072222 1 5 4  -5  11

/*
  IQR计算的思路：
     1.按照不同的设备，对Q1的值 和Q3的值分别保存到两列数据中 
       451000000000072222	2020-08-02	1   0
       451000000000072222	2020-07-02	0   5
     2.按照设备 取Q1和Q3列中的最大值 
        451000000000072222 1 5 
     3.计算IQR和上下边界值 
*/
CREATE TABLE sbbh_iqr_boundary AS
SELECT
sbbh
,q3_value - q1_value AS IQR
,q3_value + 1.5 * (q3_value - q1_value) as up_boundary
FROM (
SELECT
sbbh
,max(q1_value) as q1_value
,max(q3_value) as q3_value
FROM (
SELECT
T1.sbbh
,T1.wfsj
,T1.day_diff AS q1_value
, 0 AS q3_value
FROM sbbh_wf_sjc_pm T1 JOIN index_tbl T2 ON T1.sbbh = T2.sbbh AND T2.q1_index = T1.pm
UNION ALL
SELECT
T1.sbbh
,T1.wfsj
,0 AS q1_value
,T1.day_diff AS q3_value
FROM sbbh_wf_sjc_pm T1 JOIN index_tbl T2 ON T1.sbbh = T2.sbbh AND T2.q3_index = T1.pm
) TT1 
GROUP BY sbbh ) TTT1

-- 过滤数据

SELECT
T1.*
,T2.up_boundary
FROM sbbh_wf_sjc T1 JOIN sbbh_iqr_boundary T2 ON T1.sbbh = T2.sbbh AND T1.day_diff > T2.up_boundary

自定义函数

自定义函数分类：
1.UDF函数 传入1行某些列数据 传入1行 1列数据 substring,to_date…
2.UDTF函数 传入1行数据 返回多行多列数据 EXPLODE函数 （拓展，当传入的是Map类型数据，那么返回有两列 key value）
3.UDAF函数 传入多行数据，返回单行数据 max() count() sum()


UDF函数自定义

public class NewUDF extends GenericUDF {
    /**
     *  需求：
     *      对于日期 20240224 -> 2024-02-24
     *       SELECT change_date("20240224") => "2024-02-24"
     */

    /**
     * 注意：当jar包之前被上传过以后，需要对当前hive客户端进行关闭，之后再重启
     * 当函数定义之后，需要按照如下在hive中进行调用
     *      1.对当前项目进行打包 并上传到Linux中
     *      2.对项目jar包，添加到HIVE的 class path中
     *          add jar /root/hive-1.0.jar;
     *      3.将当前的函数注册成HIVE中的可执行函数
     *        -- 创建临时函数：
     *          create temporary function change_date as 'com.shujia.fun.NewUDF';
     *        -- 创建永久函数：
     *          create function my_db.my_lower as 'com.example.hive.udf.Lower';
     *          对于永久函数可以使用drop function 进行删除重新创建
     *
     *      4.使用函数
     *
     *
     */
    SimpleDateFormat fromDate = null;
    SimpleDateFormat toDate = null;

    /**
     *
     * 用于实例化当前的自定义函数，在方法中可以判断传入数据的参数格式 比如
     *    SELECT change_date("20240224","adsfas")
     *   可以对当前参数个数进行提示
     * @param objectInspectors 当前函数change_date传入的数据格式对象
     * @return  ObjectInspector 表示当前change_date返回值的数据类型的包装类
     * @throws UDFArgumentException
     */
    @Override
    public ObjectInspector initialize(ObjectInspector[] objectInspectors) throws UDFArgumentException {
        // 每个ObjectInspector对象代表change_date传入的一个参数
        if(objectInspectors.length != 1){
            throw new UDFArgumentLengthException("当前函数只支持传入一个参数");
        }

        fromDate = new SimpleDateFormat("yyyyMMdd");
        toDate = new SimpleDateFormat("yyyy-MM-dd");

        // 该方法的返回值类型是 change_date 计算后返回的数据类型
        //   由于 change_date返回值类型为String字符串,那么可以使用 javaStringObjectInspector 来限制其类型
        return PrimitiveObjectInspectorFactory.javaStringObjectInspector;
    }

    @Override
    public Object evaluate(DeferredObject[] deferredObjects) throws HiveException {
        // DeferredObject 将该类中保存的数据进行取出，再进行数据的计算
        // 取出一个值
        DeferredObject deferredObject = deferredObjects[0];
        // 通过get方法可以获取到数据的对象 并且需要将对象进行强制类型转换
        //   由于HIVE使用的是Hadoop,对于其中的数据可以转换成Hadoop中的数据类型
        //  text => "20240224"
        Text text = (Text) deferredObject.get();

        String format = null;
        try {
            Date date = fromDate.parse(text.toString());
            format = toDate.format(date);
        } catch (ParseException e) {
            throw new RuntimeException(e);
        }
        return format;
    }

    /**
     * 用于做字符串展示
     * @param strings
     * @return
     */
    @Override
    public String getDisplayString(String[] strings) {
        return "change_date";
    }
}

UDTF函数自定义

public class MyUDTF extends GenericUDTF {
    /**
     * 实现功能：
     *      当传入一个 字符串 以及分隔符 那么可以返回 对应字符串切分后 将每个元素转换成多行数据
     *      SELECT split_explode("hello,udtf,udf,udaf",",")
     *      计算结果：
     *      hello
     *      udtf
     *      udf
     *      udaf
     *
     *  add jar /root/hive-1.0.jar;
     *  create temporary function split_explode as 'com.shujia.fun.MyUDTF';
     *
     */


    /**
     *  对当前函数在执行时，可以进行一次实例化操作
     *
     * @param argOIs 表示为split_explode中每个参数的包装类,每个参数的包装类为 StructField
     * @return  StructObjectInspector 该对象需要包含当前UDTF函数计算后 多列的列名称 以及 列的类型
     * @throws UDFArgumentException
     */
    @Override
    public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
        List<? extends StructField> allStructFieldRefs = argOIs.getAllStructFieldRefs();
        if (allStructFieldRefs.size() !=2){
            throw new UDFArgumentLengthException("当前UDTF函数的参数个数为2");
        }

        // 定义两个ArrayList fieldNames 用于存储当前返回值列的名称
        ArrayList<String> fieldNames = new ArrayList();
        // fieldOIs 用于存储当前返回值各列的列数据类型
        ArrayList<ObjectInspector> fieldOIs = new ArrayList();
        fieldNames.add("words");
        // 使用Java中的String作为返回值的类型
        fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);

        // 将列名称以及列类型返回即可
        return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames, fieldOIs);
    }

    /**
     * SELECT split_explode("hello,udtf,udf,udaf",",")
     * 当前函数返回值是 void 所以不能直接使用return返回
     * @param objects 对应的是参数的所有列  每个Object对应一个参数
     * @throws HiveException
     */
    @Override
    public void process(Object[] objects) throws HiveException {
        String oneLine = ((Text) objects[0]).toString(); // 要处理的数据
        String regex = ((Text) objects[1]).toString();  // 分隔符
        String[] words = oneLine.split(regex);
        for (String word : words) {
            // 使用父类中继承的forward方法可以对数据进行返回
            // forward要求传入的类型为一个数组或List集合
            String[] returnColum = new String[1];
            returnColum[0] = word;
            forward(returnColum);
        }

    }

    @Override
    public void close() throws HiveException {
    }
}

UDAF函数

**自定义聚合函数，该聚合函数代码比较复杂，可以自行了解，开发时，可以使用GROUP BY + collect_list + 自定义UDF函数 进行替代 **

自定义函数

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>com.shujia</groupId>
        <artifactId>bigdata28</artifactId>
        <version>1.0</version>
    </parent>

    <artifactId>hive</artifactId>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <!-- https://mvnrepository.com/artifact/org.apache.hive/hive-exec -->
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-exec</artifactId>
            <version>3.1.2</version>
        </dependency>

        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-client</artifactId>
            <version>3.1.3</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.alibaba.fastjson2/fastjson2 -->
        <dependency>
            <groupId>com.alibaba.fastjson2</groupId>
            <artifactId>fastjson2</artifactId>
            <version>2.0.42</version>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
            <plugin>
                <artifactId>maven-assembly-plugin</artifactId>
                <configuration>
                    <descriptorRefs>
                        <descriptorRef>jar-with-dependencies</descriptorRef>
                    </descriptorRefs>
                </configuration>
                <executions>
                    <execution>
                        <id>make-assembly</id>
                        <phase>package</phase>
                        <goals>
                            <goal>single</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

</project>

介绍

Hive自定义函数包括三种UDF、UDAF、UDTF

UDF(User-Defined-Function) 一进一出

UDAF(User- Defined Aggregation Funcation) 聚集函数，多进一出。Count/max/min

UDTF(User-Defined Table-Generating Functions) 一进多出，如lateral view explore)

使用方式 ：在HIVE会话中add 自定义函数的jar文件，然后创建function继而使用函数

1、编写自定义函数

2、打包上传到集群机器中

3、进入hive客户端，添加jar包：hive> add jar /root/hive_udf.jar

4、创建临时函数：hive> create temporary function getLen as ‘com.raphael.len.GetLength’;

5、销毁临时函数：hive> DROP TEMPORARY FUNCTION getLen;

hive自定义函数中有几类数据类型：

PrimitiveObjectInspector 常用（基本数据类）：

PrimitiveObjectInspector.PrimitiveCategory.STRING

ListObjectInspector

StructObjectInspector

MapObjectInspector

等类型

三种自定义函数都会首先进行参数个数和参数类型检查

参数类型检查（是那一种大类型（primitive…）,然后具体是什么类型）

注意：在所有方法有返回时要注意返回的类型，最重要的是最后返回出去的结果，一般结果的数据类型会在初始化时就定义了，那么在最后返回结果是应该要转化成那种类型

初始化时init:

PrimitiveObjectInspector inputOI;

inputOI = (PrimitiveObjectInspector) parameters[0];

都会定义这种设置输入数据的 ObjectInspector（类型吧）

有多个参数就会设置多个。

在真正处理数据需要获取数据：

long distinctId = PrimitiveObjectInspectorUtils.getLong(objects[0], distinctIdOI);

一般都这样获取，objects是方法的参数，从外界传递进来的，包含很多值，获取第几个值，同时类型是什么（distinctIdOI）

UDF

hive的udf有两种实现方式或者实现的API，一种是udf比较简单，一种是GenericUDF比较复杂。

如果所操作的数据类型都是基础数据类型，如（Hadoop&Hive 基本writable类型，如Text,IntWritable,LongWriable,DoubleWritable等等）。那么简单的org.apache.hadoop.hive.ql.exec.UDF就可以做到。

如果所操作的数据类型是内嵌数据结构，如Map，List和Set，那么要采用org.apache.hadoop.hive.ql.udf.generic.GenericUDF

继承UDF实现

需要继承org.apache.hadoop.hive.ql.UDF，或者

org.apache.hadoop.hive.ql.udf.generic.GenericUDF，前者比较简单，只需要实现evaluate函数，evaluate函数支持重载。

package com.shujia.fun;

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

public class MyUDF extends UDF {
    /**
     *  功能：
     *      1.定义一个函数，在SELECT时，传入一个英文字符串 将首字母大写，其他变为小写
     *         SELECT change_str("helLo")  => Hello
     */

    public String evaluate(String str){
        String lowerCase = str.toLowerCase(); // 将所有的字母都变成小写
        String startChar = lowerCase.substring(0, 1).toUpperCase();
        return startChar + lowerCase.substring(1);
    }

    /**
     * 当函数定义之后，需要按照如下在hive中进行调用
     *      1.对当前项目进行打包 并上传到Linux中
     *      2.对项目jar包，添加到HIVE的 class path中
     *          add jar /root/hive-1.0.jar;
     *      3.将当前的函数注册成HIVE中的可执行函数
     *        -- 创建临时函数：
     *          create temporary function my_lower as 'com.example.hive.udf.Lower';
     *          create temporary function change_str as 'com.shujia.fun.MyUDF';
     *      4.使用函数
     */

}

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

继承GenericUDF实现

继承org.apache.hadoop.hive.ql.udf.generic.GenericUDF需要实现三个方法：

1）initialize：只调用一次，在任何evaluate()调用之前可以接收到一个可以表示函数输入参数类型的object inspectors数组。initalize用来验证该函数是否接收正确的参数类型和参数个数，最后提供最后结果对应的数据类型。

2）evaluate：真正的逻辑，读取输入数据，处理数据，返回结果。

3）getDisplayString：返回描述该方法的字符串，没有太多作用。

package com.shujia.fun;

import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentLengthException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.PrimitiveObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
import org.apache.hadoop.io.Text;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

public class NewUDF extends GenericUDF {
    /**
     *  需求：
     *      对于日期 20240224 -> 2024-02-24
     *       SELECT change_date("20240224") => "2024-02-24"
     */

    /**
     * 注意：当jar包之前被上传过以后，需要对当前hive客户端进行关闭，之后再重启
     * 当函数定义之后，需要按照如下在hive中进行调用
     *      1.对当前项目进行打包 并上传到Linux中
     *      2.对项目jar包，添加到HIVE的 class path中
     *          add jar /root/hive-1.0.jar;
     *      3.将当前的函数注册成HIVE中的可执行函数
     *        -- 创建临时函数：
     *          create temporary function change_date as 'com.shujia.fun.NewUDF';
     *        -- 创建永久函数：
     *          create function my_db.my_lower as 'com.example.hive.udf.Lower';
     *          对于永久函数可以使用drop function 进行删除重新创建
     *
     *      4.使用函数
     *
     *
     */
    SimpleDateFormat fromDate = null;
    SimpleDateFormat toDate = null;

    /**
     *
     * 用于实例化当前的自定义函数，在方法中可以判断传入数据的参数格式 比如
     *    SELECT change_date("20240224","adsfas")
     *   可以对当前参数个数进行提示
     * @param objectInspectors 当前函数change_date传入的数据格式对象
     * @return  ObjectInspector 表示当前change_date返回值的数据类型的包装类
     * @throws UDFArgumentException
     */
    @Override
    public ObjectInspector initialize(ObjectInspector[] objectInspectors) throws UDFArgumentException {
        // 每个ObjectInspector对象代表change_date传入的一个参数
        if(objectInspectors.length != 1){
            throw new UDFArgumentLengthException("当前函数只支持传入一个参数");
        }

        fromDate = new SimpleDateFormat("yyyyMMdd");
        toDate = new SimpleDateFormat("yyyy-MM-dd");

        // 该方法的返回值类型是 change_date 计算后返回的数据类型
        //   由于 change_date返回值类型为String字符串,那么可以使用 javaStringObjectInspector 来限制其类型
        return PrimitiveObjectInspectorFactory.javaStringObjectInspector;
    }

    @Override
    public Object evaluate(DeferredObject[] deferredObjects) throws HiveException {
        // DeferredObject 将该类中保存的数据进行取出，再进行数据的计算
        // 取出一个值
        DeferredObject deferredObject = deferredObjects[0];
        // 通过get方法可以获取到数据的对象 并且需要将对象进行强制类型转换
        //   由于HIVE使用的是Hadoop,对于其中的数据可以转换成Hadoop中的数据类型
        //  text => "20240224"
        Text text = (Text) deferredObject.get();

        String format = null;
        try {
            Date date = fromDate.parse(text.toString());
            format = toDate.format(date);
        } catch (ParseException e) {
            throw new RuntimeException(e);
        }
        return format;
    }

    /**
     * 用于做字符串展示
     * @param strings
     * @return
     */
    @Override
    public String getDisplayString(String[] strings) {
        return "change_date";
    }
}

UDTF

udtf用来解决输入一行输出多行(On-to-many maping) 的需求，需要继承org.apache.hadoop.hive.ql.udf.generic.GenericUDTF来实现三个方法。

1、initialize：返回UDTF的返回行的信息（返回个数，类型）。

2、process：真正的处理过程在process函数中，在process中，每一次forward()调用产生一行；如果产生多列可以将多个列的值放在一个数组中，然后将该数组传入到forward()函数。

3、close：对需要清理的方法进行清理。

forward()传入的就是最后的结果，里面一般是数组，数组有多少个元素就代码最后一行输出的结果有多少列

package com.shujia.fun;

import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentLengthException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
import org.apache.hadoop.hive.serde2.objectinspector.*;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
import org.apache.hadoop.io.Text;

import java.util.ArrayList;
import java.util.List;

public class MyUDTF extends GenericUDTF {
    /**
     * 实现功能：
     *      当传入一个 字符串 以及分隔符 那么可以返回 对应字符串切分后 将每个元素转换成多行数据
     *      SELECT split_explode("hello,udtf,udf,udaf",",")
     *      计算结果：
     *      hello
     *      udtf
     *      udf
     *      udaf
     *
     *  add jar /root/hive-1.0.jar;
     *  create temporary function split_explode as 'com.shujia.fun.MyUDTF';
     *
     */


    /**
     *  对当前函数在执行时，可以进行一次实例化操作
     *
     * @param argOIs 表示为split_explode中每个参数的包装类,每个参数的包装类为 StructField
     * @return  StructObjectInspector 该对象需要包含当前UDTF函数计算后 多列的列名称 以及 列的类型
     * @throws UDFArgumentException
     */
    @Override
    public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
        List<? extends StructField> allStructFieldRefs = argOIs.getAllStructFieldRefs();
        if (allStructFieldRefs.size() !=2){
            throw new UDFArgumentLengthException("当前UDTF函数的参数个数为2");
        }

        // 定义两个ArrayList fieldNames 用于存储当前返回值列的名称
        ArrayList<String> fieldNames = new ArrayList();
        // fieldOIs 用于存储当前返回值各列的列数据类型
        ArrayList<ObjectInspector> fieldOIs = new ArrayList();
        fieldNames.add("words");
        // 使用Java中的String作为返回值的类型
        fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);

        // 将列名称以及列类型返回即可
        return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames, fieldOIs);
    }

    /**
     * SELECT split_explode("hello,udtf,udf,udaf",",")
     * 当前函数返回值是 void 所以不能直接使用return返回
     * @param objects 对应的是参数的所有列  每个Object对应一个参数
     * @throws HiveException
     */
    @Override
    public void process(Object[] objects) throws HiveException {
        String oneLine = ((Text) objects[0]).toString(); // 要处理的数据
        String regex = ((Text) objects[1]).toString();  // 分隔符
        String[] words = oneLine.split(regex);
        for (String word : words) {
            // 使用父类中继承的forward方法可以对数据进行返回
            // forward要求传入的类型为一个数组或List集合
            String[] returnColum = new String[1];
            returnColum[0] = word;
            forward(returnColum);
        }

    }

    @Override
    public void close() throws HiveException {
//        System.out.println("不需要关闭任何...");
    }
}


package com.shujia.fun;

import com.alibaba.fastjson2.JSON;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentLengthException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
import org.apache.hadoop.hive.serde2.lazy.LazyString;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;
import org.apache.hadoop.hive.serde2.objectinspector.StructField;
import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
import org.apache.hadoop.io.Text;

import java.util.ArrayList;
import java.util.List;

public class ParseMovieJson extends GenericUDTF {
    /*
        建表语句：
CREATE TABLE movie_json(
line STRING
);
load data local inpath '/root/UDTF.txt' into table movie_json;

    1.打包项目
    2. add jar /root/hive-1.0.jar;
    3. create temporary function parse_movie_json as 'com.shujia.fun.ParseMovieJson';

    HIVE中自带的JSON处理函数：
    get_json_object
        $ 表示根节点
        . 表示其子节点
        key 表示JSON中的Key 可以直接添加 例如$.movie
        [] 表示取出JSONArray中对应下标的数据

    select get_json_object(line,'$.movie')  from movie_json;

     */



    @Override
    public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
        List<? extends StructField> allStructFieldRefs = argOIs.getAllStructFieldRefs();
        if (allStructFieldRefs.size() !=1){
            throw new UDFArgumentLengthException("当前UDTF函数的参数个数为1个 只能传入movie的数据 ");
        }

        // 定义两个ArrayList fieldNames 用于存储当前返回值列的名称
        ArrayList<String> fieldNames = new ArrayList();
        // fieldOIs 用于存储当前返回值各列的列数据类型
        ArrayList<ObjectInspector> fieldOIs = new ArrayList();
        fieldNames.add("movie_name");
        fieldNames.add("movie_type");
        // 使用Java中的String作为返回值的类型
        fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
        fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);

        // 将列名称以及列类型返回即可
        return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames, fieldOIs);
    }

    /*
        Caused by: java.lang.ClassNotFoundException: com.alibaba.fastjson2.JSON
        由于当前类不存在导致任务执行失败

        当maven中添加了fastjson2的依赖，在本地可以执行，但是当项目打包时 使用package 那么默认是
            对当前项目进行打包，而项目的依赖没有添加的打包结果的jar包中
            于是需要添加插件： 可以对maven依赖jar包进行添加到项目的jar包中


     */
    @Override
    public void process(Object[] objects) throws HiveException {
        // {"movie": [{"movie_name": "肖申克的救赎", "MovieType": "犯罪" }, {"movie_name": "肖申克的救赎", "MovieType": "剧情" }]}
//        String oneLineJson = ((Text) objects[0]).toString();
//        LazyString object = (LazyString) objects[0];
        String oneLineJson = ((LazyString) objects[0]).toString();
        JSONObject jsonObject = JSON.parseObject(oneLineJson);
        JSONArray jsonArray = jsonObject.getJSONArray("movie");
        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject oneJson = jsonArray.getJSONObject(i);
            // {"movie_name": "肖申克的救赎", "MovieType": "犯罪" }
            String movieName = oneJson.getString("movie_name");
            String movieType = oneJson.getString("MovieType");

            // 将movieName  movieType 作为一行数据进行返回
            // forward每执行一次都会返回一行数据
            //
            String[] oneLine = new String[2];
            oneLine[0] = movieName;
            oneLine[1] = movieType;
            forward(oneLine);
        }
    }

    @Override
    public void close() throws HiveException {

    }
}




需要一并打包faston-2即分析json的jar包，因此依赖pom.xml需要加上一个插件，即上文的build范围的内容

UDAF

多行进一行出，如sum()、min()，用在group by时。开发通用UDAF有两个步骤

1、第一个是编写resolver类（继承AbstractGenericUDAFResolver），

2、第二个是编写evaluator类（继承GenericUDAFEvaluator）在resolver类内部。

resolver负责类型检查，操作符重载。evaluator真正实现UDAF的逻辑。通常来说，顶层UDAF类继承org.apache.hadoop.hive.ql.udf.GenericUDAFResolver，里面编写嵌套类evaluator （继承GenericUDAFEvaluator）实现UDAF的逻辑。

实现evaluator所有evaluators必须继承抽象类

org.apache.hadoop.hive.ql.udf.generic.GenericUDAFEvaluator。子类必须实现它的一些抽象方法，实现UDAF的逻辑。

同时需要一个继承了AggregationBuffer的类，来存储中间过程中记录的数据

Mode

GenericUDAFEvaluator有一个嵌套类Mode,这个类很重要，它表示了udaf在mapreduce的各个阶段，理解Mode的含义，就可以理解了hive的UDAF的运行流程。

PARTIAL1, //从原始数据到部分聚合数据的过程（map阶段），将调用iterate()和terminatePartial()方法。

PARTIAL2, //从部分聚合数据到部分聚合数据的过程（map端的combiner阶段），将调用merge() 和terminatePartial()方法。

FINAL, //从部分聚合数据到全部聚合的过程（reduce阶段），将调用merge()和 terminate()方法。

COMPLETE //从原始数据直接到全部聚合的过程（表示只有map，没有reduce，map端直接出结果），将调用merge() 和 terminate()方法。


public static enum Mode {

    /**

     * PARTIAL1: 这个是mapreduce的map阶段:从原始数据到部分数据聚合

     * 将会调用iterate()和terminatePartial()

     */

    PARTIAL1,

        /**

     * PARTIAL2: 这个是mapreduce的map端的Combiner阶段，负责在map端合并map的数据::从部分数据聚合到部分数据聚合:

     * 将会调用merge() 和 terminatePartial()

     */

    PARTIAL2,

        /**

     * FINAL: mapreduce的reduce阶段:从部分数据的聚合到完全聚合

     * 将会调用merge()和terminate()

     */

    FINAL,

        /**

     * COMPLETE: 如果出现了这个阶段，表示mapreduce只有map，没有reduce，所以map端就直接出结果了:从原始数据直接到完全聚合

      * 将会调用 iterate()和terminate()

     */

    COMPLETE

  };

一般情况下，完整的UDAF逻辑是一个mapreduce过程，如果有mapper和reducer，就会经历PARTIAL1(mapper)，FINAL(reducer)，如果还有combiner，那就会经历PARTIAL1(mapper)，PARTIAL2(combiner)，FINAL(reducer)。

而有一些情况下的mapreduce，只有mapper，而没有reducer，所以就会只有COMPLETE阶段，这个阶段直接输入原始数据，出结果
实现代码分析

UDAF的实现代码主干如下：

//最外层继承AbstractGenericUDAFResolver

public class GenericUDAFSum extends AbstractGenericUDAFResolver {

  static final Log LOG = LogFactory.getLog(GenericUDAFSum.class.getName());

//实现getEvaluator方法

  @Override

  public GenericUDAFEvaluator getEvaluator(TypeInfo[] parameters)

    throws SemanticException {

    // Type-checking goes here!

    return new GenericUDAFSumLong();

  }

//编写一个类，继承GenericUDAFEvaluator，实现所有方法

  public static class GenericUDAFSumLong extends GenericUDAFEvaluator {

    // UDAF logic goes here!

  }

}

说明：

1、getEvaluator：继承AbstractGenericUDAFResolver 所需要实现的方法，也只需要重写这一个方法，作用是检查参数个数，参数类型等，然后返回GenericUDAFEvaluator对象。

2、GenericUDAFEvaluator：getEvaluator最后返回类型就是这个类，真正返回（return）的是继承实现GenericUDAFEvaluator的类。

例如GenericUDAFSumLong 继承GenericUDAFEvaluator需要实现如下方法：

1、init（初始化）：确定返回类型并返回UDAF的返回类型，

2、getNewAggregationBuffer：创建新的聚合计算的需要的内存，用来存储mapper,combiner,reducer运算过程中的相加总和，获取聚合中间结果缓存

3、reset：重置中间结果缓存

4、iterate： 迭代每一行的数据，等同于 Map 阶段。传进来的行数据由 HiveSQL 决定，计算的中间结果缓存到 AggregationBuffer ，@param aggregationBuffer 中间结果缓存，@param objects 行中每一列的数据

5、terminatePartial（终止部分）：对部分中间结果数据进行合并，等同于 Map 阶段的 combine，返回mapper结果，combine后的结果

6、merge（合并）：在最终进行 terminate() 前对所有传入的中间结果进行合并，等同于 Reduce 阶段的 merge。 各个 Map 传来的中间结果 partial 合并到 aggregationBuffer

7、terminate（终止）：计算合并后的数据得出最终结果,等同于 Reduce 阶段的逻辑。reducer返回结果，或者是只有mapper，没有reducer时，在mapper端返回结果

代码示例
public class UDAFDemo extends  AbstractGenericUDAFResolver {

    static final Log LOG = LogFactory.getLog(GenericUDAFSum.class.getName());

    public GenericUDAFEvaluator getEvaluator(TypeInfo[] parameters)

        throws SemanticException {

        // 检查参数个数

        if (parameters.length != 1) {

            throw new UDFArgumentTypeException(parameters.length - 1,

                    "Exactly one argument is expected.");

        }

        // 检查参数类型

        if (parameters[0].getCategory() != ObjectInspector.Category.PRIMITIVE) {

            throw new UDFArgumentTypeException(0,

                    "Only primitive type arguments are accepted but "

                            + parameters[0].getTypeName() + " is passed.");

        }

//        // 检查参数类型

//        if (parameters[0].getCategory() != ObjectInspector.Category.PRIMITIVE) {

//            throw new UDFArgumentTypeException(0, "Only primitive type argument are accepted but "

//                    + parameters[0].getTypeName() + " was passed as parameter 1");

//        }

//        if (((PrimitiveTypeInfo) parameters[0]).getPrimitiveCategory() != PrimitiveObjectInspector.PrimitiveCategory.LONG) {

//            throw new UDFArgumentTypeException(0, "Only Long Type type argument are accepted but "

//                    + parameters[0].getTypeName() + " was passed as parameter 1");

//        }

        // 检查参数类型

        switch (((PrimitiveTypeInfo) parameters[0]).getPrimitiveCategory()) {

            case BYTE:

            case SHORT:

            case INT:

            case LONG:

            case TIMESTAMP:

                return new GenericUDAFSumLong();

            case FLOAT:

            case DOUBLE:

//                case STRING:

//                    return new GenericUDAFSumDouble();

            case BOOLEAN:

            default:

                throw new UDFArgumentTypeException(0,

                        "Only numeric or string type arguments are accepted but "

                                + parameters[0].getTypeName() + " is passed.");

        }

    }

    public static class GenericUDAFSumLong extends GenericUDAFEvaluator {

        private PrimitiveObjectInspector inputOI;

        private LongWritable result;

        /** 存储sum的值的类 */

        static class SumLongAgg implements AggregationBuffer {

            boolean empty;

            long sum;

        }

        //这个方法返回了UDAF的返回类型，这里确定了sum自定义函数的返回类型是Long类型

        public ObjectInspector init(Mode m, ObjectInspector[] parameters) throws HiveException {

            assert (parameters.length == 1);

            super.init(m, parameters);

            result = new LongWritable(0);

            inputOI = (PrimitiveObjectInspector) parameters[0];

            return PrimitiveObjectInspectorFactory.writableLongObjectInspector;

        }

        //创建新的聚合计算的需要的内存，用来存储mapper,combiner,reducer运算过程中的相加总和

        public AggregationBuffer getNewAggregationBuffer() throws HiveException {

            SumLongAgg result = new SumLongAgg();

            reset(result);

            return result;

        }

        //mapreduce支持mapper和reducer的重用，所以为了兼容，也需要做内存的重用。

        public void reset(AggregationBuffer aggregationBuffer) throws HiveException {

            SumLongAgg myagg = (SumLongAgg) aggregationBuffer;

            myagg.empty = true;

            myagg.sum = 0;

        }

        private boolean warned = false;

        //map阶段调用，只要把保存当前和的对象agg，再加上输入的参数，就可以了。

        public void iterate(AggregationBuffer aggregationBuffer, Object[] objects) throws HiveException {

            assert (objects.length == 1);

            try {

                merge(aggregationBuffer, objects[0]);

            }catch (NumberFormatException e) {

                if (!warned) {

                    warned = true;

                    LOG.warn(getClass().getSimpleName() + " "

                            + StringUtils.stringifyException(e));

                }

            }

        }

        //mapper结束要返回的结果，还有combiner结束返回的结果

        public Object terminatePartial(AggregationBuffer aggregationBuffer) throws HiveException {

            return terminate(aggregationBuffer);

        }

        //combiner合并map返回的结果，还有reducer合并mapper或combiner返回的结果。

        public void merge(AggregationBuffer aggregationBuffer, Object o) throws HiveException {

            if (o != null) {

                SumLongAgg agg = (SumLongAgg) aggregationBuffer;

                agg.sum += PrimitiveObjectInspectorUtils.getLong(o, inputOI);

                agg.empty = false;

            }

        }

        //reducer返回结果，或者是只有mapper，没有reducer时，在mapper端返回结果。

        public Object terminate(AggregationBuffer aggregationBuffer) throws HiveException {

            SumLongAgg ragg = (SumLongAgg) aggregationBuffer;

            if (ragg.empty) {

                return null;

            }

            result.set(ragg.sum);

            return result;

        }

    }

}

HIVE优化

1.分区分桶

1.对于数据可以使用分区和分桶表对数据进行优化处理
表现：
1.分区表在HDFS中表目录下会生成分区目录 目录名分区字段=分区值，在分区目录中可以存储数据
2.分桶表在HDFS的表目录中，对应有多个数据包，每个数据包中存储的数据是 分桶表字段对分桶数进行取余结果相同的数据
建表：
自行查看
分区分桶的作用：
当对分区和分桶表进行查询数据时，如果对表中数据按分区字段和分桶字段进行WHERE条件过滤时，此时可以加载指定分区和分桶中的数据，避免全表扫描
注意：
对于分区表，需要知道按照那些列进行分区，并且依据是什么
1.根据后续业务查询字段进行判断
2.对于查询字段数据需要有一定的区分度
3.避免分区过多
一般情况下对于事实数据，需要按照日期进行分区

2.使用外部表

外部表的作用：
删除表时，只会删除元数据信息，不会删除具体的原始数据，对数据有一定的保护作用
使用场景： 一般情况下，是对原始表中的数据进行使用外部表修饰，原始表数据采集相对较为困难，同时表的元数据信息一般情况下不会发生变化

3.选择适当的文件压缩格式

一般情况下：
对于文本格式的数据，可以使用TextFile格式进行建表，同时TextFile格式应用场景一般是对刚采集的数据使用TextFile格式进行保存，便于直接查看数据，排查数据问题
ORCFile格式为压缩格式，具有很高的压缩比，同时查询数据效率也很高，对于计算的结果数据可以使用ORCFile格式进行保存

4.命名要规范

1.库命名规范：
单词间使用 _ 进行分割，同时单词需要使用小写，必要时使用简写形式，根据数据仓库的构建，会有固定的仓库名称来对数据进行按功能划分，比如 刚采集的数据存放在ods 库，计算的结果数据可以放在dwa/app库

2.表命名规范：
单词需小写，单词间使用 _ 进行分割。表命名一般可能会划分成多个层级来表示当前表的 功能以及存储的数据，比如 库名_业务名称_统计维度_表的性质
库名：表示当前表存储在所在的库
业务名称：根据保存的数据，在业务范围上的名称定义来进行，一般会有多个单词，每个单词间使用_进行分割
统计维度：可以按照 月(mon/m) 天(day/d) 小时(hour/H) 分钟(M) 进行标记
表的性质: tmp表示为临时表 mid 表示中间表 bak表示为备份

3.字段命名规范：
单词需小写，单词间使用 _ 进行分割，每个字段需要注释

5.数据分层，表分离，但是也不要分的太散

数据分层，表分离：
数据需要按照表的功能进行划分，存入到多个数据库中
将一张大的数据表进行拆分成多个表，比如对于一个事实表，如果事实表比较冗余，那么此时可以对表数据进行拆分
也不要分的太散：
如果分的过于分散，那么后续计算时，需要对拆分的数据进行join,会产生大量的shuffle过程，降低执行效率

6.分区裁剪 where过滤，先过滤，后join

分区裁剪：将表构建成一个分区表 对分区表使用 where 条件进行过滤，避免全表扫描，对于分区表如果需要进行JOIN操作，那么需要先使用 where条件过滤，之后再进行JOIN操作


7.mapjoin

前提：
当一张大表和小表进行关联时，可以使用mapjoin

1）设置自动选择Mapjoin
set hive.auto.convert.join = true; 默认为true
（2）大表小表的阈值设置（默认25M以下认为是小表）：
set hive.mapjoin.smalltable.filesize = 25000000;
对于小表数据默认自动使用mapjoin

explain命令：
可以查看当前SQL的执行计划

对于MapJOIN 可以调整大小表的阈值，将关联过程使用MapJoin但是需要注意，mapjoin是将小表放入内存当中，如果小表过大，内存存入不了，此时会出现内存溢出的错误，根据实际情况对hive.mapjoin.smalltable.filesize参数调整为50M

CREATE TABLE student_join_score1( 
  `id` string COMMENT '编号', 
  `name` string COMMENT '姓名', 
  `age` int COMMENT '年龄', 
  `gender` string COMMENT '性别', 
  `clazz` string COMMENT '班级',
  sum_score int COMMENT '总分'
);

explain
INSERT INTO TABLE student_join_score1
SELECT
T1.*
,T2.sum_score
FROM student_textfile T1 JOIN sum_score_tbl T2
;

hive中对于MapJOIN设置为启动开启
hive.auto.convert.join=true
– 关闭hive自动开启 mapjoin
set hive.auto.convert.join = false;

8.合并小文件

1）在map执行前合并小文件，减少map数：CombineHiveInputFormat 具有对小文件进行合并的功能（系统默认的格式）。HiveInputFormat没有对小文件合并功能。
set hive.input.format = org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
2）在Map-Reduce的任务结束时合并小文件的设置：
在map-only任务结束时合并小文件，默认true
SET hive.merge.mapfiles = true;
在map-reduce任务结束时合并小文件，默认false
SET hive.merge.mapredfiles = true;
合并文件的大小，默认256M
SET hive.merge.size.per.task = 268435456;
当输出文件的平均大小小于该值时，启动一个独立的map-reduce任务进行文件merge
SET hive.merge.smallfiles.avgsize = 128000000;

– 注意：
– 在其他框架中，也会使用HIVE中的结果表，但是对于其他框架，并没有合并小文件的自动优化，所以这时，就需要我们在HIVE中对其进行优化 比如Spark在读取HIVE数据时，不会对数据进行小文件合并，那么就需要在HIVE阶段定期对表中的数据进行小文件合并
– 对于HIVE来说，可以通过查询一张表的数据，将该表数据放入至另外一个表中，达到合并小文件的功能

一般生成小文件的情况：
1、对于使用采集工具每分钟或每小时采集一次的数据，每次采集都会生成一个对应的小文件
2、对于其他计算框架进行计算时，由于并发数过多，每个并发对应生成一个小文件

CREATE TABLE student_copy AS
SELECT
*
FROM bigdata.student

9.排序优化

– 全局排序
对于全局排序，通常使用order by 但是如果数据量较多情况下，order by中结果有可能运行不出来，或者执行较慢 ，全局排序要求Reduce数量为1
– 分区排序
对于分区排序，可以使用多个Reduce，通过 distribute by + sort by 对要排序的多列进行先分区，再对分区内的数据进行排序得到最终结果

10.数据倾斜优化

原因:
在SQL的执行过程中，存在有GROUP BY / JOIN 等操作，那么会产生Shuffle过程，将数据分发到多个Reduce中进行处理，但是每个Reduce中分配的数据并不一定均匀，那么这时每个Reduce执行时间不一致，导致有长尾现象（部分任务执行完，少部分任务依旧在执行） 整体任务执行较慢。

表现：
在SQL执行过程中，查看Yarn的执行过程，找到对应的ApplicationID,再查看其中的Reduce执行过程 通过Reduce的执行进度，进行判断其是否是一个数据倾斜问题
注意：如果每个Reduce执行都比较缓慢，那么不是数据倾斜问题,而是每个reduce中数据量都比较多

create table student_null like student;
SELECT
id
,count(*) as num
,avg(age) as avg_num
FROM student_null 
GROUP BY id


create table scores_null like scores;
load data local inpath '/root/score_null.txt' into table scores_null;

SELECT
T1.id
,sum(score)
FROM student_null T1 JOIN scores_null T2 ON T1.id  = T2.id
GROUP BY T1.id

image.png
image.png

如何解决该问题？

二次聚合

-- 原SQL
SELECT
id
,count(*) as num
,avg(age) as avg_age
FROM student_null 
GROUP BY id

/*
分析：
    1.将出现数据倾斜的Key添加随机值，进行一次统计
    2.对Key去除随机值，再进行一次求和
*/

-- 由于Reduce数量设置为4
set mapreduce.job.reduces = 4;

select floor((rand()*100)%4);

SELECT
split(T1.rand_id,'_')[0] as id
,sum(rand_num) as sum_num
,avg(rand_avg_age) as avg_age
FROM (
SELECT
concat(id,'_',floor((rand()*100)%4)) as rand_id
,count(*) as rand_num
,avg(age) as rand_avg_age
FROM student_null
GROUP BY concat(id,'_',floor((rand()*100)%4))
) T1
GROUP BY split(T1.rand_id,'_')[0]

对于Reduce端是将相同Key的Value值汇集到一起，出现数据倾斜的原因：相同Key的Value数据较多，发送到同一个Reduce中执行导致数据量较多执行较慢，添加随机值之后，将Key变成不一样的

2.过滤

对产生数据倾斜的Key进行做数据过滤操作

3.通过参数进行调整

1）开启Map端聚合参数设置
预聚合操作
set hive.map.aggr = true
set hive.groupby.mapaggr.checkinterval = 100000
set hive.groupby.skewindata = true
通过预聚合的方式 将部分数据在Map端就进行一次Reduce的业务逻辑，这样减少Shuffle过程，同时也降低了Reduce端处理数据的压力

11.合理设置Reduce个数

通过查看Yarn中的MapReduce执行过程，如果发现执行过程中Reduce都执行很慢，那么这时可以调整Reduce的数量

合理设置Reduce数
1）调整reduce个数方法一
（1）每个Reduce处理的数据量默认是256MB
hive.exec.reducers.bytes.per.reducer=256000000
（2）每个任务最大的reduce数，默认为1009
hive.exec.reducers.max=1009
（3）计算reducer数的公式
N=min(参数2，总输入数据量/参数1)

2）调整reduce个数方法二
在hadoop的mapred-default.xml文件中修改
设置每个job的Reduce个数
set mapreduce.job.reduces = N;

注意事项：reduce个数并不是越多越好
（1）过多的启动和初始化reduce也会消耗时间和资源；
（2）另外，有多少个reduce，就会有多少个输出文件，如果生成了很多个小文件，那么如果这些小文件作为下一个任务的输入，则也会出现小文件过多的问题；
在设置reduce个数的时候也需要考虑这两个原则：处理大数据量利用合适的reduce数；使单个reduce任务处理数据量大小要合适；

12.设置严格模式

Hive可以通过设置防止一些危险操作：
1）分区表不使用分区过滤 将hive.strict.checks.no.partition.filter设置为true时，对于分区表，除非where语句中含有分区字段过滤条件来限制范围，否则不允许执行。换句话说，就是用户不允许扫描所有分区。进行这个限制的原因是，通常分区表都拥有非常大的数据集，而且数据增加迅速。没有进行分区限制的查询可能会消耗令人不可接受的巨大资源来处理这个表。
2）使用order by没有limit过滤 将hive.strict.checks.orderby.no.limit设置为true时，对于使用了order by语句的查询，要求必须使用limit语句。因为order by为了执行排序过程会将所有的结果数据分发到同一个Reducer中进行处理，强制要求用户增加这个LIMIT语句可以防止Reducer额外执行很长一段时间。
3）笛卡尔积 将hive.strict.checks.cartesian.product设置为true时，会限制笛卡尔积的查询。对关系型数据库非常了解的用户可能期望在 执行JOIN查询的时候不使用ON语句而是使用where语句，这样关系数据库的执行优化器就可以高效地将WHERE语句转化成那个ON语句。不幸的是，Hive并不会执行这种优化，因此，如果表足够大，那么这个查询就会出现不可控的情况。

数据仓库

为什么需要数据仓库

业务系统分离，数据分散

对于有一些公司，会有多条业务线，业务线之间是相互隔离的，分别属于不同的部门或者子公司，比如对于保险公司，不同险种就是不同的业务线，分别存在自己内部的业务系统中。此时数据相对较为分散，不利于业务的融合发展，所以需要汇总数据，构建数据仓库（数据中心）

关系型数据库存储数据量较少，日志信息无法存储

关系型数据库比如Mysql，只能保存业务的系统数据，比如当前业务系统中的正在使用的业务数据，对于所有的业务历史数据，以及用户的操作记录信息无法保存。该部分数据相较而言较多。 比如：对于商城系统来说，该系统中关系型数据库存储的数据就是商品信息以及用户信息和当前订单信息，对于用户的浏览记录无法进行保存，无法对用户的行为进行分析，做商品推荐。
浏览记录在页面的操作日志中，一般是在前后端中添加埋点（打印的日志信息）

从决策者角度，关系型数据库并不适合

关系型数据库最主要的作用是：
对数据进行简单的增加 删除 修改 查询（遍历查询） 为后端系统提供这些服务
对于决策者来说，需要从整体数据来分析业务的发展趋势，比如对于某个业务当月的同环比增量，以及业务总量，运营成本等。需要对整个数据进行统计分析得到，传统的关系型数据库一般都是单节点，对于大批量数据的处理会较为吃力。

数仓具备特点：

数据仓库是面向主题的

数据仓库中的数据是按照一定的主题域进行组织，一个主题通常与多个操作型信息系统相关。 操作型信息系统可以看成是前后端业务系统
比如：对于保险行业，每个险种就是一个主题域 ，比如车险主题，用户主题，人寿主题等。 对于交通领域： 车辆主题（包含每个车辆的基本信息） 驾驶人主题（包含驾驶人相关的基本信息及和驾驶人相关的车辆 ） 事故主题（包含当前单位所管理的区域内所有的事故数据）等。。

数据仓库是集成的

所需数据从原来的分散数据中抽取出数据仓库的核心来，进行加工与集成统一与综合而成 。
数据仓库中的数据是会从其他多个业务系统中进行采集过来，对数据进行进一步的清洗加工

数据仓库是不可更改的

数据仓库主要是为决策分析提供数据，所涉及的操作主要是数据的查询；
对于非错误操作以外的数据，在数据仓库中都不能直接删除，对于业务系统中删除或修改的数据，在历史发展过程中都是有效的，有效的数据都需要在数据仓库中进行保存。

稳定的数据以只读格式保存，且不随时间改变

对于历史数据，数据采集到数据仓库中，经过一段时间处理后，数据通过有效性验证，就不会对该部分数据进行修改操作。后续只存在有读取查询

汇总的

将操作性数据映射成决策可用的格式。通过数据进行按照业务指标计算，得到最终可用的决策数据。

大容量

时间序列数据集合通常都非常大。表中的数据会随着时间的发展不断增多，例如：订单数据、车辆的过车数据

非规范化的

Dw数据可以是而且经常是冗余的。数据仓库中的数据可以不满足范式要求，一般只需要满足 第1、2范式即可

元数据

将描述数据的数据保存起来。对于HIVE来说其表的表结构信息就是元数据，同时对于表构建的数据字典也是属于元数据信息。

数据源

数据来自内部的和外部的非集成操作系统。 数据来源可以是公司业务系统，也可以是外部提供的，例如爬虫，以及其他公司购买的数据API

表的称谓

事实表

事实表是用来存储主题的主干内容，一些外键指向维度表。事实表一般是没有主键的，基本都是外键。数据的质量完全由业务系统来把握。一般单表字段较多，数据量比较大
主键：全局唯一，并且是索引有序的，用于标记当前该条数据，表示其唯一性，例如ID
外键: 在另外一张表中，可以使用当前表主键进行关联的。

事实表的判断方式：
事实表一般用于存储 实际发生过的行为数据，比如对于外卖订单数据，是通过下订单的行为所得到的数据。一般情况下事实表中都有时间维度。数据量较多，一般需要构建分区表对其进行存储，分区的维度通常为日期

维度表

事实表中某个方向分支，必须有主键，用于关联事实表。一般数据量较小，变化缓慢。
维度表的判断方式：
对于某个对象的属性进行描述，比如对于学生的基本信息，每个字段都是对学生对象进行描述的，同时用户信息、商品信息都是对象进行描述。

宽表：

字段和数据量比较巨大，很多维度杂糅在一起。好处：方便查询分析。缺点：没有规范。
宽表的构成： 根据主题域，将相同主题中的数据，进行关联汇集，形成的一张大表，称为一个宽表，宽表一般单独存放在宽表层（dws）中。
构建宽表的原因：
1.业务角度：
将相同业务的数据，汇集到一起，对于业务来说会有一个整体的描述，获取数据时，对于业务分析更加全面
2.技术角度：
在做指标数据计算时，如果不对数据进行汇总，那么在SQL查询时，就需要关联大量的表，造成shuffle过程IO过多，由于网络IO的速度较慢，导致整体执行速度较慢。于是可以对数据进行提前关联，将关联结果保存到磁盘中，用空间来换取执行时间。

拉链表：

记录一个事物从开始，一直到当前状态的所有变化的信息。

数据仓库建模方式

星形模型

image.png

事实表和维度表的关系：
星形模型： 一个事实表对应多个维度表可以进行关联，维度表没有其他的表可以再去进行关联操作

雪花模型

image.png

事实表和维度表的关系：
雪花模型： 一个事实表对应多个维度表可以进行关联，维度表可以再由其他的维度表进行关联得到

建模范式

第一范式：1NF简单点就是原子性，列不可再分，没有重复的列也没有重复的行，

第二范式：2NF非主属性必须依赖于键的全部，如果只依赖于主键的一部分，则需要移出创建新表。

对于数据仓库来说，仅仅需要满足第一范式或者第二范式即可

数据仓库实际上就是对数据进行管理分析的方法论，对于HIVE构建数据仓库，就需要对数据按照不同的库，将数据分门别类的存放，对于库如何设计，表如何划分种类，以及如何判断表属于哪些种类，以及库之间的关系是什么？

为什么要设计数据分层？

作为一名数据的规划者，我们肯定希望自己的数据能够有秩序地流转，数据的整个生命周期能够清晰明
确被设计者和使用者感知到。直观来讲就是如下的左图这般层次清晰、依赖关系直观。
但是，大多数情况下，我们完成的数据体系却是依赖复杂、层级混乱的。如下的右图，在不知不觉的情
况下，我们可能会做出一套表依赖结构混乱，甚至出现循环依赖的数据体系。
因此，我们需要一套行之有效的数据组织和管理方法来让我们的数据体系更有序，这就是谈到的数据
分层。数据分层并不能解决所有的数据问题，但是，数据分层却可以给我们带来如下的好处：
清晰数据结构：每一个数据分层都有它的作用域和职责，在使用表的时候能更方便地定位和理解
减少重复开发：规范数据分层，开发一些通用的中间层数据，能够减少极大的重复计算
统一数据口径：通过数据分层，提供统一的数据出口，统一对外输出的数据口径

口径：对于一个指标，不同的程序员会有不同的理解，比如：对于交通领域来说，饮酒驾驶和酒后驾驶
的违法编码选取可能会不太一样，需要对一个指标的计算逻辑进行确定。指标的计算逻辑就是口径

复杂问题简单化：将一个复杂的任务分解成多个步骤来完成，每一层解决特定的问题

数据仓库分层

**ODS（Operational Data Store）：数据运营层 **

“面向主题的”数据运营层，也叫ODS层，是最接近数据源中数据的一层，数据源中的数据，经过抽取、洗净、传输，也就说传说中的 ETL 之后，装入本层。本层的数据，总体上大多是按照源头业务系统的分类方式而分类的。
一般来讲，为了考虑后续可能需要追溯数据问题，因此对于这一层就不建议做过多的数据清洗工作，原封不动地接入原始数据即可，至于数据的去噪、去重、异常值处理等过程可以放在后面的DWD层来做。

**DWD（Data Warehouse Detail）：数据明细层 **

该层一般保持和ODS层一样的数据粒度，并且提供一定的数据质量保证。同时，为了提高数据明细层的易用性，该层会采用一些维度退化手法，将维度退化至事实表中，减少事实表和维表的关联。

**DWM（Data Warehouse Middle）：数据中间层 **

该层会在DWD层的数据基础上，对数据做轻度的聚合操作，生成一系列的中间表，提升公共指标的 复用性，减少重复加工。直观来讲，就是对通用的核心维度进行聚合操作，算出相应的统计指标。

**DWS（Data Warehouse Service）：数据服务层 **

又称宽表。按照业务划分，如流量、订单、用户等，生成字段比较多的宽表，用于提供后续的业务查询，OLAP分析，数据分发等。
一般来讲，该层的数据表会相对比较少，一张表会涵盖比较多的业务内容，由于其字段较多，因此一般也会称该层的表为宽表。在实际计算中，如果直接从DWD或者ODS计算出宽表的统计指标，会存在 计算量太大并且维度太少的问题，因此一般的做法是，在DWM层先计算出多个小的中间表，然后再拼接成一张DWS的宽表。由于宽和窄的界限不易界定，也可以去掉DWM这一层，只留DWS层，将所有的数据在放在DWS亦可。

**ADS/APP/DM（Application Data Store/Application/DataMarket）：数据应 用层/数据集市 **

在这里，主要是提供给数据产品和数据分析使用的数据，一般会存放在 ES、PostgreSql、Redis等系统中供线上系统使用，也可能会存在 Hive 或者 Druid 中供数据分析和数据挖掘使用。比如我们经常说的报表数据，一般就放在这里。

**DIM（Dimension）:维表层 **

维表层主要包含两部分数据：
高基数维度数据：一般是用户资料表、商品资料表类似的资料表。数据量可能是千万级或者上亿级别。
低基数维度数据：一般是配置表，比如枚举值对应的中文含义，或者日期维表。数据量可能是个位数或者几千几万。
数据仓库分层设计.png

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布