HIVE介绍
Hive 是建立在 Hadoop 上的数据仓库基础构架。它提供了一系列的工具，可以用来进行数据提取转化加载（ETL ），这是一种可以存储、查询和分析存储在 Hadoop 中的大规模数据的机制。Hive 定义了简单的类 SQL 查询语言，称为 HQL ，它允许熟悉 SQL 的用户查询数据。同时，这个语言也允许熟悉 MapReduce 开发者的开发自定义的 mapper 和 reducer 来处理内建的 mapper 和 reducer 无法完成的复杂的分析工作。
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的存储格式
建表语句
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 DML 用法
库操作
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';
在当前的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 ',' ;
dfs -put /root/student1000.csv /user/hive/warehouse/bigdata.db/student1000;
当创建表后，再通过文本文件添加数据到表中时，如果查询的结果，部分列出现NULL值需要排查 1.列分隔符是否出现错误 2.查看类型和数据类型是否一致 （当文本数据中的内容和列字段类型进行转换时，如果类型不匹配，那么会返回NULL）
需求：上述建表时，对于表的描述信息 包括字段类型、分隔符等出现错误，但是数据没有错误，每次删除表，都会将之前上传到HDFS中的数据删除了 。如何只删除元数据信息（表描述信息）而不删除HDFS中的数据
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 ',' ;
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中某个目录下已经存在由具体的数据了，那么此时要根据该部分数据进行创建 表并查询数据的条数
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 进行修改
需求：需要将查询的结果保存到对应的一个表中
AS 用法：
CREATE TABLE bigdata.clazz_gender_cnt AS
SELECT
clazz
,gender
,count(*) as num
FROM bigdata.student1000
GROUP BY clazz,gender
分区表
需求：在查询整个表中的数据时，对班级进行过滤，取出文科一班的所有学生
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;
修改表
修改表信息
-- 修改表名称
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
分区表
创建分区表
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);
```sql 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 方法上传数据**
```sql
--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=男
分区查询 创建 删除操作
**强制删除分区 **
当前表为分区表，并且为外部表（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 对于分区表只会覆盖当前指定的分区，对于其他分区数据不会发生任何变化
#### **增加动态分区操作** ```sql
INSERT OVERWRITE 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 需要将当前分区模式设置为非严格模式
– 添加数据时，需要从其他查询语句中进行添加，分区字段要求按照顺序，对应在SELECT查询语句的最后
select * from bigdata.student_partition_clazz_gender where clazz = ‘文科一班’ and gender = ‘女’;
#### **分区表作用**
> 1. 可以在HDFS的表目录中生成分区字段目录
> 2. 当分区表被加载查询过滤时，使用WHERE条件语句对分区字段进行过滤，那么会加载指定分区中的数据，避免全表扫描
#### **如何选取分区字段**
> 1.对于后续的查询业务中，该表中部分字段会被经常过滤的，可以作为分区字段
> 2.对于字段中的信息，需要有一定的区分度
> 3.由于HIVE数据是存储在HDFS上，并且HDFS元数据信息不能过多，于是分区表中的分区个数不能过多 => 防止产生大量的小文件
> 4.一般情况下，对于分区表存储的数据会比较大，同时是以时间不断递增的，那么分区表通常使用的字段为 日期字段
**分桶表**
**创建分桶表**
```sql
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 个桶中
-- 按照某些列进行分桶操作
CLUSTERED BY (col_name, col_name, ...)
-- 对数据进行排序操作
[SORTED BY (col_name [ASC|DESC], ...)]
-- 将数据分成num_buckets个桶
INTO num_buckets BUCKETS
```sql CREATE TABLE IF NOT EXISTS bucket_table( id int ,name STRING ) CLUSTERED BY(id) INTO 4 BUCKETS ROW FORMAT DELIMITED FIELDS TERMINATED BY ","; -- 对ID列进行分桶，并将数据写入4个桶中 ``` **插入数据** ```sql -- 1.x版本中可以使用该语句完成数据写入 3.x版本现不支持该用法 INSERT INTO TABLE bucket_table values ('1','student1'),('2','student2'),('3','student3'),('4','student4'),('5','student5'),('6','student6'),('7','student7'),('8','student8');
– 3.x 使用load 语句 dfs -put /root/bucket.txt /data load data inpath ‘/data/bucket.txt’ into table bucket_table; dfs -cat /user/hive/warehouse/bigdata.db/bucket_table/000000_0;
> set mapred.reduce.tasks=-1；
>
> Caused by: java.io.FileNotFoundException: File file:/root/bucket.txt does not exist
> -- 任务执行是使用 MapReduce进行计算，bucket.txt文件是存放在master节点的root目录下 ，从节点上执行MapReduce并不能找到本地路径中文件
查看数据
> hive> dfs -cat /user/hive/warehouse/bigdata.db/bucket_table/000000_0;
> 8,student8
> 4,student4
> hive> dfs -cat /user/hive/warehouse/bigdata.db/bucket_table/000001_0;
> 5,student5
> 1,student1
> hive> dfs -cat /user/hive/warehouse/bigdata.db/bucket_table/000002_0;
> 6,student6
> 2,student2
> hive> dfs -cat /user/hive/warehouse/bigdata.db/bucket_table/000003_0;
> 7,student7
> 3,student3
> 上述数据划分时，是以分桶列中的数据对 桶数量进行取余 得到余数相同的放在一个文件中
>
> SELECT * FROM bucket_table WHERE ID = 6;
> 上述SQL查询在所数据加载时，只需要对编号为000002_0文件进行做加载，从而避免全表扫描
> DESC FORMATTED 查看表结构时，可以看到表的桶数
> - 分桶表可以和分区表搭配使用
> - 分桶表中的字段需要在表字段定义时存在，分区表一定不能存在于字段定义阶段
### **数据加载**
#### **load 语句加载**
```sql
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的命令进行上传数据
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中
如果SQL查询结果有问题，可以通过中间生成的数据进行排查当子查询被使用多次，那么将该数据保存后，可以进行反复使用，节约计算资源
LOCATION
在建表时，指定数据的存储位置，通常和外部表搭配使用
IMPORT TABLE 表名 FROM “路径”
IMPORT TABLE student_import FROM "/data/export/student"
**IMPORT方式是对Export导出的数据进行导入操作 **
### 数据导出
#### **INSERT OVERWRITE LOCAL DIRECTORY "路径"** ```sql -- 将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 ```
#### **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
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– 不设定长度。
布尔型
布尔型 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中对于日期和时间格式数据会进行格式化
复杂数据类型
Array数据类型
-- Array数据类型可以在一列中存储多个数据
create table tbl_arr(
name String ,
score Array
)
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
)
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数据类型
create table tbl_struct(
name String ,
score struct
)
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 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
将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
需求：求出所有员工表中的部门编号
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
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
set mapred.reduce.tasks=4; 当Reduce数量发生变化时，此时结果不正确 当Reduce数量为1时，会将所有的数据发送到一个reduce Task中进行排序 该方式称为全局排序
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列 并没有完全的全局排序 **
**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 对某列进行排序 **
函数基本使用
查看函数
-- 查看所有支持的函数
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;
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函数可以进行多条件判断 在实际开发过程中，会经常使用，必须要会
字符串函数
必须知道：
lengthconcatsubstr,substringtrim**regexp_replace **regexp_extractsplit
了解：
reverseconcat_wsupperlowerget_json_object
日期函数
必须知道：
from_unixtimeunix_timestampto_dateweekofyeardatediffdate_adddate_subcurrent_dateadd_months
了解：
yearmonthdayhourminute** second**last_daytrunc
其他
** 必须掌握：**
roundfloorceilcollect_setcollect_list
需求：对于学生数据 要求将相同班级中的所有学生ID数据放入一列中
SELECT
clazz
,collect_list(id)
FROM student
GROUP BY clazz
collect_set以及collect_list 都是聚合函数，所以需要搭配GROUP BY 使用
需求：对于学生数据 要求将相同班级中的所有的年龄数据汇集到一个列中
SELECT
clazz
,collect_set(age)
FROM student
GROUP BY clazz
对于collect_set会将数据进行去重保存
行列互换
多行转一行
对于学生数据 要求将相同班级的数据 以 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行 4. 将count() OVER() 在第一行得到的结果 1000 和其他列字段进行合并
1500100001 施笑槐 22 女 文科六班 1000
再从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 女 文科六班 4. 使用count(*)对当前窗口中的数据进行计算得到结果为 104 5. 将结果和 * 的所有列进行合并
1500100001 施笑槐 22 女 文科六班 104
按照上述的过程再依次往下执行
需求：求出每个班级学生的最高分，以及当前班级中所有学生的基本信息和成绩明细数据
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
```sql 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函数**
```sql
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）昨天与当前天流量累加** ```sql
/* 分析： 昨天是当前行的上一行 -> 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）当前天数的前一天与后一天流量累加**
```sql
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.对于每个设备需要计算其吗没有数据的时间差，根据时间差长短判断是否存在问题 **
```sql 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’)
– 该数据每一条都代表一天是否有违法数据
```sql
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函数自定义** ```java 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函数自定义**
```java
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 allStructFieldRefs = argOIs.getAllStructFieldRefs();
if (allStructFieldRefs.size() !=2){
throw new UDFArgumentLengthException("当前UDTF函数的参数个数为2");
}
// 定义两个ArrayList fieldNames 用于存储当前返回值列的名称
ArrayList
// fieldOIs 用于存储当前返回值各列的列数据类型
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函数 进行替代 **
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
如何解决该问题？
二次聚合
-- 原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的速度较慢，导致整体执行速度较慢。于是可以对数据进行提前关联，将关联结果保存到磁盘中，用空间来换取执行时间。
拉链表：
记录一个事物从开始，一直到当前状态的所有变化的信息。
数据仓库建模方式
星形模型
事实表和维度表的关系： 星形模型： 一个事实表对应多个维度表可以进行关联，维度表没有其他的表可以再去进行关联操作
雪花模型
事实表和维度表的关系： 雪花模型： 一个事实表对应多个维度表可以进行关联，维度表可以再由其他的维度表进行关联得到
建模范式
第一范式：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）:维表层 **
维表层主要包含两部分数据： 高基数维度数据：一般是用户资料表、商品资料表类似的资料表。数据量可能是千万级或者上亿级别。 低基数维度数据：一般是配置表，比如枚举值对应的中文含义，或者日期维表。数据量可能是个位数或者几千几万。
推荐阅读
您阅读本篇文章共花了：
发表评论